Data Truncated for column 'X' on Binlog Updates
- Dominant language
- Go
- Stars
- 13.6k
- Forks
- 1.4k
- Avg merge
- 2h 31m
- Merged PRs (30d)
- 4
Description
Hello there! First off, thank you for this fantastic tool. My team chose gh-ost over percona specifically due to the rich featureset that gh-ost provides, including built-in throttling, live commands, and delayed cutover. It's worked like a charm so far.
I've been using gh-ost to update some column types in a very large table (~2 billion rows). I was able to run full tests on our Dev and Staging environments which worked without a hitch. However, when I ran this on our production database I found some behavior that I did not expect (nor did I see this on the previous tests).
```
ERROR Error 1265: Data truncated for column 'type_post' at row 1; query=
replace /* gh-ost `alerts`.`_post_gho` */ into
`alerts`.`_post_gho`
(`id`, `post_number`, `created`, `type_post`)
values
(?, ?, ?, ?)
; args=[2062578694 1389934815931205648_2691385137 ]
```
This error happened about 30 times and then the command panicked and exited. Upon further inspection I realized that the data that was being inserted was invalid - if you look at the number of args (3) vs the number of expected values (4) they don't seem to match up. HOWEVER, this is because the row's `type_post` column has an empty string value, so it doesn't show up in the log.
This `type_post` column in particular is interesting because it's an `enum` type which accepts two values (`post` or `comment`). I was able to inspect the data for other rows which also had invalid values for `type_post` - and it turns out there were several of them. In fact, the dev and staging databases that I tested on ALSO had these invalid rows - but I didn't know why I didn't see this error before. After a bit of digging, I think I understand what's going on.
When gh-ost copies data from the original table to the ghost table, it uses `insert ignore` syntax (see [buildRangeInsertQuery](https://github.com/github/gh-ost/blob/5af70265a3de74ad0f9719ae2c547669a5946ccf/go/sql/builder.go#L178)). This specifically suppresses errors from mysql, including errors which would be raised when inserting invalid data into an [enum field](https://dev.mysql.com/doc/refman/5.7/en/constraint-enum.html). HOWEVER when inspecting binlog events, gh-ost will use `replace into` syntax (see [BuildDMLInsertQuery](https://github.com/github/gh-ost/blob/5af70265a3de74ad0f9719ae2c547669a5946ccf/go/sql/builder.go#L363)).
My hunch is that when testing on dev/staging, gh-ost did not read any update events from the binlog which would have affected an invalid row, which means those rows were copied using `insert ignore` but never updated once they were copied. When running the same command on production, it failed because those rows were updated AFTER being copied.
My question is: should this behavior be handled within gh-ost? I think this particular scenario points out a serious flaw on my own database architecture (which I'm addressing). However, I believe that gh-ost should be consistent in how it handles errors - if [buildRangeInsertQuery](https://github.com/github/gh-ost/blob/5af70265a3de74ad0f9719ae2c547669a5946ccf/go/sql/builder.go#L178) is allowed to ignore errors, then [BuildDMLInsertQuery](https://github.com/github/gh-ost/blob/5af70265a3de74ad0f9719ae2c547669a5946ccf/go/sql/builder.go#L363) should as well. Or maybe both of them should fail on errors?
Contributor guide
Assessment
This issue has not been assessed yet.