MySQL Bugs: #19488: mysqldump fails to restore database after downgrading from 5.1 to 5.0

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:	Sergei Glukhov	CPU Architecture:	Any

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.

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

The patch looks ok to push.

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

fixed in  5.1.12

Documented in 5.1.12 changelog.