Error 1048: Column 'xxx' cannot be null when switching a column from NULLABLE to NOT NULL

[babinomec]

When trying to change a column from NULLABLE to NOT NULL, I run in the following error : Error 1048: Column 'c186' cannot be null

Version :

# ./gh-ost --version
e48844de0bee9a8db611a06cd6080cac4dab25cb

Call :

./gh-ost \
  --user="ghost" \
  --password="xxxx" \
  --host="slave" \
  --allow-master-master \
  --assume-master-host="master" \
  --database="database" \
  --table="table" \
  --alter="modify c186 text not null default ''" \
  --verbose \
  --switch-to-rbr \
  --skip-foreign-key-checks \
  --skip-strict-mode \
  --initially-drop-ghost-table \
  --initially-drop-old-table \
  --exact-rowcount \
  --ok-to-drop-table \
  --execute

Relevant table structure :

CREATE TABLE `table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  ...
  `c185` tinytext NOT NULL,
  `c186` text,
  `c187` text NOT NULL,
  ...
  PRIMARY KEY (`id`),
  ...
) ENGINE=InnoDB AUTO_INCREMENT=3156788 DEFAULT CHARSET=utf8

Error from log :

2019-06-20 17:48:54 ERROR Error 1048: Column 'c186' cannot be null; query=
                        replace /* gh-ost `database`.`_table_gho` */ into
                                `database`.`_table_gho`
                                        (`id`, ..., `c185`, `c186`, `c187`, ...)
                                values
                                        (?, ..., ?, ?, ?, ... )
                ; args=[3157562 ... [] <nil> [] ... ]

From what I understand it's probably from queries in the binlog being applied, when those don't contain every column, they are written in RBR with their default value, hence trying to insert a NULL value (old default) to a NOT NULL column (new structure).

Is there a way to address that ?

[zmoazeni]

Do you have any current rows where c186 is null? If so you'll have to address that first. I imagine this is the MySQL strictness setting raising the error.

[babinomec]

Do you have any current rows where c186 is null? If so you'll have to address that first.

Two things :

1. yes, probably (it works with an alter though)
2. according to the ids it's most likely a new line ( AUTO_INCREMENT=3156788 vs. id=3157562 in the query)

I'll try to remove the original NULL values, but I suspect I'll get the error again when inserting a new line.

I imagine this is the MySQL strictness setting raising the error.

I used --skip-strict-mode but I'm not sure it changes much in this case.

[shlomi-noach]

I'll try to remove the original NULL values, but I suspect I'll get the error again when inserting a new line.

You'll need to address both. First, make sure your new queries do not contain NULLs, or rather, make sure they have some default value. Then, backfill your existing NULL values.

For gh-ost to implicitly solve this - it would be possible, but with some considerable effort and probably also risk (gh-ost will be modifying data on your behalf, I can see how this can go wrong).