145 lines
3.6 KiB
PHP
145 lines
3.6 KiB
PHP
# Help file for avoid duplicated tests of different DEFAULT functions
|
|
#
|
|
# Needs $get_handler_status_counts defined
|
|
# normally as:
|
|
# let $get_handler_status_counts=
|
|
# SELECT * FROM performance_schema.session_status
|
|
# WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
|
|
#
|
|
# Needs
|
|
# - connection monitor
|
|
# - connection default
|
|
|
|
# And a table t3 where a is a DATETIME/DATE/TIME/TIMESTAMP column
|
|
# and b is a char/varchar() column.
|
|
|
|
FLUSH STATUS;
|
|
SET TIMESTAMP = 1234567890;
|
|
INSERT INTO t3 (a) VALUES (NOW());
|
|
|
|
connection monitor;
|
|
--source include/get_handler_status_counts.inc
|
|
connection default;
|
|
|
|
FLUSH STATUS;
|
|
SET TIMESTAMP = 1234567891;
|
|
INSERT INTO t3 VALUES ();
|
|
|
|
connection monitor;
|
|
--source include/get_handler_status_counts.inc
|
|
connection default;
|
|
|
|
FLUSH STATUS;
|
|
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00');
|
|
|
|
connection monitor;
|
|
--source include/get_handler_status_counts.inc
|
|
connection default;
|
|
|
|
FLUSH STATUS;
|
|
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
|
|
|
|
connection monitor;
|
|
--source include/get_handler_status_counts.inc
|
|
connection default;
|
|
|
|
FLUSH STATUS;
|
|
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
|
|
|
|
connection monitor;
|
|
--source include/get_handler_status_counts.inc
|
|
connection default;
|
|
|
|
--echo # 2 writes
|
|
FLUSH STATUS;
|
|
SET TIMESTAMP = 1234567892;
|
|
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
|
|
ON DUPLICATE KEY UPDATE b = "DUP_KEY";
|
|
|
|
connection monitor;
|
|
--source include/get_handler_status_counts.inc
|
|
connection default;
|
|
|
|
if ($default_update)
|
|
{
|
|
--echo # No pruning due to DEFAULT function on partitioning column
|
|
--echo # 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
|
|
--echo # 1 delete + 1 write due to moved to different partition
|
|
}
|
|
if (!$default_update)
|
|
{
|
|
--echo # 1 read_key + 1 update (same partition)
|
|
--echo # 1 (failed) write
|
|
}
|
|
FLUSH STATUS;
|
|
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01')
|
|
ON DUPLICATE KEY UPDATE a = '2011-01-01 00:00:05', b = "DUP_KEY2";
|
|
|
|
connection monitor;
|
|
--source include/get_handler_status_counts.inc
|
|
connection default;
|
|
|
|
--echo # No pruning due to updating partitioning field.
|
|
--echo # 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
|
|
--echo # 1 delete + 1 write due to moved to different partition
|
|
FLUSH STATUS;
|
|
SET TIMESTAMP = 1234567893;
|
|
UPDATE t3 SET b = 'Updated' WHERE a = '2011-01-01 00:00:02';
|
|
|
|
connection monitor;
|
|
--source include/get_handler_status_counts.inc
|
|
connection default;
|
|
|
|
if ($default_update)
|
|
{
|
|
--echo # No pruning due to DEFAULT function on partitioning column
|
|
--echo # 2 read_key + 1 read_rnd (1 read_key due to index lookup,
|
|
--echo # 1 read_rnd + 1 read_key due to positioning before update)
|
|
--echo # 1 delete + 1 write due to moved to different partition
|
|
--echo # + 1 (failed) write
|
|
}
|
|
if (!$default_update)
|
|
{
|
|
--echo # 1 read_key + 1 update (same partition)
|
|
--echo # 1 (failed) write
|
|
}
|
|
FLUSH STATUS;
|
|
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace1');
|
|
|
|
connection monitor;
|
|
--source include/get_handler_status_counts.inc
|
|
connection default;
|
|
|
|
FLUSH STATUS;
|
|
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace2');
|
|
|
|
connection monitor;
|
|
--source include/get_handler_status_counts.inc
|
|
connection default;
|
|
|
|
--echo # 1 read_key + 1 update + 1 failed write
|
|
|
|
--echo #
|
|
--echo # Test of replace of default PK (delete might be needed first)
|
|
--echo #
|
|
DELETE FROM t3 WHERE a = 0;
|
|
FLUSH STATUS;
|
|
SET TIMESTAMP = 1234567894;
|
|
REPLACE INTO t3 (b) VALUES ('Replace3');
|
|
|
|
connection monitor;
|
|
--source include/get_handler_status_counts.inc
|
|
connection default;
|
|
|
|
FLUSH STATUS;
|
|
SET TIMESTAMP = 1234567894;
|
|
REPLACE INTO t3 (b) VALUES ('Replace4');
|
|
|
|
connection monitor;
|
|
--source include/get_handler_status_counts.inc
|
|
connection default;
|
|
|
|
--echo # 1 read_key + 1 update + 1 failed write
|
|
--sorted_result
|
|
SELECT * FROM t3;
|