| Bug #36055 | mysql_upgrade doesn't really 'upgrade' tables | ||
|---|---|---|---|
| Submitted: | 14 Apr 13:53 | Modified: | 22 May 17:56 |
| Reporter: | Victoria Reznichenko | ||
| Status: | Closed | ||
| Category: | Client | Severity: | S2 (Serious) |
| Version: | all | OS: | Any |
| Assigned to: | Gleb Shchepa | Target Version: | 5.0+ |
| Triage: | D2 (Serious) | ||
[5 May 21:42]
Bugs System
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.
[12 May 18:02]
Bugs System
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.
[12 May 20:41]
Alexey Botchkov
Ok to push.
[14 May 17:19]
Bugs System
Pushed into 5.0.62
[14 May 17:21]
Bugs System
Pushed into 5.1.25-rc
[20 May 18:51]
Paul DuBois
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.
[21 May 0:34]
Bugs System
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.
[22 May 11:51]
Bugs System
Pushed into 6.0.6-alpha
[22 May 17:56]
Paul DuBois
Noted in 6.0.6 changelog.
[18 Jun 12:05]
Bugs System
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
[18 Jun 12:09]
Bugs System
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
[18 Jun 12:22]
Bugs System
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
[10 Aug 20:55]
Bugs System
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
[28 Aug 13:00]
Bugs System
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
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.