MySQL Bugs: #46427: Live downgrade fails for 5.4->5.1

Bug #46427	Live downgrade fails for 5.4->5.1
Submitted:	28 Jul 2009 12:55	Modified:	3 Nov 2009 14:50
Reporter:	Nidhi Shrotriya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Linux (Ubuntu)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
As mentioned in how to repeat section.

How to repeat:
Start server with 5.4 version. Create data(some simple tables) ->creates datadir
Stop 5.4 server.
Start 5.1 server with the above datadir. 
Connect using mysql client
mysql> show create table test1;
ERROR 1035 (HY000): Old key file for table 'test1'; repair it!

In server log:
-------------------
090728 16:31:28 mysqld_safe Starting mysqld daemon with databases from /home/nidhi/mysql-builds/mysql-5.1/../mysql-azalea/data
090728 16:31:28 [Note] Plugin 'FEDERATED' is disabled.
/home/nidhi/mysql-builds/mysql-5.1/bin/mysqld: Old key file for table 'plugin'; repair it!
090728 16:31:28 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
090728 16:31:28 InnoDB: Started; log sequence number 0 46409
090728 16:31:28 [ERROR] Cannot open mysql.event
090728 16:31:28 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
090728 16:31:28 [Note] /home/nidhi/mysql-builds/mysql-5.1/bin/mysqld: ready for connections.
Version: '5.1.38' socket: '/tmp/mysql.sock' port: 3306 Source distribution

Btw, tried mysql_upgrade from the old version(I thought it to be used for upgrade only and not for downgrade):
-------------------------
nidhi@nidhi-laptop:~/mysql-builds/mysql-5.1$ bin/mysql_upgrade --user=root --socket=/tmp/mysql.sock
Looking for 'mysql' as: bin/mysql
Looking for 'mysqlcheck' as: bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--socket=/tmp/mysql.sock'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--socket=/tmp/mysql.sock'
mysql.backup_history OK
mysql.backup_progress OK
mysql.columns_priv OK
mysql.db OK
mysql.event
Error : Old key file for table 'event'; repair it!
error : Corrupt
mysql.func OK
mysql.general_log
Error : You can't use locks with log tables.
status : OK
mysql.help_category
Error : Old key file for table 'help_category'; repair it!
error : Corrupt
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index
Error : Old key file for table 'ndb_binlog_index'; repair it!
error : Corrupt
mysql.plugin
Error : Old key file for table 'plugin'; repair it!
error : Corrupt
mysql.proc
Error : Old key file for table 'proc'; repair it!
error : Corrupt
mysql.procs_priv OK
mysql.servers OK
mysql.slow_log
Error : You can't use locks with log tables.
status : OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK

mysql.time_zone_transition_type OK
mysql.user OK
nidhi.t1_todng
Error : Old key file for table 't1_todng'; repair it!
error : Corrupt
nidhi.t2_todng
Error : Old key file for table 't2_todng'; repair it!
error : Corrupt
nidhi.test1
Error : Old key file for table 'test1'; repair it!
error : Corrupt

Repairing tables
mysql.event
Error : Old key file for table 'event'; repair it!
error : Corrupt
mysql.help_category
Error : Old key file for table 'help_category'; repair it!
error : Corrupt
mysql.ndb_binlog_index
Error : Old key file for table 'ndb_binlog_index'; repair it!
error : Corrupt
mysql.plugin
Error : Old key file for table 'plugin'; repair it!
error : Corrupt
mysql.proc
Error : Old key file for table 'proc'; repair it!
error : Corrupt
nidhi.t1_todng
Error : Old key file for table 't1_todng'; repair it!
error : Corrupt
nidhi.t2_todng
Error : Old key file for table 't2_todng'; repair it!
error : Corrupt
nidhi.test1
Error : Old key file for table 'test1'; repair it!
error : Corrupt
Running 'mysql_fix_privilege_tables'...
ERROR 1035 (HY000) at line 439: Old key file for table 'proc'; repair it!
ERROR 1035 (HY000) at line 486: Old key file for table 'proc'; repair it!
ERROR 1035 (HY000) at line 488: Old key file for table 'proc'; repair it!
ERROR 1035 (HY000) at line 495: Old key file for table 'proc'; repair it!
ERROR 1035 (HY000) at line 498: Old key file for table 'proc'; repair it!
ERROR 1035 (HY000) at line 501: Old key file for table 'proc'; repair it!
ERROR 1035 (HY000) at line 504: Old key file for table 'proc'; repair it!
ERROR 1035 (HY000) at line 507: Old key file for table 'proc'; repair it!
ERROR 1035 (HY000) at line 510: Old key file for table 'proc'; repair it!
ERROR 1035 (HY000) at line 513: Old key file for table 'proc'; repair it!
ERROR 1035 (HY000) at line 515: Old key file for table 'proc'; repair it!
ERROR 1035 (HY000) at line 535: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 536: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 538: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 540: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 574: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 576: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 577: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 579: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 581: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 584: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 587: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 590: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 593: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 596: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 599: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 602: Old key file for table 'event'; repair it!
ERROR 1035 (HY000) at line 604: Old key file for table 'event'; repair it!
FATAL ERROR: Upgrade failed

Thank you for the report.

Verified as described.

Interesting thing.

In following scenario:

5.4>use test
5.4>show create table msyql.event;
5.4>copy-paste
5.4>create table event_mysql like mysql.event;

$ mv data/test/event* ../mysql-5.1/data/test/

5.1>check table event;
+------------+-------+----------+----------+
| Table      | Op    | Msg_type | Msg_text |
+------------+-------+----------+----------+
| test.event | check | status   | OK       | 
+------------+-------+----------+----------+
1 row in set (0.07 sec)

5.1>check table event_mysql;
+------------------+-------+----------+--------------------------------------------------+
| Table            | Op    | Msg_type | Msg_text                                         |
+------------------+-------+----------+--------------------------------------------------+
| test.event_mysql | check | Error    | Old key file for table 'event_mysql'; repair it! | 
| test.event_mysql | check | error    | Corrupt                                          | 
+------------------+-------+----------+--------------------------------------------------+
2 rows in set (0.06 sec)

Workaround: manually REPAIR TABLE xxx USE_FRM

I happen to be working on very related code so I'll fix this.

patch: http://lists.mysql.com/commits/79974
queued to mysql-azalea-bugfixing.
No downgrade testcase committed, but we verified by hand (Sveta and me) that it's solved.

Bug #46526 was marked as duplicate of this one.

This was 6.0-specific (but at some point 5.4 was 6.0-based, hence the bug's title). Fixed has been pushed in 6.0 long ago.