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 21:21	Modified:	7 Jul 2006 22:52
Reporter:	Nikolay Grishakin
Status:	Closed
Category:	Server: Backup	Severity:	S2 (Serious)
Version:	5.1	OS:	Linux (Linux)
Assigned to:	Sergey Gluhov	Target Version:

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.