SET @max_row = 20; SET @@session.storage_engine = 'InnoDB'; #------------------------------------------------------------------------ # 0. Setting of auxiliary variables + Creation of an auxiliary tables # needed in many testcases #------------------------------------------------------------------------ SELECT @max_row DIV 2 INTO @max_row_div2; SELECT @max_row DIV 3 INTO @max_row_div3; SELECT @max_row DIV 4 INTO @max_row_div4; SET @max_int_4 = 2147483647; DROP TABLE IF EXISTS t0_template; CREATE TABLE t0_template ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) , PRIMARY KEY(f_int1)) ENGINE = MEMORY; # Logging of <max_row> INSERTs into t0_template suppressed DROP TABLE IF EXISTS t0_definition; CREATE TABLE t0_definition ( state CHAR(3), create_command VARBINARY(5000), file_list VARBINARY(10000), PRIMARY KEY (state) ) ENGINE = MEMORY; DROP TABLE IF EXISTS t0_aux; CREATE TABLE t0_aux ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) ENGINE = MEMORY; SET AUTOCOMMIT= 1; SET @@session.sql_mode= ''; # End of basic preparations needed for all tests #----------------------------------------------- #======================================================================== # Checks where the engine is assigned on all supported (CREATE TABLE # statement) positions + basic operations on the tables # Storage engine mixups are currently (2005-12-23) not supported #======================================================================== DROP TABLE IF EXISTS t1; #------------------------------------------------------------------------ # 1 Assignment of storage engine just after column list only #------------------------------------------------------------------------ CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) ENGINE = 'InnoDB' PARTITION BY HASH(f_int1) PARTITIONS 2; INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; # Start usability test (inc/partition_check.inc) create_command SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_int1` int(11) DEFAULT NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) PARTITIONS 2 */ # check prerequisites-1 success: 1 # check COUNT(*) success: 1 # check MIN/MAX(f_int1) success: 1 # check MIN/MAX(f_int2) success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'delete me' FROM t0_template WHERE f_int1 IN (2,3); # check prerequisites-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'delete me'; # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE # check read via f_int1 success: 1 # check read via f_int2 success: 1 # check multiple-1 success: 1 DELETE FROM t1 WHERE MOD(f_int1,3) = 0; # check multiple-2 success: 1 INSERT INTO t1 SELECT * FROM t0_template WHERE MOD(f_int1,3) = 0; # check multiple-3 success: 1 UPDATE t1 SET f_int1 = f_int1 + @max_row WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 AND @max_row_div2 + @max_row_div4; # check multiple-4 success: 1 DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row AND @max_row_div2 + @max_row_div4 + @max_row; # check multiple-5 success: 1 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-1 success: 1 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-2 success: 1 SELECT MIN(f_int1) INTO @cur_value1 FROM t1; SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value2 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#'; # check single-3 success: 1 SET @cur_value1= -1; SELECT MAX(f_int1) INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value1 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#'; # check single-4 success: 1 SELECT MAX(f_int1) INTO @cur_value FROM t1; DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#'; # check single-5 success: 1 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#'; # check single-6 success: 1 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##'; # check single-7 success: 1 DELETE FROM t1 WHERE f_charbig = '#2147483647##'; DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0; INSERT t1 SET f_int1 = 0 , f_int2 = 0, f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR), f_charbig = '#NULL#'; INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row, f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR), f_charbig = '#NULL#'; # check null success: 1 # check null-1 success: 1 UPDATE t1 SET f_int1 = -@max_row WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-2 success: 1 UPDATE t1 SET f_int1 = NULL WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-3 success: 1 DELETE FROM t1 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-4 success: 1 DELETE FROM t1 WHERE f_int1 = 0 AND f_int2 = 0 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR) AND f_charbig = '#NULL#'; SET AUTOCOMMIT= 0; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-1 success: 1 COMMIT WORK; # check transactions-2 success: 1 ROLLBACK WORK; # check transactions-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; ROLLBACK WORK; # check transactions-4 success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-5 success: 1 ROLLBACK WORK; # check transactions-6 success: 1 # INFO: Storage engine used for t1 seems to be transactional. COMMIT; # check transactions-7 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; SET @@session.sql_mode = 'traditional'; SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1, '', '', 'was inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; ERROR 22012: Division by 0 COMMIT; # check transactions-8 success: 1 # INFO: Storage engine used for t1 seems to be able to revert # changes made by the failing statement. SET @@session.sql_mode = ''; SET AUTOCOMMIT= 1; DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; UPDATE t1 SET f_charbig = REPEAT('b', 1000); # check special-1 success: 1 UPDATE t1 SET f_charbig = ''; # check special-2 success: 1 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'==='); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-1 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-2 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-3 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-4 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-5 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-6 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-7 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-8 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = old.f_int1 + @max_row, new.f_int2 = old.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-9 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = new.f_int1 + @max_row, new.f_int2 = new.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-10 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-11 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_char1, f_char2, f_charbig) SELECT CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-12 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 <some_value> OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK # check layout success: 1 REPAIR TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 repair note The storage engine for the table doesn't support repair # check layout success: 1 TRUNCATE t1; # check TRUNCATE success: 1 # check layout success: 1 # End usability test (inc/partition_check.inc) DROP TABLE t1; #------------------------------------------------------------------------ # 2 Assignment of storage engine just after partition or subpartition # name only #------------------------------------------------------------------------ CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) PARTITION BY HASH(f_int1) ( PARTITION part1 STORAGE ENGINE = 'InnoDB', PARTITION part2 STORAGE ENGINE = 'InnoDB' ); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; # Start usability test (inc/partition_check.inc) create_command SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_int1` int(11) DEFAULT NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) (PARTITION part1 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB) */ # check prerequisites-1 success: 1 # check COUNT(*) success: 1 # check MIN/MAX(f_int1) success: 1 # check MIN/MAX(f_int2) success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'delete me' FROM t0_template WHERE f_int1 IN (2,3); # check prerequisites-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'delete me'; # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE # check read via f_int1 success: 1 # check read via f_int2 success: 1 # check multiple-1 success: 1 DELETE FROM t1 WHERE MOD(f_int1,3) = 0; # check multiple-2 success: 1 INSERT INTO t1 SELECT * FROM t0_template WHERE MOD(f_int1,3) = 0; # check multiple-3 success: 1 UPDATE t1 SET f_int1 = f_int1 + @max_row WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 AND @max_row_div2 + @max_row_div4; # check multiple-4 success: 1 DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row AND @max_row_div2 + @max_row_div4 + @max_row; # check multiple-5 success: 1 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-1 success: 1 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-2 success: 1 SELECT MIN(f_int1) INTO @cur_value1 FROM t1; SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value2 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#'; # check single-3 success: 1 SET @cur_value1= -1; SELECT MAX(f_int1) INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value1 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#'; # check single-4 success: 1 SELECT MAX(f_int1) INTO @cur_value FROM t1; DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#'; # check single-5 success: 1 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#'; # check single-6 success: 1 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##'; # check single-7 success: 1 DELETE FROM t1 WHERE f_charbig = '#2147483647##'; DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0; INSERT t1 SET f_int1 = 0 , f_int2 = 0, f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR), f_charbig = '#NULL#'; INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row, f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR), f_charbig = '#NULL#'; # check null success: 1 # check null-1 success: 1 UPDATE t1 SET f_int1 = -@max_row WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-2 success: 1 UPDATE t1 SET f_int1 = NULL WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-3 success: 1 DELETE FROM t1 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-4 success: 1 DELETE FROM t1 WHERE f_int1 = 0 AND f_int2 = 0 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR) AND f_charbig = '#NULL#'; SET AUTOCOMMIT= 0; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-1 success: 1 COMMIT WORK; # check transactions-2 success: 1 ROLLBACK WORK; # check transactions-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; ROLLBACK WORK; # check transactions-4 success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-5 success: 1 ROLLBACK WORK; # check transactions-6 success: 1 # INFO: Storage engine used for t1 seems to be transactional. COMMIT; # check transactions-7 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; SET @@session.sql_mode = 'traditional'; SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1, '', '', 'was inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; ERROR 22012: Division by 0 COMMIT; # check transactions-8 success: 1 # INFO: Storage engine used for t1 seems to be able to revert # changes made by the failing statement. SET @@session.sql_mode = ''; SET AUTOCOMMIT= 1; DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; UPDATE t1 SET f_charbig = REPEAT('b', 1000); # check special-1 success: 1 UPDATE t1 SET f_charbig = ''; # check special-2 success: 1 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'==='); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-1 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-2 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-3 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-4 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-5 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-6 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-7 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-8 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = old.f_int1 + @max_row, new.f_int2 = old.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-9 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = new.f_int1 + @max_row, new.f_int2 = new.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-10 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-11 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_char1, f_char2, f_charbig) SELECT CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-12 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 <some_value> OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK # check layout success: 1 REPAIR TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 repair note The storage engine for the table doesn't support repair # check layout success: 1 TRUNCATE t1; # check TRUNCATE success: 1 # check layout success: 1 # End usability test (inc/partition_check.inc) DROP TABLE t1; CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) ( PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB', SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB', SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB') ); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; # Start usability test (inc/partition_check.inc) create_command SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_int1` int(11) DEFAULT NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE = InnoDB)) */ # check prerequisites-1 success: 1 # check COUNT(*) success: 1 # check MIN/MAX(f_int1) success: 1 # check MIN/MAX(f_int2) success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'delete me' FROM t0_template WHERE f_int1 IN (2,3); # check prerequisites-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'delete me'; # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE # check read via f_int1 success: 1 # check read via f_int2 success: 1 # check multiple-1 success: 1 DELETE FROM t1 WHERE MOD(f_int1,3) = 0; # check multiple-2 success: 1 INSERT INTO t1 SELECT * FROM t0_template WHERE MOD(f_int1,3) = 0; # check multiple-3 success: 1 UPDATE t1 SET f_int1 = f_int1 + @max_row WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 AND @max_row_div2 + @max_row_div4; # check multiple-4 success: 1 DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row AND @max_row_div2 + @max_row_div4 + @max_row; # check multiple-5 success: 1 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-1 success: 1 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-2 success: 1 SELECT MIN(f_int1) INTO @cur_value1 FROM t1; SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value2 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#'; # check single-3 success: 1 SET @cur_value1= -1; SELECT MAX(f_int1) INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value1 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#'; # check single-4 success: 1 SELECT MAX(f_int1) INTO @cur_value FROM t1; DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#'; # check single-5 success: 1 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#'; # check single-6 success: 1 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##'; ERROR HY000: Table has no partition for value 2147483647 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0; INSERT t1 SET f_int1 = 0 , f_int2 = 0, f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR), f_charbig = '#NULL#'; INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row, f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR), f_charbig = '#NULL#'; # check null success: 1 # check null-1 success: 1 UPDATE t1 SET f_int1 = -@max_row WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-2 success: 1 UPDATE t1 SET f_int1 = NULL WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-3 success: 1 DELETE FROM t1 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-4 success: 1 DELETE FROM t1 WHERE f_int1 = 0 AND f_int2 = 0 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR) AND f_charbig = '#NULL#'; SET AUTOCOMMIT= 0; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-1 success: 1 COMMIT WORK; # check transactions-2 success: 1 ROLLBACK WORK; # check transactions-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; ROLLBACK WORK; # check transactions-4 success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-5 success: 1 ROLLBACK WORK; # check transactions-6 success: 1 # INFO: Storage engine used for t1 seems to be transactional. COMMIT; # check transactions-7 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; SET @@session.sql_mode = 'traditional'; SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1, '', '', 'was inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; ERROR 22012: Division by 0 COMMIT; # check transactions-8 success: 1 # INFO: Storage engine used for t1 seems to be able to revert # changes made by the failing statement. SET @@session.sql_mode = ''; SET AUTOCOMMIT= 1; DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; UPDATE t1 SET f_charbig = REPEAT('b', 1000); # check special-1 success: 1 UPDATE t1 SET f_charbig = ''; # check special-2 success: 1 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'==='); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-1 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-2 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-3 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-4 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-5 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-6 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-7 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-8 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = old.f_int1 + @max_row, new.f_int2 = old.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-9 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = new.f_int1 + @max_row, new.f_int2 = new.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-10 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-11 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_char1, f_char2, f_charbig) SELECT CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-12 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 <some_value> OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK # check layout success: 1 REPAIR TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 repair note The storage engine for the table doesn't support repair # check layout success: 1 TRUNCATE t1; # check TRUNCATE success: 1 # check layout success: 1 # End usability test (inc/partition_check.inc) DROP TABLE t1; #------------------------------------------------------------------------ # 3 Some but not all named partitions or subpartitions get a storage # engine assigned #------------------------------------------------------------------------ CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) PARTITION BY HASH(f_int1) ( PARTITION part1 STORAGE ENGINE = 'InnoDB', PARTITION part2 ); ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) PARTITION BY HASH(f_int1) ( PARTITION part1 , PARTITION part2 STORAGE ENGINE = 'InnoDB' ); ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) ( PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11, SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB', SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB') ); ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) ( PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB', SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21, SUBPARTITION subpart22 ) ); ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) ENGINE = 'InnoDB' PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) ( PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB', SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21, SUBPARTITION subpart22 ) ); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; # Start usability test (inc/partition_check.inc) create_command SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_int1` int(11) DEFAULT NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE = InnoDB)) */ # check prerequisites-1 success: 1 # check COUNT(*) success: 1 # check MIN/MAX(f_int1) success: 1 # check MIN/MAX(f_int2) success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'delete me' FROM t0_template WHERE f_int1 IN (2,3); # check prerequisites-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'delete me'; # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE # check read via f_int1 success: 1 # check read via f_int2 success: 1 # check multiple-1 success: 1 DELETE FROM t1 WHERE MOD(f_int1,3) = 0; # check multiple-2 success: 1 INSERT INTO t1 SELECT * FROM t0_template WHERE MOD(f_int1,3) = 0; # check multiple-3 success: 1 UPDATE t1 SET f_int1 = f_int1 + @max_row WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 AND @max_row_div2 + @max_row_div4; # check multiple-4 success: 1 DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row AND @max_row_div2 + @max_row_div4 + @max_row; # check multiple-5 success: 1 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-1 success: 1 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-2 success: 1 SELECT MIN(f_int1) INTO @cur_value1 FROM t1; SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value2 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#'; # check single-3 success: 1 SET @cur_value1= -1; SELECT MAX(f_int1) INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value1 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#'; # check single-4 success: 1 SELECT MAX(f_int1) INTO @cur_value FROM t1; DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#'; # check single-5 success: 1 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#'; # check single-6 success: 1 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##'; ERROR HY000: Table has no partition for value 2147483647 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0; INSERT t1 SET f_int1 = 0 , f_int2 = 0, f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR), f_charbig = '#NULL#'; INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row, f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR), f_charbig = '#NULL#'; # check null success: 1 # check null-1 success: 1 UPDATE t1 SET f_int1 = -@max_row WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-2 success: 1 UPDATE t1 SET f_int1 = NULL WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-3 success: 1 DELETE FROM t1 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-4 success: 1 DELETE FROM t1 WHERE f_int1 = 0 AND f_int2 = 0 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR) AND f_charbig = '#NULL#'; SET AUTOCOMMIT= 0; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-1 success: 1 COMMIT WORK; # check transactions-2 success: 1 ROLLBACK WORK; # check transactions-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; ROLLBACK WORK; # check transactions-4 success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-5 success: 1 ROLLBACK WORK; # check transactions-6 success: 1 # INFO: Storage engine used for t1 seems to be transactional. COMMIT; # check transactions-7 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; SET @@session.sql_mode = 'traditional'; SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1, '', '', 'was inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; ERROR 22012: Division by 0 COMMIT; # check transactions-8 success: 1 # INFO: Storage engine used for t1 seems to be able to revert # changes made by the failing statement. SET @@session.sql_mode = ''; SET AUTOCOMMIT= 1; DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; UPDATE t1 SET f_charbig = REPEAT('b', 1000); # check special-1 success: 1 UPDATE t1 SET f_charbig = ''; # check special-2 success: 1 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'==='); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-1 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-2 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-3 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-4 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-5 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-6 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-7 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-8 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = old.f_int1 + @max_row, new.f_int2 = old.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-9 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = new.f_int1 + @max_row, new.f_int2 = new.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-10 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-11 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_char1, f_char2, f_charbig) SELECT CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-12 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 <some_value> OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK # check layout success: 1 REPAIR TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 repair note The storage engine for the table doesn't support repair # check layout success: 1 TRUNCATE t1; # check TRUNCATE success: 1 # check layout success: 1 # End usability test (inc/partition_check.inc) DROP TABLE t1; #------------------------------------------------------------------------ # 4 Storage engine assignment after partition name + after name of # subpartitions belonging to another partition #------------------------------------------------------------------------ CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) ( PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11, SUBPARTITION subpart12), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB', SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB') ); ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) ENGINE = 'InnoDB' PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) ( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'InnoDB' (SUBPARTITION subpart11, SUBPARTITION subpart12), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21, SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB') ); DROP TABLE t1; CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) ( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'InnoDB' (SUBPARTITION subpart11, SUBPARTITION subpart12), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB', SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB') ); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; # Start usability test (inc/partition_check.inc) create_command SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_int1` int(11) DEFAULT NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE = InnoDB)) */ # check prerequisites-1 success: 1 # check COUNT(*) success: 1 # check MIN/MAX(f_int1) success: 1 # check MIN/MAX(f_int2) success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'delete me' FROM t0_template WHERE f_int1 IN (2,3); # check prerequisites-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'delete me'; # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE # check read via f_int1 success: 1 # check read via f_int2 success: 1 # check multiple-1 success: 1 DELETE FROM t1 WHERE MOD(f_int1,3) = 0; # check multiple-2 success: 1 INSERT INTO t1 SELECT * FROM t0_template WHERE MOD(f_int1,3) = 0; # check multiple-3 success: 1 UPDATE t1 SET f_int1 = f_int1 + @max_row WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 AND @max_row_div2 + @max_row_div4; # check multiple-4 success: 1 DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row AND @max_row_div2 + @max_row_div4 + @max_row; # check multiple-5 success: 1 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-1 success: 1 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-2 success: 1 SELECT MIN(f_int1) INTO @cur_value1 FROM t1; SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value2 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#'; # check single-3 success: 1 SET @cur_value1= -1; SELECT MAX(f_int1) INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value1 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#'; # check single-4 success: 1 SELECT MAX(f_int1) INTO @cur_value FROM t1; DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#'; # check single-5 success: 1 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#'; # check single-6 success: 1 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##'; ERROR HY000: Table has no partition for value 2147483647 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0; INSERT t1 SET f_int1 = 0 , f_int2 = 0, f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR), f_charbig = '#NULL#'; INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row, f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR), f_charbig = '#NULL#'; # check null success: 1 # check null-1 success: 1 UPDATE t1 SET f_int1 = -@max_row WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-2 success: 1 UPDATE t1 SET f_int1 = NULL WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-3 success: 1 DELETE FROM t1 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-4 success: 1 DELETE FROM t1 WHERE f_int1 = 0 AND f_int2 = 0 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR) AND f_charbig = '#NULL#'; SET AUTOCOMMIT= 0; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-1 success: 1 COMMIT WORK; # check transactions-2 success: 1 ROLLBACK WORK; # check transactions-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; ROLLBACK WORK; # check transactions-4 success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-5 success: 1 ROLLBACK WORK; # check transactions-6 success: 1 # INFO: Storage engine used for t1 seems to be transactional. COMMIT; # check transactions-7 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; SET @@session.sql_mode = 'traditional'; SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1, '', '', 'was inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; ERROR 22012: Division by 0 COMMIT; # check transactions-8 success: 1 # INFO: Storage engine used for t1 seems to be able to revert # changes made by the failing statement. SET @@session.sql_mode = ''; SET AUTOCOMMIT= 1; DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; UPDATE t1 SET f_charbig = REPEAT('b', 1000); # check special-1 success: 1 UPDATE t1 SET f_charbig = ''; # check special-2 success: 1 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'==='); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-1 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-2 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-3 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-4 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-5 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-6 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-7 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-8 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = old.f_int1 + @max_row, new.f_int2 = old.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-9 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = new.f_int1 + @max_row, new.f_int2 = new.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-10 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-11 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_char1, f_char2, f_charbig) SELECT CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-12 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 <some_value> OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK # check layout success: 1 REPAIR TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 repair note The storage engine for the table doesn't support repair # check layout success: 1 TRUNCATE t1; # check TRUNCATE success: 1 # check layout success: 1 # End usability test (inc/partition_check.inc) DROP TABLE t1; CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) ( PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB', SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'), PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = 'InnoDB' (SUBPARTITION subpart21 ENGINE = 'InnoDB', SUBPARTITION subpart22) ); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; # Start usability test (inc/partition_check.inc) create_command SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_int1` int(11) DEFAULT NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE = InnoDB)) */ # check prerequisites-1 success: 1 # check COUNT(*) success: 1 # check MIN/MAX(f_int1) success: 1 # check MIN/MAX(f_int2) success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'delete me' FROM t0_template WHERE f_int1 IN (2,3); # check prerequisites-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'delete me'; # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE # check read via f_int1 success: 1 # check read via f_int2 success: 1 # check multiple-1 success: 1 DELETE FROM t1 WHERE MOD(f_int1,3) = 0; # check multiple-2 success: 1 INSERT INTO t1 SELECT * FROM t0_template WHERE MOD(f_int1,3) = 0; # check multiple-3 success: 1 UPDATE t1 SET f_int1 = f_int1 + @max_row WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 AND @max_row_div2 + @max_row_div4; # check multiple-4 success: 1 DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row AND @max_row_div2 + @max_row_div4 + @max_row; # check multiple-5 success: 1 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-1 success: 1 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-2 success: 1 SELECT MIN(f_int1) INTO @cur_value1 FROM t1; SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value2 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#'; # check single-3 success: 1 SET @cur_value1= -1; SELECT MAX(f_int1) INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value1 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#'; # check single-4 success: 1 SELECT MAX(f_int1) INTO @cur_value FROM t1; DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#'; # check single-5 success: 1 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#'; # check single-6 success: 1 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##'; ERROR HY000: Table has no partition for value 2147483647 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0; INSERT t1 SET f_int1 = 0 , f_int2 = 0, f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR), f_charbig = '#NULL#'; INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row, f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR), f_charbig = '#NULL#'; # check null success: 1 # check null-1 success: 1 UPDATE t1 SET f_int1 = -@max_row WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-2 success: 1 UPDATE t1 SET f_int1 = NULL WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-3 success: 1 DELETE FROM t1 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-4 success: 1 DELETE FROM t1 WHERE f_int1 = 0 AND f_int2 = 0 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR) AND f_charbig = '#NULL#'; SET AUTOCOMMIT= 0; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-1 success: 1 COMMIT WORK; # check transactions-2 success: 1 ROLLBACK WORK; # check transactions-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; ROLLBACK WORK; # check transactions-4 success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-5 success: 1 ROLLBACK WORK; # check transactions-6 success: 1 # INFO: Storage engine used for t1 seems to be transactional. COMMIT; # check transactions-7 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; SET @@session.sql_mode = 'traditional'; SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1, '', '', 'was inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; ERROR 22012: Division by 0 COMMIT; # check transactions-8 success: 1 # INFO: Storage engine used for t1 seems to be able to revert # changes made by the failing statement. SET @@session.sql_mode = ''; SET AUTOCOMMIT= 1; DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; UPDATE t1 SET f_charbig = REPEAT('b', 1000); # check special-1 success: 1 UPDATE t1 SET f_charbig = ''; # check special-2 success: 1 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'==='); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-1 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-2 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-3 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-4 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-5 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-6 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-7 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-8 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = old.f_int1 + @max_row, new.f_int2 = old.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-9 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = new.f_int1 + @max_row, new.f_int2 = new.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-10 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-11 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_char1, f_char2, f_charbig) SELECT CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-12 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 <some_value> OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK # check layout success: 1 REPAIR TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 repair note The storage engine for the table doesn't support repair # check layout success: 1 TRUNCATE t1; # check TRUNCATE success: 1 # check layout success: 1 # End usability test (inc/partition_check.inc) DROP TABLE t1; #------------------------------------------------------------------------ # 5 Precedence of storage engine assignments (if there is any) #------------------------------------------------------------------------ # 5.1 Storage engine assignment after column list + after partition # or subpartition name CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) ENGINE = 'InnoDB' PARTITION BY HASH(f_int1) ( PARTITION part1 STORAGE ENGINE = 'InnoDB', PARTITION part2 STORAGE ENGINE = 'InnoDB' ); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; # Start usability test (inc/partition_check.inc) create_command SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_int1` int(11) DEFAULT NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) (PARTITION part1 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB) */ # check prerequisites-1 success: 1 # check COUNT(*) success: 1 # check MIN/MAX(f_int1) success: 1 # check MIN/MAX(f_int2) success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'delete me' FROM t0_template WHERE f_int1 IN (2,3); # check prerequisites-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'delete me'; # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE # check read via f_int1 success: 1 # check read via f_int2 success: 1 # check multiple-1 success: 1 DELETE FROM t1 WHERE MOD(f_int1,3) = 0; # check multiple-2 success: 1 INSERT INTO t1 SELECT * FROM t0_template WHERE MOD(f_int1,3) = 0; # check multiple-3 success: 1 UPDATE t1 SET f_int1 = f_int1 + @max_row WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 AND @max_row_div2 + @max_row_div4; # check multiple-4 success: 1 DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row AND @max_row_div2 + @max_row_div4 + @max_row; # check multiple-5 success: 1 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-1 success: 1 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-2 success: 1 SELECT MIN(f_int1) INTO @cur_value1 FROM t1; SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value2 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#'; # check single-3 success: 1 SET @cur_value1= -1; SELECT MAX(f_int1) INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value1 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#'; # check single-4 success: 1 SELECT MAX(f_int1) INTO @cur_value FROM t1; DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#'; # check single-5 success: 1 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#'; # check single-6 success: 1 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##'; # check single-7 success: 1 DELETE FROM t1 WHERE f_charbig = '#2147483647##'; DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0; INSERT t1 SET f_int1 = 0 , f_int2 = 0, f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR), f_charbig = '#NULL#'; INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row, f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR), f_charbig = '#NULL#'; # check null success: 1 # check null-1 success: 1 UPDATE t1 SET f_int1 = -@max_row WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-2 success: 1 UPDATE t1 SET f_int1 = NULL WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-3 success: 1 DELETE FROM t1 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-4 success: 1 DELETE FROM t1 WHERE f_int1 = 0 AND f_int2 = 0 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR) AND f_charbig = '#NULL#'; SET AUTOCOMMIT= 0; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-1 success: 1 COMMIT WORK; # check transactions-2 success: 1 ROLLBACK WORK; # check transactions-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; ROLLBACK WORK; # check transactions-4 success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-5 success: 1 ROLLBACK WORK; # check transactions-6 success: 1 # INFO: Storage engine used for t1 seems to be transactional. COMMIT; # check transactions-7 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; SET @@session.sql_mode = 'traditional'; SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1, '', '', 'was inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; ERROR 22012: Division by 0 COMMIT; # check transactions-8 success: 1 # INFO: Storage engine used for t1 seems to be able to revert # changes made by the failing statement. SET @@session.sql_mode = ''; SET AUTOCOMMIT= 1; DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; UPDATE t1 SET f_charbig = REPEAT('b', 1000); # check special-1 success: 1 UPDATE t1 SET f_charbig = ''; # check special-2 success: 1 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'==='); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-1 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-2 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-3 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-4 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-5 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-6 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-7 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-8 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = old.f_int1 + @max_row, new.f_int2 = old.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-9 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = new.f_int1 + @max_row, new.f_int2 = new.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-10 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-11 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_char1, f_char2, f_charbig) SELECT CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-12 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 <some_value> OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK # check layout success: 1 REPAIR TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 repair note The storage engine for the table doesn't support repair # check layout success: 1 TRUNCATE t1; # check TRUNCATE success: 1 # check layout success: 1 # End usability test (inc/partition_check.inc) DROP TABLE t1; CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) ( PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB', SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB', SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB') ); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; # Start usability test (inc/partition_check.inc) create_command SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_int1` int(11) DEFAULT NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE = InnoDB)) */ # check prerequisites-1 success: 1 # check COUNT(*) success: 1 # check MIN/MAX(f_int1) success: 1 # check MIN/MAX(f_int2) success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'delete me' FROM t0_template WHERE f_int1 IN (2,3); # check prerequisites-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'delete me'; # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE # check read via f_int1 success: 1 # check read via f_int2 success: 1 # check multiple-1 success: 1 DELETE FROM t1 WHERE MOD(f_int1,3) = 0; # check multiple-2 success: 1 INSERT INTO t1 SELECT * FROM t0_template WHERE MOD(f_int1,3) = 0; # check multiple-3 success: 1 UPDATE t1 SET f_int1 = f_int1 + @max_row WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 AND @max_row_div2 + @max_row_div4; # check multiple-4 success: 1 DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row AND @max_row_div2 + @max_row_div4 + @max_row; # check multiple-5 success: 1 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-1 success: 1 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-2 success: 1 SELECT MIN(f_int1) INTO @cur_value1 FROM t1; SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value2 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#'; # check single-3 success: 1 SET @cur_value1= -1; SELECT MAX(f_int1) INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value1 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#'; # check single-4 success: 1 SELECT MAX(f_int1) INTO @cur_value FROM t1; DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#'; # check single-5 success: 1 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#'; # check single-6 success: 1 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##'; ERROR HY000: Table has no partition for value 2147483647 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0; INSERT t1 SET f_int1 = 0 , f_int2 = 0, f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR), f_charbig = '#NULL#'; INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row, f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR), f_charbig = '#NULL#'; # check null success: 1 # check null-1 success: 1 UPDATE t1 SET f_int1 = -@max_row WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-2 success: 1 UPDATE t1 SET f_int1 = NULL WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-3 success: 1 DELETE FROM t1 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-4 success: 1 DELETE FROM t1 WHERE f_int1 = 0 AND f_int2 = 0 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR) AND f_charbig = '#NULL#'; SET AUTOCOMMIT= 0; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-1 success: 1 COMMIT WORK; # check transactions-2 success: 1 ROLLBACK WORK; # check transactions-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; ROLLBACK WORK; # check transactions-4 success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-5 success: 1 ROLLBACK WORK; # check transactions-6 success: 1 # INFO: Storage engine used for t1 seems to be transactional. COMMIT; # check transactions-7 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; SET @@session.sql_mode = 'traditional'; SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1, '', '', 'was inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; ERROR 22012: Division by 0 COMMIT; # check transactions-8 success: 1 # INFO: Storage engine used for t1 seems to be able to revert # changes made by the failing statement. SET @@session.sql_mode = ''; SET AUTOCOMMIT= 1; DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; UPDATE t1 SET f_charbig = REPEAT('b', 1000); # check special-1 success: 1 UPDATE t1 SET f_charbig = ''; # check special-2 success: 1 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'==='); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-1 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-2 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-3 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-4 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-5 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-6 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-7 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-8 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = old.f_int1 + @max_row, new.f_int2 = old.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-9 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = new.f_int1 + @max_row, new.f_int2 = new.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-10 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-11 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_char1, f_char2, f_charbig) SELECT CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-12 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 <some_value> OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK # check layout success: 1 REPAIR TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 repair note The storage engine for the table doesn't support repair # check layout success: 1 TRUNCATE t1; # check TRUNCATE success: 1 # check layout success: 1 # End usability test (inc/partition_check.inc) DROP TABLE t1; # 6.2 Storage engine assignment after partition name + after # subpartition name # in partition part + in sub partition part CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) ( PARTITION part1 VALUES LESS THAN (10) STORAGE ENGINE = 'InnoDB' (SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB', SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB', SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB') ); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; # Start usability test (inc/partition_check.inc) create_command SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_int1` int(11) DEFAULT NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE = InnoDB)) */ # check prerequisites-1 success: 1 # check COUNT(*) success: 1 # check MIN/MAX(f_int1) success: 1 # check MIN/MAX(f_int2) success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'delete me' FROM t0_template WHERE f_int1 IN (2,3); # check prerequisites-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'delete me'; # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE # check read via f_int1 success: 1 # check read via f_int2 success: 1 # check multiple-1 success: 1 DELETE FROM t1 WHERE MOD(f_int1,3) = 0; # check multiple-2 success: 1 INSERT INTO t1 SELECT * FROM t0_template WHERE MOD(f_int1,3) = 0; # check multiple-3 success: 1 UPDATE t1 SET f_int1 = f_int1 + @max_row WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 AND @max_row_div2 + @max_row_div4; # check multiple-4 success: 1 DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row AND @max_row_div2 + @max_row_div4 + @max_row; # check multiple-5 success: 1 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-1 success: 1 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-2 success: 1 SELECT MIN(f_int1) INTO @cur_value1 FROM t1; SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value2 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#'; # check single-3 success: 1 SET @cur_value1= -1; SELECT MAX(f_int1) INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value1 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#'; # check single-4 success: 1 SELECT MAX(f_int1) INTO @cur_value FROM t1; DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#'; # check single-5 success: 1 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#'; # check single-6 success: 1 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##'; ERROR HY000: Table has no partition for value 2147483647 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0; INSERT t1 SET f_int1 = 0 , f_int2 = 0, f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR), f_charbig = '#NULL#'; INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row, f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR), f_charbig = '#NULL#'; # check null success: 1 # check null-1 success: 1 UPDATE t1 SET f_int1 = -@max_row WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-2 success: 1 UPDATE t1 SET f_int1 = NULL WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-3 success: 1 DELETE FROM t1 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-4 success: 1 DELETE FROM t1 WHERE f_int1 = 0 AND f_int2 = 0 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR) AND f_charbig = '#NULL#'; SET AUTOCOMMIT= 0; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-1 success: 1 COMMIT WORK; # check transactions-2 success: 1 ROLLBACK WORK; # check transactions-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; ROLLBACK WORK; # check transactions-4 success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-5 success: 1 ROLLBACK WORK; # check transactions-6 success: 1 # INFO: Storage engine used for t1 seems to be transactional. COMMIT; # check transactions-7 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; SET @@session.sql_mode = 'traditional'; SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1, '', '', 'was inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; ERROR 22012: Division by 0 COMMIT; # check transactions-8 success: 1 # INFO: Storage engine used for t1 seems to be able to revert # changes made by the failing statement. SET @@session.sql_mode = ''; SET AUTOCOMMIT= 1; DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; UPDATE t1 SET f_charbig = REPEAT('b', 1000); # check special-1 success: 1 UPDATE t1 SET f_charbig = ''; # check special-2 success: 1 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'==='); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-1 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-2 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-3 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-4 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-5 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-6 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-7 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-8 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = old.f_int1 + @max_row, new.f_int2 = old.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-9 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = new.f_int1 + @max_row, new.f_int2 = new.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-10 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-11 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_char1, f_char2, f_charbig) SELECT CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-12 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 <some_value> OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK # check layout success: 1 REPAIR TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 repair note The storage engine for the table doesn't support repair # check layout success: 1 TRUNCATE t1; # check TRUNCATE success: 1 # check layout success: 1 # End usability test (inc/partition_check.inc) DROP TABLE t1; #------------------------------------------------------------------------ # 6 Session default engine differs from engine used within create table #------------------------------------------------------------------------ SET SESSION storage_engine='MEMORY'; CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) PARTITION BY HASH(f_int1) ( PARTITION part1 ENGINE = 'InnoDB'); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; # Start usability test (inc/partition_check.inc) create_command SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_int1` int(11) DEFAULT NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) (PARTITION part1 ENGINE = InnoDB) */ # check prerequisites-1 success: 1 # check COUNT(*) success: 1 # check MIN/MAX(f_int1) success: 1 # check MIN/MAX(f_int2) success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'delete me' FROM t0_template WHERE f_int1 IN (2,3); # check prerequisites-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'delete me'; # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE # check read via f_int1 success: 1 # check read via f_int2 success: 1 # check multiple-1 success: 1 DELETE FROM t1 WHERE MOD(f_int1,3) = 0; # check multiple-2 success: 1 INSERT INTO t1 SELECT * FROM t0_template WHERE MOD(f_int1,3) = 0; # check multiple-3 success: 1 UPDATE t1 SET f_int1 = f_int1 + @max_row WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 AND @max_row_div2 + @max_row_div4; # check multiple-4 success: 1 DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row AND @max_row_div2 + @max_row_div4 + @max_row; # check multiple-5 success: 1 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-1 success: 1 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-2 success: 1 SELECT MIN(f_int1) INTO @cur_value1 FROM t1; SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value2 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#'; # check single-3 success: 1 SET @cur_value1= -1; SELECT MAX(f_int1) INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value1 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#'; # check single-4 success: 1 SELECT MAX(f_int1) INTO @cur_value FROM t1; DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#'; # check single-5 success: 1 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#'; # check single-6 success: 1 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##'; # check single-7 success: 1 DELETE FROM t1 WHERE f_charbig = '#2147483647##'; DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0; INSERT t1 SET f_int1 = 0 , f_int2 = 0, f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR), f_charbig = '#NULL#'; INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row, f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR), f_charbig = '#NULL#'; # check null success: 1 # check null-1 success: 1 UPDATE t1 SET f_int1 = -@max_row WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-2 success: 1 UPDATE t1 SET f_int1 = NULL WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-3 success: 1 DELETE FROM t1 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-4 success: 1 DELETE FROM t1 WHERE f_int1 = 0 AND f_int2 = 0 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR) AND f_charbig = '#NULL#'; SET AUTOCOMMIT= 0; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-1 success: 1 COMMIT WORK; # check transactions-2 success: 1 ROLLBACK WORK; # check transactions-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; ROLLBACK WORK; # check transactions-4 success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-5 success: 1 ROLLBACK WORK; # check transactions-6 success: 1 # INFO: Storage engine used for t1 seems to be transactional. COMMIT; # check transactions-7 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; SET @@session.sql_mode = 'traditional'; SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1, '', '', 'was inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; ERROR 22012: Division by 0 COMMIT; # check transactions-8 success: 1 # INFO: Storage engine used for t1 seems to be able to revert # changes made by the failing statement. SET @@session.sql_mode = ''; SET AUTOCOMMIT= 1; DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; UPDATE t1 SET f_charbig = REPEAT('b', 1000); # check special-1 success: 1 UPDATE t1 SET f_charbig = ''; # check special-2 success: 1 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'==='); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-1 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-2 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-3 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-4 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-5 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-6 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-7 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-8 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = old.f_int1 + @max_row, new.f_int2 = old.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-9 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = new.f_int1 + @max_row, new.f_int2 = new.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-10 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-11 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_char1, f_char2, f_charbig) SELECT CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-12 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 <some_value> OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK # check layout success: 1 REPAIR TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 repair note The storage engine for the table doesn't support repair # check layout success: 1 TRUNCATE t1; # check TRUNCATE success: 1 # check layout success: 1 # End usability test (inc/partition_check.inc) DROP TABLE t1; CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(20), f_char2 CHAR(20), f_charbig VARCHAR(1000) ) PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) ( PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB', SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB')); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; # Start usability test (inc/partition_check.inc) create_command SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_int1` int(11) DEFAULT NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB)) */ # check prerequisites-1 success: 1 # check COUNT(*) success: 1 # check MIN/MAX(f_int1) success: 1 # check MIN/MAX(f_int2) success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'delete me' FROM t0_template WHERE f_int1 IN (2,3); # check prerequisites-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'delete me'; # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE # check read via f_int1 success: 1 # check read via f_int2 success: 1 # check multiple-1 success: 1 DELETE FROM t1 WHERE MOD(f_int1,3) = 0; # check multiple-2 success: 1 INSERT INTO t1 SELECT * FROM t0_template WHERE MOD(f_int1,3) = 0; # check multiple-3 success: 1 UPDATE t1 SET f_int1 = f_int1 + @max_row WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 AND @max_row_div2 + @max_row_div4; # check multiple-4 success: 1 DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row AND @max_row_div2 + @max_row_div4 + @max_row; # check multiple-5 success: 1 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-1 success: 1 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1; INSERT INTO t1 SET f_int1 = @cur_value , f_int2 = @cur_value, f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), f_charbig = '#SINGLE#'; # check single-2 success: 1 SELECT MIN(f_int1) INTO @cur_value1 FROM t1; SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value2 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#'; # check single-3 success: 1 SET @cur_value1= -1; SELECT MAX(f_int1) INTO @cur_value2 FROM t1; UPDATE t1 SET f_int1 = @cur_value1 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#'; # check single-4 success: 1 SELECT MAX(f_int1) INTO @cur_value FROM t1; DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#'; # check single-5 success: 1 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#'; # check single-6 success: 1 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##'; ERROR HY000: Table has no partition for value 2147483647 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0; INSERT t1 SET f_int1 = 0 , f_int2 = 0, f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR), f_charbig = '#NULL#'; INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row, f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR), f_charbig = '#NULL#'; # check null success: 1 # check null-1 success: 1 UPDATE t1 SET f_int1 = -@max_row WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-2 success: 1 UPDATE t1 SET f_int1 = NULL WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-3 success: 1 DELETE FROM t1 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; # check null-4 success: 1 DELETE FROM t1 WHERE f_int1 = 0 AND f_int2 = 0 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR) AND f_charbig = '#NULL#'; SET AUTOCOMMIT= 0; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-1 success: 1 COMMIT WORK; # check transactions-2 success: 1 ROLLBACK WORK; # check transactions-3 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; ROLLBACK WORK; # check transactions-4 success: 1 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, '', '', 'was inserted' FROM t0_template source_tab WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; # check transactions-5 success: 1 ROLLBACK WORK; # check transactions-6 success: 1 # INFO: Storage engine used for t1 seems to be transactional. COMMIT; # check transactions-7 success: 1 DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; SET @@session.sql_mode = 'traditional'; SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1, '', '', 'was inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; ERROR 22012: Division by 0 COMMIT; # check transactions-8 success: 1 # INFO: Storage engine used for t1 seems to be able to revert # changes made by the failing statement. SET @@session.sql_mode = ''; SET AUTOCOMMIT= 1; DELETE FROM t1 WHERE f_charbig = 'was inserted'; COMMIT WORK; UPDATE t1 SET f_charbig = REPEAT('b', 1000); # check special-1 success: 1 UPDATE t1 SET f_charbig = ''; # check special-2 success: 1 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'==='); INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-1 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; # check trigger-2 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-3 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-4 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = new.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-5 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-6 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-7 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW BEGIN UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2, f_charbig = 'updated by trigger' WHERE f_int1 = - old.f_int1; END| DELETE FROM t0_aux WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); # check trigger-8 success: 1 DROP TRIGGER trg_1; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = 'just inserted' WHERE f_int1 <> CAST(f_char1 AS SIGNED INT); DELETE FROM t0_aux WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; DELETE FROM t1 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = old.f_int1 + @max_row, new.f_int2 = old.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-9 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET new.f_int1 = new.f_int1 + @max_row, new.f_int2 = new.f_int2 - @max_row, new.f_charbig = '####updated per update trigger####'; END| UPDATE t1 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, f_charbig = '####updated per update statement itself####'; # check trigger-10 success: 1 DROP TRIGGER trg_2; UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT), f_int2 = CAST(f_char1 AS SIGNED INT), f_charbig = CONCAT('===',f_char1,'==='); CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-11 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET new.f_int1 = @my_max1 + @counter, new.f_int2 = @my_min2 - @counter, new.f_charbig = '####updated per insert trigger####'; SET @counter = @counter + 1; END| SET @counter = 1; SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1; INSERT INTO t1 (f_char1, f_char2, f_charbig) SELECT CAST(f_int1 AS CHAR), CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 ORDER BY f_int1; DROP TRIGGER trg_3; # check trigger-12 success: 1 DELETE FROM t1 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT) AND f_int2 <> CAST(f_char1 AS SIGNED INT) AND f_charbig = '####updated per insert trigger####'; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 <some_value> OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK # check layout success: 1 REPAIR TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 repair note The storage engine for the table doesn't support repair # check layout success: 1 TRUNCATE t1; # check TRUNCATE success: 1 # check layout success: 1 # End usability test (inc/partition_check.inc) DROP TABLE t1; SET SESSION storage_engine='InnoDB'; DROP VIEW IF EXISTS v1; DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t0_aux; DROP TABLE IF EXISTS t0_definition; DROP TABLE IF EXISTS t0_template;