diff options
Diffstat (limited to 'mysql-test/suite/innodb/t')
38 files changed, 4260 insertions, 310 deletions
diff --git a/mysql-test/suite/innodb/t/innodb-autoinc-18274.test b/mysql-test/suite/innodb/t/innodb-autoinc-18274.test new file mode 100644 index 00000000000..de9f3e3d18b --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-autoinc-18274.test @@ -0,0 +1,26 @@ +-- source include/have_innodb.inc +# embedded server ignores 'delayed', so skip this +-- source include/not_embedded.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# Bug #18274 InnoDB auto_increment field reset on OPTIMIZE TABLE +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (null); +SHOW CREATE TABLE t1; +DELETE FROM t1; +OPTIMIZE TABLE t1; +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES(null); +SELECT * FROM t1; +DROP TABLE t1; + +# +# restore environment to the state it was before this test execution +# + +-- disable_query_log diff --git a/mysql-test/suite/innodb/t/innodb-autoinc-56228-master.opt b/mysql-test/suite/innodb/t/innodb-autoinc-56228-master.opt new file mode 100644 index 00000000000..0eed7aaadad --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-autoinc-56228-master.opt @@ -0,0 +1 @@ +--innodb_autoinc_lock_mode=0 diff --git a/mysql-test/suite/innodb/t/innodb-autoinc-56228.test b/mysql-test/suite/innodb/t/innodb-autoinc-56228.test new file mode 100644 index 00000000000..626efcf9897 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-autoinc-56228.test @@ -0,0 +1,33 @@ +-- source include/have_innodb.inc + +## +# Bug #56228: dropping tables from within an active statement crashes server +# +DROP TABLE IF EXISTS t1_56228; +DROP TABLE IF EXISTS t2_56228; +DROP FUNCTION IF EXISTS bug56228; + +CREATE TEMPORARY TABLE t1_56228( + c1 iNT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +CREATE TEMPORARY TABLE t2_56228( + c1 iNT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; + +DELIMITER //; + +CREATE FUNCTION bug56228() RETURNS INT DETERMINISTIC +BEGIN + INSERT INTO t1_56228 VALUES(NULL); + INSERT INTO t2_56228 VALUES(NULL); + INSERT INTO t1_56228 VALUES(NULL); + INSERT INTO t2_56228 VALUES(NULL); + DROP TEMPORARY TABLE t1_56228; + RETURN 42; +END // + +DELIMITER ;// + +SELECT bug56228(); + +DROP FUNCTION bug56228; +DROP TEMPORARY TABLE t2_56228; +DROP TEMPORARY TABLE IF EXISTS t1_56228; diff --git a/mysql-test/suite/innodb/t/innodb-autoinc.test b/mysql-test/suite/innodb/t/innodb-autoinc.test index 84c359a6bde..4ca761b45ce 100644 --- a/mysql-test/suite/innodb/t/innodb-autoinc.test +++ b/mysql-test/suite/innodb/t/innodb-autoinc.test @@ -292,21 +292,8 @@ INSERT INTO t1 VALUES (18446744073709551603); #-- 2^64 - 13 SELECT * FROM t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10; SHOW VARIABLES LIKE "%auto_inc%"; -# This should fail because of overflow but it doesn't, it seems to be -# a MySQL server bug. It wraps around to 0 for the last value. -# See MySQL Bug# 39828 -# -# Instead of wrapping around, it asserts when MySQL is compiled --with-debug -# (see sql/handler.cc:handler::update_auto_increment()). Don't test for -# overflow until Bug #39828 is fixed. -# -# Since this asserts when compiled --with-debug, we can't properly test this -# until Bug #39828 is fixed. For now, this test is meaningless. -#if Bug #39828 is fixed -#INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); -#else -INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL); -#endif +--error ER_AUTOINC_READ_FAILED +INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); SELECT * FROM t1; DROP TABLE t1; @@ -324,20 +311,8 @@ INSERT INTO t1 VALUES (18446744073709551603); #-- 2^64 - 13 SELECT * FROM t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=5, @@SESSION.AUTO_INCREMENT_OFFSET=7; SHOW VARIABLES LIKE "%auto_inc%"; -# This should fail because of overflow but it doesn't. It fails with -# a duplicate entry message because of a MySQL server bug, it wraps -# around. See MySQL Bug# 39828, once MySQL fix the bug we can replace -# the ER_DUP_ENTRY, 1062 below with the appropriate error message -# -# Since this asserts when compiled --with-debug, we can't properly test this -# until Bug #39828 is fixed. For now, this test is meaningless. -#if Bug #39828 is fixed -# Still need to fix this error code, error should mention overflow -#-- error ER_DUP_ENTRY,1062 -#INSERT INTO t1 VALUES (NULL),(NULL), (NULL); -#else -INSERT INTO t1 VALUES (NULL),(NULL); -#endif +--error ER_AUTOINC_READ_FAILED +INSERT INTO t1 VALUES (NULL),(NULL), (NULL); SELECT * FROM t1; DROP TABLE t1; @@ -375,20 +350,8 @@ INSERT INTO t1 VALUES (18446744073709551610); #-- 2^64 - 2 SELECT * FROM t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976; SHOW VARIABLES LIKE "%auto_inc%"; -# This should fail because of overflow but it doesn't. It wraps around -# and the autoinc values look bogus too. -# See MySQL Bug# 39828, once MySQL fix the bug we can enable the error -# code expected test. -# -- error ER_AUTOINC_READ_FAILED,1467 -# -# Since this asserts when compiled --with-debug, we can't properly test this -# until Bug #39828 is fixed. For now, this test is meaningless. -#if Bug #39828 is fixed -#-- error ER_AUTOINC_READ_FAILED,1467 -#INSERT INTO t1 VALUES (NULL),(NULL); -#else -INSERT INTO t1 VALUES (NULL); -#endif +--error ER_WARN_DATA_OUT_OF_RANGE +INSERT INTO t1 VALUES (NULL),(NULL); SELECT * FROM t1; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb-create-options.test b/mysql-test/suite/innodb/t/innodb-create-options.test new file mode 100644 index 00000000000..3daa5f09e71 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-create-options.test @@ -0,0 +1,575 @@ +# Tests for various combinations of ROW_FORMAT and KEY_BLOCK_SIZE +# Related bugs; +# Bug#54679: ALTER TABLE causes compressed row_format to revert to compact +# Bug#56628: ALTER TABLE .. KEY_BLOCK_SIZE=0 produces untrue warning or unnecessary error +# Bug#56632: ALTER TABLE implicitly changes ROW_FORMAT to COMPRESSED +# Rules for interpreting CREATE_OPTIONS +# 1) Create options on an ALTER are added to the options on the +# previous CREATE or ALTER statements. +# 2) KEY_BLOCK_SIZE=0 is considered a unspecified value. +# If the current ROW_FORMAT has explicitly been set to COMPRESSED, +# InnoDB will use a default value of 8. Otherwise KEY_BLOCK_SIZE +# will not be used. +# 3) ROW_FORMAT=DEFAULT allows InnoDB to choose its own default, COMPACT. +# 4) ROW_FORMAT=DEFAULT and KEY_BLOCK_SIZE=0 can be used at any time to +# unset or erase the values persisted in the MySQL dictionary and +# by SHOW CTREATE TABLE. +# 5) When incompatible values for ROW_FORMAT and KEY_BLOCK_SIZE are +# both explicitly given, the ROW_FORMAT is always used in non-strict +# mode. +# 6) InnoDB will automatically convert a table to COMPRESSED only if a +# valid non-zero KEY_BLOCK_SIZE has been given and ROW_FORMAT=DEFAULT +# or has not been used on a previous CREATE TABLE or ALTER TABLE. +# 7) InnoDB strict mode is designed to prevent incompatible create +# options from being used together. +# 8) The non-strict behavior is intended to permit you to import a +# mysqldump file into a database that does not support compressed +# tables, even if the source database contained compressed tables. +# All invalid values and/or incompatible combinations of ROW_FORMAT +# and KEY_BLOCK_SIZE are automatically corrected +# +# *** innodb_strict_mode=ON *** +# 1) Valid ROW_FORMATs are COMPRESSED, COMPACT, DEFAULT, DYNAMIC +# & REDUNDANT. All others are rejected. +# 2) Valid KEY_BLOCK_SIZEs are 0,1,2,4,8,16. All others are rejected. +# 3) KEY_BLOCK_SIZE=0 can be used to set it to 'unspecified'. +# 4) KEY_BLOCK_SIZE=1,2,4,8 & 16 are incompatible with COMPACT, DYNAMIC & +# REDUNDANT. +# 5) KEY_BLOCK_SIZE=1,2,4,8 & 16 as well as ROW_FORMAT=COMPRESSED and +# ROW_FORMAT=DYNAMIC are incompatible with innodb_file_format=Antelope +# and innodb_file_per_table=OFF +# 6) KEY_BLOCK_SIZE on an ALTER must occur with ROW_FORMAT=COMPRESSED +# or ROW_FORMAT=DEFAULT if the ROW_FORMAT was previously specified +# as COMPACT, DYNAMIC or REDUNDANT. +# 7) KEY_BLOCK_SIZE on an ALTER can occur without a ROW_FORMAT if the +# previous ROW_FORMAT was DEFAULT, COMPRESSED, or unspecified. +# +# *** innodb_strict_mode=OFF *** +# 1. Ignore a bad KEY_BLOCK_SIZE, defaulting it to 8. +# 2. Ignore a bad ROW_FORMAT, defaulting to COMPACT. +# 3. Ignore a valid KEY_BLOCK_SIZE when an incompatible but valid +# ROW_FORMAT is specified. +# 4. If innodb_file_format=Antelope or innodb_file_per_table=OFF +# it will ignore ROW_FORMAT=COMPRESSED or DYNAMIC and it will +# ignore all non-zero KEY_BLOCK_SIZEs. +# +# See InnoDB documentation page "SQL Compression Syntax Warnings and Errors" + +-- source include/have_innodb.inc +SET storage_engine=InnoDB; + +--disable_query_log +# These values can change during the test +LET $innodb_file_format_orig=`select @@innodb_file_format`; +LET $innodb_file_format_max_orig=`select @@innodb_file_format_max`; +LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`; +LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`; +--enable_query_log + +SET GLOBAL innodb_file_format=`Barracuda`; +SET GLOBAL innodb_file_per_table=ON; + +# The first half of these tests are with strict mode ON. +SET SESSION innodb_strict_mode = ON; + +--echo # Test 1) StrictMode=ON, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 +--echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' +DROP TABLE IF EXISTS t1; +--echo # 'FIXED' is sent to InnoDB since it is used by MyISAM. +--echo # But it is an invalid mode in InnoDB +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; +SHOW WARNINGS; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + + + +--echo # Test 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE +--echo # KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC +DROP TABLE IF EXISTS t1; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + + +--echo # Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + + +--echo # Test 4) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid non-zero KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 KEY_BLOCK_SIZE=2; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 KEY_BLOCK_SIZE=4; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 KEY_BLOCK_SIZE=8; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + +--echo # Test 5) StrictMode=ON, CREATE with a valid KEY_BLOCK_SIZE +--echo # ALTER with each ROW_FORMAT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=2; +SHOW CREATE TABLE t1; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW CREATE TABLE t1; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=COMPACT; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=COMPACT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + +--echo # Test 6) StrictMode=ON, CREATE with an invalid KEY_BLOCK_SIZE. +DROP TABLE IF EXISTS t1; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9; +SHOW WARNINGS; + +--echo # Test 7) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and +--echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with Antelope +--echo # and that they can be set to default values during strict mode. +SET GLOBAL innodb_file_format=Antelope; +DROP TABLE IF EXISTS t1; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 KEY_BLOCK_SIZE=8; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +SET GLOBAL innodb_file_format=Barracuda; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +SET GLOBAL innodb_file_format=Antelope; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ADD COLUMN f1 INT; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +SET GLOBAL innodb_file_format=Barracuda; + +--echo # Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and +--echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with +--echo # innodb_file_per_table=OFF and that they can be set to default +--echo # values during strict mode. +SET GLOBAL innodb_file_per_table=OFF; +DROP TABLE IF EXISTS t1; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 KEY_BLOCK_SIZE=1; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=COMPACT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DEFAULT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_per_table=ON; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +SET GLOBAL innodb_file_per_table=OFF; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ADD COLUMN f1 INT; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +SET GLOBAL innodb_file_per_table=ON; + +--echo ################################################## +SET SESSION innodb_strict_mode = OFF; + +--echo # Test 9) StrictMode=OFF, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 +--echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' +--echo # 'FIXED' is sent to InnoDB since it is used by MyISAM. +--echo # It is an invalid mode in InnoDB, use COMPACT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + +--echo # Test 10) StrictMode=OFF, CREATE with each ROW_FORMAT & a valid KEY_BLOCK_SIZE +--echo # KEY_BLOCK_SIZE is ignored with COMPACT, REDUNDANT, & DYNAMIC +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + + +--echo # Test 11) StrictMode=OFF, ALTER with each ROW_FORMAT & a valid KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + + +--echo # Test 12) StrictMode=OFF, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + +--echo # Test 13) StrictMode=OFF, CREATE with a valid KEY_BLOCK_SIZE +--echo # ALTER with each ROW_FORMAT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +SHOW CREATE TABLE t1; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SHOW CREATE TABLE t1; +ALTER TABLE t1 ROW_FORMAT=COMPACT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=COMPACT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + +--echo # Test 14) StrictMode=OFF, CREATE with an invalid KEY_BLOCK_SIZE, it defaults to 8 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=15; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + +--echo # Test 15) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a +--echo valid KEY_BLOCK_SIZE are remembered but not used when ROW_FORMAT +--echo is reverted to Antelope and then used again when ROW_FORMAT=Barracuda. +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_format=Antelope; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_format=Barracuda; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_format=Antelope; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_format=Barracuda; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + +--echo # Test 16) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a +--echo valid KEY_BLOCK_SIZE are remembered but not used when innodb_file_per_table=OFF +--echo and then used again when innodb_file_per_table=ON. +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_per_table=OFF; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_per_table=ON; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_per_table=OFF; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_per_table=ON; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + + +--echo # Cleanup +DROP TABLE IF EXISTS t1; + +--disable_query_log +EVAL SET GLOBAL innodb_file_format=$innodb_file_format_orig; +EVAL SET GLOBAL innodb_file_format_max=$innodb_file_format_max_orig; +EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; +EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig; +--enable_query_log + diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test index 26de072f08b..86189f1426d 100644 --- a/mysql-test/suite/innodb/t/innodb-index.test +++ b/mysql-test/suite/innodb/t/innodb-index.test @@ -368,36 +368,36 @@ create table t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob,h blob, i blob,j blob,k blob,l blob,m blob,n blob,o blob,p blob, q blob,r blob,s blob,t blob,u blob) engine=innodb row_format=dynamic; -create index t1a on t1 (a(1)); -create index t1b on t1 (b(1)); -create index t1c on t1 (c(1)); -create index t1d on t1 (d(1)); -create index t1e on t1 (e(1)); -create index t1f on t1 (f(1)); -create index t1g on t1 (g(1)); -create index t1h on t1 (h(1)); -create index t1i on t1 (i(1)); -create index t1j on t1 (j(1)); -create index t1k on t1 (k(1)); -create index t1l on t1 (l(1)); -create index t1m on t1 (m(1)); -create index t1n on t1 (n(1)); -create index t1o on t1 (o(1)); -create index t1p on t1 (p(1)); -create index t1q on t1 (q(1)); -create index t1r on t1 (r(1)); -create index t1s on t1 (s(1)); -create index t1t on t1 (t(1)); +create index t1a on t1 (a(767)); +create index t1b on t1 (b(767)); +create index t1c on t1 (c(767)); +create index t1d on t1 (d(767)); +create index t1e on t1 (e(767)); +create index t1f on t1 (f(767)); +create index t1g on t1 (g(767)); +create index t1h on t1 (h(767)); +create index t1i on t1 (i(767)); +create index t1j on t1 (j(767)); +create index t1k on t1 (k(767)); +create index t1l on t1 (l(767)); +create index t1m on t1 (m(767)); +create index t1n on t1 (n(767)); +create index t1o on t1 (o(767)); +create index t1p on t1 (p(767)); +create index t1q on t1 (q(767)); +create index t1r on t1 (r(767)); +create index t1s on t1 (s(767)); +create index t1t on t1 (t(767)); --error 139 -create index t1u on t1 (u(1)); +create index t1u on t1 (u(767)); --error 139 -create index t1ut on t1 (u(1), t(1)); -create index t1st on t1 (s(1), t(1)); +create index t1ut on t1 (u(767), t(767)); +create index t1st on t1 (s(767), t(767)); show create table t1; --error 139 -create index t1u on t1 (u(1)); +create index t1u on t1 (u(767)); alter table t1 row_format=compact; -create index t1u on t1 (u(1)); +create index t1u on t1 (u(767)); drop table t1; eval set global innodb_file_per_table=$per_table; diff --git a/mysql-test/suite/innodb/t/innodb-system-table-view-master.opt b/mysql-test/suite/innodb/t/innodb-system-table-view-master.opt deleted file mode 100644 index 303ec1be1d0..00000000000 --- a/mysql-test/suite/innodb/t/innodb-system-table-view-master.opt +++ /dev/null @@ -1,3 +0,0 @@ ---default-storage-engine=MyISAM ---innodb-strict-mode=0 ---innodb-file-per-table=0 diff --git a/mysql-test/suite/innodb/t/innodb-system-table-view.test b/mysql-test/suite/innodb/t/innodb-system-table-view.test deleted file mode 100644 index 904087577c8..00000000000 --- a/mysql-test/suite/innodb/t/innodb-system-table-view.test +++ /dev/null @@ -1,93 +0,0 @@ -# This is the test for Information Schema System Table View -# that displays the InnoDB system table content through -# information schema tables. - - -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES; - -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES; - -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS; - -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS; - -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; - -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; - -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS; - -# Create a foreign key constraint, and verify the information -# in INFORMATION_SCHEMA.INNODB_SYS_FOREIGN and -# INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS -CREATE TABLE parent (id INT NOT NULL, - PRIMARY KEY (id)) ENGINE=INNODB; - -CREATE TABLE child (id INT, parent_id INT, - INDEX par_ind (parent_id), - CONSTRAINT constraint_test - FOREIGN KEY (parent_id) REFERENCES parent(id) - ON DELETE CASCADE) ENGINE=INNODB; - -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; - -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; - -# Insert a row in the table "parent", and see whether that reflected in -# INNODB_SYS_TABLESTATS -INSERT INTO parent VALUES(1); - -SELECT name, num_rows, mysql_handles_opened -FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS -WHERE name LIKE "%parent"; - -SELECT NAME, FLAG, N_COLS, SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES; - -SELECT name, n_fields -from INFORMATION_SCHEMA.INNODB_SYS_INDEXES -WHERE table_id In (SELECT table_id from - INFORMATION_SCHEMA.INNODB_SYS_TABLES - WHERE name LIKE "%parent%"); - -SELECT name, n_fields -from INFORMATION_SCHEMA.INNODB_SYS_INDEXES -WHERE table_id In (SELECT table_id from - INFORMATION_SCHEMA.INNODB_SYS_TABLES - WHERE name LIKE "%child%"); - -SELECT name, pos, mtype, len -from INFORMATION_SCHEMA.INNODB_SYS_COLUMNS -WHERE table_id In (SELECT table_id from - INFORMATION_SCHEMA.INNODB_SYS_TABLES - WHERE name LIKE "%child%"); - -DROP TABLE child; - -DROP TABLE parent; - -# Create table with 2 columns in the foreign key constraint -CREATE TABLE parent (id INT NOT NULL, newid INT NOT NULL, - PRIMARY KEY (id, newid)) ENGINE=INNODB; - -CREATE TABLE child (id INT, parent_id INT, - INDEX par_ind (parent_id), - CONSTRAINT constraint_test - FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid) - ON DELETE CASCADE) ENGINE=INNODB; - -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; - -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; - -INSERT INTO parent VALUES(1, 9); - -# Nested query will open the table handle twice -SELECT * FROM parent WHERE id IN (SELECT id FROM parent); - -SELECT name, num_rows, mysql_handles_opened -FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS -WHERE name LIKE "%parent"; - -DROP TABLE child; - -DROP TABLE parent; diff --git a/mysql-test/suite/innodb/t/innodb-timeout.test b/mysql-test/suite/innodb/t/innodb-timeout.test index fd839540667..07d03093b63 100644 --- a/mysql-test/suite/innodb/t/innodb-timeout.test +++ b/mysql-test/suite/innodb/t/innodb-timeout.test @@ -1,5 +1,5 @@ -let $timeout=`select @@innodb_lock_wait_timeout`; +let $initial_timeout=`select @@innodb_lock_wait_timeout`; set global innodb_lock_wait_timeout=42; connect (a,localhost,root,,); @@ -11,19 +11,24 @@ set innodb_lock_wait_timeout=1; select @@innodb_lock_wait_timeout; connection b; +let $connection_b_id=`SELECT CONNECTION_ID()`; select @@innodb_lock_wait_timeout; set global innodb_lock_wait_timeout=347; select @@innodb_lock_wait_timeout; -set innodb_lock_wait_timeout=1; +set innodb_lock_wait_timeout=10; select @@innodb_lock_wait_timeout; connect (c,localhost,root,,); connection c; + select @@innodb_lock_wait_timeout; -connection default; + disconnect c; +--source include/wait_until_disconnected.inc connection a; +--replace_result $connection_b_id <connection_b_id> +eval SET @connection_b_id = $connection_b_id; create table t1(a int primary key)engine=innodb; begin; insert into t1 values(1),(2),(3); @@ -32,7 +37,37 @@ connection b; --send select * from t1 for update; +# Observation on information_schema.processlist (2010-12 mysql-5.5) +# ----------------------------------------------------------------- +# As soon as the server started the execution of the +# connection a: --send select ... for update +# High parallel load could delay this up to two seconds. +# and before either +# - the innodb_lock_wait_timeout was exceeded +# -> connection b reap gets ER_LOCK_WAIT_TIMEOUT +# or +# - connection a commits, the lock disappears and the statement +# of connection b finishes +# -> connection b reap gets success + result set +# we see within information_schema.processlist for connection b a row +# command state info +# Query Sending data select * from t1 for update +# The highest time value seen was @@innodb_lock_wait_timeout + 1. +# Please note that there is unfortunately nothing which says +# that we are just waiting for a lock. + connection a; +# In order to ensure that the execution of +# connection b: select * from t1 for update +# has really started and is most probably waiting for the lock now we poll on +# information_schema.processlist. +# Also our current session innodb_lock_wait_timeout of 10 seconds should big +# enough to prevent that connection b ends up with getting ER_LOCK_WAIT_TIMEOUT. +# +let $wait_timeout= 10; +let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist +WHERE id = @connection_b_id AND INFO = 'select * from t1 for update'; +--source include/wait_condition.inc commit; connection b; @@ -43,21 +78,39 @@ begin; insert into t1 values(4); connection b; +set innodb_lock_wait_timeout=3; +# 3 seconds should be big enough that the wait routine of connection a will +# hit the time span where our next statement is visible within the +# information_schema.processlist. --send select * from t1 for update; connection a; -sleep 2; +# Wait till the execution of the connection b statement was started. +let $wait_timeout= 10; +let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist +WHERE id = @connection_b_id AND INFO = 'select * from t1 for update'; +--source include/wait_condition.inc +# Wait till the execution of the connection b statement has ended. +let $wait_timeout= 10; +let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist +WHERE id = @connection_b_id AND INFO IS NULL; +--source include/wait_condition.inc +# Give "commit" though this must be too late for the statement of connection b. commit; connection b; --error ER_LOCK_WAIT_TIMEOUT reap; -drop table t1; -connection default; +disconnect b; +--source include/wait_until_disconnected.inc +connection a; disconnect a; -disconnect b; +--source include/wait_until_disconnected.inc -eval set global innodb_lock_wait_timeout=$timeout; +connection default; +drop table t1; +--replace_result $initial_timeout <initial_timeout> +eval set global innodb_lock_wait_timeout=$initial_timeout; diff --git a/mysql-test/suite/innodb/t/innodb-truncate.test b/mysql-test/suite/innodb/t/innodb-truncate.test new file mode 100644 index 00000000000..7629eb1a980 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-truncate.test @@ -0,0 +1,65 @@ +--source include/have_innodb.inc + +--echo # +--echo # TRUNCATE TABLE +--echo # +--echo # Truncating is disallowed for parent tables unless such table +--echo # participates in self-referencing foreign keys only. +--echo # +CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=INNODB; +CREATE TABLE t2 (fk INT NOT NULL, FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB; +--error ER_TRUNCATE_ILLEGAL_FK +TRUNCATE TABLE t1; +--echo # Truncation of child should succeed. +TRUNCATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT, + FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB; +--echo # Truncation of self-referencing table should succeed. +TRUNCATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # Also, truncating such tables is allowed if foreign key +--echo # checks are disabled. +--echo # + +SET @old_foreign_key_checks = @@SESSION.foreign_key_checks; +CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=INNODB; +CREATE TABLE t2 (fk INT NOT NULL, FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB; +CREATE TABLE t3 (pk INT PRIMARY KEY, fk INT, + FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB; +SET @@SESSION.foreign_key_checks = 0; +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +TRUNCATE TABLE t3; +SET @@SESSION.foreign_key_checks = 1; +--error ER_TRUNCATE_ILLEGAL_FK +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +TRUNCATE TABLE t3; +LOCK TABLES t1 WRITE; +SET @@SESSION.foreign_key_checks = 0; +TRUNCATE TABLE t1; +SET @@SESSION.foreign_key_checks = 1; +--error ER_TRUNCATE_ILLEGAL_FK +TRUNCATE TABLE t1; +UNLOCK TABLES; +DROP TABLE t3,t2,t1; +SET @@SESSION.foreign_key_checks = @old_foreign_key_checks; + +--echo # +--echo # Test that TRUNCATE resets auto-increment. +--echo # + +CREATE TABLE t1 (a INT PRIMARY KEY NOT NULL AUTO_INCREMENT); +INSERT INTO t1 VALUES (NULL), (NULL); +SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1'; +SELECT * FROM t1 ORDER BY a; +TRUNCATE TABLE t1; +SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1'; +INSERT INTO t1 VALUES (NULL), (NULL); +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; + diff --git a/mysql-test/suite/innodb/t/innodb-zip.test b/mysql-test/suite/innodb/t/innodb-zip.test index 236579c83b8..e28b0862166 100644 --- a/mysql-test/suite/innodb/t/innodb-zip.test +++ b/mysql-test/suite/innodb/t/innodb-zip.test @@ -175,9 +175,7 @@ set innodb_strict_mode = on; #Test different values of KEY_BLOCK_SIZE ---error ER_CANT_CREATE_TABLE create table t1 (id int primary key) engine = innodb key_block_size = 0; -show warnings; --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 9; @@ -198,7 +196,7 @@ create table t11(id int primary key) engine = innodb row_format = redundant; SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb'; -drop table t3, t4, t5, t6, t7, t8, t9, t10, t11; +drop table t1, t3, t4, t5, t6, t7, t8, t9, t10, t11; #test different values of ROW_FORMAT with KEY_BLOCK_SIZE create table t1 (id int primary key) engine = innodb @@ -219,14 +217,12 @@ create table t4 (id int primary key) engine = innodb key_block_size = 8 row_format = dynamic; show warnings; ---error ER_CANT_CREATE_TABLE create table t5 (id int primary key) engine = innodb key_block_size = 8 row_format = default; -show warnings; SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb'; -drop table t1; +drop table t1, t5; #test multiple errors --error ER_CANT_CREATE_TABLE diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index d9cdedb13bf..1284f19b741 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -52,6 +52,15 @@ drop table if exists t1,t2,t3,t4; drop database if exists mysqltest; --enable_warnings +# Bug#58912 InnoDB unnecessarily avoids update-in-place on column prefixes +CREATE TABLE bug58912 (a BLOB, b TEXT, PRIMARY KEY(a(1))) ENGINE=InnoDB; +INSERT INTO bug58912 VALUES(REPEAT('a',8000),REPEAT('b',8000)); +UPDATE bug58912 SET a=REPEAT('a',7999); +# The above statements used to trigger a failure during purge when +# Bug#55284 was fixed while Bug#58912 was not. Defer the DROP TABLE, +# so that purge gets a chance to run (and a double free of the +# off-page column can be detected, if one is to occur.) + # # Small basic test with ignore # @@ -1474,11 +1483,6 @@ CREATE TABLE t1 ( id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) ENGINE=InnoDB; -CREATE TABLE t2 ( -id INTEGER NOT NULL, -FOREIGN KEY (id) REFERENCES t1 (id) -) ENGINE=InnoDB; - INSERT INTO t1 (id) VALUES (NULL); SELECT * FROM t1; TRUNCATE t1; @@ -1491,7 +1495,7 @@ DELETE FROM t1; TRUNCATE t1; INSERT INTO t1 (id) VALUES (NULL); SELECT * FROM t1; -DROP TABLE t2, t1; +DROP TABLE t1; # Test that foreign keys in temporary tables are not accepted (bug #12084) CREATE TABLE t1 @@ -1726,13 +1730,15 @@ update t2 set a=2; update t3 set a=2; -- error 1452 update t4 set a=2; --- error 1451 +-- error ER_TRUNCATE_ILLEGAL_FK truncate t1; --- error 1451 +-- error ER_TRUNCATE_ILLEGAL_FK truncate t3; truncate t2; truncate t4; +-- error ER_TRUNCATE_ILLEGAL_FK truncate t1; +-- error ER_TRUNCATE_ILLEGAL_FK truncate t3; drop table t4,t3,t2,t1; @@ -2554,6 +2560,9 @@ SET GLOBAL innodb_thread_concurrency = @innodb_thread_concurrency_orig; -- enable_query_log +# Clean up after the Bug#55284/Bug#58912 test case. +DROP TABLE bug58912; + ####################################################################### # # # Please, DO NOT TOUCH this file as well as the innodb.result file. # diff --git a/mysql-test/suite/innodb/t/innodb_bug30423.test b/mysql-test/suite/innodb/t/innodb_bug30423.test new file mode 100644 index 00000000000..f2a3ee8d099 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug30423.test @@ -0,0 +1,211 @@ +# Test for Bug #30423, InnoDBs treatment of NULL in index stats causes +# bad "rows examined" estimates. +# Implemented InnoDB system variable "innodb_stats_method" with +# "nulls_equal" (default), "nulls_unequal", and "nulls_ignored" options. + +-- source include/have_innodb.inc + +let $innodb_stats_method_orig = `select @@innodb_stats_method`; + +# default setting for innodb_stats_method is "nulls_equal" +set global innodb_stats_method = default; + +select @@innodb_stats_method; + +# create three tables, bug30243_1, bug30243_2 and bug30243_3. +# The test scenario is adopted from original bug #30423 report. +# table bug30243_1 and bug30243_3 have many NULL values + +-- disable_result_log +-- disable_query_log + +DROP TABLE IF EXISTS bug30243_1; +CREATE TABLE bug30243_1 ( + org_id int(11) NOT NULL default '0', + UNIQUE KEY (org_id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +LOCK TABLES bug30243_1 WRITE; +INSERT INTO bug30243_1 VALUES (11),(15),(16),(17),(19),(20),(21),(23),(24), +(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(37),(38),(40),(41), +(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56), +(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71), +(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86), +(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101), +(102),(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114), +(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),(127), +(128),(129),(130),(131),(132),(133),(134),(135),(136),(137),(138),(139),(140), +(141),(142),(143),(144),(145); +UNLOCK TABLES; + +DROP TABLE IF EXISTS bug30243_3; +CREATE TABLE bug30243_3 ( + org_id int(11) default NULL, + KEY (org_id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO bug30243_3 VALUES (NULL); + +begin; +let $i=14; +while ($i) +{ + INSERT INTO bug30243_3 SELECT NULL FROM bug30243_3; + dec $i; +} + +INSERT INTO bug30243_3 VALUES (34),(34),(35),(56),(58),(62),(62),(64),(65),(66),(80),(135),(137),(138),(139),(140),(142),(143),(144),(145); +commit; + +DROP TABLE IF EXISTS bug30243_2; +CREATE TABLE bug30243_2 ( + org_id int(11) default NULL, + KEY `contacts$org_id` (org_id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO bug30243_2 VALUES (NULL); + +begin; +let $i=16; +while ($i) +{ + INSERT INTO bug30243_2 SELECT NULL FROM bug30243_2; + dec $i; +} + +INSERT INTO bug30243_2 VALUES (11),(15),(16),(17),(20),(21),(23),(24),(25), +(26),(27),(28),(29),(30),(31),(32),(33),(34),(37),(38),(40),(41),(42),(43), +(44),(45),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(48), +(48),(50),(51),(52),(52),(53),(54),(55),(57),(60),(61),(62),(62),(62),(62), +(62),(63),(64),(64),(65),(66),(66),(67),(68),(69),(70),(71),(72),(73),(74), +(75),(76),(77),(78),(79),(80),(80),(81),(82),(83),(84),(85),(86),(87),(88), +(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),(102), +(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114), +(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126), +(127),(128),(129),(130),(131),(132),(133),(133),(135),(135),(135),(135), +(136),(136),(138),(138),(139),(139),(139),(140),(141),(141),(142),(143), +(143),(145),(145); +commit; + + +-- enable_result_log +-- enable_query_log + +# check tables's value +select count(*) from bug30243_3 where org_id is not NULL; +select count(*) from bug30243_3 where org_id is NULL; + +select count(*) from bug30243_2 where org_id is not NULL; +select count(*) from bug30243_2 where org_id is NULL; + +select @@innodb_stats_method; + +analyze table bug30243_1; +analyze table bug30243_2; +analyze table bug30243_3; + +# Following query plan shows that we over estimate the rows per +# unique value (since there are many NULLs). +# Skip this query log since the stats estimate could vary from runs +-- disable_query_log +-- disable_result_log +explain SELECT COUNT(*), 0 + FROM bug30243_1 orgs + LEFT JOIN bug30243_3 sa_opportunities + ON orgs.org_id=sa_opportunities.org_id + LEFT JOIN bug30243_2 contacts + ON orgs.org_id=contacts.org_id ; +-- enable_query_log +-- enable_result_log + +# following set operation will fail +#--error ER_WRONG_VALUE_FOR_VAR +--error 1231 +set global innodb_stats_method = "NULL"; + +set global innodb_stats_method = "nulls_ignored"; + +select @@innodb_stats_method; + +# Regenerate the stats with "nulls_ignored" option + +analyze table bug30243_1; +analyze table bug30243_2; +analyze table bug30243_3; + +# Following query plan shows that we get the correct rows per +# unique value (should be approximately 1 row per value) +explain SELECT COUNT(*), 0 + FROM bug30243_1 orgs + LEFT JOIN bug30243_3 sa_opportunities + ON orgs.org_id=sa_opportunities.org_id + LEFT JOIN bug30243_2 contacts + ON orgs.org_id=contacts.org_id ; + +select @@innodb_stats_method; + +# Try the "nulls_unequal" option +set global innodb_stats_method = "nulls_unequal"; + +select @@innodb_stats_method; + +analyze table bug30243_1; +analyze table bug30243_2; +analyze table bug30243_3; + +# Following query plan shows that we get the correct rows per +# unique value (~1) +explain SELECT COUNT(*), 0 + FROM bug30243_1 orgs + LEFT JOIN bug30243_3 sa_opportunities + ON orgs.org_id=sa_opportunities.org_id + LEFT JOIN bug30243_2 contacts + ON orgs.org_id=contacts.org_id; + + +# Create a table with all NULL values, make sure the stats calculation +# does not crash with table of all NULL values +-- disable_query_log +CREATE TABLE table_bug30423 ( + org_id int(11) default NULL, + KEY(org_id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO `table_bug30423` VALUES (NULL); + +begin; +let $i=10; +while ($i) +{ + INSERT INTO table_bug30423 SELECT NULL FROM table_bug30423; + dec $i; +} +commit; + +-- enable_query_log + +SELECT COUNT(*) FROM table_bug30423 WHERE org_id IS NULL; + +# calculate the statistics for the table for "nulls_ignored" and +# "nulls_unequal" option +set global innodb_stats_method = "nulls_unequal"; +analyze table table_bug30423; + +set global innodb_stats_method = "nulls_ignored"; +analyze table table_bug30423; + + +eval set global innodb_stats_method = $innodb_stats_method_orig; + +drop table bug30243_2; + +drop table bug30243_1; + +drop table bug30243_3; + +drop table table_bug30423; diff --git a/mysql-test/suite/innodb/t/innodb_bug42419.test b/mysql-test/suite/innodb/t/innodb_bug42419.test index b3d732662e1..43e9ada96db 100644 --- a/mysql-test/suite/innodb/t/innodb_bug42419.test +++ b/mysql-test/suite/innodb/t/innodb_bug42419.test @@ -6,7 +6,7 @@ --source include/not_embedded.inc let $innodb_lock_wait_timeout= query_get_value(SHOW VARIABLES LIKE 'innodb_lock_wait_timeout%', Value, 1); -if (`SELECT $innodb_lock_wait_timeout < 10`) +if ($innodb_lock_wait_timeout < 10) { --echo # innodb_lock_wait_timeout must be >= 10 seconds --echo # so that this test can work all time fine on an overloaded testing box diff --git a/mysql-test/suite/innodb/t/innodb_bug53046.test b/mysql-test/suite/innodb/t/innodb_bug53046.test new file mode 100644 index 00000000000..77f0a638728 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug53046.test @@ -0,0 +1,48 @@ +# +# http://bugs.mysql.com/53046 +# dict_update_statistics_low can still be run concurrently on same table +# +# This is a symbolic test, it would not fail if the bug is present. +# Rather those SQL commands have been used during manual testing under +# UNIV_DEBUG & UNIV_SYNC_DEBUG to test all changed codepaths for locking +# correctness. +# + +-- source include/have_innodb.inc + +CREATE TABLE bug53046_1 (c1 INT PRIMARY KEY) ENGINE=INNODB; +CREATE TABLE bug53046_2 (c2 INT PRIMARY KEY, + FOREIGN KEY (c2) REFERENCES bug53046_1(c1) + ON UPDATE CASCADE ON DELETE CASCADE) ENGINE=INNODB; + +INSERT INTO bug53046_1 VALUES (1); +let $i = 5; +while ($i) { + eval INSERT INTO bug53046_1 SELECT c1+(SELECT MAX(c1) FROM bug53046_1) + FROM bug53046_1; + dec $i; +} + +INSERT INTO bug53046_2 VALUES (1), (2); + +# CREATE TABLE innodb_table_monitor (a int) ENGINE=INNODB; +# wait more than 1 minute and observe the mysqld output +# DROP TABLE innodb_table_monitor; + +ANALYZE TABLE bug53046_1; + +# this prints create time and other nondeterministic data +-- disable_result_log +SHOW TABLE STATUS LIKE 'bug53046_1'; +-- enable_result_log + +UPDATE bug53046_1 SET c1 = c1 - 1; + +DELETE FROM bug53046_1; + +INSERT INTO bug53046_1 VALUES (1); +INSERT INTO bug53046_2 VALUES (1); +TRUNCATE TABLE bug53046_2; + +DROP TABLE bug53046_2; +DROP TABLE bug53046_1; diff --git a/mysql-test/suite/innodb/t/innodb_bug53756-master.opt b/mysql-test/suite/innodb/t/innodb_bug53756-master.opt new file mode 100644 index 00000000000..425fda95086 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug53756-master.opt @@ -0,0 +1 @@ +--skip-stack-trace --skip-core-file diff --git a/mysql-test/suite/innodb/t/innodb_bug53756.test b/mysql-test/suite/innodb/t/innodb_bug53756.test new file mode 100644 index 00000000000..ae23e9d41a3 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug53756.test @@ -0,0 +1,187 @@ +# This is the test case for bug #53756. Alter table operation could +# leave a deleted record for the temp table (later renamed to the altered +# table) in the SYS_TABLES secondary index, we should ignore this row and +# find the first non-deleted row for the specified table_id when load table +# metadata in the function dict_load_table_on_id() during crash recovery. + +# +# innobackup needs to connect to the server. Not supported in embedded. +--source include/not_embedded.inc +# +# This test case needs to crash the server. Needs a debug server. +--source include/have_debug.inc +# +# Don't test this under valgrind, memory leaks will occur. +--source include/not_valgrind.inc +# +# This test case needs InnoDB. +-- source include/have_innodb.inc + +# Avoid CrashReporter popup on Mac +--source include/not_crashrep.inc + +# +# Precautionary clean up. +# +--disable_warnings +DROP TABLE IF EXISTS bug_53756 ; +--enable_warnings + +# +# Create test data. +# +CREATE TABLE bug_53756 (pk INT, c1 INT) ENGINE=InnoDB; +ALTER TABLE bug_53756 ADD PRIMARY KEY (pk); +INSERT INTO bug_53756 VALUES(1, 11), (2, 22), (3, 33), (4, 44); + +--echo +--echo # Select a less restrictive isolation level. +# Don't use user variables. They won't survive server crash. +--let $global_isolation= `SELECT @@global.tx_isolation` +--let $session_isolation= `SELECT @@session.tx_isolation` +SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +COMMIT; + +--echo +--echo # Start a transaction in the default connection for isolation. +START TRANSACTION; +SELECT @@tx_isolation; +SELECT * FROM bug_53756; + +--echo +--echo # connection con1 deletes row 1 +--connect (con1,localhost,root,,) +START TRANSACTION; +SELECT @@tx_isolation; +DELETE FROM bug_53756 WHERE pk=1; + +--echo +--echo # connection con2 deletes row 2 +--connect (con2,localhost,root,,) +START TRANSACTION; +SELECT @@tx_isolation; +DELETE FROM bug_53756 WHERE pk=2; + +--echo +--echo # connection con3 updates row 3 +--connect (con3,localhost,root,,) +START TRANSACTION; +SELECT @@tx_isolation; +UPDATE bug_53756 SET c1=77 WHERE pk=3; + +--echo +--echo # connection con4 updates row 4 +--connect (con4,localhost,root,,) +START TRANSACTION; +SELECT @@tx_isolation; +UPDATE bug_53756 SET c1=88 WHERE pk=4; + +--echo +--echo # connection con5 inserts row 5 +--connect (con5,localhost,root,,) +START TRANSACTION; +SELECT @@tx_isolation; +INSERT INTO bug_53756 VALUES(5, 55); + +--echo +--echo # connection con6 inserts row 6 +--connect (con6,localhost,root,,) +START TRANSACTION; +SELECT @@tx_isolation; +INSERT INTO bug_53756 VALUES(6, 66); + +--echo +--echo # connection con1 commits. +--connection con1 +COMMIT; + +--echo +--echo # connection con3 commits. +--connection con3 +COMMIT; + +--echo +--echo # connection con4 rolls back. +--connection con4 +ROLLBACK; + +--echo +--echo # connection con6 rolls back. +--connection con6 +ROLLBACK; + +--echo +--echo # The connections 2 and 5 stay open. + +--echo +--echo # connection default selects resulting data. +--echo # Delete of row 1 was committed. +--echo # Update of row 3 was committed. +--echo # Due to isolation level read committed, these should be included. +--echo # All other changes should not be included. +--connection default +SELECT * FROM bug_53756; + +--echo +--echo # connection default +--connection default +--echo # +--echo # Crash server. +# +# Write file to make mysql-test-run.pl expect the "crash", but don't start +# it until it's told to +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +# +START TRANSACTION; +INSERT INTO bug_53756 VALUES (666,666); +# +# Request a crash on next execution of commit. +SET SESSION debug="+d,crash_commit_before"; +# +# Execute the statement that causes the crash. +--error 2013 +COMMIT; +--echo +--echo # +--echo # disconnect con1, con2, con3, con4, con5, con6. +--disconnect con1 +--disconnect con2 +--disconnect con3 +--disconnect con4 +--disconnect con5 +--disconnect con6 +--echo # +--echo # Restart server. +# +# Write file to make mysql-test-run.pl start up the server again +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +# +# Turn on reconnect +--enable_reconnect +# +# Call script that will poll the server waiting for it to be back online again +--source include/wait_until_connected_again.inc +# +# Turn off reconnect again +--disable_reconnect +--echo + +--echo # +--echo # Select recovered data. +--echo # Delete of row 1 was committed. +--echo # Update of row 3 was committed. +--echo # These should be included. +--echo # All other changes should not be included. +--echo # Delete of row 2 and insert of row 5 should be rolled back +SELECT * FROM bug_53756; + +--echo +--echo # Clean up. +DROP TABLE bug_53756; + +--disable_query_log +eval SET GLOBAL tx_isolation= '$global_isolation'; +eval SET SESSION tx_isolation= '$session_isolation'; +--enable_query_log + diff --git a/mysql-test/suite/innodb/t/innodb_bug54679.test b/mysql-test/suite/innodb/t/innodb_bug54679.test deleted file mode 100644 index b279e1eaf9f..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug54679.test +++ /dev/null @@ -1,100 +0,0 @@ -# Test Bug #54679 alter table causes compressed row_format to revert to compact - - -let $file_format=`select @@innodb_file_format`; -let $file_format_max=`select @@innodb_file_format_max`; -let $file_per_table=`select @@innodb_file_per_table`; -SET GLOBAL innodb_file_format='Barracuda'; -SET GLOBAL innodb_file_per_table=ON; -SET innodb_strict_mode=ON; - -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; - -# The ROW_FORMAT of the table should be preserved when it is not specified -# in ALTER TABLE. -ALTER TABLE bug54679 ADD COLUMN b INT; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; - -DROP TABLE bug54679; - -# Check that the ROW_FORMAT conversion to/from COMPRESSED works. - -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; - -# KEY_BLOCK_SIZE implies COMPRESSED. -ALTER TABLE bug54679 KEY_BLOCK_SIZE=1; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; - ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE bug54679 ROW_FORMAT=REDUNDANT; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -DROP TABLE bug54679; -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; - -ALTER TABLE bug54679 KEY_BLOCK_SIZE=2; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; - -# This prevents other than REDUNDANT or COMPACT ROW_FORMAT for new tables. -SET GLOBAL innodb_file_format=Antelope; - ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE bug54679 KEY_BLOCK_SIZE=4; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE bug54679 ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -DROP TABLE bug54679; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB; - -SET GLOBAL innodb_file_format=Barracuda; -# This will prevent ROW_FORMAT=COMPRESSED, because the system tablespace -# cannot be compressed. -SET GLOBAL innodb_file_per_table=OFF; - ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE bug54679 KEY_BLOCK_SIZE=4; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE bug54679 ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -DROP TABLE bug54679; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -SET GLOBAL innodb_file_per_table=ON; -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; -DROP TABLE bug54679; - -# restore original values, quietly so the test does not fail if those -# defaults are changed --- disable_query_log -EVAL SET GLOBAL innodb_file_format=$file_format; -EVAL SET GLOBAL innodb_file_format_max=$file_format_max; -EVAL SET GLOBAL innodb_file_per_table=$file_per_table; --- enable_query_log diff --git a/mysql-test/suite/innodb/t/innodb_bug56143.test b/mysql-test/suite/innodb/t/innodb_bug56143.test new file mode 100644 index 00000000000..c21de09892c --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug56143.test @@ -0,0 +1,586 @@ +# +# Bug#56143 too many foreign keys causes output of show create table to become invalid +# http://bugs.mysql.com/56143 +# + +-- source include/have_innodb.inc + +-- disable_query_log +-- disable_result_log + +if ($VALGRIND_TEST) +{ + call mtr.add_suppression("InnoDB: Warning: a long semaphore wait:"); +} + +SET foreign_key_checks=0; + +DROP TABLE IF EXISTS bug56143_1; +DROP TABLE IF EXISTS bug56143_2; + +CREATE TABLE bug56143_1 (a INT, KEY(a)) ENGINE=INNODB; + +CREATE TABLE `bug56143_2` ( + `a` int(11) DEFAULT NULL, + KEY `a` (`a`), + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa10` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa100` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa101` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa102` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa103` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa104` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa105` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa106` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa107` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa108` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa109` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa11` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa110` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa111` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa112` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa113` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa114` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa115` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa116` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa117` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa118` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa119` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa12` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa120` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa121` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa122` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa123` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa124` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa125` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa126` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa127` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa128` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa129` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa13` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa130` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa131` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa132` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa133` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa134` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa135` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa136` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa137` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa138` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa139` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa14` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa140` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa141` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa142` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa143` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa144` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa145` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa146` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa147` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa148` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa149` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa15` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa150` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa151` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa152` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa153` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa154` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa155` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa156` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa157` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa158` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa159` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa16` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa160` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa161` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa162` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa163` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa164` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa165` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa166` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa167` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa168` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa169` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa17` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa170` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa171` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa172` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa173` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa174` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa175` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa176` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa177` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa178` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa179` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa18` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa180` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa181` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa182` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa183` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa184` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa185` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa186` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa187` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa188` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa189` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa19` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa190` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa191` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa192` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa193` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa194` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa195` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa196` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa197` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa198` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa199` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa20` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa200` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa201` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa202` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa203` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa204` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa205` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa206` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa207` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa208` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa209` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa21` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa210` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa211` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa212` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa213` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa214` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa215` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa216` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa217` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa218` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa219` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa22` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa220` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa221` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa222` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa223` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa224` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa225` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa226` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa227` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa228` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa229` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa23` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa230` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa231` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa232` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa233` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa234` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa235` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa236` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa237` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa238` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa239` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa24` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa240` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa241` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa242` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa243` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa244` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa245` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa246` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa247` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa248` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa249` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa25` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa250` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa251` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa252` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa253` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa254` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa255` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa256` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa257` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa258` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa259` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa26` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa260` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa261` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa262` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa263` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa264` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa265` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa266` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa267` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa268` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa269` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa27` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa270` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa271` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa272` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa273` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa274` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa275` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa276` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa277` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa278` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa279` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa28` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa280` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa281` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa282` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa283` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa284` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa285` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa286` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa287` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa288` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa289` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa29` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa290` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa291` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa292` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa293` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa294` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa295` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa296` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa297` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa298` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa299` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa3` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa30` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa300` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa301` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa302` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa303` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa304` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa305` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa306` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa307` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa308` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa309` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa31` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa310` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa311` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa312` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa313` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa314` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa315` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa316` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa317` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa318` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa319` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa32` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa320` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa321` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa322` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa323` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa324` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa325` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa326` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa327` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa328` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa329` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa33` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa330` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa331` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa332` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa333` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa334` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa335` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa336` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa337` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa338` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa339` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa34` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa340` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa341` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa342` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa343` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa344` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa345` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa346` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa347` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa348` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa349` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa35` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa350` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa351` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa352` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa353` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa354` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa355` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa356` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa357` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa358` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa359` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa36` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa360` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa361` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa362` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa363` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa364` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa365` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa366` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa367` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa368` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa369` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa37` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa370` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa371` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa372` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa373` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa374` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa375` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa376` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa377` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa378` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa379` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa38` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa380` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa381` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa382` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa383` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa384` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa385` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa386` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa387` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa388` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa389` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa39` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa390` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa391` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa392` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa393` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa394` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa395` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa396` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa397` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa398` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa399` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa4` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa40` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa400` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa401` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa402` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa403` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa404` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa405` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa406` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa407` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa408` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa409` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa41` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa410` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa411` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa412` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa413` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa414` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa415` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa416` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa417` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa418` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa419` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa42` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa420` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa421` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa422` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa423` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa424` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa425` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa426` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa427` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa428` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa429` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa43` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa430` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa431` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa432` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa433` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa434` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa435` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa436` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa437` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa438` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa439` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa44` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa440` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa441` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa442` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa443` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa444` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa445` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa446` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa447` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa448` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa449` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa45` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa450` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa451` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa452` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa453` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa454` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa455` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa456` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa457` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa458` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa459` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa46` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa460` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa461` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa462` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa463` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa464` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa465` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa466` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa467` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa468` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa469` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa47` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa470` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa471` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa472` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa473` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa474` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa475` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa476` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa477` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa478` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa479` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa48` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa480` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa481` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa482` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa483` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa484` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa485` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa486` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa487` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa488` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa489` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa49` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa490` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa491` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa492` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa493` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa494` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa495` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa496` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa497` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa498` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa499` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa5` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa50` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa500` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa501` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa502` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa503` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa504` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa505` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa506` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa507` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa508` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa509` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa51` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa510` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa511` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa512` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa513` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa514` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa515` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa516` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa517` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa518` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa519` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa52` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa520` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa521` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa522` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa523` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa524` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa525` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa526` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa527` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa528` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa529` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa53` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa530` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa531` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa532` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa533` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa534` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa535` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa536` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa537` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa538` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa539` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa54` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa540` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa541` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa542` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa543` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa544` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa545` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa546` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa547` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa548` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa549` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa55` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa550` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa56` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa57` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa58` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa59` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa6` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa60` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa61` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa62` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa63` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa64` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa65` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa66` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa67` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa68` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa69` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa7` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa70` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa71` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa72` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa73` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa74` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa75` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa76` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa77` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa78` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa79` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa8` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa80` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa81` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa82` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa83` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa84` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa85` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa86` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa87` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa88` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa89` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa9` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa90` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa91` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa92` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa93` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa94` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa95` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa96` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa97` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa98` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL, + CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa99` FOREIGN KEY (`a`) REFERENCES `bug56143_1` (`a`) ON UPDATE SET NULL +) ENGINE=InnoDB; + +-- enable_query_log +-- enable_result_log + +SHOW CREATE TABLE bug56143_2; + +-- disable_query_log +-- disable_result_log +DROP TABLE bug56143_1; +DROP TABLE bug56143_2; diff --git a/mysql-test/suite/innodb/t/innodb_bug56680.test b/mysql-test/suite/innodb/t/innodb_bug56680.test new file mode 100644 index 00000000000..48723195141 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug56680.test @@ -0,0 +1,142 @@ +# +# Bug #56680 InnoDB may return wrong results from a case-insensitive index +# +-- source include/have_innodb.inc + +-- disable_query_log +SET @tx_isolation_orig = @@tx_isolation; +SET @innodb_file_per_table_orig = @@innodb_file_per_table; +SET @innodb_file_format_orig = @@innodb_file_format; +SET @innodb_file_format_max_orig = @@innodb_file_format_max; +# The flag innodb_change_buffering_debug is only available in debug builds. +# It instructs InnoDB to try to evict pages from the buffer pool when +# change buffering is possible, so that the change buffer will be used +# whenever possible. +-- error 0,ER_UNKNOWN_SYSTEM_VARIABLE +SET @innodb_change_buffering_debug_orig = @@innodb_change_buffering_debug; +-- error 0,ER_UNKNOWN_SYSTEM_VARIABLE +SET GLOBAL innodb_change_buffering_debug = 1; +-- enable_query_log +SET GLOBAL tx_isolation='REPEATABLE-READ'; +SET GLOBAL innodb_file_format=Barracuda; +SET GLOBAL innodb_file_per_table=on; + +CREATE TABLE bug56680( + a INT AUTO_INCREMENT PRIMARY KEY, + b CHAR(1), + c INT, + INDEX(b)) +ENGINE=InnoDB; + +INSERT INTO bug56680 VALUES(0,'x',1); +BEGIN; +SELECT b FROM bug56680; + +connect (con1,localhost,root,,); +connection con1; +BEGIN; +UPDATE bug56680 SET b='X'; + +connection default; +# This should return the last committed value 'x', but would return 'X' +# due to a bug in row_search_for_mysql(). +SELECT b FROM bug56680; +# This would always return the last committed value 'x'. +SELECT * FROM bug56680; + +connection con1; +ROLLBACK; +disconnect con1; + +connection default; + +SELECT b FROM bug56680; + +# For the rest of this test, use the READ UNCOMMITTED isolation level +# to see what exists in the secondary index. +SET GLOBAL tx_isolation='READ-UNCOMMITTED'; + +# Create enough rows for the table, so that the insert buffer will be +# used for modifying the secondary index page. There must be multiple +# index pages, because changes to the root page are never buffered. + +INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; +INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; +INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; +INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; +INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; +INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; +INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; +INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; +INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; +INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; +INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; + +BEGIN; +SELECT b FROM bug56680 LIMIT 2; + +connect (con1,localhost,root,,); +connection con1; +BEGIN; +DELETE FROM bug56680 WHERE a=1; +# This should be buffered, if innodb_change_buffering_debug = 1 is in effect. +INSERT INTO bug56680 VALUES(1,'X',1); + +# This should force an insert buffer merge, and return 'X' in the first row. +SELECT b FROM bug56680 LIMIT 3; + +connection default; +SELECT b FROM bug56680 LIMIT 2; +CHECK TABLE bug56680; + +connection con1; +ROLLBACK; +SELECT b FROM bug56680 LIMIT 2; +CHECK TABLE bug56680; + +connection default; +disconnect con1; + +SELECT b FROM bug56680 LIMIT 2; + +CREATE TABLE bug56680_2( + a INT AUTO_INCREMENT PRIMARY KEY, + b VARCHAR(2) CHARSET latin1 COLLATE latin1_german2_ci, + c INT, + INDEX(b)) +ENGINE=InnoDB; + +INSERT INTO bug56680_2 SELECT 0,_latin1 0xdf,c FROM bug56680; + +BEGIN; +SELECT HEX(b) FROM bug56680_2 LIMIT 2; +DELETE FROM bug56680_2 WHERE a=1; +# This should be buffered, if innodb_change_buffering_debug = 1 is in effect. +INSERT INTO bug56680_2 VALUES(1,'SS',1); + +# This should force an insert buffer merge, and return 'SS' in the first row. +SELECT HEX(b) FROM bug56680_2 LIMIT 3; +CHECK TABLE bug56680_2; + +# Test this with compressed tables. +ALTER TABLE bug56680_2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; + +SELECT HEX(b) FROM bug56680_2 LIMIT 2; +DELETE FROM bug56680_2 WHERE a=1; +# This should be buffered, if innodb_change_buffering_debug = 1 is in effect. +INSERT INTO bug56680_2 VALUES(1,_latin1 0xdf,1); + +# This should force an insert buffer merge, and return 0xdf in the first row. +SELECT HEX(b) FROM bug56680_2 LIMIT 3; +CHECK TABLE bug56680_2; + +DROP TABLE bug56680_2; +DROP TABLE bug56680; + +-- disable_query_log +SET GLOBAL tx_isolation = @tx_isolation_orig; +SET GLOBAL innodb_file_per_table = @innodb_file_per_table_orig; +SET GLOBAL innodb_file_format = @innodb_file_format_orig; +SET GLOBAL innodb_file_format_max = @innodb_file_format_max_orig; +-- error 0, ER_UNKNOWN_SYSTEM_VARIABLE +SET GLOBAL innodb_change_buffering_debug = @innodb_change_buffering_debug_orig; diff --git a/mysql-test/suite/innodb/t/innodb_bug56716.test b/mysql-test/suite/innodb/t/innodb_bug56716.test deleted file mode 100644 index c8e1ed83e38..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug56716.test +++ /dev/null @@ -1,9 +0,0 @@ -# -# Bug #56716 InnoDB locks a record gap without locking the table -# - -CREATE TABLE bug56716 (a INT PRIMARY KEY,b INT,c INT,INDEX(b)) ENGINE=InnoDB; - -SELECT * FROM bug56716 WHERE b<=42 ORDER BY b DESC FOR UPDATE; - -DROP TABLE bug56716; diff --git a/mysql-test/suite/innodb/t/innodb_bug56947.test b/mysql-test/suite/innodb/t/innodb_bug56947.test new file mode 100644 index 00000000000..e11f39b97a8 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug56947.test @@ -0,0 +1,16 @@ +# +# Bug #56947 valgrind reports a memory leak in innodb-plugin.innodb-index +# +-- source include/have_innodb.inc + +SET @old_innodb_file_per_table=@@innodb_file_per_table; +# avoid a message about filed *.ibd file creation in the error log +SET GLOBAL innodb_file_per_table=0; +create table bug56947(a int not null) engine = innodb; +CREATE TABLE `bug56947#1`(a int) ENGINE=InnoDB; +--error 156 +alter table bug56947 add unique index (a); +drop table `bug56947#1`; +drop table bug56947; +--disable_query_log +SET GLOBAL innodb_file_per_table=@old_innodb_file_per_table; diff --git a/mysql-test/suite/innodb/t/innodb_bug57252.test b/mysql-test/suite/innodb/t/innodb_bug57252.test new file mode 100644 index 00000000000..04c3ed0cea7 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug57252.test @@ -0,0 +1,46 @@ +# +# Bug#57252 disabling innobase_stats_on_metadata disables ANALYZE +# http://bugs.mysql.com/57252 +# + +-- source include/have_innodb.inc + +-- disable_query_log +-- disable_result_log + +SET @innodb_stats_on_metadata_orig = @@innodb_stats_on_metadata; + +CREATE TABLE bug57252 (a INT, KEY akey (a)) ENGINE=INNODB; + +BEGIN; +let $i = 10; +while ($i) { + eval INSERT INTO bug57252 VALUES ($i); + dec $i; +} +COMMIT; + +-- enable_result_log + +SET GLOBAL innodb_stats_on_metadata=0; + +# this calls ::info() without HA_STATUS_CONST and so +# index->stat_n_diff_key_vals[] is not copied to the mysql-visible +# rec_per_key +SELECT cardinality FROM information_schema.statistics +WHERE table_name='bug57252' AND index_name='akey'; + +# this calls ::info() with HA_STATUS_CONST and so +# index->stat_n_diff_key_vals[] is copied to the mysql-visible +# rec_per_key at the end; when the bug is present dict_update_statistics() +# is not called beforehand and so index->stat_n_diff_key_vals[] contains +# an outdated data and thus we get an outdated data in the result when the +# bug is present +ANALYZE TABLE bug57252; + +SELECT cardinality FROM information_schema.statistics +WHERE table_name='bug57252' AND index_name='akey'; + +DROP TABLE bug57252; + +SET GLOBAL innodb_stats_on_metadata = @innodb_stats_on_metadata_orig; diff --git a/mysql-test/suite/innodb/t/innodb_bug57255.test b/mysql-test/suite/innodb/t/innodb_bug57255.test index 4f08a9b46bf..27e0a1af1a0 100644 --- a/mysql-test/suite/innodb/t/innodb_bug57255.test +++ b/mysql-test/suite/innodb/t/innodb_bug57255.test @@ -11,6 +11,7 @@ create table C(id int not null auto_increment primary key, f1 int not null, fore insert into A values(1), (2); --disable_query_log +begin; let $i=257; while ($i) { @@ -23,6 +24,7 @@ while ($i) insert into C(f1) values(2); dec $i; } +commit; --enable_query_log # Following Deletes should not report error diff --git a/mysql-test/suite/innodb/t/innodb_bug57904.test b/mysql-test/suite/innodb/t/innodb_bug57904.test new file mode 100755 index 00000000000..1131e24844d --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug57904.test @@ -0,0 +1,27 @@ +# +# Bug #57904 Missing constraint from information schema REFERENTIAL_CONSTRAINTS table +# +-- source include/have_innodb.inc + +CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, + price DECIMAL, PRIMARY KEY(category, id)) ENGINE=INNODB; +CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; +CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, + product_category INT NOT NULL, + product_id INT NOT NULL, + customer_id INT NOT NULL, + PRIMARY KEY(no), + INDEX (product_category, product_id), + FOREIGN KEY (product_category, product_id) + REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, + INDEX (customer_id), + FOREIGN KEY (customer_id) + REFERENCES customer(id) + ) ENGINE=INNODB; + +query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS; + +DROP TABLE product_order; +DROP TABLE product; +DROP TABLE customer; + diff --git a/mysql-test/suite/innodb/t/innodb_bug59307.test b/mysql-test/suite/innodb/t/innodb_bug59307.test new file mode 100644 index 00000000000..31841fa6018 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug59307.test @@ -0,0 +1,32 @@ +-- source include/have_innodb.inc +# Bug #59307 uninitialized value in rw_lock_set_writer_id_and_recursion_flag() +# when Valgrind instrumentation (UNIV_DEBUG_VALGRIND) is not enabled + +CREATE TABLE t1 ( + t1_int INT, + t1_time TIME +) ENGINE=innodb; + +CREATE TABLE t2 ( + t2_int int PRIMARY KEY, + t2_int2 INT +) ENGINE=INNODB; + +INSERT INTO t2 VALUES (); +INSERT INTO t1 VALUES (); + +SELECT * +FROM t1 AS t1a +WHERE NOT EXISTS + (SELECT * + FROM t1 AS t1b + WHERE t1b.t1_int NOT IN + (SELECT t2.t2_int + FROM t2 + WHERE t1b.t1_time LIKE t1b.t1_int + OR t1b.t1_time <> t2.t2_int2 + AND 6=7 + ) +) +; +DROP TABLE t1,t2; diff --git a/mysql-test/suite/innodb/t/innodb_bug59410.test b/mysql-test/suite/innodb/t/innodb_bug59410.test new file mode 100644 index 00000000000..30bb0642679 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug59410.test @@ -0,0 +1,24 @@ +# +# Bug#59410 read uncommitted: unlock row could not find a 3 mode lock on the record +# +-- source include/have_innodb.inc + +# only interested that the following do not produce something like +# InnoDB: Error: unlock row could not find a 2 mode lock on the record +# in the error log + +create table `bug59410_1`(`a` int)engine=innodb; +insert into `bug59410_1` values (1),(2),(3); +select 1 from `bug59410_1` where `a` <> any ( +select 1 from `bug59410_1` where `a` <> 1 for update) +for update; +drop table bug59410_1; + +create table bug59410_2(`a` char(1),`b` int)engine=innodb; +insert into bug59410_2 values('0',0); +set transaction isolation level read uncommitted; +start transaction; +set @a=(select b from bug59410_2 where +(select 1 from bug59410_2 where a group by @a=b) +group by @a:=b); +drop table bug59410_2; diff --git a/mysql-test/suite/innodb/t/innodb_bug59641.test b/mysql-test/suite/innodb/t/innodb_bug59641.test new file mode 100644 index 00000000000..b933abd1d14 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug59641.test @@ -0,0 +1,68 @@ +# Bug #59641 Prepared XA transaction causes shutdown hang after a crash + +-- source include/not_embedded.inc +-- source include/have_innodb.inc + +CREATE TABLE t(a INT PRIMARY KEY, b INT)ENGINE=InnoDB; +INSERT INTO t VALUES(2,2),(4,4),(8,8),(16,16),(32,32); +COMMIT; +XA START '123'; +INSERT INTO t VALUES(1,1); +XA END '123'; +XA PREPARE '123'; + +CONNECT (con1,localhost,root,,); +CONNECTION con1; + +XA START '456'; +INSERT INTO t VALUES(3,47),(5,67); +UPDATE t SET b=2*b WHERE a BETWEEN 5 AND 8; +XA END '456'; +XA PREPARE '456'; + +CONNECT (con2,localhost,root,,); +CONNECTION con2; + +XA START '789'; +UPDATE t SET b=4*a WHERE a=32; +XA END '789'; +XA PREPARE '789'; + +CONNECT (con3,localhost,root,,); +CONNECTION con3; +# The server would issue this warning on restart. +call mtr.add_suppression("Found 3 prepared XA transactions"); + +# Kill the server without sending a shutdown command +-- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +-- shutdown_server 0 +-- source include/wait_until_disconnected.inc + +# Restart the server. +-- exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +-- enable_reconnect +-- source include/wait_until_connected_again.inc +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +SELECT * FROM t; +COMMIT; + +# Shut down the server. This would hang because of the bug. +-- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +-- shutdown_server +-- source include/wait_until_disconnected.inc + +# Restart the server. +-- exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +-- enable_reconnect +-- source include/wait_until_connected_again.inc + +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +SELECT * FROM t; +COMMIT; +XA RECOVER; +XA ROLLBACK '123'; +XA ROLLBACK '456'; +XA COMMIT '789'; +SELECT * FROM t; + +DROP TABLE t; diff --git a/mysql-test/suite/innodb/t/innodb_bug60049-master.opt b/mysql-test/suite/innodb/t/innodb_bug60049-master.opt new file mode 100644 index 00000000000..22a5d4ed221 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug60049-master.opt @@ -0,0 +1 @@ +--innodb_fast_shutdown=0 diff --git a/mysql-test/suite/innodb/t/innodb_bug60049.test b/mysql-test/suite/innodb/t/innodb_bug60049.test new file mode 100644 index 00000000000..ec4e3b8de7e --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug60049.test @@ -0,0 +1,39 @@ +# Bug #60049 Verify that purge leaves no garbage in unique secondary indexes +# This test requires a fresh server start-up and a slow shutdown. +# This was a suspected bug (not a bug). + +-- source include/not_embedded.inc +-- source include/have_innodb.inc + +CREATE TABLE t(a INT)ENGINE=InnoDB; +RENAME TABLE t TO u; +DROP TABLE u; +SELECT @@innodb_fast_shutdown; +let $MYSQLD_DATADIR=`select @@datadir`; + +# Shut down the server +-- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +-- shutdown_server 30 +-- source include/wait_until_disconnected.inc + +# Check the tail of ID_IND (SYS_TABLES.ID) +let IBDATA1=$MYSQLD_DATADIR/ibdata1; +perl; +my $file = $ENV{'IBDATA1'}; +open(FILE, "<$file") || die "Unable to open $file"; +# Read DICT_HDR_TABLE_IDS, the root page number of ID_IND (SYS_TABLES.ID). +seek(FILE, 7*16384+38+36, 0) || die "Unable to seek $file"; +die unless read(FILE, $_, 4) == 4; +my $sys_tables_id_root = unpack("N", $_); +print "Last record of ID_IND root page ($sys_tables_id_root):\n"; +# This should be the last record in ID_IND. Dump it in hexadecimal. +seek(FILE, $sys_tables_id_root*16384 + 152, 0) || die "Unable to seek $file"; +read(FILE, $_, 32) || die "Unable to read $file"; +close(FILE); +print unpack("H*", $_), "\n"; +EOF + +# Restart the server. +-- exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +-- enable_reconnect +-- source include/wait_until_connected_again.inc diff --git a/mysql-test/suite/innodb/t/innodb_bug60196-master.opt b/mysql-test/suite/innodb/t/innodb_bug60196-master.opt new file mode 100755 index 00000000000..c0a1981fa7c --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug60196-master.opt @@ -0,0 +1 @@ +--lower-case-table-names=2
diff --git a/mysql-test/suite/innodb/t/innodb_bug60196.test b/mysql-test/suite/innodb/t/innodb_bug60196.test new file mode 100755 index 00000000000..ea85653f1af --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug60196.test @@ -0,0 +1,157 @@ +# Bug#60196 - Setting lowercase_table_names to 2 on Windows causing +# Foreign Key problems after an engine is restarted. + +# This test case needs InnoDB, a lowercase file system, +# lower-case-table-names=2, and cannot use the embedded server +# because it restarts the server. +--source include/not_embedded.inc +--source include/have_lowercase2.inc +--source include/have_case_insensitive_file_system.inc +--source include/have_innodb.inc + +# +# Create test data. +# +CREATE TABLE Bug_60196_FK1 (Primary_Key INT PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE Bug_60196_FK2 (Primary_Key INT PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE Bug_60196 ( + FK1_Key INT NOT NULL, + FK2_Key INT NOT NULL, + PRIMARY KEY (FK2_Key, FK1_Key), + KEY FK1_Key (FK1_Key), + KEY FK2_Key (FK2_Key), + CONSTRAINT FK_FK1 FOREIGN KEY (FK1_Key) + REFERENCES Bug_60196_FK1 (Primary_Key) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT FK_FK2 FOREIGN KEY (FK2_Key) + REFERENCES Bug_60196_FK2 (Primary_Key) + ON DELETE CASCADE + ON UPDATE CASCADE +) ENGINE=InnoDB; +INSERT INTO Bug_60196_FK1 VALUES (1), (2), (3), (4), (5); +INSERT INTO Bug_60196_FK2 VALUES (1), (2), (3), (4), (5); +INSERT INTO Bug_60196 VALUES (1, 1); +INSERT INTO Bug_60196 VALUES (1, 2); +INSERT INTO Bug_60196 VALUES (1, 3); +--error ER_NO_REFERENCED_ROW_2 +INSERT INTO Bug_60196 VALUES (1, 99); +--error ER_NO_REFERENCED_ROW_2 +INSERT INTO Bug_60196 VALUES (99, 1); + +SELECT * FROM bug_60196_FK1; +SELECT * FROM bug_60196_FK2; +SELECT * FROM bug_60196; + +--echo # Stop server + +# Write file to make mysql-test-run.pl wait for the server to stop +-- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +# Send a shutdown request to the server +-- shutdown_server 10 + +# Call script that will poll the server waiting for it to disapear +-- source include/wait_until_disconnected.inc + +--echo # Restart server. + +# Write file to make mysql-test-run.pl start up the server again +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +# Turn on reconnect +--enable_reconnect + +# Call script that will poll the server waiting for it to be back online again +--source include/wait_until_connected_again.inc + +# Turn off reconnect again +--disable_reconnect + +--echo # +--echo # Try to insert more to the example table with foreign keys. +--echo # Bug60196 causes the foreign key file not to be found after +--echo # the resstart above. +--echo # +SELECT * FROM Bug_60196; +INSERT INTO Bug_60196 VALUES (2, 1); +INSERT INTO Bug_60196 VALUES (2, 2); +INSERT INTO Bug_60196 VALUES (2, 3); +SELECT * FROM Bug_60196; + +--echo +--echo # Clean up. +DROP TABLE Bug_60196; +DROP TABLE Bug_60196_FK1; +DROP TABLE Bug_60196_FK2; + + +# Bug#60309/12356829 +# MYSQL 5.5.9 FOR MAC OSX HAS BUG WITH FOREIGN KEY CONSTRAINTS +# This testcase is different from that for Bug#60196 in that the +# referenced table contains a secondary key. When the engine is +# restarted, the referenced table is opened by the purge thread, +# which does not notice that lower_case_table_names == 2. + +# +# Create test data. +# +CREATE TABLE Bug_60309_FK ( + ID INT PRIMARY KEY, + ID2 INT, + KEY K2(ID2) +) ENGINE=InnoDB; +CREATE TABLE Bug_60309 ( + ID INT PRIMARY KEY, + FK_ID INT, + KEY (FK_ID), + CONSTRAINT FK FOREIGN KEY (FK_ID) REFERENCES Bug_60309_FK (ID) +) ENGINE=InnoDB; + +INSERT INTO Bug_60309_FK (ID, ID2) VALUES (1, 1), (2, 2), (3, 3); +INSERT INTO Bug_60309 VALUES (1, 1); +--error ER_NO_REFERENCED_ROW_2 +INSERT INTO Bug_60309 VALUES (2, 99); + +SELECT * FROM Bug_60309_FK; +SELECT * FROM Bug_60309; + +--echo # Stop server + +# Write file to make mysql-test-run.pl wait for the server to stop +-- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +# Send a shutdown request to the server +-- shutdown_server 10 + +# Call script that will poll the server waiting for it to disapear +-- source include/wait_until_disconnected.inc + +--echo # Restart server. + +# Write file to make mysql-test-run.pl start up the server again +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +# Turn on reconnect +--enable_reconnect + +# Call script that will poll the server waiting for it to be back online again +--source include/wait_until_connected_again.inc + +# Turn off reconnect again +--disable_reconnect + +--echo # +--echo # Try to insert more to the example table with foreign keys. +--echo # Bug60309 causes the foreign key file not to be found after +--echo # the resstart above. +--echo # +SELECT * FROM Bug_60309; +INSERT INTO Bug_60309 VALUES (2, 2); +INSERT INTO Bug_60309 VALUES (3, 3); +SELECT * FROM Bug_60309; + +--echo +--echo # Clean up. +DROP TABLE Bug_60309; +DROP TABLE Bug_60309_FK; diff --git a/mysql-test/suite/innodb/t/innodb_index_large_prefix.test b/mysql-test/suite/innodb/t/innodb_index_large_prefix.test new file mode 100644 index 00000000000..3ed8aa6e096 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_index_large_prefix.test @@ -0,0 +1,271 @@ +# Testcase for worklog #5743: Lift the limit of index key prefixes + +--source include/have_innodb.inc + +let $innodb_file_format_orig=`select @@innodb_file_format`; +let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; +let $innodb_file_format_max_orig=`select @@innodb_file_format_max`; +let $innodb_large_prefix_orig=`select @@innodb_large_prefix`; + +set global innodb_file_format="Barracuda"; +set global innodb_file_per_table=1; +set global innodb_large_prefix=1; + +# Create a table of DYNAMIC format, with a primary index of 1000 bytes in +# size +create table worklog5743(a TEXT not null, primary key (a(1000))) +ROW_FORMAT=DYNAMIC, engine = innodb; + +# Do some insertion and update to excercise the external cache +# code path +insert into worklog5743 values(repeat("a", 20000)); + +# default session, update the table +update worklog5743 set a = (repeat("b", 16000)); + +# Create a secondary index +create index idx on worklog5743(a(2000)); + +# Start a few sessions to do selections on table being updated in default +# session, so it would rebuild the previous version from undo log. +# 1) Default session: Initiate an update on the externally stored column +# 2) Session con1: Select from table with repeated read +# 3) Session con2: Select from table with read uncommitted +# 4) Default session: rollback updates + +begin; +update worklog5743 set a = (repeat("x", 17000)); + +# Start a new session to select the column to force it build +# an earlier version of the clustered index through undo log. So it should +# just see the result of repeat("b", 16000) +select @@session.tx_isolation; +--connect (con1,localhost,root,,) +select a = repeat("x", 17000) from worklog5743; +select a = repeat("b", 16000) from worklog5743; + +# Start another session doing "read uncommitted" query, it +# should see the uncommitted update +--connect (con2,localhost,root,,) +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +select a = repeat("x", 17000) from worklog5743; + +# Roll back the transaction +--connection default +rollback; + +drop table worklog5743; + +# Create a table with only a secondary index has large prefix column +create table worklog5743(a1 int, a2 TEXT not null) +ROW_FORMAT=DYNAMIC, engine = innodb; + +create index idx on worklog5743(a1, a2(2000)); + +insert into worklog5743 values(9, repeat("a", 10000)); + +begin; + +update worklog5743 set a1 = 1000; + +# Do a select from another connection that would use the secondary index +--connection con1 +select @@session.tx_isolation; +explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; +select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; + +# Do read uncommitted in another session, it would show there is no +# row with a1 = 9 +--connection con2 +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; + +--connection default +rollback; + +drop table worklog5743; + +# Create a table with a secondary index has small (50 bytes) prefix column +create table worklog5743(a1 int, a2 TEXT not null) +ROW_FORMAT=DYNAMIC, engine = innodb; + +create index idx on worklog5743(a1, a2(50)); + +insert into worklog5743 values(9, repeat("a", 10000)); + +begin; + +update worklog5743 set a1 = 1000; + +# Do a select from another connection that would use the secondary index +--connection con1 +select @@session.tx_isolation; +explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; +select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; + +# Do read uncommitted in another session, it would show there is no +# row with a1 = 9 +--connection con2 +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; + +--connection default +rollback; + +drop table worklog5743; + +# Create a table of ROW_FORMAT=COMPRESSED format +create table worklog5743_2(a1 int, a2 TEXT not null) +ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; + +create table worklog5743_4(a1 int, a2 TEXT not null) +ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; + +# The maximum overall index record (not prefix) length for this table +# is page_zip_empty_size() / 2, which is 960. "Too big row" error ( +# HA_ERR_TO_BIG_ROW) will be printed if this limit is exceeded. +# Considering other fields and their overhead, the maximum length +# for column a2 is 940 or 941 depending on the zlib version used and +# compressBound() value used in page_zip_empty_size() (please refer +# to Bug #47495 for more detail). +-- error 139 +create index idx1 on worklog5743_2(a1, a2(942)); + +create index idx1 on worklog5743_2(a1, a2(940)); + +# similarly, the maximum index record length for the table is +# 1984. Considering other fields and their overhead, the +# maximum length for column a2 is 1964 or 1965 (please refer +# to Bug #47495 for more detail). +-- error 139 +create index idx1 on worklog5743_4(a1, a2(1966)); + +create index idx1 on worklog5743_4(a1, a2(1964)); + +insert into worklog5743_2 values(9, repeat("a", 10000)); +insert into worklog5743_4 values(9, repeat("a", 10000)); + +begin; + +update worklog5743_2 set a1 = 1000; +update worklog5743_4 set a1 = 1000; + +# Do a select from another connection that would use the secondary index +--connection con1 +select @@session.tx_isolation; +explain select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; +select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; +select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; + +# Do read uncommitted in another session, it would show there is no +# row with a1 = 9 +--connection con2 +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; +select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; + +--connection default +rollback; + +drop table worklog5743_2; +drop table worklog5743_4; + +# Create a table with varchar column, and create index directly on this +# large column (without prefix) +create table worklog5743(a1 int, a2 varchar(3000)) +ROW_FORMAT=DYNAMIC, engine = innodb; + +# Create an index with large column without prefix +create index idx on worklog5743(a1, a2); + +insert into worklog5743 values(9, repeat("a", 3000)); + +begin; + +update worklog5743 set a1 = 1000; + +# Do a select from another connection that would use the secondary index +--connection con1 +select @@session.tx_isolation; +explain select a1 from worklog5743 where a1 = 9; +select a1 from worklog5743 where a1 = 9; + +# Do read uncommitted, it would show there is no row with a1 = 9 +--connection con2 +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +select a1 from worklog5743 where a1 = 9; + +--connection default +rollback; + +drop table worklog5743; + +# Create a table with old format, and the limit is 768 bytes. +-- error ER_INDEX_COLUMN_TOO_LONG +create table worklog5743(a TEXT not null, primary key (a(1000))) +engine = innodb; + +create table worklog5743(a TEXT) engine = innodb; + +# Excercise the column length check in ha_innobase::add_index() +-- error ER_INDEX_COLUMN_TOO_LONG +create index idx on worklog5743(a(1000)); + +# This should be successful +create index idx on worklog5743(a(725)); + +# Perform some DMLs +insert into worklog5743 values(repeat("a", 20000)); + +begin; +insert into worklog5743 values(repeat("b", 20000)); +update worklog5743 set a = (repeat("x", 25000)); + +# Start a new session to select the table to force it build +# an earlier version of the cluster index through undo log +select @@session.tx_isolation; +--connection con1 +select a = repeat("a", 20000) from worklog5743; + +--connection con2 +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +select a = repeat("x", 25000) from worklog5743; + +--connection default +rollback; + +drop table worklog5743; + +# Some border line test on the column length. +# We have a limit of 3072 bytes for Barracuda table +create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC, engine = innodb; + +# Length exceeds maximum supported key length, will auto-truncated to 3072 +create index idx on worklog5743(a(3073)); + +create index idx2 on worklog5743(a(3072)); + +show create table worklog5743; + +drop table worklog5743; + +# We have a limit of 767 bytes for Antelope table +create table worklog5743(a TEXT not null) engine = innodb; + +-- error ER_INDEX_COLUMN_TOO_LONG +create index idx on worklog5743(a(768)); + +create index idx2 on worklog5743(a(767)); + +drop table worklog5743; + +eval SET GLOBAL innodb_file_format=$innodb_file_format_orig; +eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; +eval SET GLOBAL innodb_file_format_max=$innodb_file_format_max_orig; +eval SET GLOBAL innodb_large_prefix=$innodb_large_prefix_orig; diff --git a/mysql-test/suite/innodb/t/innodb_information_schema.test b/mysql-test/suite/innodb/t/innodb_information_schema.test index 934642dcb40..05fb67edf75 100644 --- a/mysql-test/suite/innodb/t/innodb_information_schema.test +++ b/mysql-test/suite/innodb/t/innodb_information_schema.test @@ -117,11 +117,29 @@ SELECT * FROM ```t'\"_str` WHERE c1 = '4' FOR UPDATE; # executes before some of them, resulting in less than expected number # of rows being selected from innodb_locks. If there is a bug and there # are no 14 rows in innodb_locks then this test will fail with timeout. -let $count = 14; -let $table = INFORMATION_SCHEMA.INNODB_LOCKS; --- source include/wait_until_rows_count.inc -# the above enables the query log, re-disable it --- disable_query_log +# Notice that if we query INNODB_LOCKS more often than once per 0.1 sec +# then its contents will never change because the cache from which it is +# filled is updated only if it has not been read for 0.1 seconds. See +# CACHE_MIN_IDLE_TIME_US in trx/trx0i_s.c. +let $cnt=10; +while ($cnt) +{ + let $success=`SELECT COUNT(*) = 14 FROM INFORMATION_SCHEMA.INNODB_LOCKS`; + if ($success) + { + let $cnt=0; + } + if (!$success) + { + real_sleep 0.2; + dec $cnt; + } +} +if (!$success) +{ + -- echo Timeout waiting for rows in INNODB_LOCKS to appear +} + SELECT lock_mode, lock_type, lock_table, lock_index, lock_rec, lock_data FROM INFORMATION_SCHEMA.INNODB_LOCKS ORDER BY lock_data; diff --git a/mysql-test/suite/innodb/t/innodb_multi_update.test b/mysql-test/suite/innodb/t/innodb_multi_update.test index 6393671d358..e15f7470960 100644 --- a/mysql-test/suite/innodb/t/innodb_multi_update.test +++ b/mysql-test/suite/innodb/t/innodb_multi_update.test @@ -26,3 +26,12 @@ select * from bug38999_1; select * from bug38999_2; drop table bug38999_1,bug38999_2; + +--echo # +--echo # Bug#54475 improper error handling causes cascading crashing failures in innodb/ndb +--echo # +CREATE TABLE t1(f1 INT) ENGINE=INNODB; +INSERT INTO t1 VALUES(1); +--error ER_OPERAND_COLUMNS +UPDATE (SELECT ((SELECT 1 FROM t1), 1) FROM t1 WHERE (SELECT 1 FROM t1)) x, (SELECT 1) AS d SET d.f1 = 1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test index ef43a192d52..2f983436177 100644 --- a/mysql-test/suite/innodb/t/innodb_mysql.test +++ b/mysql-test/suite/innodb/t/innodb_mysql.test @@ -154,14 +154,14 @@ INSERT INTO t2 VALUES (3,2); SET AUTOCOMMIT = 0; START TRANSACTION; ---error ER_ROW_IS_REFERENCED_2 +--error ER_TRUNCATE_ILLEGAL_FK TRUNCATE TABLE t1; SELECT * FROM t1; COMMIT; SELECT * FROM t1; START TRANSACTION; ---error ER_ROW_IS_REFERENCED_2 +--error ER_TRUNCATE_ILLEGAL_FK TRUNCATE TABLE t1; SELECT * FROM t1; ROLLBACK; @@ -173,13 +173,14 @@ START TRANSACTION; SELECT * FROM t1; COMMIT; ---error ER_ROW_IS_REFERENCED_2 +--error ER_TRUNCATE_ILLEGAL_FK TRUNCATE TABLE t1; SELECT * FROM t1; DELETE FROM t2 WHERE id = 3; START TRANSACTION; SELECT * FROM t1; +--error ER_TRUNCATE_ILLEGAL_FK TRUNCATE TABLE t1; ROLLBACK; SELECT * FROM t1; @@ -278,9 +279,9 @@ SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; --echo ** error handling inside a row iteration. --echo ** DROP TRIGGER trg; -TRUNCATE TABLE t1; -TRUNCATE TABLE t2; -TRUNCATE TABLE t3; +DELETE FROM t1; +DELETE FROM t2; +DELETE FROM t3; INSERT INTO t1 VALUES (1),(2),(3),(4); INSERT INTO t3 VALUES (1),(2),(3),(4); @@ -307,9 +308,9 @@ DROP TRIGGER trg; --echo ** --echo ** Induce an error midway through an AFTER-trigger --echo ** -TRUNCATE TABLE t4; -TRUNCATE TABLE t1; -TRUNCATE TABLE t3; +DELETE FROM t4; +DELETE FROM t1; +DELETE FROM t3; INSERT INTO t1 VALUES (1),(2),(3),(4); INSERT INTO t3 VALUES (1),(2),(3),(4); delimiter ||; @@ -839,6 +840,65 @@ CREATE INDEX b ON t1(a,b,c,d); DROP TABLE t1; --echo End of 5.1 tests +--echo # + +--echo # +--echo # Bug#55826: create table .. select crashes with when KILL_BAD_DATA +--echo # is returned +--echo # + +CREATE TABLE t1(a INT) ENGINE=innodb; +INSERT INTO t1 VALUES (0); +SET SQL_MODE='STRICT_ALL_TABLES'; +--error ER_TRUNCATED_WRONG_VALUE +CREATE TABLE t2 + SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`; +DROP TABLE t1; +SET SQL_MODE=DEFAULT; + +--echo # +--echo # Bug#56862 Execution of a query that uses index merge returns a wrong result +--echo # + +CREATE TABLE t1 ( + pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, + a int, + b int, + INDEX idx(a)) +ENGINE=INNODB; + +INSERT INTO t1(a,b) VALUES + (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), + (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), + (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700), + (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000); +INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1; +INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1; +INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1 VALUES (1000000, 0, 0); + +SET SESSION sort_buffer_size = 1024*36; + +EXPLAIN +SELECT COUNT(*) FROM + (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) + WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; + +SELECT COUNT(*) FROM + (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) + WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; + +SET SESSION sort_buffer_size = DEFAULT; + +DROP TABLE t1; --echo # --echo # Bug#668644: HAVING + ORDER BY @@ -896,6 +956,28 @@ drop table t2, t1; --echo # +--echo # Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE:: +--echo # UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". +--echo # +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB; +INSERT INTO t1 VALUES (1); +LOCK TABLES t1 READ; +--echo # Even though temporary table was locked for READ we +--echo # still allow writes to it to be compatible with MyISAM. +--echo # This is possible since due to fact that temporary tables +--echo # are specific to connection and therefore locking for them +--echo # is irrelevant. +UPDATE t1 SET c = 5; +UNLOCK TABLES; +DROP TEMPORARY TABLE t1; + +--echo End of 5.1 tests + + +--echo # --echo # Bug#44613 SELECT statement inside FUNCTION takes a shared lock --echo # diff --git a/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test b/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test new file mode 100644 index 00000000000..2bc89bf05d2 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test @@ -0,0 +1,1339 @@ +######## suite/innodb/t/innodb_prefix_index_liftedlimit.test ########## +# # +# Testcase for worklog WL#5743: Lift the limit of index key prefixes # +# Accorrding to WL#5743 - prefix index limit is increased from 767 # +# to 3072 for innodb. This change is applicable with Barracuda file # +# format. # +# All sub-test in this file focus on prefix index along with other # +# operations # +# # +# # +# Creation: # +# 2011-05-19 Implemented this test as part of WL#5743 # +# # +###################################################################### + + +--source include/have_innodb.inc +# Save innodb variables +let $innodb_file_format_orig=`select @@innodb_file_format`; +let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; +let $innodb_file_format_max_orig=`select @@innodb_file_format_max`; +let $innodb_large_prefix_orig=`select @@innodb_large_prefix`; + +# Set Innodb file format as feature works for Barracuda file format +set global innodb_file_format="Barracuda"; +set global innodb_file_per_table=1; +set global innodb_large_prefix=1; + +-- disable_warnings +DROP TABLE IF EXISTS worklog5743; +-- enable_warnings +#------------------------------------------------------------------------------ +# Prefix index with VARCHAR data type , primary/secondary index and DML ops +CREATE TABLE worklog5743 ( +col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , +PRIMARY KEY (col_1_varchar(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); +# check IS +SELECT COLUMN_NAME,INDEX_NAME,SUB_PART,INDEX_TYPE FROM +INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'worklog5743' ; +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000) +WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + + +#------------------------------------------------------------------------------ +# Prefix index with TEXT data type , primary/secondary index and DML ops +CREATE TABLE worklog5743 ( +col_1_text TEXT (4000) , col_2_text TEXT (4000) , +PRIMARY KEY (col_1_text(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +CREATE INDEX prefix_idx ON worklog5743(col_1_text (3072)); +# check IS +SELECT COLUMN_NAME,INDEX_NAME,SUB_PART,INDEX_TYPE FROM +INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'worklog5743' ; +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) +WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743 WHERE col_1_text = REPEAT("b", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + +#------------------------------------------------------------------------------ +# Prefix index with MEDIUMTEXT data type , primary/secondary index and DML ops +CREATE TABLE worklog5743 ( +col_1_mediumtext MEDIUMTEXT , col_2_mediumtext MEDIUMTEXT , +PRIMARY KEY (col_1_mediumtext(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +CREATE INDEX prefix_idx ON worklog5743(col_1_mediumtext (3072)); +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_mediumtext = REPEAT("a", 4000),col_2_mediumtext = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_mediumtext = REPEAT("c", 4000) +WHERE col_1_mediumtext = REPEAT("a", 4000) +AND col_2_mediumtext = REPEAT("o", 4000); +SELECT col_1_mediumtext = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_mediumtext = REPEAT("c", 4000) +AND col_2_mediumtext = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743 WHERE col_1_mediumtext = REPEAT("b", 4000); +SELECT col_1_mediumtext = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + + +#------------------------------------------------------------------------------ +# Prefix index with LONGTEXT data type , primary/secondary index and DML ops +CREATE TABLE worklog5743 ( +col_1_longtext LONGTEXT , col_2_longtext LONGTEXT , +PRIMARY KEY (col_1_longtext(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +CREATE INDEX prefix_idx ON worklog5743(col_1_longtext (3072)); +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_longtext = REPEAT("a", 4000) , col_2_longtext = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_longtext = REPEAT("c", 4000) +WHERE col_1_longtext = REPEAT("a", 4000) +AND col_2_longtext = REPEAT("o", 4000); +SELECT col_1_longtext = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_longtext = REPEAT("c", 4000) +AND col_2_longtext = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743 WHERE col_1_longtext = REPEAT("b", 4000); +SELECT col_1_longtext = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + + +#------------------------------------------------------------------------------ +# Prefix index with BLOB data type , primary/secondary index and DML ops +CREATE TABLE worklog5743 ( +col_1_blob BLOB (4000) , col_2_blob BLOB (4000) , +PRIMARY KEY (col_1_blob(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +CREATE INDEX prefix_idx ON worklog5743(col_1_blob (3072)); +# check IS +SELECT COLUMN_NAME,INDEX_NAME,SUB_PART,INDEX_TYPE FROM +INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'worklog5743' ; +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_blob = REPEAT("a", 4000) , col_2_blob = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_blob = REPEAT("c", 4000) +WHERE col_1_blob = REPEAT("a", 4000) AND col_2_blob = REPEAT("o", 4000); +SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_blob = REPEAT("c", 4000) AND col_2_blob = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743 WHERE col_1_blob = REPEAT("b", 4000); +SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + + +#------------------------------------------------------------------------------ +# Prefix index with MEDIUMBLOB data type , primary/secondary index and DML ops +CREATE TABLE worklog5743 ( +col_1_mediumblob MEDIUMBLOB , col_2_mediumblob MEDIUMBLOB , +PRIMARY KEY (col_1_mediumblob(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +CREATE INDEX prefix_idx ON worklog5743(col_1_mediumblob (3072)); +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_mediumblob = REPEAT("a", 4000),col_2_mediumblob = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_mediumblob = REPEAT("c", 4000) +WHERE col_1_mediumblob = REPEAT("a", 4000) +AND col_2_mediumblob = REPEAT("o", 4000); +SELECT col_1_mediumblob = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_mediumblob = REPEAT("c", 4000) +AND col_2_mediumblob = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743 WHERE col_1_mediumblob = REPEAT("b", 4000); +SELECT col_1_mediumblob = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + +#------------------------------------------------------------------------------ +# Prefix index with LONGBLOB data type , primary/secondary index and DML ops +CREATE TABLE worklog5743 ( +col_1_longblob LONGBLOB , col_2_longblob LONGBLOB , +PRIMARY KEY (col_1_longblob(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +CREATE INDEX prefix_idx ON worklog5743(col_1_longblob (3072)); +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_longblob = REPEAT("a", 4000) , col_2_longblob = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_longblob = REPEAT("c", 4000) +WHERE col_1_longblob = REPEAT("a", 4000) +AND col_2_longblob = REPEAT("o", 4000); +SELECT col_1_longblob = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_longblob = REPEAT("c", 4000) +AND col_2_longblob = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743 WHERE col_1_longblob = REPEAT("b", 4000); +SELECT col_1_longblob = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + +#------------------------------------------------------------------------------ +# Prefix index with VARBINARY data type , primary/secondary index and DML ops +CREATE TABLE worklog5743 ( +col_1_varbinary VARBINARY (4000) , col_2_varbinary VARBINARY (4000) , +PRIMARY KEY (col_1_varbinary(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (3072)); +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varbinary = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) +WHERE col_1_varbinary = REPEAT("a", 4000) +AND col_2_varbinary = REPEAT("o", 4000); +SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_varbinary = REPEAT("c", 4000) +AND col_2_varbinary = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("b", 4000); +SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + +#------------------------------------------------------------------------------ +# Prefix index with CHAR data type , composite index and DML ops +CREATE TABLE worklog5743 (col_1_char CHAR (255) , col_2_char CHAR (255), +col_3_char CHAR (255), col_4_char CHAR (255),col_5_char CHAR (255), +col_6_char CHAR (255), col_7_char CHAR (255),col_8_char CHAR (255), +col_9_char CHAR (255), col_10_char CHAR (255),col_11_char CHAR (255), +col_12_char CHAR (255), col_13_char CHAR (255),col_14_char CHAR (255) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 255) , REPEAT("o", 255), +REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255) +); +# Create index with total prefix index length = 3072 +CREATE INDEX prefix_idx ON worklog5743(col_1_char(250),col_2_char(250), +col_3_char(250),col_4_char(250),col_5_char(250),col_6_char(250), +col_7_char(250),col_8_char(250),col_9_char(250),col_10_char(250), +col_11_char(250),col_12_char(250),col_13_char(72) +); +INSERT INTO worklog5743 VALUES(REPEAT("b", 255) , REPEAT("p", 255), +REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255) +); +SELECT col_1_char = REPEAT("a", 255) , col_2_char = REPEAT("o", 255) FROM worklog5743; +UPDATE worklog5743 SET col_1_char = REPEAT("c", 255) +WHERE col_1_char = REPEAT("a", 255) AND col_2_char = REPEAT("o", 255); +SELECT col_1_char = REPEAT("c", 255) FROM worklog5743 +WHERE col_1_char = REPEAT("c", 255) AND col_2_char = REPEAT("o", 255); +INSERT INTO worklog5743 VALUES(REPEAT("a", 255) , REPEAT("o", 255), +REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255) +); +DELETE FROM worklog5743 WHERE col_1_char = REPEAT("b", 255); +SELECT col_1_char = REPEAT("c", 255) FROM worklog5743; +DROP TABLE worklog5743; + +#------------------------------------------------------------------------------ +# Prefix index with BINARY data type , composite index and DML ops +CREATE TABLE worklog5743 (col_1_binary BINARY (255) , col_2_binary BINARY (255), +col_3_binary BINARY(255),col_4_binary BINARY (255),col_5_binary BINARY (255), +col_6_binary BINARY(255),col_7_binary BINARY (255),col_8_binary BINARY (255), +col_9_binary BINARY(255),col_10_binary BINARY (255),col_11_binary BINARY (255), +col_12_binary BINARY(255),col_13_binary BINARY (255),col_14_binary BINARY (255) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 255) , REPEAT("o", 255), +REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255) +); +# Create index with total prefix index length = 3072 +CREATE INDEX prefix_idx ON worklog5743(col_1_binary (250),col_2_binary (250), +col_3_binary (250),col_4_binary (250),col_5_binary (250), +col_6_binary (250),col_7_binary (250),col_8_binary (250), +col_9_binary (250),col_10_binary (250),col_11_binary (250), +col_12_binary (250),col_13_binary (72) +); +INSERT INTO worklog5743 VALUES(REPEAT("b", 255) , REPEAT("p", 255), +REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255) +); +SELECT col_1_binary = REPEAT("a", 255) , col_2_binary = REPEAT("o", 255) FROM worklog5743; +UPDATE worklog5743 SET col_1_binary = REPEAT("c", 255) +WHERE col_1_binary = REPEAT("a", 255) +AND col_2_binary = REPEAT("o", 255); +SELECT col_1_binary = REPEAT("c", 255) FROM worklog5743 +WHERE col_1_binary = REPEAT("c", 255) +AND col_2_binary = REPEAT("o", 255); +INSERT INTO worklog5743 VALUES(REPEAT("a", 255) , REPEAT("o", 255), +REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), +REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255) +); +DELETE FROM worklog5743 WHERE col_1_binary = REPEAT("b", 255); +SELECT col_1_binary = REPEAT("c", 255) FROM worklog5743; +DROP TABLE worklog5743; + +#------------------------------------------------------------------------------ +# Prefix index with VARCHAR data type , primary/seconday index , DML ops +# and COMPRESSED row format. KEY_BLOCK_SIZE is varied as 2 , 4 , 8. + +# With KEY_BLOCK_SIZE = 2,prefix index limit comes around ~948 for following +CREATE TABLE worklog5743_key2 ( +col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , +PRIMARY KEY (col_1_varchar(948)) +) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; +INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +#CREATE INDEX prefix_idx ON worklog5743_key2 (col_1_varchar (767)); +INSERT INTO worklog5743_key2 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) +FROM worklog5743_key2; +UPDATE worklog5743_key2 SET col_1_varchar = REPEAT("c", 4000) +WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743_key2 +WHERE col_2_varchar = REPEAT("o", 4000); +INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743_key2 WHERE col_1_varchar = REPEAT("b", 4000); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743_key2; +DROP TABLE worklog5743_key2; + +# With KEY_BLOCK_SIZE = 4,prefix index limit comes around ~1964 for following +CREATE TABLE worklog5743_key4 ( +col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , +PRIMARY KEY (col_1_varchar(1964)) +) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; +INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +#CREATE INDEX prefix_idx ON worklog5743_key4 (col_1_varchar (767)); +INSERT INTO worklog5743_key4 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) +FROM worklog5743_key4; +UPDATE worklog5743_key4 SET col_1_varchar = REPEAT("c", 4000) +WHERE col_1_varchar = REPEAT("a", 4000) +AND col_2_varchar = REPEAT("o", 4000); +SELECT col_1_varchar = REPEAT("b", 3500) FROM worklog5743_key4 +WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); +INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743_key4 WHERE col_1_varchar = REPEAT("b", 4000); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743_key4; +DROP TABLE worklog5743_key4; + +# With KEY_BLOCK_SIZE = 8,prefix index limit comes around ~3072 for following +CREATE TABLE worklog5743_key8 ( +col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , +PRIMARY KEY (col_1_varchar(3072)) +) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, engine = innodb; +INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +#CREATE INDEX prefix_idx ON worklog5743_key8 (col_1_varchar (767)); +INSERT INTO worklog5743_key8 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) +FROM worklog5743_key8; +UPDATE worklog5743_key8 SET col_1_varchar = REPEAT("c", 4000) +WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); +SELECT col_1_varchar = REPEAT("b", 3500) FROM worklog5743_key8 +WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); +INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743_key8 WHERE col_1_varchar = REPEAT("b", 4000); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743_key8; +DROP TABLE worklog5743_key8; + +# Prefix index with TEXT data type , primary/seconday index , DML ops +# and COMPRESSED row format. KEY_BLOCK_SIZE is varied as 2 , 4 , 8. + +# With KEY_BLOCK_SIZE = 2,prefix index limit comes around ~948 for following +CREATE TABLE worklog5743_key2 ( +col_1_text TEXT (4000) , col_2_text TEXT (4000) , +PRIMARY KEY (col_1_text(948)) +) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; +INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +#CREATE INDEX prefix_idx ON worklog5743_key2 (col_1_text (767)); +INSERT INTO worklog5743_key2 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) +FROM worklog5743_key2; +UPDATE worklog5743_key2 SET col_1_text = REPEAT("c", 4000) +WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); +SELECT col_1_text = REPEAT("b", 3500) FROM worklog5743_key2 +WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); +INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743_key2 WHERE col_1_text = REPEAT("b", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743_key2; +DROP TABLE worklog5743_key2; + +# With KEY_BLOCK_SIZE = 4,prefix index limit comes around ~1964 for following +CREATE TABLE worklog5743_key4 ( +col_1_text TEXT (4000) , col_2_text TEXT (4000) , +PRIMARY KEY (col_1_text(1964)) +) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; +INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +#CREATE INDEX prefix_idx ON worklog5743_key4 (col_1_text (767)); +INSERT INTO worklog5743_key4 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) +FROM worklog5743_key4; +UPDATE worklog5743_key4 SET col_1_text = REPEAT("c", 4000) +WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); +SELECT col_1_text = REPEAT("b", 3500) FROM worklog5743_key4 +WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); +INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743_key4 WHERE col_1_text = REPEAT("b", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743_key4; +DROP TABLE worklog5743_key4; + +# With KEY_BLOCK_SIZE = 8,prefix index limit comes around ~3072 for following +CREATE TABLE worklog5743_key8 ( +col_1_text TEXT (4000) , col_2_text TEXT (4000) , +PRIMARY KEY (col_1_text(3072)) +) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, engine = innodb; +INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +#CREATE INDEX prefix_idx ON worklog5743_key8 (col_1_text (767)); +INSERT INTO worklog5743_key8 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) +FROM worklog5743_key8; +UPDATE worklog5743_key8 SET col_1_text = REPEAT("c", 4000) +WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); +SELECT col_1_text = REPEAT("b", 3500) FROM worklog5743_key8 +WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); +INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743_key8 WHERE col_1_text = REPEAT("b", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743_key8; +DROP TABLE worklog5743_key8; + +# Prefix index with BLOB data type , primary/seconday index , DML ops +# and COMPRESSED row format. KEY_BLOCK_SIZE is varied as 2 , 4 , 8. + +# With KEY_BLOCK_SIZE = 2,prefix index limit comes around ~948 for following +CREATE TABLE worklog5743_key2 ( +col_1_blob BLOB (4000) , col_2_blob BLOB (4000) , +PRIMARY KEY (col_1_blob(948)) +) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; +INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +#CREATE INDEX prefix_idx ON worklog5743_key2 (col_1_blob (767)); +INSERT INTO worklog5743_key2 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_blob = REPEAT("a", 4000) , col_2_blob = REPEAT("o", 4000) +FROM worklog5743_key2; +UPDATE worklog5743_key2 SET col_1_blob = REPEAT("c", 4000) +WHERE col_1_blob = REPEAT("a", 4000) AND col_2_blob = REPEAT("o", 4000); +SELECT col_1_blob = REPEAT("b", 3500) FROM worklog5743_key2 +WHERE col_1_blob = REPEAT("c", 4000) AND col_2_blob = REPEAT("o", 4000); +INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743_key2 WHERE col_1_blob = REPEAT("b", 4000); +SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743_key2; +DROP TABLE worklog5743_key2; + +# With KEY_BLOCK_SIZE = 4,prefix index limit comes around ~1964 for following +CREATE TABLE worklog5743_key4 ( +col_1_blob BLOB (4000) , col_2_blob BLOB (4000) , +PRIMARY KEY (col_1_blob(1964)) +) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; +INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +#CREATE INDEX prefix_idx ON worklog5743_key4 (col_1_blob (767)); +INSERT INTO worklog5743_key4 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_blob = REPEAT("a", 4000) , col_2_blob = REPEAT("o", 4000) +FROM worklog5743_key4; +UPDATE worklog5743_key4 SET col_1_blob = REPEAT("c", 4000) +WHERE col_1_blob = REPEAT("a", 4000) AND col_2_blob = REPEAT("o", 4000); +SELECT col_1_blob = REPEAT("b", 3500) FROM worklog5743_key4 +WHERE col_1_blob = REPEAT("c", 4000) AND col_2_blob = REPEAT("o", 4000); +INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743_key4 WHERE col_1_blob = REPEAT("b", 4000); +SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743_key4; +DROP TABLE worklog5743_key4; + +# With KEY_BLOCK_SIZE = 8,prefix index limit comes around ~3072 for following +CREATE TABLE worklog5743_key8 ( +col_1_blob BLOB (4000) , col_2_blob BLOB (4000) , +PRIMARY KEY (col_1_blob(3072)) +) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, engine = innodb; +INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +#CREATE INDEX prefix_idx ON worklog5743_key8 (col_1_blob (767)); +INSERT INTO worklog5743_key8 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_blob = REPEAT("a", 4000) , col_2_blob = REPEAT("o", 4000) +FROM worklog5743_key8; +UPDATE worklog5743_key8 SET col_1_blob = REPEAT("c", 4000) +WHERE col_1_blob = REPEAT("a", 4000) AND col_2_blob = REPEAT("o", 4000); +SELECT col_1_blob = REPEAT("b", 3500) FROM worklog5743_key8 +WHERE col_1_blob = REPEAT("c", 4000) AND col_2_blob = REPEAT("o", 4000); +INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743_key8 WHERE col_1_blob = REPEAT("b", 4000); +SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743_key8; +DROP TABLE worklog5743_key8; + + +#------------------------------------------------------------------------------ +# Create mutiple prefix index. We can not create prefix index length > 16K +# as index is written in undo log page which of 16K size. +# So we can create max 5 prefix index of length 3072 on table +CREATE TABLE worklog5743 ( +col_1_varbinary VARBINARY (4000) , col_2_varchar VARCHAR (4000) , +col_3_text TEXT (4000), col_4_blob BLOB (4000),col_5_text TEXT (4000), +col_6_varchar VARCHAR (4000), col_7_binary BINARY (255) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), +REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), +REPEAT("a", 4000) , REPEAT("a", 255) +); + +# Update hangs if we create following 5 indexes. Uncomment them once its fix +# Bug#12547647 - UPDATE LOGGING COULD EXCEED LOG PAGE SIZE +#CREATE INDEX prefix_idx1 ON worklog5743(col_1_varbinary (3072)); +#CREATE INDEX prefix_idx2 ON worklog5743(col_2_varchar (3072)); +#CREATE INDEX prefix_idx3 ON worklog5743(col_3_text (3072)); +#CREATE INDEX prefix_idx4 ON worklog5743(col_4_blob (3072)); +#CREATE INDEX prefix_idx5 ON worklog5743(col_5_text (3072)); + +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000), +REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), +REPEAT("a", 4000) , REPEAT("a", 255) +); +SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) +WHERE col_1_varbinary = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); +SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_varbinary = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), +REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), +REPEAT("a", 4000) , REPEAT("a", 255) +); +DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("b", 4000); +SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + + +#------------------------------------------------------------------------------ +# Create mutiple prefix index. We can not create prefix index length > 16K as +# we write in undo log page which of 16K size. +# so we can create max 5 prefix index of length 3072 on table. +# Similar to above case but with transactions +CREATE TABLE worklog5743 ( +col_1_varbinary VARBINARY (4000) , col_2_varchar VARCHAR (4000) , +col_3_text TEXT (4000), col_4_blob BLOB (4000),col_5_text TEXT (4000), +col_6_varchar VARCHAR (4000), col_7_binary BINARY (255) +) ROW_FORMAT=DYNAMIC, engine = innodb; + + +# Update hangs if we create following 5 indexes. Uncomment them once its fix +CREATE INDEX prefix_idx1 ON worklog5743(col_1_varbinary (3072)); +CREATE INDEX prefix_idx2 ON worklog5743(col_2_varchar (3072)); +CREATE INDEX prefix_idx3 ON worklog5743(col_3_text (3072)); +CREATE INDEX prefix_idx4 ON worklog5743(col_4_blob (3072)); +CREATE INDEX prefix_idx5 ON worklog5743(col_5_text (3072)); + +START TRANSACTION; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), +REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), +REPEAT("a", 4000) , REPEAT("a", 255) +); +SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) +FROM worklog5743; +ROLLBACK; +START TRANSACTION; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), +REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), +REPEAT("a", 4000) , REPEAT("a", 255) +); +COMMIT; +SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) +FROM worklog5743; + +START TRANSACTION; +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000), +REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), +REPEAT("a", 4000) , REPEAT("a", 255) +); +ROLLBACK; +# Uncomment Update fater Bug#12547647 is fixed - UPDATE LOGGING COULD EXCEED LOG PAGE SIZE +# Bug#12547647 - UPDATE LOGGING COULD EXCEED LOG PAGE SIZE +#UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) +#WHERE col_1_varbinary = REPEAT("a", 4000) +#AND col_2_varchar = REPEAT("o", 4000); +SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_varbinary = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), +REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), +REPEAT("a", 4000) , REPEAT("a", 255) +); +DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("b", 4000); +SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + +#------------------------------------------------------------------------------ +# Prefix index with utf8 charset +# utf8 charcter takes 3 bytes in mysql so prefix index limit is 3072/3 = 1024 +CREATE TABLE worklog5743 ( +col_1_text TEXT (4000) CHARACTER SET 'utf8', +col_2_text TEXT (4000) CHARACTER SET 'utf8', +PRIMARY KEY (col_1_text(1024)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +CREATE INDEX prefix_idx ON worklog5743(col_1_text (1024)); +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) FROM worklog5743; +UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) +WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743 WHERE col_1_text = REPEAT("b", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + +# Prefix index with utf8 charset + varchar. +# For varchar we also log the column itself as oppose of TEXT so it error +# with limit 1024 due to overhead. +-- error 1118 +CREATE TABLE worklog5743 (col_1_varchar VARCHAR (4000) CHARACTER SET 'utf8', +col_2_varchar VARCHAR (4000) CHARACTER SET 'utf8' , +PRIMARY KEY (col_1_varchar(1024)) +) ROW_FORMAT=DYNAMIC, engine = innodb; + +#------------------------------------------------------------------------------ +# prefinx index on utf8 charset with transaction +CREATE TABLE worklog5743 ( +col_1_varbinary VARBINARY (4000) , +col_2_varchar VARCHAR (4000) CHARACTER SET 'utf8', +col_3_text TEXT (4000) CHARACTER SET 'utf8', +col_4_blob BLOB (4000),col_5_text TEXT (4000), +col_6_varchar VARCHAR (4000), col_7_binary BINARY (255) +) ROW_FORMAT=DYNAMIC, engine = innodb; + + +CREATE INDEX prefix_idx2 ON worklog5743(col_2_varchar (500)); +CREATE INDEX prefix_idx3 ON worklog5743(col_3_text (500)); + +START TRANSACTION; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), +REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), +REPEAT("a", 4000) , REPEAT("a", 255) +); +SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) +FROM worklog5743; +ROLLBACK; +START TRANSACTION; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), +REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), +REPEAT("a", 4000) , REPEAT("a", 255) +); +COMMIT; +SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) +FROM worklog5743; + +START TRANSACTION; +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000), +REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), +REPEAT("a", 4000) , REPEAT("a", 255) +); +ROLLBACK; +SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_varbinary = REPEAT("c", 4000) +AND col_2_varchar = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), +REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), +REPEAT("a", 4000) , REPEAT("a", 255) +); +DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("b", 4000); +SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + + +#------------------------------------------------------------------------------ +# Prefix index with utf8 charset on TEXT data type with actual utf8 character +# like "स" and "क" +CREATE TABLE worklog5743 ( +col_1_text TEXT (4000) CHARACTER SET 'utf8', +col_2_text TEXT (4000) , +PRIMARY KEY (col_1_text(1024)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("स", 4000) , REPEAT("o", 4000)); +CREATE INDEX prefix_idx ON worklog5743(col_1_text (1024)); +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_text = REPEAT("स", 4000) , col_2_text = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_text = REPEAT("क", 4000) +WHERE col_1_text = REPEAT("स", 4000) AND col_2_text = REPEAT("o", 4000); +SELECT col_1_text = REPEAT("क", 4000) FROM worklog5743 +WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743 WHERE col_1_text = REPEAT("b", 4000); +SELECT col_1_text = REPEAT("क", 4000) FROM worklog5743; +DROP TABLE worklog5743; + + +#------------------------------------------------------------------------------ +# Prefix index with transaction when 2 client are ruuning there transaction +# in different sessions.With ISOLATION LEVEL as REPEATABLE READ and +# READ UNCOMMITTED. +CREATE TABLE worklog5743 ( +col_1_text TEXT(4000) , col_2_text TEXT(4000) , +PRIMARY KEY (col_1_text(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 200) , REPEAT("o", 200)); +SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; + +--echo "In connection 1" +--connect (con1,localhost,root,,) +SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; +SELECT COUNT(*) FROM worklog5743; + + +--echo "In connection 2" +--connect (con2,localhost,root,,) +START TRANSACTION; +INSERT INTO worklog5743 VALUES(REPEAT("b", 200) , REPEAT("o", 200)); +# Uncomment after Bug#12552164 - TRANSACTION CAN NOT SEE OLD VERSION ROWS THAT +# BEING UPDATED +#UPDATE worklog5743 SET col_1_varchar = REPEAT("d", 200) WHERE col_1_varchar = +#REPEAT("a", 200) AND col_2_varchar = REPEAT("o", 200); +SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; + + +--echo "In connection 1" +--connection con1 +select @@session.tx_isolation; +SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; + +START TRANSACTION; + +SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; +SELECT COUNT(*) FROM worklog5743; + +--echo "In connection 2" +--connection con2 +COMMIT; +# Wait for commit +let $wait_condition=SELECT COUNT(*)=0 FROM information_schema.processlist +WHERE info='COMMIT'; +--source include/wait_condition.inc + +--echo "In connection 1" +--connection con1 +SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; +SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; +SELECT COUNT(*) FROM worklog5743; +COMMIT; + +--connection default +DROP TABLE worklog5743; + + +#------------------------------------------------------------------------------ +# Prefix index with transaction when 2 client are ruuning there transaction +# in different sessions.With ISOLATION LEVEL as REPEATABLE READ and +# READ UNCOMMITTED. Same as above case but con2 starts tnx before con1 + +CREATE TABLE worklog5743 ( +col_1_text TEXT(4000) , col_2_text TEXT(4000) , +PRIMARY KEY (col_1_text(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 200) , REPEAT("o", 200)); +SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; + +--echo "In connection 1" +--connection con1 +SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; +SELECT COUNT(*) FROM worklog5743; +START TRANSACTION; + + +--echo "In connection 2" +--connection con2 +START TRANSACTION; +INSERT INTO worklog5743 VALUES(REPEAT("b", 200) , REPEAT("o", 200)); +DELETE FROM worklog5743 WHERE col_1_text = REPEAT("a", 200); +SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; +COMMIT; +# Wait for commit +let $wait_condition=SELECT COUNT(*)=0 FROM information_schema.processlist +WHERE info='COMMIT'; +--source include/wait_condition.inc + + +--echo "In connection 1" +--connection con1 +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; + +SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; +SELECT COUNT(*) FROM worklog5743; +COMMIT; + +--connection default +DROP TABLE worklog5743; + +#------------------------------------------------------------------------------ + +# Prefix index with transaction when 2 client are ruuning there transaction +# in different sessions.With ISOLATION LEVEL as REPEATABLE READ and +# READ UNCOMMITTED. Same as above cases but with ROLLBACK + +CREATE TABLE worklog5743 ( +col_1_text TEXT(4000) , col_2_text TEXT(4000) , +PRIMARY KEY (col_1_text(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 200) , REPEAT("o", 200)); +SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; + +--echo "In connection 1" +--connection con1 +SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; +SELECT COUNT(*) FROM worklog5743; +START TRANSACTION; + + +--echo "In connection 2" +--connection con2 +START TRANSACTION; +INSERT INTO worklog5743 VALUES(REPEAT("b", 200) , REPEAT("o", 200)); +DELETE FROM worklog5743 WHERE col_1_text = REPEAT("a", 200); +SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; +ROLLBACK; +# Wait for rollback +let $wait_condition=SELECT COUNT(*)=0 FROM information_schema.processlist +WHERE info='COMMIT'; +--source include/wait_condition.inc + + +--echo "In connection 1" +--connection con1 +SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM +worklog5743; +SELECT COUNT(*) FROM worklog5743; +COMMIT; + +--disconnect con1 +--disconnect con2 + +--connection default +DROP TABLE worklog5743; + + +#------------------------------------------------------------------------------ +# Select queries on prefix index column as index will be used in queries. +# Use few select functions , join condition , subqueries. + +CREATE TABLE worklog5743 ( +col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , +PRIMARY KEY (col_1_varchar(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000) +WHERE col_1_varchar = REPEAT("a", 4000) +AND col_2_varchar = REPEAT("o", 4000); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_varchar = REPEAT("c", 4000) +AND col_2_varchar = REPEAT("o", 4000); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; + +# Select with Join +SELECT tbl1.col_1_varchar = tbl2.col_1_varchar +FROM worklog5743 tbl1 , worklog5743 tbl2 +WHERE tbl1.col_1_varchar = tbl2.col_1_varchar ; + +# Select in subquey +SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1 +WHERE col_1_varchar IN (SELECT tbl2.col_1_varchar FROM worklog5743 tbl2) ; +SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1 +WHERE col_1_varchar NOT IN (SELECT tbl2.col_1_varchar FROM worklog5743 tbl2) ; +SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1 WHERE +col_1_varchar IN (SELECT tbl2.col_1_varchar FROM worklog5743 tbl2) +AND col_1_varchar = REPEAT("c", 4000); +SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1 +WHERE col_1_varchar in ( +SELECT tbl2.col_1_varchar FROM worklog5743 tbl2 +WHERE tbl1.col_1_varchar != tbl2.col_1_varchar +) ; +SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1 +WHERE col_1_varchar in ( +SELECT tbl2.col_1_varchar FROM worklog5743 tbl2 +WHERE tbl1.col_1_varchar = tbl2.col_1_varchar +) ; + +# function +SELECT +REVERSE(col_1_varchar) = REPEAT("c", 4000) , +REVERSE(REVERSE(col_1_varchar)) = REPEAT("c", 4000) +FROM worklog5743; +SELECT +UPPER(col_1_varchar) = REPEAT("c", 4000) , +UPPER(col_1_varchar) = REPEAT("C", 4000) , +LOWER(UPPER(col_1_varchar)) = REPEAT("c", 4000) +FROM worklog5743; +SELECT +col_1_varchar = REPEAT("c", 4000) +FROM worklog5743 WHERE col_1_varchar like '%c__%'; +SELECT SUBSTRING(INSERT(col_1_varchar, 1, 4, 'kkkk'),1,10) FROM worklog5743 ; +SELECT CONCAT(SUBSTRING(col_1_varchar,-5,3),'append') FROM worklog5743 ; + + +DROP TABLE worklog5743; + +#------------------------------------------------------------------------------ +# Prefix index with NULL values +CREATE TABLE worklog5743 ( +col_1_varchar VARCHAR (4000) , +col_2_varchar VARCHAR (4000) , +UNIQUE INDEX (col_1_varchar(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 +VALUES(concat(REPEAT("a", 2000),REPEAT("b", 1000),REPEAT("c", 1000)), REPEAT("o", 4000)); +INSERT INTO worklog5743 +VALUES(concat(REPEAT("a", 2000),REPEAT("b", 2000)), REPEAT("o", 4000)); +INSERT INTO worklog5743 VALUES(NULL,NULL); +INSERT INTO worklog5743 VALUES(NULL,NULL); +# check IS +SELECT COLUMN_NAME,INDEX_NAME,SUB_PART,INDEX_TYPE +FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'worklog5743' ; +SELECT col_1_varchar FROM worklog5743 WHERE col_1_varchar IS NULL; +SELECT col_1_varchar = concat(REPEAT("a", 2000),REPEAT("b", 2000)) +FROM worklog5743 WHERE col_1_varchar IS NOT NULL ORDER BY 1; + + +DROP TABLE worklog5743; + +# ----------------------------------------------------------------------------- +# Try drop and add secondary prefix index +CREATE TABLE worklog5743 ( +col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , +PRIMARY KEY (col_1_varchar(3072))) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +# Create index +CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +# Drop index +DROP INDEX prefix_idx ON worklog5743; + +SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000) +WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); +# Again add index +CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + +# ----------------------------------------------------------------------------- + +# Try drop and add primary prefix index +CREATE TABLE worklog5743 ( +col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , +PRIMARY KEY `prefix_primary` (col_1_varchar(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +# Create index +CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +# Drop index +ALTER TABLE worklog5743 DROP PRIMARY KEY; + +SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000) +WHERE col_1_varchar = REPEAT("a", 4000) +AND col_2_varchar = REPEAT("o", 4000); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_varchar = REPEAT("c", 4000) +AND col_2_varchar = REPEAT("o", 4000); +# Again add index +ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar(3072)); + +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +--error 1062 +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + + +# ----------------------------------------------------------------------------- + +# Try drop and add both (primary/secondary) prefix index +CREATE TABLE worklog5743 ( +col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , +PRIMARY KEY `prefix_primary` (col_1_varchar(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +# Create index +CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +# Drop primary index +ALTER TABLE worklog5743 DROP PRIMARY KEY; +# Drop secondary index +DROP INDEX prefix_idx ON worklog5743; + +SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000) +WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); +# Again add index +ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar(3072)); +CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); + +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +--error 1062 +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000); +SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; +DROP TABLE worklog5743; + + +# ----------------------------------------------------------------------------- +# Drop index from differnt session +CREATE TABLE worklog5743 ( +col_1_varchar VARCHAR(4000) , col_2_varchar VARCHAR(4000) , +PRIMARY KEY (col_1_varchar (3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("c", 3500) , REPEAT("o", 3500)); +CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); + +# Start new session +--connect (con1,localhost,root,,) + + +--echo " Switching to con1 connection For select " +--connection con1 +SELECT col_1_varchar = REPEAT("c", 3500) , col_2_varchar = REPEAT("o", 3500) +FROM worklog5743; + +--echo " Switching to default connection For DMLs " +--connection default +START TRANSACTION; +INSERT INTO worklog5743 VALUES(REPEAT("a", 3500) , REPEAT("o", 3500)); +SELECT col_1_varchar = REPEAT("b", 3500) FROM worklog5743 +WHERE col_2_varchar = REPEAT("o", 3500); +COMMIT; + +--echo " Switching to con1 connection For Dropping index and some DMLs " +--connection con1 +START TRANSACTION; +INSERT INTO worklog5743 VALUES(REPEAT("k", 3500),REPEAT("p", 3500)); +# Drop primary index +ALTER TABLE worklog5743 DROP PRIMARY KEY; +UPDATE worklog5743 SET col_1_varchar = REPEAT("b", 3500) +WHERE col_1_varchar = REPEAT("a", 3500) +AND col_2_varchar = REPEAT("o", 3500); +SELECT col_1_varchar = REPEAT("b", 3500) FROM worklog5743 +WHERE col_2_varchar = REPEAT("o", 3500); + +--echo " Switching to default connection For DELETE " +--connection default +DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 3500); +SELECT col_1_varchar = REPEAT("a", 3500) FROM worklog5743 +WHERE col_2_varchar = REPEAT("p", 3500); + +--echo " Switching to con1 connection to commit changes " +--connection con1 +COMMIT; + +--echo " Switching to default connection to drop and end sub-test " +--connection default +DROP TABLE worklog5743; + + + +# ----------------------------------------------------------------------------- +# Create prefix index with length < 3072 , length = 3072 , length > 3072 +# - varbinary data type + secondary index +CREATE TABLE worklog5743 ( +col_1_varbinary VARBINARY (4000) , col_2_varbinary VARBINARY (4000) , +PRIMARY KEY (col_1_varbinary(3072))) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +# Create index of 3072 +CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (3072)); +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varbinary = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) +WHERE col_1_varbinary = REPEAT("a", 4000) +AND col_2_varbinary = REPEAT("o", 4000); +SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_varbinary = REPEAT("c", 4000) +AND col_2_varbinary = REPEAT("o", 4000); +DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("c", 4000); +SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_varbinary = REPEAT("c", 4000) +AND col_2_varbinary = REPEAT("o", 4000); +# Drop index +DROP INDEX prefix_idx ON worklog5743; +SELECT col_1_varbinary = REPEAT("b", 4000) FROM worklog5743 +WHERE col_1_varbinary = REPEAT("b", 4000) +AND col_2_varbinary = REPEAT("p", 4000); + + +# Again add index length < 3072 +CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (2000)); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +SELECT col_1_varbinary = REPEAT("a", 4000) FROM worklog5743; +UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) +WHERE col_1_varbinary = REPEAT("a", 4000) +AND col_2_varbinary = REPEAT("o", 4000); +DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("c", 4000); +SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_varbinary = REPEAT("c", 4000) +AND col_2_varbinary = REPEAT("o", 4000); +# Drop index +DROP INDEX prefix_idx ON worklog5743; + +# Again add index length > 3072. +# If "innodb_large_prefix" is turned on, than the index prefix larger than 3072 +# will be truncated to 3072. If the table is REDUNDANT and COMPACT, which does +# not support prefix > 767, the create index will be rejected. +CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (4000)); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +SELECT col_1_varbinary = REPEAT("a", 4000) FROM worklog5743; +UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) +WHERE col_1_varbinary = REPEAT("a", 4000) +AND col_2_varbinary = REPEAT("o", 4000); +DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("c", 4000); +SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_varbinary = REPEAT("c", 4000) +AND col_2_varbinary = REPEAT("o", 4000); + + +DROP TABLE worklog5743; + +# ----------------------------------------------------------------------------- +# Create prefix index with length < 3072 , length = 3072 , length > 3072 +# text data type + secondary index +CREATE TABLE worklog5743 (col_1_text TEXT (4000) , col_2_text TEXT (4000) , +PRIMARY KEY (col_1_text(500)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +# Create index of 3072 +CREATE INDEX prefix_idx ON worklog5743(col_1_text (3072)); +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) +FROM worklog5743; +UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) +WHERE col_1_text = REPEAT("a", 4000) +AND col_2_text = REPEAT("o", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); +DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); +# Drop index +DROP INDEX prefix_idx ON worklog5743; +SELECT col_1_text = REPEAT("b", 4000) FROM worklog5743 +WHERE col_1_text = REPEAT("b", 4000) AND col_2_text = REPEAT("p", 4000); + +# Again add index length < 3072 +CREATE INDEX prefix_idx ON worklog5743(col_1_text (1000)); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743; +UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) +WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); +DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); +# Drop index +DROP INDEX prefix_idx ON worklog5743; + +# Again add index length > 3072. Expect error.Length exceeds maximum supported +# key length +# Again add index length > 3072. +# If "innodb_large_prefix" is turned on, than the index prefix larger than 3072 +# will be truncated to 3072. If the table is REDUNDANT and COMPACT, which does +# not support prefix > 767, the create index will be rejected. +CREATE INDEX prefix_idx ON worklog5743(col_1_text (4000)); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743; +UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) +WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); +DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); + +DROP TABLE worklog5743; + + +# ----------------------------------------------------------------------------- +# Create prefix index with length < 948 , length = 948 , length > 948 +# For compressed row type + primary key +CREATE TABLE worklog5743 ( +col_1_text TEXT (4000) , col_2_text TEXT (4000) , +PRIMARY KEY (col_1_text(948)) +) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +# Create index of 767 +INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) FROM worklog5743; +UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) +WHERE col_1_text = REPEAT("a", 4000) +AND col_2_text = REPEAT("o", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_text = REPEAT("c", 4000) +AND col_2_text = REPEAT("o", 4000); +DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_text = REPEAT("c", 4000) +AND col_2_text = REPEAT("o", 4000); +# Drop index +#DROP INDEX prefix_idx ON worklog5743; +ALTER TABLE worklog5743 DROP PRIMARY KEY; +SELECT col_1_text = REPEAT("b", 4000) FROM worklog5743 +WHERE col_1_text = REPEAT("b", 4000) +AND col_2_text = REPEAT("p", 4000); + +# Again add index length < 767 +ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_text (700)); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743; +UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) +WHERE col_1_text = REPEAT("a", 4000) +AND col_2_text = REPEAT("o", 4000); +DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_text = REPEAT("c", 4000) +AND col_2_text = REPEAT("o", 4000); +# Drop index +ALTER TABLE worklog5743 DROP PRIMARY KEY; + +# Again add index length > 948. Expect error 'to big row ' due to exceed +# in key length. +-- error 139 +ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_text (950)); +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743; +UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) +WHERE col_1_text = REPEAT("a", 4000) +AND col_2_text = REPEAT("o", 4000); +DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000); +SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 +WHERE col_1_text = REPEAT("c", 4000) +AND col_2_text = REPEAT("o", 4000); + +DROP TABLE worklog5743; + +# ----------------------------------------------------------------------------- +# Create prefix index with length < 3072 , length = 3072 , length > 3072 +# data types VARCHAR +CREATE TABLE worklog5743 ( +col_1_varchar VARCHAR (4000) , PRIMARY KEY (col_1_varchar(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +ALTER TABLE worklog5743 DROP PRIMARY KEY; +ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar (900)); +ALTER TABLE worklog5743 DROP PRIMARY KEY; +# Again add index length > 3072. Expect error.Length exceeds maximum supported +# key length +# Again add index length > 3072. +# If "innodb_large_prefix" is turned on, than the index prefix larger than 3072 +# will be truncated to 3072. If the table is REDUNDANT and COMPACT, which does +# not support prefix > 767, the create index will be rejected. +# Index length is truncated only for 'create index' , but error if we add +# prefix index with length > 3072 +--error ER_TOO_LONG_KEY +ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar (3073)); +DROP TABLE worklog5743; + + +CREATE TABLE worklog5743 ( +col_1_BLOB BLOB (4000) , PRIMARY KEY (col_1_BLOB(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +ALTER TABLE worklog5743 DROP PRIMARY KEY; +ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_BLOB (500)); +ALTER TABLE worklog5743 DROP PRIMARY KEY; +# Negative case +# Again add index length > 3072. Expect error.Length exceeds maximum supported +# key length +# Index length is truncated only for 'create index' , but error if we add +# prefix index with length > 3072 +--error ER_TOO_LONG_KEY +ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_BLOB (3073)); + +DROP TABLE worklog5743; + +# ----------------------------------------------------------------------------- +# Error on adding larger prefix if violates unique index. +CREATE TABLE worklog5743 ( +col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 +VALUES(concat(REPEAT("a", 2000),REPEAT("b", 1000),REPEAT("c", 1000)), +REPEAT("o", 4000)); +INSERT INTO worklog5743 +VALUES(concat(REPEAT("a", 2000),REPEAT("b", 2000)), REPEAT("o", 4000)); +--error 1062 +ALTER TABLE worklog5743 ADD PRIMARY KEY `pk_idx` (col_1_varchar(3000)); +DROP TABLE worklog5743; + +# ----------------------------------------------------------------------------- +set global innodb_large_prefix=0; +# Prefix index > 767 is allowed if innodb_large_prefix is set to 1 +--error ER_TOO_LONG_KEY +CREATE TABLE worklog5743 ( +col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , +PRIMARY KEY (col_1_varchar(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; + + +# ----------------------------------------------------------------------------- +set global innodb_large_prefix=0; +# Backward compatibility test - Index lenghth > 767 is truncated for REDUNDANT +# and COMPACT +CREATE TABLE worklog5743 ( +col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , +PRIMARY KEY (col_1_varchar(767)) +) engine = innodb; +# Prefix index > 767 is truncated with REDUNDANT and COMPACT +CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (1000)); +DROP TABLE worklog5743; +#------------------------------------------------------------------------------ + +eval SET GLOBAL innodb_file_format=$innodb_file_format_orig; +eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; +eval SET GLOBAL innodb_file_format_max=$innodb_file_format_max_orig; +eval SET GLOBAL innodb_large_prefix=$innodb_large_prefix_orig; diff --git a/mysql-test/suite/innodb/t/innodb_prefix_index_restart_server.test b/mysql-test/suite/innodb/t/innodb_prefix_index_restart_server.test new file mode 100644 index 00000000000..587e6fe1f6b --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_prefix_index_restart_server.test @@ -0,0 +1,127 @@ +######## suite/innodb/t/innodb_prefix_iindex_restart_server.test ##### +# # +# Testcase for worklog WL#5743: Lift the limit of index key prefixes # +# Test scenario : Stop the server in between when prefix index are # +# created and see if state is preserved after restart # +# # +# Creation: # +# 2011-06-02 Implemented this test as part of WL#5743 # +# # +###################################################################### + +# Don't test this under valgrind, memory leaks will occur due restart +--source include/not_valgrind.inc +# Test restart the server and "shutdown_server" looks for pid file +# which is not there with embedded mode +--source include/not_embedded.inc +--source include/have_innodb.inc +# Save innodb variables +let $innodb_file_format_orig=`select @@innodb_file_format`; +let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; +let $innodb_file_format_max_orig=`select @@innodb_file_format_max`; +let $innodb_large_prefix_orig=`select @@innodb_large_prefix`; + +# Set Innodb file format as feature works for Barracuda file format +set global innodb_file_format="Barracuda"; +set global innodb_file_per_table=1; +set global innodb_large_prefix=1; + +-- disable_warnings +DROP TABLE IF EXISTS worklog5743; +-- enable_warnings + + +#------------------------------------------------------------------------------ +# Stop the server in between when prefix index are created and see if state is +# correct when server is restarted. +# Server is restarted at differnt points. + +CREATE TABLE worklog5743 ( +col_1_text TEXT(4000) , col_2_text TEXT(4000) , +PRIMARY KEY (col_1_text(3072)) +) ROW_FORMAT=DYNAMIC, engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT("a", 3500) , REPEAT("o", 3500)); +SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM +worklog5743; + +--echo "In connection 1" +--connect (con1,localhost,root,,) +SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM +worklog5743; +SELECT COUNT(*) FROM worklog5743; + + +--echo "In connection 2" +--connect (con2,localhost,root,,) +START TRANSACTION; +INSERT INTO worklog5743 VALUES(REPEAT("b", 3500) , REPEAT("o", 3500)); + + +--echo "In connection 1" +--connection con1 +SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM +worklog5743; +SELECT COUNT(*) FROM worklog5743; +START TRANSACTION; + + +--echo "In connection default ....restarting the server" +--connection default +# Restart the server +-- source include/restart_mysqld.inc +SELECT COUNT(*) FROM worklog5743; +SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM +worklog5743; + +--disconnect con1 +--disconnect con2 + +--echo "In connection 1" +--connect (con1,localhost,root,,) +SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM +worklog5743; +SELECT COUNT(*) FROM worklog5743; +START TRANSACTION; +INSERT INTO worklog5743 VALUES(REPEAT("b", 3500) , REPEAT("o", 3500)); +DELETE FROM worklog5743 WHERE col_1_text = REPEAT("b", 3500); +SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM +worklog5743; + +--echo "In connection default ....restarting the server" +--connection default +# Restart the server +-- source include/restart_mysqld.inc +SELECT COUNT(*) FROM worklog5743; +SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM +worklog5743; + +--disconnect con1 + +--echo "In connection 1" +--connect (con2,localhost,root,,) +SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM +worklog5743; +SELECT COUNT(*) FROM worklog5743; +START TRANSACTION; +UPDATE worklog5743 SET col_1_text = REPEAT("b", 3500) WHERE col_1_text = REPEAT("a", 3500); +SELECT col_1_text = REPEAT("b", 3500) , col_2_text = REPEAT("o", 3500) FROM +worklog5743; + +--echo "In connection default ....restarting the server" +--connection default +# Restart the server +-- source include/restart_mysqld.inc +SELECT COUNT(*) FROM worklog5743; +SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM +worklog5743; + +DROP TABLE worklog5743; + + +#------------------------------------------------------------------------------ + +eval SET GLOBAL innodb_file_format=$innodb_file_format_orig; +eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; +eval SET GLOBAL innodb_file_format_max=$innodb_file_format_max_orig; +eval SET GLOBAL innodb_large_prefix=$innodb_large_prefix_orig; + |