github / github/gh-ost

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

Open
#758 3 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
13.6k
Forks
1.4k
Avg merge
2h 31m
Merged PRs (30d)
4

Description

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 ... [] [] ... ]
```

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 ?

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.