Skip to content

It looks like there are issues if table has "int unsigned" or "bigint unsigned" columns #157

Closed

Description

@borizio

I am running the following command:

./gh-ost \
--max-load=Threads_running=100 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--user="root" \
--password='xxx' \
--host=localhost \
--allow-on-master \
--database="db" \
--table="some_table" \
--verbose \
--alter="modify source varchar(100) DEFAULT NULL" \
--switch-to-rbr \
--allow-master-master \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--initially-drop-ghost-table \
--initially-drop-old-table \
--ok-to-drop-table \
--execute  

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

Activity

  1. shlomi-noach commented on Aug 15, 2016

    @shlomi-noach
    Contributor

    @borizio thank you: I will try to reproduce and fix.

    What is then the final behavior of gh-ost in your case? Does it bail out (as I would expect)?

  2. self-assigned this
    on Aug 15, 2016
  3. borizio commented on Aug 15, 2016

    @borizio
    Author

    It retries some predefined number of times, giving an ERROR message, then
    fails with FATAL message, living *.gho and *.ghc tables

  4. shlomi-noach commented on Aug 15, 2016

    @shlomi-noach
    Contributor

    @borizio

    I'm meanwhile unable to reproduce, trying maxvalues for int unsigned and bigint unsigned.

    The fact gh-ost bailed out is good and desired behavior, thank you for confirming.

  5. borizio commented on Aug 15, 2016

    @borizio
    Author

    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)

  6. shlomi-noach commented on Aug 15, 2016

    @shlomi-noach
    Contributor

    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?

  7. borizio commented on Aug 15, 2016

    @borizio
    Author

    Here is the relevant output parts:

    2016-08-15 08:56:39 INFO Listening on unix socket file: /tmp/gh-ost.gsstats_sh1.gs_raw_linkbacks.sock
    2016-08-15 08:56:39 DEBUG Reading migration range according to key: PRIMARY
    2016-08-15 08:56:39 INFO Migration min values: [524099157,2016-07-17 00:00:03]
    2016-08-15 08:56:39 DEBUG Reading migration range according to key: PRIMARY
    2016-08-15 08:56:39 INFO Migration max values: [548566659,2016-08-15 08:56:36]
    2016-08-15 08:56:39 DEBUG Operating until row copy is complete
    # Migrating gsstats_sh1.gs_raw_linkbacks; Ghost table is gsstats_sh1._gs_raw_linkbacks_gho
    # Migrating eu1a-statdb1-1:3306; inspecting eu1a-statdb1-1:3306; executing on eu1a-statdb1-1
    # Migration started at Mon Aug 15 08:56:36 +0100 2016
    # chunk-size: 1000; max-lag-millis: 1500ms; max-load: Threads_running=100; critical-load: Threads_running=1000; nice-ratio: 0.000000
    # throttle-additional-flag-file: /tmp/gh-ost.throttle
    # postpone-cut-over-flag-file: /tmp/ghost.postpone.flag
    # panic-flag-file: /tmp/ghost.panic.flag
    # Serving on unix socket: /tmp/gh-ost.gsstats_sh1.gs_raw_linkbacks.sock
    2016-08-15 08:56:39 DEBUG Getting nothing in the write queue. Sleeping...
    Copy: 0/11347506 0.0%; Applied: 0; Backlog: 0/100; Time: 2s(total), 0s(copy); streamer: mysql-bin.022973:807902390; ETA: N/A
    Copy: 0/11347506 0.0%; Applied: 0; Backlog: 0/100; Time: 3s(total), 1s(copy); streamer: mysql-bin.022973:811818401; ETA: N/A
    2016-08-15 08:56:40 DEBUG Issued INSERT on range: [524099157,2016-07-17 00:00:03]..[524101407,2016-07-17 00:03:31]; iteration: 0; chunk-size: 1000
    2016-08-15 08:56:40 ERROR Error 1264: Out of range value for column 'ipNum' at row 1
    goroutine 70 [running]:
    runtime/debug.Stack(0x0, 0x0, 0x0)
            /usr/local/Cellar/go/1.6.2/libexec/src/runtime/debug/stack.go:24 +0x80
    runtime/debug.PrintStack()
            /usr/local/Cellar/go/1.6.2/libexec/src/runtime/debug/stack.go:16 +0x18
    github.com/github/gh-ost/vendor/github.com/outbrain/golib/log.logErrorEntry(0x2, 0x7f531cc12360, 0xc820177f20, 0x0, 0x0)
            /Users/shlomi-noach/dev/go/src/github.com/github/gh-ost/vendor/github.com/outbrain/golib/log/log.go:178 +0x159
    github.com/github/gh-ost/vendor/github.com/outbrain/golib/log.Errore(0x7f531cc12360, 0xc820177f20, 0x0, 0x0)
            /Users/shlomi-noach/dev/go/src/github.com/github/gh-ost/vendor/github.com/outbrain/golib/log/log.go:224 +0x40
    github.com/github/gh-ost/vendor/github.com/outbrain/golib/sqlutils.execInternal(0xecf436c00, 0xc820112210, 0xc8203a4000, 0x1c0, 0xc8203dc000, 0x16, 0x20, 0x0, 0x0, 0x0, ...)
            /Users/shlomi-noach/dev/go/src/github.com/github/gh-ost/vendor/github.com/outbrain/golib/sqlutils/sqlutils.go:306 +0x1b3
    github.com/github/gh-ost/vendor/github.com/outbrain/golib/sqlutils.Exec(0xc820112210, 0xc8203a4000, 0x1c0, 0xc8203dc000, 0x16, 0x20, 0x0, 0x0, 0x0, 0x0)
            /Users/shlomi-noach/dev/go/src/github.com/github/gh-ost/vendor/github.com/outbrain/golib/sqlutils/sqlutils.go:314 +0x86
    github.com/github/gh-ost/go/logic.(*Applier).ApplyDMLEventQuery(0xc8201950c0, 0xc82004ed00, 0x0, 0x0)
            /Users/shlomi-noach/dev/go/src/github.com/github/gh-ost/go/logic/applier.go:856 +0xf1
    github.com/github/gh-ost/go/logic.(*Migrator).addDMLEventsListener.func1.1(0x0, 0x0)
            /Users/shlomi-noach/dev/go/src/github.com/github/gh-ost/go/logic/migrator.go:1076 +0x39
    github.com/github/gh-ost/go/logic.(*Migrator).retryOperation(0xc8200eb7c0, 0xc8203b8040, 0x0, 0x0, 0x0, 0x0, 0x0)
            /Users/shlomi-noach/dev/go/src/github.com/github/gh-ost/go/logic/migrator.go:256 +0x74
    github.com/github/gh-ost/go/logic.(*Migrator).executeWriteFuncs(0xc8200eb7c0, 0x0, 0x0)
            /Users/shlomi-noach/dev/go/src/github.com/github/gh-ost/go/logic/migrator.go:1181 +0x144
    created by github.com/github/gh-ost/go/logic.(*Migrator).Migrate
            /Users/shlomi-noach/dev/go/src/github.com/github/gh-ost/go/logic/migrator.go:419 +0x6ec
    
    ....
    
    2016-08-15 08:58:39 FATAL Error 1264: Out of range value for column 'ipNum' at row 1
    goroutine 19 [running]:
    runtime/debug.Stack(0x0, 0x0, 0x0)
            /usr/local/Cellar/go/1.6.2/libexec/src/runtime/debug/stack.go:24 +0x80
    runtime/debug.PrintStack()
            /usr/local/Cellar/go/1.6.2/libexec/src/runtime/debug/stack.go:16 +0x18
    github.com/github/gh-ost/vendor/github.com/outbrain/golib/log.logErrorEntry(0x0, 0x7f531cc12360, 0xc820814600, 0x0, 0x0)
            /Users/shlomi-noach/dev/go/src/github.com/github/gh-ost/vendor/github.com/outbrain/golib/log/log.go:178 +0x159
    github.com/github/gh-ost/vendor/github.com/outbrain/golib/log.Fatale(0x7f531cc12360, 0xc820814600, 0x0, 0x0)
            /Users/shlomi-noach/dev/go/src/github.com/github/gh-ost/vendor/github.com/outbrain/golib/log/log.go:255 +0x40
    github.com/github/gh-ost/go/logic.(*Migrator).listenOnPanicAbort(0xc8200eb7c0)
            /Users/shlomi-noach/dev/go/src/github.com/github/gh-ost/go/logic/migrator.go:346 +0x69
    created by github.com/github/gh-ost/go/logic.(*Migrator).Migrate
            /Users/shlomi-noach/dev/go/src/github.com/github/gh-ost/go/logic/migrator.go:371 +0x305
    
  8. shlomi-noach commented on Aug 15, 2016

    @shlomi-noach
    Contributor

    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.

  9. borizio commented on Aug 15, 2016

    @borizio
    Author

    @shlomi-noach , Thanks for a tip! Hope this debug output will shed some light...

  10. shlomi-noach commented on Aug 15, 2016

    @shlomi-noach
    Contributor

    So the error is MySQL error 1264, propagated to gh-ost. Can you output the show create table _<your-table>_gho?

  11. borizio commented on Aug 15, 2016

    @borizio
    Author
    CREATE TABLE `_whatever_gho` (
      `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `time` datetime NOT NULL,
      `partnerID` int(10) unsigned NOT NULL DEFAULT '0',
      `siteID` bigint(20) unsigned DEFAULT NULL,
      `provider` smallint(5) unsigned NOT NULL,
      `PartnerData` varchar(100) DEFAULT NULL,
      `PartnerData_hash` bigint(20) unsigned DEFAULT NULL,
      `urlHash` bigint(20) unsigned NOT NULL,
      `URL` varchar(2048) NOT NULL,
      `referrerDomain` varchar(2048) DEFAULT NULL,
      `country` char(2) NOT NULL DEFAULT '',
      `state` varchar(15) NOT NULL DEFAULT '',
      `city` varchar(20) NOT NULL DEFAULT '',
      `ip` varchar(15) NOT NULL DEFAULT '',
      `ipNum` int(10) unsigned NOT NULL,
      `cookieID` varchar(100) DEFAULT NULL,
      `server` varchar(15) NOT NULL DEFAULT '',
      `referrer` varchar(2048) DEFAULT NULL,
      `UserAgent` varchar(1024) NOT NULL DEFAULT '',
      `Filter` tinyint(1) NOT NULL DEFAULT '0',
      `siteAccountID` varchar(255) DEFAULT NULL,
      `source` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`ID`,`time`)
    ) ENGINE=InnoDB AUTO_INCREMENT=548566664 DEFAULT CHARSET=utf8
    /*!50100 PARTITION BY LIST (dayofmonth(time))
    (PARTITION p1 VALUES IN (1) ENGINE = InnoDB,
     PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
     PARTITION p3 VALUES IN (3) ENGINE = InnoDB,
     PARTITION p4 VALUES IN (4) ENGINE = InnoDB,
     PARTITION p5 VALUES IN (5) ENGINE = InnoDB,
     PARTITION p6 VALUES IN (6) ENGINE = InnoDB,
     PARTITION p7 VALUES IN (7) ENGINE = InnoDB,
     PARTITION p8 VALUES IN (8) ENGINE = InnoDB,
     PARTITION p9 VALUES IN (9) ENGINE = InnoDB,
     PARTITION p10 VALUES IN (10) ENGINE = InnoDB,
     PARTITION p11 VALUES IN (11) ENGINE = InnoDB,
     PARTITION p12 VALUES IN (12) ENGINE = InnoDB,
     PARTITION p13 VALUES IN (13) ENGINE = InnoDB,
     PARTITION p14 VALUES IN (14) ENGINE = InnoDB,
     PARTITION p15 VALUES IN (15) ENGINE = InnoDB,
     PARTITION p16 VALUES IN (16) ENGINE = InnoDB,
     PARTITION p17 VALUES IN (17) ENGINE = InnoDB,
     PARTITION p18 VALUES IN (18) ENGINE = InnoDB,
     PARTITION p19 VALUES IN (19) ENGINE = InnoDB,
     PARTITION p20 VALUES IN (20) ENGINE = InnoDB,
     PARTITION p21 VALUES IN (21) ENGINE = InnoDB,
     PARTITION p22 VALUES IN (22) ENGINE = InnoDB,
     PARTITION p23 VALUES IN (23) ENGINE = InnoDB,
     PARTITION p24 VALUES IN (24) ENGINE = InnoDB,
     PARTITION p25 VALUES IN (25) ENGINE = InnoDB,
     PARTITION p26 VALUES IN (26) ENGINE = InnoDB,
     PARTITION p27 VALUES IN (27) ENGINE = InnoDB,
     PARTITION p28 VALUES IN (28) ENGINE = InnoDB,
     PARTITION p29 VALUES IN (29) ENGINE = InnoDB,
     PARTITION p30 VALUES IN (30) ENGINE = InnoDB,
     PARTITION p31 VALUES IN (31) ENGINE = InnoDB) */
    
  12. shlomi-noach commented on Aug 15, 2016

    @shlomi-noach
    Contributor

    @borizio I'm wondering whether you are able to test PR 158, as I'm unable to reproduce. The steps would be:

    git clone git@github.com:github/gh-ost.git
    cd gh-ost
    git checkout elaborate-error-output-apply-dml
    bash build.sh
    

    and get the resulting binary from /tmp/gh-ost

  13. shlomi-noach commented on Aug 15, 2016

    @shlomi-noach
    Contributor

    Also, I'm guessing you must have sql_mode=STRICT_ALL_TABLES, am I correct?

  14. shlomi-noach commented on Aug 15, 2016

    @shlomi-noach
    Contributor

    Hold that thought, I'm now able to reproduce the issue.

  15. shlomi-noach commented on Aug 15, 2016

    @shlomi-noach
    Contributor

    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-mysql library.

    It seems like while reading from the binary long it ignores the UNSIGNED_FLAG indicator and always produces a signed response.

    @siddontang am I correct in this analysis?

  16. shlomi-noach commented on Aug 15, 2016

    @shlomi-noach
    Contributor

    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.

  17. Roguelazer commented on Aug 15, 2016

    @Roguelazer
    Contributor

    Is there any way to explicitly set the sql_mode for gh-ost, or does it just use the current global default?

  18. siddontang commented on Aug 16, 2016

    @siddontang

    @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.

  19. shlomi-noach commented on Aug 16, 2016

    @shlomi-noach
    Contributor

    @siddontang thank you -- I will proceed to read all < 64bit integer types as signed int64. As for 64 bit types, still unsure what to do. Makes sense?

  20. shlomi-noach commented on Aug 16, 2016

    @shlomi-noach
    Contributor

    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.

  21. shlomi-noach commented on Aug 17, 2016

    @shlomi-noach
    Contributor

    #160 fixes this, putting this to testing.

  22. shlomi-noach commented on Aug 17, 2016

    @shlomi-noach
    Contributor

    Also notable: golang/go#9373

  23. shlomi-noach commented on Aug 18, 2016

    @shlomi-noach
    Contributor

    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.

  24. shlomi-noach commented on Aug 18, 2016

    @shlomi-noach
    Contributor

    @Roguelazer discussion on sql_mode in #164

  25. shlomi-noach commented on Aug 18, 2016

    @shlomi-noach
    Contributor

    @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.

    See:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions