Bug #18094 Slave caches invalid table definition after atlters causes select failure
Submitted: 9 Mar 2006 3:17 Modified: 23 Apr 2006 0:28
Reporter: Jonathan Miller Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S1 (Critical)
Version:5.1.8 OS:Linux (Linux)
Assigned to: Tomas Ulin CPU Architecture:Any

[9 Mar 2006 3:17] Jonathan Miller
Description:
TEST                            RESULT
-------------------------------------------------------
rpl_ndb_1                      [ fail ]

Errors are (from /home/ndbdev/jmiller/clones/mysql-5.1-new/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 122: query 'SELECT * FROM t1 ORDER BY c1 LIMIT 5' failed: 1412: Table definition has changed, please retry transaction
(the last lines may be the most important ones)

Aborting: rpl_ndb_1 failed in default mode. To continue, re-run with '--force'.

I have tried to break this one down as much as I can. Seems to need Disk Data and more of the other ALTERS to cause it to fail.

One interesting thing I did find is that if I insert blob data before the select the select will pass without issues.

How to repeat:
#### Include Section ####
--source include/have_ndb.inc
--source include/have_binlog_format_row.inc
--source include/master-slave.inc

#### Test start cleanup section #####
--disable_warnings
DROP TABLE IF EXISTS t1, t2, t3;
--enable_warnings

CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
INITIAL_SIZE 16M
UNDO_BUFFER_SIZE = 1M
ENGINE=NDB;

ALTER LOGFILE GROUP lg1
ADD UNDOFILE 'undofile02.dat'
INITIAL_SIZE 4M
ENGINE=NDB;

CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 12M
ENGINE=NDB;

ALTER TABLESPACE ts1
ADD DATAFILE 'datafile02.dat'
INITIAL_SIZE 4M
ENGINE=NDB;

CREATE TABLE t1
  (c1 INT NOT NULL PRIMARY KEY,
   c2 INT NOT NULL,
   c3 INT NOT NULL)
   TABLESPACE ts1 STORAGE DISK
   ENGINE=NDB;

--echo ***** insert some data *****

let $j= 900;
--disable_query_log
while ($j)
{
  eval INSERT INTO t1 VALUES($j,$j*2,$j+3);
  dec $j;
}
--enable_query_log

--echo ***** Select from Master *****

SELECT * FROM t1 ORDER BY c1 LIMIT 5;

--echo ***** Select from Slave *****
--sync_slave_with_master
connection slave;
SELECT * FROM t1 ORDER BY c1 LIMIT 5;

--disable_query_log
SELECT FILE_NAME, FILE_TYPE, TABLESPACE_NAME, LOGFILE_GROUP_NAME
       FROM INFORMATION_SCHEMA.FILES
       WHERE ENGINE="ndbcluster";
--enable_query_log

--echo **** Do First Set of ALTERs in the master table ****
connection master;
CREATE INDEX t1_i ON t1(c2, c3);
ALTER TABLE t1 ADD c4 TIMESTAMP;
ALTER TABLE t1 ADD c5 DOUBLE;
ALTER TABLE t1 ADD INDEX (c5);
SHOW CREATE TABLE t1;

--echo **** Show first set of ALTERs on SLAVE ****
--sync_slave_with_master
connection slave;
SHOW CREATE TABLE t1;

#--echo **** Second set of alters test 1 ****

#connection master;
#ALTER TABLE t1 RENAME t2;
#ALTER TABLE t2 DROP INDEX c5;
#CREATE TABLE t1(c1 INT)ENGINE=NDB;
#INSERT INTO t1 VALUES(1);
#DROP TABLE t1;
#ALTER TABLE t2 RENAME t1;

#--echo **** Show second set of ALTERs on MASTER ****

#SHOW CREATE TABLE t1;

#--echo **** Show second set of ALTERs on SLAVE ****
#--sync_slave_with_master
#connection slave;
#SHOW CREATE TABLE t1;

--echo **** Third and last set of alters for test1  ****
connection master;
ALTER TABLE t1 CHANGE c1 c1 DOUBLE;
ALTER TABLE t1 CHANGE c2 c2 DECIMAL(10,2);
ALTER TABLE t1 DROP COLUMN c3;
ALTER TABLE t1 CHANGE c4 c4 TEXT CHARACTER SET utf8;
ALTER TABLE t1 CHANGE c4 c4 BLOB;
ALTER TABLE t1 CHANGE c4 c3 BLOB;
#set @b1 = 'b1';
#set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
#UPDATE t1 SET c3=@b1 where c1 = 1;
#UPDATE t1 SET c3=@b1 where c1 = 2;

--echo **** Show last set of ALTERs on MASTER ****

SHOW CREATE TABLE t1;
SELECT * FROM t1 ORDER BY c1 LIMIT 5;

--echo **** Show last set of ALTERs on SLAVE ****
--sync_slave_with_master
connection slave;
SHOW CREATE TABLE t1;
# Bug 
SELECT * FROM t1 ORDER BY c1 LIMIT 5;
SELECT * FROM t1 where c1 = 1;

connection master;
DROP TABLE t1;
--sync_slave_with_master
connection slave;
[30 Mar 2006 12:52] Tomas Ulin
simpler test case, the LIMIT in the end _matters_:

#### Include Section ####
--source include/have_ndb.inc
--source include/have_binlog_format_row.inc
--source include/master-slave.inc

#### Test start cleanup section #####
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (c1 INT KEY) ENGINE=NDB;

INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

ALTER TABLE t1 ADD c2 INT;

--sync_slave_with_master
connection slave;
SELECT * FROM t1 ORDER BY c1;

connection master;
ALTER TABLE t1 CHANGE c2 c2 TEXT CHARACTER SET utf8;
ALTER TABLE t1 CHANGE c2 c2 BLOB;

--sync_slave_with_master
connection slave;
SELECT * FROM t1 ORDER BY c1 LIMIT 5;

connection master;
DROP TABLE t1;
--sync_slave_with_master
connection slave;
[13 Apr 2006 13:05] Tomas Ulin
it is the ordered index used in the LIMIT query that has the wrong version...
[21 Apr 2006 7:36] 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/5259
[21 Apr 2006 13:02] Tomas Ulin
reviewed by martin and jonas
[21 Apr 2006 14:45] Tomas Ulin
pushed to 5.1.10
[23 Apr 2006 0:28] Jon Stephens
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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.1.10 changelog. Closed.