MySQL Bugs: #26881: Large MERGE tables report incorrect specification when no differences in tables

Bug #26881	Large MERGE tables report incorrect specification when no differences in tables
Submitted:	6 Mar 2007 16:08	Modified:	18 Mar 2007 22:36
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S1 (Critical)
Version:	5.0.36	OS:	Any (All)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	backport_050036SP1, merge, regression

Description:
Large MERGE tables are now incorrectly being reported as being differently defined  to the base tables, even though they are not. This seems to happen when there is some form of length boundary hit internally. 

The following test case creates two tables, with the same columns - with 10 columns of each type, and then an auto_increment primary key. Selecting from them results in:

mysql> select * from t1;
Empty set (0.01 sec)

mysql> select * from m1;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

However, if I drop the primary key and id column on t1, and recreate the m1 table without these as well, everything works fine (note, they are still defined exactly the same beforehand though):

mysql> select * from t1;
Empty set (0.00 sec)

mysql> select * from m1;
Empty set (0.00 sec)

How to repeat:
flush tables;

drop table t1;
create table t1 (
i1 datetime,
i2 datetime,
i3 datetime,
i4 datetime,
i5 datetime,
i6 datetime,
i7 datetime,
i8 datetime,
i9 datetime,
i10 datetime,
v1 varchar(30),
v2 varchar(30),
v3 varchar(30),
v4 varchar(30),
v5 varchar(30),
v6 varchar(30),
v7 varchar(30),
v8 varchar(30),
v9 varchar(30),
v10 varchar(30),
m1 mediumint(8),
m2 mediumint(8),
m3 mediumint(8),
m4 mediumint(8),
m5 mediumint(8),
m6 mediumint(8),
m7 mediumint(8),
m8 mediumint(8),
m9 mediumint(8),
m10 mediumint(8),
s1 smallint(5),
s2 smallint(5),
s3 smallint(5),
s4 smallint(5),
s5 smallint(5),
s6 smallint(5),
s7 smallint(5),
s8 smallint(5),
s9 smallint(5),
s10 smallint(5),
t1 tinyint(3),
t2 tinyint(3),
t3 tinyint(3),
t4 tinyint(3),
t5 tinyint(3),
t6 tinyint(3),
t7 tinyint(3),
t8 tinyint(3),
t9 tinyint(3),
t10 tinyint(3),
id INT AUTO_INCREMENT,
PRIMARY KEY (id)
)engine=myisam;

drop table m1;
create table m1(
i1 datetime,
i2 datetime,
i3 datetime,
i4 datetime,
i5 datetime,
i6 datetime,
i7 datetime,
i8 datetime,
i9 datetime,
i10 datetime,
v1 varchar(30),
v2 varchar(30),
v3 varchar(30),
v4 varchar(30),
v5 varchar(30),
v6 varchar(30),
v7 varchar(30),
v8 varchar(30),
v9 varchar(30),
v10 varchar(30),
m1 mediumint(8),
m2 mediumint(8),
m3 mediumint(8),
m4 mediumint(8),
m5 mediumint(8),
m6 mediumint(8),
m7 mediumint(8),
m8 mediumint(8),
m9 mediumint(8),
m10 mediumint(8),
s1 smallint(5),
s2 smallint(5),
s3 smallint(5),
s4 smallint(5),
s5 smallint(5),
s6 smallint(5),
s7 smallint(5),
s8 smallint(5),
s9 smallint(5),
s10 smallint(5),
t1 tinyint(3),
t2 tinyint(3),
t3 tinyint(3),
t4 tinyint(3),
t5 tinyint(3),
t6 tinyint(3),
t7 tinyint(3),
t8 tinyint(3),
t9 tinyint(3),
t10 tinyint(3),
id INT AUTO_INCREMENT,
PRIMARY KEY (id)
) engine=merge union(t1);

select * from t1;
select * from m1;

flush tables;

alter table t1 drop PRIMARY KEY, drop column id;

drop table m1;
create table m1(
i1 datetime,
i2 datetime,
i3 datetime,
i4 datetime,
i5 datetime,
i6 datetime,
i7 datetime,
i8 datetime,
i9 datetime,
i10 datetime,
v1 varchar(30),
v2 varchar(30),
v3 varchar(30),
v4 varchar(30),
v5 varchar(30),
v6 varchar(30),
v7 varchar(30),
v8 varchar(30),
v9 varchar(30),
v10 varchar(30),
m1 mediumint(8),
m2 mediumint(8),
m3 mediumint(8),
m4 mediumint(8),
m5 mediumint(8),
m6 mediumint(8),
m7 mediumint(8),
m8 mediumint(8),
m9 mediumint(8),
m10 mediumint(8),
s1 smallint(5),
s2 smallint(5),
s3 smallint(5),
s4 smallint(5),
s5 smallint(5),
s6 smallint(5),
s7 smallint(5),
s8 smallint(5),
s9 smallint(5),
s10 smallint(5),
t1 tinyint(3),
t2 tinyint(3),
t3 tinyint(3),
t4 tinyint(3),
t5 tinyint(3),
t6 tinyint(3),
t7 tinyint(3),
t8 tinyint(3),
t9 tinyint(3),
t10 tinyint(3)
) engine=merge union(t1);

select * from t1;
select * from m1;

After talking to Trudy setting showstopper flag, since it's a regression.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21722

ChangeSet@1.2609, 2007-03-12 16:56:09+04:00, svoj@mysql.com +4 -0
  BUG#26881 - Large MERGE tables report incorrect specification when no
              differences in tables
  For certain merge tables were wrongly reported as having incorrect
  definition:
  - tables (underlying or merge) that were altered (affects 4.1 and up);
  - tables that have short varchar columns, which are converted to
    char (affects tables created by 4.1 only).
  
  This is fixed by relaxing a check for underlying conformance and setting
  field type to FIELD_TYPE_STRING in case varchar is shorter than 4
  when a table is created.

There're two bugs here, really. A changelog for the one of them:

If tables in a merge (and a MERGE table itself) had short VARCHAR column (less than 4 bytes) and at least one (but not all) tables were ALTER'ed (even to an identical table: ALTER TABLE xxx ENGINE=yyy), table definitions went ouf of sync.

this bug was not present in 5.0+ which doesn't automatically change short varchar to char.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21787

ChangeSet@1.2617, 2007-03-13 18:02:06+04:00, svoj@mysql.com +5 -0
  BUG#26881 - Large MERGE tables report incorrect specification when no
              differences in tables
  Certain merge tables were wrongly reported as having incorrect definition:
  - Some fields that are 1 byte long (e.g. TINYINT, CHAR(1)), might
    be internally casted (in certain cases) to a different type on a
    storage engine layer. (affects 4.1 and up)
  - If tables in a merge (and a MERGE table itself) had short VARCHAR column (less
    than 4 bytes) and at least one (but not all) tables were ALTER'ed (even to an
    identical table: ALTER TABLE xxx ENGINE=yyy), table definitions went ouf of
    sync. (affects 4.1 only)
  
  This is fixed by relaxing a check for underlying conformance and setting
  field type to FIELD_TYPE_STRING in case varchar is shorter than 4
  when a table is created.

Fixed in 4.1.23, 5.0.38, 5.1.17.

Noted in 4.1.23, 5.0.38, 5.1.17 changelogs.

For MERGE tables defined on underlying tables that contained a short
VARCHAR column (shorter than four characters), using ALTER TABLE on
at least one but not all of the underlying tables caused the table
definitions to be considered different from that of the MERGE table,
even if the ALTER TABLE did not change the definition.  

Also noted in 4.1.23 changelog.

Also, when the underlying tables contained a TINYINT or CHAR(1)
column, the MERGE storage engine incorrectly reported that they
differed from the MERGE table in certain cases.

Hello,

we have mysql version 4.1.23-pro and still have problem with MERGE tables.

mysql> status
--------------
mysql  Ver 14.7 Distrib 4.1.23, for pc-linux-gnu (i686) using  EditLine wrapper

Connection id:          2
Current database:       XY
Current user:           XY@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         4.1.23-pro
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    cp1250
Db     characterset:    cp1250
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock

and we have still problem with Merge tables.

mysql> create table t1 (
    -> i1 datetime,
    -> v1 varchar(30),
    -> m1 mediumint(8),
    -> s1 smallint(5),
    -> t1 tinyint(3),
    -> id INT AUTO_INCREMENT,
    -> PRIMARY KEY (id)
    -> )engine=myisam;
Query OK, 0 rows affected (0.04 sec)

mysql> create table m1(
    -> i1 datetime,
    -> v1 varchar(30),
    -> m1 mediumint(8),
    -> s1 smallint(5),
    -> t1 tinyint(3)
    -> ) engine=merge union(t1);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from m1;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

Is it possible to fix it in our licenced version?

Radek,

in `t1` you have extra `id` field, that doesn't exist in `m1`. So it is expected to get error in this case. You should either remove `id` from `t1` or add `id` to `m1`.