maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #00378
bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2700)
#At lp:maria
2700 knielsen@xxxxxxxxxxxxxxx 2009-06-09
Fix test suite for XtraDB. Fix XtraDB for no atomic operations. Fix a few small XtraDB bugs.
added:
BUILD/compile-innodb
BUILD/compile-innodb-debug
mysql-test/include/innodb-index.inc
mysql-test/r/innodb-analyze.result
mysql-test/r/innodb-index.result
mysql-test/r/innodb-index_ucs2.result
mysql-test/r/innodb-timeout.result
mysql-test/r/innodb-use-sys-malloc.result
mysql-test/r/innodb-zip.result
mysql-test/r/innodb_bug36169.result
mysql-test/r/innodb_bug36172.result
mysql-test/r/innodb_bug40360.result
mysql-test/r/innodb_bug41904.result
mysql-test/r/innodb_information_schema.result
mysql-test/r/innodb_xtradb_bug317074.result
mysql-test/t/innodb-analyze.test
mysql-test/t/innodb-index.test
mysql-test/t/innodb-index_ucs2.test
mysql-test/t/innodb-timeout.test
mysql-test/t/innodb-use-sys-malloc-master.opt
mysql-test/t/innodb-use-sys-malloc.test
mysql-test/t/innodb-zip.test
mysql-test/t/innodb_bug36169.test
mysql-test/t/innodb_bug36172.test
mysql-test/t/innodb_bug40360.test
mysql-test/t/innodb_bug41904.test
mysql-test/t/innodb_information_schema.test
mysql-test/t/innodb_xtradb_bug317074.test
modified:
mysql-test/r/index_merge_innodb.result
mysql-test/r/information_schema.result
mysql-test/r/information_schema_all_engines.result
mysql-test/r/innodb-autoinc.result
mysql-test/r/innodb.result
mysql-test/r/innodb_bug34300.result
mysql-test/r/row-checksum-old.result
mysql-test/r/row-checksum.result
mysql-test/r/variables.result
mysql-test/t/information_schema.test
mysql-test/t/innodb-autoinc.test
mysql-test/t/innodb.test
mysql-test/t/innodb_bug34300.test
mysql-test/t/partition_innodb.test
mysql-test/t/type_bit_innodb.test
mysql-test/t/variables.test
mysys/thr_mutex.c
storage/innobase/ibuf/ibuf0ibuf.c
storage/innobase/include/sync0rw.h
storage/innobase/include/sync0rw.ic
storage/innobase/setup.sh
=== added file 'BUILD/compile-innodb'
--- a/BUILD/compile-innodb 1970-01-01 00:00:00 +0000
+++ b/BUILD/compile-innodb 2009-06-09 09:25:18 +0000
@@ -0,0 +1,24 @@
+#! /bin/sh
+#
+# Copyright (c) 2006, 2009, Innobase Oy. All Rights Reserved.
+#
+# This program is free software; you can redistribute it and/or modify it under
+# the terms of the GNU General Public License as published by the Free Software
+# Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful, but WITHOUT
+# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+# FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License along with
+# this program; if not, write to the Free Software Foundation, Inc., 59 Temple
+# Place, Suite 330, Boston, MA 02111-1307 USA
+#
+
+path=`dirname $0`
+. "$path/SETUP.sh"
+
+extra_flags="$pentium_cflags $fast_cflags -g"
+extra_configs="$pentium_configs $static_link --with-plugins=innobase"
+
+. "$path/FINISH.sh"
=== added file 'BUILD/compile-innodb-debug'
--- a/BUILD/compile-innodb-debug 1970-01-01 00:00:00 +0000
+++ b/BUILD/compile-innodb-debug 2009-06-09 09:25:18 +0000
@@ -0,0 +1,24 @@
+#! /bin/sh
+#
+# Copyright (c) 2005, 2009, Innobase Oy. All Rights Reserved.
+#
+# This program is free software; you can redistribute it and/or modify it under
+# the terms of the GNU General Public License as published by the Free Software
+# Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful, but WITHOUT
+# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+# FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License along with
+# this program; if not, write to the Free Software Foundation, Inc., 59 Temple
+# Place, Suite 330, Boston, MA 02111-1307 USA
+#
+
+path=`dirname $0`
+. "$path/SETUP.sh" $@ --with-debug=full
+
+extra_flags="$pentium_cflags $debug_cflags"
+extra_configs="$pentium_configs $debug_configs --with-plugins=innobase"
+
+. "$path/FINISH.sh"
=== added file 'mysql-test/include/innodb-index.inc'
--- a/mysql-test/include/innodb-index.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/innodb-index.inc 2009-06-09 09:25:18 +0000
@@ -0,0 +1,26 @@
+--eval create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb default charset=$charset
+insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
+commit;
+--error ER_DUP_ENTRY
+alter table t1 add unique index (b);
+insert into t1 values(8,9,'fff','fff');
+select * from t1;
+show create table t1;
+alter table t1 add index (b);
+insert into t1 values(10,10,'kkk','iii');
+select * from t1;
+select * from t1 force index(b) order by b;
+explain select * from t1 force index(b) order by b;
+show create table t1;
+alter table t1 add unique index (c), add index (d);
+insert into t1 values(11,11,'aaa','mmm');
+select * from t1;
+select * from t1 force index(b) order by b;
+select * from t1 force index(c) order by c;
+select * from t1 force index(d) order by d;
+explain select * from t1 force index(b) order by b;
+explain select * from t1 force index(c) order by c;
+explain select * from t1 force index(d) order by d;
+show create table t1;
+check table t1;
+drop table t1;
=== modified file 'mysql-test/r/index_merge_innodb.result'
--- a/mysql-test/r/index_merge_innodb.result 2006-09-18 14:55:56 +0000
+++ b/mysql-test/r/index_merge_innodb.result 2009-06-09 09:25:18 +0000
@@ -111,7 +111,7 @@ count(*)
explain select count(*) from t1 where
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge i1,i2 i1,i2 10,10 NULL 4 Using intersect(i1,i2); Using where; Using index
+1 SIMPLE t1 index_merge i1,i2 i1,i2 10,10 NULL 3 Using intersect(i1,i2); Using where; Using index
select count(*) from t1 where
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
count(*)
@@ -119,7 +119,7 @@ count(*)
explain select count(*) from t1 where
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge i1,i3 i1,i3 10,10 NULL 4 Using intersect(i1,i3); Using where; Using index
+1 SIMPLE t1 index_merge i1,i3 i1,i3 10,10 NULL 3 Using intersect(i1,i3); Using where; Using index
select count(*) from t1 where
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
count(*)
=== modified file 'mysql-test/r/information_schema.result'
--- a/mysql-test/r/information_schema.result 2009-04-08 16:55:26 +0000
+++ b/mysql-test/r/information_schema.result 2009-06-09 09:25:18 +0000
@@ -42,7 +42,7 @@ WHERE table_schema IN ('mysql', 'INFORMA
table_name<>'ndb_binlog_index' AND
table_name<>'ndb_apply_status' AND
NOT (table_schema = 'INFORMATION_SCHEMA' AND table_name LIKE 'PBXT_%');
-select * from v1;
+select * from v1 ORDER BY c COLLATE utf8_bin;
c
CHARACTER_SETS
COLLATIONS
@@ -54,6 +54,17 @@ EVENTS
FILES
GLOBAL_STATUS
GLOBAL_VARIABLES
+INNODB_BUFFER_POOL_PAGES
+INNODB_BUFFER_POOL_PAGES_BLOB
+INNODB_BUFFER_POOL_PAGES_INDEX
+INNODB_CMP
+INNODB_CMPMEM
+INNODB_CMPMEM_RESET
+INNODB_CMP_RESET
+INNODB_LOCKS
+INNODB_LOCK_WAITS
+INNODB_RSEG
+INNODB_TRX
KEY_COLUMN_USAGE
PARTITIONS
PLUGINS
@@ -72,6 +83,7 @@ TABLE_PRIVILEGES
TRIGGERS
USER_PRIVILEGES
VIEWS
+XTRADB_ENHANCEMENTS
columns_priv
db
event
@@ -87,6 +99,11 @@ proc
procs_priv
servers
slow_log
+t1
+t2
+t3
+t4
+t5
tables_priv
time_zone
time_zone_leap_second
@@ -94,11 +111,6 @@ time_zone_name
time_zone_transition
time_zone_transition_type
user
-t1
-t4
-t2
-t3
-t5
v1
select c,table_name from v1
inner join information_schema.TABLES v2 on (v1.c=v2.table_name)
@@ -800,6 +812,8 @@ TABLES CREATE_TIME datetime
TABLES UPDATE_TIME datetime
TABLES CHECK_TIME datetime
TRIGGERS CREATED datetime
+INNODB_TRX trx_started datetime
+INNODB_TRX trx_wait_started datetime
event execute_at datetime
event last_executed datetime
event starts datetime
@@ -848,6 +862,7 @@ TABLES TABLE_NAME select
TABLE_CONSTRAINTS TABLE_NAME select
TABLE_PRIVILEGES TABLE_NAME select
VIEWS TABLE_NAME select
+INNODB_BUFFER_POOL_PAGES_INDEX table_name select
delete from mysql.user where user='mysqltest_4';
delete from mysql.db where user='mysqltest_4';
flush privileges;
@@ -1223,12 +1238,12 @@ DROP PROCEDURE p1;
DROP USER mysql_bug20230@localhost;
SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test');
MAX(table_name)
-VIEWS
+XTRADB_ENHANCEMENTS
SELECT table_name from information_schema.tables
WHERE table_name=(SELECT MAX(table_name)
FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test'));
table_name
-VIEWS
+XTRADB_ENHANCEMENTS
DROP TABLE IF EXISTS bug23037;
DROP FUNCTION IF EXISTS get_value;
SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
=== modified file 'mysql-test/r/information_schema_all_engines.result'
--- a/mysql-test/r/information_schema_all_engines.result 2009-04-08 16:55:26 +0000
+++ b/mysql-test/r/information_schema_all_engines.result 2009-06-09 09:25:18 +0000
@@ -29,6 +29,18 @@ TABLE_PRIVILEGES
TRIGGERS
USER_PRIVILEGES
VIEWS
+INNODB_BUFFER_POOL_PAGES_INDEX
+INNODB_RSEG
+INNODB_LOCKS
+INNODB_BUFFER_POOL_PAGES
+XTRADB_ENHANCEMENTS
+INNODB_TRX
+INNODB_BUFFER_POOL_PAGES_BLOB
+INNODB_LOCK_WAITS
+INNODB_CMP_RESET
+INNODB_CMP
+INNODB_CMPMEM_RESET
+INNODB_CMPMEM
PBXT_STATISTICS
SELECT t.table_name, c1.column_name
FROM information_schema.tables t
@@ -73,6 +85,18 @@ TABLE_PRIVILEGES TABLE_SCHEMA
TRIGGERS TRIGGER_SCHEMA
USER_PRIVILEGES GRANTEE
VIEWS TABLE_SCHEMA
+INNODB_BUFFER_POOL_PAGES_INDEX schema_name
+INNODB_RSEG rseg_id
+INNODB_LOCKS lock_id
+INNODB_BUFFER_POOL_PAGES page_type
+XTRADB_ENHANCEMENTS name
+INNODB_TRX trx_id
+INNODB_BUFFER_POOL_PAGES_BLOB space_id
+INNODB_LOCK_WAITS requesting_trx_id
+INNODB_CMP_RESET page_size
+INNODB_CMP page_size
+INNODB_CMPMEM_RESET page_size
+INNODB_CMPMEM page_size
PBXT_STATISTICS ID
SELECT t.table_name, c1.column_name
FROM information_schema.tables t
@@ -117,6 +141,18 @@ TABLE_PRIVILEGES TABLE_SCHEMA
TRIGGERS TRIGGER_SCHEMA
USER_PRIVILEGES GRANTEE
VIEWS TABLE_SCHEMA
+INNODB_BUFFER_POOL_PAGES_INDEX schema_name
+INNODB_RSEG rseg_id
+INNODB_LOCKS lock_id
+INNODB_BUFFER_POOL_PAGES page_type
+XTRADB_ENHANCEMENTS name
+INNODB_TRX trx_id
+INNODB_BUFFER_POOL_PAGES_BLOB space_id
+INNODB_LOCK_WAITS requesting_trx_id
+INNODB_CMP_RESET page_size
+INNODB_CMP page_size
+INNODB_CMPMEM_RESET page_size
+INNODB_CMPMEM page_size
PBXT_STATISTICS ID
select 1 as f1 from information_schema.tables where "CHARACTER_SETS"=
(select cast(table_name as char) from information_schema.tables
@@ -149,6 +185,17 @@ EVENTS information_schema.EVENTS 1
FILES information_schema.FILES 1
GLOBAL_STATUS information_schema.GLOBAL_STATUS 1
GLOBAL_VARIABLES information_schema.GLOBAL_VARIABLES 1
+INNODB_BUFFER_POOL_PAGES information_schema.INNODB_BUFFER_POOL_PAGES 1
+INNODB_BUFFER_POOL_PAGES_BLOB information_schema.INNODB_BUFFER_POOL_PAGES_BLOB 1
+INNODB_BUFFER_POOL_PAGES_INDEX information_schema.INNODB_BUFFER_POOL_PAGES_INDEX 1
+INNODB_CMP information_schema.INNODB_CMP 1
+INNODB_CMPMEM information_schema.INNODB_CMPMEM 1
+INNODB_CMPMEM_RESET information_schema.INNODB_CMPMEM_RESET 1
+INNODB_CMP_RESET information_schema.INNODB_CMP_RESET 1
+INNODB_LOCKS information_schema.INNODB_LOCKS 1
+INNODB_LOCK_WAITS information_schema.INNODB_LOCK_WAITS 1
+INNODB_RSEG information_schema.INNODB_RSEG 1
+INNODB_TRX information_schema.INNODB_TRX 1
KEY_COLUMN_USAGE information_schema.KEY_COLUMN_USAGE 1
PARTITIONS information_schema.PARTITIONS 1
PBXT_STATISTICS information_schema.PBXT_STATISTICS 1
@@ -168,6 +215,7 @@ TABLE_PRIVILEGES information_schema.TABL
TRIGGERS information_schema.TRIGGERS 1
USER_PRIVILEGES information_schema.USER_PRIVILEGES 1
VIEWS information_schema.VIEWS 1
+XTRADB_ENHANCEMENTS information_schema.XTRADB_ENHANCEMENTS 1
Database: information_schema
+---------------------------------------+
| Tables |
@@ -200,6 +248,18 @@ Database: information_schema
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+| INNODB_BUFFER_POOL_PAGES_INDEX |
+| INNODB_RSEG |
+| INNODB_LOCKS |
+| INNODB_BUFFER_POOL_PAGES |
+| XTRADB_ENHANCEMENTS |
+| INNODB_TRX |
+| INNODB_BUFFER_POOL_PAGES_BLOB |
+| INNODB_LOCK_WAITS |
+| INNODB_CMP_RESET |
+| INNODB_CMP |
+| INNODB_CMPMEM_RESET |
+| INNODB_CMPMEM |
| PBXT_STATISTICS |
+---------------------------------------+
Database: INFORMATION_SCHEMA
@@ -234,6 +294,18 @@ Database: INFORMATION_SCHEMA
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+| INNODB_BUFFER_POOL_PAGES_INDEX |
+| INNODB_RSEG |
+| INNODB_LOCKS |
+| INNODB_BUFFER_POOL_PAGES |
+| XTRADB_ENHANCEMENTS |
+| INNODB_TRX |
+| INNODB_BUFFER_POOL_PAGES_BLOB |
+| INNODB_LOCK_WAITS |
+| INNODB_CMP_RESET |
+| INNODB_CMP |
+| INNODB_CMPMEM_RESET |
+| INNODB_CMPMEM |
| PBXT_STATISTICS |
+---------------------------------------+
Wildcard: inf_rmation_schema
@@ -244,5 +316,5 @@ Wildcard: inf_rmation_schema
+--------------------+
SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA;
table_schema count(*)
-information_schema 29
+information_schema 41
mysql 22
=== added file 'mysql-test/r/innodb-analyze.result'
--- a/mysql-test/r/innodb-analyze.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb-analyze.result 2009-06-09 09:25:18 +0000
@@ -0,0 +1,2 @@
+Variable_name Value
+innodb_stats_sample_pages 1
=== modified file 'mysql-test/r/innodb-autoinc.result'
--- a/mysql-test/r/innodb-autoinc.result 2009-04-08 16:55:26 +0000
+++ b/mysql-test/r/innodb-autoinc.result 2009-06-09 09:25:18 +0000
@@ -579,3 +579,291 @@ c1
18446744073709551610
18446744073709551615
DROP TABLE t1;
+SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
+SET @@INSERT_ID=1;
+SHOW VARIABLES LIKE "auto_inc%";
+Variable_name Value
+auto_increment_increment 1
+auto_increment_offset 1
+CREATE TABLE t1 (c1 DOUBLE NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(NULL, 1);
+INSERT INTO t1 VALUES(NULL, 2);
+SELECT * FROM t1;
+c1 c2
+1 1
+2 2
+ALTER TABLE t1 CHANGE c1 c1 SERIAL;
+SELECT * FROM t1;
+c1 c2
+1 1
+2 2
+INSERT INTO t1 VALUES(NULL, 3);
+INSERT INTO t1 VALUES(NULL, 4);
+SELECT * FROM t1;
+c1 c2
+1 1
+2 2
+3 3
+4 4
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (c1 FLOAT NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(NULL, 1);
+INSERT INTO t1 VALUES(NULL, 2);
+SELECT * FROM t1;
+c1 c2
+1 1
+2 2
+ALTER TABLE t1 CHANGE c1 c1 SERIAL;
+SELECT * FROM t1;
+c1 c2
+1 1
+2 2
+INSERT INTO t1 VALUES(NULL, 3);
+INSERT INTO t1 VALUES(NULL, 4);
+SELECT * FROM t1;
+c1 c2
+1 1
+2 2
+3 3
+4 4
+DROP TABLE t1;
+SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=5;
+DROP TABLE IF EXISTS t1;
+Warnings:
+Note 1051 Unknown table 't1'
+DROP TABLE IF EXISTS t2;
+Warnings:
+Note 1051 Unknown table 't2'
+CREATE TABLE t1 (
+a INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
+b INT(10) UNSIGNED NOT NULL,
+c ENUM('FALSE','TRUE') DEFAULT NULL,
+PRIMARY KEY (a)) ENGINE = InnoDB;
+CREATE TABLE t2 (
+m INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
+n INT(10) UNSIGNED NOT NULL,
+o enum('FALSE','TRUE') DEFAULT NULL,
+PRIMARY KEY (m)) ENGINE = InnoDB;
+INSERT INTO t2 (n,o) VALUES
+(1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'),
+(3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false');
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `m` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `n` int(10) unsigned NOT NULL,
+ `o` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`m`)
+) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
+SELECT * FROM t1;
+a b c
+1 1 TRUE
+2 1 FALSE
+3 2 TRUE
+4 2 FALSE
+5 3 TRUE
+6 3 FALSE
+7 4 TRUE
+8 4 FALSE
+9 5 TRUE
+10 5 FALSE
+13 1 TRUE
+14 1 FALSE
+15 2 TRUE
+16 2 FALSE
+17 3 TRUE
+18 3 FALSE
+19 4 TRUE
+20 4 FALSE
+21 5 TRUE
+22 5 FALSE
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SELECT * FROM t1;
+a b c
+1 1 TRUE
+2 1 FALSE
+3 2 TRUE
+4 2 FALSE
+5 3 TRUE
+6 3 FALSE
+7 4 TRUE
+8 4 FALSE
+9 5 TRUE
+10 5 FALSE
+13 1 TRUE
+14 1 FALSE
+15 2 TRUE
+16 2 FALSE
+17 3 TRUE
+18 3 FALSE
+19 4 TRUE
+20 4 FALSE
+21 5 TRUE
+22 5 FALSE
+23 1 FALSE
+24 2 FALSE
+25 3 FALSE
+26 4 FALSE
+27 5 FALSE
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SELECT * FROM t1;
+a b c
+1 1 TRUE
+2 1 FALSE
+3 2 TRUE
+4 2 FALSE
+5 3 TRUE
+6 3 FALSE
+7 4 TRUE
+8 4 FALSE
+9 5 TRUE
+10 5 FALSE
+13 1 TRUE
+14 1 FALSE
+15 2 TRUE
+16 2 FALSE
+17 3 TRUE
+18 3 FALSE
+19 4 TRUE
+20 4 FALSE
+21 5 TRUE
+22 5 FALSE
+23 1 FALSE
+24 2 FALSE
+25 3 FALSE
+26 4 FALSE
+27 5 FALSE
+30 1 FALSE
+31 2 FALSE
+32 3 FALSE
+33 4 FALSE
+34 5 FALSE
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SELECT * FROM t1;
+a b c
+1 1 TRUE
+2 1 FALSE
+3 2 TRUE
+4 2 FALSE
+5 3 TRUE
+6 3 FALSE
+7 4 TRUE
+8 4 FALSE
+9 5 TRUE
+10 5 FALSE
+13 1 TRUE
+14 1 FALSE
+15 2 TRUE
+16 2 FALSE
+17 3 TRUE
+18 3 FALSE
+19 4 TRUE
+20 4 FALSE
+21 5 TRUE
+22 5 FALSE
+23 1 FALSE
+24 2 FALSE
+25 3 FALSE
+26 4 FALSE
+27 5 FALSE
+30 1 FALSE
+31 2 FALSE
+32 3 FALSE
+33 4 FALSE
+34 5 FALSE
+37 1 FALSE
+38 2 FALSE
+39 3 FALSE
+40 4 FALSE
+41 5 FALSE
+44 1 FALSE
+45 2 FALSE
+46 3 FALSE
+47 4 FALSE
+48 5 FALSE
+51 1 FALSE
+52 2 FALSE
+53 3 FALSE
+54 4 FALSE
+55 5 FALSE
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE IF EXISTS t1;
+Warnings:
+Note 1051 Unknown table 't1'
+DROP TABLE IF EXISTS t2;
+Warnings:
+Note 1051 Unknown table 't2'
+CREATE TABLE t1(
+c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
+PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
+CREATE TABLE t2(
+c1 TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT
+PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t2 SELECT c1 FROM t1;
+Got one of the listed errors
+INSERT INTO t2 SELECT NULL FROM t1;
+Got one of the listed errors
+DROP TABLE t1;
+DROP TABLE t2;
=== added file 'mysql-test/r/innodb-index.result'
--- a/mysql-test/r/innodb-index.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb-index.result 2009-06-09 09:25:18 +0000
@@ -0,0 +1,1137 @@
+SET @save_innodb_file_format_check=@@global.innodb_file_format_check;
+create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb;
+insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak');
+commit;
+alter table t1 add index b (b), add index b (b);
+ERROR 42000: Duplicate key name 'b'
+alter table t1 add index (b,b);
+ERROR 42S21: Duplicate column name 'b'
+alter table t1 add index d2 (d);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ KEY `d2` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+explain select * from t1 force index(d2) order by d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL d2 23 NULL 4
+select * from t1 force index (d2) order by d;
+a b c d
+3 4 ad ad
+2 3 ak ak
+5 5 oo oo
+4 4 tr tr
+alter table t1 add unique index (b);
+ERROR 23000: Duplicate entry '4' for key 'b'
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ KEY `d2` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+alter table t1 add index (b);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ KEY `d2` (`d`),
+ KEY `b` (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+CREATE TABLE `t1#1`(a INT PRIMARY KEY) ENGINE=InnoDB;
+call mtr.add_suppression(" table `test`\\.`t1#[12]` already exists in InnoDB internal");
+alter table t1 add unique index (c), add index (d);
+ERROR HY000: Table 'test.t1#1' already exists
+rename table `t1#1` to `t1#2`;
+alter table t1 add unique index (c), add index (d);
+ERROR HY000: Table 'test.t1#2' already exists
+drop table `t1#2`;
+alter table t1 add unique index (c), add index (d);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ UNIQUE KEY `c` (`c`),
+ KEY `d2` (`d`),
+ KEY `b` (`b`),
+ KEY `d` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 10 NULL 4
+alter table t1 add primary key (a), drop index c;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `d2` (`d`),
+ KEY `b` (`b`),
+ KEY `d` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+alter table t1 add primary key (c);
+ERROR 42000: Multiple primary key defined
+alter table t1 drop primary key, add primary key (b);
+ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
+create unique index c on t1 (c);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `c` (`c`),
+ KEY `d2` (`d`),
+ KEY `b` (`b`),
+ KEY `d` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 10 NULL 4
+select * from t1 force index(c) order by c;
+a b c d
+3 4 ad ad
+2 3 ak ak
+5 5 oo oo
+4 4 tr tr
+alter table t1 drop index b, add index (b);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `c` (`c`),
+ KEY `d2` (`d`),
+ KEY `d` (`d`),
+ KEY `b` (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+insert into t1 values(6,1,'ggg','ggg');
+select * from t1;
+a b c d
+2 3 ak ak
+3 4 ad ad
+4 4 tr tr
+5 5 oo oo
+6 1 ggg ggg
+select * from t1 force index(b) order by b;
+a b c d
+6 1 ggg ggg
+2 3 ak ak
+3 4 ad ad
+4 4 tr tr
+5 5 oo oo
+select * from t1 force index(c) order by c;
+a b c d
+3 4 ad ad
+2 3 ak ak
+6 1 ggg ggg
+5 5 oo oo
+4 4 tr tr
+select * from t1 force index(d) order by d;
+a b c d
+3 4 ad ad
+2 3 ak ak
+6 1 ggg ggg
+5 5 oo oo
+4 4 tr tr
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 5
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 10 NULL 5
+explain select * from t1 force index(d) order by d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL d 23 NULL 5
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `c` (`c`),
+ KEY `d2` (`d`),
+ KEY `d` (`d`),
+ KEY `b` (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop table t1;
+create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
+insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ad','ad'),(4,4,'afe','afe');
+commit;
+alter table t1 add index (c(2));
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `c` (`c`(2))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+alter table t1 add unique index (d(10));
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `d` (`d`(10)),
+ KEY `c` (`c`(2))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+insert into t1 values(5,1,'ggg','ggg');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 3 ad ad
+4 4 afe afe
+5 1 ggg ggg
+select * from t1 force index(c) order by c;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 3 ad ad
+4 4 afe afe
+5 1 ggg ggg
+select * from t1 force index(d) order by d;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 3 ad ad
+4 4 afe afe
+5 1 ggg ggg
+explain select * from t1 order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using filesort
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using filesort
+explain select * from t1 force index(d) order by d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using filesort
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `d` (`d`(10)),
+ KEY `c` (`c`(2))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+alter table t1 drop index d;
+insert into t1 values(8,9,'fff','fff');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 3 ad ad
+4 4 afe afe
+5 1 ggg ggg
+8 9 fff fff
+select * from t1 force index(c) order by c;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 3 ad ad
+4 4 afe afe
+8 9 fff fff
+5 1 ggg ggg
+explain select * from t1 order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
+explain select * from t1 order by d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `c` (`c`(2))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop table t1;
+create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
+insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
+commit;
+alter table t1 add unique index (b,c);
+insert into t1 values(8,9,'fff','fff');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+select * from t1 force index(b) order by b;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 16 NULL 5
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `b` (`b`,`c`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+alter table t1 add index (b,c);
+insert into t1 values(11,11,'kkk','kkk');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+11 11 kkk kkk
+select * from t1 force index(b) order by b;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+11 11 kkk kkk
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 16 NULL 6
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `b` (`b`,`c`),
+ KEY `b_2` (`b`,`c`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+alter table t1 add unique index (c,d);
+insert into t1 values(13,13,'yyy','aaa');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+11 11 kkk kkk
+13 13 yyy aaa
+select * from t1 force index(b) order by b;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+11 11 kkk kkk
+13 13 yyy aaa
+select * from t1 force index(c) order by c;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+11 11 kkk kkk
+13 13 yyy aaa
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 16 NULL 7
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 34 NULL 7
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `b` (`b`,`c`),
+ UNIQUE KEY `c` (`c`,`d`),
+ KEY `b_2` (`b`,`c`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop table t1;
+create table t1(a int not null, b int not null, c int, primary key (a), key (b)) engine = innodb;
+create table t3(a int not null, c int not null, d int, primary key (a), key (c)) engine = innodb;
+create table t4(a int not null, d int not null, e int, primary key (a), key (d)) engine = innodb;
+create table t2(a int not null, b int not null, c int not null, d int not null, e int,
+foreign key (b) references t1(b) on delete cascade,
+foreign key (c) references t3(c), foreign key (d) references t4(d))
+engine = innodb;
+alter table t1 drop index b;
+ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint
+alter table t3 drop index c;
+ERROR HY000: Cannot drop index 'c': needed in a foreign key constraint
+alter table t4 drop index d;
+ERROR HY000: Cannot drop index 'd': needed in a foreign key constraint
+alter table t2 drop index b;
+ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint
+alter table t2 drop index b, drop index c, drop index d;
+ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint
+create unique index dc on t2 (d,c);
+create index dc on t1 (b,c);
+alter table t2 add primary key (a);
+insert into t1 values (1,1,1);
+insert into t3 values (1,1,1);
+insert into t4 values (1,1,1);
+insert into t2 values (1,1,1,1,1);
+commit;
+alter table t4 add constraint dc foreign key (a) references t1(a);
+show create table t4;
+Table Create Table
+t4 CREATE TABLE `t4` (
+ `a` int(11) NOT NULL,
+ `d` int(11) NOT NULL,
+ `e` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `d` (`d`),
+ CONSTRAINT `dc` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+alter table t3 add constraint dc foreign key (a) references t1(a);
+ERROR HY000: Can't create table '#sql-temporary' (errno: 121)
+show create table t3;
+Table Create Table
+t3 CREATE TABLE `t3` (
+ `a` int(11) NOT NULL,
+ `c` int(11) NOT NULL,
+ `d` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `c` (`c`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+alter table t2 drop index b, add index (b);
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` int(11) NOT NULL,
+ `d` int(11) NOT NULL,
+ `e` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `dc` (`d`,`c`),
+ KEY `c` (`c`),
+ KEY `b` (`b`),
+ CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`b`) ON DELETE CASCADE,
+ CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t3` (`c`),
+ CONSTRAINT `t2_ibfk_3` FOREIGN KEY (`d`) REFERENCES `t4` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+delete from t1;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `dc` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
+drop index dc on t4;
+ERROR 42000: Can't DROP 'dc'; check that column/key exists
+alter table t3 drop foreign key dc;
+ERROR HY000: Error on rename of './test/t3' to '#sql2-temporary' (errno: 152)
+alter table t4 drop foreign key dc;
+select * from t2;
+a b c d e
+1 1 1 1 1
+delete from t1;
+select * from t2;
+a b c d e
+drop table t2,t4,t3,t1;
+create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb default charset=utf8;
+insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
+commit;
+alter table t1 add unique index (b);
+ERROR 23000: Duplicate entry '2' for key 'b'
+insert into t1 values(8,9,'fff','fff');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8
+alter table t1 add index (b);
+insert into t1 values(10,10,'kkk','iii');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+select * from t1 force index(b) order by b;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 6
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `b` (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8
+alter table t1 add unique index (c), add index (d);
+insert into t1 values(11,11,'aaa','mmm');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+11 11 aaa mmm
+select * from t1 force index(b) order by b;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+11 11 aaa mmm
+select * from t1 force index(c) order by c;
+a b c d
+11 11 aaa mmm
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+select * from t1 force index(d) order by d;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+11 11 aaa mmm
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 7
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 31 NULL 7
+explain select * from t1 force index(d) order by d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL d 63 NULL 7
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `c` (`c`),
+ KEY `b` (`b`),
+ KEY `d` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+drop table t1;
+create table t1(a int not null, b int) engine = innodb;
+insert into t1 values (1,1),(1,1),(1,1),(1,1);
+alter table t1 add unique index (a);
+ERROR 23000: Duplicate entry '1' for key 'a'
+alter table t1 add unique index (b);
+ERROR 23000: Duplicate entry '1' for key 'b'
+alter table t1 add unique index (a), add unique index(b);
+ERROR 23000: Duplicate entry '1' for key 'a'
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop table t1;
+create table t1(a int not null, c int not null,b int, primary key(a), unique key(c), key(b)) engine = innodb;
+alter table t1 drop index c, drop index b;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `c` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop table t1;
+create table t1(a int not null, b int, primary key(a)) engine = innodb;
+alter table t1 add index (b);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `b` (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop table t1;
+create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
+insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ac','ac'),(4,4,'afe','afe'),(5,4,'affe','affe');
+alter table t1 add unique index (b), add unique index (c), add unique index (d);
+ERROR 23000: Duplicate entry '4' for key 'b'
+alter table t1 add unique index (c), add unique index (b), add index (d);
+ERROR 23000: Duplicate entry 'ac' for key 'c'
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop table t1;
+create table t1(a int not null, b int not null, c int, primary key (a), key(c)) engine=innodb;
+insert into t1 values (5,1,5),(4,2,4),(3,3,3),(2,4,2),(1,5,1);
+alter table t1 add unique index (b);
+insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `b` (`b`),
+ KEY `c` (`c`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 5 NULL 9
+explain select * from t1 order by a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 9
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 4 NULL 9
+select * from t1 order by a;
+a b c
+1 5 1
+2 4 2
+3 3 3
+4 2 4
+5 1 5
+10 20 20
+11 19 19
+12 18 18
+13 17 17
+select * from t1 force index(b) order by b;
+a b c
+5 1 5
+4 2 4
+3 3 3
+2 4 2
+1 5 1
+13 17 17
+12 18 18
+11 19 19
+10 20 20
+select * from t1 force index(c) order by c;
+a b c
+1 5 1
+2 4 2
+3 3 3
+4 2 4
+5 1 5
+13 17 17
+12 18 18
+11 19 19
+10 20 20
+drop table t1;
+create table t1(a int not null, b int not null) engine=innodb;
+insert into t1 values (1,1);
+alter table t1 add primary key(b);
+insert into t1 values (2,2);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ PRIMARY KEY (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+select * from t1;
+a b
+1 1
+2 2
+explain select * from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+explain select * from t1 order by a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using filesort
+explain select * from t1 order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2
+checksum table t1;
+Table Checksum
+test.t1 582702641
+drop table t1;
+create table t1(a int not null) engine=innodb;
+insert into t1 values (1);
+alter table t1 add primary key(a);
+insert into t1 values (2);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+commit;
+select * from t1;
+a
+1
+2
+explain select * from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index
+explain select * from t1 order by a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index
+drop table t1;
+create table t2(d varchar(17) primary key) engine=innodb default charset=utf8;
+create table t3(a int primary key) engine=innodb;
+insert into t3 values(22),(44),(33),(55),(66);
+insert into t2 values ('jejdkrun87'),('adfd72nh9k'),
+('adfdpplkeock'),('adfdijnmnb78k'),('adfdijn0loKNHJik');
+create table t1(a int, b blob, c text, d text not null)
+engine=innodb default charset = utf8;
+insert into t1
+select a,left(repeat(d,100*a),65535),repeat(d,20*a),d from t2,t3;
+drop table t2, t3;
+select count(*) from t1 where a=44;
+count(*)
+5
+select a,
+length(b),b=left(repeat(d,100*a),65535),length(c),c=repeat(d,20*a),d from t1;
+a length(b) b=left(repeat(d,100*a),65535) length(c) c=repeat(d,20*a) d
+22 22000 1 4400 1 adfd72nh9k
+22 35200 1 7040 1 adfdijn0loKNHJik
+22 28600 1 5720 1 adfdijnmnb78k
+22 26400 1 5280 1 adfdpplkeock
+22 22000 1 4400 1 jejdkrun87
+33 33000 1 6600 1 adfd72nh9k
+33 52800 1 10560 1 adfdijn0loKNHJik
+33 42900 1 8580 1 adfdijnmnb78k
+33 39600 1 7920 1 adfdpplkeock
+33 33000 1 6600 1 jejdkrun87
+44 44000 1 8800 1 adfd72nh9k
+44 65535 1 14080 1 adfdijn0loKNHJik
+44 57200 1 11440 1 adfdijnmnb78k
+44 52800 1 10560 1 adfdpplkeock
+44 44000 1 8800 1 jejdkrun87
+55 55000 1 11000 1 adfd72nh9k
+55 65535 1 17600 1 adfdijn0loKNHJik
+55 65535 1 14300 1 adfdijnmnb78k
+55 65535 1 13200 1 adfdpplkeock
+55 55000 1 11000 1 jejdkrun87
+66 65535 1 13200 1 adfd72nh9k
+66 65535 1 21120 1 adfdijn0loKNHJik
+66 65535 1 17160 1 adfdijnmnb78k
+66 65535 1 15840 1 adfdpplkeock
+66 65535 1 13200 1 jejdkrun87
+alter table t1 add primary key (a), add key (b(20));
+ERROR 23000: Duplicate entry '22' for key 'PRIMARY'
+delete from t1 where a%2;
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+alter table t1 add primary key (a,b(255),c(255)), add key (b(767));
+select count(*) from t1 where a=44;
+count(*)
+5
+select a,
+length(b),b=left(repeat(d,100*a),65535),length(c),c=repeat(d,20*a),d from t1;
+a length(b) b=left(repeat(d,100*a),65535) length(c) c=repeat(d,20*a) d
+22 22000 1 4400 1 adfd72nh9k
+22 35200 1 7040 1 adfdijn0loKNHJik
+22 28600 1 5720 1 adfdijnmnb78k
+22 26400 1 5280 1 adfdpplkeock
+22 22000 1 4400 1 jejdkrun87
+44 44000 1 8800 1 adfd72nh9k
+44 65535 1 14080 1 adfdijn0loKNHJik
+44 57200 1 11440 1 adfdijnmnb78k
+44 52800 1 10560 1 adfdpplkeock
+44 44000 1 8800 1 jejdkrun87
+66 65535 1 13200 1 adfd72nh9k
+66 65535 1 21120 1 adfdijn0loKNHJik
+66 65535 1 17160 1 adfdijnmnb78k
+66 65535 1 15840 1 adfdpplkeock
+66 65535 1 13200 1 jejdkrun87
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` blob NOT NULL,
+ `c` text NOT NULL,
+ `d` text NOT NULL,
+ PRIMARY KEY (`a`,`b`(255),`c`(255)),
+ KEY `b` (`b`(767))
+) ENGINE=InnoDB DEFAULT CHARSET=utf8
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+explain select * from t1 where b like 'adfd%';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL b NULL NULL NULL 15 Using where
+create table t2(a int, b varchar(255), primary key(a,b)) engine=innodb;
+insert into t2 select a,left(b,255) from t1;
+drop table t1;
+rename table t2 to t1;
+set innodb_lock_wait_timeout=1;
+begin;
+select a from t1 limit 1 for update;
+a
+22
+set innodb_lock_wait_timeout=1;
+create index t1ba on t1 (b,a);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+commit;
+begin;
+select a from t1 limit 1 lock in share mode;
+a
+22
+create index t1ba on t1 (b,a);
+drop index t1ba on t1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+commit;
+explain select a from t1 order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL t1ba 261 NULL 15 Using index
+select a,sleep(2+a/100) from t1 order by b limit 3;
+select sleep(1);
+sleep(1)
+0
+drop index t1ba on t1;
+a sleep(2+a/100)
+22 0
+44 0
+66 0
+explain select a from t1 order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 261 NULL 15 Using index; Using filesort
+select a from t1 order by b limit 3;
+a
+22
+66
+44
+commit;
+drop table t1;
+set global innodb_file_per_table=on;
+set global innodb_file_format='Barracuda';
+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 t1u on t1 (u(1));
+ERROR HY000: Too big row
+create index t1ut on t1 (u(1), t(1));
+ERROR HY000: Too big row
+create index t1st on t1 (s(1), t(1));
+show create table t1;
+Table Create Table
+t1 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,
+ KEY `t1a` (`a`(1)),
+ KEY `t1b` (`b`(1)),
+ KEY `t1c` (`c`(1)),
+ KEY `t1d` (`d`(1)),
+ KEY `t1e` (`e`(1)),
+ KEY `t1f` (`f`(1)),
+ KEY `t1g` (`g`(1)),
+ KEY `t1h` (`h`(1)),
+ KEY `t1i` (`i`(1)),
+ KEY `t1j` (`j`(1)),
+ KEY `t1k` (`k`(1)),
+ KEY `t1l` (`l`(1)),
+ KEY `t1m` (`m`(1)),
+ KEY `t1n` (`n`(1)),
+ KEY `t1o` (`o`(1)),
+ KEY `t1p` (`p`(1)),
+ KEY `t1q` (`q`(1)),
+ KEY `t1r` (`r`(1)),
+ KEY `t1s` (`s`(1)),
+ KEY `t1t` (`t`(1)),
+ KEY `t1st` (`s`(1),`t`(1))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
+create index t1u on t1 (u(1));
+ERROR HY000: Too big row
+alter table t1 row_format=compact;
+create index t1u on t1 (u(1));
+drop table t1;
+set global innodb_file_per_table=0;
+set global innodb_file_format=Antelope;
+SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
+SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
+CREATE TABLE t1(
+c1 BIGINT(12) NOT NULL,
+PRIMARY KEY (c1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+CREATE TABLE t2(
+c1 BIGINT(16) NOT NULL,
+c2 BIGINT(12) NOT NULL,
+c3 BIGINT(12) NOT NULL,
+PRIMARY KEY (c1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3) REFERENCES t1(c1);
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `fk_t2_ca` (`c3`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `i_t2_c3_c2` (`c3`,`c2`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
+INSERT INTO t2 VALUES(0,0,0);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`))
+INSERT INTO t1 VALUES(0);
+INSERT INTO t2 VALUES(0,0,0);
+DROP TABLE t2;
+CREATE TABLE t2(
+c1 BIGINT(16) NOT NULL,
+c2 BIGINT(12) NOT NULL,
+c3 BIGINT(12) NOT NULL,
+PRIMARY KEY (c1,c2,c3)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3) REFERENCES t1(c1);
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`,`c2`,`c3`),
+ KEY `fk_t2_ca` (`c3`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`,`c2`,`c3`),
+ KEY `i_t2_c3_c2` (`c3`,`c2`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+INSERT INTO t2 VALUES(0,0,1);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`))
+INSERT INTO t2 VALUES(0,0,0);
+DELETE FROM t1;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`))
+DELETE FROM t2;
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1(
+c1 BIGINT(12) NOT NULL,
+c2 INT(4) NOT NULL,
+PRIMARY KEY (c2,c1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+CREATE TABLE t2(
+c1 BIGINT(16) NOT NULL,
+c2 BIGINT(12) NOT NULL,
+c3 BIGINT(12) NOT NULL,
+PRIMARY KEY (c1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1);
+ERROR HY000: Can't create table '#sql-temporary' (errno: 150)
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
+ERROR HY000: Can't create table '#sql-temporary' (errno: 150)
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
+ERROR HY000: Can't create table '#sql-temporary' (errno: 150)
+ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL;
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
+ERROR HY000: Can't create table '#sql-temporary' (errno: 150)
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` bigint(12) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ PRIMARY KEY (`c2`,`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `fk_t2_ca` (`c3`,`c2`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+CREATE INDEX i_t2_c2_c1 ON t2(c2, c1);
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `fk_t2_ca` (`c3`,`c2`),
+ KEY `i_t2_c2_c1` (`c2`,`c1`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1, c2);
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `fk_t2_ca` (`c3`,`c2`),
+ KEY `i_t2_c2_c1` (`c2`,`c1`),
+ KEY `i_t2_c3_c1_c2` (`c3`,`c1`,`c2`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `i_t2_c2_c1` (`c2`,`c1`),
+ KEY `i_t2_c3_c1_c2` (`c3`,`c1`,`c2`),
+ KEY `i_t2_c3_c2` (`c3`,`c2`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
+SET GLOBAL innodb_file_format_check=@save_innodb_file_format_check;
=== added file 'mysql-test/r/innodb-index_ucs2.result'
--- a/mysql-test/r/innodb-index_ucs2.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb-index_ucs2.result 2009-06-09 09:25:18 +0000
@@ -0,0 +1,116 @@
+create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb default charset=ucs2;
+insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
+commit;
+alter table t1 add unique index (b);
+ERROR 23000: Duplicate entry '2' for key 'b'
+insert into t1 values(8,9,'fff','fff');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=ucs2
+alter table t1 add index (b);
+insert into t1 values(10,10,'kkk','iii');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+select * from t1 force index(b) order by b;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 6
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `b` (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=ucs2
+alter table t1 add unique index (c), add index (d);
+insert into t1 values(11,11,'aaa','mmm');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+11 11 aaa mmm
+select * from t1 force index(b) order by b;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+11 11 aaa mmm
+select * from t1 force index(c) order by c;
+a b c d
+11 11 aaa mmm
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+select * from t1 force index(d) order by d;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+11 11 aaa mmm
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 7
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 21 NULL 7
+explain select * from t1 force index(d) order by d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL d 43 NULL 7
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `c` (`c`),
+ KEY `b` (`b`),
+ KEY `d` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=ucs2
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+drop table t1;
=== added file 'mysql-test/r/innodb-timeout.result'
--- a/mysql-test/r/innodb-timeout.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb-timeout.result 2009-06-09 09:25:18 +0000
@@ -0,0 +1,38 @@
+set global innodb_lock_wait_timeout=42;
+select @@innodb_lock_wait_timeout;
+@@innodb_lock_wait_timeout
+42
+set innodb_lock_wait_timeout=1;
+select @@innodb_lock_wait_timeout;
+@@innodb_lock_wait_timeout
+1
+select @@innodb_lock_wait_timeout;
+@@innodb_lock_wait_timeout
+42
+set global innodb_lock_wait_timeout=347;
+select @@innodb_lock_wait_timeout;
+@@innodb_lock_wait_timeout
+42
+set innodb_lock_wait_timeout=1;
+select @@innodb_lock_wait_timeout;
+@@innodb_lock_wait_timeout
+1
+select @@innodb_lock_wait_timeout;
+@@innodb_lock_wait_timeout
+347
+create table t1(a int primary key)engine=innodb;
+begin;
+insert into t1 values(1),(2),(3);
+select * from t1 for update;
+commit;
+a
+1
+2
+3
+begin;
+insert into t1 values(4);
+select * from t1 for update;
+commit;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+drop table t1;
+set global innodb_lock_wait_timeout=50;
=== added file 'mysql-test/r/innodb-use-sys-malloc.result'
--- a/mysql-test/r/innodb-use-sys-malloc.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb-use-sys-malloc.result 2009-06-09 09:25:18 +0000
@@ -0,0 +1,48 @@
+SELECT @@GLOBAL.innodb_use_sys_malloc;
+@@GLOBAL.innodb_use_sys_malloc
+1
+1 Expected
+SET @@GLOBAL.innodb_use_sys_malloc=0;
+ERROR HY000: Variable 'innodb_use_sys_malloc' is a read only variable
+Expected error 'Read only variable'
+SELECT @@GLOBAL.innodb_use_sys_malloc;
+@@GLOBAL.innodb_use_sys_malloc
+1
+1 Expected
+drop table if exists t1;
+create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
+insert into t1 values (1),(2),(3),(4),(5),(6),(7);
+select * from t1;
+a
+1
+2
+3
+4
+5
+6
+7
+drop table t1;
+SELECT @@GLOBAL.innodb_use_sys_malloc;
+@@GLOBAL.innodb_use_sys_malloc
+1
+1 Expected
+SET @@GLOBAL.innodb_use_sys_malloc=0;
+ERROR HY000: Variable 'innodb_use_sys_malloc' is a read only variable
+Expected error 'Read only variable'
+SELECT @@GLOBAL.innodb_use_sys_malloc;
+@@GLOBAL.innodb_use_sys_malloc
+1
+1 Expected
+drop table if exists t1;
+create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
+insert into t1 values (1),(2),(3),(4),(5),(6),(7);
+select * from t1;
+a
+1
+2
+3
+4
+5
+6
+7
+drop table t1;
=== added file 'mysql-test/r/innodb-zip.result'
--- a/mysql-test/r/innodb-zip.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb-zip.result 2009-06-09 09:25:18 +0000
@@ -0,0 +1,421 @@
+set global innodb_file_per_table=off;
+set global innodb_file_format=`0`;
+create table t0(a int primary key) engine=innodb row_format=compressed;
+Warnings:
+Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
+Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
+create table t00(a int primary key) engine=innodb
+key_block_size=4 row_format=compressed;
+Warnings:
+Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
+Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
+Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
+Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
+Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
+create table t1(a int primary key) engine=innodb row_format=dynamic;
+Warnings:
+Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
+Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
+create table t2(a int primary key) engine=innodb row_format=redundant;
+create table t3(a int primary key) engine=innodb row_format=compact;
+create table t4(a int primary key) engine=innodb key_block_size=9;
+Warnings:
+Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
+Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
+Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9.
+create table t5(a int primary key) engine=innodb
+key_block_size=1 row_format=redundant;
+Warnings:
+Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
+Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
+Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1.
+set global innodb_file_per_table=on;
+create table t6(a int primary key) engine=innodb
+key_block_size=1 row_format=redundant;
+Warnings:
+Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
+Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1.
+set global innodb_file_format=`1`;
+create table t7(a int primary key) engine=innodb
+key_block_size=1 row_format=redundant;
+Warnings:
+Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
+create table t8(a int primary key) engine=innodb
+key_block_size=1 row_format=fixed;
+Warnings:
+Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
+Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
+create table t9(a int primary key) engine=innodb
+key_block_size=1 row_format=compact;
+Warnings:
+Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
+create table t10(a int primary key) engine=innodb
+key_block_size=1 row_format=dynamic;
+Warnings:
+Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
+create table t11(a int primary key) engine=innodb
+key_block_size=1 row_format=compressed;
+create table t12(a int primary key) engine=innodb
+key_block_size=1;
+create table t13(a int primary key) engine=innodb
+row_format=compressed;
+create table t14(a int primary key) engine=innodb key_block_size=9;
+Warnings:
+Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9.
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+table_schema table_name row_format
+test t0 Compact
+test t00 Compact
+test t1 Compact
+test t10 Dynamic
+test t11 Compressed
+test t12 Compressed
+test t13 Compressed
+test t14 Compact
+test t2 Redundant
+test t3 Compact
+test t4 Compact
+test t5 Redundant
+test t6 Redundant
+test t7 Redundant
+test t8 Compact
+test t9 Compact
+drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14;
+alter table t1 key_block_size=0;
+Warnings:
+Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=0.
+alter table t1 row_format=dynamic;
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+table_schema table_name row_format
+test t1 Dynamic
+alter table t1 row_format=compact;
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+table_schema table_name row_format
+test t1 Compact
+alter table t1 row_format=redundant;
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+table_schema table_name row_format
+test t1 Redundant
+drop table t1;
+create table t1(a int not null, b text, index(b(10))) engine=innodb
+key_block_size=1;
+create table t2(b text)engine=innodb;
+insert into t2 values(concat('1abcdefghijklmnopqrstuvwxyz', repeat('A',5000)));
+insert into t1 select 1, b from t2;
+commit;
+begin;
+update t1 set b=repeat('B',100);
+select a,left(b,40) from t1 natural join t2;
+a left(b,40)
+1 1abcdefghijklmnopqrstuvwxyzAAAAAAAAAAAAA
+rollback;
+select a,left(b,40) from t1 natural join t2;
+a left(b,40)
+1 1abcdefghijklmnopqrstuvwxyzAAAAAAAAAAAAA
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+table_schema table_name row_format
+test t1 Compressed
+test t2 Compact
+drop table t1,t2;
+SET SESSION innodb_strict_mode = off;
+CREATE TABLE t1(
+c TEXT NOT NULL, d TEXT NOT NULL,
+PRIMARY KEY (c(767),d(767)))
+ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
+ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs
+CREATE TABLE t1(
+c TEXT NOT NULL, d TEXT NOT NULL,
+PRIMARY KEY (c(767),d(767)))
+ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;
+ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs
+CREATE TABLE t1(
+c TEXT NOT NULL, d TEXT NOT NULL,
+PRIMARY KEY (c(767),d(767)))
+ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
+drop table t1;
+CREATE TABLE t1(c TEXT, PRIMARY KEY (c(440)))
+ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
+ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs
+CREATE TABLE t1(c TEXT, PRIMARY KEY (c(438)))
+ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
+INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512));
+DROP TABLE t1;
+create table t1( c1 int not null, c2 blob, c3 blob, c4 blob,
+primary key(c1, c2(22), c3(22)))
+engine = innodb row_format = dynamic;
+begin;
+insert into t1 values(1, repeat('A', 20000), repeat('B', 20000),
+repeat('C', 20000));
+update t1 set c3 = repeat('D', 20000) where c1 = 1;
+commit;
+select count(*) from t1 where c2 = repeat('A', 20000);
+count(*)
+1
+select count(*) from t1 where c3 = repeat('D', 20000);
+count(*)
+1
+select count(*) from t1 where c4 = repeat('C', 20000);
+count(*)
+1
+update t1 set c3 = repeat('E', 20000) where c1 = 1;
+drop table t1;
+set global innodb_file_format=`0`;
+select @@innodb_file_format;
+@@innodb_file_format
+Antelope
+set global innodb_file_format=`1`;
+select @@innodb_file_format;
+@@innodb_file_format
+Barracuda
+set global innodb_file_format=`2`;
+ERROR HY000: Incorrect arguments to SET
+set global innodb_file_format=`-1`;
+ERROR HY000: Incorrect arguments to SET
+set global innodb_file_format=`Antelope`;
+set global innodb_file_format=`Barracuda`;
+set global innodb_file_format=`Cheetah`;
+ERROR HY000: Incorrect arguments to SET
+set global innodb_file_format=`abc`;
+ERROR HY000: Incorrect arguments to SET
+set global innodb_file_format=`1a`;
+ERROR HY000: Incorrect arguments to SET
+set global innodb_file_format=``;
+ERROR HY000: Incorrect arguments to SET
+set global innodb_file_per_table = on;
+set global innodb_file_format = `1`;
+set innodb_strict_mode = off;
+create table t1 (id int primary key) engine = innodb key_block_size = 0;
+Warnings:
+Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=0.
+drop table t1;
+set innodb_strict_mode = on;
+create table t1 (id int primary key) engine = innodb key_block_size = 0;
+ERROR HY000: Can't create table 'test.t1' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 0. Valid values are [1, 2, 4, 8, 16]
+Error 1005 Can't create table 'test.t1' (errno: 1478)
+create table t2 (id int primary key) engine = innodb key_block_size = 9;
+ERROR HY000: Can't create table 'test.t2' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
+Error 1005 Can't create table 'test.t2' (errno: 1478)
+create table t3 (id int primary key) engine = innodb key_block_size = 1;
+create table t4 (id int primary key) engine = innodb key_block_size = 2;
+create table t5 (id int primary key) engine = innodb key_block_size = 4;
+create table t6 (id int primary key) engine = innodb key_block_size = 8;
+create table t7 (id int primary key) engine = innodb key_block_size = 16;
+create table t8 (id int primary key) engine = innodb row_format = compressed;
+create table t9 (id int primary key) engine = innodb row_format = dynamic;
+create table t10(id int primary key) engine = innodb row_format = compact;
+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';
+table_schema table_name row_format
+test t10 Compact
+test t11 Redundant
+test t3 Compressed
+test t4 Compressed
+test t5 Compressed
+test t6 Compressed
+test t7 Compressed
+test t8 Compressed
+test t9 Dynamic
+drop table t3, t4, t5, t6, t7, t8, t9, t10, t11;
+create table t1 (id int primary key) engine = innodb
+key_block_size = 8 row_format = compressed;
+create table t2 (id int primary key) engine = innodb
+key_block_size = 8 row_format = redundant;
+ERROR HY000: Can't create table 'test.t2' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.
+Error 1005 Can't create table 'test.t2' (errno: 1478)
+create table t3 (id int primary key) engine = innodb
+key_block_size = 8 row_format = compact;
+ERROR HY000: Can't create table 'test.t3' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
+Error 1005 Can't create table 'test.t3' (errno: 1478)
+create table t4 (id int primary key) engine = innodb
+key_block_size = 8 row_format = dynamic;
+ERROR HY000: Can't create table 'test.t4' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.
+Error 1005 Can't create table 'test.t4' (errno: 1478)
+create table t5 (id int primary key) engine = innodb
+key_block_size = 8 row_format = default;
+ERROR HY000: Can't create table 'test.t5' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
+Error 1005 Can't create table 'test.t5' (errno: 1478)
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+table_schema table_name row_format
+test t1 Compressed
+drop table t1;
+create table t1 (id int primary key) engine = innodb
+key_block_size = 9 row_format = redundant;
+ERROR HY000: Can't create table 'test.t1' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
+Error 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.
+Error 1005 Can't create table 'test.t1' (errno: 1478)
+create table t2 (id int primary key) engine = innodb
+key_block_size = 9 row_format = compact;
+ERROR HY000: Can't create table 'test.t2' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
+Error 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
+Error 1005 Can't create table 'test.t2' (errno: 1478)
+create table t2 (id int primary key) engine = innodb
+key_block_size = 9 row_format = dynamic;
+ERROR HY000: Can't create table 'test.t2' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
+Error 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.
+Error 1005 Can't create table 'test.t2' (errno: 1478)
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+table_schema table_name row_format
+set global innodb_file_per_table = off;
+create table t1 (id int primary key) engine = innodb key_block_size = 1;
+ERROR HY000: Can't create table 'test.t1' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
+Error 1005 Can't create table 'test.t1' (errno: 1478)
+create table t2 (id int primary key) engine = innodb key_block_size = 2;
+ERROR HY000: Can't create table 'test.t2' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
+Error 1005 Can't create table 'test.t2' (errno: 1478)
+create table t3 (id int primary key) engine = innodb key_block_size = 4;
+ERROR HY000: Can't create table 'test.t3' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
+Error 1005 Can't create table 'test.t3' (errno: 1478)
+create table t4 (id int primary key) engine = innodb key_block_size = 8;
+ERROR HY000: Can't create table 'test.t4' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
+Error 1005 Can't create table 'test.t4' (errno: 1478)
+create table t5 (id int primary key) engine = innodb key_block_size = 16;
+ERROR HY000: Can't create table 'test.t5' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
+Error 1005 Can't create table 'test.t5' (errno: 1478)
+create table t6 (id int primary key) engine = innodb row_format = compressed;
+ERROR HY000: Can't create table 'test.t6' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
+Error 1005 Can't create table 'test.t6' (errno: 1478)
+create table t7 (id int primary key) engine = innodb row_format = dynamic;
+ERROR HY000: Can't create table 'test.t7' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
+Error 1005 Can't create table 'test.t7' (errno: 1478)
+create table t8 (id int primary key) engine = innodb row_format = compact;
+create table t9 (id int primary key) engine = innodb row_format = redundant;
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+table_schema table_name row_format
+test t8 Compact
+test t9 Redundant
+drop table t8, t9;
+set global innodb_file_per_table = on;
+set global innodb_file_format = `0`;
+create table t1 (id int primary key) engine = innodb key_block_size = 1;
+ERROR HY000: Can't create table 'test.t1' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
+Error 1005 Can't create table 'test.t1' (errno: 1478)
+create table t2 (id int primary key) engine = innodb key_block_size = 2;
+ERROR HY000: Can't create table 'test.t2' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
+Error 1005 Can't create table 'test.t2' (errno: 1478)
+create table t3 (id int primary key) engine = innodb key_block_size = 4;
+ERROR HY000: Can't create table 'test.t3' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
+Error 1005 Can't create table 'test.t3' (errno: 1478)
+create table t4 (id int primary key) engine = innodb key_block_size = 8;
+ERROR HY000: Can't create table 'test.t4' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
+Error 1005 Can't create table 'test.t4' (errno: 1478)
+create table t5 (id int primary key) engine = innodb key_block_size = 16;
+ERROR HY000: Can't create table 'test.t5' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
+Error 1005 Can't create table 'test.t5' (errno: 1478)
+create table t6 (id int primary key) engine = innodb row_format = compressed;
+ERROR HY000: Can't create table 'test.t6' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope.
+Error 1005 Can't create table 'test.t6' (errno: 1478)
+create table t7 (id int primary key) engine = innodb row_format = dynamic;
+ERROR HY000: Can't create table 'test.t7' (errno: 1478)
+show errors;
+Level Code Message
+Error 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
+Error 1005 Can't create table 'test.t7' (errno: 1478)
+create table t8 (id int primary key) engine = innodb row_format = compact;
+create table t9 (id int primary key) engine = innodb row_format = redundant;
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+table_schema table_name row_format
+test t8 Compact
+test t9 Redundant
+drop table t8, t9;
+set global innodb_file_per_table=0;
+set global innodb_file_format=Antelope;
+set global innodb_file_per_table=on;
+set global innodb_file_format=`Barracuda`;
+set global innodb_file_format_check=`Antelope`;
+create table normal_table (
+c1 int
+) engine = innodb;
+select @@innodb_file_format_check;
+@@innodb_file_format_check
+Antelope
+create table zip_table (
+c1 int
+) engine = innodb key_block_size = 8;
+select @@innodb_file_format_check;
+@@innodb_file_format_check
+Barracuda
+set global innodb_file_format_check=`Antelope`;
+select @@innodb_file_format_check;
+@@innodb_file_format_check
+Antelope
+show table status;
+select @@innodb_file_format_check;
+@@innodb_file_format_check
+Barracuda
+drop table normal_table, zip_table;
=== modified file 'mysql-test/r/innodb.result'
--- a/mysql-test/r/innodb.result 2009-02-19 09:01:25 +0000
+++ b/mysql-test/r/innodb.result 2009-06-09 09:25:18 +0000
@@ -1508,7 +1508,7 @@ t2 CREATE TABLE `t2` (
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop index id2 on t2;
drop index id on t2;
-Got one of the listed errors
+ERROR HY000: Cannot drop index 'id': needed in a foreign key constraint
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
@@ -1738,7 +1738,7 @@ count(*)
drop table t1;
show status like "Innodb_buffer_pool_pages_total";
Variable_name Value
-Innodb_buffer_pool_pages_total 512
+Innodb_buffer_pool_pages_total 511
show status like "Innodb_page_size";
Variable_name Value
Innodb_page_size 16384
@@ -1784,7 +1784,7 @@ innodb_sync_spin_loops 20
SET @old_innodb_thread_concurrency= @@global.innodb_thread_concurrency;
show variables like "innodb_thread_concurrency";
Variable_name Value
-innodb_thread_concurrency 8
+innodb_thread_concurrency 0
set global innodb_thread_concurrency=1001;
Warnings:
Warning 1292 Truncated incorrect thread_concurrency value: '1001'
@@ -1970,7 +1970,7 @@ explain select count(*) from t1 where v
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 13 const # Using where; Using index
alter table t1 add unique(v);
-ERROR 23000: Duplicate entry '{ ' for key 'v_2'
+ERROR 23000: Duplicate entry 'v' for key 'v_2'
alter table t1 add key(v);
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
qq
@@ -2377,6 +2377,8 @@ t1 CREATE TABLE `t1` (
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 (v varchar(10), c char(10)) row_format=fixed;
+Warnings:
+Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
@@ -3190,6 +3192,7 @@ t1 CREATE TABLE `t1` (
CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1
DROP TABLE t1,t2;
+set innodb_strict_mode=on;
CREATE TABLE t1 (
c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
=== modified file 'mysql-test/r/innodb_bug34300.result'
--- a/mysql-test/r/innodb_bug34300.result 2009-02-19 09:01:25 +0000
+++ b/mysql-test/r/innodb_bug34300.result 2009-06-09 09:25:18 +0000
@@ -1,8 +1,4 @@
-SELECT f4, f8 FROM bug34300;
f4 f8
xxx zzz
-ALTER TABLE bug34300 ADD COLUMN (f10 INT);
-SELECT f4, f8 FROM bug34300;
f4 f8
xxx zzz
-DROP TABLE bug34300;
=== added file 'mysql-test/r/innodb_bug36169.result'
--- a/mysql-test/r/innodb_bug36169.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb_bug36169.result 2009-06-09 09:25:18 +0000
@@ -0,0 +1,5 @@
+SET @save_innodb_file_format=@@global.innodb_file_format;
+SET @save_innodb_file_format_check=@@global.innodb_file_format_check;
+SET @save_innodb_file_per_table=@@global.innodb_file_per_table;
+SET GLOBAL innodb_file_format='Barracuda';
+SET GLOBAL innodb_file_per_table=ON;
=== added file 'mysql-test/r/innodb_bug36172.result'
--- a/mysql-test/r/innodb_bug36172.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb_bug36172.result 2009-06-09 09:25:18 +0000
@@ -0,0 +1 @@
+SET storage_engine=InnoDB;
=== added file 'mysql-test/r/innodb_bug40360.result'
--- a/mysql-test/r/innodb_bug40360.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb_bug40360.result 2009-06-09 09:25:18 +0000
@@ -0,0 +1,4 @@
+SET TX_ISOLATION='READ-COMMITTED';
+CREATE TABLE bug40360 (a INT) engine=innodb;
+INSERT INTO bug40360 VALUES (1);
+DROP TABLE bug40360;
=== added file 'mysql-test/r/innodb_bug41904.result'
--- a/mysql-test/r/innodb_bug41904.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb_bug41904.result 2009-06-09 09:25:18 +0000
@@ -0,0 +1,4 @@
+CREATE TABLE bug41904 (id INT PRIMARY KEY, uniquecol CHAR(15)) ENGINE=InnoDB;
+INSERT INTO bug41904 VALUES (1,NULL), (2,NULL);
+CREATE UNIQUE INDEX ui ON bug41904 (uniquecol);
+DROP TABLE bug41904;
=== added file 'mysql-test/r/innodb_information_schema.result'
--- a/mysql-test/r/innodb_information_schema.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb_information_schema.result 2009-06-09 09:25:18 +0000
@@ -0,0 +1,23 @@
+lock_mode lock_type lock_table lock_index lock_rec lock_data
+X RECORD `test`.```t'\"_str` `PRIMARY` 2 '1', 'abc', '''abc', 'abc''', 'a''bc', 'a''bc''', '''abc'''''
+X RECORD `test`.```t'\"_str` `PRIMARY` 2 '1', 'abc', '''abc', 'abc''', 'a''bc', 'a''bc''', '''abc'''''
+X RECORD `test`.```t'\"_str` `PRIMARY` 3 '2', 'abc', '"abc', 'abc"', 'a"bc', 'a"bc"', '"abc""'
+X RECORD `test`.```t'\"_str` `PRIMARY` 3 '2', 'abc', '"abc', 'abc"', 'a"bc', 'a"bc"', '"abc""'
+X RECORD `test`.```t'\"_str` `PRIMARY` 4 '3', 'abc', '\\abc', 'abc\\', 'a\\bc', 'a\\bc\\', '\\abc\\\\'
+X RECORD `test`.```t'\"_str` `PRIMARY` 4 '3', 'abc', '\\abc', 'abc\\', 'a\\bc', 'a\\bc\\', '\\abc\\\\'
+X RECORD `test`.```t'\"_str` `PRIMARY` 5 '4', 'abc', '\0abc', 'abc\0', 'a\0bc', 'a\0bc\0', 'a\0bc\0\0'
+X RECORD `test`.```t'\"_str` `PRIMARY` 5 '4', 'abc', '\0abc', 'abc\0', 'a\0bc', 'a\0bc\0', 'a\0bc\0\0'
+X RECORD `test`.`t_min` `PRIMARY` 2 -128, 0, -32768, 0, -8388608, 0, -2147483648, 0, -9223372036854775808, 0
+X RECORD `test`.`t_min` `PRIMARY` 2 -128, 0, -32768, 0, -8388608, 0, -2147483648, 0, -9223372036854775808, 0
+X RECORD `test`.`t_max` `PRIMARY` 2 127, 255, 32767, 65535, 8388607, 16777215, 2147483647, 4294967295, 9223372036854775807, 18446744073709551615
+X RECORD `test`.`t_max` `PRIMARY` 2 127, 255, 32767, 65535, 8388607, 16777215, 2147483647, 4294967295, 9223372036854775807, 18446744073709551615
+X RECORD `test`.```t'\"_str` `PRIMARY` 1 supremum pseudo-record
+X RECORD `test`.```t'\"_str` `PRIMARY` 1 supremum pseudo-record
+lock_table COUNT(*)
+`test`.`t_max` 2
+`test`.`t_min` 2
+`test`.```t'\"_str` 10
+lock_table COUNT(*)
+"test"."t_max" 2
+"test"."t_min" 2
+"test"."`t'\""_str" 10
=== added file 'mysql-test/r/innodb_xtradb_bug317074.result'
--- a/mysql-test/r/innodb_xtradb_bug317074.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb_xtradb_bug317074.result 2009-06-09 09:25:18 +0000
@@ -0,0 +1,5 @@
+SET @save_innodb_file_format=@@global.innodb_file_format;
+SET @save_innodb_file_format_check=@@global.innodb_file_format_check;
+SET @save_innodb_file_per_table=@@global.innodb_file_per_table;
+SET GLOBAL innodb_file_format='Barracuda';
+SET GLOBAL innodb_file_per_table=ON;
=== modified file 'mysql-test/r/row-checksum-old.result'
--- a/mysql-test/r/row-checksum-old.result 2008-06-28 12:45:15 +0000
+++ b/mysql-test/r/row-checksum-old.result 2009-06-09 09:25:18 +0000
@@ -72,6 +72,8 @@ Table Checksum
test.t1 4108368782
drop table if exists t1;
create table t1 (a int null, v varchar(100)) engine=innodb checksum=0 row_format=fixed;
+Warnings:
+Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
insert into t1 values(null, null), (1, "hello");
checksum table t1;
Table Checksum
=== modified file 'mysql-test/r/row-checksum.result'
--- a/mysql-test/r/row-checksum.result 2008-06-28 12:45:15 +0000
+++ b/mysql-test/r/row-checksum.result 2009-06-09 09:25:18 +0000
@@ -72,6 +72,8 @@ Table Checksum
test.t1 3885665021
drop table if exists t1;
create table t1 (a int null, v varchar(100)) engine=innodb checksum=0 row_format=fixed;
+Warnings:
+Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
insert into t1 values(null, null), (1, "hello");
checksum table t1;
Table Checksum
=== modified file 'mysql-test/r/variables.result'
--- a/mysql-test/r/variables.result 2009-02-19 09:01:25 +0000
+++ b/mysql-test/r/variables.result 2009-06-09 09:25:18 +0000
@@ -300,14 +300,14 @@ set @@rand_seed1=10000000,@@rand_seed2=1
select ROUND(RAND(),5);
ROUND(RAND(),5)
0.02887
-show variables like '%alloc%';
+show variables where variable_name in ('query_alloc_block_size', 'query_prealloc_size', 'range_alloc_block_size', 'transaction_alloc_block_size', 'transaction_prealloc_size');
Variable_name Value
query_alloc_block_size 8192
query_prealloc_size 8192
range_alloc_block_size 4096
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
-select * from information_schema.session_variables where variable_name like '%alloc%' order by 1;
+select * from information_schema.session_variables where variable_name in ('query_alloc_block_size', 'query_prealloc_size', 'range_alloc_block_size', 'transaction_alloc_block_size', 'transaction_prealloc_size') order by 1;
VARIABLE_NAME VARIABLE_VALUE
QUERY_ALLOC_BLOCK_SIZE 8192
QUERY_PREALLOC_SIZE 8192
@@ -322,14 +322,14 @@ set @@transaction_prealloc_size=1024*21-
select @@query_alloc_block_size;
@@query_alloc_block_size
17408
-show variables like '%alloc%';
+show variables where variable_name in ('query_alloc_block_size', 'query_prealloc_size', 'range_alloc_block_size', 'transaction_alloc_block_size', 'transaction_prealloc_size');
Variable_name Value
query_alloc_block_size 17408
query_prealloc_size 18432
range_alloc_block_size 16384
transaction_alloc_block_size 19456
transaction_prealloc_size 20480
-select * from information_schema.session_variables where variable_name like '%alloc%' order by 1;
+select * from information_schema.session_variables where variable_name in ('query_alloc_block_size', 'query_prealloc_size', 'range_alloc_block_size', 'transaction_alloc_block_size', 'transaction_prealloc_size') order by 1;
VARIABLE_NAME VARIABLE_VALUE
QUERY_ALLOC_BLOCK_SIZE 17408
QUERY_PREALLOC_SIZE 18432
@@ -339,14 +339,14 @@ TRANSACTION_PREALLOC_SIZE 20480
set @@range_alloc_block_size=default;
set @@query_alloc_block_size=default, @@query_prealloc_size=default;
set transaction_alloc_block_size=default, @@transaction_prealloc_size=default;
-show variables like '%alloc%';
+show variables where variable_name in ('query_alloc_block_size', 'query_prealloc_size', 'range_alloc_block_size', 'transaction_alloc_block_size', 'transaction_prealloc_size');
Variable_name Value
query_alloc_block_size 8192
query_prealloc_size 8192
range_alloc_block_size 4096
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
-select * from information_schema.session_variables where variable_name like '%alloc%' order by 1;
+select * from information_schema.session_variables where variable_name in ('query_alloc_block_size', 'query_prealloc_size', 'range_alloc_block_size', 'transaction_alloc_block_size', 'transaction_prealloc_size') order by 1;
VARIABLE_NAME VARIABLE_VALUE
QUERY_ALLOC_BLOCK_SIZE 8192
QUERY_PREALLOC_SIZE 8192
=== modified file 'mysql-test/t/information_schema.test'
--- a/mysql-test/t/information_schema.test 2009-04-08 16:55:26 +0000
+++ b/mysql-test/t/information_schema.test 2009-06-09 09:25:18 +0000
@@ -43,7 +43,7 @@ create view v1 (c) as
table_name<>'ndb_binlog_index' AND
table_name<>'ndb_apply_status' AND
NOT (table_schema = 'INFORMATION_SCHEMA' AND table_name LIKE 'PBXT_%');
-select * from v1;
+select * from v1 ORDER BY c COLLATE utf8_bin;
select c,table_name from v1
inner join information_schema.TABLES v2 on (v1.c=v2.table_name)
=== added file 'mysql-test/t/innodb-analyze.test'
--- a/mysql-test/t/innodb-analyze.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb-analyze.test 2009-06-09 09:25:18 +0000
@@ -0,0 +1,65 @@
+#
+# Test that mysqld does not crash when running ANALYZE TABLE with
+# different values of the parameter innodb_stats_sample_pages.
+#
+
+-- source include/have_innodb.inc
+
+# we care only that the following SQL commands do not produce errors
+# and do not crash the server
+-- disable_query_log
+-- disable_result_log
+-- enable_warnings
+
+SET @save_innodb_stats_sample_pages=@@innodb_stats_sample_pages;
+SET GLOBAL innodb_stats_sample_pages=0;
+
+# check that the value has been adjusted to 1
+-- enable_result_log
+SHOW VARIABLES LIKE 'innodb_stats_sample_pages';
+-- disable_result_log
+
+CREATE TABLE innodb_analyze (
+ a INT,
+ b INT,
+ KEY(a),
+ KEY(b,a)
+) ENGINE=InnoDB;
+
+# test with empty table
+
+ANALYZE TABLE innodb_analyze;
+
+SET GLOBAL innodb_stats_sample_pages=2;
+ANALYZE TABLE innodb_analyze;
+
+SET GLOBAL innodb_stats_sample_pages=4;
+ANALYZE TABLE innodb_analyze;
+
+SET GLOBAL innodb_stats_sample_pages=8;
+ANALYZE TABLE innodb_analyze;
+
+SET GLOBAL innodb_stats_sample_pages=16;
+ANALYZE TABLE innodb_analyze;
+
+INSERT INTO innodb_analyze VALUES
+(1,1), (1,1), (1,2), (1,3), (1,4), (1,5),
+(8,1), (8,8), (8,2), (7,1), (1,4), (3,5);
+
+SET GLOBAL innodb_stats_sample_pages=1;
+ANALYZE TABLE innodb_analyze;
+
+SET GLOBAL innodb_stats_sample_pages=2;
+ANALYZE TABLE innodb_analyze;
+
+SET GLOBAL innodb_stats_sample_pages=4;
+ANALYZE TABLE innodb_analyze;
+
+SET GLOBAL innodb_stats_sample_pages=8;
+ANALYZE TABLE innodb_analyze;
+
+SET GLOBAL innodb_stats_sample_pages=16;
+ANALYZE TABLE innodb_analyze;
+
+SET GLOBAL innodb_stats_sample_pages=@save_innodb_stats_sample_pages;
+DROP TABLE innodb_analyze;
=== modified file 'mysql-test/t/innodb-autoinc.test'
--- a/mysql-test/t/innodb-autoinc.test 2009-04-08 16:55:26 +0000
+++ b/mysql-test/t/innodb-autoinc.test 2009-06-09 09:25:18 +0000
@@ -390,3 +390,91 @@ INSERT INTO t1 VALUES (NULL);
#endif
SELECT * FROM t1;
DROP TABLE t1;
+
+#
+# Check for floating point autoinc column handling
+#
+SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
+SET @@INSERT_ID=1;
+SHOW VARIABLES LIKE "auto_inc%";
+CREATE TABLE t1 (c1 DOUBLE NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(NULL, 1);
+INSERT INTO t1 VALUES(NULL, 2);
+SELECT * FROM t1;
+ALTER TABLE t1 CHANGE c1 c1 SERIAL;
+SELECT * FROM t1;
+INSERT INTO t1 VALUES(NULL, 3);
+INSERT INTO t1 VALUES(NULL, 4);
+SELECT * FROM t1;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (c1 FLOAT NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(NULL, 1);
+INSERT INTO t1 VALUES(NULL, 2);
+SELECT * FROM t1;
+ALTER TABLE t1 CHANGE c1 c1 SERIAL;
+SELECT * FROM t1;
+INSERT INTO t1 VALUES(NULL, 3);
+INSERT INTO t1 VALUES(NULL, 4);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+#
+# Bug# 42714: AUTOINC column calculated next value not greater than highest
+# value stored in table.
+#
+SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=5;
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+CREATE TABLE t1 (
+ a INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
+ b INT(10) UNSIGNED NOT NULL,
+ c ENUM('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (a)) ENGINE = InnoDB;
+CREATE TABLE t2 (
+ m INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
+ n INT(10) UNSIGNED NOT NULL,
+ o enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (m)) ENGINE = InnoDB;
+INSERT INTO t2 (n,o) VALUES
+ (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'),
+ (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false');
+SHOW CREATE TABLE t2;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+DROP TABLE t2;
+#
+# 43203: Overflow from auto incrementing causes server segv
+#
+
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+CREATE TABLE t1(
+ c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
+ PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
+CREATE TABLE t2(
+ c1 TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT
+ PRIMARY KEY) ENGINE=InnoDB;
+-- error ER_DUP_ENTRY,1062
+INSERT INTO t2 SELECT c1 FROM t1;
+-- error ER_DUP_ENTRY,1467
+INSERT INTO t2 SELECT NULL FROM t1;
+DROP TABLE t1;
+DROP TABLE t2;
=== added file 'mysql-test/t/innodb-index.test'
--- a/mysql-test/t/innodb-index.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb-index.test 2009-06-09 09:25:18 +0000
@@ -0,0 +1,516 @@
+-- source include/have_innodb.inc
+
+SET @save_innodb_file_format_check=@@global.innodb_file_format_check;
+
+create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb;
+insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak');
+commit;
+--error ER_DUP_KEYNAME
+alter table t1 add index b (b), add index b (b);
+--error ER_DUP_FIELDNAME
+alter table t1 add index (b,b);
+alter table t1 add index d2 (d);
+show create table t1;
+explain select * from t1 force index(d2) order by d;
+select * from t1 force index (d2) order by d;
+--error ER_DUP_ENTRY
+alter table t1 add unique index (b);
+show create table t1;
+alter table t1 add index (b);
+show create table t1;
+
+# Check how existing tables interfere with temporary tables.
+CREATE TABLE `t1#1`(a INT PRIMARY KEY) ENGINE=InnoDB;
+
+call mtr.add_suppression(" table `test`\\.`t1#[12]` already exists in InnoDB internal");
+
+--error 156
+alter table t1 add unique index (c), add index (d);
+rename table `t1#1` to `t1#2`;
+--error 156
+alter table t1 add unique index (c), add index (d);
+drop table `t1#2`;
+
+alter table t1 add unique index (c), add index (d);
+show create table t1;
+explain select * from t1 force index(c) order by c;
+alter table t1 add primary key (a), drop index c;
+show create table t1;
+--error ER_MULTIPLE_PRI_KEY
+alter table t1 add primary key (c);
+--error ER_DUP_ENTRY
+alter table t1 drop primary key, add primary key (b);
+create unique index c on t1 (c);
+show create table t1;
+explain select * from t1 force index(c) order by c;
+select * from t1 force index(c) order by c;
+alter table t1 drop index b, add index (b);
+show create table t1;
+insert into t1 values(6,1,'ggg','ggg');
+select * from t1;
+select * from t1 force index(b) order by b;
+select * from t1 force index(c) order by c;
+select * from t1 force index(d) order by d;
+explain select * from t1 force index(b) order by b;
+explain select * from t1 force index(c) order by c;
+explain select * from t1 force index(d) order by d;
+show create table t1;
+drop table t1;
+
+create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
+insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ad','ad'),(4,4,'afe','afe');
+commit;
+alter table t1 add index (c(2));
+show create table t1;
+alter table t1 add unique index (d(10));
+show create table t1;
+insert into t1 values(5,1,'ggg','ggg');
+select * from t1;
+select * from t1 force index(c) order by c;
+select * from t1 force index(d) order by d;
+explain select * from t1 order by b;
+explain select * from t1 force index(c) order by c;
+explain select * from t1 force index(d) order by d;
+show create table t1;
+alter table t1 drop index d;
+insert into t1 values(8,9,'fff','fff');
+select * from t1;
+select * from t1 force index(c) order by c;
+explain select * from t1 order by b;
+explain select * from t1 force index(c) order by c;
+explain select * from t1 order by d;
+show create table t1;
+drop table t1;
+
+create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
+insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
+commit;
+alter table t1 add unique index (b,c);
+insert into t1 values(8,9,'fff','fff');
+select * from t1;
+select * from t1 force index(b) order by b;
+explain select * from t1 force index(b) order by b;
+show create table t1;
+alter table t1 add index (b,c);
+insert into t1 values(11,11,'kkk','kkk');
+select * from t1;
+select * from t1 force index(b) order by b;
+explain select * from t1 force index(b) order by b;
+show create table t1;
+alter table t1 add unique index (c,d);
+insert into t1 values(13,13,'yyy','aaa');
+select * from t1;
+select * from t1 force index(b) order by b;
+select * from t1 force index(c) order by c;
+explain select * from t1 force index(b) order by b;
+explain select * from t1 force index(c) order by c;
+show create table t1;
+drop table t1;
+
+create table t1(a int not null, b int not null, c int, primary key (a), key (b)) engine = innodb;
+create table t3(a int not null, c int not null, d int, primary key (a), key (c)) engine = innodb;
+create table t4(a int not null, d int not null, e int, primary key (a), key (d)) engine = innodb;
+create table t2(a int not null, b int not null, c int not null, d int not null, e int,
+foreign key (b) references t1(b) on delete cascade,
+foreign key (c) references t3(c), foreign key (d) references t4(d))
+engine = innodb;
+--error ER_DROP_INDEX_FK
+alter table t1 drop index b;
+--error ER_DROP_INDEX_FK
+alter table t3 drop index c;
+--error ER_DROP_INDEX_FK
+alter table t4 drop index d;
+--error ER_DROP_INDEX_FK
+alter table t2 drop index b;
+--error ER_DROP_INDEX_FK
+alter table t2 drop index b, drop index c, drop index d;
+# Apparently, the following makes mysql_alter_table() drop index d.
+create unique index dc on t2 (d,c);
+create index dc on t1 (b,c);
+# This should preserve the foreign key constraints.
+alter table t2 add primary key (a);
+insert into t1 values (1,1,1);
+insert into t3 values (1,1,1);
+insert into t4 values (1,1,1);
+insert into t2 values (1,1,1,1,1);
+commit;
+alter table t4 add constraint dc foreign key (a) references t1(a);
+show create table t4;
+--replace_regex /'test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
+# a foreign key 'test/dc' already exists
+--error ER_CANT_CREATE_TABLE
+alter table t3 add constraint dc foreign key (a) references t1(a);
+show create table t3;
+alter table t2 drop index b, add index (b);
+show create table t2;
+--error ER_ROW_IS_REFERENCED_2
+delete from t1;
+--error ER_CANT_DROP_FIELD_OR_KEY
+drop index dc on t4;
+# there is no foreign key dc on t3
+--replace_regex /'\.\/test\/#sql2-[0-9a-f-]*'/'#sql2-temporary'/
+--error ER_ERROR_ON_RENAME
+alter table t3 drop foreign key dc;
+alter table t4 drop foreign key dc;
+select * from t2;
+delete from t1;
+select * from t2;
+
+drop table t2,t4,t3,t1;
+
+-- let charset = utf8
+-- source include/innodb-index.inc
+
+create table t1(a int not null, b int) engine = innodb;
+insert into t1 values (1,1),(1,1),(1,1),(1,1);
+--error ER_DUP_ENTRY
+alter table t1 add unique index (a);
+--error ER_DUP_ENTRY
+alter table t1 add unique index (b);
+--error ER_DUP_ENTRY
+alter table t1 add unique index (a), add unique index(b);
+show create table t1;
+drop table t1;
+
+create table t1(a int not null, c int not null,b int, primary key(a), unique key(c), key(b)) engine = innodb;
+alter table t1 drop index c, drop index b;
+show create table t1;
+drop table t1;
+
+create table t1(a int not null, b int, primary key(a)) engine = innodb;
+alter table t1 add index (b);
+show create table t1;
+drop table t1;
+
+create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
+insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ac','ac'),(4,4,'afe','afe'),(5,4,'affe','affe');
+--error ER_DUP_ENTRY
+alter table t1 add unique index (b), add unique index (c), add unique index (d);
+--error ER_DUP_ENTRY
+alter table t1 add unique index (c), add unique index (b), add index (d);
+show create table t1;
+drop table t1;
+
+create table t1(a int not null, b int not null, c int, primary key (a), key(c)) engine=innodb;
+insert into t1 values (5,1,5),(4,2,4),(3,3,3),(2,4,2),(1,5,1);
+alter table t1 add unique index (b);
+insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17);
+show create table t1;
+check table t1;
+explain select * from t1 force index(c) order by c;
+explain select * from t1 order by a;
+explain select * from t1 force index(b) order by b;
+select * from t1 order by a;
+select * from t1 force index(b) order by b;
+select * from t1 force index(c) order by c;
+drop table t1;
+
+create table t1(a int not null, b int not null) engine=innodb;
+insert into t1 values (1,1);
+alter table t1 add primary key(b);
+insert into t1 values (2,2);
+show create table t1;
+check table t1;
+select * from t1;
+explain select * from t1;
+explain select * from t1 order by a;
+explain select * from t1 order by b;
+checksum table t1;
+drop table t1;
+
+create table t1(a int not null) engine=innodb;
+insert into t1 values (1);
+alter table t1 add primary key(a);
+insert into t1 values (2);
+show create table t1;
+check table t1;
+commit;
+select * from t1;
+explain select * from t1;
+explain select * from t1 order by a;
+drop table t1;
+
+create table t2(d varchar(17) primary key) engine=innodb default charset=utf8;
+create table t3(a int primary key) engine=innodb;
+
+insert into t3 values(22),(44),(33),(55),(66);
+
+insert into t2 values ('jejdkrun87'),('adfd72nh9k'),
+('adfdpplkeock'),('adfdijnmnb78k'),('adfdijn0loKNHJik');
+
+create table t1(a int, b blob, c text, d text not null)
+engine=innodb default charset = utf8;
+
+# r2667 The following test is disabled because MySQL behavior changed.
+# r2667 The test was added with this comment:
+# r2667
+# r2667 ------------------------------------------------------------------------
+# r2667 r1699 | marko | 2007-08-10 19:53:19 +0300 (Fri, 10 Aug 2007) | 5 lines
+# r2667
+# r2667 branches/zip: Add changes that accidentally omitted from r1698:
+# r2667
+# r2667 innodb-index.test, innodb-index.result: Add a test for creating
+# r2667 a PRIMARY KEY on a column that contains a NULL value.
+# r2667 ------------------------------------------------------------------------
+# r2667
+# r2667 but in BZR-r2667:
+# r2667 http://bazaar.launchpad.net/~mysql/mysql-server/mysql-5.1/revision/davi%40mysql.com-20080617141221-8yre8ys9j4uw3xx5?start_revid=joerg%40mysql.com-20080630105418-7qoe5ehomgrcdb89
+# r2667 MySQL changed the behavior to do full table copy when creating PRIMARY INDEX
+# r2667 on a non-NULL column instead of calling ::add_index() which would fail (and
+# r2667 this is what we were testing here). Before r2667 the code execution path was
+# r2667 like this (when adding PRIMARY INDEX on a non-NULL column with ALTER TABLE):
+# r2667
+# r2667 mysql_alter_table()
+# r2667 compare_tables() // would return ALTER_TABLE_INDEX_CHANGED
+# r2667 ::add_index() // would fail with "primary index cannot contain NULL"
+# r2667
+# r2667 after r2667 the code execution path is the following:
+# r2667
+# r2667 mysql_alter_table()
+# r2667 compare_tables() // returns ALTER_TABLE_DATA_CHANGED
+# r2667 full copy is done, without calling ::add_index()
+# r2667
+# r2667 To enable, remove "# r2667: " below.
+# r2667
+# r2667: insert into t1 values (null,null,null,'null');
+insert into t1
+select a,left(repeat(d,100*a),65535),repeat(d,20*a),d from t2,t3;
+drop table t2, t3;
+select count(*) from t1 where a=44;
+select a,
+length(b),b=left(repeat(d,100*a),65535),length(c),c=repeat(d,20*a),d from t1;
+# r2667: --error ER_PRIMARY_CANT_HAVE_NULL
+# r2667: alter table t1 add primary key (a), add key (b(20));
+# r2667: delete from t1 where d='null';
+--error ER_DUP_ENTRY
+alter table t1 add primary key (a), add key (b(20));
+delete from t1 where a%2;
+check table t1;
+alter table t1 add primary key (a,b(255),c(255)), add key (b(767));
+select count(*) from t1 where a=44;
+select a,
+length(b),b=left(repeat(d,100*a),65535),length(c),c=repeat(d,20*a),d from t1;
+show create table t1;
+check table t1;
+explain select * from t1 where b like 'adfd%';
+
+#
+# Test locking
+#
+
+create table t2(a int, b varchar(255), primary key(a,b)) engine=innodb;
+insert into t2 select a,left(b,255) from t1;
+drop table t1;
+rename table t2 to t1;
+
+connect (a,localhost,root,,);
+connect (b,localhost,root,,);
+connection a;
+set innodb_lock_wait_timeout=1;
+begin;
+# Obtain an IX lock on the table
+select a from t1 limit 1 for update;
+connection b;
+set innodb_lock_wait_timeout=1;
+# This would require an S lock on the table, conflicting with the IX lock.
+--error ER_LOCK_WAIT_TIMEOUT
+create index t1ba on t1 (b,a);
+connection a;
+commit;
+begin;
+# Obtain an IS lock on the table
+select a from t1 limit 1 lock in share mode;
+connection b;
+# This will require an S lock on the table. No conflict with the IS lock.
+create index t1ba on t1 (b,a);
+# This would require an X lock on the table, conflicting with the IS lock.
+--error ER_LOCK_WAIT_TIMEOUT
+drop index t1ba on t1;
+connection a;
+commit;
+explain select a from t1 order by b;
+--send
+select a,sleep(2+a/100) from t1 order by b limit 3;
+
+# The following DROP INDEX will succeed, altough the SELECT above has
+# opened a read view. However, during the execution of the SELECT,
+# MySQL should hold a table lock that should block the execution
+# of the DROP INDEX below.
+
+connection b;
+select sleep(1);
+drop index t1ba on t1;
+
+# After the index was dropped, subsequent SELECTs will use the same
+# read view, but they should not be accessing the dropped index any more.
+
+connection a;
+reap;
+explain select a from t1 order by b;
+select a from t1 order by b limit 3;
+commit;
+
+connection default;
+disconnect a;
+disconnect b;
+
+drop table t1;
+
+let $per_table=`select @@innodb_file_per_table`;
+let $format=`select @@innodb_file_format`;
+set global innodb_file_per_table=on;
+set global innodb_file_format='Barracuda';
+# Test creating a table that could lead to undo log overflow.
+# In the undo log, we write a 768-byte prefix (REC_MAX_INDEX_COL_LEN)
+# of each externally stored column that appears as a column prefix in an index.
+# For this test case, it would suffice to write 1 byte, though.
+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));
+--error 139
+create index t1u on t1 (u(1));
+--error 139
+create index t1ut on t1 (u(1), t(1));
+create index t1st on t1 (s(1), t(1));
+show create table t1;
+--error 139
+create index t1u on t1 (u(1));
+alter table t1 row_format=compact;
+create index t1u on t1 (u(1));
+
+drop table t1;
+eval set global innodb_file_per_table=$per_table;
+eval set global innodb_file_format=$format;
+
+#
+# Test to check whether CREATE INDEX handles implicit foreign key
+# constraint modifications (Issue #70, Bug #38786)
+#
+SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
+SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
+
+CREATE TABLE t1(
+ c1 BIGINT(12) NOT NULL,
+ PRIMARY KEY (c1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+CREATE TABLE t2(
+ c1 BIGINT(16) NOT NULL,
+ c2 BIGINT(12) NOT NULL,
+ c3 BIGINT(12) NOT NULL,
+ PRIMARY KEY (c1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+ FOREIGN KEY (c3) REFERENCES t1(c1);
+
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
+
+SHOW CREATE TABLE t2;
+
+CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
+
+SHOW CREATE TABLE t2;
+
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
+
+--error ER_NO_REFERENCED_ROW_2
+INSERT INTO t2 VALUES(0,0,0);
+INSERT INTO t1 VALUES(0);
+INSERT INTO t2 VALUES(0,0,0);
+
+DROP TABLE t2;
+
+CREATE TABLE t2(
+ c1 BIGINT(16) NOT NULL,
+ c2 BIGINT(12) NOT NULL,
+ c3 BIGINT(12) NOT NULL,
+ PRIMARY KEY (c1,c2,c3)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+ FOREIGN KEY (c3) REFERENCES t1(c1);
+
+SHOW CREATE TABLE t2;
+
+CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
+
+SHOW CREATE TABLE t2;
+--error ER_NO_REFERENCED_ROW_2
+INSERT INTO t2 VALUES(0,0,1);
+INSERT INTO t2 VALUES(0,0,0);
+--error ER_ROW_IS_REFERENCED_2
+DELETE FROM t1;
+DELETE FROM t2;
+
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE t1(
+ c1 BIGINT(12) NOT NULL,
+ c2 INT(4) NOT NULL,
+ PRIMARY KEY (c2,c1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+CREATE TABLE t2(
+ c1 BIGINT(16) NOT NULL,
+ c2 BIGINT(12) NOT NULL,
+ c3 BIGINT(12) NOT NULL,
+ PRIMARY KEY (c1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--replace_regex /'test\.#sql-[0-9_a-f-]*'/'#sql-temporary'/
+--error ER_CANT_CREATE_TABLE
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+ FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1);
+--replace_regex /'test\.#sql-[0-9_a-f-]*'/'#sql-temporary'/
+--error ER_CANT_CREATE_TABLE
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+ FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
+--replace_regex /'test\.#sql-[0-9_a-f-]*'/'#sql-temporary'/
+--error ER_CANT_CREATE_TABLE
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+ FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
+ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL;
+--replace_regex /'test\.#sql-[0-9_a-f-]*'/'#sql-temporary'/
+--error ER_CANT_CREATE_TABLE
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+ FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
+
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+ FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+CREATE INDEX i_t2_c2_c1 ON t2(c2, c1);
+SHOW CREATE TABLE t2;
+CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1, c2);
+SHOW CREATE TABLE t2;
+CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
+SHOW CREATE TABLE t2;
+
+DROP TABLE t2;
+DROP TABLE t1;
+SET GLOBAL innodb_file_format_check=@save_innodb_file_format_check;
=== added file 'mysql-test/t/innodb-index_ucs2.test'
--- a/mysql-test/t/innodb-index_ucs2.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb-index_ucs2.test 2009-06-09 09:25:18 +0000
@@ -0,0 +1,5 @@
+-- source include/have_innodb.inc
+-- source include/have_ucs2.inc
+
+-- let charset = ucs2
+-- source include/innodb-index.inc
=== added file 'mysql-test/t/innodb-timeout.test'
--- a/mysql-test/t/innodb-timeout.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb-timeout.test 2009-06-09 09:25:18 +0000
@@ -0,0 +1,64 @@
+-- source include/have_innodb.inc
+
+let $timeout=`select @@innodb_lock_wait_timeout`;
+set global innodb_lock_wait_timeout=42;
+
+connect (a,localhost,root,,);
+connect (b,localhost,root,,);
+
+connection a;
+select @@innodb_lock_wait_timeout;
+set innodb_lock_wait_timeout=1;
+select @@innodb_lock_wait_timeout;
+
+connection b;
+select @@innodb_lock_wait_timeout;
+set global innodb_lock_wait_timeout=347;
+select @@innodb_lock_wait_timeout;
+set innodb_lock_wait_timeout=1;
+select @@innodb_lock_wait_timeout;
+
+connect (c,localhost,root,,);
+connection c;
+select @@innodb_lock_wait_timeout;
+connection default;
+disconnect c;
+
+connection a;
+create table t1(a int primary key)engine=innodb;
+begin;
+insert into t1 values(1),(2),(3);
+
+connection b;
+--send
+select * from t1 for update;
+
+connection a;
+commit;
+
+connection b;
+reap;
+
+connection a;
+begin;
+insert into t1 values(4);
+
+connection b;
+--send
+select * from t1 for update;
+
+connection a;
+sleep 2;
+commit;
+
+connection b;
+--error ER_LOCK_WAIT_TIMEOUT
+reap;
+drop table t1;
+
+connection default;
+
+disconnect a;
+disconnect b;
+
+eval set global innodb_lock_wait_timeout=$timeout;
=== added file 'mysql-test/t/innodb-use-sys-malloc-master.opt'
--- a/mysql-test/t/innodb-use-sys-malloc-master.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb-use-sys-malloc-master.opt 2009-06-09 09:25:18 +0000
@@ -0,0 +1,2 @@
+--innodb-use-sys-malloc=true
+--innodb-use-sys-malloc=true
=== added file 'mysql-test/t/innodb-use-sys-malloc.test'
--- a/mysql-test/t/innodb-use-sys-malloc.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb-use-sys-malloc.test 2009-06-09 09:25:18 +0000
@@ -0,0 +1,48 @@
+--source include/have_innodb.inc
+
+#display current value of innodb_use_sys_malloc
+SELECT @@GLOBAL.innodb_use_sys_malloc;
+--echo 1 Expected
+
+#try changing it. Should fail.
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+SET @@GLOBAL.innodb_use_sys_malloc=0;
+--echo Expected error 'Read only variable'
+
+SELECT @@GLOBAL.innodb_use_sys_malloc;
+--echo 1 Expected
+
+
+#do some stuff to see if it works.
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
+insert into t1 values (1),(2),(3),(4),(5),(6),(7);
+select * from t1;
+drop table t1;
+--source include/have_innodb.inc
+
+#display current value of innodb_use_sys_malloc
+SELECT @@GLOBAL.innodb_use_sys_malloc;
+--echo 1 Expected
+
+#try changing it. Should fail.
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+SET @@GLOBAL.innodb_use_sys_malloc=0;
+--echo Expected error 'Read only variable'
+
+SELECT @@GLOBAL.innodb_use_sys_malloc;
+--echo 1 Expected
+
+
+#do some stuff to see if it works.
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
+insert into t1 values (1),(2),(3),(4),(5),(6),(7);
+select * from t1;
+drop table t1;
=== added file 'mysql-test/t/innodb-zip.test'
--- a/mysql-test/t/innodb-zip.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb-zip.test 2009-06-09 09:25:18 +0000
@@ -0,0 +1,347 @@
+-- source include/have_innodb.inc
+
+let $per_table=`select @@innodb_file_per_table`;
+let $format=`select @@innodb_file_format`;
+let $innodb_file_format_check_orig=`select @@innodb_file_format_check`;
+set global innodb_file_per_table=off;
+set global innodb_file_format=`0`;
+
+create table t0(a int primary key) engine=innodb row_format=compressed;
+create table t00(a int primary key) engine=innodb
+key_block_size=4 row_format=compressed;
+create table t1(a int primary key) engine=innodb row_format=dynamic;
+create table t2(a int primary key) engine=innodb row_format=redundant;
+create table t3(a int primary key) engine=innodb row_format=compact;
+create table t4(a int primary key) engine=innodb key_block_size=9;
+create table t5(a int primary key) engine=innodb
+key_block_size=1 row_format=redundant;
+
+set global innodb_file_per_table=on;
+create table t6(a int primary key) engine=innodb
+key_block_size=1 row_format=redundant;
+set global innodb_file_format=`1`;
+create table t7(a int primary key) engine=innodb
+key_block_size=1 row_format=redundant;
+create table t8(a int primary key) engine=innodb
+key_block_size=1 row_format=fixed;
+create table t9(a int primary key) engine=innodb
+key_block_size=1 row_format=compact;
+create table t10(a int primary key) engine=innodb
+key_block_size=1 row_format=dynamic;
+create table t11(a int primary key) engine=innodb
+key_block_size=1 row_format=compressed;
+create table t12(a int primary key) engine=innodb
+key_block_size=1;
+create table t13(a int primary key) engine=innodb
+row_format=compressed;
+create table t14(a int primary key) engine=innodb key_block_size=9;
+
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+
+drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14;
+alter table t1 key_block_size=0;
+alter table t1 row_format=dynamic;
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+alter table t1 row_format=compact;
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+alter table t1 row_format=redundant;
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+drop table t1;
+
+create table t1(a int not null, b text, index(b(10))) engine=innodb
+key_block_size=1;
+
+create table t2(b text)engine=innodb;
+insert into t2 values(concat('1abcdefghijklmnopqrstuvwxyz', repeat('A',5000)));
+
+insert into t1 select 1, b from t2;
+commit;
+
+connect (a,localhost,root,,);
+connect (b,localhost,root,,);
+
+connection a;
+begin;
+update t1 set b=repeat('B',100);
+
+connection b;
+select a,left(b,40) from t1 natural join t2;
+
+connection a;
+rollback;
+
+connection b;
+select a,left(b,40) from t1 natural join t2;
+
+connection default;
+disconnect a;
+disconnect b;
+
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+drop table t1,t2;
+
+# The following should fail even in non-strict mode.
+SET SESSION innodb_strict_mode = off;
+--error ER_TOO_BIG_ROWSIZE
+CREATE TABLE t1(
+ c TEXT NOT NULL, d TEXT NOT NULL,
+ PRIMARY KEY (c(767),d(767)))
+ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
+--error ER_TOO_BIG_ROWSIZE
+CREATE TABLE t1(
+ c TEXT NOT NULL, d TEXT NOT NULL,
+ PRIMARY KEY (c(767),d(767)))
+ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;
+CREATE TABLE t1(
+ c TEXT NOT NULL, d TEXT NOT NULL,
+ PRIMARY KEY (c(767),d(767)))
+ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
+drop table t1;
+--error ER_TOO_BIG_ROWSIZE
+CREATE TABLE t1(c TEXT, PRIMARY KEY (c(440)))
+ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
+# The maximum key size for a compressed row actually depends on the
+# version of libz used, as account must be taken for the maximum
+# compressed size of a key, and this differs between libz
+# versions. Some libz versions allow a size of 439, some only 438.
+CREATE TABLE t1(c TEXT, PRIMARY KEY (c(438)))
+ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
+INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512));
+DROP TABLE t1;
+
+#
+# Test blob column inheritance (mantis issue#36)
+#
+
+create table t1( c1 int not null, c2 blob, c3 blob, c4 blob,
+ primary key(c1, c2(22), c3(22)))
+ engine = innodb row_format = dynamic;
+begin;
+insert into t1 values(1, repeat('A', 20000), repeat('B', 20000),
+ repeat('C', 20000));
+
+update t1 set c3 = repeat('D', 20000) where c1 = 1;
+commit;
+
+# one blob column which is unchanged in update and part of PK
+# one blob column which is changed and part of of PK
+# one blob column which is not part of PK and is unchanged
+select count(*) from t1 where c2 = repeat('A', 20000);
+select count(*) from t1 where c3 = repeat('D', 20000);
+select count(*) from t1 where c4 = repeat('C', 20000);
+
+update t1 set c3 = repeat('E', 20000) where c1 = 1;
+drop table t1;
+
+#
+#
+# Test innodb_file_format
+#
+set global innodb_file_format=`0`;
+select @@innodb_file_format;
+set global innodb_file_format=`1`;
+select @@innodb_file_format;
+-- error ER_WRONG_ARGUMENTS
+set global innodb_file_format=`2`;
+-- error ER_WRONG_ARGUMENTS
+set global innodb_file_format=`-1`;
+set global innodb_file_format=`Antelope`;
+set global innodb_file_format=`Barracuda`;
+-- error ER_WRONG_ARGUMENTS
+set global innodb_file_format=`Cheetah`;
+-- error ER_WRONG_ARGUMENTS
+set global innodb_file_format=`abc`;
+-- error ER_WRONG_ARGUMENTS
+set global innodb_file_format=`1a`;
+-- error ER_WRONG_ARGUMENTS
+set global innodb_file_format=``;
+
+#test strict mode.
+# this does not work anymore, has been removed from mysqltest
+# -- enable_errors
+set global innodb_file_per_table = on;
+set global innodb_file_format = `1`;
+
+set innodb_strict_mode = off;
+create table t1 (id int primary key) engine = innodb key_block_size = 0;
+drop table t1;
+
+#set strict_mode
+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 errors;
+
+--error ER_CANT_CREATE_TABLE
+create table t2 (id int primary key) engine = innodb key_block_size = 9;
+show errors;
+
+
+create table t3 (id int primary key) engine = innodb key_block_size = 1;
+create table t4 (id int primary key) engine = innodb key_block_size = 2;
+create table t5 (id int primary key) engine = innodb key_block_size = 4;
+create table t6 (id int primary key) engine = innodb key_block_size = 8;
+create table t7 (id int primary key) engine = innodb key_block_size = 16;
+
+#check various ROW_FORMAT values.
+create table t8 (id int primary key) engine = innodb row_format = compressed;
+create table t9 (id int primary key) engine = innodb row_format = dynamic;
+create table t10(id int primary key) engine = innodb row_format = compact;
+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;
+
+#test different values of ROW_FORMAT with KEY_BLOCK_SIZE
+create table t1 (id int primary key) engine = innodb
+key_block_size = 8 row_format = compressed;
+
+--error ER_CANT_CREATE_TABLE
+create table t2 (id int primary key) engine = innodb
+key_block_size = 8 row_format = redundant;
+show errors;
+
+--error ER_CANT_CREATE_TABLE
+create table t3 (id int primary key) engine = innodb
+key_block_size = 8 row_format = compact;
+show errors;
+
+--error ER_CANT_CREATE_TABLE
+create table t4 (id int primary key) engine = innodb
+key_block_size = 8 row_format = dynamic;
+show errors;
+
+--error ER_CANT_CREATE_TABLE
+create table t5 (id int primary key) engine = innodb
+key_block_size = 8 row_format = default;
+show errors;
+
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+drop table t1;
+
+#test multiple errors
+--error ER_CANT_CREATE_TABLE
+create table t1 (id int primary key) engine = innodb
+key_block_size = 9 row_format = redundant;
+show errors;
+
+--error ER_CANT_CREATE_TABLE
+create table t2 (id int primary key) engine = innodb
+key_block_size = 9 row_format = compact;
+show errors;
+
+--error ER_CANT_CREATE_TABLE
+create table t2 (id int primary key) engine = innodb
+key_block_size = 9 row_format = dynamic;
+show errors;
+
+SELECT table_schema, table_name, row_format
+FROM information_schema.tables WHERE engine='innodb';
+
+#test valid values with innodb_file_per_table unset
+set global innodb_file_per_table = off;
+
+--error ER_CANT_CREATE_TABLE
+create table t1 (id int primary key) engine = innodb key_block_size = 1;
+show errors;
+--error ER_CANT_CREATE_TABLE
+create table t2 (id int primary key) engine = innodb key_block_size = 2;
+show errors;
+--error ER_CANT_CREATE_TABLE
+create table t3 (id int primary key) engine = innodb key_block_size = 4;
+show errors;
+--error ER_CANT_CREATE_TABLE
+create table t4 (id int primary key) engine = innodb key_block_size = 8;
+show errors;
+--error ER_CANT_CREATE_TABLE
+create table t5 (id int primary key) engine = innodb key_block_size = 16;
+show errors;
+--error ER_CANT_CREATE_TABLE
+create table t6 (id int primary key) engine = innodb row_format = compressed;
+show errors;
+--error ER_CANT_CREATE_TABLE
+create table t7 (id int primary key) engine = innodb row_format = dynamic;
+show errors;
+create table t8 (id int primary key) engine = innodb row_format = compact;
+create table t9 (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 t8, t9;
+
+#test valid values with innodb_file_format unset
+set global innodb_file_per_table = on;
+set global innodb_file_format = `0`;
+
+--error ER_CANT_CREATE_TABLE
+create table t1 (id int primary key) engine = innodb key_block_size = 1;
+show errors;
+--error ER_CANT_CREATE_TABLE
+create table t2 (id int primary key) engine = innodb key_block_size = 2;
+show errors;
+--error ER_CANT_CREATE_TABLE
+create table t3 (id int primary key) engine = innodb key_block_size = 4;
+show errors;
+--error ER_CANT_CREATE_TABLE
+create table t4 (id int primary key) engine = innodb key_block_size = 8;
+show errors;
+--error ER_CANT_CREATE_TABLE
+create table t5 (id int primary key) engine = innodb key_block_size = 16;
+show errors;
+--error ER_CANT_CREATE_TABLE
+create table t6 (id int primary key) engine = innodb row_format = compressed;
+show errors;
+--error ER_CANT_CREATE_TABLE
+create table t7 (id int primary key) engine = innodb row_format = dynamic;
+show errors;
+create table t8 (id int primary key) engine = innodb row_format = compact;
+create table t9 (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 t8, t9;
+
+eval set global innodb_file_per_table=$per_table;
+eval set global innodb_file_format=$format;
+#
+# Testing of tablespace tagging
+#
+-- disable_info
+set global innodb_file_per_table=on;
+set global innodb_file_format=`Barracuda`;
+set global innodb_file_format_check=`Antelope`;
+create table normal_table (
+ c1 int
+) engine = innodb;
+select @@innodb_file_format_check;
+create table zip_table (
+ c1 int
+) engine = innodb key_block_size = 8;
+select @@innodb_file_format_check;
+set global innodb_file_format_check=`Antelope`;
+select @@innodb_file_format_check;
+-- disable_result_log
+show table status;
+-- enable_result_log
+select @@innodb_file_format_check;
+drop table normal_table, zip_table;
+-- disable_result_log
+
+#
+# restore environment to the state it was before this test execution
+#
+
+-- disable_query_log
+eval set global innodb_file_format=$format;
+eval set global innodb_file_per_table=$per_table;
+eval set global innodb_file_format_check=$innodb_file_format_check_orig;
=== modified file 'mysql-test/t/innodb.test'
--- a/mysql-test/t/innodb.test 2009-01-23 12:22:05 +0000
+++ b/mysql-test/t/innodb.test 2009-06-09 09:25:18 +0000
@@ -1126,7 +1126,7 @@ show create table t2;
create index id2 on t2 (id);
show create table t2;
drop index id2 on t2;
---error 1025,1025
+--error ER_DROP_INDEX_FK
drop index id on t2;
show create table t2;
drop table t2;
@@ -1294,6 +1294,7 @@ drop table t1;
# Test for testable InnoDB status variables. This test
# uses previous ones(pages_created, rows_deleted, ...).
+--replace_result 512 511
show status like "Innodb_buffer_pool_pages_total";
show status like "Innodb_page_size";
show status like "Innodb_rows_deleted";
@@ -2357,6 +2358,7 @@ DROP TABLE t1,t2;
#
# Bug #21101 (Prints wrong error message if max row size is too large)
#
+set innodb_strict_mode=on;
--error 1118
CREATE TABLE t1 (
c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
=== modified file 'mysql-test/t/innodb_bug34300.test'
--- a/mysql-test/t/innodb_bug34300.test 2009-02-19 09:01:25 +0000
+++ b/mysql-test/t/innodb_bug34300.test 2009-06-09 09:25:18 +0000
@@ -9,6 +9,7 @@
-- disable_result_log
# set packet size and reconnect
+SET @save_max_allowed_packet=@@global.max_allowed_packet;
SET @@global.max_allowed_packet=16777216;
--connect (newconn, localhost, root,,)
@@ -21,7 +22,6 @@ CREATE TABLE bug34300 (
INSERT INTO bug34300 VALUES ('xxx', repeat('a', 8459264), 'zzz');
--- enable_query_log
-- enable_result_log
SELECT f4, f8 FROM bug34300;
@@ -31,11 +31,6 @@ ALTER TABLE bug34300 ADD COLUMN (f10 INT
SELECT f4, f8 FROM bug34300;
DROP TABLE bug34300;
-
disconnect newconn;
connection default;
---disable_result_log
---disable_query_log
-SET @@global.max_allowed_packet=default;
---enable_result_log
---enable_query_log
+SET @@global.max_allowed_packet=@save_max_allowed_packet;
=== added file 'mysql-test/t/innodb_bug36169.test'
--- a/mysql-test/t/innodb_bug36169.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb_bug36169.test 2009-06-09 09:25:18 +0000
@@ -0,0 +1,1162 @@
+#
+# Bug#36169 create innodb compressed table with too large row size crashed
+# http://bugs.mysql.com/36169
+#
+
+-- source include/have_innodb.inc
+
+SET @save_innodb_file_format=@@global.innodb_file_format;
+SET @save_innodb_file_format_check=@@global.innodb_file_format_check;
+SET @save_innodb_file_per_table=@@global.innodb_file_per_table;
+SET GLOBAL innodb_file_format='Barracuda';
+SET GLOBAL innodb_file_per_table=ON;
+
+#
+# The following is copied from http://bugs.mysql.com/36169
+# (http://bugs.mysql.com/file.php?id=9121)
+# Probably it can be simplified but that is not obvious.
+#
+
+# we care only that the following SQL commands do produce errors
+# as expected and do not crash the server
+-- disable_query_log
+-- disable_result_log
+
+# Generating 10 tables
+# Creating a table with 94 columns and 24 indexes
+DROP TABLE IF EXISTS `table0`;
+--error ER_TOO_BIG_ROWSIZE
+CREATE TABLE IF NOT EXISTS `table0`
+(`col0` BOOL,
+`col1` BOOL,
+`col2` TINYINT,
+`col3` DATE,
+`col4` TIME,
+`col5` SET ('test1','test2','test3'),
+`col6` TIME,
+`col7` TEXT,
+`col8` DECIMAL,
+`col9` SET ('test1','test2','test3'),
+`col10` FLOAT,
+`col11` DOUBLE PRECISION,
+`col12` ENUM ('test1','test2','test3'),
+`col13` TINYBLOB,
+`col14` YEAR,
+`col15` SET ('test1','test2','test3'),
+`col16` NUMERIC,
+`col17` NUMERIC,
+`col18` BLOB,
+`col19` DATETIME,
+`col20` DOUBLE PRECISION,
+`col21` DECIMAL,
+`col22` DATETIME,
+`col23` NUMERIC,
+`col24` NUMERIC,
+`col25` LONGTEXT,
+`col26` TINYBLOB,
+`col27` TIME,
+`col28` TINYBLOB,
+`col29` ENUM ('test1','test2','test3'),
+`col30` SMALLINT,
+`col31` REAL,
+`col32` FLOAT,
+`col33` CHAR (175),
+`col34` TINYTEXT,
+`col35` TINYTEXT,
+`col36` TINYBLOB,
+`col37` TINYBLOB,
+`col38` TINYTEXT,
+`col39` MEDIUMBLOB,
+`col40` TIMESTAMP,
+`col41` DOUBLE,
+`col42` SMALLINT,
+`col43` LONGBLOB,
+`col44` VARCHAR (80),
+`col45` MEDIUMTEXT,
+`col46` NUMERIC,
+`col47` BIGINT,
+`col48` DATE,
+`col49` TINYBLOB,
+`col50` DATE,
+`col51` BOOL,
+`col52` MEDIUMINT,
+`col53` FLOAT,
+`col54` TINYBLOB,
+`col55` LONGTEXT,
+`col56` SMALLINT,
+`col57` ENUM ('test1','test2','test3'),
+`col58` DATETIME,
+`col59` MEDIUMTEXT,
+`col60` VARCHAR (232),
+`col61` NUMERIC,
+`col62` YEAR,
+`col63` SMALLINT,
+`col64` TIMESTAMP,
+`col65` BLOB,
+`col66` LONGBLOB,
+`col67` INT,
+`col68` LONGTEXT,
+`col69` ENUM ('test1','test2','test3'),
+`col70` INT,
+`col71` TIME,
+`col72` TIMESTAMP,
+`col73` TIMESTAMP,
+`col74` VARCHAR (170),
+`col75` SET ('test1','test2','test3'),
+`col76` TINYBLOB,
+`col77` BIGINT,
+`col78` NUMERIC,
+`col79` DATETIME,
+`col80` YEAR,
+`col81` NUMERIC,
+`col82` LONGBLOB,
+`col83` TEXT,
+`col84` CHAR (83),
+`col85` DECIMAL,
+`col86` FLOAT,
+`col87` INT,
+`col88` VARCHAR (145),
+`col89` DATE,
+`col90` DECIMAL,
+`col91` DECIMAL,
+`col92` MEDIUMBLOB,
+`col93` TIME,
+KEY `idx0` (`col69`,`col90`,`col8`),
+KEY `idx1` (`col60`),
+KEY `idx2` (`col60`,`col70`,`col74`),
+KEY `idx3` (`col22`,`col32`,`col72`,`col30`),
+KEY `idx4` (`col29`),
+KEY `idx5` (`col19`,`col45`(143)),
+KEY `idx6` (`col46`,`col48`,`col5`,`col39`(118)),
+KEY `idx7` (`col48`,`col61`),
+KEY `idx8` (`col93`),
+KEY `idx9` (`col31`),
+KEY `idx10` (`col30`,`col21`),
+KEY `idx11` (`col67`),
+KEY `idx12` (`col44`,`col6`,`col8`,`col38`(226)),
+KEY `idx13` (`col71`,`col41`,`col15`,`col49`(88)),
+KEY `idx14` (`col78`),
+KEY `idx15` (`col63`,`col67`,`col64`),
+KEY `idx16` (`col17`,`col86`),
+KEY `idx17` (`col77`,`col56`,`col10`,`col55`(24)),
+KEY `idx18` (`col62`),
+KEY `idx19` (`col31`,`col57`,`col56`,`col53`),
+KEY `idx20` (`col46`),
+KEY `idx21` (`col83`(54)),
+KEY `idx22` (`col51`,`col7`(120)),
+KEY `idx23` (`col7`(163),`col31`,`col71`,`col14`)
+)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
+
+# Creating a table with 10 columns and 32 indexes
+DROP TABLE IF EXISTS `table1`;
+--error ER_TOO_BIG_ROWSIZE
+CREATE TABLE IF NOT EXISTS `table1`
+(`col0` CHAR (113),
+`col1` FLOAT,
+`col2` BIGINT,
+`col3` DECIMAL,
+`col4` BLOB,
+`col5` LONGTEXT,
+`col6` SET ('test1','test2','test3'),
+`col7` BIGINT,
+`col8` BIGINT,
+`col9` TINYBLOB,
+KEY `idx0` (`col5`(101),`col7`,`col8`),
+KEY `idx1` (`col8`),
+KEY `idx2` (`col4`(177),`col9`(126),`col6`,`col3`),
+KEY `idx3` (`col5`(160)),
+KEY `idx4` (`col9`(242)),
+KEY `idx5` (`col4`(139),`col2`,`col3`),
+KEY `idx6` (`col7`),
+KEY `idx7` (`col6`,`col2`,`col0`,`col3`),
+KEY `idx8` (`col9`(66)),
+KEY `idx9` (`col5`(253)),
+KEY `idx10` (`col1`,`col7`,`col2`),
+KEY `idx11` (`col9`(242),`col0`,`col8`,`col5`(163)),
+KEY `idx12` (`col8`),
+KEY `idx13` (`col0`,`col9`(37)),
+KEY `idx14` (`col0`),
+KEY `idx15` (`col5`(111)),
+KEY `idx16` (`col8`,`col0`,`col5`(13)),
+KEY `idx17` (`col4`(139)),
+KEY `idx18` (`col5`(189),`col2`,`col3`,`col9`(136)),
+KEY `idx19` (`col0`,`col3`,`col1`,`col8`),
+KEY `idx20` (`col8`),
+KEY `idx21` (`col0`,`col7`,`col9`(227),`col3`),
+KEY `idx22` (`col0`),
+KEY `idx23` (`col2`),
+KEY `idx24` (`col3`),
+KEY `idx25` (`col2`,`col3`),
+KEY `idx26` (`col0`),
+KEY `idx27` (`col5`(254)),
+KEY `idx28` (`col3`),
+KEY `idx29` (`col3`),
+KEY `idx30` (`col7`,`col3`,`col0`,`col4`(220)),
+KEY `idx31` (`col4`(1),`col0`)
+)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
+
+# Creating a table with 141 columns and 18 indexes
+DROP TABLE IF EXISTS `table2`;
+--error ER_TOO_BIG_ROWSIZE
+CREATE TABLE IF NOT EXISTS `table2`
+(`col0` BOOL,
+`col1` MEDIUMINT,
+`col2` VARCHAR (209),
+`col3` MEDIUMBLOB,
+`col4` CHAR (13),
+`col5` DOUBLE,
+`col6` TINYTEXT,
+`col7` REAL,
+`col8` SMALLINT,
+`col9` BLOB,
+`col10` TINYINT,
+`col11` DECIMAL,
+`col12` BLOB,
+`col13` DECIMAL,
+`col14` LONGBLOB,
+`col15` SMALLINT,
+`col16` LONGBLOB,
+`col17` TINYTEXT,
+`col18` FLOAT,
+`col19` CHAR (78),
+`col20` MEDIUMTEXT,
+`col21` SET ('test1','test2','test3'),
+`col22` MEDIUMINT,
+`col23` INT,
+`col24` MEDIUMBLOB,
+`col25` ENUM ('test1','test2','test3'),
+`col26` TINYBLOB,
+`col27` VARCHAR (116),
+`col28` TIMESTAMP,
+`col29` BLOB,
+`col30` SMALLINT,
+`col31` DOUBLE PRECISION,
+`col32` DECIMAL,
+`col33` DECIMAL,
+`col34` TEXT,
+`col35` MEDIUMINT,
+`col36` MEDIUMINT,
+`col37` BIGINT,
+`col38` VARCHAR (253),
+`col39` TINYBLOB,
+`col40` MEDIUMBLOB,
+`col41` BIGINT,
+`col42` DOUBLE,
+`col43` TEXT,
+`col44` BLOB,
+`col45` TIME,
+`col46` MEDIUMINT,
+`col47` DOUBLE PRECISION,
+`col48` SET ('test1','test2','test3'),
+`col49` DOUBLE PRECISION,
+`col50` VARCHAR (97),
+`col51` TEXT,
+`col52` NUMERIC,
+`col53` ENUM ('test1','test2','test3'),
+`col54` MEDIUMTEXT,
+`col55` MEDIUMINT,
+`col56` DATETIME,
+`col57` DATETIME,
+`col58` MEDIUMTEXT,
+`col59` CHAR (244),
+`col60` LONGBLOB,
+`col61` MEDIUMBLOB,
+`col62` DOUBLE,
+`col63` SMALLINT,
+`col64` BOOL,
+`col65` SMALLINT,
+`col66` VARCHAR (212),
+`col67` TIME,
+`col68` REAL,
+`col69` BOOL,
+`col70` BIGINT,
+`col71` DATE,
+`col72` TINYINT,
+`col73` ENUM ('test1','test2','test3'),
+`col74` DATE,
+`col75` TIME,
+`col76` DATETIME,
+`col77` BOOL,
+`col78` TINYTEXT,
+`col79` MEDIUMINT,
+`col80` NUMERIC,
+`col81` LONGTEXT,
+`col82` SET ('test1','test2','test3'),
+`col83` DOUBLE PRECISION,
+`col84` NUMERIC,
+`col85` VARCHAR (184),
+`col86` DOUBLE PRECISION,
+`col87` MEDIUMTEXT,
+`col88` MEDIUMBLOB,
+`col89` BOOL,
+`col90` SMALLINT,
+`col91` TINYINT,
+`col92` ENUM ('test1','test2','test3'),
+`col93` BOOL,
+`col94` TIMESTAMP,
+`col95` BOOL,
+`col96` MEDIUMTEXT,
+`col97` DECIMAL,
+`col98` BOOL,
+`col99` DECIMAL,
+`col100` MEDIUMINT,
+`col101` DOUBLE PRECISION,
+`col102` TINYINT,
+`col103` BOOL,
+`col104` MEDIUMINT,
+`col105` DECIMAL,
+`col106` NUMERIC,
+`col107` TIMESTAMP,
+`col108` MEDIUMBLOB,
+`col109` TINYBLOB,
+`col110` SET ('test1','test2','test3'),
+`col111` YEAR,
+`col112` TIMESTAMP,
+`col113` CHAR (201),
+`col114` BOOL,
+`col115` TINYINT,
+`col116` DOUBLE,
+`col117` TINYINT,
+`col118` TIMESTAMP,
+`col119` SET ('test1','test2','test3'),
+`col120` SMALLINT,
+`col121` TINYBLOB,
+`col122` TIMESTAMP,
+`col123` BLOB,
+`col124` DATE,
+`col125` SMALLINT,
+`col126` ENUM ('test1','test2','test3'),
+`col127` MEDIUMBLOB,
+`col128` DOUBLE PRECISION,
+`col129` REAL,
+`col130` VARCHAR (159),
+`col131` MEDIUMBLOB,
+`col132` BIGINT,
+`col133` INT,
+`col134` SET ('test1','test2','test3'),
+`col135` CHAR (198),
+`col136` SET ('test1','test2','test3'),
+`col137` MEDIUMTEXT,
+`col138` SMALLINT,
+`col139` BLOB,
+`col140` LONGBLOB,
+KEY `idx0` (`col14`(139),`col24`(208),`col38`,`col35`),
+KEY `idx1` (`col48`,`col118`,`col29`(131),`col100`),
+KEY `idx2` (`col86`,`col67`,`col43`(175)),
+KEY `idx3` (`col19`),
+KEY `idx4` (`col40`(220),`col67`),
+KEY `idx5` (`col99`,`col56`),
+KEY `idx6` (`col68`,`col28`,`col137`(157)),
+KEY `idx7` (`col51`(160),`col99`,`col45`,`col39`(9)),
+KEY `idx8` (`col15`,`col52`,`col90`,`col94`),
+KEY `idx9` (`col24`(3),`col139`(248),`col108`(118),`col41`),
+KEY `idx10` (`col36`,`col92`,`col114`),
+KEY `idx11` (`col115`,`col9`(116)),
+KEY `idx12` (`col130`,`col93`,`col134`),
+KEY `idx13` (`col123`(65)),
+KEY `idx14` (`col44`(90),`col86`,`col119`),
+KEY `idx15` (`col69`),
+KEY `idx16` (`col132`,`col81`(118),`col18`),
+KEY `idx17` (`col24`(250),`col7`,`col92`,`col45`)
+)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
+
+# Creating a table with 199 columns and 1 indexes
+DROP TABLE IF EXISTS `table3`;
+--error ER_TOO_BIG_ROWSIZE
+CREATE TABLE IF NOT EXISTS `table3`
+(`col0` SMALLINT,
+`col1` SET ('test1','test2','test3'),
+`col2` TINYTEXT,
+`col3` DOUBLE,
+`col4` NUMERIC,
+`col5` DATE,
+`col6` BIGINT,
+`col7` DOUBLE,
+`col8` TEXT,
+`col9` INT,
+`col10` REAL,
+`col11` TINYINT,
+`col12` NUMERIC,
+`col13` NUMERIC,
+`col14` TIME,
+`col15` DOUBLE,
+`col16` REAL,
+`col17` MEDIUMBLOB,
+`col18` YEAR,
+`col19` TINYTEXT,
+`col20` YEAR,
+`col21` CHAR (250),
+`col22` TINYINT,
+`col23` TINYINT,
+`col24` SMALLINT,
+`col25` DATETIME,
+`col26` MEDIUMINT,
+`col27` LONGBLOB,
+`col28` VARCHAR (106),
+`col29` FLOAT,
+`col30` MEDIUMTEXT,
+`col31` TINYBLOB,
+`col32` BIGINT,
+`col33` YEAR,
+`col34` REAL,
+`col35` MEDIUMBLOB,
+`col36` LONGTEXT,
+`col37` LONGBLOB,
+`col38` BIGINT,
+`col39` FLOAT,
+`col40` TIME,
+`col41` DATETIME,
+`col42` BOOL,
+`col43` BIGINT,
+`col44` SMALLINT,
+`col45` TIME,
+`col46` DOUBLE PRECISION,
+`col47` TIME,
+`col48` TINYTEXT,
+`col49` DOUBLE PRECISION,
+`col50` BIGINT,
+`col51` NUMERIC,
+`col52` TINYBLOB,
+`col53` DATE,
+`col54` DECIMAL,
+`col55` SMALLINT,
+`col56` TINYTEXT,
+`col57` ENUM ('test1','test2','test3'),
+`col58` YEAR,
+`col59` TIME,
+`col60` TINYINT,
+`col61` DECIMAL,
+`col62` DOUBLE,
+`col63` DATE,
+`col64` LONGTEXT,
+`col65` DOUBLE,
+`col66` VARCHAR (88),
+`col67` MEDIUMTEXT,
+`col68` DATE,
+`col69` MEDIUMINT,
+`col70` DECIMAL,
+`col71` MEDIUMTEXT,
+`col72` LONGTEXT,
+`col73` REAL,
+`col74` DOUBLE,
+`col75` TIME,
+`col76` DATE,
+`col77` DECIMAL,
+`col78` MEDIUMBLOB,
+`col79` NUMERIC,
+`col80` BIGINT,
+`col81` YEAR,
+`col82` SMALLINT,
+`col83` MEDIUMINT,
+`col84` TINYINT,
+`col85` MEDIUMBLOB,
+`col86` TIME,
+`col87` MEDIUMBLOB,
+`col88` LONGTEXT,
+`col89` BOOL,
+`col90` BLOB,
+`col91` LONGBLOB,
+`col92` YEAR,
+`col93` BLOB,
+`col94` INT,
+`col95` TINYTEXT,
+`col96` TINYINT,
+`col97` DECIMAL,
+`col98` ENUM ('test1','test2','test3'),
+`col99` MEDIUMINT,
+`col100` TINYINT,
+`col101` MEDIUMBLOB,
+`col102` TINYINT,
+`col103` SET ('test1','test2','test3'),
+`col104` TIMESTAMP,
+`col105` TEXT,
+`col106` DATETIME,
+`col107` MEDIUMTEXT,
+`col108` CHAR (220),
+`col109` TIME,
+`col110` VARCHAR (131),
+`col111` DECIMAL,
+`col112` FLOAT,
+`col113` SMALLINT,
+`col114` BIGINT,
+`col115` LONGBLOB,
+`col116` SET ('test1','test2','test3'),
+`col117` ENUM ('test1','test2','test3'),
+`col118` BLOB,
+`col119` MEDIUMTEXT,
+`col120` SET ('test1','test2','test3'),
+`col121` DATETIME,
+`col122` FLOAT,
+`col123` VARCHAR (242),
+`col124` YEAR,
+`col125` MEDIUMBLOB,
+`col126` TIME,
+`col127` BOOL,
+`col128` TINYBLOB,
+`col129` DOUBLE,
+`col130` TINYINT,
+`col131` BIGINT,
+`col132` SMALLINT,
+`col133` INT,
+`col134` DOUBLE PRECISION,
+`col135` MEDIUMBLOB,
+`col136` SET ('test1','test2','test3'),
+`col137` TINYTEXT,
+`col138` DOUBLE PRECISION,
+`col139` NUMERIC,
+`col140` BLOB,
+`col141` SET ('test1','test2','test3'),
+`col142` INT,
+`col143` VARCHAR (26),
+`col144` BLOB,
+`col145` REAL,
+`col146` SET ('test1','test2','test3'),
+`col147` LONGBLOB,
+`col148` TEXT,
+`col149` BLOB,
+`col150` CHAR (189),
+`col151` LONGTEXT,
+`col152` INT,
+`col153` FLOAT,
+`col154` LONGTEXT,
+`col155` DATE,
+`col156` LONGBLOB,
+`col157` TINYBLOB,
+`col158` REAL,
+`col159` DATE,
+`col160` TIME,
+`col161` YEAR,
+`col162` DOUBLE,
+`col163` VARCHAR (90),
+`col164` FLOAT,
+`col165` NUMERIC,
+`col166` ENUM ('test1','test2','test3'),
+`col167` DOUBLE PRECISION,
+`col168` DOUBLE PRECISION,
+`col169` TINYBLOB,
+`col170` TIME,
+`col171` SMALLINT,
+`col172` TINYTEXT,
+`col173` SMALLINT,
+`col174` DOUBLE,
+`col175` VARCHAR (14),
+`col176` VARCHAR (90),
+`col177` REAL,
+`col178` MEDIUMINT,
+`col179` TINYBLOB,
+`col180` FLOAT,
+`col181` TIMESTAMP,
+`col182` REAL,
+`col183` DOUBLE PRECISION,
+`col184` BIGINT,
+`col185` INT,
+`col186` MEDIUMTEXT,
+`col187` TIME,
+`col188` FLOAT,
+`col189` TIME,
+`col190` INT,
+`col191` FLOAT,
+`col192` MEDIUMINT,
+`col193` TINYINT,
+`col194` MEDIUMTEXT,
+`col195` DATE,
+`col196` TIME,
+`col197` YEAR,
+`col198` CHAR (206),
+KEY `idx0` (`col39`,`col23`)
+)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
+
+# Creating a table with 133 columns and 16 indexes
+DROP TABLE IF EXISTS `table4`;
+--error ER_TOO_BIG_ROWSIZE
+CREATE TABLE IF NOT EXISTS `table4`
+(`col0` VARCHAR (60),
+`col1` NUMERIC,
+`col2` LONGTEXT,
+`col3` MEDIUMTEXT,
+`col4` LONGTEXT,
+`col5` LONGBLOB,
+`col6` LONGBLOB,
+`col7` DATETIME,
+`col8` TINYTEXT,
+`col9` BLOB,
+`col10` BOOL,
+`col11` BIGINT,
+`col12` TEXT,
+`col13` VARCHAR (213),
+`col14` TINYBLOB,
+`col15` BOOL,
+`col16` MEDIUMTEXT,
+`col17` DOUBLE,
+`col18` TEXT,
+`col19` BLOB,
+`col20` SET ('test1','test2','test3'),
+`col21` TINYINT,
+`col22` DATETIME,
+`col23` TINYINT,
+`col24` ENUM ('test1','test2','test3'),
+`col25` REAL,
+`col26` BOOL,
+`col27` FLOAT,
+`col28` LONGBLOB,
+`col29` DATETIME,
+`col30` FLOAT,
+`col31` SET ('test1','test2','test3'),
+`col32` LONGBLOB,
+`col33` NUMERIC,
+`col34` YEAR,
+`col35` VARCHAR (146),
+`col36` BIGINT,
+`col37` DATETIME,
+`col38` DATE,
+`col39` SET ('test1','test2','test3'),
+`col40` CHAR (112),
+`col41` FLOAT,
+`col42` YEAR,
+`col43` TIME,
+`col44` DOUBLE,
+`col45` NUMERIC,
+`col46` FLOAT,
+`col47` DECIMAL,
+`col48` BIGINT,
+`col49` DECIMAL,
+`col50` YEAR,
+`col51` MEDIUMTEXT,
+`col52` LONGBLOB,
+`col53` SET ('test1','test2','test3'),
+`col54` BLOB,
+`col55` FLOAT,
+`col56` REAL,
+`col57` REAL,
+`col58` TEXT,
+`col59` MEDIUMBLOB,
+`col60` INT,
+`col61` INT,
+`col62` DATE,
+`col63` TEXT,
+`col64` DATE,
+`col65` ENUM ('test1','test2','test3'),
+`col66` DOUBLE PRECISION,
+`col67` TINYTEXT,
+`col68` TINYBLOB,
+`col69` FLOAT,
+`col70` BLOB,
+`col71` DATETIME,
+`col72` DOUBLE,
+`col73` LONGTEXT,
+`col74` TIME,
+`col75` DATETIME,
+`col76` VARCHAR (122),
+`col77` MEDIUMTEXT,
+`col78` MEDIUMTEXT,
+`col79` BOOL,
+`col80` LONGTEXT,
+`col81` TINYTEXT,
+`col82` NUMERIC,
+`col83` DOUBLE PRECISION,
+`col84` DATE,
+`col85` YEAR,
+`col86` BLOB,
+`col87` TINYTEXT,
+`col88` DOUBLE PRECISION,
+`col89` MEDIUMINT,
+`col90` MEDIUMTEXT,
+`col91` NUMERIC,
+`col92` DATETIME,
+`col93` NUMERIC,
+`col94` SET ('test1','test2','test3'),
+`col95` TINYTEXT,
+`col96` SET ('test1','test2','test3'),
+`col97` YEAR,
+`col98` MEDIUMINT,
+`col99` TEXT,
+`col100` TEXT,
+`col101` TIME,
+`col102` VARCHAR (225),
+`col103` TINYTEXT,
+`col104` TEXT,
+`col105` MEDIUMTEXT,
+`col106` TINYINT,
+`col107` TEXT,
+`col108` LONGBLOB,
+`col109` LONGTEXT,
+`col110` TINYTEXT,
+`col111` CHAR (56),
+`col112` YEAR,
+`col113` ENUM ('test1','test2','test3'),
+`col114` TINYBLOB,
+`col115` DATETIME,
+`col116` DATE,
+`col117` TIME,
+`col118` MEDIUMTEXT,
+`col119` DOUBLE PRECISION,
+`col120` FLOAT,
+`col121` TIMESTAMP,
+`col122` MEDIUMINT,
+`col123` YEAR,
+`col124` DATE,
+`col125` TEXT,
+`col126` FLOAT,
+`col127` TINYTEXT,
+`col128` BOOL,
+`col129` NUMERIC,
+`col130` TIMESTAMP,
+`col131` INT,
+`col132` MEDIUMBLOB,
+KEY `idx0` (`col130`),
+KEY `idx1` (`col30`,`col55`,`col19`(31)),
+KEY `idx2` (`col104`(186)),
+KEY `idx3` (`col131`),
+KEY `idx4` (`col64`,`col93`,`col2`(11)),
+KEY `idx5` (`col34`,`col121`,`col22`),
+KEY `idx6` (`col33`,`col55`,`col83`),
+KEY `idx7` (`col17`,`col87`(245),`col99`(17)),
+KEY `idx8` (`col65`,`col120`),
+KEY `idx9` (`col82`),
+KEY `idx10` (`col9`(72)),
+KEY `idx11` (`col88`),
+KEY `idx12` (`col128`,`col9`(200),`col71`,`col66`),
+KEY `idx13` (`col77`(126)),
+KEY `idx14` (`col105`(26),`col13`,`col117`),
+KEY `idx15` (`col4`(246),`col130`,`col115`,`col3`(141))
+)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
+
+# Creating a table with 176 columns and 13 indexes
+DROP TABLE IF EXISTS `table5`;
+--error ER_TOO_BIG_ROWSIZE
+CREATE TABLE IF NOT EXISTS `table5`
+(`col0` MEDIUMTEXT,
+`col1` VARCHAR (90),
+`col2` TINYTEXT,
+`col3` TIME,
+`col4` BOOL,
+`col5` TINYTEXT,
+`col6` BOOL,
+`col7` TIMESTAMP,
+`col8` TINYBLOB,
+`col9` TINYINT,
+`col10` YEAR,
+`col11` SET ('test1','test2','test3'),
+`col12` TEXT,
+`col13` CHAR (248),
+`col14` BIGINT,
+`col15` TEXT,
+`col16` TINYINT,
+`col17` NUMERIC,
+`col18` SET ('test1','test2','test3'),
+`col19` LONGBLOB,
+`col20` FLOAT,
+`col21` INT,
+`col22` TEXT,
+`col23` BOOL,
+`col24` DECIMAL,
+`col25` DOUBLE PRECISION,
+`col26` FLOAT,
+`col27` TINYBLOB,
+`col28` NUMERIC,
+`col29` MEDIUMBLOB,
+`col30` DATE,
+`col31` LONGTEXT,
+`col32` DATE,
+`col33` FLOAT,
+`col34` BIGINT,
+`col35` TINYTEXT,
+`col36` MEDIUMTEXT,
+`col37` TIME,
+`col38` INT,
+`col39` TINYINT,
+`col40` SET ('test1','test2','test3'),
+`col41` CHAR (130),
+`col42` SMALLINT,
+`col43` INT,
+`col44` MEDIUMTEXT,
+`col45` VARCHAR (126),
+`col46` INT,
+`col47` DOUBLE PRECISION,
+`col48` BIGINT,
+`col49` MEDIUMTEXT,
+`col50` TINYBLOB,
+`col51` MEDIUMINT,
+`col52` TEXT,
+`col53` VARCHAR (208),
+`col54` VARCHAR (207),
+`col55` NUMERIC,
+`col56` DATETIME,
+`col57` ENUM ('test1','test2','test3'),
+`col58` NUMERIC,
+`col59` TINYBLOB,
+`col60` VARCHAR (73),
+`col61` MEDIUMTEXT,
+`col62` TINYBLOB,
+`col63` DATETIME,
+`col64` NUMERIC,
+`col65` MEDIUMINT,
+`col66` DATETIME,
+`col67` NUMERIC,
+`col68` TINYINT,
+`col69` VARCHAR (58),
+`col70` DECIMAL,
+`col71` MEDIUMTEXT,
+`col72` DATE,
+`col73` TIME,
+`col74` DOUBLE PRECISION,
+`col75` DECIMAL,
+`col76` MEDIUMBLOB,
+`col77` REAL,
+`col78` YEAR,
+`col79` YEAR,
+`col80` LONGBLOB,
+`col81` BLOB,
+`col82` BIGINT,
+`col83` ENUM ('test1','test2','test3'),
+`col84` NUMERIC,
+`col85` SET ('test1','test2','test3'),
+`col86` MEDIUMTEXT,
+`col87` LONGBLOB,
+`col88` TIME,
+`col89` ENUM ('test1','test2','test3'),
+`col90` DECIMAL,
+`col91` FLOAT,
+`col92` DATETIME,
+`col93` TINYTEXT,
+`col94` TIMESTAMP,
+`col95` TIMESTAMP,
+`col96` TEXT,
+`col97` REAL,
+`col98` VARCHAR (198),
+`col99` TIME,
+`col100` TINYINT,
+`col101` BIGINT,
+`col102` LONGBLOB,
+`col103` LONGBLOB,
+`col104` MEDIUMINT,
+`col105` MEDIUMTEXT,
+`col106` TIMESTAMP,
+`col107` SMALLINT,
+`col108` NUMERIC,
+`col109` DECIMAL,
+`col110` FLOAT,
+`col111` DECIMAL,
+`col112` REAL,
+`col113` TINYTEXT,
+`col114` FLOAT,
+`col115` VARCHAR (7),
+`col116` LONGTEXT,
+`col117` DATE,
+`col118` BIGINT,
+`col119` TEXT,
+`col120` BIGINT,
+`col121` BLOB,
+`col122` CHAR (110),
+`col123` NUMERIC,
+`col124` MEDIUMBLOB,
+`col125` NUMERIC,
+`col126` NUMERIC,
+`col127` BOOL,
+`col128` TIME,
+`col129` TINYBLOB,
+`col130` TINYBLOB,
+`col131` DATE,
+`col132` INT,
+`col133` VARCHAR (123),
+`col134` CHAR (238),
+`col135` VARCHAR (225),
+`col136` LONGTEXT,
+`col137` LONGBLOB,
+`col138` REAL,
+`col139` TINYBLOB,
+`col140` DATETIME,
+`col141` TINYTEXT,
+`col142` LONGBLOB,
+`col143` BIGINT,
+`col144` VARCHAR (236),
+`col145` TEXT,
+`col146` YEAR,
+`col147` DECIMAL,
+`col148` TEXT,
+`col149` MEDIUMBLOB,
+`col150` TINYINT,
+`col151` BOOL,
+`col152` VARCHAR (72),
+`col153` INT,
+`col154` VARCHAR (165),
+`col155` TINYINT,
+`col156` MEDIUMTEXT,
+`col157` DOUBLE PRECISION,
+`col158` TIME,
+`col159` MEDIUMBLOB,
+`col160` LONGBLOB,
+`col161` DATETIME,
+`col162` DOUBLE PRECISION,
+`col163` BLOB,
+`col164` ENUM ('test1','test2','test3'),
+`col165` TIMESTAMP,
+`col166` DATE,
+`col167` TINYBLOB,
+`col168` TINYBLOB,
+`col169` LONGBLOB,
+`col170` DATETIME,
+`col171` BIGINT,
+`col172` VARCHAR (30),
+`col173` LONGTEXT,
+`col174` TIME,
+`col175` FLOAT,
+KEY `idx0` (`col16`,`col156`(139),`col97`,`col120`),
+KEY `idx1` (`col24`,`col0`(108)),
+KEY `idx2` (`col117`,`col173`(34),`col132`,`col82`),
+KEY `idx3` (`col2`(86)),
+KEY `idx4` (`col2`(43)),
+KEY `idx5` (`col83`,`col35`(87),`col111`),
+KEY `idx6` (`col6`,`col134`,`col92`),
+KEY `idx7` (`col56`),
+KEY `idx8` (`col30`,`col53`,`col129`(66)),
+KEY `idx9` (`col53`,`col113`(211),`col32`,`col15`(75)),
+KEY `idx10` (`col34`),
+KEY `idx11` (`col126`),
+KEY `idx12` (`col24`)
+)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
+
+# Creating a table with 179 columns and 46 indexes
+DROP TABLE IF EXISTS `table6`;
+-- error ER_TOO_BIG_ROWSIZE
+--error ER_TOO_BIG_ROWSIZE
+CREATE TABLE IF NOT EXISTS `table6`
+(`col0` ENUM ('test1','test2','test3'),
+`col1` MEDIUMBLOB,
+`col2` MEDIUMBLOB,
+`col3` DATETIME,
+`col4` DATE,
+`col5` YEAR,
+`col6` REAL,
+`col7` NUMERIC,
+`col8` MEDIUMBLOB,
+`col9` TEXT,
+`col10` TIMESTAMP,
+`col11` DOUBLE,
+`col12` DOUBLE,
+`col13` SMALLINT,
+`col14` TIMESTAMP,
+`col15` DECIMAL,
+`col16` DATE,
+`col17` TEXT,
+`col18` LONGBLOB,
+`col19` BIGINT,
+`col20` FLOAT,
+`col21` DATETIME,
+`col22` TINYINT,
+`col23` MEDIUMBLOB,
+`col24` SET ('test1','test2','test3'),
+`col25` TIME,
+`col26` TEXT,
+`col27` LONGTEXT,
+`col28` BIGINT,
+`col29` REAL,
+`col30` YEAR,
+`col31` MEDIUMBLOB,
+`col32` MEDIUMINT,
+`col33` FLOAT,
+`col34` TEXT,
+`col35` DATE,
+`col36` TIMESTAMP,
+`col37` REAL,
+`col38` BLOB,
+`col39` BLOB,
+`col40` BLOB,
+`col41` TINYBLOB,
+`col42` INT,
+`col43` TINYINT,
+`col44` REAL,
+`col45` BIGINT,
+`col46` TIMESTAMP,
+`col47` BLOB,
+`col48` ENUM ('test1','test2','test3'),
+`col49` BOOL,
+`col50` CHAR (109),
+`col51` DOUBLE,
+`col52` DOUBLE PRECISION,
+`col53` ENUM ('test1','test2','test3'),
+`col54` FLOAT,
+`col55` DOUBLE PRECISION,
+`col56` CHAR (166),
+`col57` TEXT,
+`col58` TIME,
+`col59` DECIMAL,
+`col60` TEXT,
+`col61` ENUM ('test1','test2','test3'),
+`col62` LONGTEXT,
+`col63` YEAR,
+`col64` DOUBLE,
+`col65` CHAR (87),
+`col66` DATE,
+`col67` BOOL,
+`col68` MEDIUMBLOB,
+`col69` DATETIME,
+`col70` DECIMAL,
+`col71` TIME,
+`col72` REAL,
+`col73` LONGTEXT,
+`col74` BLOB,
+`col75` REAL,
+`col76` INT,
+`col77` INT,
+`col78` FLOAT,
+`col79` DOUBLE,
+`col80` MEDIUMINT,
+`col81` ENUM ('test1','test2','test3'),
+`col82` VARCHAR (221),
+`col83` BIGINT,
+`col84` TINYINT,
+`col85` BIGINT,
+`col86` FLOAT,
+`col87` MEDIUMBLOB,
+`col88` CHAR (126),
+`col89` MEDIUMBLOB,
+`col90` DATETIME,
+`col91` TINYINT,
+`col92` DOUBLE,
+`col93` NUMERIC,
+`col94` DATE,
+`col95` BLOB,
+`col96` DATETIME,
+`col97` TIME,
+`col98` LONGBLOB,
+`col99` INT,
+`col100` SET ('test1','test2','test3'),
+`col101` TINYBLOB,
+`col102` INT,
+`col103` MEDIUMBLOB,
+`col104` MEDIUMTEXT,
+`col105` FLOAT,
+`col106` TINYBLOB,
+`col107` VARCHAR (26),
+`col108` TINYINT,
+`col109` TIME,
+`col110` TINYBLOB,
+`col111` LONGBLOB,
+`col112` TINYTEXT,
+`col113` FLOAT,
+`col114` TINYINT,
+`col115` NUMERIC,
+`col116` TIME,
+`col117` SET ('test1','test2','test3'),
+`col118` DATE,
+`col119` SMALLINT,
+`col120` BLOB,
+`col121` TINYTEXT,
+`col122` REAL,
+`col123` YEAR,
+`col124` REAL,
+`col125` BOOL,
+`col126` BLOB,
+`col127` REAL,
+`col128` MEDIUMBLOB,
+`col129` TIMESTAMP,
+`col130` LONGBLOB,
+`col131` MEDIUMBLOB,
+`col132` YEAR,
+`col133` YEAR,
+`col134` INT,
+`col135` MEDIUMINT,
+`col136` MEDIUMINT,
+`col137` TINYTEXT,
+`col138` TINYBLOB,
+`col139` BLOB,
+`col140` SET ('test1','test2','test3'),
+`col141` ENUM ('test1','test2','test3'),
+`col142` ENUM ('test1','test2','test3'),
+`col143` TINYTEXT,
+`col144` DATETIME,
+`col145` TEXT,
+`col146` DOUBLE PRECISION,
+`col147` DECIMAL,
+`col148` MEDIUMTEXT,
+`col149` TINYTEXT,
+`col150` SET ('test1','test2','test3'),
+`col151` MEDIUMTEXT,
+`col152` CHAR (126),
+`col153` DOUBLE,
+`col154` CHAR (243),
+`col155` SET ('test1','test2','test3'),
+`col156` SET ('test1','test2','test3'),
+`col157` DATETIME,
+`col158` DOUBLE,
+`col159` NUMERIC,
+`col160` DECIMAL,
+`col161` FLOAT,
+`col162` LONGBLOB,
+`col163` LONGTEXT,
+`col164` INT,
+`col165` TIME,
+`col166` CHAR (27),
+`col167` VARCHAR (63),
+`col168` TEXT,
+`col169` TINYBLOB,
+`col170` TINYBLOB,
+`col171` ENUM ('test1','test2','test3'),
+`col172` INT,
+`col173` TIME,
+`col174` DECIMAL,
+`col175` DOUBLE,
+`col176` MEDIUMBLOB,
+`col177` LONGBLOB,
+`col178` CHAR (43),
+KEY `idx0` (`col131`(219)),
+KEY `idx1` (`col67`,`col122`,`col59`,`col87`(33)),
+KEY `idx2` (`col83`,`col42`,`col57`(152)),
+KEY `idx3` (`col106`(124)),
+KEY `idx4` (`col173`,`col80`,`col165`,`col89`(78)),
+KEY `idx5` (`col174`,`col145`(108),`col23`(228),`col141`),
+KEY `idx6` (`col157`,`col140`),
+KEY `idx7` (`col130`(188),`col15`),
+KEY `idx8` (`col52`),
+KEY `idx9` (`col144`),
+KEY `idx10` (`col155`),
+KEY `idx11` (`col62`(230),`col1`(109)),
+KEY `idx12` (`col151`(24),`col95`(85)),
+KEY `idx13` (`col114`),
+KEY `idx14` (`col42`,`col98`(56),`col146`),
+KEY `idx15` (`col147`,`col39`(254),`col35`),
+KEY `idx16` (`col79`),
+KEY `idx17` (`col65`),
+KEY `idx18` (`col149`(165),`col168`(119),`col32`,`col117`),
+KEY `idx19` (`col64`),
+KEY `idx20` (`col93`),
+KEY `idx21` (`col64`,`col113`,`col104`(182)),
+KEY `idx22` (`col52`,`col111`(189)),
+KEY `idx23` (`col45`),
+KEY `idx24` (`col154`,`col107`,`col110`(159)),
+KEY `idx25` (`col149`(1),`col87`(131)),
+KEY `idx26` (`col58`,`col115`,`col63`),
+KEY `idx27` (`col95`(9),`col0`,`col87`(113)),
+KEY `idx28` (`col92`,`col130`(1)),
+KEY `idx29` (`col151`(129),`col137`(254),`col13`),
+KEY `idx30` (`col49`),
+KEY `idx31` (`col28`),
+KEY `idx32` (`col83`,`col146`),
+KEY `idx33` (`col155`,`col90`,`col17`(245)),
+KEY `idx34` (`col174`,`col169`(44),`col107`),
+KEY `idx35` (`col113`),
+KEY `idx36` (`col52`),
+KEY `idx37` (`col16`,`col120`(190)),
+KEY `idx38` (`col28`),
+KEY `idx39` (`col131`(165)),
+KEY `idx40` (`col135`,`col26`(86)),
+KEY `idx41` (`col69`,`col94`),
+KEY `idx42` (`col105`,`col151`(38),`col97`),
+KEY `idx43` (`col88`),
+KEY `idx44` (`col176`(100),`col42`,`col73`(189),`col94`),
+KEY `idx45` (`col2`(27),`col27`(116))
+)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
+
+SET GLOBAL innodb_file_format=@save_innodb_file_format;
+SET GLOBAL innodb_file_format_check=@save_innodb_file_format_check;
+SET GLOBAL innodb_file_per_table=@save_innodb_file_per_table;
+
+DROP TABLE IF EXISTS table0;
+DROP TABLE IF EXISTS table1;
+DROP TABLE IF EXISTS table2;
+DROP TABLE IF EXISTS table3;
+DROP TABLE IF EXISTS table4;
+DROP TABLE IF EXISTS table5;
+DROP TABLE IF EXISTS table6;
+
=== added file 'mysql-test/t/innodb_bug36172.test'
--- a/mysql-test/t/innodb_bug36172.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb_bug36172.test 2009-06-09 09:25:18 +0000
@@ -0,0 +1,33 @@
+#
+# Test case for bug 36172
+#
+
+-- source include/not_embedded.inc
+-- source include/have_innodb.inc
+
+SET storage_engine=InnoDB;
+
+# we do not really care about what gets printed, we are only
+# interested in getting success or failure according to our
+# expectations
+
+-- disable_query_log
+-- disable_result_log
+
+SET @save_innodb_file_format=@@global.innodb_file_format;
+SET @save_innodb_file_format_check=@@global.innodb_file_format_check;
+SET @save_innodb_file_per_table=@@global.innodb_file_per_table;
+SET GLOBAL innodb_file_format='Barracuda';
+SET GLOBAL innodb_file_per_table=on;
+
+DROP TABLE IF EXISTS `table0`;
+CREATE TABLE `table0` ( `col0` tinyint(1) DEFAULT NULL, `col1` tinyint(1) DEFAULT NULL, `col2` tinyint(4) DEFAULT NULL, `col3` date DEFAULT NULL, `col4` time DEFAULT NULL, `col5` set('test1','test2','test3') DEFAULT NULL, `col6` time DEFAULT NULL, `col7` text, `col8` decimal(10,0) DEFAULT NULL, `col9` set('test1','test2','test3') DEFAULT NULL, `col10` float DEFAULT NULL, `col11` double DEFAULT NULL, `col12` enum('test1','test2','test3') DEFAULT NULL, `col13` tinyblob, `col14` year(4) DEFAULT NULL, `col15` set('test1','test2','test3') DEFAULT NULL, `col16` decimal(10,0) DEFAULT NULL, `col17` decimal(10,0) DEFAULT NULL, `col18` blob, `col19` datetime DEFAULT NULL, `col20` double DEFAULT NULL, `col21` decimal(10,0) DEFAULT NULL, `col22` datetime DEFAULT NULL, `col23` decimal(10,0) DEFAULT NULL, `col24` decimal(10,0) DEFAULT NULL, `col25` longtext, `col26` tinyblob, `col27` time DEFAULT NULL, `col28` tinyblob, `col29` enum('test1','test2','test3') DEFAULT NULL, `col30` smallint(6) DEFAULT NULL, `col31` double DEFAULT NULL, `col32` float DEFAULT NULL, `col33` char(175) DEFAULT NULL, `col34` tinytext, `col35` tinytext, `col36` tinyblob, `col37` tinyblob, `col38` tinytext, `col39` mediumblob, `col40` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `col41` double DEFAULT NULL, `col42` smallint(6) DEFAULT NULL, `col43` longblob, `col44` varchar(80) DEFAULT NULL, `col45` mediumtext, `col46` decimal(10,0) DEFAULT NULL, `col47` bigint(20) DEFAULT NULL, `col48` date DEFAULT NULL, `col49` tinyblob, `col50` date DEFAULT NULL, `col51` tinyint(1) DEFAULT NULL, `col52` mediumint(9) DEFAULT NULL, `col53` float DEFAULT NULL, `col54` tinyblob, `col55` longtext, `col56` smallint(6) DEFAULT NULL, `col57` enum('test1','test2','test3') DEFAULT NULL, `col58` datetime DEFAULT NULL, `col59` mediumtext, `col60` varchar(232) DEFAULT NULL, `col61` decimal(10,0) DEFAULT NULL, `col62` year(4) DEFAULT NULL, `col63` smallint(6) DEFAULT NULL, `col64` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `col65` blob, `col66` longblob, `col67` int(11) DEFAULT NULL, `col68` longtext, `col69` enum('test1','test2','test3') DEFAULT NULL, `col70` int(11) DEFAULT NULL, `col71` time DEFAULT NULL, `col72` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `col73` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `col74` varchar(170) DEFAULT NULL, `col75` set('test1','test2','test3') DEFAULT NULL, `col76` tinyblob, `col77` bigint(20) DEFAULT NULL, `col78` decimal(10,0) DEFAULT NULL, `col79` datetime DEFAULT NULL, `col80` year(4) DEFAULT NULL, `col81` decimal(10,0) DEFAULT NULL, `col82` longblob, `col83` text, `col84` char(83) DEFAULT NULL, `col85` decimal(10,0) DEFAULT NULL, `col86` float DEFAULT NULL, `col87` int(11) DEFAULT NULL, `col88` varchar(145) DEFAULT NULL, `col89` date DEFAULT NULL, `col90` decimal(10,0) DEFAULT NULL, `col91` decimal(10,0) DEFAULT NULL, `col92` mediumblob, `col93` time DEFAULT NULL, KEY `idx0` (`col69`,`col90`,`col8`), KEY `idx1` (`col60`), KEY `idx2` (`col60`,`col70`,`col74`), KEY `idx3` (`col22`,`col32`,`col72`,`col30`), KEY `idx4` (`col29`), KEY `idx5` (`col19`,`col45`(143)), KEY `idx6` (`col46`,`col48`,`col5`,`col39`(118)), KEY `idx7` (`col48`,`col61`), KEY `idx8` (`col93`), KEY `idx9` (`col31`), KEY `idx10` (`col30`,`col21`), KEY `idx11` (`col67`), KEY `idx12` (`col44`,`col6`,`col8`,`col38`(226)), KEY `idx13` (`col71`,`col41`,`col15`,`col49`(88)), KEY `idx14` (`col78`), KEY `idx15` (`col63`,`col67`,`col64`), KEY `idx16` (`col17`,`col86`), KEY `idx17` (`col77`,`col56`,`col10`,`col55`(24)), KEY `idx18` (`col62`), KEY `idx19` (`col31`,`col57`,`col56`,`col53`), KEY `idx20` (`col46`), KEY `idx21` (`col83`(54)), KEY `idx22` (`col51`,`col7`(120)), KEY `idx23` (`col7`(163),`col31`,`col71`,`col14`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
+insert ignore into `table0` set `col23` = 7887371.5084383683, `col24` = 4293854615.6906948000, `col25` = 'vitalist', `col26` = 'widespread', `col27` = '3570490', `col28` = 'habitual', `col30` = -5471, `col31` = 4286985783.6771750000, `col32` = 6354540.9826654866, `col33` = 'defoliation', `col34` = 'logarithms', `col35` = 'tegument\'s', `col36` = 'scouting\'s', `col37` = 'intermittency', `col38` = 'elongates', `col39` = 'prophecies', `col40` = '20560103035939', `col41` = 4292809130.0544143000, `col42` = 22057, `col43` = 'Hess\'s', `col44` = 'bandstand', `col45` = 'phenylketonuria', `col46` = 6338767.4018677324, `col47` = 5310247, `col48` = '12592418', `col49` = 'churchman\'s', `col50` = '32226125', `col51` = -58, `col52` = -6207968, `col53` = 1244839.3255104220, `col54` = 'robotized', `col55` = 'monotonous', `col56` = -26909, `col58` = '20720107023550', `col59` = 'suggestiveness\'s', `col60` = 'gemology', `col61` = 4287800670.2229986000, `col62` = '1944', `col63` = -16827, `col64` = '20700107212324', `col65` = 'Nicolais', `col66` = 'apteryx', `col67` = 6935317, `col68` = 'stroganoff', `col70` = 3316430, `col71` = '3277608', `col72` = '19300511045918', `col73` = '20421201003327', `col74` = 'attenuant', `col75` = '15173', `col76` = 'upstroke\'s', `col77` = 8118987, `col78` = 6791516.2735374002, `col79` = '20780701144624', `col80` = '2134', `col81` = 4290682351.3127537000, `col82` = 'unexplainably', `col83` = 'Storm', `col84` = 'Greyso\'s', `col85` = 4289119212.4306774000, `col86` = 7617575.8796655172, `col87` = -6325335, `col88` = 'fondue\'s', `col89` = '40608940', `col90` = 1659421.8093508712, `col91` = 8346904.6584368423, `col92` = 'reloads', `col93` = '5188366';
+CHECK TABLE table0 EXTENDED;
+INSERT IGNORE INTO `table0` SET `col19` = '19940127002709', `col20` = 2383927.9055146948, `col21` = 4293243420.5621204000, `col22` = '20511211123705', `col23` = 4289899778.6573381000, `col24` = 4293449279.0540481000, `col25` = 'emphysemic', `col26` = 'dentally', `col27` = '2347406', `col28` = 'eruct', `col30` = 1222, `col31` = 4294372994.9941406000, `col32` = 4291385574.1173744000, `col33` = 'borrowing\'s', `col34` = 'septics', `col35` = 'ratter\'s', `col36` = 'Kaye', `col37` = 'Florentia', `col38` = 'allium', `col39` = 'barkeep', `col40` = '19510407003441', `col41` = 4293559200.4215522000, `col42` = 22482, `col43` = 'decussate', `col44` = 'Brom\'s', `col45` = 'violated', `col46` = 4925506.4635456400, `col47` = 930549, `col48` = '51296066', `col49` = 'voluminously', `col50` = '29306676', `col51` = -88, `col52` = -2153690, `col53` = 4290250202.1464887000, `col54` = 'expropriation', `col55` = 'Aberdeen\'s', `col56` = 20343, `col58` = '19640415171532', `col59` = 'extern', `col60` = 'Ubana', `col61` = 4290487961.8539081000, `col62` = '2147', `col63` = -24271, `col64` = '20750801194548', `col65` = 'Cunaxa\'s', `col66` = 'pasticcio', `col67` = 2795817, `col68` = 'Indore\'s', `col70` = 6864127, `col71` = '1817832', `col72` = '20540506114211', `col73` = '20040101012300', `col74` = 'rationalized', `col75` = '45522', `col76` = 'indene', `col77` = -6964559, `col78` = 4247535.5266884370, `col79` = '20720416124357', `col80` = '2143', `col81` = 4292060102.4466386000, `col82` = 'striving', `col83` = 'boneblack\'s', `col84` = 'redolent', `col85` = 6489697.9009369183, `col86` = 4287473465.9731131000, `col87` = 7726015, `col88` = 'perplexed', `col89` = '17153791', `col90` = 5478587.1108127078, `col91` = 4287091404.7004304000, `col92` = 'Boulez\'s', `col93` = '2931278';
+CHECK TABLE table0 EXTENDED;
+
+SET GLOBAL innodb_file_format=@save_innodb_file_format;
+SET GLOBAL innodb_file_format_check=@save_innodb_file_format_check;
+SET GLOBAL innodb_file_per_table=@save_innodb_file_per_table;
+DROP TABLE table0;
=== added file 'mysql-test/t/innodb_bug40360.test'
--- a/mysql-test/t/innodb_bug40360.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb_bug40360.test 2009-06-09 09:25:18 +0000
@@ -0,0 +1,16 @@
+#
+# Make sure http://bugs.mysql.com/40360 remains fixed.
+#
+
+-- source include/not_embedded.inc
+-- source include/have_innodb.inc
+
+SET TX_ISOLATION='READ-COMMITTED';
+
+# This is the default since MySQL 5.1.29 SET BINLOG_FORMAT='STATEMENT';
+
+CREATE TABLE bug40360 (a INT) engine=innodb;
+
+INSERT INTO bug40360 VALUES (1);
+
+DROP TABLE bug40360;
=== added file 'mysql-test/t/innodb_bug41904.test'
--- a/mysql-test/t/innodb_bug41904.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb_bug41904.test 2009-06-09 09:25:18 +0000
@@ -0,0 +1,14 @@
+#
+# Make sure http://bugs.mysql.com/41904 remains fixed.
+#
+
+-- source include/not_embedded.inc
+-- source include/have_innodb.inc
+
+CREATE TABLE bug41904 (id INT PRIMARY KEY, uniquecol CHAR(15)) ENGINE=InnoDB;
+
+INSERT INTO bug41904 VALUES (1,NULL), (2,NULL);
+
+CREATE UNIQUE INDEX ui ON bug41904 (uniquecol);
+
+DROP TABLE bug41904;
=== added file 'mysql-test/t/innodb_information_schema.test'
--- a/mysql-test/t/innodb_information_schema.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb_information_schema.test 2009-06-09 09:25:18 +0000
@@ -0,0 +1,145 @@
+#
+# Test that user data is correctly "visualized" in
+# INFORMATION_SCHEMA.innodb_locks.lock_data
+#
+
+-- source include/have_innodb.inc
+
+-- disable_query_log
+-- disable_result_log
+
+SET storage_engine=InnoDB;
+
+-- disable_warnings
+DROP TABLE IF EXISTS t_min, t_max;
+-- enable_warnings
+
+let $table_def =
+(
+ c01 TINYINT,
+ c02 TINYINT UNSIGNED,
+ c03 SMALLINT,
+ c04 SMALLINT UNSIGNED,
+ c05 MEDIUMINT,
+ c06 MEDIUMINT UNSIGNED,
+ c07 INT,
+ c08 INT UNSIGNED,
+ c09 BIGINT,
+ c10 BIGINT UNSIGNED,
+ PRIMARY KEY(c01, c02, c03, c04, c05, c06, c07, c08, c09, c10)
+);
+
+-- eval CREATE TABLE t_min $table_def;
+INSERT INTO t_min VALUES
+(-128, 0,
+ -32768, 0,
+ -8388608, 0,
+ -2147483648, 0,
+ -9223372036854775808, 0);
+
+-- eval CREATE TABLE t_max $table_def;
+INSERT INTO t_max VALUES
+(127, 255,
+ 32767, 65535,
+ 8388607, 16777215,
+ 2147483647, 4294967295,
+ 9223372036854775807, 18446744073709551615);
+
+CREATE TABLE ```t'\"_str` (
+ c1 VARCHAR(32),
+ c2 VARCHAR(32),
+ c3 VARCHAR(32),
+ c4 VARCHAR(32),
+ c5 VARCHAR(32),
+ c6 VARCHAR(32),
+ c7 VARCHAR(32),
+ PRIMARY KEY(c1, c2, c3, c4, c5, c6, c7)
+);
+INSERT INTO ```t'\"_str` VALUES
+('1', 'abc', '''abc', 'abc''', 'a''bc', 'a''bc''', '''abc''''');
+INSERT INTO ```t'\"_str` VALUES
+('2', 'abc', '"abc', 'abc"', 'a"bc', 'a"bc"', '"abc""');
+INSERT INTO ```t'\"_str` VALUES
+('3', 'abc', '\\abc', 'abc\\', 'a\\bc', 'a\\bc\\', '\\abc\\\\');
+INSERT INTO ```t'\"_str` VALUES
+('4', 'abc', 0x00616263, 0x61626300, 0x61006263, 0x6100626300, 0x610062630000);
+
+-- connect (con_lock,localhost,root,,)
+-- connect (con_min_trylock,localhost,root,,)
+-- connect (con_max_trylock,localhost,root,,)
+-- connect (con_str_insert_supremum,localhost,root,,)
+-- connect (con_str_lock_row1,localhost,root,,)
+-- connect (con_str_lock_row2,localhost,root,,)
+-- connect (con_str_lock_row3,localhost,root,,)
+-- connect (con_str_lock_row4,localhost,root,,)
+-- connect (con_verify_innodb_locks,localhost,root,,)
+
+-- connection con_lock
+SET autocommit=0;
+SELECT * FROM t_min FOR UPDATE;
+SELECT * FROM t_max FOR UPDATE;
+SELECT * FROM ```t'\"_str` FOR UPDATE;
+
+-- connection con_min_trylock
+-- send
+SELECT * FROM t_min FOR UPDATE;
+
+-- connection con_max_trylock
+-- send
+SELECT * FROM t_max FOR UPDATE;
+
+-- connection con_str_insert_supremum
+-- send
+INSERT INTO ```t'\"_str` VALUES
+('z', 'z', 'z', 'z', 'z', 'z', 'z');
+
+-- connection con_str_lock_row1
+-- send
+SELECT * FROM ```t'\"_str` WHERE c1 = '1' FOR UPDATE;
+
+-- connection con_str_lock_row2
+-- send
+SELECT * FROM ```t'\"_str` WHERE c1 = '2' FOR UPDATE;
+
+-- connection con_str_lock_row3
+-- send
+SELECT * FROM ```t'\"_str` WHERE c1 = '3' FOR UPDATE;
+
+-- connection con_str_lock_row4
+-- send
+SELECT * FROM ```t'\"_str` WHERE c1 = '4' FOR UPDATE;
+
+# Give time to the above 2 queries to execute before continuing.
+# Without this sleep it sometimes happens that the SELECT from innodb_locks
+# executes before some of them, resulting in less than expected number
+# of rows being selected from innodb_locks.
+-- sleep 0.1
+
+-- enable_result_log
+-- connection con_verify_innodb_locks
+SELECT lock_mode, lock_type, lock_table, lock_index, lock_rec, lock_data
+FROM INFORMATION_SCHEMA.INNODB_LOCKS ORDER BY lock_data;
+
+SELECT lock_table,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS
+GROUP BY lock_table;
+
+set @save_sql_mode = @@sql_mode;
+SET SQL_MODE='ANSI_QUOTES';
+SELECT lock_table,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS
+GROUP BY lock_table;
+SET @@sql_mode=@save_sql_mode;
+-- disable_result_log
+
+-- connection default
+
+-- disconnect con_lock
+-- disconnect con_min_trylock
+-- disconnect con_max_trylock
+-- disconnect con_str_insert_supremum
+-- disconnect con_str_lock_row1
+-- disconnect con_str_lock_row2
+-- disconnect con_str_lock_row3
+-- disconnect con_str_lock_row4
+-- disconnect con_verify_innodb_locks
+
+DROP TABLE t_min, t_max, ```t'\"_str`;
=== added file 'mysql-test/t/innodb_xtradb_bug317074.test'
--- a/mysql-test/t/innodb_xtradb_bug317074.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb_xtradb_bug317074.test 2009-06-09 09:25:18 +0000
@@ -0,0 +1,45 @@
+-- source include/have_innodb.inc
+
+SET @save_innodb_file_format=@@global.innodb_file_format;
+SET @save_innodb_file_format_check=@@global.innodb_file_format_check;
+SET @save_innodb_file_per_table=@@global.innodb_file_per_table;
+SET GLOBAL innodb_file_format='Barracuda';
+SET GLOBAL innodb_file_per_table=ON;
+
+-- disable_query_log
+-- disable_result_log
+
+DROP TABLE IF EXISTS `test1`;
+CREATE TABLE IF NOT EXISTS `test1` (
+ `a` int primary key auto_increment,
+ `b` int default 0,
+ `c` char(100) default 'testtest'
+) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
+
+delimiter |;
+CREATE PROCEDURE insert_many(p1 int)
+BEGIN
+SET @x = 0;
+SET @y = 0;
+REPEAT
+ insert into test1 set b=1;
+ SET @x = @x + 1;
+ SET @y = @y + 1;
+ IF @y >= 100 THEN
+ commit;
+ SET @y = 0;
+ END IF;
+UNTIL @x >= p1 END REPEAT;
+END|
+delimiter ;|
+call insert_many(100000);
+DROP PROCEDURE insert_many;
+
+# The bug is hangup at the following statement
+ALTER TABLE test1 ENGINE=MyISAM;
+
+SET GLOBAL innodb_file_format=@save_innodb_file_format;
+SET GLOBAL innodb_file_format_check=@save_innodb_file_format_check;
+SET GLOBAL innodb_file_per_table=@save_innodb_file_per_table;
+
+DROP TABLE test1;
=== modified file 'mysql-test/t/partition_innodb.test'
--- a/mysql-test/t/partition_innodb.test 2008-11-14 22:51:17 +0000
+++ b/mysql-test/t/partition_innodb.test 2009-06-09 09:25:18 +0000
@@ -27,14 +27,14 @@ UPDATE t1 SET DATA = data*2 WHERE id = 3
# grouping/referencing in replace_regex is very slow on long strings,
# removing all before/after the interesting row before grouping/referencing
---replace_regex /.*---TRANSACTION [0-9]+ [0-9]+, .*, OS thread id [0-9]+// /MySQL thread id [0-9]+, query id [0-9]+ .*// /.*([0-9]+ lock struct\(s\)), heap size [0-9]+, ([0-9]+ row lock\(s\)).*/\1 \2/
+--replace_regex /.*LIST OF TRANSACTIONS FOR EACH SESSION:// /MySQL thread id [0-9]+, query id [0-9]+ .*// /.*([0-9]+ lock struct\(s\)), heap size [0-9]+, ([0-9]+ row lock\(s\)).*/\1 \2/
SHOW ENGINE InnoDB STATUS;
UPDATE t1 SET data = data*2 WHERE data = 2;
# grouping/referencing in replace_regex is very slow on long strings,
# removing all before/after the interesting row before grouping/referencing
---replace_regex /.*---TRANSACTION [0-9]+ [0-9]+, .*, OS thread id [0-9]+// /MySQL thread id [0-9]+, query id [0-9]+ .*// /.*([0-9]+ lock struct\(s\)), heap size [0-9]+, ([0-9]+ row lock\(s\)).*/\1 \2/
+--replace_regex /.*LIST OF TRANSACTIONS FOR EACH SESSION:// /MySQL thread id [0-9]+, query id [0-9]+ .*// /.*([0-9]+ lock struct\(s\)), heap size [0-9]+, ([0-9]+ row lock\(s\)).*/\1 \2/
SHOW ENGINE InnoDB STATUS;
SET @@session.tx_isolation = @old_tx_isolation;
=== modified file 'mysql-test/t/type_bit_innodb.test'
--- a/mysql-test/t/type_bit_innodb.test 2007-06-06 17:57:07 +0000
+++ b/mysql-test/t/type_bit_innodb.test 2009-06-09 09:25:18 +0000
@@ -40,6 +40,7 @@ drop table t1;
create table t1 (a bit) engine=innodb;
insert into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001');
select hex(a) from t1;
+--replace_regex /entry '(.*)' for/entry '' for/
--error ER_DUP_ENTRY
alter table t1 add unique (a);
drop table t1;
=== modified file 'mysql-test/t/variables.test'
--- a/mysql-test/t/variables.test 2009-02-19 09:01:25 +0000
+++ b/mysql-test/t/variables.test 2009-06-09 09:25:18 +0000
@@ -174,21 +174,21 @@ select @@timestamp>0;
set @@rand_seed1=10000000,@@rand_seed2=1000000;
select ROUND(RAND(),5);
-show variables like '%alloc%';
-select * from information_schema.session_variables where variable_name like '%alloc%' order by 1;
+show variables where variable_name in ('query_alloc_block_size', 'query_prealloc_size', 'range_alloc_block_size', 'transaction_alloc_block_size', 'transaction_prealloc_size');
+select * from information_schema.session_variables where variable_name in ('query_alloc_block_size', 'query_prealloc_size', 'range_alloc_block_size', 'transaction_alloc_block_size', 'transaction_prealloc_size') order by 1;
set @@range_alloc_block_size=1024*16;
set @@query_alloc_block_size=1024*17+2;
set @@query_prealloc_size=1024*18;
set @@transaction_alloc_block_size=1024*20-1;
set @@transaction_prealloc_size=1024*21-1;
select @@query_alloc_block_size;
-show variables like '%alloc%';
-select * from information_schema.session_variables where variable_name like '%alloc%' order by 1;
+show variables where variable_name in ('query_alloc_block_size', 'query_prealloc_size', 'range_alloc_block_size', 'transaction_alloc_block_size', 'transaction_prealloc_size');
+select * from information_schema.session_variables where variable_name in ('query_alloc_block_size', 'query_prealloc_size', 'range_alloc_block_size', 'transaction_alloc_block_size', 'transaction_prealloc_size') order by 1;
set @@range_alloc_block_size=default;
set @@query_alloc_block_size=default, @@query_prealloc_size=default;
set transaction_alloc_block_size=default, @@transaction_prealloc_size=default;
-show variables like '%alloc%';
-select * from information_schema.session_variables where variable_name like '%alloc%' order by 1;
+show variables where variable_name in ('query_alloc_block_size', 'query_prealloc_size', 'range_alloc_block_size', 'transaction_alloc_block_size', 'transaction_prealloc_size');
+select * from information_schema.session_variables where variable_name in ('query_alloc_block_size', 'query_prealloc_size', 'range_alloc_block_size', 'transaction_alloc_block_size', 'transaction_prealloc_size') order by 1;
#
# Bug #10904 Illegal mix of collations between
=== modified file 'mysys/thr_mutex.c'
--- a/mysys/thr_mutex.c 2009-02-19 09:01:25 +0000
+++ b/mysys/thr_mutex.c 2009-06-09 09:25:18 +0000
@@ -149,6 +149,35 @@ static inline void remove_from_active_li
mp->prev= mp->next= 0;
}
+/*
+ We initialise the hashes for deadlock detection lazily.
+ This greatly helps with performance when lots of mutexes are initiased but
+ only a few of them are actually used (eg. XtraDB).
+*/
+static int safe_mutex_lazy_init_deadlock_detection(safe_mutex_t *mp)
+{
+ if (!my_multi_malloc(MY_FAE | MY_WME,
+ &mp->locked_mutex, sizeof(*mp->locked_mutex),
+ &mp->used_mutex, sizeof(*mp->used_mutex), NullS))
+ {
+ return 1; /* Error */
+ }
+
+ pthread_mutex_lock(&THR_LOCK_mutex);
+ mp->id= ++safe_mutex_id;
+ pthread_mutex_unlock(&THR_LOCK_mutex);
+ hash_init(mp->locked_mutex, &my_charset_bin,
+ 1000,
+ offsetof(safe_mutex_deadlock_t, id),
+ sizeof(mp->id),
+ 0, 0, HASH_UNIQUE);
+ hash_init(mp->used_mutex, &my_charset_bin,
+ 1000,
+ offsetof(safe_mutex_t, id),
+ sizeof(mp->id),
+ 0, 0, HASH_UNIQUE);
+ return 0;
+}
int safe_mutex_init(safe_mutex_t *mp,
const pthread_mutexattr_t *attr __attribute__((unused)),
@@ -167,35 +196,8 @@ int safe_mutex_init(safe_mutex_t *mp,
mp->line= line;
/* Skip the very common '&' prefix from the autogenerated name */
mp->name= name[0] == '&' ? name + 1 : name;
+ /* Deadlock detection is initialised only lazily, on first use. */
- if (safe_mutex_deadlock_detector && !( my_flags & MYF_NO_DEADLOCK_DETECTION))
- {
- if (!my_multi_malloc(MY_FAE | MY_WME,
- &mp->locked_mutex, sizeof(*mp->locked_mutex),
- &mp->used_mutex, sizeof(*mp->used_mutex), NullS))
- {
- /* Disable deadlock handling for this mutex */
- my_flags|= MYF_NO_DEADLOCK_DETECTION;
- }
- else
- {
- pthread_mutex_lock(&THR_LOCK_mutex);
- mp->id= ++safe_mutex_id;
- pthread_mutex_unlock(&THR_LOCK_mutex);
- hash_init(mp->locked_mutex, &my_charset_bin,
- 1000,
- offsetof(safe_mutex_deadlock_t, id),
- sizeof(mp->id),
- 0, 0, HASH_UNIQUE);
- hash_init(mp->used_mutex, &my_charset_bin,
- 1000,
- offsetof(safe_mutex_t, id),
- sizeof(mp->id),
- 0, 0, HASH_UNIQUE);
- }
- }
- else
- my_flags|= MYF_NO_DEADLOCK_DETECTION;
mp->create_flags= my_flags;
#ifdef SAFE_MUTEX_DETECT_DESTROY
@@ -310,7 +312,8 @@ int safe_mutex_lock(safe_mutex_t *mp, my
/* Deadlock detection */
mp->prev= mp->next= 0;
- if (!(mp->active_flags & (MYF_TRY_LOCK | MYF_NO_DEADLOCK_DETECTION)))
+ if (!(mp->active_flags & (MYF_TRY_LOCK | MYF_NO_DEADLOCK_DETECTION)) &&
+ (mp->used_mutex != NULL || !safe_mutex_lazy_init_deadlock_detection(mp)))
{
safe_mutex_t **mutex_in_use= my_thread_var_mutex_in_use();
@@ -643,7 +646,7 @@ int safe_mutex_destroy(safe_mutex_t *mp,
void safe_mutex_free_deadlock_data(safe_mutex_t *mp)
{
/* Free all entries that points to this one */
- if (!(mp->create_flags & MYF_NO_DEADLOCK_DETECTION))
+ if (!(mp->create_flags & MYF_NO_DEADLOCK_DETECTION) && mp->used_mutex != NULL)
{
pthread_mutex_lock(&THR_LOCK_mutex);
my_hash_iterate(mp->used_mutex,
=== modified file 'storage/innobase/ibuf/ibuf0ibuf.c'
--- a/storage/innobase/ibuf/ibuf0ibuf.c 2009-03-31 04:19:17 +0000
+++ b/storage/innobase/ibuf/ibuf0ibuf.c 2009-06-09 09:25:18 +0000
@@ -422,7 +422,12 @@ ibuf_init_at_db_start(void)
grow in size, as the references on the upper levels of the tree can
change */
- ibuf->max_size = ut_min( buf_pool_get_curr_size() / UNIV_PAGE_SIZE
+ /* The default for ibuf_max_size is calculated from the requested
+ buffer pool size srv_buf_pool_size, not the actual size as returned
+ by buf_pool_get_curr_size(). The latter can differ from the former
+ by one page due to alignment requirements, and we do not want a
+ user-visible variable like INNODB_IBUF_MAX_SIZE to vary at random. */
+ ibuf->max_size = ut_min( srv_buf_pool_size / UNIV_PAGE_SIZE
/ IBUF_POOL_SIZE_PER_MAX_SIZE, (ulint) srv_ibuf_max_size / UNIV_PAGE_SIZE);
srv_ibuf_max_size = (long long) ibuf->max_size * UNIV_PAGE_SIZE;
=== modified file 'storage/innobase/include/sync0rw.h'
--- a/storage/innobase/include/sync0rw.h 2009-03-31 04:19:17 +0000
+++ b/storage/innobase/include/sync0rw.h 2009-06-09 09:25:18 +0000
@@ -357,6 +357,8 @@ rw_lock_get_x_lock_count(
rw_lock_t* lock); /* in: rw-lock */
/************************************************************************
Accessor functions for rw lock. */
+
+#ifdef INNODB_RW_LOCKS_USE_ATOMICS
UNIV_INLINE
ulint
rw_lock_get_s_waiters(
@@ -372,6 +374,14 @@ ulint
rw_lock_get_wx_waiters(
/*================*/
rw_lock_t* lock);
+#else /* !INNODB_RW_LOCKS_USE_ATOMICS */
+UNIV_INLINE
+ulint
+rw_lock_get_waiters(
+/*==================*/
+ rw_lock_t* lock);
+#endif /* INNODB_RW_LOCKS_USE_ATOMICS */
+
UNIV_INLINE
ulint
rw_lock_get_writer(
@@ -488,6 +498,7 @@ rw_lock_debug_print(
rw_lock_debug_t* info); /* in: debug struct */
#endif /* UNIV_SYNC_DEBUG */
+/*
#ifndef INNODB_RW_LOCKS_USE_ATOMICS
#error INNODB_RW_LOCKS_USE_ATOMICS is not defined. Do you use enough new GCC or compatibles?
#error Or do you use exact options for CFLAGS?
@@ -495,6 +506,7 @@ rw_lock_debug_print(
#error e.g. (for Sparc_64): "-m64 -mcpu=v9"
#error Otherwise, this build may be slower than normal version.
#endif
+*/
/* NOTE! The structure appears here only for the compiler to know its size.
Do not use its fields directly! The structure used in the spin lock
=== modified file 'storage/innobase/include/sync0rw.ic'
--- a/storage/innobase/include/sync0rw.ic 2009-03-31 04:19:17 +0000
+++ b/storage/innobase/include/sync0rw.ic 2009-06-09 09:25:18 +0000
@@ -68,6 +68,8 @@ rw_lock_remove_debug_info(
/************************************************************************
Accessor functions for rw lock. */
+
+#ifdef INNODB_RW_LOCKS_USE_ATOMICS
UNIV_INLINE
ulint
rw_lock_get_s_waiters(
@@ -93,23 +95,32 @@ rw_lock_get_wx_waiters(
{
return(lock->wait_ex_waiters);
}
+#else /* !INNODB_RW_LOCKS_USE_ATOMICS */
+UNIV_INLINE
+ulint
+rw_lock_get_waiters(
+/*================*/
+ /* out: 1 if waiters, 0 otherwise */
+ rw_lock_t* lock) /* in: rw-lock */
+{
+ return(lock->waiters);
+}
+#endif /* INNODB_RW_LOCKS_USE_ATOMICS */
+
/************************************************************************
Sets lock->waiters to 1. It is not an error if lock->waiters is already
1. On platforms where ATOMIC builtins are used this function enforces a
memory barrier. */
+#ifdef INNODB_RW_LOCKS_USE_ATOMICS
UNIV_INLINE
void
rw_lock_set_s_waiter_flag(
/*====================*/
rw_lock_t* lock) /* in: rw-lock */
{
-#ifdef INNODB_RW_LOCKS_USE_ATOMICS
// os_compare_and_swap(&lock->s_waiters, 0, 1);
__sync_lock_test_and_set(&lock->s_waiters, 1);
-#else /* INNODB_RW_LOCKS_USE_ATOMICS */
- lock->s_waiters = 1;
-#endif /* INNODB_RW_LOCKS_USE_ATOMICS */
}
UNIV_INLINE
void
@@ -117,12 +128,8 @@ rw_lock_set_x_waiter_flag(
/*====================*/
rw_lock_t* lock) /* in: rw-lock */
{
-#ifdef INNODB_RW_LOCKS_USE_ATOMICS
// os_compare_and_swap(&lock->x_waiters, 0, 1);
__sync_lock_test_and_set(&lock->x_waiters, 1);
-#else /* INNODB_RW_LOCKS_USE_ATOMICS */
- lock->x_waiters = 1;
-#endif /* INNODB_RW_LOCKS_USE_ATOMICS */
}
UNIV_INLINE
void
@@ -130,30 +137,34 @@ rw_lock_set_wx_waiter_flag(
/*====================*/
rw_lock_t* lock) /* in: rw-lock */
{
-#ifdef INNODB_RW_LOCKS_USE_ATOMICS
// os_compare_and_swap(&lock->wait_ex_waiters, 0, 1);
__sync_lock_test_and_set(&lock->wait_ex_waiters, 1);
-#else /* INNODB_RW_LOCKS_USE_ATOMICS */
- lock->wait_ex_waiters = 1;
-#endif /* INNODB_RW_LOCKS_USE_ATOMICS */
}
+#else /* !INNODB_RW_LOCKS_USE_ATOMICS */
+UNIV_INLINE
+void
+rw_lock_set_waiter_flag(
+/*====================*/
+ rw_lock_t* lock) /* in: rw-lock */
+{
+ lock->waiters = 1;
+}
+#endif /* INNODB_RW_LOCKS_USE_ATOMICS */
/************************************************************************
Resets lock->waiters to 0. It is not an error if lock->waiters is already
0. On platforms where ATOMIC builtins are used this function enforces a
memory barrier. */
+#ifdef INNODB_RW_LOCKS_USE_ATOMICS
+
UNIV_INLINE
void
rw_lock_reset_s_waiter_flag(
/*======================*/
rw_lock_t* lock) /* in: rw-lock */
{
-#ifdef INNODB_RW_LOCKS_USE_ATOMICS
// os_compare_and_swap(&lock->s_waiters, 1, 0);
__sync_lock_test_and_set(&lock->s_waiters, 0);
-#else /* INNODB_RW_LOCKS_USE_ATOMICS */
- lock->s_waiters = 0;
-#endif /* INNODB_RW_LOCKS_USE_ATOMICS */
}
UNIV_INLINE
void
@@ -161,12 +172,8 @@ rw_lock_reset_x_waiter_flag(
/*======================*/
rw_lock_t* lock) /* in: rw-lock */
{
-#ifdef INNODB_RW_LOCKS_USE_ATOMICS
// os_compare_and_swap(&lock->x_waiters, 1, 0);
__sync_lock_test_and_set(&lock->x_waiters, 0);
-#else /* INNODB_RW_LOCKS_USE_ATOMICS */
- lock->x_waiters = 0;
-#endif /* INNODB_RW_LOCKS_USE_ATOMICS */
}
UNIV_INLINE
void
@@ -174,13 +181,19 @@ rw_lock_reset_wx_waiter_flag(
/*======================*/
rw_lock_t* lock) /* in: rw-lock */
{
-#ifdef INNODB_RW_LOCKS_USE_ATOMICS
// os_compare_and_swap(&lock->wait_ex_waiters, 1, 0);
__sync_lock_test_and_set(&lock->wait_ex_waiters, 0);
-#else /* INNODB_RW_LOCKS_USE_ATOMICS */
- lock->wait_ex_waiters = 0;
-#endif /* INNODB_RW_LOCKS_USE_ATOMICS */
}
+#else /* !INNODB_RW_LOCKS_USE_ATOMICS */
+UNIV_INLINE
+void
+rw_lock_reset_waiter_flag(
+/*======================*/
+ rw_lock_t* lock) /* in: rw-lock */
+{
+ lock->waiters = 0;
+}
+#endif /* INNODB_RW_LOCKS_USE_ATOMICS */
/**********************************************************************
Returns the write-status of the lock - this function made more sense
=== modified file 'storage/innobase/setup.sh'
--- a/storage/innobase/setup.sh 2009-03-31 04:19:17 +0000
+++ b/storage/innobase/setup.sh 2009-06-09 09:25:18 +0000
@@ -26,15 +26,15 @@ TARGETDIR=../storage/innobase
# link the build scripts
BUILDSCRIPTS="compile-innodb compile-innodb-debug"
for script in $BUILDSCRIPTS ; do
- ln -sf $TARGETDIR/$script ../../BUILD/
+ cp $script ../../BUILD/
done
cd ../../mysql-test/t
-ln -sf ../$TARGETDIR/mysql-test/*.test ../$TARGETDIR/mysql-test/*.opt .
+cp ../$TARGETDIR/mysql-test/*.test ../$TARGETDIR/mysql-test/*.opt .
cd ../r
-ln -sf ../$TARGETDIR/mysql-test/*.result .
+cp ../$TARGETDIR/mysql-test/*.result .
cd ../include
-ln -sf ../$TARGETDIR/mysql-test/*.inc .
+cp ../$TARGETDIR/mysql-test/*.inc .
# Apply any patches that are needed to make the mysql-test suite successful.
# These patches are usually needed because of deviations of behavior between
