Bug #19488 mysqldump fails to restore database after downgrading from 5.1 to 5.0
Submitted: 2 May 2006 19:21 Modified: 7 Jul 2006 20:52
Reporter: Nikolay Grishakin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S2 (Serious)
Version:5.1 OS:Linux (Linux)
Assigned to: Sergey Glukhov CPU Architecture:Any

[2 May 2006 19:21] Nikolay Grishakin
Description:
mysqldump fails to restore database after downgrading from 5.1 to 5.0.
Steps to reproduce:
1. I installed two nodes cluster in 5.1. 
2. I created and populated TPCB database in 5.1:
 perl load_tpcb.pl -so -e ndb 
3. took mysql dump: mysqldump -u root TPCB > tpcb_backup.sql 
4 Installed 5.0 
Tried to restore TPCB database with $ mysql TPCB -u root < tpcb_backup.sql 
and got the following error: “ERROR 1064 (42000) at line 23: You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 'PARTITION BY KEY ()' at line 7”

mysqldump 5.1 creates the following SQL statment for each CREATE TABLE:

CREATE TABLE `account` (
  `aid` int(11) NOT NULL DEFAULT '0',
  `bid` int(11) DEFAULT NULL,
  `balance` decimal(8,2) DEFAULT NULL,
  `filler` char(80) DEFAULT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY () ;

MySQL 5.0 does not understand partitions and error is being returned.

mysqldump is supposed to label segments of sql statements with version strings like '/* 5120 <5.1.20 statement> */', so these segments are ignored when the dump file is loaded using the older version of mysql. mysqldump should create table in old way and use ALTER TABLE with appropriate labels to add "PARTITION BY KEY ()" parameter.

Now it's impossible to use this back up method for downgrading.

Test was conducted on ndb15 machine. All scripts can be found under ndb15:/home/ndbdev/ngrishakin

How to repeat:
see above.
[25 May 2006 10:43] 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/6866
[2 Jun 2006 12:15] Alexander Barkov
The patch looks ok to push.
[7 Jun 2006 11:57] 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/7350
[22 Jun 2006 15:01] Sergey Glukhov
fixed in  5.1.12
[7 Jul 2006 20:52] Mike Hillyer
Documented in 5.1.12 changelog.