diff options
Diffstat (limited to 'mysql-test/suite')
24 files changed, 899 insertions, 195 deletions
diff --git a/mysql-test/suite/galera/include/galera_dump_sr_table.inc b/mysql-test/suite/galera/include/galera_dump_sr_table.inc index 48a55006eae..1e6ff5548d2 100644 --- a/mysql-test/suite/galera/include/galera_dump_sr_table.inc +++ b/mysql-test/suite/galera/include/galera_dump_sr_table.inc @@ -11,17 +11,17 @@ while ($seqno <= $sr_max) --let $sr_fragment_file = $MYSQLTEST_VARDIR/tmp/sr_fragment.log --exec rm -rf $sr_fragment_file --disable_query_log - --eval SELECT frag FROM mysql.wsrep_streaming_log WHERE seqno = $seqno INTO DUMPFILE '$sr_fragment_file' + --eval SELECT frag INTO DUMPFILE '$sr_fragment_file' FROM mysql.wsrep_streaming_log WHERE seqno = $seqno --enable_query_log --let $sr_binlog_file = $MYSQLTEST_VARDIR/tmp/sr_binlog.log --exec rm -rf $sr_binlog_file - --exec cp std_data/binlog-header.log $sr_binlog_file + --exec cp std_data/binlog-header.binlog $sr_binlog_file --exec cat $sr_fragment_file >> $sr_binlog_file --replace_regex /SET TIMESTAMP=[0-9]+/SET TIMESTAMP=<TIMESTAMP>/ /#[0-9]+ +[0-9]+:[0-9]+:[0-9]+/<ISO TIMESTAMP>/ /pseudo_thread_id=[0-9]+/pseudo_thread_id=<PSEUDO_THREAD_ID>/ /thread_id=[0-9]+/thread_id=<QUERY_THREAD_ID>/ /table id [0-9]+/table id <TABLE_ID>/ /mapped to number [0-9]+/mapped to number <TABLE_ID>/ /auto_increment_increment=[0-9]+/auto_increment_increment=<AUTO_INCREMENT_INCREMENT>/ /auto_increment_offset=[0-9]+/auto_increment_offset=<AUTO_INCREMENT_OFFSET>/ /exec_time=[0-9]+/exec_time=<EXEC_TIME>/ - --exec $MYSQL_BINLOG --skip-gtids $sr_binlog_file --base64-output=decode-rows --start-position=120 | grep -v 'SET @' 2>&1 + --exec $MYSQL_BINLOG $sr_binlog_file --base64-output=decode-rows --start-position=256 --skip-annotate-row-events | grep -v 'SET @' 2>&1 --inc $seqno } diff --git a/mysql-test/suite/galera/r/galera_last_committed_id.result b/mysql-test/suite/galera/r/galera_last_committed_id.result new file mode 100644 index 00000000000..fabc5337576 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_last_committed_id.result @@ -0,0 +1,38 @@ +connection node_2; +connection node_1; +SELECT WSREP_LAST_WRITTEN_GTID() = '00000000-0000-0000-0000-000000000000:-1'; +WSREP_LAST_WRITTEN_GTID() = '00000000-0000-0000-0000-000000000000:-1' +1 +wsrep_last_committed_id_match +1 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +connection node_1a; +CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +connection node_1; +SELECT WSREP_LAST_WRITTEN_GTID() = '00000000-0000-0000-0000-000000000000:-1'; +WSREP_LAST_WRITTEN_GTID() = '00000000-0000-0000-0000-000000000000:-1' +1 +connection node_1a; +INSERT INTO t1 VALUES (1); +connection node_1; +wsrep_last_committed_id_match +1 +SET AUTOCOMMIT=OFF; +START TRANSACTION; +SELECT WSREP_LAST_WRITTEN_GTID() = '00000000-0000-0000-0000-000000000000:-1'; +WSREP_LAST_WRITTEN_GTID() = '00000000-0000-0000-0000-000000000000:-1' +1 +INSERT INTO t1 VALUES (1); +SELECT WSREP_LAST_WRITTEN_GTID() = '00000000-0000-0000-0000-000000000000:-1'; +WSREP_LAST_WRITTEN_GTID() = '00000000-0000-0000-0000-000000000000:-1' +1 +wsrep_last_committed_id_match +1 +COMMIT; +wsrep_last_committed_id_advanced +1 +wsrep_last_committed_id_advanced +1 +SET AUTOCOMMIT=ON; +DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/galera_sync_wait_upto.result b/mysql-test/suite/galera/r/galera_sync_wait_upto.result new file mode 100644 index 00000000000..7d691e105da --- /dev/null +++ b/mysql-test/suite/galera/r/galera_sync_wait_upto.result @@ -0,0 +1,43 @@ +connection node_2; +connection node_1; +CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +SELECT WSREP_SYNC_WAIT_UPTO_GTID(NULL); +ERROR HY000: Incorrect arguments to wsrep_sync_wait_upto_gtid +SELECT WSREP_SYNC_WAIT_UPTO_GTID('a'); +ERROR HY000: Incorrect arguments to wsrep_sync_wait_upto_gtid +SELECT WSREP_SYNC_WAIT_UPTO_GTID(2); +ERROR HY000: Incorrect arguments to wsrep_sync_wait_upto_gtid +WSREP_SYNC_WAIT_UPTO +1 +WSREP_SYNC_WAIT_UPTO +1 +WSREP_SYNC_WAIT_UPTO +1 +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection node_2; +SET GLOBAL DEBUG_DBUG = "d,sync.wsrep_apply_cb"; +connection node_1; +INSERT INTO t1 VALUES (2); +INSERT INTO t1 VALUES (3); +connection node_2; +SET SESSION wsrep_sync_wait = 0; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2; +connection node_2a; +SET SESSION wsrep_sync_wait = 0; +SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_cb"; +connection node_2; +WSREP_SYNC_WAIT_UPTO +1 +gtid_current = gtid_first +1 +SET GLOBAL DEBUG_DBUG = ""; +SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_cb"; +WSREP_SYNC_WAIT_UPTO +1 +seqno_current = seqno_second +1 +SET DEBUG_SYNC = "RESET"; +connection node_1; +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_last_committed_id.test b/mysql-test/suite/galera/t/galera_last_committed_id.test new file mode 100644 index 00000000000..550838cdcd9 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_last_committed_id.test @@ -0,0 +1,68 @@ +# +# Tests functions WSREP_LAST_WRITTEN_GTID and WSREP_LAST_SEEN_GTID +# + +--source include/galera_cluster.inc + +# Returns -1 if no transactions have been run + +SELECT WSREP_LAST_WRITTEN_GTID() = '00000000-0000-0000-0000-000000000000:-1'; + +--disable_query_log +--let $seqno = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_last_committed'` +--let $state = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_state_uuid'` +--eval SELECT WSREP_LAST_SEEN_GTID() = '$state:$seqno' AS wsrep_last_committed_id_match; +--enable_query_log + +# WSREP_LAST_WRITTEN_GTID() should not be influenced by transactions committed +# on other connections + +--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 +--connection node_1a +CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); + +--connection node_1 +SELECT WSREP_LAST_WRITTEN_GTID() = '00000000-0000-0000-0000-000000000000:-1'; + +# WSREP_LAST_SEEN_GTID() should be influenced by transactions committed +# on other connections + +--connection node_1a +INSERT INTO t1 VALUES (1); +--disable_query_log +--let $wsrep_last_committed_id_conn_1a = `SELECT WSREP_LAST_SEEN_GTID()` +--enable_query_log + +--connection node_1 +--disable_query_log +--eval SELECT WSREP_LAST_SEEN_GTID() = '$wsrep_last_committed_id_conn_1a' AS wsrep_last_committed_id_match; +--enable_query_log + +# Should not advance while a transaction is in progress + +SET AUTOCOMMIT=OFF; +START TRANSACTION; +SELECT WSREP_LAST_WRITTEN_GTID() = '00000000-0000-0000-0000-000000000000:-1'; + +--disable_query_log +--let $wsrep_last_committed_id_before = `SELECT WSREP_LAST_SEEN_GTID()` +--enable_query_log +INSERT INTO t1 VALUES (1); +SELECT WSREP_LAST_WRITTEN_GTID() = '00000000-0000-0000-0000-000000000000:-1'; +--disable_query_log +--eval SELECT WSREP_LAST_SEEN_GTID() = '$wsrep_last_committed_id_before' AS wsrep_last_committed_id_match; +--enable_query_log + +# Should only advance after the transaction has been committed + +COMMIT; +--disable_query_log +--let $seqno = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_last_committed'` +--let $state = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_state_uuid'` +--eval SELECT WSREP_LAST_WRITTEN_GTID() = '$state:$seqno' AS wsrep_last_committed_id_advanced; +--eval SELECT WSREP_LAST_SEEN_GTID() = '$state:$seqno' AS wsrep_last_committed_id_advanced; +--enable_query_log +SET AUTOCOMMIT=ON; + +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_sync_wait_upto-master.opt b/mysql-test/suite/galera/t/galera_sync_wait_upto-master.opt new file mode 100644 index 00000000000..0b35236062b --- /dev/null +++ b/mysql-test/suite/galera/t/galera_sync_wait_upto-master.opt @@ -0,0 +1 @@ +--wsrep-sync-wait=0 --wsrep-causal-reads=OFF
\ No newline at end of file diff --git a/mysql-test/suite/galera/t/galera_sync_wait_upto.test b/mysql-test/suite/galera/t/galera_sync_wait_upto.test new file mode 100644 index 00000000000..32c6b590c84 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_sync_wait_upto.test @@ -0,0 +1,115 @@ +# +# Tests the wsrep_sync_wait_upto variable. +# + +--source include/galera_cluster.inc +--source include/have_debug_sync.inc + +CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); + + +# Test with invalid values + +--error ER_WRONG_ARGUMENTS +SELECT WSREP_SYNC_WAIT_UPTO_GTID(NULL); + +--error ER_WRONG_ARGUMENTS +SELECT WSREP_SYNC_WAIT_UPTO_GTID('a'); + +--error ER_WRONG_ARGUMENTS +SELECT WSREP_SYNC_WAIT_UPTO_GTID(2); + + +# If set to low value, expect no waiting + +--disable_query_log +--let $seqno = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_last_committed'` +--let $state = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_state_uuid'` +--enable_query_log + +--disable_query_log +--eval SELECT WSREP_SYNC_WAIT_UPTO_GTID('00000000-0000-0000-0000-000000000000:-1') AS WSREP_SYNC_WAIT_UPTO; +--enable_query_log + +--disable_query_log +--eval SELECT WSREP_SYNC_WAIT_UPTO_GTID('$state:0') AS WSREP_SYNC_WAIT_UPTO; +--enable_query_log + + +# If set to current last_committed value + +--disable_query_log +--eval SELECT WSREP_SYNC_WAIT_UPTO_GTID('$state:$seqno') AS WSREP_SYNC_WAIT_UPTO; +--enable_query_log + + +# If set to very high value, will wait + +--disable_query_log +--error ER_LOCK_WAIT_TIMEOUT +--eval SELECT WSREP_SYNC_WAIT_UPTO_GTID('$state:9223372036854775807', 1) AS WSREP_SYNC_WAIT_UPTO; +--enable_query_log + + +# If applier is blocked, will wait + +--connection node_2 +SET GLOBAL DEBUG_DBUG = "d,sync.wsrep_apply_cb"; + + +--connection node_1 +# Perform two inserts and record the IDs of each +INSERT INTO t1 VALUES (2); +--let $gtid_first = `SELECT WSREP_LAST_WRITTEN_GTID()` + +INSERT INTO t1 VALUES (3); +--let $gtid_second = `SELECT WSREP_LAST_WRITTEN_GTID()` + +--connection node_2 +SET SESSION wsrep_sync_wait = 0; + +--disable_query_log +--error ER_LOCK_WAIT_TIMEOUT +--eval SELECT WSREP_SYNC_WAIT_UPTO_GTID('$gtid_first', 1) AS WSREP_SYNC_WAIT_UPTO; +--enable_query_log + +--disable_query_log +--send_eval SELECT WSREP_SYNC_WAIT_UPTO_GTID('$gtid_first') AS WSREP_SYNC_WAIT_UPTO; +--enable_query_log + +# Unblock applier +--connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2 +--connection node_2a +SET SESSION wsrep_sync_wait = 0; +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO LIKE 'SELECT WSREP_SYNC_WAIT%'; +--source include/wait_condition.inc +SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_cb"; + +--connection node_2 +--reap + +# Confirm that we were allowed to proceed when the applier reached $seqno_first +--let $gtid_current = `SELECT WSREP_LAST_SEEN_GTID()` +--disable_query_log +--eval SELECT '$gtid_current' = '$gtid_first' AS `gtid_current = gtid_first` +--enable_query_log + +SET GLOBAL DEBUG_DBUG = ""; +SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_cb"; + +# Move forward some more, to $seqno_second; + +--disable_query_log +--eval SELECT WSREP_SYNC_WAIT_UPTO_GTID('$gtid_second') AS WSREP_SYNC_WAIT_UPTO; +--enable_query_log + +--let $gtid_current = `SELECT WSREP_LAST_SEEN_GTID()` +--disable_query_log +--eval SELECT '$gtid_current' = '$gtid_second' AS `seqno_current = seqno_second` +--enable_query_log + +SET DEBUG_SYNC = "RESET"; + +--connection node_1 +DROP TABLE t1; diff --git a/mysql-test/suite/galera_sr/r/MDEV-18585.result b/mysql-test/suite/galera_sr/r/MDEV-18585.result new file mode 100644 index 00000000000..d30def1ea63 --- /dev/null +++ b/mysql-test/suite/galera_sr/r/MDEV-18585.result @@ -0,0 +1,36 @@ +connection node_2; +connection node_1; +CREATE TABLE t1 (f1 INT PRIMARY KEY); +SET SESSION wsrep_trx_fragment_unit='ROWS'; +SET SESSION wsrep_trx_fragment_size=1; +INSERT INTO t1 VALUES (1), (2); +SET SESSION wsrep_trx_fragment_unit='BYTES'; +SET SESSION wsrep_trx_fragment_size=1; +INSERT INTO t1 VALUES (3), (4); +SET SESSION wsrep_trx_fragment_unit='STATEMENTS'; +SET SESSION wsrep_trx_fragment_size=1; +INSERT INTO t1 VALUES (5), (6); +SET SESSION wsrep_trx_fragment_unit=default; +SET SESSION wsrep_trx_fragment_size=default; +SHOW BINLOG EVENTS IN 'mysqld-bin.000002' FROM 518; +Log_name Pos Event_type Server_id End_log_pos Info +mysqld-bin.000002 518 Gtid 1 560 BEGIN GTID 0-1-2 +mysqld-bin.000002 560 Annotate_rows 1 613 INSERT INTO t1 VALUES (1), (2) +mysqld-bin.000002 613 Table_map 1 658 table_id: # (test.t1) +mysqld-bin.000002 658 Write_rows_v1 1 696 table_id: # flags: STMT_END_F +mysqld-bin.000002 696 Table_map 1 741 table_id: # (test.t1) +mysqld-bin.000002 741 Write_rows_v1 1 779 table_id: # flags: STMT_END_F +mysqld-bin.000002 779 Xid 1 810 COMMIT /* xid=# */ +mysqld-bin.000002 810 Gtid 1 852 BEGIN GTID 0-1-3 +mysqld-bin.000002 852 Annotate_rows 1 905 INSERT INTO t1 VALUES (3), (4) +mysqld-bin.000002 905 Table_map 1 950 table_id: # (test.t1) +mysqld-bin.000002 950 Write_rows_v1 1 988 table_id: # flags: STMT_END_F +mysqld-bin.000002 988 Table_map 1 1033 table_id: # (test.t1) +mysqld-bin.000002 1033 Write_rows_v1 1 1071 table_id: # flags: STMT_END_F +mysqld-bin.000002 1071 Xid 1 1102 COMMIT /* xid=# */ +mysqld-bin.000002 1102 Gtid 1 1144 BEGIN GTID 0-1-4 +mysqld-bin.000002 1144 Annotate_rows 1 1197 INSERT INTO t1 VALUES (5), (6) +mysqld-bin.000002 1197 Table_map 1 1242 table_id: # (test.t1) +mysqld-bin.000002 1242 Write_rows_v1 1 1285 table_id: # flags: STMT_END_F +mysqld-bin.000002 1285 Xid 1 1316 COMMIT /* xid=# */ +DROP TABLE t1; diff --git a/mysql-test/suite/galera_sr/r/galera_sr_create_drop.result b/mysql-test/suite/galera_sr/r/galera_sr_create_drop.result new file mode 100644 index 00000000000..c8658f09ff0 --- /dev/null +++ b/mysql-test/suite/galera_sr/r/galera_sr_create_drop.result @@ -0,0 +1,28 @@ +connection node_2; +connection node_1; +SET SESSION wsrep_trx_fragment_size=1; +CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB; +connection node_2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) NOT NULL, + PRIMARY KEY (`f1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +connection node_1; +DROP TABLE t1; +connection node_2; +SHOW CREATE TABLE t1; +ERROR 42S02: Table 'test.t1' doesn't exist +CREATE DATABASE mdev_18587; +connection node_2; +SHOW DATABASES LIKE 'mdev_18587'; +Database (mdev_18587) +mdev_18587 +connection node_1; +DROP DATABASE mdev_18587; +connection node_2; +SHOW DATABASES LIKE 'mdev_18587'; +Database (mdev_18587) +connection node_1; +SET SESSION wsrep_trx_fragment_size=DEFAULT; diff --git a/mysql-test/suite/galera_sr/r/galera_sr_table_contents.result b/mysql-test/suite/galera_sr/r/galera_sr_table_contents.result index 29bb71704e2..006a1a9da96 100644 --- a/mysql-test/suite/galera_sr/r/galera_sr_table_contents.result +++ b/mysql-test/suite/galera_sr/r/galera_sr_table_contents.result @@ -1,3 +1,5 @@ +connection node_2; +connection node_1; CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2),(3); CREATE TABLE t2 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; @@ -9,16 +11,12 @@ START TRANSACTION; Start of Simple Insert INSERT INTO t1 VALUES (4); DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 73 Query thread_id=<QUERY_THREAD_ID> exec_time=<EXEC_TIME> error_code=0 -SET TIMESTAMP=<TIMESTAMP>/*!*/; -/*!\C latin1 *//*!*/; -BEGIN -/*!*/; -# at 193 -<ISO TIMESTAMP> server id 1 end_log_pos 114 Table_map: `test`.`t1` mapped to number <TABLE_ID> -# at 234 -<ISO TIMESTAMP> server id 1 end_log_pos 150 Write_rows: table id <TABLE_ID> flags: STMT_END_F +# at 256 +# at 300 +<ISO TIMESTAMP> server id 1 end_log_pos 85 CRC32 0x00000301 Table_map: `test`.`t1` mapped to number <TABLE_ID> +# at 341 +<ISO TIMESTAMP> server id 1 end_log_pos 119 CRC32 0x00000004 Write_rows: table id <TABLE_ID> flags: STMT_END_F +# Number of rows: 1 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; @@ -29,34 +27,34 @@ ROLLBACK; Start of Multi-row Update UPDATE t1 SET f1 = f1 + 10; DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 73 Query thread_id=<QUERY_THREAD_ID> exec_time=<EXEC_TIME> error_code=0 -SET TIMESTAMP=<TIMESTAMP>/*!*/; -/*!\C latin1 *//*!*/; -BEGIN -/*!*/; -# at 193 -<ISO TIMESTAMP> server id 1 end_log_pos 114 Table_map: `test`.`t1` mapped to number <TABLE_ID> -# at 234 -<ISO TIMESTAMP> server id 1 end_log_pos 156 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# at 256 +# at 301 +<ISO TIMESTAMP> server id 1 end_log_pos 86 CRC32 0x00000301 Table_map: `test`.`t1` mapped to number <TABLE_ID> +# at 342 +<ISO TIMESTAMP> server id 1 end_log_pos 126 CRC32 0x0000000b Update_rows: table id <TABLE_ID> flags: STMT_END_F +# Number of rows: 1 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 197 Table_map: `test`.`t1` mapped to number <TABLE_ID> -# at 161 -<ISO TIMESTAMP> server id 1 end_log_pos 239 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# at 256 +# at 301 +<ISO TIMESTAMP> server id 1 end_log_pos 212 CRC32 0x00000301 Table_map: `test`.`t1` mapped to number <TABLE_ID> +# at 342 +<ISO TIMESTAMP> server id 1 end_log_pos 252 CRC32 0x0000000c Update_rows: table id <TABLE_ID> flags: STMT_END_F +# Number of rows: 1 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 280 Table_map: `test`.`t1` mapped to number <TABLE_ID> -# at 161 -<ISO TIMESTAMP> server id 1 end_log_pos 322 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# at 256 +# at 301 +<ISO TIMESTAMP> server id 1 end_log_pos 338 CRC32 0x00000301 Table_map: `test`.`t1` mapped to number <TABLE_ID> +# at 342 +<ISO TIMESTAMP> server id 1 end_log_pos 378 CRC32 0x0000000d Update_rows: table id <TABLE_ID> flags: STMT_END_F +# Number of rows: 1 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; @@ -67,73 +65,79 @@ ROLLBACK; Start of Multi-table Update UPDATE t1, t2 SET t1.f1 = t1.f1 + 100, t2.f1 = t2.f1 + 100; DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 82 Query thread_id=<QUERY_THREAD_ID> exec_time=<EXEC_TIME> error_code=0 -SET TIMESTAMP=<TIMESTAMP>/*!*/; -/*!\C latin1 *//*!*/; -BEGIN -/*!*/; -# at 202 -<ISO TIMESTAMP> server id 1 end_log_pos 123 Table_map: `test`.`t1` mapped to number <TABLE_ID> -# at 243 -<ISO TIMESTAMP> server id 1 end_log_pos 164 Table_map: `test`.`t2` mapped to number <TABLE_ID> -# at 284 -<ISO TIMESTAMP> server id 1 end_log_pos 206 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# at 256 +# at 333 +<ISO TIMESTAMP> server id 1 end_log_pos 118 CRC32 0x00000301 Table_map: `test`.`t1` mapped to number <TABLE_ID> +# at 374 +<ISO TIMESTAMP> server id 1 end_log_pos 159 CRC32 0x00000301 Table_map: `test`.`t2` mapped to number <TABLE_ID> +# at 415 +<ISO TIMESTAMP> server id 1 end_log_pos 199 CRC32 0x00000065 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# Number of rows: 1 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 247 Table_map: `test`.`t1` mapped to number <TABLE_ID> -# at 161 -<ISO TIMESTAMP> server id 1 end_log_pos 288 Table_map: `test`.`t2` mapped to number <TABLE_ID> -# at 202 -<ISO TIMESTAMP> server id 1 end_log_pos 330 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# at 256 +# at 333 +<ISO TIMESTAMP> server id 1 end_log_pos 317 CRC32 0x00000301 Table_map: `test`.`t1` mapped to number <TABLE_ID> +# at 374 +<ISO TIMESTAMP> server id 1 end_log_pos 358 CRC32 0x00000301 Table_map: `test`.`t2` mapped to number <TABLE_ID> +# at 415 +<ISO TIMESTAMP> server id 1 end_log_pos 398 CRC32 0x00000066 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# Number of rows: 1 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 371 Table_map: `test`.`t1` mapped to number <TABLE_ID> -# at 161 -<ISO TIMESTAMP> server id 1 end_log_pos 412 Table_map: `test`.`t2` mapped to number <TABLE_ID> -# at 202 -<ISO TIMESTAMP> server id 1 end_log_pos 454 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# at 256 +# at 333 +<ISO TIMESTAMP> server id 1 end_log_pos 516 CRC32 0x00000301 Table_map: `test`.`t1` mapped to number <TABLE_ID> +# at 374 +<ISO TIMESTAMP> server id 1 end_log_pos 557 CRC32 0x00000301 Table_map: `test`.`t2` mapped to number <TABLE_ID> +# at 415 +<ISO TIMESTAMP> server id 1 end_log_pos 597 CRC32 0x00000067 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# Number of rows: 1 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 495 Table_map: `test`.`t1` mapped to number <TABLE_ID> -# at 161 -<ISO TIMESTAMP> server id 1 end_log_pos 536 Table_map: `test`.`t2` mapped to number <TABLE_ID> -# at 202 -<ISO TIMESTAMP> server id 1 end_log_pos 578 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# at 256 +# at 333 +<ISO TIMESTAMP> server id 1 end_log_pos 715 CRC32 0x00000301 Table_map: `test`.`t1` mapped to number <TABLE_ID> +# at 374 +<ISO TIMESTAMP> server id 1 end_log_pos 756 CRC32 0x00000301 Table_map: `test`.`t2` mapped to number <TABLE_ID> +# at 415 +<ISO TIMESTAMP> server id 1 end_log_pos 796 CRC32 0x00000065 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# Number of rows: 1 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 619 Table_map: `test`.`t1` mapped to number <TABLE_ID> -# at 161 -<ISO TIMESTAMP> server id 1 end_log_pos 660 Table_map: `test`.`t2` mapped to number <TABLE_ID> -# at 202 -<ISO TIMESTAMP> server id 1 end_log_pos 702 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# at 256 +# at 333 +<ISO TIMESTAMP> server id 1 end_log_pos 914 CRC32 0x00000301 Table_map: `test`.`t1` mapped to number <TABLE_ID> +# at 374 +<ISO TIMESTAMP> server id 1 end_log_pos 955 CRC32 0x00000301 Table_map: `test`.`t2` mapped to number <TABLE_ID> +# at 415 +<ISO TIMESTAMP> server id 1 end_log_pos 995 CRC32 0x00000066 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# Number of rows: 1 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 743 Table_map: `test`.`t1` mapped to number <TABLE_ID> -# at 161 -<ISO TIMESTAMP> server id 1 end_log_pos 784 Table_map: `test`.`t2` mapped to number <TABLE_ID> -# at 202 -<ISO TIMESTAMP> server id 1 end_log_pos 826 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# at 256 +# at 333 +<ISO TIMESTAMP> server id 1 end_log_pos 1113 CRC32 0x00000301 Table_map: `test`.`t1` mapped to number <TABLE_ID> +# at 374 +<ISO TIMESTAMP> server id 1 end_log_pos 1154 CRC32 0x00000301 Table_map: `test`.`t2` mapped to number <TABLE_ID> +# at 415 +<ISO TIMESTAMP> server id 1 end_log_pos 1194 CRC32 0x00000067 Update_rows: table id <TABLE_ID> flags: STMT_END_F +# Number of rows: 1 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; @@ -147,47 +151,23 @@ SAVEPOINT X; INSERT INTO t1 VALUES (2000); ROLLBACK TO SAVEPOINT X; DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 73 Query thread_id=<QUERY_THREAD_ID> exec_time=<EXEC_TIME> error_code=0 -SET TIMESTAMP=<TIMESTAMP>/*!*/; -/*!\C latin1 *//*!*/; -BEGIN -/*!*/; -# at 193 -<ISO TIMESTAMP> server id 1 end_log_pos 114 Table_map: `test`.`t1` mapped to number <TABLE_ID> -# at 234 -<ISO TIMESTAMP> server id 1 end_log_pos 150 Write_rows: table id <TABLE_ID> flags: STMT_END_F +# at 256 +# at 303 +<ISO TIMESTAMP> server id 1 end_log_pos 88 CRC32 0x00000301 Table_map: `test`.`t1` mapped to number <TABLE_ID> +# at 344 +<ISO TIMESTAMP> server id 1 end_log_pos 122 CRC32 0x000003e8 Write_rows: table id <TABLE_ID> flags: STMT_END_F +# Number of rows: 1 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 231 Query thread_id=<QUERY_THREAD_ID> exec_time=<EXEC_TIME> error_code=0 -SET TIMESTAMP=<TIMESTAMP>/*!*/; -/*!\C latin1 *//*!*/; -SAVEPOINT `X` -/*!*/; -DELIMITER ; -# End of log file -ROLLBACK /* added by mysqlbinlog */; -/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; -DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 272 Table_map: `test`.`t1` mapped to number <TABLE_ID> -# at 161 -<ISO TIMESTAMP> server id 1 end_log_pos 308 Write_rows: table id <TABLE_ID> flags: STMT_END_F -DELIMITER ; -# End of log file -ROLLBACK /* added by mysqlbinlog */; -/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; -DELIMITER /*!*/; -# at 120 -<ISO TIMESTAMP> server id 1 end_log_pos 391 Query thread_id=<QUERY_THREAD_ID> exec_time=<EXEC_TIME> error_code=0 -SET TIMESTAMP=<TIMESTAMP>/*!*/; -/*!\C latin1 *//*!*/; -ROLLBACK TO `X` -/*!*/; +# at 256 +# at 303 +<ISO TIMESTAMP> server id 1 end_log_pos 210 CRC32 0x00000301 Table_map: `test`.`t1` mapped to number <TABLE_ID> +# at 344 +<ISO TIMESTAMP> server id 1 end_log_pos 244 CRC32 0x000007d0 Write_rows: table id <TABLE_ID> flags: STMT_END_F +# Number of rows: 1 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; diff --git a/mysql-test/suite/galera_sr/r/mysql-wsrep-features#136.result b/mysql-test/suite/galera_sr/r/mysql-wsrep-features#136.result index 25bc2e11a3e..84246a8eed2 100644 --- a/mysql-test/suite/galera_sr/r/mysql-wsrep-features#136.result +++ b/mysql-test/suite/galera_sr/r/mysql-wsrep-features#136.result @@ -29,7 +29,6 @@ mysqld-bin.000001 <Pos> Gtid 1 <End_log_pos> BEGIN GTID 0-1-2 mysqld-bin.000001 <Pos> Annotate_rows 1 <End_log_pos> INSERT INTO t1 VALUES (1),(2) mysqld-bin.000001 <Pos> Table_map 1 <End_log_pos> table_id: ### (test.t1) mysqld-bin.000001 <Pos> Write_rows_v1 1 <End_log_pos> table_id: ### flags: STMT_END_F -mysqld-bin.000001 <Pos> Annotate_rows 1 <End_log_pos> INSERT INTO t1 VALUES (1),(2) mysqld-bin.000001 <Pos> Table_map 1 <End_log_pos> table_id: ### (test.t1) mysqld-bin.000001 <Pos> Write_rows_v1 1 <End_log_pos> table_id: ### flags: STMT_END_F mysqld-bin.000001 <Pos> Xid 1 <End_log_pos> COMMIT /* xid=### */ @@ -52,7 +51,6 @@ mysqld-bin.000001 <Pos> Gtid 1 <End_log_pos> BEGIN GTID 0-1-2 mysqld-bin.000001 <Pos> Annotate_rows 1 <End_log_pos> INSERT INTO t1 VALUES (1),(2) mysqld-bin.000001 <Pos> Table_map 1 <End_log_pos> table_id: ### (test.t1) mysqld-bin.000001 <Pos> Write_rows_v1 1 <End_log_pos> table_id: ### flags: STMT_END_F -mysqld-bin.000001 <Pos> Annotate_rows 1 <End_log_pos> INSERT INTO t1 VALUES (1),(2) mysqld-bin.000001 <Pos> Table_map 1 <End_log_pos> table_id: ### (test.t1) mysqld-bin.000001 <Pos> Write_rows_v1 1 <End_log_pos> table_id: ### flags: STMT_END_F mysqld-bin.000001 <Pos> Xid 1 <End_log_pos> COMMIT /* xid=### */ diff --git a/mysql-test/suite/galera_sr/t/MDEV-18585.cnf b/mysql-test/suite/galera_sr/t/MDEV-18585.cnf new file mode 100644 index 00000000000..00801511d76 --- /dev/null +++ b/mysql-test/suite/galera_sr/t/MDEV-18585.cnf @@ -0,0 +1,5 @@ +!include ../galera_2nodes.cnf + +[mysqld.1] +log-bin +log-slave-updates diff --git a/mysql-test/suite/galera_sr/t/MDEV-18585.test b/mysql-test/suite/galera_sr/t/MDEV-18585.test new file mode 100644 index 00000000000..18bbbe8f7e0 --- /dev/null +++ b/mysql-test/suite/galera_sr/t/MDEV-18585.test @@ -0,0 +1,42 @@ +# +# MDEV-18686 Verify that the Annotate_rows_log_event is written only +# once per statement into binlog. +# +--source include/galera_cluster.inc + +CREATE TABLE t1 (f1 INT PRIMARY KEY); + +# +# Unit ROW +# +SET SESSION wsrep_trx_fragment_unit='ROWS'; +SET SESSION wsrep_trx_fragment_size=1; + +INSERT INTO t1 VALUES (1), (2); + +# +# Unit BYTE +# +SET SESSION wsrep_trx_fragment_unit='BYTES'; +SET SESSION wsrep_trx_fragment_size=1; + +INSERT INTO t1 VALUES (3), (4); + +# +# Unit STATEMENT +# +SET SESSION wsrep_trx_fragment_unit='STATEMENTS'; +SET SESSION wsrep_trx_fragment_size=1; + +INSERT INTO t1 VALUES (5), (6); + +# +# Reset to default settings +# +SET SESSION wsrep_trx_fragment_unit=default; +SET SESSION wsrep_trx_fragment_size=default; + +--replace_regex /table_id: [0-9]+/table_id: #/ /xid=[0-9]+/xid=#/ +SHOW BINLOG EVENTS IN 'mysqld-bin.000002' FROM 518; + +DROP TABLE t1; diff --git a/mysql-test/suite/galera_sr/t/galera_sr_create_drop.test b/mysql-test/suite/galera_sr/t/galera_sr_create_drop.test new file mode 100644 index 00000000000..b7987d26191 --- /dev/null +++ b/mysql-test/suite/galera_sr/t/galera_sr_create_drop.test @@ -0,0 +1,33 @@ +# +# Verify that CREATE/DROP DDLs work when streaming replication is on. +# + +--source include/galera_cluster.inc + +SET SESSION wsrep_trx_fragment_size=1; + +# +# CREATE/DROP TABLE succeeds and the change is propagated to node_2. +# +CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB; +--connection node_2 +SHOW CREATE TABLE t1; +--connection node_1 +DROP TABLE t1; +--connection node_2 +--error ER_NO_SUCH_TABLE +SHOW CREATE TABLE t1; + +# +# CREATE/DROP DATABASE succeeds and the change is propagated to node_2. +# +CREATE DATABASE mdev_18587; +--connection node_2 +SHOW DATABASES LIKE 'mdev_18587'; +--connection node_1 +DROP DATABASE mdev_18587; +--connection node_2 +SHOW DATABASES LIKE 'mdev_18587'; +--connection node_1 + +SET SESSION wsrep_trx_fragment_size=DEFAULT; diff --git a/mysql-test/suite/gcol/r/gcol_select_innodb.result b/mysql-test/suite/gcol/r/gcol_select_innodb.result index 97c6410ef5d..f933fe22bdc 100644 --- a/mysql-test/suite/gcol/r/gcol_select_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_select_innodb.result @@ -699,7 +699,7 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) @@ -754,7 +754,7 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) @@ -810,7 +810,7 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) @@ -874,7 +874,7 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result index 0f902e09d63..853412fe7fe 100644 --- a/mysql-test/suite/gcol/r/gcol_select_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result @@ -1325,7 +1325,7 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) @@ -1381,7 +1381,7 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY,v_idx,v_idx2 PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) @@ -1439,7 +1439,7 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY,v_idx2 PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) @@ -1506,7 +1506,7 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) diff --git a/mysql-test/suite/innodb/r/instant_alter.result b/mysql-test/suite/innodb/r/instant_alter.result index 448f5c668e2..8753977c0b8 100644 --- a/mysql-test/suite/innodb/r/instant_alter.result +++ b/mysql-test/suite/innodb/r/instant_alter.result @@ -741,8 +741,8 @@ CREATE TABLE t1 (f TINYINT, g SMALLINT UNSIGNED) ENGINE=InnoDB ROW_FORMAT=REDUND INSERT INTO t1 VALUES(127,6502),(-128,33101); ALTER TABLE t1 MODIFY f SMALLINT DEFAULT 12345, MODIFY g BIGINT UNSIGNED DEFAULT 1234567; -affected rows: 0 -info: Records: 0 Duplicates: 0 Warnings: 0 +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 SELECT * FROM t1; f g 127 6502 @@ -779,10 +779,90 @@ DELETE FROM t1 LIMIT 3; ALTER TABLE t1 MODIFY f BIT(15); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY f BIT(8); +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 SELECT HEX(f) FROM t1; HEX(f) 80 DROP TABLE t1; +CREATE TABLE t1 (b BIT NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT DEFAULT CHARSET utf16; +INSERT INTO t1 SET b=b'1'; +ALTER TABLE t1 CHANGE b c BIT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT HEX(c) FROM t1; +HEX(c) +1 +DROP TABLE t1; +CREATE TABLE t1 (c VARCHAR(10) NOT NULL DEFAULT 'scary') ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1() VALUES(); +ALTER TABLE t1 ADD f TINYINT NOT NULL DEFAULT -42; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY f MEDIUMINT NOT NULL DEFAULT 64802, +MODIFY c VARCHAR(20) NOT NULL DEFAULT 'gory', +ADD d DATETIME; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +INSERT INTO t1() VALUES(); +INSERT INTO t1 (c,f,d) VALUES ('fury', -8388608, now()); +SELECT * FROM t1; +c f d +scary -42 NULL +gory 64802 NULL +fury -8388608 1970-01-01 03:00:42 +DROP TABLE t1; +CREATE TABLE t1 (t TINYINT PRIMARY KEY, m MEDIUMINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +SELECT table_id INTO @table_id1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +INSERT INTO t1 VALUES (-42, -123456); +ALTER TABLE t1 CHANGE t s SMALLINT; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +affected rows: 1 +ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ALTER TABLE t1 CHANGE m i INT; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT table_id INTO @table_id3 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +affected rows: 1 +SELECT @table_id1 = @table_id2, @table_id2 = @table_id3; +@table_id1 = @table_id2 @table_id2 = @table_id3 +0 0 +INSERT IGNORE INTO t1 VALUES (0, -123456); +Warnings: +Warning 1062 Duplicate entry '-123456' for key 'm' +REPLACE INTO t1 VALUES(-42, 123456); +INSERT IGNORE INTO t1 VALUES(32768, 2147483648); +Warnings: +Warning 1264 Out of range value for column 's' at row 1 +Warning 1264 Out of range value for column 'i' at row 1 +SELECT * FROM t1; +s i +-42 123456 +32767 2147483647 +DROP TABLE t1; +CREATE TABLE t1 (a SERIAL, b INT, c TINYINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 (c) VALUES(1),(2),(3); +ALTER TABLE t1 MODIFY c BIGINT; +affected rows: 3 +info: Records: 3 Duplicates: 0 Warnings: 0 +UPDATE t1 SET b=1 WHERE c=2; +UPDATE t1 SET c=4 WHERE a=3; +UPDATE t1 SET b=2 WHERE c>3; +UPDATE t1 SET c=c+1; +ERROR 23000: Duplicate entry '2' for key 'c' +SELECT * FROM t1; +a b c +1 NULL 1 +2 1 2 +3 2 4 +DROP TABLE t1; CREATE TABLE t1 (id INT PRIMARY KEY, c2 INT UNIQUE, c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'), @@ -1508,10 +1588,90 @@ DELETE FROM t1 LIMIT 3; ALTER TABLE t1 MODIFY f BIT(15); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY f BIT(8); +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 SELECT HEX(f) FROM t1; HEX(f) 80 DROP TABLE t1; +CREATE TABLE t1 (b BIT NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET utf16; +INSERT INTO t1 SET b=b'1'; +ALTER TABLE t1 CHANGE b c BIT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT HEX(c) FROM t1; +HEX(c) +1 +DROP TABLE t1; +CREATE TABLE t1 (c VARCHAR(10) NOT NULL DEFAULT 'scary') ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1() VALUES(); +ALTER TABLE t1 ADD f TINYINT NOT NULL DEFAULT -42; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY f MEDIUMINT NOT NULL DEFAULT 64802, +MODIFY c VARCHAR(20) NOT NULL DEFAULT 'gory', +ADD d DATETIME; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +INSERT INTO t1() VALUES(); +INSERT INTO t1 (c,f,d) VALUES ('fury', -8388608, now()); +SELECT * FROM t1; +c f d +scary -42 NULL +gory 64802 NULL +fury -8388608 1970-01-01 03:00:42 +DROP TABLE t1; +CREATE TABLE t1 (t TINYINT PRIMARY KEY, m MEDIUMINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=COMPACT; +SELECT table_id INTO @table_id1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +INSERT INTO t1 VALUES (-42, -123456); +ALTER TABLE t1 CHANGE t s SMALLINT; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +affected rows: 1 +ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ALTER TABLE t1 CHANGE m i INT; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT table_id INTO @table_id3 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +affected rows: 1 +SELECT @table_id1 = @table_id2, @table_id2 = @table_id3; +@table_id1 = @table_id2 @table_id2 = @table_id3 +0 0 +INSERT IGNORE INTO t1 VALUES (0, -123456); +Warnings: +Warning 1062 Duplicate entry '-123456' for key 'm' +REPLACE INTO t1 VALUES(-42, 123456); +INSERT IGNORE INTO t1 VALUES(32768, 2147483648); +Warnings: +Warning 1264 Out of range value for column 's' at row 1 +Warning 1264 Out of range value for column 'i' at row 1 +SELECT * FROM t1; +s i +-42 123456 +32767 2147483647 +DROP TABLE t1; +CREATE TABLE t1 (a SERIAL, b INT, c TINYINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 (c) VALUES(1),(2),(3); +ALTER TABLE t1 MODIFY c BIGINT; +affected rows: 3 +info: Records: 3 Duplicates: 0 Warnings: 0 +UPDATE t1 SET b=1 WHERE c=2; +UPDATE t1 SET c=4 WHERE a=3; +UPDATE t1 SET b=2 WHERE c>3; +UPDATE t1 SET c=c+1; +ERROR 23000: Duplicate entry '2' for key 'c' +SELECT * FROM t1; +a b c +1 NULL 1 +2 1 2 +3 2 4 +DROP TABLE t1; CREATE TABLE t1 (id INT PRIMARY KEY, c2 INT UNIQUE, c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'), @@ -2237,14 +2397,94 @@ DELETE FROM t1 LIMIT 3; ALTER TABLE t1 MODIFY f BIT(15); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY f BIT(8); +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 SELECT HEX(f) FROM t1; HEX(f) 80 DROP TABLE t1; +CREATE TABLE t1 (b BIT NOT NULL) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET utf16; +INSERT INTO t1 SET b=b'1'; +ALTER TABLE t1 CHANGE b c BIT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT HEX(c) FROM t1; +HEX(c) +1 +DROP TABLE t1; +CREATE TABLE t1 (c VARCHAR(10) NOT NULL DEFAULT 'scary') ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1() VALUES(); +ALTER TABLE t1 ADD f TINYINT NOT NULL DEFAULT -42; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY f MEDIUMINT NOT NULL DEFAULT 64802, +MODIFY c VARCHAR(20) NOT NULL DEFAULT 'gory', +ADD d DATETIME; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +INSERT INTO t1() VALUES(); +INSERT INTO t1 (c,f,d) VALUES ('fury', -8388608, now()); +SELECT * FROM t1; +c f d +scary -42 NULL +gory 64802 NULL +fury -8388608 1970-01-01 03:00:42 +DROP TABLE t1; +CREATE TABLE t1 (t TINYINT PRIMARY KEY, m MEDIUMINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +SELECT table_id INTO @table_id1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +INSERT INTO t1 VALUES (-42, -123456); +ALTER TABLE t1 CHANGE t s SMALLINT; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +affected rows: 1 +ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ALTER TABLE t1 CHANGE m i INT; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT table_id INTO @table_id3 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +affected rows: 1 +SELECT @table_id1 = @table_id2, @table_id2 = @table_id3; +@table_id1 = @table_id2 @table_id2 = @table_id3 +0 0 +INSERT IGNORE INTO t1 VALUES (0, -123456); +Warnings: +Warning 1062 Duplicate entry '-123456' for key 'm' +REPLACE INTO t1 VALUES(-42, 123456); +INSERT IGNORE INTO t1 VALUES(32768, 2147483648); +Warnings: +Warning 1264 Out of range value for column 's' at row 1 +Warning 1264 Out of range value for column 'i' at row 1 +SELECT * FROM t1; +s i +-42 123456 +32767 2147483647 +DROP TABLE t1; +CREATE TABLE t1 (a SERIAL, b INT, c TINYINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 (c) VALUES(1),(2),(3); +ALTER TABLE t1 MODIFY c BIGINT; +affected rows: 3 +info: Records: 3 Duplicates: 0 Warnings: 0 +UPDATE t1 SET b=1 WHERE c=2; +UPDATE t1 SET c=4 WHERE a=3; +UPDATE t1 SET b=2 WHERE c>3; +UPDATE t1 SET c=c+1; +ERROR 23000: Duplicate entry '2' for key 'c' +SELECT * FROM t1; +a b c +1 NULL 1 +2 1 2 +3 2 4 +DROP TABLE t1; disconnect analyze; SELECT variable_value-@old_instant instants FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; instants -171 +174 SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency; diff --git a/mysql-test/suite/innodb/r/instant_alter_charset,redundant.rdiff b/mysql-test/suite/innodb/r/instant_alter_charset,redundant.rdiff index 9a8dcd45e36..82a5ca95986 100644 --- a/mysql-test/suite/innodb/r/instant_alter_charset,redundant.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter_charset,redundant.rdiff @@ -1,23 +1,5 @@ --- instant_alter_charset.result +++ instant_alter_charset,redundant.result -@@ -143,7 +143,7 @@ - drop index ab, - add unique key ab(a,c), - algorithm=instant; --ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY -+ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY - drop table key_part_change; - create table key_part_change_and_rename ( - a char(100) charset ascii, -@@ -156,7 +156,7 @@ - drop index ab, - add unique key ab(a,b), - algorithm=instant; --ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY -+ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY - drop table key_part_change_and_rename; - create table enum_and_set ( - a enum('one', 'two') charset utf8mb3, @@ -254,7 +254,6 @@ alter table boundary_255 modify b varchar(200) charset utf8mb3, diff --git a/mysql-test/suite/innodb/r/instant_alter_extend.result b/mysql-test/suite/innodb/r/instant_alter_extend.result Binary files differindex d353e5f97f4..ca8e566f19c 100644 --- a/mysql-test/suite/innodb/r/instant_alter_extend.result +++ b/mysql-test/suite/innodb/r/instant_alter_extend.result diff --git a/mysql-test/suite/innodb/t/instant_alter.test b/mysql-test/suite/innodb/t/instant_alter.test index d662277b8e3..99f49300b2e 100644 --- a/mysql-test/suite/innodb/t/instant_alter.test +++ b/mysql-test/suite/innodb/t/instant_alter.test @@ -668,10 +668,66 @@ ALTER TABLE t1 MODIFY f BIT(15); DELETE FROM t1 LIMIT 3; --enable_info ALTER TABLE t1 MODIFY f BIT(15); +ALTER TABLE t1 MODIFY f BIT(8); --disable_info SELECT HEX(f) FROM t1; DROP TABLE t1; +eval CREATE TABLE t1 (b BIT NOT NULL) $engine DEFAULT CHARSET utf16; +INSERT INTO t1 SET b=b'1'; +--enable_info +ALTER TABLE t1 CHANGE b c BIT NOT NULL; +--disable_info +SELECT HEX(c) FROM t1; +DROP TABLE t1; + +eval CREATE TABLE t1 (c VARCHAR(10) NOT NULL DEFAULT 'scary') $engine; +INSERT INTO t1() VALUES(); +--enable_info +ALTER TABLE t1 ADD f TINYINT NOT NULL DEFAULT -42; +ALTER TABLE t1 MODIFY f MEDIUMINT NOT NULL DEFAULT 64802, +MODIFY c VARCHAR(20) NOT NULL DEFAULT 'gory', +ADD d DATETIME; +--disable_info +INSERT INTO t1() VALUES(); +INSERT INTO t1 (c,f,d) VALUES ('fury', -8388608, now()); +SELECT * FROM t1; +DROP TABLE t1; + +eval CREATE TABLE t1 (t TINYINT PRIMARY KEY, m MEDIUMINT UNIQUE) $engine; +SELECT table_id INTO @table_id1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +INSERT INTO t1 VALUES (-42, -123456); +--enable_info +ALTER TABLE t1 CHANGE t s SMALLINT; +SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT; +ALTER TABLE t1 CHANGE m i INT; +SELECT table_id INTO @table_id3 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +--disable_info +SELECT @table_id1 = @table_id2, @table_id2 = @table_id3; +INSERT IGNORE INTO t1 VALUES (0, -123456); +REPLACE INTO t1 VALUES(-42, 123456); +INSERT IGNORE INTO t1 VALUES(32768, 2147483648); +SELECT * FROM t1; +DROP TABLE t1; + +eval CREATE TABLE t1 (a SERIAL, b INT, c TINYINT UNIQUE) $engine; +INSERT INTO t1 (c) VALUES(1),(2),(3); +--enable_info +ALTER TABLE t1 MODIFY c BIGINT; +--disable_info +UPDATE t1 SET b=1 WHERE c=2; +UPDATE t1 SET c=4 WHERE a=3; +UPDATE t1 SET b=2 WHERE c>3; +--error ER_DUP_ENTRY +UPDATE t1 SET c=c+1; +SELECT * FROM t1; +DROP TABLE t1; + dec $format; } disconnect analyze; diff --git a/mysql-test/suite/innodb/t/instant_alter_extend.test b/mysql-test/suite/innodb/t/instant_alter_extend.test index 80f82820afe..6b528c1d30c 100644 --- a/mysql-test/suite/innodb/t/instant_alter_extend.test +++ b/mysql-test/suite/innodb/t/instant_alter_extend.test @@ -1,8 +1,10 @@ --source include/have_innodb.inc +--source include/innodb_row_format.inc --source include/maybe_debug.inc -- echo # -- echo # MDEV-15563: Instant ROW_FORMAT=REDUNDANT column type change&extension +-- echo # (reverted in MDEV-18627) -- echo # # Use character-set-server in test db @@ -10,8 +12,6 @@ create or replace database test; use test; set default_storage_engine=innodb; -set @save_format= @@GLOBAL.innodb_default_row_format; -SET GLOBAL innodb_default_row_format=redundant; set @bigval= repeat('0123456789', 30); delimiter ~~; @@ -45,7 +45,9 @@ set @save_debug= @@SESSION.debug_dbug; set debug_dbug= '+d,ib_instant_error'; --enable_query_log } -alter table t modify a char(200), algorithm=instant; +--enable_info +alter table t modify a char(200); +--disable_info select count(a) from t where a = @bigval; select a, length(a) from t where a = 'z'; @@ -53,7 +55,9 @@ check table t extended; call check_table('t'); --echo # CHAR enlargement -alter table t modify a char(220), algorithm=instant; +--enable_info +alter table t modify a char(220); +--disable_info select count(a) from t where a = @bigval; select a, length(a) from t where a = 'z'; @@ -61,10 +65,10 @@ check table t extended; call check_table('t'); --echo # Convert from VARCHAR to a bigger CHAR ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -alter table t modify a varchar(200), algorithm=instant; -alter table t modify a varchar(200), algorithm=copy; -alter table t modify a char(255), algorithm=instant; +--enable_info +alter table t modify a varchar(200); +alter table t modify a char(255); +--disable_info select count(a) from t where a = @bigval; select a, length(a) from t where a = 'z'; @@ -74,14 +78,17 @@ call check_table('t'); --echo # BINARY/VARBINARY test create or replace table t (a varbinary(300)); ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -alter table t modify a binary(255), algorithm=instant; -alter table t modify a binary(255), algorithm=copy; +insert into t values(NULL); +--enable_info +alter table t modify a binary(255); +--disable_info create or replace table t (a varbinary(200)); insert into t values (@bigval); insert into t values ('z'); -alter table t modify a binary(200), algorithm=instant; +--enable_info +alter table t modify a binary(200); +--disable_info select count(a) from t where a = @bigval; select length(a) from t where left(a, 1) = 'z'; @@ -89,16 +96,18 @@ check table t extended; call check_table('t'); --echo # BINARY enlargement -alter table t modify a binary(220), algorithm=instant; +--enable_info +alter table t modify a binary(220); +--disable_info check table t extended; call check_table('t'); --echo # Convert from VARBINARY to a bigger BINARY ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -alter table t modify a varbinary(220), algorithm=instant; -alter table t modify a varbinary(220), algorithm=copy; -alter table t modify a binary(255), algorithm=instant; +--enable_info +alter table t modify a varbinary(220); +alter table t modify a binary(255); +--disable_info select count(a) from t where a = @bigval; select a, length(a) from t where a = 'z'; @@ -110,25 +119,33 @@ call check_table('t'); --echo # Integer conversions create or replace table t (x tinyint); insert into t values (127); -alter table t modify x smallint, algorithm=instant; +--enable_info +alter table t modify x smallint; +--disable_info select * from t; check table t extended; call check_table('t'); update t set x= 32767; -alter table t modify x mediumint, algorithm=instant; +--enable_info +alter table t modify x mediumint; +--disable_info select * from t; check table t extended; call check_table('t'); update t set x= 8388607; -alter table t modify x int, algorithm=instant; +--enable_info +alter table t modify x int; +--disable_info select * from t; check table t extended; call check_table('t'); update t set x= 2147483647; -alter table t modify x bigint, algorithm=instant; +--enable_info +alter table t modify x bigint; +--disable_info select * from t; check table t extended; call check_table('t'); @@ -163,13 +180,19 @@ call check_table('t2'); create or replace table t1 (x mediumint); insert into t1 values (1); insert into t1 values (1); -alter table t1 add column y int first, modify x int, algorithm instant; +--enable_info +alter table t1 add column y int first, modify x int; --error ER_DUP_ENTRY alter table t1 add column z int first, add primary key (x); +--disable_info --echo # Check assertion in wrong instant operation create or replace table t1 (a varchar(26) not null) default character set utf8mb4; -alter table t1 modify a varchar(25) not null; +insert into t1 values ('abcdef'), (repeat('x',26)); +--enable_info +alter ignore table t1 modify a varchar(25) not null; +--disable_info +select * from t1; --echo # Check row_mysql_store_col_in_innobase_format() create or replace table t1(x int primary key, a varchar(20)); @@ -181,30 +204,46 @@ update t1 set a= 'foo' where x = 2; --echo # create or replace table t1 (x int, y int); insert into t1 (x, y) values (11, 22); -alter table t1 modify x bigint, algorithm instant; -alter table t1 add primary key (x), algorithm inplace; +--enable_info +alter table t1 modify x bigint; +alter table t1 add primary key (x); +--disable_info +select * from t1; check table t1; create or replace table t1 (a varchar(10), y int); insert into t1 (a, y) values ("0123456789", 33); -alter table t1 modify a char(15), algorithm instant; -alter table t1 add primary key (a), algorithm inplace; +--enable_info +alter table t1 modify a char(15); +alter table t1 add primary key (a); +--disable_info +select * from t1; check table t1; create or replace table t1 (x int primary key, y int); insert into t1 (x, y) values (44, 55); -alter table t1 modify x bigint, algorithm inplace; +--enable_info +alter table t1 modify x bigint; +--disable_info +select * from t1; check table t1; create or replace table t1 (x int primary key, y int); insert into t1 values (66, 77); -alter table t1 add column z int, algorithm instant; -alter table t1 drop column y, algorithm instant; +--enable_info +alter table t1 add column z int; +alter table t1 drop column y; +--disable_info +select * from t1; +check table t1; create or replace table t1 (x integer, a varchar(20)); -alter table t1 add index idx3 (a); +--enable_info insert into t1 (x, a) values (73, 'a'); +alter table t1 add index idx3 (a); alter table t1 modify a char(20); +--disable_info +select * from t1; +check table t1; create or replace database test charset latin1; -SET GLOBAL innodb_default_row_format=@save_format; diff --git a/mysql-test/suite/sys_vars/r/histogram_size_basic.result b/mysql-test/suite/sys_vars/r/histogram_size_basic.result index 1f310600d00..05a52309d9f 100644 --- a/mysql-test/suite/sys_vars/r/histogram_size_basic.result +++ b/mysql-test/suite/sys_vars/r/histogram_size_basic.result @@ -1,20 +1,20 @@ SET @start_global_value = @@global.histogram_size; SELECT @start_global_value; @start_global_value -0 +254 SET @start_session_value = @@session.histogram_size; SELECT @start_session_value; @start_session_value -0 +254 '#--------------------FN_DYNVARS_053_01-------------------------#' SET @@global.histogram_size = DEFAULT; SELECT @@global.histogram_size; @@global.histogram_size -0 +254 SET @@session.histogram_size = DEFAULT; SELECT @@session.histogram_size; @@session.histogram_size -0 +254 '#--------------------FN_DYNVARS_053_03-------------------------#' SET @@global.histogram_size = 1; SELECT @@global.histogram_size; @@ -129,8 +129,8 @@ SELECT @@local.histogram_size = @@session.histogram_size; SET @@global.histogram_size = @start_global_value; SELECT @@global.histogram_size; @@global.histogram_size -0 +254 SET @@session.histogram_size = @start_session_value; SELECT @@session.histogram_size; @@session.histogram_size -0 +254 diff --git a/mysql-test/suite/sys_vars/r/histogram_type_basic.result b/mysql-test/suite/sys_vars/r/histogram_type_basic.result index f688a2a15fd..db42204ac1f 100644 --- a/mysql-test/suite/sys_vars/r/histogram_type_basic.result +++ b/mysql-test/suite/sys_vars/r/histogram_type_basic.result @@ -1,16 +1,16 @@ SET @start_global_value = @@global.histogram_type; SELECT @start_global_value; @start_global_value -SINGLE_PREC_HB +DOUBLE_PREC_HB SET @start_session_value = @@session.histogram_type; SELECT @start_session_value; @start_session_value -SINGLE_PREC_HB +DOUBLE_PREC_HB SET @@global.histogram_type = 1; SET @@global.histogram_type = DEFAULT; SELECT @@global.histogram_type; @@global.histogram_type -SINGLE_PREC_HB +DOUBLE_PREC_HB SET @@global.histogram_type = 0; SELECT @@global.histogram_type; @@global.histogram_type @@ -71,9 +71,9 @@ HISTOGRAM_TYPE DOUBLE_PREC_HB SET @@global.histogram_type = @start_global_value; SELECT @@global.histogram_type; @@global.histogram_type -SINGLE_PREC_HB +DOUBLE_PREC_HB SET @@session.histogram_type = @start_session_value; SELECT @@session.histogram_type; @@session.histogram_type -SINGLE_PREC_HB +DOUBLE_PREC_HB set sql_mode=''; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 723008d1ffd..6428c4b72ce 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -1287,10 +1287,10 @@ ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME HISTOGRAM_SIZE -SESSION_VALUE 0 -GLOBAL_VALUE 0 +SESSION_VALUE 254 +GLOBAL_VALUE 254 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 0 +DEFAULT_VALUE 254 VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Number of bytes used for a histogram. If set to 0, no histograms are created by ANALYZE. @@ -1301,10 +1301,10 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME HISTOGRAM_TYPE -SESSION_VALUE SINGLE_PREC_HB -GLOBAL_VALUE SINGLE_PREC_HB +SESSION_VALUE DOUBLE_PREC_HB +GLOBAL_VALUE DOUBLE_PREC_HB GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE SINGLE_PREC_HB +DEFAULT_VALUE DOUBLE_PREC_HB VARIABLE_SCOPE SESSION VARIABLE_TYPE ENUM VARIABLE_COMMENT Specifies type of the histograms created by ANALYZE. Possible values are: SINGLE_PREC_HB - single precision height-balanced, DOUBLE_PREC_HB - double precision height-balanced. diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index f2031d7e6b9..f9bc7d3a028 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -1427,10 +1427,10 @@ ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME HISTOGRAM_SIZE -SESSION_VALUE 0 -GLOBAL_VALUE 0 +SESSION_VALUE 254 +GLOBAL_VALUE 254 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 0 +DEFAULT_VALUE 254 VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Number of bytes used for a histogram. If set to 0, no histograms are created by ANALYZE. @@ -1441,10 +1441,10 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME HISTOGRAM_TYPE -SESSION_VALUE SINGLE_PREC_HB -GLOBAL_VALUE SINGLE_PREC_HB +SESSION_VALUE DOUBLE_PREC_HB +GLOBAL_VALUE DOUBLE_PREC_HB GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE SINGLE_PREC_HB +DEFAULT_VALUE DOUBLE_PREC_HB VARIABLE_SCOPE SESSION VARIABLE_TYPE ENUM VARIABLE_COMMENT Specifies type of the histograms created by ANALYZE. Possible values are: SINGLE_PREC_HB - single precision height-balanced, DOUBLE_PREC_HB - double precision height-balanced. |