MySQL Bugs: #21378: Alter table from X storage engine to NDB could cause data loss

Bug #21378	Alter table from X storage engine to NDB could cause data loss
Submitted:	31 Jul 2006 22:23	Modified:	4 Nov 2006 3:57
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	4.1, 5.0	OS:	Linux (Linux)
Assigned to:	Martin Skold	CPU Architecture:	Any

Description:
Hi,

This was found by one of our customer.

The problem is that a customer could have a database "foo" on more then one MYSQLD process that is connected to a MySQL Cluster. In side "foo" there could be a myisam table called "t1" that holds data different then the "t1" on the other mysqld. If you alter t1 on one of the mysqld servers to use the NDB engine, the other table's data is overwritten by the first.

How to repeat:
mysql#1> create database foo;
mysql#1> create table t1(c1 int key)ENGINE=MyISAM;
mysql#1> insert into t1 values(1),(3),(5);
mysql#1> select * from t1;
+----+
| c1 |
+----+
|  1 |
|  3 |
|  5 |
+----+
3 rows in set (0.00 sec)

mysql#2> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TESTER             |
| cluster            |
| mysql              |
| foo                |
| test               |
+--------------------+

mysql#2> use foo;
mysql#2> create table t1(c1 int key)ENGINE=MyISAM;
mysql#2> insert into t1 vaules(100),(344),(533);
mysql#2> select * from t1;
+-----+
| c1  |
+-----+
| 100 |
| 344 |
| 533 |
+-----+
3 rows in set (0.00 sec)

mysql#1> alter table t1 engine=ndb;

mysql#2> select * from t1;
+----+
| c1 |
+----+
|  1 |
|  3 |
|  5 |
+----+
3 rows in set (0.00 sec)

Will add a check that local tables are not allowed to be
overwritten by autodiscover.

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

ChangeSet@1.2540, 2006-09-12 16:04:52+02:00, mskold@mysql.com +3 -0
  Bug #21378  Alter table from X storage engine to NDB could cause data loss: Added warning if local table shadows ndb table

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

ChangeSet@1.2310, 2006-09-12 16:34:12+02:00, mskold@mysql.com +2 -0
  Bug #21378  Alter table from X storage engine to NDB could cause data loss: don't overwrite local tables when pushing out schema changes

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

ChangeSet@1.2315, 2006-09-13 13:41:05+02:00, mskold@mysql.com +1 -0
  Bug #21378  Alter table from X storage engine to NDB could cause data loss: post review fix

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

ChangeSet@1.2541, 2006-09-13 18:49:05+02:00, mskold@mysql.com +1 -0
  Bug #21378  Alter table from X storage engine to NDB could cause data loss: skip autodiscover of local tables

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

ChangeSet@1.2542, 2006-09-13 19:56:02+02:00, mskold@mysql.com +1 -0
  Bug #21378  Alter table from X storage engine to NDB could cause data loss: updated results

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

ChangeSet@1.2321, 2006-09-18 13:00:21+02:00, mskold@mysql.com +2 -0
  Bug #21378  Alter table from X storage engine to NDB could cause data loss: added show tables to ensure tables are gone

pushed into 4.1.22

pushed into 5.0.29

Okay, I see a bunch of fixes here with differing descriptions of how this will be handled.

If I've a table foo on server A and another with the same name on server B, and I issue ALTER TABLE foo ENGINE=NDB on Server A, exactly what happens to the foo table on Server B as a result of the (final) fix?

Thanks!

The fix genarates a warning to user if a local table is found
during autodiscovery, i.e. when issuing SHOW TABLES (in 4.1 and upward).
In 5.1, schema changes are pushed out to all connected mysqld's,
if a shadowing local table is found during this operation a warning
is written in the log file of the mysqld process.

-- Martin

Hi Martin,

Thanks for the info. I have documented this as follows.

For 4.1.22/5.0.29:

        
        <para>
          <literal>NDB Cluster</literal>: Attempting to create an
          <literal>NDB</literal> table on a MySQL with an existing
          non-Cluster table with the same name in the same database
          could result in data loss or corruption. MySQL now issues a
          warning when a <literal>SHOW TABLES</literal> or other
          statement causing table discovery finds such a table. (Bug
          #21378)
        </para>

*Note: The 5.0 fix is documented in the 5.0.30 Release Notes.*

For 5.1.12:

        <para>
          <literal>NDB Cluster</literal>: Attempting to create an
          <literal>NDB</literal> table on a MySQL with an existing
          non-Cluster table with the same name in the same database
          could result in data loss or corruption. Now, if such a table
          is encountered during autodiscovery, a warning is written to
          the error log of the affected <command>mysqld</command>, and
          the local table is overwritten. (Bug #21378)
        </para>

Please advise immediately if any of the above is incorrect.

There is Bug #27247 which might be related.