Error 1364

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

Non viene indicato alcun file sorgente né alcun test. Inizia riproducendo il comando segnalato con MySQL 5.7.19 e traccia il modo in cui gh-ost copia le righe dopo l’aggiunta di una colonna NOT NULL senza un valore predefinito. Il lavoro è completato quando la migrazione non termina più con Error 1364 e il numero di righe della tabella originale e della tabella ghost rimane coerente.

Scritto dal modello di indicizzazione a partire dal testo della issue.