# insert into ... select ... from - auto\_increment messed up

**URL:** <https://forums.percona.com/t/insert-into-select-from-auto-increment-messed-up/2539>\
**Category:** Percona XtraDB Cluster 5.x\
**Created:** [February 26, 2013, 9:53am UTC](https://forums.percona.com/t/insert-into-select-from-auto-increment-messed-up/2539 "2013-02-26T09:53:05Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![PeterBeter](https://avatars.discourse-cdn.com/v4/letter/p/c4cdca/32.png) [@PeterBeter](https://forums.percona.com/u/PeterBeter)\
**Post date:** [February 26, 2013, 9:53am UTC](https://forums.percona.com/t/insert-into-select-from-auto-increment-messed-up/2539/1 "2013-02-26T09:53:05Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![revin](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/revin/32/824_2.png) [@revin](https://forums.percona.com/u/revin)\
**Post date:** [February 28, 2013, 11:53pm UTC](https://forums.percona.com/t/insert-into-select-from-auto-increment-messed-up/2539/2 "2013-02-28T23:53:56Z")

</div>

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;