Error 1364

[sky-9527-github]

hi, shlomi noach

gh-ost 1.0.46 MySQL 5.7.19

./gh-ost --host=127.0.0.1 --port=3306 --user=root --password=123456 \ --database=db1 --table=t1 \ --max-load='Threads_running=20,threads_connected=20' \ --critical-load='Threads_running=30,threads_connected=30' \ --chunk-size=2000 --assume-rbr --cut-over=atomic --timestamp-old-table \ --exact-rowcount --concurrent-rowcount \ --panic-flag-file=/tmp/gh-ost.panic.flag \ --postpone-cut-over-flag-file=/tmp/gh-ost.postpone.flag \ --default-retries=120 --approve-renamed-columns --verbose --debug --dml-batch-size=10 \ --allow-on-master --allow-master-master \ --alter "add c6 varchar(6) not null" \ --execute

1> original table: SQL> show create table t1\G 1. row Table: t1 Create Table: CREATE TABLE t1 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(5) DEFAULT NULL, c1 datetime NOT NULL DEFAULT '1970-01-01 00:00:00', c2 varchar(10) NOT NULL DEFAULT 'xx', c30 varchar(50) NOT NULL DEFAULT 'c30', c3 varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'xx', PRIMARY KEY (id), KEY i_c3 (c3), KEY i_c30 (c30) ) ENGINE=InnoDB AUTO_INCREMENT=8126491 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

2> ghost table: SQL> show create table _t1_gho\G 1. row Table: _t1_gho Create Table: CREATE TABLE _t1_gho ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(5) DEFAULT NULL, c1 datetime NOT NULL DEFAULT '1970-01-01 00:00:00', c2 varchar(10) NOT NULL DEFAULT 'xx', c30 varchar(50) NOT NULL DEFAULT 'c30', c3 varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'xx', c6 varchar(6) NOT NULL, PRIMARY KEY (id), KEY i_c3 (c3), KEY i_c30 (c30) ) ENGINE=InnoDB AUTO_INCREMENT=8126490 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

3> gh-ost running， Insert the original table, Because the ghost table added field c6 not null, and there is no default value,gh-ost will report an error: SQL> insert into t1 (c3) values ('a'); Query OK, 1 row affected (0.02 sec)

4> The program error is as follows, and automatically exit after running for a period of time: 2018-05-30 10:22:09 ERROR Error 1364: Field 'c6' doesn't have a default value; query= replace / gh-ost db1._t1_gho / into db1._t1_gho (id, name, c1, c2, c30, c3) values (?, ?, ?, ?, ?, ?) ; args=[8126490 1970-01-01 00:00:00 xx c30 a]

2018-05-30 10:22:09 FATAL Error 1364: Field 'c6' doesn't have a default value; query= replace / gh-ost db1._t1_gho / into db1._t1_gho (id, name, c1, c2, c30, c3) values (?, ?, ?, ?, ?, ?) ; args=[8126490 1970-01-01 00:00:00 xx c30 a]

5> Line number comparison: SQL> select count() from _t1_gho; +----------+ | count() | +----------+ | 5242910 | +----------+ 1 row in set (2.26 sec)

SQL> select count() from t1;
+----------+ | count() | +----------+ | 5242911 | +----------+ 1 row in set (0.93 sec)

thanks shlomi noach

[shlomi-noach]

Sounds to me like everything is working as expected. You tried to do a dangerous thing and gh-ost protected you from corrupting your data.

[ggunson]

gh-ost sets its session sql_mode to STRICT_ALL_TABLES, so an INSERT that doesn't set a value for a NOT NULL column would error, I believe?

I think a simple workaround would be to set a default value for c6 in the gh-ost command, or not setting it NOT NULL.

(What's "dangerous" about this, @shlomi-noach?)

[tomkrouper]

I think the "dangerous" is that gh-ost sees MySQL reporting an error. Error 1364 to be specific. I'm guessing gh-ost doesn't check what kind of error, it just sees an error and gets out.

[sky-9527-github]

Thank you all for your answers I know this is an error for mysql The purpose of raising this question is to explain that dl operations such as pt-ost or gh-ost can't solve the ddl operations in this scenario. Currently, only mysql alter table t1 add c6 varchar(6) not null; Way to solve Provide a case, share, idea for people who use gh-ost tools later thanks

[shlomi-noach]

Hey, no, I think I misinterpreted the problem and the word "dangerous" was indeed irrelevant.