insert into ... select ... from - auto_increment messed up

  • Filter
  • Time
  • Show
Clear All
new posts

  • insert into ... select ... from - auto_increment messed up

    Setup: 2 Nodes with
    mysql Ver 14.14 Distrib 5.5.29, for Linux (x86_64) using readline 5.1
    on Ubuntu 12.04/x64

    I've a Table MyTempTable with id (auto_increment), ShortText (varchar 25) and LongText (varchar 255). everything is fine:

    1 Short_01 Long_01
    2 Short_02 Long_02
    3 Short_03 Long_03
    4 Short_04 Long_04

    If i copy the content (ShortText, LongText) to another table, the auto_increment column is f*cked uo between the nodes:

    my query: "INSERT INTO MyFinalTable (ShortText,LongText) SELECT ShortText,LongText FROM MyTempTable"

    Now on node_01:
    1 Short_01 Long_01
    3 Short_02 Long_02
    5 Short_03 Long_03
    7 Short_04 Long_04

    Now on node_02:
    1 Short_01 Long_01
    2 Short_02 Long_02
    4 Short_03 Long_03
    6 Short_04 Long_04

    what's wrong with that query and the galera cluster? it looks like - instead of inserting on one node and then copy/replicate - the wsrep runs inserts on each node...
    how to fix/circumvent that?

  • #2

    This is expected behavior, for 2 reasons: 1) to avoid conflicts on auto-incrementing rows, PXC automatically handles the auto_increment_increment columns to be different on each node 2) When you did the INSERT INTO SELECT, you did not include the auto-incrementing column hence the target table generating a different key for each.

    A more deterministic approach is to:

    INSERT INTO MyFinalTable (id, ShortText,LongText) SELECT id,ShortText,LongText FROM MyTempTable;
    Our documentation has a lot of answers about common questions on Percona software, have you checked there before posting that question here? http://www.percona.com/forums/core/i...lies/smile.png

    Join us at the annual Percona Live MySQL Users Conference - http://www.percona.com/live/mysql-conference-2014/