MySQL Bugs: #6762: ALTER TABLE gives weird results and error message

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	CPU Architecture:	Any

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.

verified on linux in 4.1.10 src tree
(originally reported on MacOS)

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.

Added test file for mysql-test to reproduce problem

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

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

Pushed to 4.1.11 and 5.0.5
It should not be that weird any more.

Noted in 4.1.11, 5.0.5 changelogs.