MySQL Forums
Forum List  »  Merge Storage Engine

Merge table incorrectly claiming tables are not identical
Posted by: ahigerd
Date: September 28, 2006 12:10PM

I've been bashing at this for about an hour now. I'm using MySQL 5.0.24 and I'm trying to create a merge table to ease implementation of a new feature for my application, but no matter what I try I get the error:

ERROR 1168 (HY000): All tables in the MERGE table are not identically defined

However, I can confirm that the tables are indeed identical; both tables produce bytewise identical output in SHOW CREATE TABLE except for the names of the tables. In fact, the second table was generated by changing the name in the SHOW CREATE TABLE for the first table. The merge table was created using the same method, except with the primary key changed to an ordinary key as the documentation instructs and with the engine and union set appropriately.

The source tables follow this schema:
CREATE TABLE `eventLog` (
`eventLogID` bigint(20) unsigned NOT NULL auto_increment,
`usersID` bigint(20) unsigned NOT NULL default '0',
`timeIn` bigint(20) unsigned default NULL,
`timeOut` bigint(20) unsigned default NULL,
`flagIn` enum('0','1') NOT NULL default '0',
`flagOut` enum('0','1') NOT NULL default '0',
`adminID` bigint(20) unsigned default NULL,
`auditDate` bigint(20) unsigned default NULL,
`auditNote` text,
`jobID` bigint(20) unsigned NOT NULL default '0',
`clockNote` varchar(255) default NULL,
`adjTimeIn` int(10) unsigned default NULL,
`adjTimeOut` int(10) unsigned default NULL,
`clientIn` int(10) unsigned default NULL,
`clientOut` int(10) unsigned default NULL,
`clientAdjIn` int(10) unsigned default NULL,
`clientAdjOut` int(10) unsigned default NULL,
`changelogID` int(10) unsigned default NULL,
`remoteIpIn` varchar(15) default NULL,
`remoteIpOut` varchar(15) default NULL,
`cidInNumber` varchar(32) default NULL,
`cidInName` varchar(32) default NULL,
`cidOutNumber` varchar(32) default NULL,
`cidOutName` varchar(32) default NULL,
`voiceIn` varchar(18) default NULL,
`voiceOut` varchar(18) default NULL,
`clockNoteOut` varchar(255) default NULL,
PRIMARY KEY (`eventLogID`),
KEY `timeIn` (`timeIn`),
KEY `jobID` (`jobID`),
KEY `usersID` (`usersID`),
KEY `timeOut` (`timeOut`)
) ENGINE=MyISAM AUTO_INCREMENT=374 DEFAULT CHARSET=latin1 PACK_KEYS=1

The merge table follows the same schema except for the following differences:
< PRIMARY KEY (`eventLogID`),
> KEY (`eventLogID`),

< ) ENGINE=MyISAM AUTO_INCREMENT=374 DEFAULT CHARSET=latin1 PACK_KEYS=1
> ) ENGINE=MERGE UNION=(eventLog, deletedEventLog) DEFAULT CHARSET=latin1

The only difference I can possibly see is that SHOW TABLE STATUS indicates that eventLog is a version 9 table while deletedEventLog is a version 10 table, but mysql_upgrade does nothing to the table.

Options: ReplyQuote


Subject
Views
Written By
Posted
Merge table incorrectly claiming tables are not identical
5994
September 28, 2006 12:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.