diff options
| author | Marko Mäkelä <marko.makela@mariadb.com> | 2022-02-17 14:37:17 +0200 |
|---|---|---|
| committer | Marko Mäkelä <marko.makela@mariadb.com> | 2022-02-17 14:37:17 +0200 |
| commit | f04b459fb75b28dd57c7f22bc69588f087956373 (patch) | |
| tree | 62f1dd7658c744e961f54d7e8ddfc12869e384b0 | |
| parent | 8bc5bf2cb6a235e55a8fd9aaaff5d2b18296d37b (diff) | |
| parent | cac995ec6f7d23c5b725e6ee19fc4f6ed38561f1 (diff) | |
| download | mariadb-git-f04b459fb75b28dd57c7f22bc69588f087956373.tar.gz | |
Merge 10.5 into 10.6
42 files changed, 678 insertions, 782 deletions
diff --git a/client/mysqltest.cc b/client/mysqltest.cc index 77fffb8cfb3..9ad73f3e09e 100644 --- a/client/mysqltest.cc +++ b/client/mysqltest.cc @@ -156,6 +156,7 @@ static struct property prop_list[] = { { &display_session_track_info, 0, 1, 1, "$ENABLED_STATE_CHANGE_INFO" }, { &display_metadata, 0, 0, 0, "$ENABLED_METADATA" }, { &ps_protocol_enabled, 0, 0, 0, "$ENABLED_PS_PROTOCOL" }, + { &view_protocol_enabled, 0, 0, 0, "$ENABLED_VIEW_PROTOCOL"}, { &disable_query_log, 0, 0, 1, "$ENABLED_QUERY_LOG" }, { &disable_result_log, 0, 0, 1, "$ENABLED_RESULT_LOG" }, { &disable_warnings, 0, 0, 1, "$ENABLED_WARNINGS" } @@ -170,6 +171,7 @@ enum enum_prop { P_SESSION_TRACK, P_META, P_PS, + P_VIEW, P_QUERY, P_RESULT, P_WARN, @@ -375,6 +377,7 @@ enum enum_commands { Q_LOWERCASE, Q_START_TIMER, Q_END_TIMER, Q_CHARACTER_SET, Q_DISABLE_PS_PROTOCOL, Q_ENABLE_PS_PROTOCOL, + Q_DISABLE_VIEW_PROTOCOL, Q_ENABLE_VIEW_PROTOCOL, Q_ENABLE_NON_BLOCKING_API, Q_DISABLE_NON_BLOCKING_API, Q_DISABLE_RECONNECT, Q_ENABLE_RECONNECT, Q_IF, @@ -462,6 +465,8 @@ const char *command_names[]= "character_set", "disable_ps_protocol", "enable_ps_protocol", + "disable_view_protocol", + "enable_view_protocol", "enable_non_blocking_api", "disable_non_blocking_api", "disable_reconnect", @@ -1387,6 +1392,16 @@ void close_connections() DBUG_VOID_RETURN; } +void close_util_connections() +{ + DBUG_ENTER("close_util_connections"); + if (cur_con->util_mysql) + { + mysql_close(cur_con->util_mysql); + cur_con->util_mysql = 0; + } + DBUG_VOID_RETURN; +} void close_statements() { @@ -9720,6 +9735,14 @@ int main(int argc, char **argv) case Q_ENABLE_PS_PROTOCOL: set_property(command, P_PS, ps_protocol); break; + case Q_DISABLE_VIEW_PROTOCOL: + set_property(command, P_VIEW, 0); + /* Close only util connections */ + close_util_connections(); + break; + case Q_ENABLE_VIEW_PROTOCOL: + set_property(command, P_VIEW, view_protocol); + break; case Q_DISABLE_NON_BLOCKING_API: non_blocking_api_enabled= 0; break; diff --git a/man/mysqladmin.1 b/man/mysqladmin.1 index aa77cd989fc..2b6fab12f0e 100644 --- a/man/mysqladmin.1 +++ b/man/mysqladmin.1 @@ -15,7 +15,7 @@ .\" administration: server .\" server administration .SH "NAME" -mysqladmin \- client for administering a MariaB server +mysqladmin \- client for administering a MariaDB server .SH "SYNOPSIS" .HP \w'\fBmysqladmin\ [\fR\fB\fIoptions\fR\fR\fB]\ \fR\fB\fIcommand\fR\fR\fB\ [\fR\fB\fIcommand\-arg\fR\fR\fB]\ [\fR\fB\fIcommand\fR\fR\fB\ [\fR\fB\fIcommand\-arg\fR\fR\fB]]\ \&.\&.\&.\fR\ 'u \fBmysqladmin [\fR\fB\fIoptions\fR\fR\fB] \fR\fB\fIcommand\fR\fR\fB [\fR\fB\fIcommand\-arg\fR\fR\fB] [\fR\fB\fIcommand\fR\fR\fB [\fR\fB\fIcommand\-arg\fR\fR\fB]] \&.\&.\&.\fR diff --git a/mysql-test/include/have_view_protocol.inc b/mysql-test/include/have_view_protocol.inc new file mode 100644 index 00000000000..6b1436a7a7a --- /dev/null +++ b/mysql-test/include/have_view_protocol.inc @@ -0,0 +1,6 @@ +# The file with expected results fits only to a run with +# view-protocol. +if (`SELECT $VIEW_PROTOCOL = 0`) +{ + --skip Test requires view-protocol +}
\ No newline at end of file diff --git a/mysql-test/main/check_view_protocol.result b/mysql-test/main/check_view_protocol.result new file mode 100644 index 00000000000..697bc759498 --- /dev/null +++ b/mysql-test/main/check_view_protocol.result @@ -0,0 +1,19 @@ +flush status; +create table t1 (a int); +insert into t1 (a) values (1); +create table t2 (b int); +insert into t2 (b) values (2); +select * from t1; +a +1 +show status like 'Opened_views'; +Variable_name Value +Opened_views 1 +flush status; +select * from t2; +b +2 +show status like 'Opened_views'; +Variable_name Value +Opened_views 0 +drop table t1, t2; diff --git a/mysql-test/main/check_view_protocol.test b/mysql-test/main/check_view_protocol.test new file mode 100644 index 00000000000..c4d5670669c --- /dev/null +++ b/mysql-test/main/check_view_protocol.test @@ -0,0 +1,21 @@ +-- source include/have_view_protocol.inc + +flush status; + +create table t1 (a int); +insert into t1 (a) values (1); +create table t2 (b int); +insert into t2 (b) values (2); +select * from t1; +show status like 'Opened_views'; + +flush status; +--disable_view_protocol +select * from t2; +--enable_view_protocol + +show status like 'Opened_views'; + +drop table t1, t2; + + diff --git a/mysql-test/main/session_tracker_last_gtid.result b/mysql-test/main/session_tracker_last_gtid.result index 795d0aaa2a2..5a78d5f03ce 100644 --- a/mysql-test/main/session_tracker_last_gtid.result +++ b/mysql-test/main/session_tracker_last_gtid.result @@ -32,3 +32,4 @@ drop table t1; -- last_gtid -- 0-1-1002 +reset master; diff --git a/mysql-test/main/session_tracker_last_gtid.test b/mysql-test/main/session_tracker_last_gtid.test index 24cf2104a0c..58aee2de837 100644 --- a/mysql-test/main/session_tracker_last_gtid.test +++ b/mysql-test/main/session_tracker_last_gtid.test @@ -17,3 +17,4 @@ select @@last_gtid; drop table t1; --disable_session_track_info +reset master; diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result index 2ccdfe20d0e..737db83103c 100644 --- a/mysql-test/suite/innodb/r/foreign_key.result +++ b/mysql-test/suite/innodb/r/foreign_key.result @@ -656,9 +656,9 @@ START TRANSACTION WITH CONSISTENT SNAPSHOT; connection default; DELETE IGNORE FROM t1 WHERE b = 1; Warnings: -Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 20. Please drop extra constraints and try again +Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop extra constraints and try again Warning 1296 Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB -Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 20. Please drop extra constraints and try again +Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop extra constraints and try again Warning 1296 Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB SELECT a FROM t1 FORCE INDEX(a); a @@ -809,6 +809,25 @@ ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint Parsing foreign keys 3... ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") Parsing foreign keys 4... +# +# MDEV-27583 InnoDB uses different constants for FK cascade +# error message in SQL vs error log +# +CREATE TABLE t1 +(a INT, b INT, KEY(b), +CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE) +ENGINE=InnoDB; +INSERT INTO t1 (a,b) VALUES +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,1),(1,0); +DELETE FROM t1 WHERE b = 1; +ERROR HY000: Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB +SHOW WARNINGS; +Level Code Message +Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop extra constraints and try again +Error 1296 Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB +DROP TABLE t1; +FOUND 1 /InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15.*/ in mysqld.1.err # End of 10.2 tests CREATE TABLE t1 (a GEOMETRY, INDEX(a(8)), FOREIGN KEY (a) REFERENCES x (xx)) ENGINE=InnoDB; diff --git a/mysql-test/suite/innodb/r/innodb-16k.result b/mysql-test/suite/innodb/r/innodb-16k.result index 84b1eb6d3d3..53902ce6ca0 100644 --- a/mysql-test/suite/innodb/r/innodb-16k.result +++ b/mysql-test/suite/innodb/r/innodb-16k.result @@ -1,17 +1,18 @@ call mtr.add_suppression("InnoDB: Cannot add field .* in table"); -SET @save_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency; -SET GLOBAL innodb_purge_rseg_truncate_frequency=1; # Test 1) Show the page size from Information Schema SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size'; variable_value 16384 # Test 4) The maximum row size is dependent upon the page size. -# Redundant: 8123, Compact: 8126. -# Compressed: 8126, Dynamic: 8126. +# Redundant: 8123, Compact: 8126, Dynamic: 8126. # Each row format has its own amount of overhead that # varies depending on number of fields and other overhead. SET SESSION innodb_strict_mode = ON; +SET @save_frequency=@@GLOBAL.innodb_purge_rseg_truncate_frequency; +SET @save_level=@@GLOBAL.innodb_compression_level; +SET GLOBAL innodb_purge_rseg_truncate_frequency=1; +SET GLOBAL innodb_compression_level=1; CREATE TABLE t1 ( c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), @@ -20,7 +21,7 @@ c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200), c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), -c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(157) +c36 char(200), c37 char(200), c38 char(196) ) ROW_FORMAT=compressed; DROP TABLE t1; CREATE TABLE t1 ( @@ -500,12 +501,13 @@ drop table t1; CREATE TABLE t1(c text, PRIMARY KEY (c(440))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. -CREATE TABLE t1(c text, PRIMARY KEY (c(438))) +CREATE TABLE t1(c text, PRIMARY KEY (c(292))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512)); -InnoDB 0 transactions not purged DROP TABLE t1; +InnoDB 0 transactions not purged +SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; +SET GLOBAL innodb_compression_level=@save_level; DROP TABLE t1_purge, t2_purge, t3_purge, t4_purge; DROP TABLE tlong; DROP TABLE tlong2; -SET GLOBAL innodb_purge_rseg_truncate_frequency=@save_frequency; diff --git a/mysql-test/suite/innodb/r/instant_alter,32k.rdiff b/mysql-test/suite/innodb/r/instant_alter,32k.rdiff index 37c3c479e68..cfe010012b0 100644 --- a/mysql-test/suite/innodb/r/instant_alter,32k.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter,32k.rdiff @@ -1,15 +1,13 @@ ---- instant_alter.result -+++ instant_alter,32k.result -@@ -2,7 +2,7 @@ - # MDEV-11369: Instant ADD COLUMN for InnoDB - # +@@ -5,7 +5,7 @@ + SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT; + call mtr.add_suppression("Cannot add field `.*` in table `test`.`.*` because after adding it, the row size is"); CREATE TABLE t(a INT UNIQUE)ENGINE=InnoDB ROW_FORMAT=COMPACT; -ALTER TABLE t ADD e INT, ROW_FORMAT=COMPRESSED; +ALTER TABLE t ADD e INT, ROW_FORMAT=DYNAMIC; INSERT INTO t SET a=1; SET @old_instant= (SELECT variable_value FROM information_schema.global_status -@@ -33,17 +33,17 @@ +@@ -36,17 +36,17 @@ `c` int(11) NOT NULL, `d` int(11) NOT NULL, UNIQUE KEY `a` (`a`) @@ -30,7 +28,7 @@ DROP TABLE t; connect analyze, localhost, root; connection default; -@@ -374,7 +374,7 @@ +@@ -404,7 +404,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -39,7 +37,7 @@ connection default; ALTER TABLE big ADD COLUMN (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde', -@@ -397,7 +397,7 @@ +@@ -428,7 +428,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -48,7 +46,7 @@ connection default; ROLLBACK; CHECKSUM TABLE big; -@@ -410,7 +410,7 @@ +@@ -442,7 +442,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -56,8 +54,8 @@ +1 connection default; InnoDB 0 transactions not purged - DROP TABLE t1,t2,t3,big; -@@ -734,7 +734,7 @@ + DROP TABLE t1,t2,t3,t4,big; +@@ -1326,7 +1326,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -66,7 +64,7 @@ connection default; ALTER TABLE big ADD COLUMN (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde', -@@ -757,7 +757,7 @@ +@@ -1350,7 +1350,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -75,7 +73,7 @@ connection default; ROLLBACK; CHECKSUM TABLE big; -@@ -770,7 +770,7 @@ +@@ -1364,7 +1364,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -83,8 +81,8 @@ +1 connection default; InnoDB 0 transactions not purged - DROP TABLE t1,t2,t3,big; -@@ -1094,7 +1094,7 @@ + DROP TABLE t1,t2,t3,t4,big; +@@ -2248,7 +2248,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -93,7 +91,7 @@ connection default; ALTER TABLE big ADD COLUMN (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde', -@@ -1117,7 +1117,7 @@ +@@ -2272,7 +2272,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -102,7 +100,7 @@ connection default; ROLLBACK; CHECKSUM TABLE big; -@@ -1130,7 +1130,7 @@ +@@ -2286,7 +2286,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -110,4 +108,4 @@ +1 connection default; InnoDB 0 transactions not purged - DROP TABLE t1,t2,t3,big; + DROP TABLE t1,t2,t3,t4,big; diff --git a/mysql-test/suite/innodb/r/instant_alter,4k.rdiff b/mysql-test/suite/innodb/r/instant_alter,4k.rdiff index 14ef4971267..a885eb260d6 100644 --- a/mysql-test/suite/innodb/r/instant_alter,4k.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter,4k.rdiff @@ -1,5 +1,3 @@ ---- instant_alter.result -+++ instant_alter,4k.result @@ -244,7 +244,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; @@ -47,7 +45,7 @@ SELECT * FROM t2; id c1 d1 1 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf -@@ -326,8 +332,14 @@ +@@ -330,8 +336,14 @@ affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE t3 ADD COLUMN b BLOB NOT NULL; @@ -63,7 +61,7 @@ INSERT INTO t3 SET id=4; ERROR HY000: Field 'c2' doesn't have a default value INSERT INTO t3 SET id=4, c2=0, b=0xf09f98b1; -@@ -340,7 +352,9 @@ +@@ -344,7 +356,9 @@ ALTER TABLE t3 CHANGE t phrase TEXT DEFAULT 0xc3a4c3a448, CHANGE b b BLOB NOT NULL DEFAULT 'binary line of business'; affected rows: 4 @@ -74,7 +72,7 @@ INSERT INTO t3 SET id=5, c2=9; Warnings: Note 1265 Data truncated for column 'c7' at row 1 -@@ -354,7 +368,9 @@ +@@ -358,7 +372,9 @@ 5 9 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 ääH binary line of business ALTER TABLE t3 DROP c3, DROP c7; affected rows: 0 @@ -85,7 +83,7 @@ SELECT * FROM t3; id c2 c4 c5 c6 c8 phrase b 1 1 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog -@@ -382,6 +398,8 @@ +@@ -386,6 +402,8 @@ (id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000), p POINT NOT NULL DEFAULT ST_GeomFromText('POINT(0 0)'), SPATIAL INDEX(p)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; @@ -94,7 +92,7 @@ BEGIN; INSERT INTO big SET id=1, c1=REPEAT('a', 200), c2=REPEAT('b', 200), c3=REPEAT('c', 159); -@@ -400,13 +418,15 @@ +@@ -404,13 +422,15 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -112,7 +110,7 @@ CHECKSUM TABLE big; Table Checksum test.big 1705165209 -@@ -424,7 +444,7 @@ +@@ -428,7 +448,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -121,7 +119,7 @@ connection default; ROLLBACK; CHECKSUM TABLE big; -@@ -438,7 +458,7 @@ +@@ -442,7 +462,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -130,7 +128,7 @@ connection default; InnoDB 0 transactions not purged DROP TABLE t1,t2,t3,t4,big; -@@ -1162,7 +1182,7 @@ +@@ -1166,7 +1186,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -139,7 +137,7 @@ connection default; ROLLBACK; connection analyze; -@@ -1173,7 +1193,7 @@ +@@ -1177,7 +1197,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -148,7 +146,7 @@ connection default; BEGIN; UPDATE t2 SET d1 = repeat(id, 200); -@@ -1185,7 +1205,7 @@ +@@ -1189,7 +1209,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -157,7 +155,7 @@ connection default; ROLLBACK; connection analyze; -@@ -1196,11 +1216,13 @@ +@@ -1200,11 +1220,13 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -173,7 +171,7 @@ SELECT * FROM t2; id c1 d1 1 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf -@@ -1245,7 +1267,9 @@ +@@ -1253,7 +1275,9 @@ info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE t3 ADD COLUMN b BLOB NOT NULL; affected rows: 0 @@ -184,7 +182,7 @@ INSERT INTO t3 SET id=4; ERROR HY000: Field 'c2' doesn't have a default value INSERT INTO t3 SET id=4, c2=0, b=0xf09f98b1; -@@ -1258,7 +1282,9 @@ +@@ -1266,7 +1290,9 @@ ALTER TABLE t3 CHANGE t phrase TEXT DEFAULT 0xc3a4c3a448, CHANGE b b BLOB NOT NULL DEFAULT 'binary line of business'; affected rows: 4 @@ -195,7 +193,7 @@ INSERT INTO t3 SET id=5, c2=9; Warnings: Note 1265 Data truncated for column 'c7' at row 1 -@@ -1272,7 +1298,9 @@ +@@ -1280,7 +1306,9 @@ 5 9 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 ääH binary line of business ALTER TABLE t3 DROP c3, DROP c7; affected rows: 0 @@ -206,7 +204,7 @@ SELECT * FROM t3; id c2 c4 c5 c6 c8 phrase b 1 1 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog -@@ -1300,6 +1328,8 @@ +@@ -1308,6 +1336,8 @@ (id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000), p POINT NOT NULL DEFAULT ST_GeomFromText('POINT(0 0)'), SPATIAL INDEX(p)) ENGINE=InnoDB ROW_FORMAT=COMPACT; @@ -215,7 +213,7 @@ BEGIN; INSERT INTO big SET id=1, c1=REPEAT('a', 200), c2=REPEAT('b', 200), c3=REPEAT('c', 159); -@@ -1318,13 +1348,15 @@ +@@ -1326,13 +1356,15 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -233,7 +231,7 @@ CHECKSUM TABLE big; Table Checksum test.big 1705165209 -@@ -1342,7 +1374,7 @@ +@@ -1350,7 +1382,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -242,7 +240,7 @@ connection default; ROLLBACK; CHECKSUM TABLE big; -@@ -1356,7 +1388,7 @@ +@@ -1364,7 +1396,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -251,7 +249,7 @@ connection default; InnoDB 0 transactions not purged DROP TABLE t1,t2,t3,t4,big; -@@ -2080,7 +2112,7 @@ +@@ -2088,7 +2120,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -260,7 +258,7 @@ connection default; ROLLBACK; connection analyze; -@@ -2091,7 +2123,7 @@ +@@ -2099,7 +2131,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -269,7 +267,7 @@ connection default; BEGIN; UPDATE t2 SET d1 = repeat(id, 200); -@@ -2103,7 +2135,7 @@ +@@ -2111,7 +2143,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -278,7 +276,7 @@ connection default; ROLLBACK; connection analyze; -@@ -2114,7 +2146,7 @@ +@@ -2122,7 +2154,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -287,7 +285,7 @@ connection default; ALTER TABLE t2 DROP p; affected rows: 0 -@@ -2236,7 +2268,7 @@ +@@ -2248,7 +2280,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -296,7 +294,7 @@ connection default; ALTER TABLE big ADD COLUMN (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde', -@@ -2260,7 +2292,7 @@ +@@ -2272,7 +2304,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -305,7 +303,7 @@ connection default; ROLLBACK; CHECKSUM TABLE big; -@@ -2274,7 +2306,7 @@ +@@ -2286,7 +2318,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -314,7 +312,7 @@ connection default; InnoDB 0 transactions not purged DROP TABLE t1,t2,t3,t4,big; -@@ -2816,7 +2848,7 @@ +@@ -2836,7 +2868,7 @@ FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; instants diff --git a/mysql-test/suite/innodb/r/instant_alter,64k.rdiff b/mysql-test/suite/innodb/r/instant_alter,64k.rdiff index d9a398fc8c8..4e49b41e93a 100644 --- a/mysql-test/suite/innodb/r/instant_alter,64k.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter,64k.rdiff @@ -1,15 +1,13 @@ ---- instant_alter.result -+++ instant_alter,64k.result -@@ -2,7 +2,7 @@ - # MDEV-11369: Instant ADD COLUMN for InnoDB - # +@@ -5,7 +5,7 @@ + SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT; + call mtr.add_suppression("Cannot add field `.*` in table `test`.`.*` because after adding it, the row size is"); CREATE TABLE t(a INT UNIQUE)ENGINE=InnoDB ROW_FORMAT=COMPACT; -ALTER TABLE t ADD e INT, ROW_FORMAT=COMPRESSED; +ALTER TABLE t ADD e INT, ROW_FORMAT=DYNAMIC; INSERT INTO t SET a=1; SET @old_instant= (SELECT variable_value FROM information_schema.global_status -@@ -33,17 +33,17 @@ +@@ -36,17 +36,17 @@ `c` int(11) NOT NULL, `d` int(11) NOT NULL, UNIQUE KEY `a` (`a`) @@ -30,7 +28,7 @@ DROP TABLE t; connect analyze, localhost, root; connection default; -@@ -374,7 +374,7 @@ +@@ -404,7 +404,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -39,7 +37,7 @@ connection default; ALTER TABLE big ADD COLUMN (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde', -@@ -397,7 +397,7 @@ +@@ -428,7 +428,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -48,7 +46,7 @@ connection default; ROLLBACK; CHECKSUM TABLE big; -@@ -410,7 +410,7 @@ +@@ -442,7 +442,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -56,8 +54,8 @@ +1 connection default; InnoDB 0 transactions not purged - DROP TABLE t1,t2,t3,big; -@@ -734,7 +734,7 @@ + DROP TABLE t1,t2,t3,t4,big; +@@ -1326,7 +1326,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -66,7 +64,7 @@ connection default; ALTER TABLE big ADD COLUMN (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde', -@@ -757,7 +757,7 @@ +@@ -1350,7 +1350,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -75,7 +73,7 @@ connection default; ROLLBACK; CHECKSUM TABLE big; -@@ -770,7 +770,7 @@ +@@ -1364,7 +1364,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -83,8 +81,8 @@ +1 connection default; InnoDB 0 transactions not purged - DROP TABLE t1,t2,t3,big; -@@ -1094,7 +1094,7 @@ + DROP TABLE t1,t2,t3,t4,big; +@@ -2248,7 +2248,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -93,7 +91,7 @@ connection default; ALTER TABLE big ADD COLUMN (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde', -@@ -1117,7 +1117,7 @@ +@@ -2272,7 +2272,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -102,7 +100,7 @@ connection default; ROLLBACK; CHECKSUM TABLE big; -@@ -1130,7 +1130,7 @@ +@@ -2286,7 +2286,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -110,4 +108,4 @@ +1 connection default; InnoDB 0 transactions not purged - DROP TABLE t1,t2,t3,big; + DROP TABLE t1,t2,t3,t4,big; diff --git a/mysql-test/suite/innodb/r/instant_alter,8k.rdiff b/mysql-test/suite/innodb/r/instant_alter,8k.rdiff index b96262866e5..3babe020c09 100644 --- a/mysql-test/suite/innodb/r/instant_alter,8k.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter,8k.rdiff @@ -1,6 +1,4 @@ ---- instant_alter.result -+++ instant_alter,8k.result -@@ -240,7 +240,7 @@ +@@ -244,7 +244,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -9,7 +7,7 @@ connection default; ROLLBACK; connection analyze; -@@ -250,7 +250,7 @@ +@@ -255,7 +255,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -18,7 +16,7 @@ connection default; BEGIN; UPDATE t2 SET d1 = repeat(id, 200); -@@ -261,7 +261,7 @@ +@@ -267,7 +267,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -27,7 +25,7 @@ connection default; ROLLBACK; connection analyze; -@@ -271,7 +271,7 @@ +@@ -278,7 +278,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -36,7 +34,7 @@ connection default; ALTER TABLE t2 DROP p; affected rows: 0 -@@ -374,7 +374,7 @@ +@@ -404,7 +404,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -45,7 +43,7 @@ connection default; ALTER TABLE big ADD COLUMN (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde', -@@ -397,7 +397,7 @@ +@@ -428,7 +428,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -54,7 +52,7 @@ connection default; ROLLBACK; CHECKSUM TABLE big; -@@ -410,7 +410,7 @@ +@@ -442,7 +442,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -62,8 +60,8 @@ +5 connection default; InnoDB 0 transactions not purged - DROP TABLE t1,t2,t3,big; -@@ -600,7 +600,7 @@ + DROP TABLE t1,t2,t3,t4,big; +@@ -1166,7 +1166,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -72,7 +70,7 @@ connection default; ROLLBACK; connection analyze; -@@ -610,7 +610,7 @@ +@@ -1177,7 +1177,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -81,7 +79,7 @@ connection default; BEGIN; UPDATE t2 SET d1 = repeat(id, 200); -@@ -621,7 +621,7 @@ +@@ -1189,7 +1189,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -90,7 +88,7 @@ connection default; ROLLBACK; connection analyze; -@@ -631,7 +631,7 @@ +@@ -1200,7 +1200,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -99,7 +97,7 @@ connection default; ALTER TABLE t2 DROP p; affected rows: 0 -@@ -734,7 +734,7 @@ +@@ -1326,7 +1326,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -108,7 +106,7 @@ connection default; ALTER TABLE big ADD COLUMN (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde', -@@ -757,7 +757,7 @@ +@@ -1350,7 +1350,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -117,7 +115,7 @@ connection default; ROLLBACK; CHECKSUM TABLE big; -@@ -770,7 +770,7 @@ +@@ -1364,7 +1364,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -125,8 +123,8 @@ +5 connection default; InnoDB 0 transactions not purged - DROP TABLE t1,t2,t3,big; -@@ -960,7 +960,7 @@ + DROP TABLE t1,t2,t3,t4,big; +@@ -2088,7 +2088,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -135,7 +133,7 @@ connection default; ROLLBACK; connection analyze; -@@ -970,7 +970,7 @@ +@@ -2099,7 +2099,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -144,7 +142,7 @@ connection default; BEGIN; UPDATE t2 SET d1 = repeat(id, 200); -@@ -981,7 +981,7 @@ +@@ -2111,7 +2111,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -153,7 +151,7 @@ connection default; ROLLBACK; connection analyze; -@@ -991,7 +991,7 @@ +@@ -2122,7 +2122,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; clust_index_size @@ -162,7 +160,7 @@ connection default; ALTER TABLE t2 DROP p; affected rows: 0 -@@ -1094,7 +1094,7 @@ +@@ -2248,7 +2248,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -171,7 +169,7 @@ connection default; ALTER TABLE big ADD COLUMN (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde', -@@ -1117,7 +1117,7 @@ +@@ -2272,7 +2272,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -180,7 +178,7 @@ connection default; ROLLBACK; CHECKSUM TABLE big; -@@ -1130,7 +1130,7 @@ +@@ -2286,7 +2286,7 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; clust_index_size @@ -188,4 +186,4 @@ +5 connection default; InnoDB 0 transactions not purged - DROP TABLE t1,t2,t3,big; + DROP TABLE t1,t2,t3,t4,big; diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test index 07601adffd9..71290c722d7 100644 --- a/mysql-test/suite/innodb/t/foreign_key.test +++ b/mysql-test/suite/innodb/t/foreign_key.test @@ -821,6 +821,29 @@ eval create table t1($fk_ref int primary key, $fk_field int, $constrs) engine in drop table t1; --enable_query_log +--echo # +--echo # MDEV-27583 InnoDB uses different constants for FK cascade +--echo # error message in SQL vs error log +--echo # + +CREATE TABLE t1 +(a INT, b INT, KEY(b), +CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE) +ENGINE=InnoDB; + +INSERT INTO t1 (a,b) VALUES +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,1),(1,0); + +--error ER_GET_ERRMSG +DELETE FROM t1 WHERE b = 1; +SHOW WARNINGS; +DROP TABLE t1; + +let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err; +let SEARCH_PATTERN= InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15.*; +-- source include/search_pattern_in_file.inc + --echo # End of 10.2 tests # MDEV-21792 Server aborts upon attempt to create foreign key on spatial field diff --git a/mysql-test/suite/innodb/t/innodb-16k.test b/mysql-test/suite/innodb/t/innodb-16k.test index c059f62385b..56109543ee4 100644 --- a/mysql-test/suite/innodb/t/innodb-16k.test +++ b/mysql-test/suite/innodb/t/innodb-16k.test @@ -4,9 +4,6 @@ --source include/have_innodb_16k.inc call mtr.add_suppression("InnoDB: Cannot add field .* in table"); -SET @save_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency; -SET GLOBAL innodb_purge_rseg_truncate_frequency=1; - let $MYSQLD_DATADIR= `select @@datadir`; --echo # Test 1) Show the page size from Information Schema @@ -14,15 +11,17 @@ SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size'; --echo # Test 4) The maximum row size is dependent upon the page size. ---echo # Redundant: 8123, Compact: 8126. ---echo # Compressed: 8126, Dynamic: 8126. +--echo # Redundant: 8123, Compact: 8126, Dynamic: 8126. --echo # Each row format has its own amount of overhead that --echo # varies depending on number of fields and other overhead. SET SESSION innodb_strict_mode = ON; +SET @save_frequency=@@GLOBAL.innodb_purge_rseg_truncate_frequency; +SET @save_level=@@GLOBAL.innodb_compression_level; +SET GLOBAL innodb_purge_rseg_truncate_frequency=1; +SET GLOBAL innodb_compression_level=1; - -# Compressed table; 7959 bytes with 40 CHAR fields +# Compressed table: compressBound() for the s390x DFLTCC instruction CREATE TABLE t1 ( c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), @@ -31,7 +30,7 @@ c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200), c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), -c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(157) +c36 char(200), c37 char(200), c38 char(196) ) ROW_FORMAT=compressed; DROP TABLE t1; @@ -450,16 +449,18 @@ drop table t1; 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))) +CREATE TABLE t1(c text, PRIMARY KEY (c(292))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512)); +DROP TABLE t1; + --source include/wait_all_purged.inc -DROP TABLE t1; +SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; +SET GLOBAL innodb_compression_level=@save_level; + DROP TABLE t1_purge, t2_purge, t3_purge, t4_purge; DROP TABLE tlong; DROP TABLE tlong2; - -SET GLOBAL innodb_purge_rseg_truncate_frequency=@save_frequency;
\ No newline at end of file diff --git a/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test index 78494910b48..e8a7d603750 100644 --- a/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test +++ b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test @@ -333,6 +333,7 @@ CREATE TABLE t1(a TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL UNIQUE) ENGINE=InnoD let $fts_aux_file= `select concat('FTS_',right(concat(repeat('0',16), lower(hex(TABLE_ID))),16),'_BEING_DELETED.ibd') FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1'`; write_file $MYSQLD_DATADIR/test/$fts_aux_file; EOF +--replace_regex /".*" from/"Resource temporarily unavailable" from/ --error ER_GET_ERRNO ALTER TABLE t1 ADD FULLTEXT(a), ALGORITHM=INPLACE; DROP TABLE t1; diff --git a/mysql-test/suite/innodb_zip/include/innodb_create_tab_indx.inc b/mysql-test/suite/innodb_zip/include/innodb_create_tab_indx.inc index 413a026265e..43f731a40b3 100644 --- a/mysql-test/suite/innodb_zip/include/innodb_create_tab_indx.inc +++ b/mysql-test/suite/innodb_zip/include/innodb_create_tab_indx.inc @@ -2,7 +2,7 @@ eval CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=$block_size; diff --git a/mysql-test/suite/innodb_zip/include/innodb_load_data.inc b/mysql-test/suite/innodb_zip/include/innodb_load_data.inc index 1bcb30131ac..88bd67ee320 100644 --- a/mysql-test/suite/innodb_zip/include/innodb_load_data.inc +++ b/mysql-test/suite/innodb_zip/include/innodb_load_data.inc @@ -1,14 +1,14 @@ --echo # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); while ($i) { diff --git a/mysql-test/suite/innodb_zip/r/bug36172.result b/mysql-test/suite/innodb_zip/r/bug36172.result deleted file mode 100644 index 727f100d064..00000000000 --- a/mysql-test/suite/innodb_zip/r/bug36172.result +++ /dev/null @@ -1,4 +0,0 @@ -set @file_per_table=@@global.innodb_file_per_table; -SET GLOBAL innodb_file_per_table=on; -SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; -SET GLOBAL innodb_file_per_table=@file_per_table; diff --git a/mysql-test/suite/innodb_zip/r/bug52745.result b/mysql-test/suite/innodb_zip/r/bug52745.result index 20605eb274d..7fb48b8ff4f 100644 --- a/mysql-test/suite/innodb_zip/r/bug52745.result +++ b/mysql-test/suite/innodb_zip/r/bug52745.result @@ -1,5 +1,3 @@ -SET GLOBAL innodb_file_per_table=on; -SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; CREATE TABLE bug52745 ( a2 int(10) unsigned DEFAULT NULL, col37 time DEFAULT NULL, @@ -37,29 +35,8 @@ 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(4) 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 +col72 datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; -Warnings: -Note 1291 Column 'col82' has duplicated value '' in ENUM -Note 1291 Column 'col82' has duplicated value '' in ENUM SET sql_mode = default; INSERT IGNORE INTO bug52745 SET col40='0000-00-00 00:00:00', @@ -81,21 +58,7 @@ 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); +col71='Elora\''; Warnings: Warning 1265 Data truncated for column 'col53' at row 1 Warning 1264 Out of range value for column 'col54' at row 1 @@ -107,9 +70,6 @@ Warning 1264 Out of range value for column 'col66' at row 1 Warning 1265 Data truncated for column 'col68' at row 1 Warning 1265 Data truncated for column 'col69' at row 1 Warning 1264 Out of range value for column 'col70' at row 1 -Warning 1264 Out of range value for column 'col78' at row 1 -Warning 1265 Data truncated for column 'col79' at row 1 -Warning 1264 Out of range value for column 'col84' at row 1 SHOW WARNINGS; Level Code Message Warning 1265 Data truncated for column 'col53' at row 1 @@ -122,8 +82,4 @@ Warning 1264 Out of range value for column 'col66' at row 1 Warning 1265 Data truncated for column 'col68' at row 1 Warning 1265 Data truncated for column 'col69' at row 1 Warning 1264 Out of range value for column 'col70' at row 1 -Warning 1264 Out of range value for column 'col78' at row 1 -Warning 1265 Data truncated for column 'col79' at row 1 -Warning 1264 Out of range value for column 'col84' at row 1 DROP TABLE bug52745; -SET GLOBAL innodb_file_per_table=1; diff --git a/mysql-test/suite/innodb_zip/r/index_large_prefix.result b/mysql-test/suite/innodb_zip/r/index_large_prefix.result index 986453851eb..19a44555e13 100644 --- a/mysql-test/suite/innodb_zip/r/index_large_prefix.result +++ b/mysql-test/suite/innodb_zip/r/index_large_prefix.result @@ -105,7 +105,7 @@ ERROR 42000: Row size too large. The maximum row size for the used table type, n show warnings; Level Code Message Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs -create index idx4 on worklog5743_1(a2(434)); +create index idx4 on worklog5743_1(a2(290)); show warnings; Level Code Message create index idx5 on worklog5743_1(a1, a2(430)); @@ -113,7 +113,7 @@ ERROR 42000: Row size too large. The maximum row size for the used table type, n show warnings; Level Code Message Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs -create index idx6 on worklog5743_1(a1, a2(428)); +create index idx6 on worklog5743_1(a1, a2(283)); show warnings; Level Code Message create index idx2 on worklog5743_2(a2(4000)); @@ -127,7 +127,7 @@ ERROR 42000: Row size too large. The maximum row size for the used table type, n show warnings; Level Code Message Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs -create index idx4 on worklog5743_2(a2(946)); +create index idx4 on worklog5743_2(a2(802)); show warnings; Level Code Message create index idx5 on worklog5743_2(a1, a2(942)); @@ -135,7 +135,7 @@ ERROR 42000: Row size too large. The maximum row size for the used table type, n show warnings; Level Code Message Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs -create index idx6 on worklog5743_2(a1, a2(940)); +create index idx6 on worklog5743_2(a1, a2(795)); show warnings; Level Code Message create index idx2 on worklog5743_4(a2(4000)); @@ -149,7 +149,7 @@ ERROR 42000: Row size too large. The maximum row size for the used table type, n show warnings; Level Code Message Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs -create index idx4 on worklog5743_4(a2(1970)); +create index idx4 on worklog5743_4(a2(1826)); show warnings; Level Code Message create index idx5 on worklog5743_4(a1, a2(1966)); @@ -157,7 +157,7 @@ ERROR 42000: Row size too large. The maximum row size for the used table type, n show warnings; Level Code Message Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs -create index idx6 on worklog5743_4(a1, a2(1964)); +create index idx6 on worklog5743_4(a1, a2(1819)); show warnings; Level Code Message create index idx2 on worklog5743_8(a2(3073)); diff --git a/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff b/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff index 6848c66b5f6..87aea75d29c 100644 --- a/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff +++ b/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff @@ -1,5 +1,3 @@ ---- page_size.result -+++ page_size,4k.reject @@ -3,7 +3,7 @@ SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size'; @@ -85,8 +83,8 @@ -c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), -c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), -c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), --c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(157) -+c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(76) +-c36 char(200), c37 char(200), c38 char(196) ++c06 char(200), c07 char(200), c08 char(176) ) ROW_FORMAT=compressed; DROP TABLE t1; CREATE TABLE t1 ( @@ -465,5 +463,5 @@ -Warning 139 Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. +Warning 139 Row size too large (> 1982). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. DROP TABLE t1; - CREATE TABLE t1(c text, PRIMARY KEY (c(438))) + CREATE TABLE t1(c text, PRIMARY KEY (c(293))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; diff --git a/mysql-test/suite/innodb_zip/r/page_size,8k.rdiff b/mysql-test/suite/innodb_zip/r/page_size,8k.rdiff index f71d5f9b37e..3f125c150c4 100644 --- a/mysql-test/suite/innodb_zip/r/page_size,8k.rdiff +++ b/mysql-test/suite/innodb_zip/r/page_size,8k.rdiff @@ -1,5 +1,3 @@ ---- page_size.result -+++ page_size,8k.reject @@ -3,7 +3,7 @@ SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size'; @@ -83,8 +81,8 @@ -c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), -c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), -c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), --c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(157) -+c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(103) +-c36 char(200), c37 char(200), c38 char(196) ++c16 char(200), c17 char(200), c18 char(182) ) ROW_FORMAT=compressed; DROP TABLE t1; CREATE TABLE t1 ( @@ -411,5 +409,5 @@ -Warning 139 Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. +Warning 139 Row size too large (> 4030). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. DROP TABLE t1; - CREATE TABLE t1(c text, PRIMARY KEY (c(438))) + CREATE TABLE t1(c text, PRIMARY KEY (c(293))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; diff --git a/mysql-test/suite/innodb_zip/r/page_size.result b/mysql-test/suite/innodb_zip/r/page_size.result index 8925fe3a8ed..ff4ec9738d4 100644 --- a/mysql-test/suite/innodb_zip/r/page_size.result +++ b/mysql-test/suite/innodb_zip/r/page_size.result @@ -101,7 +101,7 @@ c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200), c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), -c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(157) +c36 char(200), c37 char(200), c38 char(196) ) ROW_FORMAT=compressed; DROP TABLE t1; CREATE TABLE t1 ( @@ -591,7 +591,7 @@ ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; Warnings: Warning 139 Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. DROP TABLE t1; -CREATE TABLE t1(c text, PRIMARY KEY (c(438))) +CREATE TABLE t1(c text, PRIMARY KEY (c(293))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512)); DROP TABLE t1; diff --git a/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result b/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result index ee1121f9e20..88e8991dba4 100644 --- a/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result +++ b/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result @@ -324,7 +324,7 @@ col_1_binary = REPEAT("c", 255) DROP TABLE worklog5743; CREATE TABLE worklog5743_key2 ( col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , -PRIMARY KEY (col_1_varchar(948)) +PRIMARY KEY (col_1_varchar(801)) ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); INSERT INTO worklog5743_key2 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); @@ -348,7 +348,7 @@ col_1_varchar = REPEAT("c", 4000) DROP TABLE worklog5743_key2; CREATE TABLE worklog5743_key4 ( col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , -PRIMARY KEY (col_1_varchar(1964)) +PRIMARY KEY (col_1_varchar(1825)) ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); INSERT INTO worklog5743_key4 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); @@ -397,7 +397,7 @@ col_1_varchar = REPEAT("c", 4000) DROP TABLE worklog5743_key8; CREATE TABLE worklog5743_key2 ( col_1_text TEXT (4000) , col_2_text TEXT (4000) , -PRIMARY KEY (col_1_text(948)) +PRIMARY KEY (col_1_text(801)) ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); INSERT INTO worklog5743_key2 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); @@ -421,7 +421,7 @@ col_1_text = REPEAT("c", 4000) DROP TABLE worklog5743_key2; CREATE TABLE worklog5743_key4 ( col_1_text TEXT (4000) , col_2_text TEXT (4000) , -PRIMARY KEY (col_1_text(1964)) +PRIMARY KEY (col_1_text(1825)) ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); INSERT INTO worklog5743_key4 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); @@ -469,7 +469,7 @@ col_1_text = REPEAT("c", 4000) DROP TABLE worklog5743_key8; CREATE TABLE worklog5743_key2 ( col_1_blob BLOB (4000) , col_2_blob BLOB (4000) , -PRIMARY KEY (col_1_blob(948)) +PRIMARY KEY (col_1_blob(801)) ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); INSERT INTO worklog5743_key2 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); @@ -493,7 +493,7 @@ col_1_blob = REPEAT("c", 4000) DROP TABLE worklog5743_key2; CREATE TABLE worklog5743_key4 ( col_1_blob BLOB (4000) , col_2_blob BLOB (4000) , -PRIMARY KEY (col_1_blob(1964)) +PRIMARY KEY (col_1_blob(1825)) ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); INSERT INTO worklog5743_key4 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); @@ -1305,7 +1305,7 @@ col_1_text = REPEAT("c", 4000) DROP TABLE worklog5743; CREATE TABLE worklog5743 ( col_1_text TEXT (4000) , col_2_text TEXT (4000) , -PRIMARY KEY (col_1_text(948)) +PRIMARY KEY (col_1_text(801)) ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); diff --git a/mysql-test/suite/innodb_zip/r/wl6344_compress_level.result b/mysql-test/suite/innodb_zip/r/wl6344_compress_level.result index 3798d0679db..6fa87e648d7 100644 --- a/mysql-test/suite/innodb_zip/r/wl6344_compress_level.result +++ b/mysql-test/suite/innodb_zip/r/wl6344_compress_level.result @@ -4,7 +4,7 @@ SET @save_innodb_compression_level = @@global.innodb_compression_level; SET global innodb_compression_level=0; #create table with 1K block size CREATE TABLE tab5 (col_1 CHAR (255) , -col_2 VARCHAR (255), col_3 longtext, +col_2 VARCHAR (121), col_3 longtext, col_4 longtext,col_5 longtext, col_6 longtext , col_7 longtext , col_8 longtext ,col_9 longtext , @@ -55,7 +55,7 @@ COMMIT; SET global innodb_compression_level=9; #create table with 1K block size CREATE TABLE tab6 (col_1 CHAR (255) , -col_2 VARCHAR (255), col_3 longtext, +col_2 VARCHAR (121), col_3 longtext, col_4 longtext,col_5 longtext, col_6 longtext , col_7 longtext , col_8 longtext ,col_9 longtext , diff --git a/mysql-test/suite/innodb_zip/r/wl6347_comp_indx_stat.result b/mysql-test/suite/innodb_zip/r/wl6347_comp_indx_stat.result index 3b98527250b..ad1c1141a20 100644 --- a/mysql-test/suite/innodb_zip/r/wl6347_comp_indx_stat.result +++ b/mysql-test/suite/innodb_zip/r/wl6347_comp_indx_stat.result @@ -19,7 +19,7 @@ SET GLOBAL innodb_compression_level=0; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -47,15 +47,15 @@ The size of the tab5.ibd file: 65536 # for deterministic reasons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -278,9 +278,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -303,9 +300,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -334,9 +328,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table # testcase : pass = 1 fail = 0 SET @comp_val=0; @@ -365,7 +356,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=1; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -393,15 +384,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -624,9 +615,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -649,9 +637,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -680,9 +665,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table # testcase : pass = 1 fail = 0 SET @comp_val=0; @@ -711,7 +693,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=1; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -739,15 +721,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -962,7 +944,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 131072 # fetch the compressed page and check the stats =============== Fetch Records @@ -970,9 +952,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -986,7 +965,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 131072 # fetch the compressed same page once again and check the stats # the stat figures should be same as above query =============== @@ -995,9 +974,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -1011,7 +987,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 131072 DROP TABLE tab5; #****************************************************************** # Test 1-8K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 8K @@ -1024,7 +1000,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=1; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -1052,15 +1028,15 @@ The size of the tab5.ibd file: 122880 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -1275,7 +1251,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 212992 +The size of the tab5.ibd file: 180224 # fetch the compressed page and check the stats =============== Fetch Records @@ -1283,9 +1259,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -1299,7 +1272,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 212992 +The size of the tab5.ibd file: 180224 # fetch the compressed same page once again and check the stats # the stat figures should be same as above query =============== @@ -1308,9 +1281,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -1324,7 +1294,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 212992 +The size of the tab5.ibd file: 180224 DROP TABLE tab5; #****************************************************************** # Test 1-16K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 16K @@ -1337,7 +1307,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=1; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -1365,15 +1335,15 @@ The size of the tab5.ibd file: 245760 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -1588,7 +1558,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 # fetch the compressed page and check the stats =============== Fetch Records @@ -1596,9 +1566,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -1612,7 +1579,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 # fetch the compressed same page once again and check the stats # the stat figures should be same as above query =============== @@ -1621,9 +1588,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -1637,7 +1601,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 DROP TABLE tab5; #****************************************************************** # Test 2: test the interaction between wl6347 & wl6344 (2.2) @@ -1661,7 +1625,7 @@ SET GLOBAL innodb_compression_level=9; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -1690,15 +1654,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -1922,9 +1886,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -1947,9 +1908,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -1978,9 +1936,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table # testcase : pass = 1 fail = 0 SET @comp_val=0; @@ -2009,7 +1964,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=ON; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -2038,15 +1993,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -2270,9 +2225,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -2295,9 +2247,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -2326,9 +2275,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table # testcase : pass = 1 fail = 0 SET @comp_val=0; @@ -2357,7 +2303,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=ON; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -2386,15 +2332,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -2610,7 +2556,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 81920 +The size of the tab5.ibd file: 77824 # fetch the compressed page and check the stats =============== Fetch Records @@ -2618,9 +2564,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -2634,7 +2577,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 81920 +The size of the tab5.ibd file: 77824 # fetch the compressed same page once again and check the stats # the stat figures should be same as above query =============== @@ -2643,9 +2586,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -2659,7 +2599,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 81920 +The size of the tab5.ibd file: 77824 DROP TABLE tab5; #****************************************************************** # Test 2-8K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=9 with page size 8K @@ -2672,7 +2612,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=ON; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -2701,15 +2641,15 @@ The size of the tab5.ibd file: 122880 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -2924,7 +2864,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 155648 # fetch the compressed page and check the stats =============== Fetch Records @@ -2932,9 +2872,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table # The size of the file with 0 compress = 212992 SET @inl_val=1; @@ -2949,7 +2886,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 155648 # fetch the compressed same page once again and check the stats # the stat figures should be same as above query =============== @@ -2958,9 +2895,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -2974,7 +2908,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 155648 DROP TABLE tab5; #****************************************************************** # Test 2-16K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=9 with page size 16K @@ -2987,7 +2921,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=ON; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -3016,15 +2950,15 @@ The size of the tab5.ibd file: 245760 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -3239,7 +3173,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 # fetch the compressed page and check the stats =============== Fetch Records @@ -3247,9 +3181,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table # The size of the file with 0 compress = 344064 SET @inl_val=1; @@ -3264,7 +3195,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 # fetch the compressed same page once again and check the stats # the stat figures should be same as above query =============== @@ -3273,9 +3204,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -3289,7 +3217,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 DROP TABLE tab5; #****************************************************************** # Test 3: test the interaction between wl6347 & wl6344 (2.3) @@ -3312,7 +3240,7 @@ SET GLOBAL innodb_compression_level=default; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -3341,15 +3269,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -3573,9 +3501,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -3598,9 +3523,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -3628,7 +3550,7 @@ SET GLOBAL innodb_compression_level=default; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -3657,15 +3579,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -3889,9 +3811,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -3919,7 +3838,7 @@ SET GLOBAL innodb_compression_level=default; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -3948,15 +3867,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -4172,7 +4091,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 81920 +The size of the tab5.ibd file: 77824 # fetch the compressed page and check the stats =============== Fetch Records @@ -4180,9 +4099,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -4196,7 +4112,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 81920 +The size of the tab5.ibd file: 77824 DROP TABLE tab5; #****************************************************************** # Test 3-8K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=Def with page size 8K @@ -4210,7 +4126,7 @@ SET GLOBAL innodb_compression_level=default; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -4239,15 +4155,15 @@ The size of the tab5.ibd file: 122880 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -4463,7 +4379,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 155648 # fetch the compressed page and check the stats =============== Fetch Records @@ -4471,9 +4387,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -4487,7 +4400,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 155648 DROP TABLE tab5; #****************************************************************** # Test 3-16K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=Def with page size 16K @@ -4501,7 +4414,7 @@ SET GLOBAL innodb_compression_level=default; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -4530,15 +4443,15 @@ The size of the tab5.ibd file: 245760 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -4754,7 +4667,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 # fetch the compressed page and check the stats =============== Fetch Records @@ -4762,9 +4675,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -4778,7 +4688,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 DROP TABLE tab5; #****************************************************************** # Test 4: test the interaction between wl6347 & wl6344 (2.5 & 2.6) @@ -4812,7 +4722,7 @@ SELECT @@innodb_compression_level; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -4840,15 +4750,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -5071,9 +4981,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -5096,9 +5003,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -5127,9 +5031,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table # testcase : pass = 1 fail = 0 SET @comp_val=0; @@ -5158,7 +5059,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=ON; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -5186,15 +5087,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -5417,9 +5318,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -5442,9 +5340,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -5471,7 +5366,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=ON; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -5499,15 +5394,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -5722,7 +5617,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 81920 +The size of the tab5.ibd file: 77824 # fetch the compressed page and check the stats =============== Fetch Records @@ -5730,9 +5625,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -5746,7 +5638,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 81920 +The size of the tab5.ibd file: 77824 # fetch the compressed same page once again and check the stats # the stat figures should be same as above query =============== @@ -5755,9 +5647,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -5771,7 +5660,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 81920 +The size of the tab5.ibd file: 77824 DROP TABLE tab5; #****************************************************************** # Test 4-8K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=0 with page size 8K @@ -5784,7 +5673,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=ON; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -5812,15 +5701,15 @@ The size of the tab5.ibd file: 122880 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -6035,7 +5924,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 155648 # fetch the compressed page and check the stats =============== Fetch Records @@ -6043,9 +5932,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -6059,7 +5945,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 155648 # fetch the compressed same page once again and check the stats # the stat figures should be same as above query =============== @@ -6068,9 +5954,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -6084,7 +5967,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 155648 DROP TABLE tab5; #****************************************************************** # Test 4-16K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=0 with page size 16K @@ -6097,7 +5980,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=ON; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -6125,15 +6008,15 @@ The size of the tab5.ibd file: 245760 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -6348,7 +6231,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 # fetch the compressed page and check the stats =============== Fetch Records @@ -6356,9 +6239,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -6372,7 +6252,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 # fetch the compressed same page once again and check the stats # the stat figures should be same as above query =============== @@ -6381,9 +6261,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -6397,7 +6274,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 DROP TABLE tab5; #****************************************************************** # Test 5: test the interaction between wl6347 & wl6344 (2.7) @@ -6433,7 +6310,7 @@ SELECT @@innodb_compression_level; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -6461,15 +6338,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -6692,9 +6569,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -6717,9 +6591,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -6749,9 +6620,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table # testcase : pass = 1 fail = 0 SET @comp_val=0; @@ -6781,7 +6649,7 @@ SET GLOBAL innodb_compression_failure_threshold_pct=10; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -6809,15 +6677,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -7040,9 +6908,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -7065,9 +6930,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=2; # Check the stats of the table @@ -7094,7 +6956,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=ON; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -7122,15 +6984,15 @@ The size of the tab5.ibd file: 65536 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -7345,7 +7207,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 81920 +The size of the tab5.ibd file: 77824 # fetch the compressed page and check the stats =============== Fetch Records @@ -7353,9 +7215,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -7369,7 +7228,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 81920 +The size of the tab5.ibd file: 77824 # fetch the compressed same page once again and check the stats # the stat figures should be same as above query =============== @@ -7378,9 +7237,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -7394,7 +7250,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 81920 +The size of the tab5.ibd file: 77824 DROP TABLE tab5; #****************************************************************** # Test 5-8K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=10 with page size 8K @@ -7408,7 +7264,7 @@ SET GLOBAL innodb_compression_failure_threshold_pct=10; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -7436,15 +7292,15 @@ The size of the tab5.ibd file: 122880 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -7659,7 +7515,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 155648 # fetch the compressed page and check the stats =============== Fetch Records @@ -7667,9 +7523,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -7683,7 +7536,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 155648 # fetch the compressed same page once again and check the stats # the stat figures should be same as above query =============== @@ -7692,9 +7545,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -7708,7 +7558,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 163840 +The size of the tab5.ibd file: 155648 DROP TABLE tab5; #****************************************************************** # Test 5-16K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=10 with page size 16K @@ -7721,7 +7571,7 @@ SET GLOBAL innodb_cmp_per_index_enabled=ON; # Create table & Index CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB, col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT, -col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255)) +col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(67)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; CREATE INDEX idx9 ON tab5(col_9(10)); CREATE INDEX idx8 ON tab5(col_8(10)); @@ -7749,15 +7599,15 @@ The size of the tab5.ibd file: 245760 # for determintic resons simple data should be inserted. # insert some 100 records # Load the data -SET @col_1 = repeat('a', 100); -SET @col_2 = repeat('b', 100); -SET @col_3 = repeat('c', 100); -SET @col_4 = repeat('d', 100); -SET @col_5 = repeat('e', 100); -SET @col_6 = repeat('f', 100); -SET @col_7 = repeat('g', 100); -SET @col_8 = repeat('h', 100); -SET @col_9 = repeat('i', 100); +SET @col_1 = repeat('a', 67); +SET @col_2 = repeat('b', 67); +SET @col_3 = repeat('c', 67); +SET @col_4 = repeat('d', 67); +SET @col_5 = repeat('e', 67); +SET @col_6 = repeat('f', 67); +SET @col_7 = repeat('g', 67); +SET @col_8 = repeat('h', 67); +SET @col_9 = repeat('i', 67); INSERT INTO tab5 VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9); INSERT INTO tab5 @@ -7972,7 +7822,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 # fetch the compressed page and check the stats =============== Fetch Records @@ -7980,9 +7830,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -7996,7 +7843,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 # fetch the compressed same page once again and check the stats # the stat figures should be same as above query =============== @@ -8005,9 +7852,6 @@ Fetch Records SELECT col_7,col_8,col_9 FROM tab5 WHERE col_2='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' LIMIT 1; -col_7 gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg -col_8 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -col_9 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii # check the stats of the table & size of the table SET @inl_val=1; # Check the stats of the table @@ -8021,7 +7865,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 327680 +The size of the tab5.ibd file: 311296 DROP TABLE tab5; #****************************************************************** # Test 6: Create multiple tables & indexes having same name in 2 diff DB's diff --git a/mysql-test/suite/innodb_zip/t/bug36172.test b/mysql-test/suite/innodb_zip/t/bug36172.test deleted file mode 100644 index 1f6533d07bf..00000000000 --- a/mysql-test/suite/innodb_zip/t/bug36172.test +++ /dev/null @@ -1,27 +0,0 @@ ---source include/innodb_page_size_small.inc -# -# Test case for bug 36172 -# - -set @file_per_table=@@global.innodb_file_per_table; -SET GLOBAL innodb_file_per_table=on; - -SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; - -# 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 - -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; -SET sql_mode = default; -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; --- enable_query_log --- enable_result_log -SET GLOBAL innodb_file_per_table=@file_per_table; diff --git a/mysql-test/suite/innodb_zip/t/bug52745.test b/mysql-test/suite/innodb_zip/t/bug52745.test index be4b5c2bfcb..b642479f093 100644 --- a/mysql-test/suite/innodb_zip/t/bug52745.test +++ b/mysql-test/suite/innodb_zip/t/bug52745.test @@ -1,8 +1,5 @@ -- source include/innodb_page_size_small.inc -let $file_per_table=`select @@innodb_file_per_table`; -SET GLOBAL innodb_file_per_table=on; -SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; CREATE TABLE bug52745 ( a2 int(10) unsigned DEFAULT NULL, col37 time DEFAULT NULL, @@ -40,25 +37,7 @@ CREATE TABLE bug52745 ( 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(4) 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 + col72 datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; SET sql_mode = default; INSERT IGNORE INTO bug52745 SET @@ -81,24 +60,8 @@ 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); +col71='Elora\''; SHOW WARNINGS; DROP TABLE bug52745; - -EVAL SET GLOBAL innodb_file_per_table=$file_per_table; diff --git a/mysql-test/suite/innodb_zip/t/index_large_prefix.test b/mysql-test/suite/innodb_zip/t/index_large_prefix.test index 2c9da76f9e8..a254c4e61ee 100644 --- a/mysql-test/suite/innodb_zip/t/index_large_prefix.test +++ b/mysql-test/suite/innodb_zip/t/index_large_prefix.test @@ -145,14 +145,14 @@ show warnings; -- error ER_TOO_BIG_ROWSIZE create index idx3 on worklog5743_1(a2(436)); show warnings; -# Bug#13391353 Limit is one byte less on on 32bit-Linux only -create index idx4 on worklog5743_1(a2(434)); +# MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction +create index idx4 on worklog5743_1(a2(290)); show warnings; -- error ER_TOO_BIG_ROWSIZE create index idx5 on worklog5743_1(a1, a2(430)); show warnings; -# Bug#13391353 Limit is one byte less on on 32bit-Linux only -create index idx6 on worklog5743_1(a1, a2(428)); +# MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction +create index idx6 on worklog5743_1(a1, a2(283)); show warnings; # Test edge cases for indexes using key_block_size=2 @@ -162,14 +162,14 @@ show warnings; -- error ER_TOO_BIG_ROWSIZE create index idx3 on worklog5743_2(a2(948)); show warnings; -# Bug#13391353 Limit is one byte less on on 32bit-Linux only -create index idx4 on worklog5743_2(a2(946)); +# MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction +create index idx4 on worklog5743_2(a2(802)); show warnings; -- error ER_TOO_BIG_ROWSIZE create index idx5 on worklog5743_2(a1, a2(942)); show warnings; -# Bug#13391353 Limit is one byte less on on 32bit-Linux only -create index idx6 on worklog5743_2(a1, a2(940)); +# MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction +create index idx6 on worklog5743_2(a1, a2(795)); show warnings; # Test edge cases for indexes using key_block_size=4 @@ -179,14 +179,14 @@ show warnings; -- error ER_TOO_BIG_ROWSIZE create index idx3 on worklog5743_4(a2(1972)); show warnings; -# Bug#13391353 Limit is one byte less on on 32bit-Linux only -create index idx4 on worklog5743_4(a2(1970)); +# MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction +create index idx4 on worklog5743_4(a2(1826)); show warnings; -- error ER_TOO_BIG_ROWSIZE create index idx5 on worklog5743_4(a1, a2(1966)); show warnings; -# Bug#13391353 Limit is one byte less on on 32bit-Linux only -create index idx6 on worklog5743_4(a1, a2(1964)); +# MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction +create index idx6 on worklog5743_4(a1, a2(1819)); show warnings; # Test edge cases for indexes using key_block_size=8 diff --git a/mysql-test/suite/innodb_zip/t/page_size.test b/mysql-test/suite/innodb_zip/t/page_size.test index 0faf4428f62..bee2d585101 100644 --- a/mysql-test/suite/innodb_zip/t/page_size.test +++ b/mysql-test/suite/innodb_zip/t/page_size.test @@ -86,11 +86,11 @@ c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(156) ) ROW_FORMAT=compact; -# Compressed table; 1878 bytes with 10 CHAR fields -# Bug#13391353 Limit is 1876 on 32-Linux only +# Compressed table +# MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction CREATE TABLE t1 ( c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), -c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(76) +c06 char(200), c07 char(200), c08 char(176) ) ROW_FORMAT=compressed; DROP TABLE t1; --replace_regex /> [0-9]*/> max_row_size/ @@ -186,13 +186,13 @@ c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200), c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(203) ) ROW_FORMAT=compact; -# Compressed table; 3905 bytes with 20 CHAR fields -# Bug#13391353 Limit is 3903 on 32-Linux only +# Compressed table +# MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction CREATE TABLE t1 ( c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200), -c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(103) +c16 char(200), c17 char(200), c18 char(182) ) ROW_FORMAT=compressed; DROP TABLE t1; --replace_regex /> [0-9]*/> max_row_size/ @@ -308,8 +308,8 @@ c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(247) ) ROW_FORMAT=compact; -# Compressed table; 7959 bytes with 40 CHAR fields -# Bug#13391353 Limit is 7957 on 32-Linux only +# Compressed table +# MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction CREATE TABLE t1 ( c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), @@ -318,7 +318,7 @@ c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200), c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), -c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(157) +c36 char(200), c37 char(200), c38 char(196) ) ROW_FORMAT=compressed; DROP TABLE t1; --error ER_TOO_BIG_ROWSIZE @@ -868,7 +868,9 @@ DROP TABLE t1; CREATE TABLE t1(c text, PRIMARY KEY (c(440))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; DROP TABLE t1; -CREATE TABLE t1(c text, PRIMARY KEY (c(438))) +--disable_warnings +CREATE TABLE t1(c text, PRIMARY KEY (c(293))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; +--enable_warnings INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512)); DROP TABLE t1; diff --git a/mysql-test/suite/innodb_zip/t/prefix_index_liftedlimit.test b/mysql-test/suite/innodb_zip/t/prefix_index_liftedlimit.test index a020496f755..32d7eacd29f 100644 --- a/mysql-test/suite/innodb_zip/t/prefix_index_liftedlimit.test +++ b/mysql-test/suite/innodb_zip/t/prefix_index_liftedlimit.test @@ -297,10 +297,9 @@ 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)) +PRIMARY KEY (col_1_varchar(801)) ) 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)); @@ -316,10 +315,9 @@ 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)) +PRIMARY KEY (col_1_varchar(1825)) ) 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)); @@ -358,10 +356,9 @@ 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)) +PRIMARY KEY (col_1_text(801)) ) 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)); @@ -377,10 +374,9 @@ 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)) +PRIMARY KEY (col_1_text(1825)) ) 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)); @@ -418,10 +414,9 @@ 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)) +PRIMARY KEY (col_1_blob(801)) ) 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)); @@ -437,10 +432,9 @@ 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)) +PRIMARY KEY (col_1_blob(1825)) ) 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)); @@ -1200,7 +1194,7 @@ DROP TABLE worklog5743; # 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)) +PRIMARY KEY (col_1_text(801)) ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); # Create index of 767 diff --git a/mysql-test/suite/innodb_zip/t/wl6344_compress_level.test b/mysql-test/suite/innodb_zip/t/wl6344_compress_level.test index f0ddb35aa4a..d44dbfd73b0 100644 --- a/mysql-test/suite/innodb_zip/t/wl6344_compress_level.test +++ b/mysql-test/suite/innodb_zip/t/wl6344_compress_level.test @@ -18,7 +18,7 @@ SET global innodb_compression_level=0; -- echo #create table with 1K block size CREATE TABLE tab5 (col_1 CHAR (255) , -col_2 VARCHAR (255), col_3 longtext, +col_2 VARCHAR (121), col_3 longtext, col_4 longtext,col_5 longtext, col_6 longtext , col_7 longtext , col_8 longtext ,col_9 longtext , @@ -62,7 +62,7 @@ SET global innodb_compression_level=9; -- echo #create table with 1K block size CREATE TABLE tab6 (col_1 CHAR (255) , -col_2 VARCHAR (255), col_3 longtext, +col_2 VARCHAR (121), col_3 longtext, col_4 longtext,col_5 longtext, col_6 longtext , col_7 longtext , col_8 longtext ,col_9 longtext , diff --git a/mysql-test/suite/maria/repair.result b/mysql-test/suite/maria/repair.result index 722d9f28712..458b5503602 100644 --- a/mysql-test/suite/maria/repair.result +++ b/mysql-test/suite/maria/repair.result @@ -31,3 +31,34 @@ SET max_session_mem_used=50000; REPAIR LOCAL TABLE t1 USE_FRM; REPAIR LOCAL TABLE t1; DROP TABLE t1; +SET max_session_mem_used=default; + +# MDEV-17223 Assertion `thd->killed != 0' failed in +# ha_maria::enable_indexes +# +CREATE OR REPLACE TABLE t1 (c VARCHAR(1024) NOT NULL) ENGINE=Aria ROW_FORMAT FIXED; +insert into t1 select char(seq) from seq_65_to_256; +insert into t1 values ("a"); +ALTER TABLE t1 ADD PRIMARY KEY(c(67)); +ERROR 23000: Duplicate entry 'a' for key 'PRIMARY' +select count(*) from t1; +count(*) +193 +drop table t1; + +# MDEV-17223 Assertion `thd->killed != 0' failed in +# ha_maria::enable_indexes +# +SET SESSION aria_sort_buffer_size=1023; +Warnings: +Warning 1292 Truncated incorrect aria_sort_buffer_size value: '1023' +CREATE TABLE t2 (c TEXT,INDEX(c(1000))) ENGINE=Aria; +INSERT INTO t2 select char(seq) from seq_65_to_255; +SELECT COUNT(*) FROM t2; +COUNT(*) +191 +DROP TABLE t2; +SET SESSION aria_sort_buffer_size=default; +# +# End of 10.3 tests +# diff --git a/mysql-test/suite/maria/repair.test b/mysql-test/suite/maria/repair.test index 571f861c512..688ac076e1d 100644 --- a/mysql-test/suite/maria/repair.test +++ b/mysql-test/suite/maria/repair.test @@ -3,6 +3,7 @@ # as memory usage is different compared to normal server. --source include/not_embedded.inc +--source include/have_sequence.inc # # MDEV-11539 test_if_reopen: Assertion `strcmp(share->unique_file_name,filename) || share->last_version' failed upon select from I_S @@ -41,3 +42,35 @@ REPAIR LOCAL TABLE t1 USE_FRM; REPAIR LOCAL TABLE t1; --enable_result_log DROP TABLE t1; +SET max_session_mem_used=default; + +--echo +--echo # MDEV-17223 Assertion `thd->killed != 0' failed in +--echo # ha_maria::enable_indexes +--echo # + +CREATE OR REPLACE TABLE t1 (c VARCHAR(1024) NOT NULL) ENGINE=Aria ROW_FORMAT FIXED; +insert into t1 select char(seq) from seq_65_to_256; +insert into t1 values ("a"); +--error ER_DUP_ENTRY +ALTER TABLE t1 ADD PRIMARY KEY(c(67)); +select count(*) from t1; +drop table t1; + +--echo +--echo # MDEV-17223 Assertion `thd->killed != 0' failed in +--echo # ha_maria::enable_indexes +--echo # + +SET SESSION aria_sort_buffer_size=1023; +CREATE TABLE t2 (c TEXT,INDEX(c(1000))) ENGINE=Aria; +--disable_warnings +INSERT INTO t2 select char(seq) from seq_65_to_255; +--enable_warnings +SELECT COUNT(*) FROM t2; +DROP TABLE t2; +SET SESSION aria_sort_buffer_size=default; + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/suite/rpl/r/rpl_seconds_behind_master_spike.result b/mysql-test/suite/rpl/r/rpl_seconds_behind_master_spike.result index be18f95c2c0..4eeb863bb40 100644 --- a/mysql-test/suite/rpl/r/rpl_seconds_behind_master_spike.result +++ b/mysql-test/suite/rpl/r/rpl_seconds_behind_master_spike.result @@ -29,9 +29,10 @@ count(*)=1 # not considered in Seconds_Behind_Master calculation connection slave1; # Safely resume slave SQL thread -SET @@global.debug_dbug=''; -SET DEBUG_SYNC='pause_sql_thread_on_fde CLEAR'; +# Prove SQL thread is in state "debug sync point: now" +SET @@global.debug_dbug="-d,pause_sql_thread_on_fde"; SET DEBUG_SYNC='now SIGNAL sql_thread_continue'; +# Wait for SQL thread to continue into normal execution SET DEBUG_SYNC='RESET'; connection master; DROP TABLE t1; diff --git a/mysql-test/suite/rpl/t/rpl_seconds_behind_master_spike.test b/mysql-test/suite/rpl/t/rpl_seconds_behind_master_spike.test index 029625a09ad..31a9478f632 100644 --- a/mysql-test/suite/rpl/t/rpl_seconds_behind_master_spike.test +++ b/mysql-test/suite/rpl/t/rpl_seconds_behind_master_spike.test @@ -71,10 +71,24 @@ if(`select $sbm > $t_now - $t_master_events_logged + 1`) } --echo # Safely resume slave SQL thread -SET @@global.debug_dbug=''; -SET DEBUG_SYNC='pause_sql_thread_on_fde CLEAR'; + +--let $dbug_wait_state="debug sync point: now" +--echo # Prove SQL thread is in state $dbug_wait_state +--let $wait_condition= SELECT STATE=$dbug_wait_state from information_schema.PROCESSLIST where COMMAND="Slave_SQL" +--source include/wait_condition.inc + +SET @@global.debug_dbug="-d,pause_sql_thread_on_fde"; SET DEBUG_SYNC='now SIGNAL sql_thread_continue'; +# We have to wait for the SQL thread to acknowledge the sql_thread_continue +# signal. Otherwise the below RESET command can overwrite the signal before +# the SQL thread is notified to proceed, causing it to "permanently" become +# stuck awaiting the signal (until timeout is reached). + +--echo # Wait for SQL thread to continue into normal execution +--let $wait_condition= SELECT STATE!= $dbug_wait_state from information_schema.PROCESSLIST where COMMAND="Slave_SQL" +--source include/wait_condition.inc + # Reset last sql_thread_continue signal SET DEBUG_SYNC='RESET'; diff --git a/sql/wsrep_mysqld.cc b/sql/wsrep_mysqld.cc index 32b58b64930..6c93d89afa1 100644 --- a/sql/wsrep_mysqld.cc +++ b/sql/wsrep_mysqld.cc @@ -329,7 +329,6 @@ void WSREP_LOG(void (*fun)(const char* fmt, ...), const char* fmt, ...) wsrep_uuid_t local_uuid = WSREP_UUID_UNDEFINED; wsrep_seqno_t local_seqno = WSREP_SEQNO_UNDEFINED; -wsp::node_status local_status; /* */ diff --git a/sql/wsrep_utils.h b/sql/wsrep_utils.h index 974c623521e..88c5770d3fe 100644 --- a/sql/wsrep_utils.h +++ b/sql/wsrep_utils.h @@ -21,27 +21,6 @@ unsigned int wsrep_check_ip (const char* const addr, bool *is_ipv6); size_t wsrep_guess_ip (char* buf, size_t buf_len); -namespace wsp { -class node_status -{ -public: - node_status() : status(wsrep::server_state::s_disconnected) {} - void set(enum wsrep::server_state::state new_status, - const wsrep::view* view= 0) - { - if (status != new_status || 0 != view) - { - wsrep_notify_status(new_status, view); - status= new_status; - } - } - enum wsrep::server_state::state get() const { return status; } -private: - enum wsrep::server_state::state status; -}; -} /* namespace wsp */ - -extern wsp::node_status local_status; /* returns the length of the host part of the address string */ size_t wsrep_host_len(const char* addr, size_t addr_len); diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 0c0a16c626b..7a28e98472a 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -2149,7 +2149,7 @@ convert_error_code_to_mysql( "constraints that exceed max " "depth of %d. Please " "drop extra constraints and try " - "again", DICT_FK_MAX_RECURSIVE_LOAD); + "again", FK_MAX_CASCADE_DEL); return(HA_ERR_FK_DEPTH_EXCEEDED); case DB_CANT_CREATE_GEOMETRY_OBJECT: @@ -13072,7 +13072,6 @@ bool create_table_info_t::row_size_is_acceptable( if (info.row_is_too_big()) { ut_ad(info.get_overrun_size() != 0); - ut_ad(info.max_leaf_size != 0); const size_t idx= info.get_first_overrun_field_index(); const dict_field_t *field= dict_index_get_nth_field(&index, idx); diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc index 1764f76c3d5..c180365f9e4 100644 --- a/storage/maria/ha_maria.cc +++ b/storage/maria/ha_maria.cc @@ -2072,13 +2072,19 @@ int ha_maria::enable_indexes(uint mode) param->orig_sort_buffer_length= THDVAR(thd,sort_buffer_size); param->stats_method= (enum_handler_stats_method)THDVAR(thd,stats_method); param->tmpdir= &mysql_tmpdir_list; - if ((error= (repair(thd, param, 0) != HA_ADMIN_OK)) && param->retry_repair) + + /* + Don't retry repair if we get duplicate key error if + create_unique_index_by_sort is enabled + This can be set when doing an ALTER TABLE and enabling unique keys + */ + if ((error= (repair(thd, param, 0) != HA_ADMIN_OK)) && param->retry_repair && + (my_errno != HA_ERR_FOUND_DUPP_KEY || + !file->create_unique_index_by_sort)) { sql_print_warning("Warning: Enabling keys got errno %d on %s.%s, " "retrying", my_errno, param->db_name, param->table_name); - /* This should never fail normally */ - DBUG_ASSERT(thd->killed != 0); /* Repairing by sort failed. Now try standard repair method. */ param->testflag &= ~T_REP_BY_SORT; file->state->records= start_rows; diff --git a/support-files/policy/selinux/mariadb-server.te b/support-files/policy/selinux/mariadb-server.te index 851f8add61c..89846063506 100644 --- a/support-files/policy/selinux/mariadb-server.te +++ b/support-files/policy/selinux/mariadb-server.te @@ -21,6 +21,7 @@ require { type shell_exec_t; type anon_inodefs_t; type fixed_disk_device_t; + type usermodehelper_t; class lnk_file read; class process { getattr signull }; class unix_stream_socket connectto; |