Bug #6762 ALTER TABLE gives weird results and error message
Submitted: 23 Nov 2004 0:30 Modified: 12 Apr 2005 2:28
Reporter: Harrison Fisk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:4.1.7 OS:Any (all)
Assigned to: Magnus Blåudd

[23 Nov 2004 0:30] Harrison Fisk
Description:
When a table exists in the cluster and you alter it thru one mysqld, the other mysqlds get a few weird responses before it starts working OK.

The mysqlds will eventually get the correct info, but it takes two queries for it to work.

How to repeat:
-- create our cluster table
mysql_one> create table t1 (a int) type=ndb;
Query OK, 0 rows affected, 1 warning (0.36 sec)

-- this works fine, discovery works good
mysql_two> SELECT * FROM t1;
Empty set (0.06 sec)

-- change the table structure
mysql_one> ALTER TABLE t1 add b int;
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- try to select from ALTER'd table on other mysqld, gives false error
mysql_two> SELECT * FROM t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

-- try again on same mysqld
mysql_two> SELECT * FROM t1;
Empty set (0.24 sec)

-- works, but we get a message output by mysqld, which it shouldn't do
041122 19:25:40 [ERROR] mysqld: Can't open file: 't1.ndb' (errno: 3)

Suggested fix:
Have mysqld detect the ALTER'd table cleanly without giving wrong error messages to both the user and error log.
[9 Feb 2005 12:34] Tomas Ulin
verified on linux in 4.1.10 src tree
(originally reported on MacOS)
[10 Feb 2005 15:09] Magnus Blåudd
I believe this is by design, the first query against a table that is already open in mysqld 2  AND has been changed from another mysqld will fail , but the error message on the second server should of course be different.

Ex, as it should be:
-- try to select from ALTER'd table on other mysqld, gives false error
mysql_two> SELECT * FROM t1;
ERROR 114? (42S02): Table definition for 'test.t1' has changed, please retry.

The reason why it  should be this way is because it's not until the query is executed against NDB that the error condition is detected. Then it's too late to retry, since the information about what should be retried is lost, maybe not in this simple case, but most likely in a more advanced query.

Suggested solution is to set a better error message in 4.1(but I'm not sure it's allowed to add error meassages in 4.1 anymore?, although a hardcoded one should always be possible). In 5.0 maybe we could think of a more proactive solution, like sending a signal that all open 't1' tables has to be flushed.

A workaround should be to perform a FLUSH TABLES on all servers, but that does not seem to work at the moment.
[10 Feb 2005 15:13] Magnus Blåudd
Added test file for mysql-test to reproduce problem
[4 Apr 2005 10:30] 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/internals/23613
[7 Apr 2005 18:29] 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/internals/23772
[7 Apr 2005 19:41] Magnus Blåudd
Pushed to 4.1.11 and 5.0.5
It should not be that weird any more.
[12 Apr 2005 2:28] Paul Dubois
Noted in 4.1.11, 5.0.5 changelogs.