--source include/big_test.inc # Disable in valgrind because of timeout --source include/not_valgrind.inc --source include/have_innodb_16k.inc --source include/have_debug.inc --source include/not_crashrep.inc --echo # --echo # This test tests various DML operations and their effect on the tablespaces --echo # --echo # --echo # Test with tables using general tablespaces --echo # --echo # --echo # Scenario-1: Create a general tablespace without autoextend_size attribute and validate that the allocation --echo # happens using the old allocation logic --echo # CREATE TABLESPACE myspace; CREATE TABLE tsp(c1 INT, c2 TEXT) TABLESPACE myspace; let $row_count = 1; let $table_name = tsp; --source suite/innodb/include/innodb_autoextend_dml.inc SELECT COUNT(*) FROM tsp; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; let $row_count = 1; let $table_name = tsp; --source suite/innodb/include/innodb_autoextend_dml.inc SELECT COUNT(*) FROM tsp; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; DROP TABLE tsp; DROP TABLESPACE myspace; --echo # --echo # Scenario-2: Create a general tablespace without autoextend_size attribute and validate --echo # that the allocation happens as per the old logic until autoextend_size attribute is --echo # changed for the tablespace --echo # CREATE TABLESPACE myspace; --echo # Without the autoextend_size attribute, the tablespace should extend by adding only --echo # a few pages CREATE TABLE tsp(c1 INT, c2 TEXT) TABLESPACE myspace; let $row_count = 1; let $table_name = tsp; --source suite/innodb/include/innodb_autoextend_dml.inc SELECT COUNT(*) FROM tsp; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; ALTER TABLESPACE myspace AUTOEXTEND_SIZE 4M; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; --echo # Tablespace should be extended to make it's size same as the autoextend_size let $row_count = 1; let $table_name = tsp; --source suite/innodb/include/innodb_autoextend_dml.inc SELECT COUNT(*) FROM tsp; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; --echo # Tablespace should extend by a multiple of autoextend_size let $row_count = 10; let $table_name = tsp; --source suite/innodb/include/innodb_autoextend_dml.inc SELECT COUNT(*) FROM tsp; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; DROP TABLE tsp; DROP TABLESPACE myspace; --echo # --echo # Scenario-3: Test allocation with partitioned and sub-partitioned tables --echo # CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE (c1) ( PARTITION p0 VALUES LESS THAN (20), PARTITION p1 VALUES LESS THAN (50), PARTITION p2 VALUES LESS THAN maxvalue); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; DELIMITER |; CREATE PROCEDURE bulk_insert() BEGIN DECLARE i INT DEFAULT 1; WHILE i < 10000 DO INSERT INTO tpart VALUES(i / 100, 'abcdefgh'); SET i = i + 1; END WHILE; END | DELIMITER ;| call bulk_insert(); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; SELECT COUNT(*) FROM tpart; DROP TABLE tpart; CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE (c1) SUBPARTITION BY HASH (c1) SUBPARTITIONS 3 ( PARTITION p1 values less than (10), PARTITION p2 values less than (20), PARTITION p3 values less than maxvalue); call bulk_insert(); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; SELECT COUNT(*) FROM tpart; DROP TABLE tpart; DROP PROCEDURE bulk_insert; --echo # --echo # Scenario-4: Test truncate table with non-partitioned table --echo # CREATE TABLE mytable (c1 INT) AUTOEXTEND_SIZE 4M; INSERT INTO mytable VALUES(1); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; TRUNCATE TABLE mytable; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; DROP TABLE mytable; --echo # --echo # Scenario-5: Test truncate table with partitioned and sub-partitioned table --echo # CREATE TABLE tpart (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE (c1) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN maxvalue); INSERT INTO tpart VALUES (99, repeat('a', 10000)), (199, repeat('b', 10000)), (299, repeat('c', 10000)); SELECT COUNT(*) FROM tpart; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; TRUNCATE TABLE tpart; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; DROP TABLE tpart; --echo # Test truncate table with sub-partitioned table CREATE TABLE tsubpart (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE (c1) SUBPARTITION BY HASH (c1) SUBPARTITIONS 3 ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN maxvalue); INSERT INTO tsubpart VALUES (99, repeat('a', 10000)), (199, repeat('b', 10000)), (299, repeat('c', 10000)); SELECT COUNT(*) FROM tsubpart; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart%'; TRUNCATE TABLE tsubpart; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart%'; DROP TABLE tsubpart; --echo # --echo # Scenario-6: Test truncate table with explicit tablespace --echo # CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd' AUTOEXTEND_SIZE 4M; CREATE TABLE mytable (c1 INT) TABLESPACE myspace; INSERT INTO mytable VALUES (1); SELECT COUNT(*) FROM mytable; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; TRUNCATE TABLE mytable; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; DROP TABLE mytable; DROP TABLESPACE myspace; --echo # --echo # Scenario-7: Test crash recovery and verify that the autoextend_size value --echo # is reinstated after recovery --echo # CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd' AUTOEXTEND_SIZE 8m; --echo # The initial size of the tablespace should be same as the autoextend_size SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; CREATE TABLE tsp(c1 int, c2 longblob) TABLESPACE myspace; --echo # Crash the server while attempting to allocate more space INSERT INTO tsp VALUES(1, repeat(1, 4 * 1024 * 1024)); --source include/expect_crash.inc SET DEBUG="+d, fsp_crash_before_space_extend"; --error 2013 INSERT INTO tsp VALUES(1, repeat(1, 4 * 1024 * 1024)); --echo # Restart mysqld after the crash and reconnect --source include/start_mysqld.inc # Verify the current size of the tablespace SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; # Insert another row and verify that the space has been extended by the autoextend_size value INSERT INTO tsp VALUES(1, repeat(1, 4 * 1024 * 1024)); SELECT COUNT(*) FROM tsp; --echo # Verify new file size is a multiple of autoextend_size SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; DROP TABLE tsp; DROP TABLESPACE myspace; --echo # --echo # Scenario-8: Test crash recovery when the server is started with --skip-innodb-validate-tablespace-paths --echo # option and verify that the server respects the autoextend_size value --echo # CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd' AUTOEXTEND_SIZE 8m; --echo # The initialize size of the tablespace should be same as the autoextend_size SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; CREATE TABLE tsp(c1 int, c2 longblob) TABLESPACE myspace; --echo # Crash the server while attempting to allocate more space INSERT INTO tsp VALUES(1, repeat(1, 4 * 1024 * 1024)); SET DEBUG="+d, fsp_crash_before_space_extend"; --source include/expect_crash.inc --error 2013 INSERT INTO tsp VALUES(1, repeat(1, 4 * 1024 * 1024)); --echo # Restart mysqld with --skip-innodb-validate-tablespace-paths option --echo # after the crash and reconnect --let $restart_parameters = restart: --skip-innodb-validate-tablespace-paths --source include/start_mysqld.inc # Verify the current size of the tablespace SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; # Insert couple of more rows and verify that the space has been extended by the autoextend_size value INSERT INTO tsp VALUES(1, repeat(1, 4 * 1024 * 1024)); SELECT COUNT(*) FROM tsp; --echo # Verify new file size is a multiple of autoextend_size SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%myspace%'; DROP TABLE tsp; DROP TABLESPACE myspace; # Restart the server without --skip-innodb-validate-tablespace-paths option --let $restart_parameters = --source include/restart_mysqld.inc --echo # --echo # Scenario-9: Test concurrent inserts and ALTER TABLESPACE ... AUTOEXTEND_SIZE --echo # CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd' AUTOEXTEND_SIZE 4m; CREATE TABLE tsp(id int, l longblob, v varchar(100)) TABLESPACE myspace; delimiter |; CREATE PROCEDURE update_aes() BEGIN DECLARE i INT DEFAULT 0; WHILE i < 1000 DO ALTER TABLESPACE myspace AUTOEXTEND_SIZE=4m; ALTER TABLESPACE myspace AUTOEXTEND_SIZE=64m; ALTER TABLESPACE myspace AUTOEXTEND_SIZE=16m; ALTER TABLESPACE myspace AUTOEXTEND_SIZE=8m; ALTER TABLESPACE myspace AUTOEXTEND_SIZE=32m; SET i = i + 1; END WHILE; END | CREATE PROCEDURE insert_data() BEGIN DECLARE i INT DEFAULT 0; WHILE i < 10000 DO INSERT INTO tsp (l) VALUES (repeat(2, 1024*16)); SET i = i + 1; END WHILE; END | delimiter ;| # Create concurrent connections connect (conn1, localhost, root,,); connect (conn2, localhost, root,,); # Connection to conn1 --connection conn1 --SEND CALL insert_data(); # Connection to conn2 --connection conn2 CALL update_aes(); --connection conn1 --reap --connection default --disconnect conn1 --disconnect conn2 SELECT COUNT(*) FROM tsp; DROP TABLE tsp; DROP TABLESPACE myspace; DROP PROCEDURE update_aes; DROP PROCEDURE insert_data;