path: root/mysql-test/suite/innodb/t/innodb-index-online.test

--source include/innodb_page_size_small.inc
--source include/innodb_encrypt_log.inc
--source include/have_debug.inc
--source include/have_debug_sync.inc
--source include/no_valgrind_without_big.inc

let $innodb_metrics_select=
SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';

call mtr.add_suppression("InnoDB: Warning: Small buffer pool size");

# Save the initial number of concurrent sessions.
--source include/count_sessions.inc

CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 TEXT)
ENGINE=InnoDB STATS_PERSISTENT=0;
INSERT INTO t1 VALUES (1,1,''), (2,2,''), (3,3,''), (4,4,''), (5,5,'');

SET GLOBAL innodb_monitor_enable = module_ddl;
eval $innodb_metrics_select;

SET DEBUG_SYNC = 'RESET';
SET DEBUG_SYNC = 'write_row_noreplace SIGNAL have_handle WAIT_FOR go_ahead';
--send
INSERT INTO t1 VALUES(1,2,3);

connect (con1,localhost,root,,);

# This should block at the end because of the INSERT in connection default
# is holding a metadata lock.
SET DEBUG_SYNC = 'now WAIT_FOR have_handle';
SET lock_wait_timeout = 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE t1 ADD UNIQUE INDEX(c2);
SET DEBUG_SYNC = 'now SIGNAL go_ahead';

connection default;
--error ER_DUP_ENTRY
reap;
eval $innodb_metrics_select;

connection con1;
SET @saved_debug_dbug = @@SESSION.debug_dbug;
SET DEBUG_DBUG = '+d,innodb_OOM_prepare_inplace_alter';
--error ER_OUT_OF_RESOURCES
ALTER TABLE t1 ADD UNIQUE INDEX(c2);
SET DEBUG_DBUG = @saved_debug_dbug;
SET DEBUG_DBUG = '+d,innodb_OOM_inplace_alter';
--error ER_OUT_OF_RESOURCES
CREATE UNIQUE INDEX c2 ON t1(c2);
SET DEBUG_DBUG = @saved_debug_dbug;
SET DEBUG_DBUG = '+d,innodb_OOM_prepare_add_index';
--error ER_OUT_OF_RESOURCES
ALTER TABLE t1 ADD KEY(c2), ADD KEY c3_10(c3(10)), ADD KEY c3_c2(c3(4),c2);
SET DEBUG_DBUG = @saved_debug_dbug;
CREATE UNIQUE INDEX c2 ON t1(c2);
DROP INDEX c2 ON t1;

connection default;
SHOW CREATE TABLE t1;
# Insert a duplicate entry (4) for the upcoming UNIQUE INDEX(c2).
BEGIN;
INSERT INTO t1 VALUES(7,4,2);

connection con1;
# This DEBUG_SYNC should not kick in yet, because the duplicate key will be
# detected before we get a chance to apply the online log.
SET DEBUG_SYNC = 'row_log_apply_before SIGNAL scanned WAIT_FOR rollback_done';
# This will be a lock wait timeout on the meta-data lock,
# because the transaction inserting (7,4,2) is still active.
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE t1 ADD UNIQUE INDEX(c2);
connection default;
COMMIT;
connection con1;
--error ER_DUP_ENTRY
ALTER TABLE t1 ADD UNIQUE INDEX(c2);
connection default;
DELETE FROM t1 WHERE c1 = 7;
connection con1;
# ADD FOREIGN KEY is not supported in-place
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1 ADD FOREIGN KEY(c2) REFERENCES t1(c2), ALGORITHM = INPLACE;
# The previous DEBUG_SYNC should be ignored, because an exclusive lock
# has been requested and the online log is not being allocated.
ALTER TABLE t1 ADD UNIQUE INDEX(c2), LOCK = EXCLUSIVE, ALGORITHM = INPLACE;
DROP INDEX c2 ON t1;
# Now the previous DEBUG_SYNC should kick in.
--send
ALTER TABLE t1 ADD UNIQUE INDEX(c2);
connection default;
SET DEBUG_SYNC = 'now WAIT_FOR scanned';
eval $innodb_metrics_select;

# Insert a duplicate entry (4) for the already started UNIQUE INDEX(c2).
BEGIN;
INSERT INTO t1 VALUES(7,4,2);
COMMIT;
DELETE FROM t1 where c1 = 7;
SET DEBUG_SYNC = 'now SIGNAL rollback_done';

connection con1;
# Because the modification log will be applied in order, there will be
# a duplicate key error on the (7,4,2) even though we roll it back.
--error ER_DUP_ENTRY
reap;
# Now, create the index without any concurrent DML, while no duplicate exists.
SET DEBUG_SYNC = 'row_log_apply_after SIGNAL created WAIT_FOR dml_done';
--send
ALTER TABLE t1 ADD UNIQUE INDEX(c2);
connection default;
SET DEBUG_SYNC = 'now WAIT_FOR created';
# At this point, the index has been created inside InnoDB but not yet
# in the MySQL data dictionary.
eval $innodb_metrics_select;
# A duplicate key error should now be triggered by InnoDB, but reported
# by the ALTER TABLE because the index does not 'officially' exist yet.
INSERT INTO t1 VALUES(6,3,1);
SET DEBUG_SYNC = 'now SIGNAL dml_done';
connection con1;
# This is due to the duplicate entry (6,3,1).
--error ER_DUP_ENTRY
reap;
DELETE FROM t1 WHERE c1=6;
ALTER TABLE t1 ADD UNIQUE INDEX(c2);
eval $innodb_metrics_select;

connection default;
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES(6,3,1);
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES(7,4,2);
ALTER TABLE t1 STATS_PERSISTENT=1;
ANALYZE TABLE t1;
# Purge may or may not have cleaned up the DELETE FROM t1 WHERE c1 = 7;
UPDATE mysql.innodb_index_stats SET stat_value = 5
WHERE database_name = 'test' AND table_name= 't1' AND index_name = 'PRIMARY'
AND stat_value = 6;
--replace_column 4 LAST_UPDATE
SELECT * FROM mysql.innodb_index_stats WHERE table_name IN ('t1');
CREATE TABLE t1_c2_stats SELECT * FROM mysql.innodb_index_stats
WHERE database_name = 'test' AND table_name = 't1' and index_name = 'c2';
# in Embedded mode (./mtr --embedded-server) the t1_c2_stats table gets
# created in MyISAM format by default even if we set
# default_storage_engine='innodb'
ALTER TABLE t1_c2_stats ENGINE=INNODB;
DROP INDEX c2 ON t1;
ANALYZE TABLE t1_c2_stats;
--replace_column 4 LAST_UPDATE
SELECT * FROM mysql.innodb_index_stats WHERE table_name IN ('t1', 't1_c2_stats');

connection con1;
let $ID= `SELECT @id := CONNECTION_ID()`;
--error ER_QUERY_INTERRUPTED
KILL QUERY @id;

SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c2d_created WAIT_FOR kill_done';
--send
CREATE INDEX c2d ON t1(c2);

connection default;
SET DEBUG_SYNC = 'now WAIT_FOR c2d_created';
eval $innodb_metrics_select;
let $ignore= `SELECT @id := $ID`;
KILL QUERY @id;
SET DEBUG_SYNC = 'now SIGNAL kill_done';

connection con1;
--error ER_QUERY_INTERRUPTED
reap;
eval $innodb_metrics_select;

connection default;
CHECK TABLE t1;
INSERT INTO t1 SELECT  5 + c1, c2,  c3 FROM t1;
INSERT INTO t1 SELECT 10 + c1, c2, c3 FROM t1;
INSERT INTO t1 SELECT 20 + c1, c2, c3 FROM t1;
INSERT INTO t1 SELECT 40 + c1, c2, c3 FROM t1;
# Purge may or may not have cleaned up the DELETE FROM t1 WHERE c1 = 7;
--replace_result 81 80
EXPLAIN SELECT COUNT(*) FROM t1 WHERE c2 > 3;
ANALYZE TABLE t1;

connection con1;
# Forge some statistics for c2d, and see that they will be used
UPDATE t1_c2_stats SET index_name = 'c2d';
# Fake the statistics. The cardinality should be 5,80.
UPDATE t1_c2_stats SET stat_value = 2 WHERE stat_name = 'n_diff_pfx01';
INSERT INTO t1_c2_stats
SELECT database_name, table_name, index_name, last_update, 'n_diff_pfx02', 80,
sample_size, 'c2,c1' FROM t1_c2_stats
WHERE stat_name = 'n_diff_pfx01' AND stat_description = 'c2';
INSERT INTO mysql.innodb_index_stats SELECT * FROM t1_c2_stats;
DROP TABLE t1_c2_stats;

CREATE INDEX c2d ON t1(c2);
# This should show the newly calculated stats by CREATE INDEX above,
# not the faked cardinality=4 for c2d(c2).
# Purge may or may not have cleaned up the DELETE FROM t1 WHERE c1 = 7;
--replace_result 81 80
SHOW INDEX FROM t1;
EXPLAIN SELECT COUNT(*) FROM t1 WHERE c2 > 3;

SHOW CREATE TABLE t1;

connection default;
SET @merge_encrypt_0=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_merge_blocks_encrypted');
SET @merge_decrypt_0=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_merge_blocks_decrypted');
SET @rowlog_encrypt_0=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_encrypted');
connection con1;

# Exceed the configured innodb_online_alter_log_max_size.
# The actual limit is a multiple of innodb_sort_buf_size,
# because that is the size of the in-memory log buffers.
SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c2e_created WAIT_FOR dml2_done';
# Ensure that the ALTER TABLE will be executed even with some concurrent DML.
SET lock_wait_timeout = 10;
--send
ALTER TABLE t1 CHANGE c2 c22 INT, DROP INDEX c2d, ADD INDEX c2e(c22, c3(10)), ALGORITHM = NOCOPY;

# Generate some log (delete-mark, delete-unmark, insert etc.)
# while the index creation is blocked. Some of this may run
# in parallel with the clustered index scan.
connection default;
INSERT INTO t1 SELECT  80 + c1, c2, c3 FROM t1;
INSERT INTO t1 SELECT 160 + c1, c2, c3 FROM t1;
#UPDATE t1 SET c2 = c2 + 1;
SET DEBUG_SYNC = 'now WAIT_FOR c2e_created';
# At this point, the clustered index scan must have completed,
# but the modification log keeps accumulating due to the DEBUG_SYNC.
eval $innodb_metrics_select;
let $c= 8;
while ($c)
{
  UPDATE t1 SET c2 = c2 + 1;
  UPDATE t1 SET c2 = c2 + 2;
  dec $c;
}
# Incomplete index c2e should exist until the DDL thread notices the overflow.
# (The output below strips TEMP_INDEX_PREFIX from the name.)
eval $innodb_metrics_select;
SELECT sf.name, sf.pos FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES si
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_FIELDS sf
ON si.index_id = sf.index_id WHERE si.name = '?c2e';

SET @merge_encrypt_1=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_merge_blocks_encrypted');
SET @merge_decrypt_1=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_merge_blocks_decrypted');
SET @rowlog_encrypt_1=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_encrypted');

SELECT
(@merge_encrypt_1-@merge_encrypt_0)-
(@merge_decrypt_1-@merge_decrypt_0) as sort_balance,
@merge_encrypt_1>@merge_encrypt_0, @merge_decrypt_1>@merge_decrypt_0,
@rowlog_encrypt_1>@rowlog_encrypt_0;

# Release con1.
SET DEBUG_SYNC = 'now SIGNAL dml2_done';

connection con1;
# If the following fails with the wrong error, it probably means that
# you should rerun with a larger mtr --debug-sync-timeout.
--error ER_INNODB_ONLINE_LOG_TOO_BIG
reap;
# The index c2e should have been dropped from the data dictionary
# when the above error was noticed. It should still exist in the
# cache with index->online_status = ONLINE_INDEX_ABORTED_DROPPED.
eval $innodb_metrics_select;
SELECT sf.name, sf.pos FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES si
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_FIELDS sf
ON si.index_id = sf.index_id WHERE si.name = 'c2e';

# ddl_background_drop_indexes = 1 here, because the incomplete index c2e still
# exists in the InnoDB data dictionary cache.
eval $innodb_metrics_select;

connection default;

ALTER TABLE t1 COMMENT 'testing if c2e will be dropped';

# Check that the 'zombie' index c2e was dropped.
eval $innodb_metrics_select;

SET @merge_encrypt_1=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_merge_blocks_encrypted');
SET @merge_decrypt_1=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_merge_blocks_decrypted');
SET @rowlog_encrypt_1=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_encrypted');
SET @rowlog_decrypt_1=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_decrypted');

connection con1;
# Accumulate and apply some modification log.
SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c2f_created WAIT_FOR dml3_done';
--send
ALTER TABLE t1 ADD INDEX c2f(c22f), CHANGE c2 c22f INT;

connection default;
SET DEBUG_SYNC = 'now WAIT_FOR c2f_created';
# Generate some log (delete-mark, delete-unmark, insert etc.)
eval $innodb_metrics_select;
let $c= 6;
while ($c)
{
BEGIN;
INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 160;
DELETE FROM t1 WHERE c1 > 320;
COMMIT;
BEGIN;
UPDATE t1 SET c2 = c2 + 1;
COMMIT;
dec $c;
}
eval $innodb_metrics_select;
# Release con1.
SET DEBUG_SYNC = 'now SIGNAL dml3_done';

connection con1;
reap;

eval $innodb_metrics_select;

connection default;

SET @merge_encrypt_2=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_merge_blocks_encrypted');
SET @merge_decrypt_2=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_merge_blocks_decrypted');
SET @rowlog_encrypt_2=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_encrypted');
SET @rowlog_decrypt_2=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_decrypted');

SELECT
(@merge_encrypt_2-@merge_encrypt_1)-
(@merge_decrypt_2-@merge_decrypt_1) as sort_balance,
(@rowlog_encrypt_2-@rowlog_encrypt_1)-
(@rowlog_decrypt_2-@rowlog_decrypt_1) as log_balance;
SELECT
@merge_encrypt_2-@merge_encrypt_1>0 as sort_encrypted,
@merge_decrypt_2-@merge_decrypt_1>0 as sort_decrypted,
@rowlog_encrypt_2-@rowlog_encrypt_1>0 as log_encrypted,
@rowlog_decrypt_2-@rowlog_decrypt_1>0 as log_decrypted;

connection con1;
SELECT COUNT(c22f) FROM t1;
CHECK TABLE t1;

# Create a column prefix index.
--error ER_DUP_ENTRY
ALTER TABLE t1 ADD UNIQUE INDEX c3p5(c3(5));
UPDATE t1 SET c3 = NULL WHERE c3 = '';
SET lock_wait_timeout = 1;
SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c3p5_created WAIT_FOR ins_done';
--send
ALTER TABLE t1 ADD UNIQUE INDEX c3p5(c3(5));

connection default;
SET DEBUG_SYNC = 'now WAIT_FOR c3p5_created';

# Check that the index was created.
SELECT sf.name, sf.pos FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES si
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_FIELDS sf
ON si.index_id = sf.index_id WHERE si.name = '?c3p5';

SET DEBUG_SYNC = 'ib_after_row_insert SIGNAL ins_done WAIT_FOR ddl_timed_out';
--send
INSERT INTO t1 VALUES(347,33101,NULL);

connection con1;
--error ER_LOCK_WAIT_TIMEOUT
reap;
SET DEBUG_SYNC = 'now SIGNAL ddl_timed_out';

# InnoDB should have cleaned up the index c3p5 from the data dictionary,
# but not yet from the dictionary cache.
SELECT sf.name, sf.pos FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES si
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_FIELDS sf
ON si.index_id = sf.index_id WHERE si.name = 'c3p5';
eval $innodb_metrics_select;

connection default;
reap;
# Index c3p5 should still exist in the data dictionary cache.
eval $innodb_metrics_select;

--disable_parsing
# Temporarily disabled by fix for bug#14213236. Should be either
# removed or updated to take into account that locking for IMPORT/
# DISCARD TABLESPACE happens on MDL layer. New test case is added 
# to validate this at MDL layer(i_main.alter_table.test)

SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c2g_created WAIT_FOR dml4_done';
# The lock upgrade at the end of the ALTER will conflict with the DISCARD.
SET lock_wait_timeout = 1;
--send
ALTER TABLE t1 DROP INDEX c2f, ADD INDEX c2g(c22f);

connection con1;
SET DEBUG_SYNC = 'now WAIT_FOR c2g_created';

connect (con2,localhost,root,,);

# This will conflict with the ALTER in connection default, above.
SET lock_wait_timeout = 10;
--send
ALTER TABLE t1 DISCARD TABLESPACE;

connection con1;
let $wait_condition=
  SELECT COUNT(*) = 1 FROM information_schema.processlist
  WHERE state = 'Waiting for table level lock' and
        info = 'ALTER TABLE t1 DISCARD TABLESPACE';
--source include/wait_condition.inc

SET DEBUG_SYNC = 'now SIGNAL dml4_done';
disconnect con1;
connection con2;
reap;
disconnect con2;
connection default;
--error ER_LOCK_WAIT_TIMEOUT
reap;
--enable_parsing
#remove below con1 disconnect if above test case is enabled
connection default;

SHOW CREATE TABLE t1;
ALTER TABLE t1 DROP INDEX c2d, DROP INDEX c2f;
# The ALTER TABLE should have cleaned up c3p5 from the cache.
eval $innodb_metrics_select;
ALTER TABLE t1 ADD INDEX c2h(c22f), ALGORITHM = INPLACE;
--error ER_DUP_KEYNAME
ALTER TABLE t1 ADD INDEX c2h(c22f), ALGORITHM = COPY;

SET DEBUG_SYNC = 'RESET';
SET GLOBAL innodb_monitor_disable = module_ddl;

DROP TABLE t1;

--echo #
--echo # MDEV-13205 assertion !dict_index_is_online_ddl(index) upon ALTER TABLE
--echo #
CREATE TABLE t1 (c VARCHAR(64)) ENGINE=InnoDB;
INSERT INTO t1 VALUES('foo');
SET DEBUG_SYNC = 'row_log_apply_before SIGNAL t1u_created WAIT_FOR dup_done';
send ALTER TABLE t1 ADD UNIQUE(c);

connection con1;
SET DEBUG_SYNC = 'now WAIT_FOR t1u_created';
INSERT INTO t1 VALUES('bar'),('bar');
SET DEBUG_SYNC = 'now SIGNAL dup_done';

connection default;
--error ER_DUP_ENTRY
reap;

SET DEBUG_SYNC = 'RESET';
disconnect con1;

CREATE TABLE t2 (c VARCHAR(64)) ENGINE=InnoDB;
--error ER_CANT_CREATE_TABLE
ALTER TABLE t2 ADD FOREIGN KEY (c) REFERENCES t1 (c);
DROP TABLE t2,t1;

CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES(0,0);

connect (con1,localhost,root,,);
SET DEBUG_SYNC = 'row_log_apply_before SIGNAL created WAIT_FOR inserted';
send ALTER TABLE t1 ADD INDEX(b);

connection default;
SET DEBUG_SYNC = 'now WAIT_FOR created';
BEGIN;
INSERT INTO t1 VALUES(1,1);
ROLLBACK;
SET DEBUG_SYNC = 'now SIGNAL inserted';

connection con1;
reap;
disconnect con1;

connection default;
SELECT * FROM t1;
CHECK TABLE t1;
DROP TABLE t1;
SET DEBUG_SYNC = 'RESET';

# Check that all connections opened by test cases in this file are really
# gone so execution of other tests won't be affected by their presence.
--source include/wait_until_count_sessions.inc

--disable_warnings
SET GLOBAL innodb_monitor_enable  = default;
SET GLOBAL innodb_monitor_disable = default;
--enable_warnings