Bug #21411 Merge engine does not work on tables with varchar

• Submitted: 2 Aug 2006 10:45
• Modified: 2 Aug 2006 13:34
• Reporter: David Hammink
• Status: Can't repeat
• Category: MySQL Server: Merge storage engine
• Severity: S2 (Serious)
• Version: 5.0.22
• CPU Architecture: Any

[2 Aug 2006 10:45] David Hammink

Description:
I tried to make a merge table with two identical tables which contained varchar. 
After I setup the tables and the merged table I did a 
SELECT * FROM mergedtable it gave me
All tables in the MERGE table are not identically define

Alterred the varchar to char and it worked

How to repeat:
VARCHAR use in tables causes problems with merge
5.0.22 

Try this
CREATE TABLE `WLFiles_200608` (
  `WL_FileID` int(11) unsigned NOT NULL auto_increment,
  `INTELKENNUNG` smallint(4),
  `DienstID` smallint(4) ,
  `Date` datetime default NULL,
  `SerialNo` smallint(3) unsigned ,
  `FileName` varchar(100) default NULL,
  `Directory` varchar(100) default NULL,
  `Sent` bit(1) default NULL,
  PRIMARY KEY  (`WL_FileID`)
)ENGINE=MyISAM ;
CREATE TABLE `WLFiles_200608` (
  `WL_FileID` int(11) unsigned NOT NULL auto_increment,
  `INTELKENNUNG` smallint(4) ,
  `DienstID` smallint(4) ,
  `Date` datetime default NULL,
  `SerialNo` smallint(3) unsigned ,
  `FileName` varchar(100) default NULL,
  `Directory` varchar(100) default NULL,
  `Sent` bit(1) default NULL,
  PRIMARY KEY  (`WL_FileID`)
)ENGINE=MyISAM ;

CREATE TABLE `WLFiles_T` (
  `WL_FileID` int(11) unsigned NOT NULL auto_increment,
  `INTELKENNUNG` smallint(4) ,
  `DienstID` smallint(4) ,
  `Date` datetime default NULL,
  `SerialNo` smallint(3) unsigned ,
  `FileName` varchar(100) default NULL,
  `Directory` varchar(100) default NULL,
  `Sent` bit(1) default NULL,
  INDEX  (`WL_FileID`) 
  )
  ENGINE=MRG_MyISAM UNION=(WLFiles_200607,WLFiles_200608) INSERT_METHOD LAST;
  
  SELECT * FROM WLFiles_T;

the last statement will produce 
All tables in the MERGE table are not identically defined

Change the varchar into char and the problem is solved

CREATE TABLE `WLFiles_200608` (
  `WL_FileID` int(11) unsigned NOT NULL auto_increment,
  `INTELKENNUNG` smallint(4),
  `DienstID` smallint(4) ,
  `Date` datetime default NULL,
  `SerialNo` smallint(3) unsigned ,
  `FileName` char(100) default NULL,
  `Directory` char(100) default NULL,
  `Sent` bit(1) default NULL,
  PRIMARY KEY  (`WL_FileID`)
)ENGINE=MyISAM ;
CREATE TABLE `WLFiles_200608` (
  `WL_FileID` int(11) unsigned NOT NULL auto_increment,
  `INTELKENNUNG` smallint(4) ,
  `DienstID` smallint(4) ,
  `Date` datetime default NULL,
  `SerialNo` smallint(3) unsigned ,
  `FileName` char(100) default NULL,
  `Directory` char(100) default NULL,
  `Sent` bit(1) default NULL,
  PRIMARY KEY  (`WL_FileID`)
)ENGINE=MyISAM ;

CREATE TABLE `WLFiles_T` (
  `WL_FileID` int(11) unsigned NOT NULL auto_increment,
  `INTELKENNUNG` smallint(4) ,
  `DienstID` smallint(4) ,
  `Date` datetime default NULL,
  `SerialNo` smallint(3) unsigned ,
  `FileName` char(100) default NULL,
  `Directory` char(100) default NULL,
  `Sent` bit(1) default NULL,
  INDEX  (`WL_FileID`) 
  )
  ENGINE=MRG_MyISAM UNION=(WLFiles_200607,WLFiles_200608) INSERT_METHOD LAST;

[2 Aug 2006 11:10] David Hammink

Adding default values to the smallint values cuase the same error to appear

[2 Aug 2006 13:34] Valeriy Kravchuk

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with 5.0.25-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `WLFiles_200607` (
    ->   `WL_FileID` int(11) unsigned NOT NULL auto_increment,
    ->   `INTELKENNUNG` smallint(4),
    ->   `DienstID` smallint(4) ,
    ->   `Date` datetime default NULL,
    ->   `SerialNo` smallint(3) unsigned ,
    ->   `FileName` varchar(100) default NULL,
    ->   `Directory` varchar(100) default NULL,
    ->   `Sent` bit(1) default NULL,
    ->   PRIMARY KEY  (`WL_FileID`)
    -> )ENGINE=MyISAM ;
CQuery OK, 0 rows affected (0.01 sec)

Rmysql> CREATE TABLE `WLFiles_200608` (
    ->   `WL_FileID` int(11) unsigned NOT NULL auto_increment,
    ->   `INTELKENNUNG` smallint(4) ,
    ->   `DienstID` smallint(4) ,
    ->   `Date` datetime default NULL,
    ->   `SerialNo` smallint(3) unsigned ,
    ->   `FileName` varchar(100) default NULL,
    ->   `Directory` varchar(100) default NULL,
    ->   `Sent` bit(1) default NULL,
    ->   PRIMARY KEY  (`WL_FileID`)
    -> )ENGINE=MyISAM ;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `WLFiles_T` (
    ->   `WL_FileID` int(11) unsigned NOT NULL auto_increment,
    ->   `INTELKENNUNG` smallint(4) ,
    ->   `DienstID` smallint(4) ,
    ->   `Date` datetime default NULL,
    ->   `SerialNo` smallint(3) unsigned ,
    ->   `FileName` varchar(100) default NULL,
    ->   `Directory` varchar(100) default NULL,
    ->   `Sent` bit(1) default NULL,
    ->   INDEX  (`WL_FileID`)
    ->   )
    ->   ENGINE=MRG_MyISAM UNION=(WLFiles_200607,WLFiles_200608) INSERT_METHOD
LAST;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM WLFiles_T;
Empty set (0.00 sec)

So, please, wait for 5.0.24 to be released officially and check with it.