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

[31 Jul 2006 22:23] Jonathan Miller
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)
[9 Aug 2006 14:00] Martin Skold
Will add a check that local tables are not allowed to be
overwritten by autodiscover.
[12 Sep 2006 14:05] 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/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
[12 Sep 2006 14:34] 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/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
[13 Sep 2006 11:41] 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/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
[13 Sep 2006 16:49] 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/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
[13 Sep 2006 17:56] 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/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
[18 Sep 2006 11:24] 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/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
[1 Nov 2006 14:27] Jonas Oreland
pushed into 4.1.22
[1 Nov 2006 14:42] Jonas Oreland
pushed into 5.0.29
[2 Nov 2006 8:16] Jon Stephens
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!
[2 Nov 2006 14:07] Martin Skold
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
[4 Nov 2006 3:57] Jon Stephens
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.
[19 Mar 2007 10:33] Sveta Smirnova
There is Bug #27247 which might be related.