Bug #55408 Planning to upgrade to 5.1.x from 4.1.24
Submitted: 20 Jul 2010 16:36 Modified: 20 Jul 2010 17:32
Reporter: Kevin A Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:4.1.24 OS:Linux (CentOS)
Assigned to: CPU Architecture:Any
Tags: 4.1, 5.1, innodb, myisam, mysqldump, upgrade

[20 Jul 2010 16:36] Kevin A
Description:
We are planning to upgrade MySQL from our current version 4.1.24 to 5.1.x
From the documentation that I have read it seems the recommended way of doing this is to dump all our data and reload after we upgrade to 5.1

We have a few MyISAM tables that are about 60 GB each and hence will probably take a very long time to do the whole dump and reload DB process.
(Our Data Directory is almost 900 GB). Being in an industry where DB downtime is not an option I would like to know my options of upgrading if even possible.
(FYI, We use a mix of InnoDB and MyISAM tables)

How to repeat:
On a side note, I was trying to check how our current database would work with MySQL 5.1 without a dump and reload. I created a table under 4.1

CREATE TABLE `test1` (
  `id` int(11) NOT NULL auto_increment,
  `val` int(11) default NULL,
  `data` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `val_data_idx` (`val`,`data`),
  KEY `id_val_idx` (`id`,`val`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 

Added a few rows,

insert into test1 values (null, 100, 'row1'), (null, 200, 'row2'), (null, 300, 'row3');

Stopped 4.1. Start MySQL 5.1 pointing to the same datadir as 4.1. 
Started a 5.1 client and ran the following:

mysql> select * from test1;
+----+------+------+
| id | val  | data |
+----+------+------+
|  1 |  700 | row1 |
|  2 |  200 | row2 |
|  3 |  300 | row3 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> check table test1;
+-------------------------+-------+----------+------------------------------------------------------------------------------------+
| Table                   | Op    | Msg_type | Msg_text                                                                           |
+-------------------------+-------+----------+------------------------------------------------------------------------------------+
| mysql_myisam_test.test1 | check | error    | Table upgrade required. Please do "REPAIR TABLE `test1`" or dump/reload to fix it! |
+-------------------------+-------+----------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test1 values (null, 400, 'row1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+----+------+------+
| id | val  | data |
+----+------+------+
|  1 |  700 | row1 |
|  2 |  200 | row2 |
|  3 |  300 | row3 |
|  4 |  400 | row1 |
+----+------+------+
4 rows in set (0.00 sec)

What I don't understand is, if I can still select/insert data into that table and seems to behave like it does with 4.1, then why 
do I need to dump and reload the data?  And if I need to do that what are my options for big tables. 

Suggested fix:
None I could think of.
[20 Jul 2010 16:39] Valeriy Kravchuk
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.
[20 Jul 2010 17:32] Kevin A
Can you please explain why this isn't a bug?
 Check table reports that the table needs to be repaired but I can still use it.  At the very least the error message could be more descriptive.