3761 Marko Mäkelä 2012-05-02
WL#5854 phase 1: MODIFY...NULL for ROW_FORMAT=REDUNDANT
check_if_supported_inplace_alter(): Allow ALTER_COLUMN_NULLABLE for
ROW_FORMAT=REDUNDANT tables.
innobase_set_nullable(): Allow a column to be set NULL in
a ROW_FORMAT=REDUNDANT table.
commit_inplace_alter_table(): Invoke innobase_set_nullable() when needed.
added:
mysql-test/suite/innodb/r/innodb-alter-nullable.result
mysql-test/suite/innodb/t/innodb-alter-nullable.test
modified:
storage/innobase/handler/handler0alter.cc
3760 Marko Mäkelä 2012-05-02
Branch mysql-trunk-wl5854 from mysql-trunk.
modified:
.bzr-mysql/default.conf
3759 Andrei Elkin 2012-04-30
Bug#14018843 - FAULTY LOGICS IN MTS_DEBUG_CONCURRENT_ACCESS HANDLING
Introduced to verify MTS concurrent behavior `mts_debug_concurrent_access'
debug flag was unnecessaliry checked by IO thread which leads to a hang like.
http://pb2.no.oracle.com/web.py?template=mysql_show_test_failure&search=yes&push_id=3396783&test_id=3400389&test_run=rpl_binlog-debug-big&test_suite=rpl&test_case=rpl_mts_debug
Fixed with excluding other than MTS worker threads
from checking the debug flag.
One-liner fixes suppressing a warning in rpl_filter_tables_not_exist is piggybacked.
@ mysql-test/suite/rpl/r/rpl_filter_tables_not_exist.result
results got updated.
@ mysql-test/suite/rpl/t/rpl_filter_tables_not_exist.test
a necessary unsafe suppression on the master side is added.
modified:
mysql-test/suite/rpl/r/rpl_filter_tables_not_exist.result
mysql-test/suite/rpl/t/rpl_filter_tables_not_exist.test
sql/rpl_info_table.cc
=== modified file '.bzr-mysql/default.conf'
--- a/.bzr-mysql/default.conf revid:andrei.elkin@oracle.com-20120430161426-810vqz82xvp2c9dy
+++ b/.bzr-mysql/default.conf revid:marko.makela@stripped5852-0rnowlt6pnlscfmp
@@ -1,4 +1,4 @@
[MYSQL]
post_commit_to = "commits@stripped"
post_push_to = "commits@stripped"
-tree_name = "mysql-trunk"
+tree_name = "mysql-trunk-wl5854"
=== added file 'mysql-test/suite/innodb/r/innodb-alter-nullable.result'
--- a/mysql-test/suite/innodb/r/innodb-alter-nullable.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/r/innodb-alter-nullable.result revid:marko.makela@stripped852-0rnowlt6pnlscfmp
@@ -0,0 +1,58 @@
+CREATE TABLE tr (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
+ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
+CREATE TABLE tc (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
+ENGINE=InnoDB;
+ALTER TABLE tr CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
+ALTER TABLE tc CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
+ALTER TABLE tr MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
+ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tr MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE'
+ALTER TABLE tc MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
+ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tc MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE'
+ALTER TABLE tr CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL;
+ERROR 42S22: Unknown column 'c2' in 'tr'
+ALTER TABLE tr MODIFY c2 INT, MODIFY c2 INT NOT NULL;
+ERROR 42S22: Unknown column 'c2' in 'tr'
+ALTER TABLE tr MODIFY c2 INT UNSIGNED, MODIFY c2 INT;
+ERROR 42S22: Unknown column 'c2' in 'tr'
+ALTER TABLE tr MODIFY c2 CHAR(1) NOT NULL, MODIFY c2 INT NOT NULL;
+ERROR 42S22: Unknown column 'c2' in 'tr'
+ALTER TABLE tr CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tc CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tr MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tc MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tr MODIFY c2 INT, ALGORITHM=INPLACE;
+ALTER TABLE tc MODIFY c2 INT, ALGORITHM=INPLACE;
+ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tc MODIFY c2 INT, ALGORITHM=INPLACE'
+ALTER TABLE tr MODIFY c2 INT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tc MODIFY c2 INT NULL, ALGORITHM=INPLACE;
+ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tc MODIFY c2 INT NULL, ALGORITHM=INPLACE'
+DROP TABLE tc, tr;
+CREATE TABLE tr (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
+ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
+CREATE TABLE tc (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
+ENGINE=InnoDB;
+ALTER TABLE tr CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
+ALTER TABLE tc CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
+ALTER TABLE tr MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
+ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tr MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE'
+ALTER TABLE tc MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
+ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tc MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE'
+ALTER TABLE tr CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL;
+ERROR 42S22: Unknown column 'c2' in 'tr'
+ALTER TABLE tr MODIFY c2 INT, MODIFY c2 INT NOT NULL;
+ERROR 42S22: Unknown column 'c2' in 'tr'
+ALTER TABLE tr MODIFY c2 INT UNSIGNED, MODIFY c2 INT;
+ERROR 42S22: Unknown column 'c2' in 'tr'
+ALTER TABLE tr MODIFY c2 CHAR(1) NOT NULL, MODIFY c2 INT NOT NULL;
+ERROR 42S22: Unknown column 'c2' in 'tr'
+ALTER TABLE tr CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tc CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tr MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tc MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tr MODIFY c2 INT, ALGORITHM=INPLACE;
+ALTER TABLE tc MODIFY c2 INT, ALGORITHM=INPLACE;
+ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tc MODIFY c2 INT, ALGORITHM=INPLACE'
+ALTER TABLE tr MODIFY c2 INT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tc MODIFY c2 INT NULL, ALGORITHM=INPLACE;
+ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tc MODIFY c2 INT NULL, ALGORITHM=INPLACE'
+DROP TABLE tc, tr;
=== added file 'mysql-test/suite/innodb/t/innodb-alter-nullable.test'
--- a/mysql-test/suite/innodb/t/innodb-alter-nullable.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/t/innodb-alter-nullable.test revid:marko.makela@stripped
@@ -0,0 +1,104 @@
+--source include/have_innodb.inc
+
+# WL#5854 part 1: MODIFY...NULL
+# First, this is only implemented for ROW_FORMAT=REDUNDANT tables,
+# because that one does not change the storage format of B-tree records.
+#
+# Implementing the rest of WL#5854 will require a multi-versioned
+# data dictionary (multiple SYS_TABLES and clustered index for the table).
+
+CREATE TABLE tr (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
+ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
+
+CREATE TABLE tc (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
+ENGINE=InnoDB;
+
+# This one will be a no-op.
+# MySQL should perhaps issue an error, because it refuses to modify
+# the PRIMARY KEY column c1 from NOT NULL to NULL.
+ALTER TABLE tr CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
+ALTER TABLE tc CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
+
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE tr MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE tc MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
+
+# Request some conflicting changes for a single column.
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE tr CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL;
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE tr MODIFY c2 INT, MODIFY c2 INT NOT NULL;
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE tr MODIFY c2 INT UNSIGNED, MODIFY c2 INT;
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE tr MODIFY c2 CHAR(1) NOT NULL, MODIFY c2 INT NOT NULL;
+
+# No-ops.
+ALTER TABLE tr CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tc CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tr MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tc MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
+
+ALTER TABLE tr MODIFY c2 INT, ALGORITHM=INPLACE;
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE tc MODIFY c2 INT, ALGORITHM=INPLACE;
+
+ALTER TABLE tr MODIFY c2 INT NULL, ALGORITHM=INPLACE;
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE tc MODIFY c2 INT NULL, ALGORITHM=INPLACE;
+
+DROP TABLE tc, tr;
+--source include/have_innodb.inc
+
+# WL#5854 part 1: MODIFY...NULL
+# This is only implemented for ROW_FORMAT=REDUNDANT tables,
+# because that one does not change the storage format of B-tree records.
+#
+# Implementing the change for later InnoDB formats would require a
+# multi-versioned data dictionary (and multi-versioned SYS_COLUMNS at that).
+# Implementing ADD COLUMN and DROP COLUMN only needs a multi-versioned
+# clustered index, and simple "append" versioning of SYS_COLUMNS.
+
+CREATE TABLE tr (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
+ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
+
+CREATE TABLE tc (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
+ENGINE=InnoDB;
+
+# This one will be a no-op.
+# MySQL should perhaps issue an error, because it refuses to modify
+# the PRIMARY KEY column c1 from NOT NULL to NULL.
+ALTER TABLE tr CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
+ALTER TABLE tc CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
+
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE tr MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE tc MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
+
+# Request some conflicting changes for a single column.
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE tr CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL;
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE tr MODIFY c2 INT, MODIFY c2 INT NOT NULL;
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE tr MODIFY c2 INT UNSIGNED, MODIFY c2 INT;
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE tr MODIFY c2 CHAR(1) NOT NULL, MODIFY c2 INT NOT NULL;
+
+# No-ops.
+ALTER TABLE tr CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tc CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tr MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
+ALTER TABLE tc MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
+
+ALTER TABLE tr MODIFY c2 INT, ALGORITHM=INPLACE;
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE tc MODIFY c2 INT, ALGORITHM=INPLACE;
+
+ALTER TABLE tr MODIFY c2 INT NULL, ALGORITHM=INPLACE;
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE tc MODIFY c2 INT NULL, ALGORITHM=INPLACE;
+
+DROP TABLE tc, tr;
=== modified file 'storage/innobase/handler/handler0alter.cc'
--- a/storage/innobase/handler/handler0alter.cc revid:andrei.elkin@stripped0430161426-810vqz82xvp2c9dy
+++ b/storage/innobase/handler/handler0alter.cc revid:marko.makela@stripped105852-0rnowlt6pnlscfmp
@@ -55,9 +55,15 @@ static const Alter_inplace_info::HA_ALTE
| Alter_inplace_info::ALTER_RENAME
| Alter_inplace_info::CHANGE_CREATE_OPTION;
+/** Operations for altering the record format */
+static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_INPLACE_REFORMAT
+ = 0;
+
/** Operations that InnoDB can perform online */
static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_ONLINE_OPERATIONS
= INNOBASE_INPLACE_IGNORE
+ | INNOBASE_INPLACE_REFORMAT
+ | Alter_inplace_info::ALTER_COLUMN_NULLABLE
| Alter_inplace_info::ADD_INDEX
| Alter_inplace_info::DROP_INDEX
| Alter_inplace_info::ADD_UNIQUE_INDEX
@@ -199,6 +205,22 @@ ha_innobase::check_if_supported_inplace_
DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED);
}
+ if ((ha_alter_info->handler_flags
+ & Alter_inplace_info::ALTER_COLUMN_NULLABLE)
+ && dict_table_is_comp(prebuilt->table)) {
+ /* For now, we only support changing a column from
+ NOT NULL to NULL when ROW_FORMAT=REDUNDANT. */
+ DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED);
+ }
+
+ if (!(ha_alter_info->handler_flags &
+ ~(INNOBASE_INPLACE_REFORMAT
+ | Alter_inplace_info::ALTER_COLUMN_NULLABLE))) {
+ /* These operations will actually be performed during
+ ha_innobase::commit_inplace_alter_table(). */
+ DBUG_RETURN(HA_ALTER_INPLACE_NO_LOCK);
+ }
+
update_thd();
trx_search_latch_release_if_reserved(prebuilt->trx);
@@ -254,11 +276,6 @@ ha_innobase::check_if_supported_inplace_
prebuilt->trx->will_lock++;
- /* TODO: Reject if creating a fulltext index and there is an
- incompatible FTS_DOC_ID or FTS_DOC_ID_INDEX, either in the table
- or in the ha_alter_info. We can and should reject this before
- locking the data dictionary. */
-
/* Creating the primary key requires an exclusive lock on the
table during the whole copying operation. */
if (ha_alter_info->handler_flags & Alter_inplace_info::ADD_PK_INDEX) {
@@ -2743,6 +2760,79 @@ err_exit:
DBUG_RETURN(false);
}
+/** Allow a column to be set NULL in a ROW_FORMAT=REDUNDANT table.
+@param table_share the TABLE_SHARE
+@param prebuilt the prebuilt struct
+@param trx data dictionary transaction
+@param nth_col 0-based index of the column
+@retval true Failure
+@retval false Success */
+static __attribute__((nonnull, warn_unused_result))
+bool
+innobase_set_nullable(
+/*==================*/
+ const TABLE_SHARE* table_share,
+ row_prebuilt_t* prebuilt,
+ trx_t* trx,
+ ulint nth_col)
+{
+ pars_info_t* info;
+ dberr_t error;
+ dict_col_t* col;
+
+ DBUG_ENTER("innobase_set_nullable");
+ DBUG_ASSERT(trx_get_dict_operation(trx) == TRX_DICT_OP_INDEX);
+ ut_ad(trx->dict_operation_lock_mode == RW_X_LATCH);
+ ut_ad(mutex_own(&dict_sys->mutex));
+#ifdef UNIV_SYNC_DEBUG
+ ut_ad(rw_lock_own(&dict_operation_lock, RW_LOCK_EX));
+#endif /* UNIV_SYNC_DEBUG */
+
+ /* The NULL flag is stored in SYS_COLUMNS, not in SYS_INDEXES
+ (or SYS_FIELDS). ADD COLUMN and DROP COLUMN will only
+ multi-version the clustered index, not the table columns.
+ Thus, we can only change the NULLable flag of a column in
+ tables where the change does not affect the B-tree record
+ format (ROW_FORMAT=REDUNDANT). */
+ ut_ad(!dict_table_is_comp(prebuilt->table));
+ col = dict_table_get_nth_col(prebuilt->table, nth_col);
+
+ ut_ad(col->prtype & DATA_NOT_NULL);
+
+ info = pars_info_create();
+
+ pars_info_add_ull_literal(info, "tableid", prebuilt->table->id);
+ pars_info_add_int4_literal(info, "nth", nth_col);
+ pars_info_add_int4_literal(info, "prtype",
+ col->prtype & ~DATA_NOT_NULL);
+
+ trx->op_info = "setting column nullable in SYS_COLUMNS";
+
+ error = que_eval_sql(
+ info,
+ "PROCEDURE NULLABLE_SYS_COLUMNS_PROC () IS\n"
+ "BEGIN\n"
+ "UPDATE SYS_COLUMNS SET PRTYPE=:prtype\n"
+ "WHERE TABLE_ID=:tableid AND POS=:nth;\n"
+ "END;\n",
+ FALSE, trx);
+
+ DBUG_EXECUTE_IF("ib_set_nullable_error",
+ error = DB_OUT_OF_FILE_SPACE;);
+
+ trx->op_info = "";
+
+ if (error != DB_SUCCESS) {
+ my_error_innodb(error, table_share->table_name.str, 0);
+ trx->error_state = DB_SUCCESS;
+ DBUG_RETURN(true);
+ }
+
+ /* Set the column nullable in the data dictionary cache. */
+ col->prtype &= ~DATA_NOT_NULL;
+ DBUG_RETURN(false);
+}
+
/** Commit or rollback the changes made during
prepare_inplace_alter_table() and inplace_alter_table() inside
the storage engine. Note that the allowed level of concurrency
@@ -2960,6 +3050,39 @@ processed_field:
}
}
+ if (err == 0 && !new_clustered
+ && (ha_alter_info->handler_flags
+ & Alter_inplace_info::ALTER_COLUMN_NULLABLE)) {
+ DBUG_ASSERT(!dict_table_is_comp(prebuilt->table));
+
+ List_iterator_fast<Create_field> cf_it;
+ ulint col = 0;
+
+ for (Field** fp = table->field;
+ err == 0 && col < dict_table_get_n_cols(prebuilt->table);
+ fp++, col++) {
+ cf_it.init(ha_alter_info->alter_info->create_list);
+ while (Create_field* cf = cf_it++) {
+ if (cf->field == *fp
+ && ((cf->flags ^ (*fp)->flags)
+ & NOT_NULL_FLAG)) {
+ DBUG_ASSERT((*fp)->flags
+ & NOT_NULL_FLAG);
+ if (innobase_set_nullable(
+ table_share,
+ prebuilt, trx, col)) {
+ err = -1;
+ }
+ }
+ }
+ }
+ }
+
+ if (err == 0 && !new_clustered
+ && (ha_alter_info->handler_flags & INNOBASE_INPLACE_REFORMAT)) {
+ ut_error;
+ }
+
if (err == 0) {
trx_commit:
trx_commit_for_mysql(trx);
No bundle (reason: useless for push emails).| Thread |
|---|
| • bzr push into mysql-trunk-wl5854 branch (marko.makela:3759 to 3761) WL#5854 | marko.makela | 2 May |
