MySQL Replication of INSERT... SELECT with ORDER BY

The documentation page for INSERT ... SELECT states that for 开发者_开发问答INSERT ... SELECT to work with replication, an ORDER BY some column must be used so that rows may be inserted in a predictable order, therefore auto-increment columns will function correctly.

This has me wondering... when you do an ORDER BY and the column you pick is non-unique, what does MySQL use to decide further ordering? It must be deterministic, otherwise the documentation would say that you need to choose an ORDER BY unique column, yes? Or is the documentation wrong?

If you use an ORDER BY with a column which does not have distinct values for all matched rows, the order is nondeterminstic, yes.

This does break statement-based replication, IF the inserted order matters. The inserted order certainly matters if you use an auto_increment column in the destination table, but often doesn't otherwise.

Order also matters if you have a LIMIT on the statement (update and delete too).

However, if you use row-based replicaiton you can do pretty much anything you like and not break replication. At least, not unless you really try.

These rules are so complicated, that I recommend that you just ALWAYS use a completely determinstic ORDER BY on an insert... select, or an UPDATE with a LIMIT.

Row-based replication is also your friend. I've not really used it much in production, but understand that

• It is more similar to how replication works in other databases
• Most of the time it Just Works.