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

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

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

Peter,

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;