diff options
| author | Pavan Naik <pavan.naik@oracle.com> | 2013-11-29 15:13:47 +0530 |
|---|---|---|
| committer | Pavan Naik <pavan.naik@oracle.com> | 2013-11-29 15:13:47 +0530 |
| commit | 4f5a31715353877ea059f9b406a96c0e9e031557 (patch) | |
| tree | 5d011a3ddb6d3772ee0230460efad32b81244c01 /mysql-test/suite/innodb/t | |
| parent | a33a1e55a3099659b66e2d041ce9836df51b3102 (diff) | |
| download | mariadb-git-4f5a31715353877ea059f9b406a96c0e9e031557.tar.gz | |
BUG#16321920 : CREATE A SEPARATE INNODB_ZIP TEST SUITE
Fix :
-------
Created separate suites called innodb_zip ans i_innodb_zip that contain all compression tests.
Running the new suites with following compression-related parameters :
* innodb_compression_level = {1/9}
* innodb_log_compressed_pages = {ON/OFF}
Diffstat (limited to 'mysql-test/suite/innodb/t')
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb-create-options.test | 573 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb-zip.test | 342 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb_bug36169.test | 1160 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb_bug36172.test | 30 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb_bug52745.test | 107 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb_bug53591.test | 20 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb_bug56680.test | 140 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb_cmp_drop_table-master.opt | 1 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb_cmp_drop_table.test | 59 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb_index_large_prefix.test | 269 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test | 1338 |
11 files changed, 0 insertions, 4039 deletions
diff --git a/mysql-test/suite/innodb/t/innodb-create-options.test b/mysql-test/suite/innodb/t/innodb-create-options.test deleted file mode 100644 index d6726683e7e..00000000000 --- a/mysql-test/suite/innodb/t/innodb-create-options.test +++ /dev/null @@ -1,573 +0,0 @@ -# Tests for various combinations of ROW_FORMAT and KEY_BLOCK_SIZE -# Related bugs; -# Bug#54679: ALTER TABLE causes compressed row_format to revert to compact -# Bug#56628: ALTER TABLE .. KEY_BLOCK_SIZE=0 produces untrue warning or unnecessary error -# Bug#56632: ALTER TABLE implicitly changes ROW_FORMAT to COMPRESSED -# Rules for interpreting CREATE_OPTIONS -# 1) Create options on an ALTER are added to the options on the -# previous CREATE or ALTER statements. -# 2) KEY_BLOCK_SIZE=0 is considered a unspecified value. -# If the current ROW_FORMAT has explicitly been set to COMPRESSED, -# InnoDB will use a default value of 8. Otherwise KEY_BLOCK_SIZE -# will not be used. -# 3) ROW_FORMAT=DEFAULT allows InnoDB to choose its own default, COMPACT. -# 4) ROW_FORMAT=DEFAULT and KEY_BLOCK_SIZE=0 can be used at any time to -# unset or erase the values persisted in the MySQL dictionary and -# by SHOW CTREATE TABLE. -# 5) When incompatible values for ROW_FORMAT and KEY_BLOCK_SIZE are -# both explicitly given, the ROW_FORMAT is always used in non-strict -# mode. -# 6) InnoDB will automatically convert a table to COMPRESSED only if a -# valid non-zero KEY_BLOCK_SIZE has been given and ROW_FORMAT=DEFAULT -# or has not been used on a previous CREATE TABLE or ALTER TABLE. -# 7) InnoDB strict mode is designed to prevent incompatible create -# options from being used together. -# 8) The non-strict behavior is intended to permit you to import a -# mysqldump file into a database that does not support compressed -# tables, even if the source database contained compressed tables. -# All invalid values and/or incompatible combinations of ROW_FORMAT -# and KEY_BLOCK_SIZE are automatically corrected -# -# *** innodb_strict_mode=ON *** -# 1) Valid ROW_FORMATs are COMPRESSED, COMPACT, DEFAULT, DYNAMIC -# & REDUNDANT. All others are rejected. -# 2) Valid KEY_BLOCK_SIZEs are 0,1,2,4,8,16. All others are rejected. -# 3) KEY_BLOCK_SIZE=0 can be used to set it to 'unspecified'. -# 4) KEY_BLOCK_SIZE=1,2,4,8 & 16 are incompatible with COMPACT, DYNAMIC & -# REDUNDANT. -# 5) KEY_BLOCK_SIZE=1,2,4,8 & 16 as well as ROW_FORMAT=COMPRESSED and -# ROW_FORMAT=DYNAMIC are incompatible with innodb_file_format=Antelope -# and innodb_file_per_table=OFF -# 6) KEY_BLOCK_SIZE on an ALTER must occur with ROW_FORMAT=COMPRESSED -# or ROW_FORMAT=DEFAULT if the ROW_FORMAT was previously specified -# as COMPACT, DYNAMIC or REDUNDANT. -# 7) KEY_BLOCK_SIZE on an ALTER can occur without a ROW_FORMAT if the -# previous ROW_FORMAT was DEFAULT, COMPRESSED, or unspecified. -# -# *** innodb_strict_mode=OFF *** -# 1. Ignore a bad KEY_BLOCK_SIZE, defaulting it to 8. -# 2. Ignore a bad ROW_FORMAT, defaulting to COMPACT. -# 3. Ignore a valid KEY_BLOCK_SIZE when an incompatible but valid -# ROW_FORMAT is specified. -# 4. If innodb_file_format=Antelope or innodb_file_per_table=OFF -# it will ignore ROW_FORMAT=COMPRESSED or DYNAMIC and it will -# ignore all non-zero KEY_BLOCK_SIZEs. -# -# See InnoDB documentation page "SQL Compression Syntax Warnings and Errors" - --- source include/have_innodb.inc -SET storage_engine=InnoDB; - ---disable_query_log -# These values can change during the test -LET $innodb_file_format_orig=`select @@innodb_file_format`; -LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`; -LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`; ---enable_query_log - -SET GLOBAL innodb_file_format=`Barracuda`; -SET GLOBAL innodb_file_per_table=ON; - -# The first half of these tests are with strict mode ON. -SET SESSION innodb_strict_mode = ON; - ---echo # Test 1) StrictMode=ON, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 ---echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' -DROP TABLE IF EXISTS t1; ---echo # 'FIXED' is sent to InnoDB since it is used by MyISAM. ---echo # But it is an invalid mode in InnoDB ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; -SHOW WARNINGS; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - - - ---echo # Test 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE ---echo # KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC -DROP TABLE IF EXISTS t1; ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; -SHOW WARNINGS; ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; -SHOW WARNINGS; ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; -SHOW WARNINGS; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - - ---echo # Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ); ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - - ---echo # Test 4) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid non-zero KEY_BLOCK_SIZE -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 KEY_BLOCK_SIZE=2; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -ALTER TABLE t1 ROW_FORMAT=REDUNDANT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 KEY_BLOCK_SIZE=4; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -ALTER TABLE t1 ROW_FORMAT=DYNAMIC; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 KEY_BLOCK_SIZE=8; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -ALTER TABLE t1 ROW_FORMAT=COMPRESSED; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 KEY_BLOCK_SIZE=16; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; -ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - ---echo # Test 5) StrictMode=ON, CREATE with a valid KEY_BLOCK_SIZE ---echo # ALTER with each ROW_FORMAT -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=2; -SHOW CREATE TABLE t1; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW CREATE TABLE t1; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=COMPACT; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=REDUNDANT; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -ALTER TABLE t1 ROW_FORMAT=COMPRESSED; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=COMPACT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - ---echo # Test 6) StrictMode=ON, CREATE with an invalid KEY_BLOCK_SIZE. -DROP TABLE IF EXISTS t1; ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9; -SHOW WARNINGS; - ---echo # Test 7) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and ---echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with Antelope ---echo # and that they can be set to default values during strict mode. -SET GLOBAL innodb_file_format=Antelope; -DROP TABLE IF EXISTS t1; ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4; -SHOW WARNINGS; ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; -SHOW WARNINGS; ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; -SHOW WARNINGS; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 KEY_BLOCK_SIZE=8; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=COMPRESSED; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -SET GLOBAL innodb_file_format=Barracuda; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; -SET GLOBAL innodb_file_format=Antelope; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ADD COLUMN f1 INT; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -ALTER TABLE t1 ADD COLUMN f2 INT; -SHOW WARNINGS; -SET GLOBAL innodb_file_format=Barracuda; - ---echo # Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and ---echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with ---echo # innodb_file_per_table=OFF and that they can be set to default ---echo # values during strict mode. -SET GLOBAL innodb_file_per_table=OFF; -DROP TABLE IF EXISTS t1; ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16; -SHOW WARNINGS; ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; -SHOW WARNINGS; ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; -SHOW WARNINGS; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 KEY_BLOCK_SIZE=1; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=COMPRESSED; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -ALTER TABLE t1 ROW_FORMAT=COMPACT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=REDUNDANT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=DEFAULT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_per_table=ON; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; -SET GLOBAL innodb_file_per_table=OFF; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ADD COLUMN f1 INT; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -ALTER TABLE t1 ADD COLUMN f2 INT; -SHOW WARNINGS; -SET GLOBAL innodb_file_per_table=ON; - ---echo ################################################## -SET SESSION innodb_strict_mode = OFF; - ---echo # Test 9) StrictMode=OFF, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 ---echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' ---echo # 'FIXED' is sent to InnoDB since it is used by MyISAM. ---echo # It is an invalid mode in InnoDB, use COMPACT -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - ---echo # Test 10) StrictMode=OFF, CREATE with each ROW_FORMAT & a valid KEY_BLOCK_SIZE ---echo # KEY_BLOCK_SIZE is ignored with COMPACT, REDUNDANT, & DYNAMIC -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - - ---echo # Test 11) StrictMode=OFF, ALTER with each ROW_FORMAT & a valid KEY_BLOCK_SIZE -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ); -ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ); -ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ); -ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ); -ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ); -ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - - ---echo # Test 12) StrictMode=OFF, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid KEY_BLOCK_SIZE -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 KEY_BLOCK_SIZE=2; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=REDUNDANT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=DYNAMIC; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=COMPRESSED; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 KEY_BLOCK_SIZE=4; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; -ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=8; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - ---echo # Test 13) StrictMode=OFF, CREATE with a valid KEY_BLOCK_SIZE ---echo # ALTER with each ROW_FORMAT -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16; -SHOW WARNINGS; -SHOW CREATE TABLE t1; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SHOW CREATE TABLE t1; -ALTER TABLE t1 ROW_FORMAT=COMPACT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=REDUNDANT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=DYNAMIC; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=COMPRESSED; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ROW_FORMAT=COMPACT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - ---echo # Test 14) StrictMode=OFF, CREATE with an invalid KEY_BLOCK_SIZE, it defaults to 8 -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=15; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - ---echo # Test 15) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a ---echo valid KEY_BLOCK_SIZE are remembered but not used when ROW_FORMAT ---echo is reverted to Antelope and then used again when ROW_FORMAT=Barracuda. -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_format=Antelope; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_format=Barracuda; -ALTER TABLE t1 ADD COLUMN f2 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_format=Antelope; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_format=Barracuda; -ALTER TABLE t1 ADD COLUMN f2 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - ---echo # Test 16) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a ---echo valid KEY_BLOCK_SIZE are remembered but not used when innodb_file_per_table=OFF ---echo and then used again when innodb_file_per_table=ON. -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_per_table=OFF; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_per_table=ON; -ALTER TABLE t1 ADD COLUMN f2 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_per_table=OFF; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_per_table=ON; -ALTER TABLE t1 ADD COLUMN f2 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - - ---echo # Cleanup -DROP TABLE IF EXISTS t1; - ---disable_query_log -EVAL SET GLOBAL innodb_file_format=$innodb_file_format_orig; -EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; -EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig; ---enable_query_log - diff --git a/mysql-test/suite/innodb/t/innodb-zip.test b/mysql-test/suite/innodb/t/innodb-zip.test deleted file mode 100644 index 39a094d0359..00000000000 --- a/mysql-test/suite/innodb/t/innodb-zip.test +++ /dev/null @@ -1,342 +0,0 @@ --- source include/have_innodb.inc - -let $per_table=`select @@innodb_file_per_table`; -let $format=`select @@innodb_file_format`; -let $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`; - -set session innodb_strict_mode=0; -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, data_length, index_length -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, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; -alter table t1 row_format=compact; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; -alter table t1 row_format=redundant; -SELECT table_schema, table_name, row_format, data_length, index_length -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, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; -drop table t1,t2; - -# The following should fail in non-strict mode too. -# (The fix of Bug #50945 only affects REDUNDANT and COMPACT tables.) -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; -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_VALUE_FOR_VAR -set global innodb_file_format=`2`; --- error ER_WRONG_VALUE_FOR_VAR -set global innodb_file_format=`-1`; -set global innodb_file_format=`Antelope`; -set global innodb_file_format=`Barracuda`; --- error ER_WRONG_VALUE_FOR_VAR -set global innodb_file_format=`Cheetah`; --- error ER_WRONG_VALUE_FOR_VAR -set global innodb_file_format=`abc`; --- error ER_WRONG_VALUE_FOR_VAR -set global innodb_file_format=`1a`; --- error ER_WRONG_VALUE_FOR_VAR -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 - -create table t1 (id int primary key) engine = innodb key_block_size = 0; - ---error ER_CANT_CREATE_TABLE -create table t2 (id int primary key) engine = innodb key_block_size = 9; -show warnings; - - -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, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; -drop table t1, t3, t4, t5, t6, t7, t8, t9, t10, t11; - -#test different values of ROW_FORMAT with KEY_BLOCK_SIZE -create table t1 (id int primary key) engine = innodb -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 warnings; - ---error ER_CANT_CREATE_TABLE -create table t3 (id int primary key) engine = innodb -key_block_size = 8 row_format = compact; -show warnings; - ---error ER_CANT_CREATE_TABLE -create table t4 (id int primary key) engine = innodb -key_block_size = 8 row_format = dynamic; -show warnings; - -create table t5 (id int primary key) engine = innodb -key_block_size = 8 row_format = default; - -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; -drop table t1, t5; - -#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 warnings; - ---error ER_CANT_CREATE_TABLE -create table t2 (id int primary key) engine = innodb -key_block_size = 9 row_format = compact; -show warnings; - ---error ER_CANT_CREATE_TABLE -create table t2 (id int primary key) engine = innodb -key_block_size = 9 row_format = dynamic; -show warnings; - -SELECT table_schema, table_name, row_format, data_length, index_length -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 warnings; ---error ER_CANT_CREATE_TABLE -create table t2 (id int primary key) engine = innodb key_block_size = 2; -show warnings; ---error ER_CANT_CREATE_TABLE -create table t3 (id int primary key) engine = innodb key_block_size = 4; -show warnings; ---error ER_CANT_CREATE_TABLE -create table t4 (id int primary key) engine = innodb key_block_size = 8; -show warnings; ---error ER_CANT_CREATE_TABLE -create table t5 (id int primary key) engine = innodb key_block_size = 16; -show warnings; ---error ER_CANT_CREATE_TABLE -create table t6 (id int primary key) engine = innodb row_format = compressed; -show warnings; ---error ER_CANT_CREATE_TABLE -create table t7 (id int primary key) engine = innodb row_format = dynamic; -show warnings; -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, data_length, index_length -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 warnings; ---error ER_CANT_CREATE_TABLE -create table t2 (id int primary key) engine = innodb key_block_size = 2; -show warnings; ---error ER_CANT_CREATE_TABLE -create table t3 (id int primary key) engine = innodb key_block_size = 4; -show warnings; ---error ER_CANT_CREATE_TABLE -create table t4 (id int primary key) engine = innodb key_block_size = 8; -show warnings; ---error ER_CANT_CREATE_TABLE -create table t5 (id int primary key) engine = innodb key_block_size = 16; -show warnings; ---error ER_CANT_CREATE_TABLE -create table t6 (id int primary key) engine = innodb row_format = compressed; -show warnings; ---error ER_CANT_CREATE_TABLE -create table t7 (id int primary key) engine = innodb row_format = dynamic; -show warnings; -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, data_length, index_length -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_max=`Antelope`; -create table normal_table ( - c1 int -) engine = innodb; -select @@innodb_file_format_max; -create table zip_table ( - c1 int -) engine = innodb key_block_size = 8; -select @@innodb_file_format_max; -set global innodb_file_format_max=`Antelope`; -select @@innodb_file_format_max; --- disable_result_log -show table status; --- enable_result_log -select @@innodb_file_format_max; -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 session innodb_strict_mode=$innodb_strict_mode_orig; diff --git a/mysql-test/suite/innodb/t/innodb_bug36169.test b/mysql-test/suite/innodb/t/innodb_bug36169.test deleted file mode 100644 index 5bbbf45d484..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug36169.test +++ /dev/null @@ -1,1160 +0,0 @@ -# -# Bug#36169 create innodb compressed table with too large row size crashed -# http://bugs.mysql.com/36169 -# - --- source include/have_innodb.inc - -let $file_format=`select @@innodb_file_format`; -let $file_per_table=`select @@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`; -set innodb_strict_mode=on; ---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; - -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; - -EVAL SET GLOBAL innodb_file_format=$file_format; -EVAL SET GLOBAL innodb_file_per_table=$file_per_table; diff --git a/mysql-test/suite/innodb/t/innodb_bug36172.test b/mysql-test/suite/innodb/t/innodb_bug36172.test deleted file mode 100644 index 8a143da752d..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug36172.test +++ /dev/null @@ -1,30 +0,0 @@ -# -# 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 - -let $file_format=`select @@innodb_file_format`; -let $file_per_table=`select @@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; -DROP TABLE table0; -EVAL SET GLOBAL innodb_file_format=$file_format; -EVAL SET GLOBAL innodb_file_per_table=$file_per_table; diff --git a/mysql-test/suite/innodb/t/innodb_bug52745.test b/mysql-test/suite/innodb/t/innodb_bug52745.test deleted file mode 100644 index 3c5d79826f0..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug52745.test +++ /dev/null @@ -1,107 +0,0 @@ --- source include/have_innodb.inc - -let $file_format=`select @@innodb_file_format`; -let $file_per_table=`select @@innodb_file_per_table`; -SET GLOBAL innodb_file_format='Barracuda'; -SET GLOBAL innodb_file_per_table=on; - -CREATE TABLE bug52745 ( - a2 int(10) unsigned DEFAULT NULL, - col37 time DEFAULT NULL, - col38 char(229) CHARACTER SET utf8 DEFAULT NULL, - col39 text, - col40 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - col41 int(10) unsigned DEFAULT NULL, - col42 varchar(248) CHARACTER SET utf8 DEFAULT NULL, - col43 smallint(5) unsigned zerofill DEFAULT NULL, - col44 varchar(150) CHARACTER SET utf8 DEFAULT NULL, - col45 float unsigned zerofill DEFAULT NULL, - col46 binary(1) DEFAULT NULL, - col47 tinyint(4) DEFAULT NULL, - col48 tinyint(1) DEFAULT NULL, - col49 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - col50 binary(1) DEFAULT NULL, - col51 double unsigned zerofill DEFAULT NULL, - col52 int(10) unsigned DEFAULT NULL, - col53 time DEFAULT NULL, - col54 double unsigned DEFAULT NULL, - col55 time DEFAULT NULL, - col56 mediumtext CHARACTER SET latin2, - col57 blob, - col58 decimal(52,16) unsigned zerofill NOT NULL DEFAULT '000000000000000000000000000000000000.0000000000000000', - col59 binary(1) DEFAULT NULL, - col60 longblob, - col61 time DEFAULT NULL, - col62 longtext CHARACTER SET utf8 COLLATE utf8_persian_ci, - col63 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - col64 int(10) unsigned DEFAULT NULL, - col65 date DEFAULT NULL, - col66 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - col67 binary(1) DEFAULT NULL, - col68 tinyblob, - col69 date DEFAULT NULL, - col70 tinyint(3) unsigned zerofill DEFAULT NULL, - col71 varchar(44) CHARACTER SET utf8 DEFAULT NULL, - col72 datetime DEFAULT NULL, - col73 smallint(5) unsigned zerofill DEFAULT NULL, - col74 longblob, - col75 bit(34) DEFAULT NULL, - col76 float unsigned zerofill DEFAULT NULL, - col77 year(2) DEFAULT NULL, - col78 tinyint(3) unsigned DEFAULT NULL, - col79 set('msfheowh','tbpxbgf','by','wahnrjw','myqfasxz','rsokyumrt') CHARACTER SET latin2 DEFAULT NULL, - col80 datetime DEFAULT NULL, - col81 smallint(6) DEFAULT NULL, - col82 enum('xtaurnqfqz','rifrse','kuzwpbvb','niisabk','zxavro','rbvasv','','uulrfaove','','') DEFAULT NULL, - col83 bigint(20) unsigned zerofill DEFAULT NULL, - col84 float unsigned zerofill DEFAULT NULL, - col85 double DEFAULT NULL, - col86 enum('ylannv','','vlkhycqc','snke','cxifustp','xiaxaswzp','oxl') CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL, - col87 varbinary(221) DEFAULT NULL, - col88 double unsigned DEFAULT NULL, - col89 float unsigned zerofill DEFAULT NULL, - col90 tinyblob -) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; - -INSERT INTO bug52745 SET -col40='0000-00-00 00:00:00', -col51=16547, -col53='7711484', -col54=-28604, -col55='7112612', -col56='wakefulness\'', -col57=repeat('absorbefacient\'',106), -col58=11027, -col59='AM09gW7', -col60=repeat('Noelani\'',16), -col61='2520576', -col62='substitutiv', -col63='19950106155112', -col64=-12038, -col65='86238806', -col66='19600719080256', -col68=repeat('Sagittarius\'',54), -col69='38943902', -col70=1232, -col71='Elora\'', -col74=repeat('zipp',11), -col75='0', -col76=23254, -col78=13247, -col79='56219', -col80='20500609035724', -col81=11632, -col82=7, -col84=-23863, -col85=6341, -col87='HZdkf.4 s7t,5Rmq 8so fmr,ruGLUG25TrtI.yQ 2SuHq0ML7rw7.4 b2yf2E5TJxOtBBZImezDnzpj,uPYfznnEUDN1e9aQoO 2DsplB7TFWy oQJ br HLF :F,eQ p4i1oWsr lL3PG,hjCz6hYqN h1QTjLCjrv:QCdSzpYBibJAtZCxLOk3l6Blsh.W', -col88=16894, -col89=6161, -col90=repeat('gale',48); - -SHOW WARNINGS; - -DROP TABLE bug52745; - -EVAL SET GLOBAL innodb_file_format=$file_format; -EVAL SET GLOBAL innodb_file_per_table=$file_per_table; diff --git a/mysql-test/suite/innodb/t/innodb_bug53591.test b/mysql-test/suite/innodb/t/innodb_bug53591.test deleted file mode 100644 index 5c3f2b2bc83..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug53591.test +++ /dev/null @@ -1,20 +0,0 @@ --- source include/have_innodb.inc - -let $file_format=`select @@innodb_file_format`; -let $file_per_table=`select @@innodb_file_per_table`; - -SET GLOBAL innodb_file_format='Barracuda'; -SET GLOBAL innodb_file_per_table=on; - -set old_alter_table=0; - -CREATE TABLE bug53591(a text charset utf8 not null) -ENGINE=InnoDB KEY_BLOCK_SIZE=1; --- error 139 -ALTER TABLE bug53591 ADD PRIMARY KEY(a(220)); -SHOW WARNINGS; - -DROP TABLE bug53591; - -EVAL SET GLOBAL innodb_file_format=$file_format; -EVAL SET GLOBAL innodb_file_per_table=$file_per_table; diff --git a/mysql-test/suite/innodb/t/innodb_bug56680.test b/mysql-test/suite/innodb/t/innodb_bug56680.test deleted file mode 100644 index 936191a9b9f..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug56680.test +++ /dev/null @@ -1,140 +0,0 @@ -# -# Bug #56680 InnoDB may return wrong results from a case-insensitive index -# --- source include/have_innodb.inc - --- disable_query_log -SET @tx_isolation_orig = @@tx_isolation; -SET @innodb_file_per_table_orig = @@innodb_file_per_table; -SET @innodb_file_format_orig = @@innodb_file_format; -# The flag innodb_change_buffering_debug is only available in debug builds. -# It instructs InnoDB to try to evict pages from the buffer pool when -# change buffering is possible, so that the change buffer will be used -# whenever possible. --- error 0,ER_UNKNOWN_SYSTEM_VARIABLE -SET @innodb_change_buffering_debug_orig = @@innodb_change_buffering_debug; --- error 0,ER_UNKNOWN_SYSTEM_VARIABLE -SET GLOBAL innodb_change_buffering_debug = 1; --- enable_query_log -SET GLOBAL tx_isolation='REPEATABLE-READ'; -SET GLOBAL innodb_file_format=Barracuda; -SET GLOBAL innodb_file_per_table=on; - -CREATE TABLE bug56680( - a INT AUTO_INCREMENT PRIMARY KEY, - b CHAR(1), - c INT, - INDEX(b)) -ENGINE=InnoDB; - -INSERT INTO bug56680 VALUES(0,'x',1); -BEGIN; -SELECT b FROM bug56680; - -connect (con1,localhost,root,,); -connection con1; -BEGIN; -UPDATE bug56680 SET b='X'; - -connection default; -# This should return the last committed value 'x', but would return 'X' -# due to a bug in row_search_for_mysql(). -SELECT b FROM bug56680; -# This would always return the last committed value 'x'. -SELECT * FROM bug56680; - -connection con1; -ROLLBACK; -disconnect con1; - -connection default; - -SELECT b FROM bug56680; - -# For the rest of this test, use the READ UNCOMMITTED isolation level -# to see what exists in the secondary index. -SET GLOBAL tx_isolation='READ-UNCOMMITTED'; - -# Create enough rows for the table, so that the insert buffer will be -# used for modifying the secondary index page. There must be multiple -# index pages, because changes to the root page are never buffered. - -INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; -INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; -INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; -INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; -INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; -INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; -INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; -INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; -INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; -INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; -INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; - -BEGIN; -SELECT b FROM bug56680 LIMIT 2; - -connect (con1,localhost,root,,); -connection con1; -BEGIN; -DELETE FROM bug56680 WHERE a=1; -# This should be buffered, if innodb_change_buffering_debug = 1 is in effect. -INSERT INTO bug56680 VALUES(1,'X',1); - -# This should force an insert buffer merge, and return 'X' in the first row. -SELECT b FROM bug56680 LIMIT 3; - -connection default; -SELECT b FROM bug56680 LIMIT 2; -CHECK TABLE bug56680; - -connection con1; -ROLLBACK; -SELECT b FROM bug56680 LIMIT 2; -CHECK TABLE bug56680; - -connection default; -disconnect con1; - -SELECT b FROM bug56680 LIMIT 2; - -CREATE TABLE bug56680_2( - a INT AUTO_INCREMENT PRIMARY KEY, - b VARCHAR(2) CHARSET latin1 COLLATE latin1_german2_ci, - c INT, - INDEX(b)) -ENGINE=InnoDB; - -INSERT INTO bug56680_2 SELECT 0,_latin1 0xdf,c FROM bug56680; - -BEGIN; -SELECT HEX(b) FROM bug56680_2 LIMIT 2; -DELETE FROM bug56680_2 WHERE a=1; -# This should be buffered, if innodb_change_buffering_debug = 1 is in effect. -INSERT INTO bug56680_2 VALUES(1,'SS',1); - -# This should force an insert buffer merge, and return 'SS' in the first row. -SELECT HEX(b) FROM bug56680_2 LIMIT 3; -CHECK TABLE bug56680_2; - -# Test this with compressed tables. -ALTER TABLE bug56680_2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; - -SELECT HEX(b) FROM bug56680_2 LIMIT 2; -DELETE FROM bug56680_2 WHERE a=1; -# This should be buffered, if innodb_change_buffering_debug = 1 is in effect. -INSERT INTO bug56680_2 VALUES(1,_latin1 0xdf,1); - -# This should force an insert buffer merge, and return 0xdf in the first row. -SELECT HEX(b) FROM bug56680_2 LIMIT 3; -CHECK TABLE bug56680_2; - -DROP TABLE bug56680_2; -DROP TABLE bug56680; - --- disable_query_log -SET GLOBAL tx_isolation = @tx_isolation_orig; -SET GLOBAL innodb_file_per_table = @innodb_file_per_table_orig; -SET GLOBAL innodb_file_format = @innodb_file_format_orig; --- error 0, ER_UNKNOWN_SYSTEM_VARIABLE -SET GLOBAL innodb_change_buffering_debug = @innodb_change_buffering_debug_orig; diff --git a/mysql-test/suite/innodb/t/innodb_cmp_drop_table-master.opt b/mysql-test/suite/innodb/t/innodb_cmp_drop_table-master.opt deleted file mode 100644 index a9a3d8c3db8..00000000000 --- a/mysql-test/suite/innodb/t/innodb_cmp_drop_table-master.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb-buffer-pool-size=8M diff --git a/mysql-test/suite/innodb/t/innodb_cmp_drop_table.test b/mysql-test/suite/innodb/t/innodb_cmp_drop_table.test deleted file mode 100644 index 92f4f715241..00000000000 --- a/mysql-test/suite/innodb/t/innodb_cmp_drop_table.test +++ /dev/null @@ -1,59 +0,0 @@ --- source include/have_innodb.inc - -let $per_table=`select @@innodb_file_per_table`; -let $format=`select @@innodb_file_format`; - --- let $query_i_s = SELECT page_size FROM information_schema.innodb_cmpmem WHERE pages_used > 0 - -set global innodb_file_per_table=on; -set global innodb_file_format=`1`; - -create table t1(a text) engine=innodb key_block_size=8; - --- disable_query_log - -# insert some rows so we are using compressed pages --- let $i = 10 -while ($i) -{ - insert into t1 values(repeat('abcdefghijklmnopqrstuvwxyz',100)); - dec $i; -} --- enable_query_log - -# we should be using some 8K pages --- eval $query_i_s - -drop table t1; - -# because of lazy eviction at drop table in 5.5 there should be some -# used 8K pages --- eval $query_i_s - -# create a non-compressed table and insert enough into it to evict -# compressed pages -create table t2(a text) engine=innodb; - --- disable_query_log - --- let $i = 400 -while ($i) -{ - insert into t2 values(repeat('abcdefghijklmnopqrstuvwxyz',1000)); - dec $i; -} - --- enable_query_log - -# now there should be no 8K pages in the buffer pool --- eval $query_i_s - -drop table t2; - -# -# 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; diff --git a/mysql-test/suite/innodb/t/innodb_index_large_prefix.test b/mysql-test/suite/innodb/t/innodb_index_large_prefix.test deleted file mode 100644 index 54fb5651649..00000000000 --- a/mysql-test/suite/innodb/t/innodb_index_large_prefix.test +++ /dev/null @@ -1,269 +0,0 @@ -# Testcase for worklog #5743: Lift the limit of index key prefixes - ---source include/have_innodb.inc - -let $innodb_file_format_orig=`select @@innodb_file_format`; -let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; -let $innodb_large_prefix_orig=`select @@innodb_large_prefix`; - -set global innodb_file_format="Barracuda"; -set global innodb_file_per_table=1; -set global innodb_large_prefix=1; - -# Create a table of DYNAMIC format, with a primary index of 1000 bytes in -# size -create table worklog5743(a TEXT not null, primary key (a(1000))) -ROW_FORMAT=DYNAMIC, engine = innodb; - -# Do some insertion and update to excercise the external cache -# code path -insert into worklog5743 values(repeat("a", 20000)); - -# default session, update the table -update worklog5743 set a = (repeat("b", 16000)); - -# Create a secondary index -create index idx on worklog5743(a(2000)); - -# Start a few sessions to do selections on table being updated in default -# session, so it would rebuild the previous version from undo log. -# 1) Default session: Initiate an update on the externally stored column -# 2) Session con1: Select from table with repeated read -# 3) Session con2: Select from table with read uncommitted -# 4) Default session: rollback updates - -begin; -update worklog5743 set a = (repeat("x", 17000)); - -# Start a new session to select the column to force it build -# an earlier version of the clustered index through undo log. So it should -# just see the result of repeat("b", 16000) -select @@session.tx_isolation; ---connect (con1,localhost,root,,) -select a = repeat("x", 17000) from worklog5743; -select a = repeat("b", 16000) from worklog5743; - -# Start another session doing "read uncommitted" query, it -# should see the uncommitted update ---connect (con2,localhost,root,,) -SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -select a = repeat("x", 17000) from worklog5743; - -# Roll back the transaction ---connection default -rollback; - -drop table worklog5743; - -# Create a table with only a secondary index has large prefix column -create table worklog5743(a1 int, a2 TEXT not null) -ROW_FORMAT=DYNAMIC, engine = innodb; - -create index idx on worklog5743(a1, a2(2000)); - -insert into worklog5743 values(9, repeat("a", 10000)); - -begin; - -update worklog5743 set a1 = 1000; - -# Do a select from another connection that would use the secondary index ---connection con1 -select @@session.tx_isolation; -explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; -select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; - -# Do read uncommitted in another session, it would show there is no -# row with a1 = 9 ---connection con2 -SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; - ---connection default -rollback; - -drop table worklog5743; - -# Create a table with a secondary index has small (50 bytes) prefix column -create table worklog5743(a1 int, a2 TEXT not null) -ROW_FORMAT=DYNAMIC, engine = innodb; - -create index idx on worklog5743(a1, a2(50)); - -insert into worklog5743 values(9, repeat("a", 10000)); - -begin; - -update worklog5743 set a1 = 1000; - -# Do a select from another connection that would use the secondary index ---connection con1 -select @@session.tx_isolation; -explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; -select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; - -# Do read uncommitted in another session, it would show there is no -# row with a1 = 9 ---connection con2 -SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; - ---connection default -rollback; - -drop table worklog5743; - -# Create a table of ROW_FORMAT=COMPRESSED format -create table worklog5743_2(a1 int, a2 TEXT not null) -ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; - -create table worklog5743_4(a1 int, a2 TEXT not null) -ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; - -# The maximum overall index record (not prefix) length for this table -# is page_zip_empty_size() / 2, which is 960. "Too big row" error ( -# HA_ERR_TO_BIG_ROW) will be printed if this limit is exceeded. -# Considering other fields and their overhead, the maximum length -# for column a2 is 940 or 941 depending on the zlib version used and -# compressBound() value used in page_zip_empty_size() (please refer -# to Bug #47495 for more detail). --- error 139 -create index idx1 on worklog5743_2(a1, a2(942)); - -create index idx1 on worklog5743_2(a1, a2(940)); - -# similarly, the maximum index record length for the table is -# 1984. Considering other fields and their overhead, the -# maximum length for column a2 is 1964 or 1965 (please refer -# to Bug #47495 for more detail). --- error 139 -create index idx1 on worklog5743_4(a1, a2(1966)); - -create index idx1 on worklog5743_4(a1, a2(1964)); - -insert into worklog5743_2 values(9, repeat("a", 10000)); -insert into worklog5743_4 values(9, repeat("a", 10000)); - -begin; - -update worklog5743_2 set a1 = 1000; -update worklog5743_4 set a1 = 1000; - -# Do a select from another connection that would use the secondary index ---connection con1 -select @@session.tx_isolation; -explain select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; -select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; -select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; - -# Do read uncommitted in another session, it would show there is no -# row with a1 = 9 ---connection con2 -SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; -select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; - ---connection default -rollback; - -drop table worklog5743_2; -drop table worklog5743_4; - -# Create a table with varchar column, and create index directly on this -# large column (without prefix) -create table worklog5743(a1 int, a2 varchar(3000)) -ROW_FORMAT=DYNAMIC, engine = innodb; - -# Create an index with large column without prefix -create index idx on worklog5743(a1, a2); - -insert into worklog5743 values(9, repeat("a", 3000)); - -begin; - -update worklog5743 set a1 = 1000; - -# Do a select from another connection that would use the secondary index ---connection con1 -select @@session.tx_isolation; -explain select a1 from worklog5743 where a1 = 9; -select a1 from worklog5743 where a1 = 9; - -# Do read uncommitted, it would show there is no row with a1 = 9 ---connection con2 -SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -select a1 from worklog5743 where a1 = 9; - ---connection default -rollback; - -drop table worklog5743; - -# Create a table with old format, and the limit is 768 bytes. --- error ER_INDEX_COLUMN_TOO_LONG -create table worklog5743(a TEXT not null, primary key (a(1000))) -engine = innodb; - -create table worklog5743(a TEXT) engine = innodb; - -# Excercise the column length check in ha_innobase::add_index() --- error ER_INDEX_COLUMN_TOO_LONG -create index idx on worklog5743(a(1000)); - -# This should be successful -create index idx on worklog5743(a(725)); - -# Perform some DMLs -insert into worklog5743 values(repeat("a", 20000)); - -begin; -insert into worklog5743 values(repeat("b", 20000)); -update worklog5743 set a = (repeat("x", 25000)); - -# Start a new session to select the table to force it build -# an earlier version of the cluster index through undo log -select @@session.tx_isolation; ---connection con1 -select a = repeat("a", 20000) from worklog5743; - ---connection con2 -SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -select a = repeat("x", 25000) from worklog5743; - ---connection default -rollback; - -drop table worklog5743; - -# Some border line test on the column length. -# We have a limit of 3072 bytes for Barracuda table -create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC, engine = innodb; - -# Length exceeds maximum supported key length, will auto-truncated to 3072 -create index idx on worklog5743(a(3073)); - -create index idx2 on worklog5743(a(3072)); - -show create table worklog5743; - -drop table worklog5743; - -# We have a limit of 767 bytes for Antelope table -create table worklog5743(a TEXT not null) engine = innodb; - --- error ER_INDEX_COLUMN_TOO_LONG -create index idx on worklog5743(a(768)); - -create index idx2 on worklog5743(a(767)); - -drop table worklog5743; - -eval SET GLOBAL innodb_file_format=$innodb_file_format_orig; -eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; -eval SET GLOBAL innodb_large_prefix=$innodb_large_prefix_orig; diff --git a/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test b/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test deleted file mode 100644 index 6019a2fcaaa..00000000000 --- a/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test +++ /dev/null @@ -1,1338 +0,0 @@ -######## suite/innodb/t/innodb_prefix_index_liftedlimit.test ########## -# # -# Testcase for worklog WL#5743: Lift the limit of index key prefixes # -# Accorrding to WL#5743 - prefix index limit is increased from 767 # -# to 3072 for innodb. This change is applicable with Barracuda file # -# format. # -# All sub-test in this file focus on prefix index along with other # -# operations # -# # -# # -# Creation: # -# 2011-05-19 Implemented this test as part of WL#5743 # -# # -###################################################################### - - ---source include/have_innodb.inc -# Save innodb variables -let $innodb_file_format_orig=`select @@innodb_file_format`; -let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; -let $innodb_large_prefix_orig=`select @@innodb_large_prefix`; - -# Set Innodb file format as feature works for Barracuda file format -set global innodb_file_format="Barracuda"; -set global innodb_file_per_table=1; -set global innodb_large_prefix=1; - --- disable_warnings -DROP TABLE IF EXISTS worklog5743; --- enable_warnings -#------------------------------------------------------------------------------ -# Prefix index with VARCHAR data type , primary/secondary index and DML ops -CREATE TABLE worklog5743 ( -col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , -PRIMARY KEY (col_1_varchar(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); -# check IS -SELECT COLUMN_NAME,INDEX_NAME,SUB_PART,INDEX_TYPE FROM -INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'worklog5743' ; -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000) -WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - - -#------------------------------------------------------------------------------ -# Prefix index with TEXT data type , primary/secondary index and DML ops -CREATE TABLE worklog5743 ( -col_1_text TEXT (4000) , col_2_text TEXT (4000) , -PRIMARY KEY (col_1_text(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -CREATE INDEX prefix_idx ON worklog5743(col_1_text (3072)); -# check IS -SELECT COLUMN_NAME,INDEX_NAME,SUB_PART,INDEX_TYPE FROM -INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'worklog5743' ; -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) -WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743 WHERE col_1_text = REPEAT("b", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - -#------------------------------------------------------------------------------ -# Prefix index with MEDIUMTEXT data type , primary/secondary index and DML ops -CREATE TABLE worklog5743 ( -col_1_mediumtext MEDIUMTEXT , col_2_mediumtext MEDIUMTEXT , -PRIMARY KEY (col_1_mediumtext(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -CREATE INDEX prefix_idx ON worklog5743(col_1_mediumtext (3072)); -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_mediumtext = REPEAT("a", 4000),col_2_mediumtext = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_mediumtext = REPEAT("c", 4000) -WHERE col_1_mediumtext = REPEAT("a", 4000) -AND col_2_mediumtext = REPEAT("o", 4000); -SELECT col_1_mediumtext = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_mediumtext = REPEAT("c", 4000) -AND col_2_mediumtext = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743 WHERE col_1_mediumtext = REPEAT("b", 4000); -SELECT col_1_mediumtext = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - - -#------------------------------------------------------------------------------ -# Prefix index with LONGTEXT data type , primary/secondary index and DML ops -CREATE TABLE worklog5743 ( -col_1_longtext LONGTEXT , col_2_longtext LONGTEXT , -PRIMARY KEY (col_1_longtext(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -CREATE INDEX prefix_idx ON worklog5743(col_1_longtext (3072)); -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_longtext = REPEAT("a", 4000) , col_2_longtext = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_longtext = REPEAT("c", 4000) -WHERE col_1_longtext = REPEAT("a", 4000) -AND col_2_longtext = REPEAT("o", 4000); -SELECT col_1_longtext = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_longtext = REPEAT("c", 4000) -AND col_2_longtext = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743 WHERE col_1_longtext = REPEAT("b", 4000); -SELECT col_1_longtext = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - - -#------------------------------------------------------------------------------ -# Prefix index with BLOB data type , primary/secondary index and DML ops -CREATE TABLE worklog5743 ( -col_1_blob BLOB (4000) , col_2_blob BLOB (4000) , -PRIMARY KEY (col_1_blob(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -CREATE INDEX prefix_idx ON worklog5743(col_1_blob (3072)); -# check IS -SELECT COLUMN_NAME,INDEX_NAME,SUB_PART,INDEX_TYPE FROM -INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'worklog5743' ; -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_blob = REPEAT("a", 4000) , col_2_blob = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_blob = REPEAT("c", 4000) -WHERE col_1_blob = REPEAT("a", 4000) AND col_2_blob = REPEAT("o", 4000); -SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_blob = REPEAT("c", 4000) AND col_2_blob = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743 WHERE col_1_blob = REPEAT("b", 4000); -SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - - -#------------------------------------------------------------------------------ -# Prefix index with MEDIUMBLOB data type , primary/secondary index and DML ops -CREATE TABLE worklog5743 ( -col_1_mediumblob MEDIUMBLOB , col_2_mediumblob MEDIUMBLOB , -PRIMARY KEY (col_1_mediumblob(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -CREATE INDEX prefix_idx ON worklog5743(col_1_mediumblob (3072)); -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_mediumblob = REPEAT("a", 4000),col_2_mediumblob = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_mediumblob = REPEAT("c", 4000) -WHERE col_1_mediumblob = REPEAT("a", 4000) -AND col_2_mediumblob = REPEAT("o", 4000); -SELECT col_1_mediumblob = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_mediumblob = REPEAT("c", 4000) -AND col_2_mediumblob = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743 WHERE col_1_mediumblob = REPEAT("b", 4000); -SELECT col_1_mediumblob = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - -#------------------------------------------------------------------------------ -# Prefix index with LONGBLOB data type , primary/secondary index and DML ops -CREATE TABLE worklog5743 ( -col_1_longblob LONGBLOB , col_2_longblob LONGBLOB , -PRIMARY KEY (col_1_longblob(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -CREATE INDEX prefix_idx ON worklog5743(col_1_longblob (3072)); -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_longblob = REPEAT("a", 4000) , col_2_longblob = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_longblob = REPEAT("c", 4000) -WHERE col_1_longblob = REPEAT("a", 4000) -AND col_2_longblob = REPEAT("o", 4000); -SELECT col_1_longblob = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_longblob = REPEAT("c", 4000) -AND col_2_longblob = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743 WHERE col_1_longblob = REPEAT("b", 4000); -SELECT col_1_longblob = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - -#------------------------------------------------------------------------------ -# Prefix index with VARBINARY data type , primary/secondary index and DML ops -CREATE TABLE worklog5743 ( -col_1_varbinary VARBINARY (4000) , col_2_varbinary VARBINARY (4000) , -PRIMARY KEY (col_1_varbinary(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (3072)); -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varbinary = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) -WHERE col_1_varbinary = REPEAT("a", 4000) -AND col_2_varbinary = REPEAT("o", 4000); -SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_varbinary = REPEAT("c", 4000) -AND col_2_varbinary = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("b", 4000); -SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - -#------------------------------------------------------------------------------ -# Prefix index with CHAR data type , composite index and DML ops -CREATE TABLE worklog5743 (col_1_char CHAR (255) , col_2_char CHAR (255), -col_3_char CHAR (255), col_4_char CHAR (255),col_5_char CHAR (255), -col_6_char CHAR (255), col_7_char CHAR (255),col_8_char CHAR (255), -col_9_char CHAR (255), col_10_char CHAR (255),col_11_char CHAR (255), -col_12_char CHAR (255), col_13_char CHAR (255),col_14_char CHAR (255) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 255) , REPEAT("o", 255), -REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255) -); -# Create index with total prefix index length = 3072 -CREATE INDEX prefix_idx ON worklog5743(col_1_char(250),col_2_char(250), -col_3_char(250),col_4_char(250),col_5_char(250),col_6_char(250), -col_7_char(250),col_8_char(250),col_9_char(250),col_10_char(250), -col_11_char(250),col_12_char(250),col_13_char(72) -); -INSERT INTO worklog5743 VALUES(REPEAT("b", 255) , REPEAT("p", 255), -REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255) -); -SELECT col_1_char = REPEAT("a", 255) , col_2_char = REPEAT("o", 255) FROM worklog5743; -UPDATE worklog5743 SET col_1_char = REPEAT("c", 255) -WHERE col_1_char = REPEAT("a", 255) AND col_2_char = REPEAT("o", 255); -SELECT col_1_char = REPEAT("c", 255) FROM worklog5743 -WHERE col_1_char = REPEAT("c", 255) AND col_2_char = REPEAT("o", 255); -INSERT INTO worklog5743 VALUES(REPEAT("a", 255) , REPEAT("o", 255), -REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255) -); -DELETE FROM worklog5743 WHERE col_1_char = REPEAT("b", 255); -SELECT col_1_char = REPEAT("c", 255) FROM worklog5743; -DROP TABLE worklog5743; - -#------------------------------------------------------------------------------ -# Prefix index with BINARY data type , composite index and DML ops -CREATE TABLE worklog5743 (col_1_binary BINARY (255) , col_2_binary BINARY (255), -col_3_binary BINARY(255),col_4_binary BINARY (255),col_5_binary BINARY (255), -col_6_binary BINARY(255),col_7_binary BINARY (255),col_8_binary BINARY (255), -col_9_binary BINARY(255),col_10_binary BINARY (255),col_11_binary BINARY (255), -col_12_binary BINARY(255),col_13_binary BINARY (255),col_14_binary BINARY (255) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 255) , REPEAT("o", 255), -REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255) -); -# Create index with total prefix index length = 3072 -CREATE INDEX prefix_idx ON worklog5743(col_1_binary (250),col_2_binary (250), -col_3_binary (250),col_4_binary (250),col_5_binary (250), -col_6_binary (250),col_7_binary (250),col_8_binary (250), -col_9_binary (250),col_10_binary (250),col_11_binary (250), -col_12_binary (250),col_13_binary (72) -); -INSERT INTO worklog5743 VALUES(REPEAT("b", 255) , REPEAT("p", 255), -REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255) -); -SELECT col_1_binary = REPEAT("a", 255) , col_2_binary = REPEAT("o", 255) FROM worklog5743; -UPDATE worklog5743 SET col_1_binary = REPEAT("c", 255) -WHERE col_1_binary = REPEAT("a", 255) -AND col_2_binary = REPEAT("o", 255); -SELECT col_1_binary = REPEAT("c", 255) FROM worklog5743 -WHERE col_1_binary = REPEAT("c", 255) -AND col_2_binary = REPEAT("o", 255); -INSERT INTO worklog5743 VALUES(REPEAT("a", 255) , REPEAT("o", 255), -REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255), -REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255) -); -DELETE FROM worklog5743 WHERE col_1_binary = REPEAT("b", 255); -SELECT col_1_binary = REPEAT("c", 255) FROM worklog5743; -DROP TABLE worklog5743; - -#------------------------------------------------------------------------------ -# Prefix index with VARCHAR data type , primary/seconday index , DML ops -# and COMPRESSED row format. KEY_BLOCK_SIZE is varied as 2 , 4 , 8. - -# With KEY_BLOCK_SIZE = 2,prefix index limit comes around ~948 for following -CREATE TABLE worklog5743_key2 ( -col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , -PRIMARY KEY (col_1_varchar(948)) -) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; -INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -#CREATE INDEX prefix_idx ON worklog5743_key2 (col_1_varchar (767)); -INSERT INTO worklog5743_key2 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) -FROM worklog5743_key2; -UPDATE worklog5743_key2 SET col_1_varchar = REPEAT("c", 4000) -WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743_key2 -WHERE col_2_varchar = REPEAT("o", 4000); -INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743_key2 WHERE col_1_varchar = REPEAT("b", 4000); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743_key2; -DROP TABLE worklog5743_key2; - -# With KEY_BLOCK_SIZE = 4,prefix index limit comes around ~1964 for following -CREATE TABLE worklog5743_key4 ( -col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , -PRIMARY KEY (col_1_varchar(1964)) -) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; -INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -#CREATE INDEX prefix_idx ON worklog5743_key4 (col_1_varchar (767)); -INSERT INTO worklog5743_key4 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) -FROM worklog5743_key4; -UPDATE worklog5743_key4 SET col_1_varchar = REPEAT("c", 4000) -WHERE col_1_varchar = REPEAT("a", 4000) -AND col_2_varchar = REPEAT("o", 4000); -SELECT col_1_varchar = REPEAT("b", 3500) FROM worklog5743_key4 -WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); -INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743_key4 WHERE col_1_varchar = REPEAT("b", 4000); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743_key4; -DROP TABLE worklog5743_key4; - -# With KEY_BLOCK_SIZE = 8,prefix index limit comes around ~3072 for following -CREATE TABLE worklog5743_key8 ( -col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , -PRIMARY KEY (col_1_varchar(3072)) -) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, engine = innodb; -INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -#CREATE INDEX prefix_idx ON worklog5743_key8 (col_1_varchar (767)); -INSERT INTO worklog5743_key8 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) -FROM worklog5743_key8; -UPDATE worklog5743_key8 SET col_1_varchar = REPEAT("c", 4000) -WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); -SELECT col_1_varchar = REPEAT("b", 3500) FROM worklog5743_key8 -WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); -INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743_key8 WHERE col_1_varchar = REPEAT("b", 4000); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743_key8; -DROP TABLE worklog5743_key8; - -# Prefix index with TEXT data type , primary/seconday index , DML ops -# and COMPRESSED row format. KEY_BLOCK_SIZE is varied as 2 , 4 , 8. - -# With KEY_BLOCK_SIZE = 2,prefix index limit comes around ~948 for following -CREATE TABLE worklog5743_key2 ( -col_1_text TEXT (4000) , col_2_text TEXT (4000) , -PRIMARY KEY (col_1_text(948)) -) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; -INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -#CREATE INDEX prefix_idx ON worklog5743_key2 (col_1_text (767)); -INSERT INTO worklog5743_key2 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) -FROM worklog5743_key2; -UPDATE worklog5743_key2 SET col_1_text = REPEAT("c", 4000) -WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); -SELECT col_1_text = REPEAT("b", 3500) FROM worklog5743_key2 -WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); -INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743_key2 WHERE col_1_text = REPEAT("b", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743_key2; -DROP TABLE worklog5743_key2; - -# With KEY_BLOCK_SIZE = 4,prefix index limit comes around ~1964 for following -CREATE TABLE worklog5743_key4 ( -col_1_text TEXT (4000) , col_2_text TEXT (4000) , -PRIMARY KEY (col_1_text(1964)) -) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; -INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -#CREATE INDEX prefix_idx ON worklog5743_key4 (col_1_text (767)); -INSERT INTO worklog5743_key4 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) -FROM worklog5743_key4; -UPDATE worklog5743_key4 SET col_1_text = REPEAT("c", 4000) -WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); -SELECT col_1_text = REPEAT("b", 3500) FROM worklog5743_key4 -WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); -INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743_key4 WHERE col_1_text = REPEAT("b", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743_key4; -DROP TABLE worklog5743_key4; - -# With KEY_BLOCK_SIZE = 8,prefix index limit comes around ~3072 for following -CREATE TABLE worklog5743_key8 ( -col_1_text TEXT (4000) , col_2_text TEXT (4000) , -PRIMARY KEY (col_1_text(3072)) -) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, engine = innodb; -INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -#CREATE INDEX prefix_idx ON worklog5743_key8 (col_1_text (767)); -INSERT INTO worklog5743_key8 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) -FROM worklog5743_key8; -UPDATE worklog5743_key8 SET col_1_text = REPEAT("c", 4000) -WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); -SELECT col_1_text = REPEAT("b", 3500) FROM worklog5743_key8 -WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); -INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743_key8 WHERE col_1_text = REPEAT("b", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743_key8; -DROP TABLE worklog5743_key8; - -# Prefix index with BLOB data type , primary/seconday index , DML ops -# and COMPRESSED row format. KEY_BLOCK_SIZE is varied as 2 , 4 , 8. - -# With KEY_BLOCK_SIZE = 2,prefix index limit comes around ~948 for following -CREATE TABLE worklog5743_key2 ( -col_1_blob BLOB (4000) , col_2_blob BLOB (4000) , -PRIMARY KEY (col_1_blob(948)) -) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; -INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -#CREATE INDEX prefix_idx ON worklog5743_key2 (col_1_blob (767)); -INSERT INTO worklog5743_key2 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_blob = REPEAT("a", 4000) , col_2_blob = REPEAT("o", 4000) -FROM worklog5743_key2; -UPDATE worklog5743_key2 SET col_1_blob = REPEAT("c", 4000) -WHERE col_1_blob = REPEAT("a", 4000) AND col_2_blob = REPEAT("o", 4000); -SELECT col_1_blob = REPEAT("b", 3500) FROM worklog5743_key2 -WHERE col_1_blob = REPEAT("c", 4000) AND col_2_blob = REPEAT("o", 4000); -INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743_key2 WHERE col_1_blob = REPEAT("b", 4000); -SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743_key2; -DROP TABLE worklog5743_key2; - -# With KEY_BLOCK_SIZE = 4,prefix index limit comes around ~1964 for following -CREATE TABLE worklog5743_key4 ( -col_1_blob BLOB (4000) , col_2_blob BLOB (4000) , -PRIMARY KEY (col_1_blob(1964)) -) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; -INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -#CREATE INDEX prefix_idx ON worklog5743_key4 (col_1_blob (767)); -INSERT INTO worklog5743_key4 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_blob = REPEAT("a", 4000) , col_2_blob = REPEAT("o", 4000) -FROM worklog5743_key4; -UPDATE worklog5743_key4 SET col_1_blob = REPEAT("c", 4000) -WHERE col_1_blob = REPEAT("a", 4000) AND col_2_blob = REPEAT("o", 4000); -SELECT col_1_blob = REPEAT("b", 3500) FROM worklog5743_key4 -WHERE col_1_blob = REPEAT("c", 4000) AND col_2_blob = REPEAT("o", 4000); -INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743_key4 WHERE col_1_blob = REPEAT("b", 4000); -SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743_key4; -DROP TABLE worklog5743_key4; - -# With KEY_BLOCK_SIZE = 8,prefix index limit comes around ~3072 for following -CREATE TABLE worklog5743_key8 ( -col_1_blob BLOB (4000) , col_2_blob BLOB (4000) , -PRIMARY KEY (col_1_blob(3072)) -) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, engine = innodb; -INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -#CREATE INDEX prefix_idx ON worklog5743_key8 (col_1_blob (767)); -INSERT INTO worklog5743_key8 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_blob = REPEAT("a", 4000) , col_2_blob = REPEAT("o", 4000) -FROM worklog5743_key8; -UPDATE worklog5743_key8 SET col_1_blob = REPEAT("c", 4000) -WHERE col_1_blob = REPEAT("a", 4000) AND col_2_blob = REPEAT("o", 4000); -SELECT col_1_blob = REPEAT("b", 3500) FROM worklog5743_key8 -WHERE col_1_blob = REPEAT("c", 4000) AND col_2_blob = REPEAT("o", 4000); -INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743_key8 WHERE col_1_blob = REPEAT("b", 4000); -SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743_key8; -DROP TABLE worklog5743_key8; - - -#------------------------------------------------------------------------------ -# Create mutiple prefix index. We can not create prefix index length > 16K -# as index is written in undo log page which of 16K size. -# So we can create max 5 prefix index of length 3072 on table -CREATE TABLE worklog5743 ( -col_1_varbinary VARBINARY (4000) , col_2_varchar VARCHAR (4000) , -col_3_text TEXT (4000), col_4_blob BLOB (4000),col_5_text TEXT (4000), -col_6_varchar VARCHAR (4000), col_7_binary BINARY (255) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), -REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), -REPEAT("a", 4000) , REPEAT("a", 255) -); - -# Update hangs if we create following 5 indexes. Uncomment them once its fix -# Bug#12547647 - UPDATE LOGGING COULD EXCEED LOG PAGE SIZE -#CREATE INDEX prefix_idx1 ON worklog5743(col_1_varbinary (3072)); -#CREATE INDEX prefix_idx2 ON worklog5743(col_2_varchar (3072)); -#CREATE INDEX prefix_idx3 ON worklog5743(col_3_text (3072)); -#CREATE INDEX prefix_idx4 ON worklog5743(col_4_blob (3072)); -#CREATE INDEX prefix_idx5 ON worklog5743(col_5_text (3072)); - -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000), -REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), -REPEAT("a", 4000) , REPEAT("a", 255) -); -SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) -WHERE col_1_varbinary = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); -SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_varbinary = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), -REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), -REPEAT("a", 4000) , REPEAT("a", 255) -); -DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("b", 4000); -SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - - -#------------------------------------------------------------------------------ -# Create mutiple prefix index. We can not create prefix index length > 16K as -# we write in undo log page which of 16K size. -# so we can create max 5 prefix index of length 3072 on table. -# Similar to above case but with transactions -CREATE TABLE worklog5743 ( -col_1_varbinary VARBINARY (4000) , col_2_varchar VARCHAR (4000) , -col_3_text TEXT (4000), col_4_blob BLOB (4000),col_5_text TEXT (4000), -col_6_varchar VARCHAR (4000), col_7_binary BINARY (255) -) ROW_FORMAT=DYNAMIC, engine = innodb; - - -# Update hangs if we create following 5 indexes. Uncomment them once its fix -CREATE INDEX prefix_idx1 ON worklog5743(col_1_varbinary (3072)); -CREATE INDEX prefix_idx2 ON worklog5743(col_2_varchar (3072)); -CREATE INDEX prefix_idx3 ON worklog5743(col_3_text (3072)); -CREATE INDEX prefix_idx4 ON worklog5743(col_4_blob (3072)); -CREATE INDEX prefix_idx5 ON worklog5743(col_5_text (3072)); - -START TRANSACTION; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), -REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), -REPEAT("a", 4000) , REPEAT("a", 255) -); -SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) -FROM worklog5743; -ROLLBACK; -START TRANSACTION; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), -REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), -REPEAT("a", 4000) , REPEAT("a", 255) -); -COMMIT; -SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) -FROM worklog5743; - -START TRANSACTION; -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000), -REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), -REPEAT("a", 4000) , REPEAT("a", 255) -); -ROLLBACK; -# Uncomment Update fater Bug#12547647 is fixed - UPDATE LOGGING COULD EXCEED LOG PAGE SIZE -# Bug#12547647 - UPDATE LOGGING COULD EXCEED LOG PAGE SIZE -#UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) -#WHERE col_1_varbinary = REPEAT("a", 4000) -#AND col_2_varchar = REPEAT("o", 4000); -SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_varbinary = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), -REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), -REPEAT("a", 4000) , REPEAT("a", 255) -); -DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("b", 4000); -SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - -#------------------------------------------------------------------------------ -# Prefix index with utf8 charset -# utf8 charcter takes 3 bytes in mysql so prefix index limit is 3072/3 = 1024 -CREATE TABLE worklog5743 ( -col_1_text TEXT (4000) CHARACTER SET 'utf8', -col_2_text TEXT (4000) CHARACTER SET 'utf8', -PRIMARY KEY (col_1_text(1024)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -CREATE INDEX prefix_idx ON worklog5743(col_1_text (1024)); -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) FROM worklog5743; -UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) -WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743 WHERE col_1_text = REPEAT("b", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - -# Prefix index with utf8 charset + varchar. -# For varchar we also log the column itself as oppose of TEXT so it error -# with limit 1024 due to overhead. ---replace_regex /> [0-9]*/> max_row_size/ --- error 1118 -CREATE TABLE worklog5743 (col_1_varchar VARCHAR (4000) CHARACTER SET 'utf8', -col_2_varchar VARCHAR (4000) CHARACTER SET 'utf8' , -PRIMARY KEY (col_1_varchar(1024)) -) ROW_FORMAT=DYNAMIC, engine = innodb; - -#------------------------------------------------------------------------------ -# prefinx index on utf8 charset with transaction -CREATE TABLE worklog5743 ( -col_1_varbinary VARBINARY (4000) , -col_2_varchar VARCHAR (4000) CHARACTER SET 'utf8', -col_3_text TEXT (4000) CHARACTER SET 'utf8', -col_4_blob BLOB (4000),col_5_text TEXT (4000), -col_6_varchar VARCHAR (4000), col_7_binary BINARY (255) -) ROW_FORMAT=DYNAMIC, engine = innodb; - - -CREATE INDEX prefix_idx2 ON worklog5743(col_2_varchar (500)); -CREATE INDEX prefix_idx3 ON worklog5743(col_3_text (500)); - -START TRANSACTION; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), -REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), -REPEAT("a", 4000) , REPEAT("a", 255) -); -SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) -FROM worklog5743; -ROLLBACK; -START TRANSACTION; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), -REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), -REPEAT("a", 4000) , REPEAT("a", 255) -); -COMMIT; -SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) -FROM worklog5743; - -START TRANSACTION; -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000), -REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), -REPEAT("a", 4000) , REPEAT("a", 255) -); -ROLLBACK; -SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_varbinary = REPEAT("c", 4000) -AND col_2_varchar = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), -REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), -REPEAT("a", 4000) , REPEAT("a", 255) -); -DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("b", 4000); -SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - - -#------------------------------------------------------------------------------ -# Prefix index with utf8 charset on TEXT data type with actual utf8 character -# like "स" and "क" -CREATE TABLE worklog5743 ( -col_1_text TEXT (4000) CHARACTER SET 'utf8', -col_2_text TEXT (4000) , -PRIMARY KEY (col_1_text(1024)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("स", 4000) , REPEAT("o", 4000)); -CREATE INDEX prefix_idx ON worklog5743(col_1_text (1024)); -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_text = REPEAT("स", 4000) , col_2_text = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_text = REPEAT("क", 4000) -WHERE col_1_text = REPEAT("स", 4000) AND col_2_text = REPEAT("o", 4000); -SELECT col_1_text = REPEAT("क", 4000) FROM worklog5743 -WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743 WHERE col_1_text = REPEAT("b", 4000); -SELECT col_1_text = REPEAT("क", 4000) FROM worklog5743; -DROP TABLE worklog5743; - - -#------------------------------------------------------------------------------ -# Prefix index with transaction when 2 client are ruuning there transaction -# in different sessions.With ISOLATION LEVEL as REPEATABLE READ and -# READ UNCOMMITTED. -CREATE TABLE worklog5743 ( -col_1_text TEXT(4000) , col_2_text TEXT(4000) , -PRIMARY KEY (col_1_text(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 200) , REPEAT("o", 200)); -SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; - ---echo "In connection 1" ---connect (con1,localhost,root,,) -SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; -SELECT COUNT(*) FROM worklog5743; - - ---echo "In connection 2" ---connect (con2,localhost,root,,) -START TRANSACTION; -INSERT INTO worklog5743 VALUES(REPEAT("b", 200) , REPEAT("o", 200)); -# Uncomment after Bug#12552164 - TRANSACTION CAN NOT SEE OLD VERSION ROWS THAT -# BEING UPDATED -#UPDATE worklog5743 SET col_1_varchar = REPEAT("d", 200) WHERE col_1_varchar = -#REPEAT("a", 200) AND col_2_varchar = REPEAT("o", 200); -SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; - - ---echo "In connection 1" ---connection con1 -select @@session.tx_isolation; -SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; -SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; - -START TRANSACTION; - -SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; -SELECT COUNT(*) FROM worklog5743; - ---echo "In connection 2" ---connection con2 -COMMIT; -# Wait for commit -let $wait_condition=SELECT COUNT(*)=0 FROM information_schema.processlist -WHERE info='COMMIT'; ---source include/wait_condition.inc - ---echo "In connection 1" ---connection con1 -SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; -SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; -SELECT COUNT(*) FROM worklog5743; -COMMIT; - ---connection default -DROP TABLE worklog5743; - - -#------------------------------------------------------------------------------ -# Prefix index with transaction when 2 client are ruuning there transaction -# in different sessions.With ISOLATION LEVEL as REPEATABLE READ and -# READ UNCOMMITTED. Same as above case but con2 starts tnx before con1 - -CREATE TABLE worklog5743 ( -col_1_text TEXT(4000) , col_2_text TEXT(4000) , -PRIMARY KEY (col_1_text(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 200) , REPEAT("o", 200)); -SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; - ---echo "In connection 1" ---connection con1 -SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; -SELECT COUNT(*) FROM worklog5743; -START TRANSACTION; - - ---echo "In connection 2" ---connection con2 -START TRANSACTION; -INSERT INTO worklog5743 VALUES(REPEAT("b", 200) , REPEAT("o", 200)); -DELETE FROM worklog5743 WHERE col_1_text = REPEAT("a", 200); -SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; -COMMIT; -# Wait for commit -let $wait_condition=SELECT COUNT(*)=0 FROM information_schema.processlist -WHERE info='COMMIT'; ---source include/wait_condition.inc - - ---echo "In connection 1" ---connection con1 -SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; - -SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; -SELECT COUNT(*) FROM worklog5743; -COMMIT; - ---connection default -DROP TABLE worklog5743; - -#------------------------------------------------------------------------------ - -# Prefix index with transaction when 2 client are ruuning there transaction -# in different sessions.With ISOLATION LEVEL as REPEATABLE READ and -# READ UNCOMMITTED. Same as above cases but with ROLLBACK - -CREATE TABLE worklog5743 ( -col_1_text TEXT(4000) , col_2_text TEXT(4000) , -PRIMARY KEY (col_1_text(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 200) , REPEAT("o", 200)); -SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; - ---echo "In connection 1" ---connection con1 -SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; -SELECT COUNT(*) FROM worklog5743; -START TRANSACTION; - - ---echo "In connection 2" ---connection con2 -START TRANSACTION; -INSERT INTO worklog5743 VALUES(REPEAT("b", 200) , REPEAT("o", 200)); -DELETE FROM worklog5743 WHERE col_1_text = REPEAT("a", 200); -SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; -ROLLBACK; -# Wait for rollback -let $wait_condition=SELECT COUNT(*)=0 FROM information_schema.processlist -WHERE info='COMMIT'; ---source include/wait_condition.inc - - ---echo "In connection 1" ---connection con1 -SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM -worklog5743; -SELECT COUNT(*) FROM worklog5743; -COMMIT; - ---disconnect con1 ---disconnect con2 - ---connection default -DROP TABLE worklog5743; - - -#------------------------------------------------------------------------------ -# Select queries on prefix index column as index will be used in queries. -# Use few select functions , join condition , subqueries. - -CREATE TABLE worklog5743 ( -col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , -PRIMARY KEY (col_1_varchar(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000) -WHERE col_1_varchar = REPEAT("a", 4000) -AND col_2_varchar = REPEAT("o", 4000); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_varchar = REPEAT("c", 4000) -AND col_2_varchar = REPEAT("o", 4000); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; - -# Select with Join -SELECT tbl1.col_1_varchar = tbl2.col_1_varchar -FROM worklog5743 tbl1 , worklog5743 tbl2 -WHERE tbl1.col_1_varchar = tbl2.col_1_varchar ; - -# Select in subquey -SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1 -WHERE col_1_varchar IN (SELECT tbl2.col_1_varchar FROM worklog5743 tbl2) ; -SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1 -WHERE col_1_varchar NOT IN (SELECT tbl2.col_1_varchar FROM worklog5743 tbl2) ; -SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1 WHERE -col_1_varchar IN (SELECT tbl2.col_1_varchar FROM worklog5743 tbl2) -AND col_1_varchar = REPEAT("c", 4000); -SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1 -WHERE col_1_varchar in ( -SELECT tbl2.col_1_varchar FROM worklog5743 tbl2 -WHERE tbl1.col_1_varchar != tbl2.col_1_varchar -) ; -SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1 -WHERE col_1_varchar in ( -SELECT tbl2.col_1_varchar FROM worklog5743 tbl2 -WHERE tbl1.col_1_varchar = tbl2.col_1_varchar -) ; - -# function -SELECT -REVERSE(col_1_varchar) = REPEAT("c", 4000) , -REVERSE(REVERSE(col_1_varchar)) = REPEAT("c", 4000) -FROM worklog5743; -SELECT -UPPER(col_1_varchar) = REPEAT("c", 4000) , -UPPER(col_1_varchar) = REPEAT("C", 4000) , -LOWER(UPPER(col_1_varchar)) = REPEAT("c", 4000) -FROM worklog5743; -SELECT -col_1_varchar = REPEAT("c", 4000) -FROM worklog5743 WHERE col_1_varchar like '%c__%'; -SELECT SUBSTRING(INSERT(col_1_varchar, 1, 4, 'kkkk'),1,10) FROM worklog5743 ; -SELECT CONCAT(SUBSTRING(col_1_varchar,-5,3),'append') FROM worklog5743 ; - - -DROP TABLE worklog5743; - -#------------------------------------------------------------------------------ -# Prefix index with NULL values -CREATE TABLE worklog5743 ( -col_1_varchar VARCHAR (4000) , -col_2_varchar VARCHAR (4000) , -UNIQUE INDEX (col_1_varchar(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 -VALUES(concat(REPEAT("a", 2000),REPEAT("b", 1000),REPEAT("c", 1000)), REPEAT("o", 4000)); -INSERT INTO worklog5743 -VALUES(concat(REPEAT("a", 2000),REPEAT("b", 2000)), REPEAT("o", 4000)); -INSERT INTO worklog5743 VALUES(NULL,NULL); -INSERT INTO worklog5743 VALUES(NULL,NULL); -# check IS -SELECT COLUMN_NAME,INDEX_NAME,SUB_PART,INDEX_TYPE -FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'worklog5743' ; -SELECT col_1_varchar FROM worklog5743 WHERE col_1_varchar IS NULL; -SELECT col_1_varchar = concat(REPEAT("a", 2000),REPEAT("b", 2000)) -FROM worklog5743 WHERE col_1_varchar IS NOT NULL ORDER BY 1; - - -DROP TABLE worklog5743; - -# ----------------------------------------------------------------------------- -# Try drop and add secondary prefix index -CREATE TABLE worklog5743 ( -col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , -PRIMARY KEY (col_1_varchar(3072))) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -# Create index -CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -# Drop index -DROP INDEX prefix_idx ON worklog5743; - -SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000) -WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); -# Again add index -CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - -# ----------------------------------------------------------------------------- - -# Try drop and add primary prefix index -CREATE TABLE worklog5743 ( -col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , -PRIMARY KEY `prefix_primary` (col_1_varchar(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -# Create index -CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -# Drop index -ALTER TABLE worklog5743 DROP PRIMARY KEY; - -SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000) -WHERE col_1_varchar = REPEAT("a", 4000) -AND col_2_varchar = REPEAT("o", 4000); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_varchar = REPEAT("c", 4000) -AND col_2_varchar = REPEAT("o", 4000); -# Again add index -ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar(3072)); - -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); ---error 1062 -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - - -# ----------------------------------------------------------------------------- - -# Try drop and add both (primary/secondary) prefix index -CREATE TABLE worklog5743 ( -col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , -PRIMARY KEY `prefix_primary` (col_1_varchar(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -# Create index -CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -# Drop primary index -ALTER TABLE worklog5743 DROP PRIMARY KEY; -# Drop secondary index -DROP INDEX prefix_idx ON worklog5743; - -SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000) -WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); -# Again add index -ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar(3072)); -CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); - -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); ---error 1062 -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000); -SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; - - -# ----------------------------------------------------------------------------- -# Drop index from differnt session -CREATE TABLE worklog5743 ( -col_1_varchar VARCHAR(4000) , col_2_varchar VARCHAR(4000) , -PRIMARY KEY (col_1_varchar (3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("c", 3500) , REPEAT("o", 3500)); -CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); - -# Start new session ---connect (con1,localhost,root,,) - - ---echo " Switching to con1 connection For select " ---connection con1 -SELECT col_1_varchar = REPEAT("c", 3500) , col_2_varchar = REPEAT("o", 3500) -FROM worklog5743; - ---echo " Switching to default connection For DMLs " ---connection default -START TRANSACTION; -INSERT INTO worklog5743 VALUES(REPEAT("a", 3500) , REPEAT("o", 3500)); -SELECT col_1_varchar = REPEAT("b", 3500) FROM worklog5743 -WHERE col_2_varchar = REPEAT("o", 3500); -COMMIT; - ---echo " Switching to con1 connection For Dropping index and some DMLs " ---connection con1 -START TRANSACTION; -INSERT INTO worklog5743 VALUES(REPEAT("k", 3500),REPEAT("p", 3500)); -# Drop primary index -ALTER TABLE worklog5743 DROP PRIMARY KEY; -UPDATE worklog5743 SET col_1_varchar = REPEAT("b", 3500) -WHERE col_1_varchar = REPEAT("a", 3500) -AND col_2_varchar = REPEAT("o", 3500); -SELECT col_1_varchar = REPEAT("b", 3500) FROM worklog5743 -WHERE col_2_varchar = REPEAT("o", 3500); - ---echo " Switching to default connection For DELETE " ---connection default -DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 3500); -SELECT col_1_varchar = REPEAT("a", 3500) FROM worklog5743 -WHERE col_2_varchar = REPEAT("p", 3500); - ---echo " Switching to con1 connection to commit changes " ---connection con1 -COMMIT; - ---echo " Switching to default connection to drop and end sub-test " ---connection default -DROP TABLE worklog5743; - - - -# ----------------------------------------------------------------------------- -# Create prefix index with length < 3072 , length = 3072 , length > 3072 -# - varbinary data type + secondary index -CREATE TABLE worklog5743 ( -col_1_varbinary VARBINARY (4000) , col_2_varbinary VARBINARY (4000) , -PRIMARY KEY (col_1_varbinary(3072))) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -# Create index of 3072 -CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (3072)); -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varbinary = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) -WHERE col_1_varbinary = REPEAT("a", 4000) -AND col_2_varbinary = REPEAT("o", 4000); -SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_varbinary = REPEAT("c", 4000) -AND col_2_varbinary = REPEAT("o", 4000); -DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("c", 4000); -SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_varbinary = REPEAT("c", 4000) -AND col_2_varbinary = REPEAT("o", 4000); -# Drop index -DROP INDEX prefix_idx ON worklog5743; -SELECT col_1_varbinary = REPEAT("b", 4000) FROM worklog5743 -WHERE col_1_varbinary = REPEAT("b", 4000) -AND col_2_varbinary = REPEAT("p", 4000); - - -# Again add index length < 3072 -CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (2000)); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -SELECT col_1_varbinary = REPEAT("a", 4000) FROM worklog5743; -UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) -WHERE col_1_varbinary = REPEAT("a", 4000) -AND col_2_varbinary = REPEAT("o", 4000); -DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("c", 4000); -SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_varbinary = REPEAT("c", 4000) -AND col_2_varbinary = REPEAT("o", 4000); -# Drop index -DROP INDEX prefix_idx ON worklog5743; - -# Again add index length > 3072. -# If "innodb_large_prefix" is turned on, than the index prefix larger than 3072 -# will be truncated to 3072. If the table is REDUNDANT and COMPACT, which does -# not support prefix > 767, the create index will be rejected. -CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (4000)); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -SELECT col_1_varbinary = REPEAT("a", 4000) FROM worklog5743; -UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) -WHERE col_1_varbinary = REPEAT("a", 4000) -AND col_2_varbinary = REPEAT("o", 4000); -DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("c", 4000); -SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_varbinary = REPEAT("c", 4000) -AND col_2_varbinary = REPEAT("o", 4000); - - -DROP TABLE worklog5743; - -# ----------------------------------------------------------------------------- -# Create prefix index with length < 3072 , length = 3072 , length > 3072 -# text data type + secondary index -CREATE TABLE worklog5743 (col_1_text TEXT (4000) , col_2_text TEXT (4000) , -PRIMARY KEY (col_1_text(500)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -# Create index of 3072 -CREATE INDEX prefix_idx ON worklog5743(col_1_text (3072)); -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) -FROM worklog5743; -UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) -WHERE col_1_text = REPEAT("a", 4000) -AND col_2_text = REPEAT("o", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); -DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); -# Drop index -DROP INDEX prefix_idx ON worklog5743; -SELECT col_1_text = REPEAT("b", 4000) FROM worklog5743 -WHERE col_1_text = REPEAT("b", 4000) AND col_2_text = REPEAT("p", 4000); - -# Again add index length < 3072 -CREATE INDEX prefix_idx ON worklog5743(col_1_text (1000)); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743; -UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) -WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); -DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); -# Drop index -DROP INDEX prefix_idx ON worklog5743; - -# Again add index length > 3072. Expect error.Length exceeds maximum supported -# key length -# Again add index length > 3072. -# If "innodb_large_prefix" is turned on, than the index prefix larger than 3072 -# will be truncated to 3072. If the table is REDUNDANT and COMPACT, which does -# not support prefix > 767, the create index will be rejected. -CREATE INDEX prefix_idx ON worklog5743(col_1_text (4000)); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743; -UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) -WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000); -DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000); - -DROP TABLE worklog5743; - - -# ----------------------------------------------------------------------------- -# Create prefix index with length < 948 , length = 948 , length > 948 -# For compressed row type + primary key -CREATE TABLE worklog5743 ( -col_1_text TEXT (4000) , col_2_text TEXT (4000) , -PRIMARY KEY (col_1_text(948)) -) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); -# Create index of 767 -INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); -SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) FROM worklog5743; -UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) -WHERE col_1_text = REPEAT("a", 4000) -AND col_2_text = REPEAT("o", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_text = REPEAT("c", 4000) -AND col_2_text = REPEAT("o", 4000); -DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_text = REPEAT("c", 4000) -AND col_2_text = REPEAT("o", 4000); -# Drop index -#DROP INDEX prefix_idx ON worklog5743; -ALTER TABLE worklog5743 DROP PRIMARY KEY; -SELECT col_1_text = REPEAT("b", 4000) FROM worklog5743 -WHERE col_1_text = REPEAT("b", 4000) -AND col_2_text = REPEAT("p", 4000); - -# Again add index length < 767 -ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_text (700)); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743; -UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) -WHERE col_1_text = REPEAT("a", 4000) -AND col_2_text = REPEAT("o", 4000); -DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_text = REPEAT("c", 4000) -AND col_2_text = REPEAT("o", 4000); -# Drop index -ALTER TABLE worklog5743 DROP PRIMARY KEY; - -# Again add index length > 948. Expect error 'to big row ' due to exceed -# in key length. --- error 139 -ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_text (950)); -INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); -SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743; -UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000) -WHERE col_1_text = REPEAT("a", 4000) -AND col_2_text = REPEAT("o", 4000); -DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000); -SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743 -WHERE col_1_text = REPEAT("c", 4000) -AND col_2_text = REPEAT("o", 4000); - -DROP TABLE worklog5743; - -# ----------------------------------------------------------------------------- -# Create prefix index with length < 3072 , length = 3072 , length > 3072 -# data types VARCHAR -CREATE TABLE worklog5743 ( -col_1_varchar VARCHAR (4000) , PRIMARY KEY (col_1_varchar(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -ALTER TABLE worklog5743 DROP PRIMARY KEY; -ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar (900)); -ALTER TABLE worklog5743 DROP PRIMARY KEY; -# Again add index length > 3072. Expect error.Length exceeds maximum supported -# key length -# Again add index length > 3072. -# If "innodb_large_prefix" is turned on, than the index prefix larger than 3072 -# will be truncated to 3072. If the table is REDUNDANT and COMPACT, which does -# not support prefix > 767, the create index will be rejected. -# Index length is truncated only for 'create index' , but error if we add -# prefix index with length > 3072 ---error ER_TOO_LONG_KEY -ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar (3073)); -DROP TABLE worklog5743; - - -CREATE TABLE worklog5743 ( -col_1_BLOB BLOB (4000) , PRIMARY KEY (col_1_BLOB(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; -ALTER TABLE worklog5743 DROP PRIMARY KEY; -ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_BLOB (500)); -ALTER TABLE worklog5743 DROP PRIMARY KEY; -# Negative case -# Again add index length > 3072. Expect error.Length exceeds maximum supported -# key length -# Index length is truncated only for 'create index' , but error if we add -# prefix index with length > 3072 ---error ER_TOO_LONG_KEY -ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_BLOB (3073)); - -DROP TABLE worklog5743; - -# ----------------------------------------------------------------------------- -# Error on adding larger prefix if violates unique index. -CREATE TABLE worklog5743 ( -col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) -) ROW_FORMAT=DYNAMIC, engine = innodb; -INSERT INTO worklog5743 -VALUES(concat(REPEAT("a", 2000),REPEAT("b", 1000),REPEAT("c", 1000)), -REPEAT("o", 4000)); -INSERT INTO worklog5743 -VALUES(concat(REPEAT("a", 2000),REPEAT("b", 2000)), REPEAT("o", 4000)); ---error 1062 -ALTER TABLE worklog5743 ADD PRIMARY KEY `pk_idx` (col_1_varchar(3000)); -DROP TABLE worklog5743; - -# ----------------------------------------------------------------------------- -set global innodb_large_prefix=0; -# Prefix index > 767 is allowed if innodb_large_prefix is set to 1 ---error ER_TOO_LONG_KEY -CREATE TABLE worklog5743 ( -col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , -PRIMARY KEY (col_1_varchar(3072)) -) ROW_FORMAT=DYNAMIC, engine = innodb; - - -# ----------------------------------------------------------------------------- -set global innodb_large_prefix=0; -# Backward compatibility test - Index lenghth > 767 is truncated for REDUNDANT -# and COMPACT -CREATE TABLE worklog5743 ( -col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , -PRIMARY KEY (col_1_varchar(767)) -) engine = innodb; -# Prefix index > 767 is truncated with REDUNDANT and COMPACT -CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (1000)); -DROP TABLE worklog5743; -#------------------------------------------------------------------------------ - -eval SET GLOBAL innodb_file_format=$innodb_file_format_orig; -eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; -eval SET GLOBAL innodb_large_prefix=$innodb_large_prefix_orig; |