Re: Extended insert syntax and replication

[Jeremiah Gowdy]

The inserts succeed on the master, so the problem isn't my SQL syntax.  They 
are legal extended inserts.  It's just that the replication slave seems to 
insert them out of order and screw up the auto-increment primary key.

----- Original Message ----- 
From: <[EMAIL PROTECTED]>
To: "Jeremiah Gowdy" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Thursday, June 23, 2005 2:51 PM
Subject: Re: Extended insert syntax and replication

Hi,
this is a perl script converter for inserts to simple form. if you work 
from a
mysqldump, you can try it to see if the converted inserts do not genrate 
errors
:
http://platon.sk/cvs/cvs.php/scripts/perl/mysql/mysqldump-convert.pl

Mathias

Selon Jeremiah Gowdy <[EMAIL PROTECTED]>:

I have two servers doing replication for logs.  When I do extended insert
syntax on the master to combine multiple log entries, the slave complains
about duplicate primary key numbers, even though my inserts don't set the
primary key and the primary key is auto_increment.

So any time I use extended insert syntax, my replication breaks with:

Duplicate entry '2835610' for key 1 on query. Default database:
'AppServerLog'. Query: 'INSERT DELAYED INTO AppServerLog.Details 
(SessionID,
FunctionCallID, DetailLevel, Tag, DateTime, SourceFileName,
SourceLineNumber, Data) VALUES
(361019539513084542,0,'Low',23,20050623142238,'',0,'Detected incoming 
call')

Here are the tables that cause this behavior.  Replication only breaks 
when
I use extended insert syntax.  What I notice in the slave is that it 
seems
that it is doing the inserts out of order.


CREATE TABLE `Details` (
  `ID` bigint(20) NOT NULL auto_increment,
  `SessionID` bigint(20) NOT NULL default '0',
  `FunctionCallID` bigint(20) NOT NULL default '0',
  `DetailLevel` enum('Error','Warn','Low','High') NOT NULL default 
'Error',
  `Tag` int(11) NOT NULL default '0',
  `DateTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `SourceFileName` varchar(100) NOT NULL default '',
  `SourceLineNumber` int(11) NOT NULL default '0',
  `Data` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `FunctionCalls` (
  `Sequence` int(11) NOT NULL auto_increment,
  `ServerName` varchar(32) NOT NULL default '',
  `SessionID` bigint(20) NOT NULL default '0',
  `ProcessID` int(11) NOT NULL default '0',
  `ThreadID` int(11) NOT NULL default '0',
  `FunctionName` varchar(64) NOT NULL default '',
  `FunctionVersion` int(11) NOT NULL default '0',
  `CalledDateTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `FinishedDateTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `Exception` tinyint(4) NOT NULL default '0',
  `ID` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Session` (
  `Sequence` int(11) NOT NULL auto_increment,
  `IP` varchar(24) NOT NULL default '',
  `Identity` varchar(64) NOT NULL default '',
  `ProgramName` varchar(32) NOT NULL default '',
  `ProgramSessionID` bigint(20) NOT NULL default '0',
  `Established` datetime NOT NULL default '0000-00-00 00:00:00',
  `ID` bigint(20) NOT NULL default '0',
  `AppServerNumber` int(11) NOT NULL default '0',
  PRIMARY KEY  (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]