Description:
Running 5.2.2 build (pulled from public bk on 20070204) as slave to 5.0.22-Debian_0ubuntu6.06.2-log.
Database being replicated is my MythTV database.
I've ALTERed the tables to ENGINE=Falcon and started slave (after restoring from mysqldump).
i got the 'replace into' bug... but converted a table to myisam and back (and set transaction-isolation=read-committed in my.cnf) and continued okay.
replication stopped with this error:
Last_Errno: 1032
Last_Error: Error 'Can't find record in 'program'' on query. De
fault database: 'mythconverg'. Query: 'DELETE FROM program WHERE starttime <= DA
TE_SUB(CURRENT_DATE, INTERVAL 1 DAY)'
then the following command produced this:
mysql> alter table program engine=myisam;
ERROR 1582 (23000): Duplicate entry '1009-2007-01-20 12:00:00-0' for key 'PRIMAR
Y'
CREATE TABLE `program` (
`chanid` int(10) unsigned NOT NULL DEFAULT '0',
`starttime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`endtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`title` varchar(128) NOT NULL DEFAULT '',
`subtitle` varchar(128) NOT NULL DEFAULT '',
`description` text NOT NULL,
`category` varchar(64) NOT NULL DEFAULT '',
`category_type` varchar(64) NOT NULL DEFAULT '',
`airdate` year(4) NOT NULL DEFAULT '0000',
`stars` float NOT NULL DEFAULT '0',
`previouslyshown` tinyint(4) NOT NULL DEFAULT '0',
`title_pronounce` varchar(128) NOT NULL DEFAULT '',
`stereo` tinyint(1) NOT NULL DEFAULT '0',
`subtitled` tinyint(1) NOT NULL DEFAULT '0',
`hdtv` tinyint(1) NOT NULL DEFAULT '0',
`closecaptioned` tinyint(1) NOT NULL DEFAULT '0',
`partnumber` int(11) NOT NULL DEFAULT '0',
`parttotal` int(11) NOT NULL DEFAULT '0',
`seriesid` varchar(12) NOT NULL DEFAULT '',
`originalairdate` date DEFAULT NULL,
`showtype` varchar(30) NOT NULL DEFAULT '',
`colorcode` varchar(20) NOT NULL DEFAULT '',
`syndicatedepisodenumber` varchar(20) NOT NULL DEFAULT '',
`programid` varchar(20) NOT NULL DEFAULT '',
`manualid` int(10) unsigned NOT NULL DEFAULT '0',
`generic` tinyint(1) DEFAULT '0',
`listingsource` int(11) NOT NULL DEFAULT '0',
`first` tinyint(1) NOT NULL DEFAULT '0',
`last` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`chanid`,`starttime`,`manualid`),
KEY `endtime` (`endtime`),
KEY `title` (`title`),
KEY `title_pronounce` (`title_pronounce`),
KEY `seriesid` (`seriesid`),
KEY `programid` (`programid`),
KEY `id_start_end` (`chanid`,`starttime`,`endtime`)
) ENGINE=Falcon DEFAULT CHARSET=latin1
then an alter back, and the slave was able to start again (!?)
although now,
Last_Errno: 1032
Last_Error: Error 'Can't find record in 'credits'' on query. De
fault database: 'mythconverg'. Query: 'DELETE FROM credits WHERE starttime <= DA
TE_SUB(CURRENT_DATE, INTERVAL 1 DAY)'
mysql> alter table credits engine=myisam;
ERROR 1582 (23000): Duplicate entry '1003-2007-01-25 00:25:00-584-director' for
key 'chanid'
and "select * from credits WHERE starttime <= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)" gives 3343 rows. although with replication and times and all that... this probably doesn't make much sense...
what's stranger is that i just stopped the falcon mysqld instance to tar up the datadir and when i started it again, replication resumed okay!
Apart from 1 REPLACE INTO bug (already filed), this database is being replicated to 5.1 NDB disk data okay.
How to repeat:
i have the 70MB tarball of the falcon instance datadir if needed.
I can also pass along the complete master system as well.
Suggested fix:
not fail
Description: Running 5.2.2 build (pulled from public bk on 20070204) as slave to 5.0.22-Debian_0ubuntu6.06.2-log. Database being replicated is my MythTV database. I've ALTERed the tables to ENGINE=Falcon and started slave (after restoring from mysqldump). i got the 'replace into' bug... but converted a table to myisam and back (and set transaction-isolation=read-committed in my.cnf) and continued okay. replication stopped with this error: Last_Errno: 1032 Last_Error: Error 'Can't find record in 'program'' on query. De fault database: 'mythconverg'. Query: 'DELETE FROM program WHERE starttime <= DA TE_SUB(CURRENT_DATE, INTERVAL 1 DAY)' then the following command produced this: mysql> alter table program engine=myisam; ERROR 1582 (23000): Duplicate entry '1009-2007-01-20 12:00:00-0' for key 'PRIMAR Y' CREATE TABLE `program` ( `chanid` int(10) unsigned NOT NULL DEFAULT '0', `starttime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `endtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `title` varchar(128) NOT NULL DEFAULT '', `subtitle` varchar(128) NOT NULL DEFAULT '', `description` text NOT NULL, `category` varchar(64) NOT NULL DEFAULT '', `category_type` varchar(64) NOT NULL DEFAULT '', `airdate` year(4) NOT NULL DEFAULT '0000', `stars` float NOT NULL DEFAULT '0', `previouslyshown` tinyint(4) NOT NULL DEFAULT '0', `title_pronounce` varchar(128) NOT NULL DEFAULT '', `stereo` tinyint(1) NOT NULL DEFAULT '0', `subtitled` tinyint(1) NOT NULL DEFAULT '0', `hdtv` tinyint(1) NOT NULL DEFAULT '0', `closecaptioned` tinyint(1) NOT NULL DEFAULT '0', `partnumber` int(11) NOT NULL DEFAULT '0', `parttotal` int(11) NOT NULL DEFAULT '0', `seriesid` varchar(12) NOT NULL DEFAULT '', `originalairdate` date DEFAULT NULL, `showtype` varchar(30) NOT NULL DEFAULT '', `colorcode` varchar(20) NOT NULL DEFAULT '', `syndicatedepisodenumber` varchar(20) NOT NULL DEFAULT '', `programid` varchar(20) NOT NULL DEFAULT '', `manualid` int(10) unsigned NOT NULL DEFAULT '0', `generic` tinyint(1) DEFAULT '0', `listingsource` int(11) NOT NULL DEFAULT '0', `first` tinyint(1) NOT NULL DEFAULT '0', `last` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`chanid`,`starttime`,`manualid`), KEY `endtime` (`endtime`), KEY `title` (`title`), KEY `title_pronounce` (`title_pronounce`), KEY `seriesid` (`seriesid`), KEY `programid` (`programid`), KEY `id_start_end` (`chanid`,`starttime`,`endtime`) ) ENGINE=Falcon DEFAULT CHARSET=latin1 then an alter back, and the slave was able to start again (!?) although now, Last_Errno: 1032 Last_Error: Error 'Can't find record in 'credits'' on query. De fault database: 'mythconverg'. Query: 'DELETE FROM credits WHERE starttime <= DA TE_SUB(CURRENT_DATE, INTERVAL 1 DAY)' mysql> alter table credits engine=myisam; ERROR 1582 (23000): Duplicate entry '1003-2007-01-25 00:25:00-584-director' for key 'chanid' and "select * from credits WHERE starttime <= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)" gives 3343 rows. although with replication and times and all that... this probably doesn't make much sense... what's stranger is that i just stopped the falcon mysqld instance to tar up the datadir and when i started it again, replication resumed okay! Apart from 1 REPLACE INTO bug (already filed), this database is being replicated to 5.1 NDB disk data okay. How to repeat: i have the 70MB tarball of the falcon instance datadir if needed. I can also pass along the complete master system as well. Suggested fix: not fail