MySQL Bugs: #36055: mysql_upgrade doesn't really 'upgrade' tables

Bug #36055	mysql_upgrade doesn't really 'upgrade' tables
Submitted:	14 Apr 2008 11:53	Modified:	22 May 2008 15:56
Reporter:	Victoria Reznichenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	all	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
If you use mysql_upgrade to check and fix tables for upgrade it doesn't re-create .frm file and doesn't produce any warning about this which makes further usage a bit dangerous as you can not use REPAIR TABLE .. USE_FRM after this because you have 'old' table version.

This is simple test:

Create table in 4.1:

mysql> create database xxx;
Query OK, 1 row affected (0.00 sec)

mysql> use xxx;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show table status like 't1';
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
| Name | Type   | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Create_options | Comment |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
| t1   | MyISAM | Fixed      |    2 |              5 |          10 |     21474836479 |         1024 |         0 |           NULL | 2008-04-11 07:07:40 | 2008-04-11 07:07:49 | NULL       |                |         |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
1 row in set (0.00 sec)

then move files to 5.0

mysql> show table status like 't1';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| t1   | MyISAM |       7 | Fixed      |    2 |              5 |          10 |     21474836479 |         1024 |         0 |           NULL | 2008-04-11 07:07:40 | 2008-04-11 07:08:58 | NULL       | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

Run mysql_upgrade

xxx.t1                                             OK
Running 'mysql_fix_privilege_tables'...
OK

mysql> show table status like 't1';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| t1   | MyISAM |       7 | Fixed      |    2 |              5 |          10 |     21474836479 |         1024 |         0 |           NULL | 2008-04-11 07:07:40 | 2008-04-11 07:08:58 | NULL       | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

table version is 7.

Let's try to use REPAIR TABLE .. USE_FRM now:

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)

mysql> repair table t1 use_frm;
+--------+--------+----------+------------------------------------+
| Table  | Op     | Msg_type | Msg_text                           |
+--------+--------+----------+------------------------------------+
| xxx.t1 | repair | warning  | Number of rows changed from 0 to 1 |
| xxx.t1 | repair | status   | OK                                 |
+--------+--------+----------+------------------------------------+
2 rows in set (0.01 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Yes, we have this behaviour documented for REPAIR TABLE:

"
Do not use USE_FRM if your table was created by a different version of the MySQL server than the one you are currently running. Doing so risks the loss of all rows in the table.
"

http://dev.mysql.com/doc/refman/5.0/en/repair-table.html

However if user use official tool for upgrade mysql_upgrade he/she should except it will fix everything or will warn about possible issues.

How to repeat:
1. create simple table in 4.1

create table t1(id int);
insert into t1 values(1),(2);

2. move table files to 5.0 and run mysql_upgrade
3. check table version
4. try to use REPAIR TABLE USE_FRM on it

Suggested fix:
Either fix mysql_upgrade or warn users that mysql_upgrade that it can not fix all issues.

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/46368

ChangeSet@1.2614, 2008-05-06 00:40:38+05:00, gshchepa@host.loc +2 -0
  Fixed bug #36055: mysql_upgrade doesn't really 'upgrade' tables
  
  The REPAIR TABLE ... USE_FRM query silently corrupts data of tables
  with old .FRM file version.
  The mysql_upgrade client program or the REPAIR TABLE query (without
  the USE_FRM clause) can't prevent this trouble, because in the
  common case they don't upgrade .FRM file to compatible structure.
  
  1. Evaluation of the REPAIR TABLE ... USE_FRM query has been
     modified to reject such tables with the message:
     "Failed repairing incompatible .FRM file".
  
  2. REPAIR TABLE query (without USE_FRM clause) evaluation has been
     modified to upgrade .FRM files to current version.
  
  3. CHECK TABLE ... FOR UPGRADE query evaluation has been modified
     to return error status when .FRM file has incompatible version.
  
  4. mysql_upgrade and mysqlcheck client programs call CHECK TABLE
     FOR UPGRADE and REPAIR TABLE queries, so their behaviors have
     been changed too to upgrade .FRM files with incompatible
     version numbers.

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/46629

ChangeSet@1.2614, 2008-05-12 21:01:13+05:00, gshchepa@host.loc +7 -0
  Fixed bug #36055: mysql_upgrade doesn't really 'upgrade' tables
  
  The REPAIR TABLE ... USE_FRM query silently corrupts data of tables
  with old .FRM file version.
  The mysql_upgrade client program or the REPAIR TABLE query (without
  the USE_FRM clause) can't prevent this trouble, because in the
  common case they don't upgrade .FRM file to compatible structure.
  
  1. Evaluation of the REPAIR TABLE ... USE_FRM query has been
     modified to reject such tables with the message:
     "Failed repairing incompatible .FRM file".
  
  2. REPAIR TABLE query (without USE_FRM clause) evaluation has been
     modified to upgrade .FRM files to current version.
  
  3. CHECK TABLE ... FOR UPGRADE query evaluation has been modified
     to return error status when .FRM file has incompatible version.
  
  4. mysql_upgrade and mysqlcheck client programs call CHECK TABLE
     FOR UPGRADE and REPAIR TABLE queries, so their behaviors have
     been changed too to upgrade .FRM files with incompatible
     version numbers.

Ok to push.

Pushed into 5.0.62

Pushed into 5.1.25-rc

Noted in 5.0.62, 5.1.25 changelogs.

Some changes were made to CHECK TABLE ... FOR UPGRADE and REPAIR
TABLE with respect to detection and handling of tables with
incompatible .frm files (files created with a different version of
the MySQL server). These changes also affect mysqlcheck because that
program uses CHECK TABLE and REPAIR table, and thus also
mysql_upgrade because that program invokes mysqlcheck.
      
- If your table was created by a different version of the MySQL server
than the one you are currently running, CHECK TABLE ... FOR UPGRADE
indicates that the table has an .frm file with an incompatible
version. In this case, the result set returned by CHECK TABLE
contains a line with a Msg_type value of error and a Msg_text value
of Table upgrade required. Please do "REPAIR TABLE `tbl_name`" to fix
it!     
      
- REPAIR TABLE without USE_FRM upgrades the .frm file to the current
version. 
        
- If you use REPAIR TABLE ...USE_FRM and your table was created by a
different version of the MySQL server than the one you are currently
running, REPAIR TABLE will not attempt to repair the table. In this
case, the result set returned by REPAIR TABLE contains a line with a
Msg_type value of error and a Msg_text value of Failed reparing
incompatible .FRM file.

Previously, use of REPAIR TABLE ...USE_FRM with a table created by a 
different version of the MySQL server risked the loss of all rows in
the table.  

Setting report to Need Doc Info pending push into 6.0.x.

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/46899

ChangeSet@1.2632, 2008-05-20 23:49:52+02:00, gshchepa@devsrv-b.mysql.com +2 -0
  repair.result, sql_table.cc:
    Bug#36055: minor post-commit fix of typo in error message text.

Pushed into 6.0.6-alpha

Noted in 6.0.6 changelog.

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/48060

2656 Sven Sandberg	2008-06-12 [merge]
      Merge of the following in 6.0-rpl: pull from main and bug number 37200

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/48061

2656 Sven Sandberg	2008-06-12 [merge]
      Merge of the following in 6.0-rpl: pull from main and bug number 37200

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/48064

2656 Sven Sandberg	2008-06-12 [merge]
      Merge of the following in 6.0-rpl: pull from main and bug number 37200

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/51259

2656 jonas@mysql.com	2008-08-10 [merge]
      merge

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/52829