Error 1048 dropping a NULL column and adding same named column back as NOT NULL in the same migration

[ikewalker]

version 1.0.35

I discovered this issue when trying to execute a migration with gh-ost to drop a NULL column and add it back as NOT NULL with a default value in order to backfill the existing rows with a static value for that column.

Here is a simplified test to reproduce the issue:

-- Create test table
CREATE TABLE `test`.`foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `str` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# Populate some data in the table to give gh-ost some rows to copy
mysqlslap --defaults-file=/Users/ikewalker/sandboxes/msb_5_6_35/my.sandbox.cnf \
  --iterations 100000 --create-schema=test --query="insert into foo (str) values (NULL);"

# Start loading more data into the table
# Leave this running when you start the migration so they are running concurrently
mysqlslap --defaults-file=/Users/ikewalker/sandboxes/msb_5_6_35/my.sandbox.cnf \
  --iterations 1000000 --create-schema=test --query="insert into foo (str) values (NULL);"

# Execute the migration to drop the str column and add it back as NOT NULL.
$ ./gh-ost --execute --allow-on-master --user msandbox --password msandbox \
  --port 5635 --switch-to-rbr --database test --table foo \
  --alter 'DROP str,ADD str varchar(16)  NOT NULL DEFAULT "bar"'

# gh-ost will start throwing the following error over and over again
2017-03-16 07:28:04 ERROR Error 1048: Column 'str' cannot be null; query=
			replace /* gh-ost `test`.`_foo_gho` */ into
				`test`.`_foo_gho`
					(`id`, `str`)
				values
					(?, ?)
		; args=[314115 <nil>]
Copy: 0/313964 0.0%; Applied: 0; Backlog: 100/100; Time: 21s(total), 21s(copy); streamer: mysql_sandbox5635-bin.000002:101884612; State: migrating; ETA: N/A```

[shlomi-noach]

Because they are named the same, gh-ost does not understand these are two different columns, and tries copying column data from original table to the ghost table, and since the original table has NULLs this fails.

The solution to this Issue would be along the same lines of rename column handling, but to the opposite effect. gh-ost will need to parse the alter statement to detect the drop+add (gh-ost is mostly unconcerned with the statement itself and instead just looks at the structure of the ghost table).

[shlomi-noach]

Notes for add and drop syntax: since [column] is optional, further validation is required to avoid mistreating key, index and more as column names.

alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name ]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        reference_definition
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} index_name
  | DROP FOREIGN KEY fk_symbol

[druud62]

With a recent MySQL, a column-rename is a virtually free alter, so you can use a temporary column name for the migration, and then rename the column with a manual alter.

Alternatively, gh-ost could do the final alter for such a case, just before renaming the table into place.

[shlomi-noach]

@druud62 this issue is already solved and the operation is supported.