MySQL Bugs: #24363: ndb_restore with schema changes results in wrong data

Bug #24363	ndb_restore with schema changes results in wrong data
Submitted:	16 Nov 2006 14:29	Modified:	24 Mar 2007 2:30
Reporter:	Geert Vanderkelen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.1bk	OS:	Linux (Linux)
Assigned to:	Guangbao Ni	CPU Architecture:	Any
Tags:	Backup, ndb, restore

Description:
ndb_restore doesn't take into account any schema changes resulting in incorrect data in the table. When schema stored in backup is not same as schema currently loaded in Cluster, it should fail with an error or adapt to the new size.

How to repeat:
Using current 5.1bk:
Configuration of cluster is not important, following is done a cluster with only one data node.

mysql> create table t1 (id int, txt binary(32)) engine=ndb;
mysql> insert into t1 values (1,'Geert'),(2,'Jan'),(3,'Michel');
mysql> select * from t1;
+------+----------------------------------+
| id   | txt                              |
+------+----------------------------------+
|    1 | Geert                            | 
|    3 | Michel                           | 
|    2 | Jan                              | 
+------+----------------------------------+

ndb_mgm> start backup

mysql> truncate t1;

shell> ndb_restore -n 3 -b 1 -r

mysql> select * from t1;
+------+------------------------------------------------------------------+
| id   | txt                                                              |
+------+------------------------------------------------------------------+
|    1 | Geert                                                       | 
|    3 | Michel                                                      | 
|    2 | Jan                                                         | 
+------+------------------------------------------------------------------+

Suggested fix:
- short term: let ndb_restore exit with an error when schema doesn't match backup
- long term: adapt the tuples stored in the backup to the new schema (see also bug 22240)

Correction on How to reproduce:

After the TRUNCATE t1 you need to alter the table:

mysql> TRUNCATE t1;
mysql> ALTER TABLE t1 MODIFY COLUMN txt BINARY(64);

and then to the ndb_restore

I couldn't reproduce it.

with a CHAR column it becomes more obvious

> create table t1 (id int, txt char(32)) engine=ndb;
> insert into t1 values (1,'Geert'),(2,'Jan'),(3,'Michel');
> select id, concat('#',txt,'#'), length(trim(txt)) from t1;

+------+---------------------+-------------------+
| id   | concat('#',txt,'#') | length(trim(txt)) |
+------+---------------------+-------------------+
|    3 | #Michel#            |                 6 | 
|    2 | #Jan#               |                 3 | 
|    1 | #Geert#             |                 5 | 
+------+---------------------+-------------------+

/* now do the backup */

> TRUNCATE TABLE t1;
> ALTER TABLE t1 MODIFY COLUMN txt char(64);

/* and now ndb_restore the row data only, not the meta data,
   and do it on the live nodes, do not restart --initial */

> select id, concat('#',txt,'#'), length(trim(txt)) from t1;
+------+--------------------------------------------------------------------+-------------------+
| id   | concat('#',txt,'#')                                                | length(trim(txt)) |
+------+--------------------------------------------------------------------+-------------------+
|    1 | #Geert                                                     # |                64 | 
|    3 | #Michel                                                     # |                64 | 
|    2 | #Jan                                                        # |                64 | 
+------+--------------------------------------------------------------------+-------------------+

Yes, restoring row data only, not meta data is wrong,
not restarting --initial before restoring is wrong, too,
but ndb_restore should detect and *tell* that something
is wrong, it should not report success in this case ...

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/17654

ChangeSet@1.2343, 2007-01-05 16:55:34+08:00, gni@dev3-221.dev.cn.tlan +8 -0
  BUG#24363 If the table schema has changed, the resoring will fail.

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/18824

ChangeSet@1.2343, 2007-01-26 11:04:40+08:00, gni@dev3-221.dev.cn.tlan +4 -0
  BUG#24363 If the table structure has been changed, the restoring will fail.

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/18825

ChangeSet@1.2343, 2007-01-26 11:03:29+08:00, gni@dev3-221.dev.cn.tlan +5 -0
  BUG#24363 If the table structure has been changed, the restoring will fail.

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/19315

ChangeSet@1.2343, 2007-02-05 16:48:08+08:00, gni@dev3-221.dev.cn.tlan +4 -0
  BUG#24363 If the table structure has been changed, the default action about the restore will fail. 
            You can skip the table structure check with --skip-table-check (-s) option.

please check that the --print options still work (i.e. the consumer_printer) - think you may be missing a method for it.

Hi stewart,
   It is ok with -print option, for the table_equal() method has been implemented by the base class. When the child classed (i.e. the consumer_printer) don't implement it, it will use the base class implementation.
  Thank you!

Hi Tomas,
   This bug needn't be fixed in 5.0.

ahh... okay, I'm obviously blind then (i now see it).

Looks good.

should also get justin to review and tick the checkbox when done.

Approved by stewart and justin

push into mysql-5.1-new-ndb-bj

pushed to mysql-5.1-new-ndb, 5.1.17

Fixed in 5.1.17

Tomas, Geert: Request for info to you in email.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.17 changelog; noted behaviour change and new ndb_restore option in 5.1 Manual.

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/27574

ChangeSet@1.2149, 2007-05-29 15:46:22+02:00, jonas@perch.ndb.mysql.com +4 -0
  ndb - bug#24363 - wl2325-5.0