MySQL Bugs: #17713: merge table error using table from 4.1 to 5.0

Bug #17713	merge table error using table from 4.1 to 5.0
Submitted:	24 Feb 2006 23:19	Modified:	23 Jan 2008 12:12
Reporter:	Rob Burnett
Status:	Won't fix
Category:	Server: Merge	Severity:	S2 (Serious)
Version:	5.0.22-BK, 5.0.18	OS:	Microsoft Windows (win 2000 server)
Assigned to:	Ingo Strüwing	Target Version:
Triage:	D3 (Medium)

Description:
This thread describes the problem and a response from Ingo Strüwing that
I should report this to the bugs section.

http://forums.mysql.com/read.php?93,71961,71961#msg-71961

Bascally 4.1 tables used as merge moved to version 5.0 are not recoginzed to be the same
and when they are tried to be used in a merge table they give the error 

"All tables in the MERGE table are not identically defined "

The show create table show correctly however I think what Ingo Strüwing wrote
in that the the header in the MyISAM index file shows differences this could be the
problem.

Thanks

How to repeat:
Only an example, Take two tables from a 4.1 server that were used in a merge table and
move them to version 5.0 and delete the merge table and try to create a new merge table
with the two tables and you will get the error

"All tables in the MERGE table are not identically defined "

You cannot use the create table like .... and populate the new table created with the
data.. you have to do a show create table and run that in a sql query to make the new
table and then move the data from the 4.1 table to the newly created 5.0 table and do
this for the 2nd table and now the merge table will work.

Thank you for a problem report. Please, describe the procedure in more details. Have you
just copied files for MyISAM tables from 4.1 to 5.0? Have you performed mysqldump and
then restore, as it is recomended in the manual?

I can interject here.  I have this very same issue in attempting an upgrade from 4.1 to
5.0.19.  Although all MYISAM and MERGE tables work initially after moving files from one
version to the other, any attempt to create a MERGE table against migrated 4.1 tables
failed with the error mentioned.  I have attempted to mysqldump and restore the tables
and this indeed does correct the issue.  Any type of recreation of the table structure in
5.0.19 will put the tables in a working order perfectly including using ALTER TABLE
Engine=MyISAM on them.

It is just the TIME!  I have about 200 tables and this is production.  My upgrade window
is like 4 hours max!  I desperately need alternatives.

Your merge tables if copied from 4.1 to a 5.# server will work if the storage location is
the same.. IE the directory of the database is the same path as it appears that the dir
path to the tables that make up the merge table are stored in the merge table file. My
1st problem i changed paths and then the copies to the 5.# server will not work. If i
hand edit the paths to the correct one then it works. The larger problem here is the
tables cannot be migrated by merely making a copy of them and moving them or upgrading
the server as the table structure is different from 4.# to to the 5.#, therefore unless
some code is changed you have to do the sql dump and restore... or you can do a show
create table and copy this out and recreate a new table and copy the data from the 4.#
table to the newly created 5.# table. 

This creates many problems, as the migration of the older server files cannot be easily
brought to the 5. server. The mysql dump.. is so painfully slow I've not used it to date
and have had errors associated with it at times. My web code actually drops the merge and
works on the individual tables  and recreates the merge, so i have to have all the tables
upgraded.

What i'm working on now is getting the code and everything up on the 5.# server and will
move it all over the weekend etc.. however i'm runng into other problems while using 5.#
with data load infiles.. etc...

Version 5.0.19 has a special option that may help you with tables taken from 4.1.  Please,
read the manual (http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html):

"--check-upgrade, -g

Invoke CHECK TABLE with the FOR UPGRADE option to check tables for incompatibilities with
the current version of the server. This option was added in MySQL 5.0.19."

Maybe, it will help you to check tables online. Anyway, as the only proper way to upgrade
is described in the manual, I think, it is not a bug.

Hi, I've been following this bug, as I have the same problem.  We have a warehouse built
on MySQL with around 120gb of data, most of it built using merge tables.  I ran into the
same problem when attempting to upgrade a portion of our database on a test machine.

As an aside, I was able to make the merge work by executing an alter statement on each of
the child tables, instead of the mysqldump.  I would imagine that the alter will be
quicker than the mysqldump.  I first executed the alter as follows:

alter table <table> change <colname> <colname> <old definition>

on some random column and that did the trick for me.

In any case, rebuilding all the tables is very time consuming, and will really hamper our
ability to upgrade.  I would imagine there are quite a few other installations that will
have the same problem, and even worse- it's likely going to be the largest installations
that will be using merge tables like this, and so it's these large installations that
will suffer the most from having to manually upgrade tables. 

Right now for an upgrade we're faced with having to migrate as many tables as possible
offline on another machine prior to the upgrade, then taking significant downtime while
we upgrade to 5.0 then migrate the rest of the tables, which is pretty unpalatable.

I think it's perfectly ok to change the format of a table or column between releases, so
that when I alter a table's structure, my new definition is incompatible with the old
one.  However, I think you should allow us to create merge tables in 5.0 using the 4.1
varchar definition, as long as all the tables in the union are using the 4.1 varchar. 
Doing so would allow us to upgrade to the 5.0 structure in place, incrementally, and
would make life a lot easier.

(sorry didn't mean to be anonymous there)
My upgrade was from 4.1.16 to 5.0.19, on linux centos 3.

As small warning to those who are following this bug. I also had this problem and worked
around it by "ALTERing" 200GBs of child tables, but a while later I found the MySQL V5
was too unstable on one of the production servers (a 64bit SMP) - see bug
http://bugs.mysql.com/?id=18137, which isn't getting any where.

But then I could not continue to use V5 nor could I back-out because I cant "UnALTER" the
V5 tables back to V4. So you need to be really sure V5 is stable enough for you before you
do the ALTERs.

Dave

another note here.  It doesn't seem like mysqlcheck -g will catch this issue.  mysqlcheck
-g executes CHECK TABLE FOR UPGRADE, but my version 9 mysql tables with varchars come
back as OK from that check.  For instance (on a test table I created before upgrading):

ck@localhost stage> show create table refer_test\G
*************************** 1. row ***************************
       Table: refer_test
Create Table: CREATE TABLE "refer_test" (
  "referrer" varchar(255) NOT NULL default '',
  "type" varchar(32) default NULL,
  "domain" varchar(255) default NULL,
  "path" varchar(255) default NULL,
  "qs" varchar(255) default NULL,
  PRIMARY KEY  ("referrer")
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ck@localhost stage> show table status like 'refer_test'\G
*************************** 1. row ***************************
           Name: refer_test
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 474217
 Avg_row_length: 213
    Data_length: 101058856
Max_data_length: 4294967295
   Index_length: 27843584
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2005-03-01 09:38:05
    Update_time: 2005-03-21 23:11:26
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

ck@localhost stage> check table refer_test for upgrade;
+------------------+-------+----------+----------+
| Table            | Op    | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| stage.refer_test | check | status   | OK       |
+------------------+-------+----------+----------+
1 row in set (0.00 sec)

ck@localhost stage> create table merge_test like refer_test;
Query OK, 0 rows affected (0.00 sec)

ck@localhost stage> alter table merge_test engine=merge union=(refer_test);
ERROR 1168 (HY000): All tables in the MERGE table are not identically defined

Here's somthing to think about Valeriy....

MySql does say "MySQL generally recommends that you dump and reload your tables from any
previous version to upgrade to 5.0..... but the rest of the documentation says that you
can move the files and try all kinds of stuff to repair and fix etc due to errors etc...

They need to specifically state that you MUST dump the tables and bring them back in so 
you do not you have this issue as well as other problems associated with the change made
in the Myisam table header. Pretty simple i guess, but it's a major issue for people that
have REAL databases that have large amounts of data...  I've actually have started looking
into MS SQL server and the oracle version as the changes in the newer versions of MySql
are making my life miserable and If i have to make major changes like this to move
forward then it's not really worth it for me even though it's free...

Actually If you do a show create table on the tables and create a new table and then
insert from the 4. to the 5. table things will work ok... You cannot do a create table
#ver5.name# like #ver4.name#.... this will take the 4. header and recreate it and you'll
not a ver 5. table...

hmmm.... thinking here.

Verified with 5.0.22-BK on Linux. Start with MySQL 4.1-BK:

mysql> create table v41 (c1 int auto_increment primary key, c2 varchar(100))
engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into v41 (c2) values(rand());
Query OK, 1 row affected (0.00 sec)

mysql> insert into v41 (c2) values(rand());
Query OK, 1 row affected (0.00 sec)

mysql> insert into v41 (c2) values(rand());
Query OK, 1 row affected (0.00 sec)

mysql> insert into v41 (c2) values(rand());
Query OK, 1 row affected (0.00 sec)

mysql> show table status like 'v41'\G
*************************** 1. row ***************************
           Name: v41
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 28
    Data_length: 112
Max_data_length: 4294967295
   Index_length: 2048
      Data_free: 0
 Auto_increment: 5
    Create_time: 2006-04-28 17:07:59
    Update_time: 2006-04-28 17:08:08
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/4.1> bin/mysqladmin -uroot shutdown
STOPPING server from pid file /home/openxs/dbs/4.1/var/suse.pid
060428 17:08:27  mysqld ended

[1]+  Done                    bin/mysqld_safe
openxs@suse:~/dbs/4.1> cd ../5.0
openxs@suse:~/dbs/5.0> cp ../4.1/var/test/v41.* var/test
openxs@suse:~/dbs/5.0> bin/mysqld_safe &
[1] 20752
openxs@suse:~/dbs/5.0> Starting mysqld daemon with databases from /home/openxs/d
bs/5.0/var

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 3 to server version: 5.0.22

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

mysql> show table status like 'v41'\G
*************************** 1. row ***************************
           Name: v41
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 28
    Data_length: 112
Max_data_length: 4294967295
   Index_length: 2048
      Data_free: 0
 Auto_increment: 5
    Create_time: 2006-04-28 17:07:59
    Update_time: 2006-04-28 17:08:46
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> create table merge_test like v41;
Query OK, 0 rows affected (0.00 sec)

mysql> check table v41 for upgrade;
+----------+-------+----------+----------+
| Table    | Op    | Msg_type | Msg_text |
+----------+-------+----------+----------+
| test.v41 | check | status   | OK       |
+----------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> alter table merge_test engine=merge union(v41);
ERROR 1168 (HY000): All tables in the MERGE table are not identically defined

There is still NO OTHER WAY to upgrade, in most cases, other than dump and restore! Sorry
for that. This should never happened.

I see the same problem with Fedora Linux. Using Alter on the sub table appears to
"upgrade" the sub tables to 5.2.

Has anyone heard of other workarounds?

Strange how the 4.x sub tables work stand alone unde 5.x but not as part of a merge
table.

Very disappointing that the MySQL folks do not consider this a problem !

Very atypical in my experience.

I guess I need a conversion script to convert my 4000+ tables.

Jack

In 5.0 we have a new varchar repesentation. The records in the .MYD file look differently.
5.0 can work with both kinds of varchar representation. But when a new table is created or
a table is altered, only the new varchar representation is created.

New and old varchar columns have different types internally. When a MERGE table compares
the MyISAM tables it rates an old and new table as incompatible if they have at least one
varchar column. The types are stored in the .frm file and the MERGE table itself has a
.frm too. All of them need to match.

For a MERGE table to be usable, either all tables, including the MERGE table itself, are
old, or all tables, including the MERGE table itself, are new.

When migrating to 5.0, a MERGE table should work, as long as none of the tables is
altered. As mentioned by Rob Burnett, the change of the paths in the .MRG file can be
done with an editor. But after altering any (but not all) of the tables, the MERGE table
stops working.

Hence the recommendation is to dump and restore the tables for the migration.
The second best solution is to ALTER all tables. ALTER TABLE name ENGINE=MyISAM for the
children is sufficient. ALTER TABLE name ENGINE=MRG_MYISAM should work for the parent.

One possible way around this would be to allow to specify a special column type for the
old varchar. Thanks to Chris Kiernan for this idea.

However, what we need is this:
- A good name for the old varchar type (OLDVARCHAR?, TRIMCHAR?).
- Permission to implement this from our architecture board.

Another alternative could be to compare old and new varchar as equivalent types and
handle the difference in the merge code. This would mean that accesses of old tables need
to convert the records from/to the new format. The parent table can always quickly be
altered to be of the new type. Anyway, this alternative seems to be much more complicated
and bears higher risk for new bugs.

I'll search advice from the architecture board.

Changed status to "Need feedback" while waiting for a decision from our architecture
board.

One small hint of feedback, I received, said that the representation of old and new
varchar in the .MYD file is the same. We could make a migration program that changes the
header of the .MYI file and the .frm file. The details are still open though.

I may have misunderstood the suggestion, or it won't work.
Need more feedback from our architects.

If a user, for whatever reason, does an ALTER TABLE on a merge table
in 5.0, the underlying 4.1 tables can be incompatible with the 5.0
merge table.

Using the merge table then may lead to error 1168, and this can easily
be fixed by issuing an ALTER TABLE on the underlying 4.1 tables and
the merge tables to fix the problem:

  ALTER TABLE underlying_table ENGINE=MyISAM;
  ALTER TABLE merge_table ENGINE=MyISAM;

I was not sure this bug#34444 is similar to this, but filed a new one; it has a way on how
to fix the code.