1# 2# Bug#18591145 - SOME MONOTONICALLY INCREASING STATUS VARIABLES DECREASES UNEXPECTEDLY 3# 4show variables like "show_COMPATIBILITY_56"; 5Variable_name Value 6show_compatibility_56 ON 7set global SHOW_COMPATIBILITY_56 = 1; 8CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64), val VARCHAR(1024)); 9# Insert 1 tuple to increment com_insert status. 10INSERT INTO t1(name, val) VALUES ('dummy', 0); 11connect con1, localhost, root,,; 12SET DEBUG_SYNC='before_preparing_global_status_array SIGNAL change_user WAIT_FOR continue'; 13SET DEBUG_SYNC='after_preparing_global_status_array SIGNAL continue_change_user'; 14INSERT INTO t1(name, val) SELECT * FROM INFORMATION_SCHEMA.global_status WHERE variable_name='com_insert';; 15connection default; 16SET DEBUG_SYNC='now WAIT_FOR change_user'; 17SET DEBUG_SYNC='thd_cleanup_start SIGNAL continue WAIT_FOR continue_change_user'; 18connection con1; 19Warnings: 20Warning 1287 'INFORMATION_SCHEMA.GLOBAL_STATUS' is deprecated and will be removed in a future release. Please use performance_schema.global_status instead 21SET DEBUG_SYNC='RESET'; 22connection default; 23INSERT INTO t1(name, val) 24SELECT * FROM INFORMATION_SCHEMA.global_status WHERE variable_name='com_insert'; 25Warnings: 26Warning 1287 'INFORMATION_SCHEMA.GLOBAL_STATUS' is deprecated and will be removed in a future release. Please use performance_schema.global_status instead 27# With fix, difference of com_insert status should be "0" here. 28SELECT (SELECT val FROM t1 WHERE id = 2) - (SELECT val FROM t1 WHERE id = 3); 29(SELECT val FROM t1 WHERE id = 2) - (SELECT val FROM t1 WHERE id = 3) 300 31# Testing the same scenario with performance_schema. 32set global SHOW_COMPATIBILITY_56 = 0; 33truncate t1; 34# Insert 1 tuple to increment com_insert status. 35INSERT INTO t1(name, val) VALUES ('dummy', 0); 36connection con1; 37SET DEBUG_SYNC='before_materialize_global_status_array SIGNAL change_user WAIT_FOR continue'; 38SET DEBUG_SYNC='after_materialize_global_status_array SIGNAL continue_change_user'; 39INSERT INTO t1(name, val) SELECT * FROM performance_schema.global_status WHERE variable_name='Handler_commit';; 40connection default; 41SET DEBUG_SYNC='now WAIT_FOR change_user'; 42SET DEBUG_SYNC='thd_cleanup_start SIGNAL continue WAIT_FOR continue_change_user'; 43connection con1; 44SET DEBUG_SYNC='RESET'; 45connection default; 46INSERT INTO t1(name, val) SELECT * FROM performance_schema.global_status WHERE variable_name='Handler_commit'; 47# With fix, Handler_commit status should be 1 (select insert during switch user). 48SELECT (SELECT val FROM t1 WHERE id = 3) - (SELECT val FROM t1 WHERE id = 2); 49(SELECT val FROM t1 WHERE id = 3) - (SELECT val FROM t1 WHERE id = 2) 501 51set global SHOW_COMPATIBILITY_56=1; 52disconnect con1; 53DROP TABLE t1; 54# 55# Bug#29836204: P_S TABLE ACCESS HANGS WHILE IN LOCK TABLES MODE 56# 57SET @saved_show_compatibility_56= @@global.show_compatibility_56; 58# Ensures that the P_S is used for the SHOW command. 59SET GLOBAL show_compatibility_56= OFF; 60CREATE TABLE t1(fld1 int) ENGINE=MYISAM; 61LOCK TABLE t1 WRITE; 62ALTER TABLE t1 DISABLE KEYS; 63# Without patch, the SHOW command hangs. 64SHOW SESSION VARIABLES LIKE 'FOREIGN_KEY_CHECKS'; 65Variable_name Value 66foreign_key_checks ON 67# Wihout patch, the SELECT from P_S hangs. 68SELECT * FROM performance_schema.global_variables WHERE variable_name="read_only"; 69VARIABLE_NAME VARIABLE_VALUE 70read_only OFF 71# Test added for coverage (Querying from I_S) 72SET GLOBAL show_compatibility_56= @saved_show_compatibility_56; 73SHOW SESSION VARIABLES LIKE 'FOREIGN_KEY_CHECKS'; 74Variable_name Value 75foreign_key_checks ON 76# Clean up. 77UNLOCK TABLES; 78DROP TABLE t1; 79