github / github/gh-ost

Data Truncated for column 'X' on Binlog Updates

Open
#469 1 comment 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

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

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.