You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
It looks like there are issues if table has "int unsigned" or "bigint unsigned" columns #157
while copying the table, the following error messages are reported:
Copy: 111000/7058065 1.6%; Applied: 0; Backlog: 21/100; Time: 1m10s(total), 1m4s(copy); streamer: mysql-bin.022930:833676076; ETA: 1h6m46s
2016-08-14 13:49:43 ERROR Error 1264: Out of range value for column 'ipNum' at row 1
2016-08-14 13:49:44 ERROR Error 1264: Out of range value for column 'ipNum' at row 1
2016-08-14 13:49:45 ERROR Error 1264: Out of range value for column 'ipNum' at row 1
The table schema includes the following definition:
...
`ip` varchar(15) NOT NULL,
`ipNum` int(10) unsigned NOT NULL,
`dbTime` int(10) unsigned NOT NULL DEFAULT '0',
...
The ipNum values are bigger then int signed max values. The output does not indicates what exactly are the violating value or row number, so its not possible to verify the issue
So probably we're dealing with wrong error message here. Another reason that the copy could fail in my case is that there is an another enum column in the modified table and in has the rows with an "empty" values in it (happens when inserting row with the wrong value of enum)
The error does state Out of range value for column 'ipNum' at row 1. For completeness, can you run again with --debug --stack and paste the output around the error?
This gets high priority. With relaxed sql_mode it may mean injecting wrong value.
Shedding more light on what happens: a RBR event consisting of int unsigned value, with val > max-signed, is read in code as signed int, causing value to be negative; then the app writes this value onto ghost table.
With sql_mode=STRICT_ALL_TABLES this is caught by MySQL and rejected; but with non-strict sql_mode this can go silent.
OK, looking more deeply into this I believe I will solve this on the app side, recognizing the unsigned columns and converting the datatype. Expect to fix by tomorrow.
@borizio I've verified on multiple use cases on my side, I'm happy if you can verify on yours as well. Will close for now, but please do comment & update on this thread.
@siddontang to clarify, I have not modified code in go-mysql but rather used my application's logic to determine that the value returned by go-mysql is in fact unsigned, and make the casting on my side.
I've also worked around this limitation: golang/go#9373 (golang does not support high values for bigint unsigned, i.e. uint64) by converting such values to string.
Activity
shlomi-noach commentedon Aug 15, 2016
@borizio thank you: I will try to reproduce and fix.
What is then the final behavior of
gh-ostin your case? Does it bail out (as I would expect)?borizio commentedon Aug 15, 2016
It retries some predefined number of times, giving an ERROR message, then
fails with FATAL message, living *.gho and *.ghc tables
shlomi-noach commentedon Aug 15, 2016
@borizio
I'm meanwhile unable to reproduce, trying maxvalues for
int unsignedandbigint unsigned.The fact
gh-ostbailed out is good and desired behavior, thank you for confirming.borizio commentedon Aug 15, 2016
So probably we're dealing with wrong error message here. Another reason that the copy could fail in my case is that there is an another enum column in the modified table and in has the rows with an "empty" values in it (happens when inserting row with the wrong value of enum)
shlomi-noach commentedon Aug 15, 2016
The error does state
Out of range value for column 'ipNum' at row 1. For completeness, can you run again with--debug --stackand paste the output around the error?borizio commentedon Aug 15, 2016
Here is the relevant output parts:
shlomi-noach commentedon Aug 15, 2016
nitpick, to markdown large sections of code, prepend and append with three backtics: ``` on their own line; see how I've edited your own comment.
borizio commentedon Aug 15, 2016
@shlomi-noach , Thanks for a tip! Hope this debug output will shed some light...
shlomi-noach commentedon Aug 15, 2016
So the error is MySQL error 1264, propagated to
gh-ost. Can you output theshow create table _<your-table>_gho?borizio commentedon Aug 15, 2016
shlomi-noach commentedon Aug 15, 2016
@borizio I'm wondering whether you are able to test PR 158, as I'm unable to reproduce. The steps would be:
and get the resulting binary from
/tmp/gh-ostshlomi-noach commentedon Aug 15, 2016
Also, I'm guessing you must have
sql_mode=STRICT_ALL_TABLES, am I correct?shlomi-noach commentedon Aug 15, 2016
Hold that thought, I'm now able to reproduce the issue.
shlomi-noach commentedon Aug 15, 2016
I believe the offending code to be in: https://github.com/github/gh-ost/blob/master/vendor/github.com/siddontang/go-mysql/replication/row_event.go#L350
part of the
go-mysqllibrary.It seems like while reading from the binary long it ignores the
UNSIGNED_FLAGindicator and always produces a signed response.@siddontang am I correct in this analysis?
shlomi-noach commentedon Aug 15, 2016
This gets high priority. With relaxed
sql_modeit may mean injecting wrong value.Shedding more light on what happens: a RBR event consisting of
int unsignedvalue, with val > max-signed, is read in code as signed int, causing value to be negative; then the app writes this value onto ghost table.With
sql_mode=STRICT_ALL_TABLESthis is caught by MySQL and rejected; but with non-strictsql_modethis can go silent.Roguelazer commentedon Aug 15, 2016
Is there any way to explicitly set the sql_mode for gh-ost, or does it just use the current global default?
siddontang commentedon Aug 16, 2016
@shlomi-noach
I remember that we can't know whether the integer is unsigned or not, so here all use integer instead like go SQL driver uses int64.
shlomi-noach commentedon Aug 16, 2016
@siddontang thank you -- I will proceed to read all < 64bit integer types as signedint64. As for 64 bit types, still unsure what to do. Makes sense?shlomi-noach commentedon Aug 16, 2016
OK, looking more deeply into this I believe I will solve this on the app side, recognizing the unsigned columns and converting the datatype. Expect to fix by tomorrow.
shlomi-noach commentedon Aug 17, 2016
#160 fixes this, putting this to testing.
shlomi-noach commentedon Aug 17, 2016
Also notable: golang/go#9373
shlomi-noach commentedon Aug 18, 2016
Closed by #160 and released in https://github.com/github/gh-ost/releases/tag/v1.0.10
@borizio I've verified on multiple use cases on my side, I'm happy if you can verify on yours as well. Will close for now, but please do comment & update on this thread.
shlomi-noach commentedon Aug 18, 2016
@Roguelazer discussion on
sql_modein #164shlomi-noach commentedon Aug 18, 2016
@siddontang to clarify, I have not modified code in
go-mysqlbut rather used my application's logic to determine that the value returned bygo-mysqlis in fact unsigned, and make the casting on my side.I've also worked around this limitation: golang/go#9373 (golang does not support high values for
bigint unsigned, i.e.uint64) by converting such values to string.See: