subject:"Extended insert syntax and replication"

Extended insert syntax and replication

2005-06-23 Thread Jeremiah Gowdy

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 '-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 '-00-00 00:00:00',
 `FinishedDateTime` datetime NOT NULL default '-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 '-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]

Re: Extended insert syntax and replication

2005-06-23 Thread mfatene

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 '-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 '-00-00 00:00:00',
   `FinishedDateTime` datetime NOT NULL default '-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 '-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]

Re: Extended insert syntax and replication

2005-06-23 Thread 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 '-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 '-00-00 00:00:00',
  `FinishedDateTime` datetime NOT NULL default '-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 '-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]

Re: Extended insert syntax and replication

2005-06-23 Thread mfatene

see this for troubleshootings :
http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/Replication_Problems.html

hope that helps

Mathias

Selon Jeremiah Gowdy [EMAIL PROTECTED]:

 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 '-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 '-00-00 00:00:00',
`FinishedDateTime` datetime NOT NULL default '-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 '-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]





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

Extended insert syntax and replication

Re: Extended insert syntax and replication

Re: Extended insert syntax and replication

Re: Extended insert syntax and replication

4 matches

Site Navigation

Mail list logo

Footer information