Bug #48633 archive tables are not upgradeable
Submitted: 9 Nov 2009 9:45 Modified: 16 Oct 2011 23:03
Reporter: Anurag Shekhar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Archive storage engine Severity:S1 (Critical)
Version:5.1+ OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R4 (High) / E3 (Medium)

[9 Nov 2009 9:45] Anurag Shekhar
Description:
Archive table created in 5.0 is not upgradeable to 5.1.

How to repeat:
Copy a 5.0 archive table to 5.1 and try repair on it.
Accessing 5.0 table in 5.1 can result in server crash unless patch
for bug#47012 is applied.

Suggested fix:
Proposed patch for bug#47012 blocks the open for repair of 5.0 archive table. This can be allowed after adequate testing.
[9 Nov 2009 12:07] Miguel Solorzano
Thank you for the bug report. I couldn't repeat then I assume the patch was already applied?:

miguel@quetzal:~/dbs/5.1$ bin/mysql -uroot d1
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.42-debug Source distribution         

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

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(255) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> check table t1 extended;
+-------+-------+----------+----------+
| Table | Op    | Msg_type | Msg_text |
+-------+-------+----------+----------+
| d1.t1 | check | status   | OK       |
+-------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> repair table t1 extended;
+-------+--------+----------+----------+
| Table | Op     | Msg_type | Msg_text |
+-------+--------+----------+----------+
| d1.t1 | repair | status   | OK       |
+-------+--------+----------+----------+
1 row in set (0.00 sec)

mysql>
[11 Nov 2009 10:24] Mattias Jonsson
To verify this bug, you have to create the table in 5.0, and then do a REPAIR in 5.1+.
[11 Nov 2009 15:29] Miguel Solorzano
I created the table on 5.0 and then copy/paste on 5.1 according instruction in bug: http://bugs.mysql.com/bug.php?id=47012
[17 Nov 2009 10:27] Anurag Shekhar
can you please try starting mysql with -A option ?
[17 Nov 2009 18:29] Miguel Solorzano
Verified according with Anurag's IRC instructions:

miguel@quetzal3:~/dbs$ 5.1/bin/mysql -uroot d2
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.42-debug Source distribution

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

mysql> show create table t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[6 Mar 2010 13:26] Sergey Vojtovich
R4/E3 - rather untested code, high risk to break a table after upgrade.
[25 May 2010 10:08] Shane Bester
it is unclear from the bug report, so an example is needed:

1. create any archive tables in 5.0 (e.g 5.0.91)
2. now upgrade to 5.1 (any version where bug #47012 is fixed, e.g 5.1.47)
3. 'select * from t1' or any access gives this error:

mysql> select * from t1;
ERROR 1459 (HY000): Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it!

there is no way to repair/fix this in 5.1.  so, make sure to dump/reload archive tables between 5.0 and 5.1
[18 Mar 2011 10:09] Valeriy Kravchuk
Bug #60502 was marked as a duplicate of this one.
[16 Oct 2011 23:03] Paul Dubois
Noted in 5.6.4 changelog.

Previously, MySQL servers from 5.1 and up refused to open ARCHIVE
tables created in 5.0 because opening them caused a server crash. The
server now can open 5.0 ARCHIVE tables, and REPAIR TABLE updates them
to the format used in 5.6. However, the recommended upgrade procedure
is still to dump 5.0 ARCHIVE tables before upgrading and reload them
after upgrading.