MySQL Bugs: #51948: MySQL5.1/5.5 can't open MyISAM tables created with MySQL 6.0 or MariaDB 5.1

Bug #51948	MySQL5.1/5.5 can't open MyISAM tables created with MySQL 6.0 or MariaDB 5.1
Submitted:	11 Mar 2010 9:59	Modified:	22 Apr 2010 18:23
Reporter:	Michael Widenius	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	compatilbility, Contribution, myisam

Description:
MySQL5.1/5.5 can't open MyISAM tables created with MySQL 6.0 or MariaDB 5.1

The same is probably true of MyISAM tables created with Drizzle (not verified but likely as Drizzle is based on MySQL 6.0)

If you create a table with for example MariaDB 5.1 and then copy it to MySQL, you will get the error:

Old key file for table 'xxxx'; repair it!

However REPAIR will fail, as MyISAM can't open the file.

Se also:
http://bugs.launchpad.net/maria/+bug/534626

How to repeat:
In MariaDB:

use test;
CREATE TABLE t1 (a char(100), b int) ENGINE=myisam;
INSERT INTO t1 values ('aaaa', 10);
SELECT * FROM t1;

Shutdown MariaDB and point MySQL at the same datadir.

In MySQL:

use test;
SELECT * FROM t1;
REPAIR TABLE t1;

Errors from MySQL:

mysql> select * from t1;
ERROR 1035 (HY000): Old key file for table 't1'; repair it!
mysql> repair table t1;
+---------+--------+----------+-----------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+--------+----------+-----------------------------------------+
| test.t1 | repair | Error | Old key file for table 't1'; repair it! |
| test.t1 | repair | error | Corrupt |
+---------+--------+----------+-----------------------------------------+

Suggested fix:
=== modified file 'storage/myisam/mi_open.c'
--- storage/myisam/mi_open.c	2009-10-27 13:20:34 +0000
+++ storage/myisam/mi_open.c	2010-03-11 09:18:31 +0000
@@ -150,7 +150,7 @@ MI_INFO *mi_open(const char *name, int m
 	  HA_OPTION_COMPRESS_RECORD | HA_OPTION_READ_ONLY_DATA |
 	  HA_OPTION_TEMP_COMPRESS_RECORD | HA_OPTION_CHECKSUM |
           HA_OPTION_TMP_TABLE | HA_OPTION_DELAY_KEY_WRITE |
-          HA_OPTION_RELIES_ON_SQL_LAYER))
+          HA_OPTION_RELIES_ON_SQL_LAYER | HA_OPTION_NULL_FIELDS))
     {
       DBUG_PRINT("error",("wrong options: 0x%lx", share->options));
       my_errno=HA_ERR_OLD_FILE;

Thank you for the bug report. Verified just as described on Mac OS X:

77-52-57-53:maria openxs$ bin/mysql -uroot test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.1.42-MariaDB-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> create table tmaria(c1 int primary key, c2 int) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

MariaDB [test]> insert into tmaria values(1,2), (2,3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> exit
Bye
77-52-57-53:maria openxs$ bin/mysqladmin -uroot shutdown
100311 20:27:13 mysqld_safe mysqld from pid file /Users/openxs/dbs/maria/var/77-52-63-22.dialup.umc.net.ua.pid ended
[1]+  Done                    bin/mysqld_safe
77-52-57-53:maria openxs$ cp var/test/tmaria.* ../5.1/var/test/
77-52-57-53:maria openxs$ cd ../5.1
77-52-57-53:5.1 openxs$ bin/mysqld_safe &
[1] 89475
77-52-57-53:5.1 openxs$ 100311 20:27:38 mysqld_safe Logging to '/Users/openxs/dbs/5.1/var/77-52-63-22.dialup.umc.net.ua.err'.
chown: /Users/openxs/dbs/5.1/var/77-52-63-22.dialup.umc.net.ua.err: Operation not permitted
100311 20:27:38 mysqld_safe Starting mysqld daemon with databases from /Users/openxs/dbs/5.1/var

77-52-57-53:5.1 openxs$ 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
Server version: 5.1.45-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from tmaria;
ERROR 1035 (HY000): Old key file for table 'tmaria'; repair it!
mysql> repair table tmaria;
+-------------+--------+----------+---------------------------------------------+
| Table       | Op     | Msg_type | Msg_text                                    |
+-------------+--------+----------+---------------------------------------------+
| test.tmaria | repair | Error    | Old key file for table 'tmaria'; repair it! |
| test.tmaria | repair | error    | Corrupt                                     |
+-------------+--------+----------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from tmaria;
ERROR 1035 (HY000): Old key file for table 'tmaria'; repair it!
mysql> exit
Bye
77-52-57-53:5.1 openxs$ bin/myisamchk -vvv var/test/tmaria.MYImyisamchk: error: 'var/test/tmaria.MYI' is an old type of MyISAM-table

I do not see this problem with MyISAM table created by recent mysql-6.0-codebase though.

Setting as unsupported as the use case for this situation is not clear.

Reasons to implement this patch:
- To ensure that MySQL 5.x can open MyISAM tables created with user who tried out MySQL 6.0
- To ensure that MySQL 5.x can open MyISAM tables created by other branches, like MariaDB or Drizzle.
- To show that Oracle is standing by it's commitments to work together with the MySQL communities.

If this patch is not implemented, it means that MySQL users can easily move to MariaDB but MariaDB users can't move to MySQL, which is not good for the MySQL ecosystem.