some more points, that i read at mysql documentation and mariadb jira (i didn't read the mariadb kb yet) ---- 1) reported at MDEV-7314, from Jean Weisbuch (jean@phpnet.org) ... Hitting a duplicate key on such query means that no lock has been issued. The KB on concurrent INSERTS on *MyISAM* states that : If the binary log is used, CREATE TABLE ... SELECT and INSERT ... SELECT statements cannot use concurrent inserts. These statements acquire a read lock on the table, so concurrent inserts will need to wait. This way the log can be safely used to restore data. ---- 2) from mysql documentation http://dev.mysql.com/doc/refman/5.7/en/insert-select.html - To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts for INSERT ... SELECT <http://dev.mysql.com/doc/refman/5.7/en/insert-select.html> statements. The order in which rows are returned by a SELECT <http://dev.mysql.com/doc/refman/5.7/en/select.html> statement with no ORDER BY clause is not determined. This means that, when using replication, there is no guarantee that such a SELECT returns rows in the same order on the master and the slave; this can lead to inconsistencies between them. To prevent this from occurring, you should always write INSERT ... SELECT statements that are to be replicated as INSERT ... SELECT ... ORDER BY *column*. The choice of *column* does not matter as long as the same order for returning the rows is enforced on both the master and the slave. See also Section 17.4.1.16, “Replication and LIMIT” <http://dev.mysql.com/doc/refman/5.7/en/replication-features-limit.html>. Due to this issue, INSERT ... SELECT ON DUPLICATE KEY UPDATE <http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html> and INSERT IGNORE ... SELECT <http://dev.mysql.com/doc/refman/5.7/en/insert-select.html>statements are flagged as unsafe for statement-based replication. With this change, such statements produce a warning in the log when using statement-based mode and are logged using the row-based format when usingMIXED mode. (Bug #11758262, Bug #50439) See also Section 17.1.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication” <http://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html>. In MySQL 5.7, an INSERT ... SELECT statement that acted on partitioned tables using a storage engine such asMyISAM <http://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html> that employs table-level locks locks all partitions of the target table; however, only those partitions that are actually read from the source table are locked. (This does not occur with tables using storage engines such as InnoDB <http://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html> that employ row-level locking.) See Section 18.6.4, “Partitioning and Locking” <http://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-locking.html>, for more information. ---- 3) from http://dev.mysql.com/doc/refman/5.7/en/replication-features-limit.html 17.4.1.16 Replication and LIMIT Statement-based replication of LIMIT clauses in DELETE <http://dev.mysql.com/doc/refman/5.7/en/delete.html>, UPDATE <http://dev.mysql.com/doc/refman/5.7/en/update.html>, and INSERT ... SELECT <http://dev.mysql.com/doc/refman/5.7/en/insert-select.html> statements is unsafe since the order of the rows affected is not defined. (Such statements can be replicated correctly with statement-based replication only if they also contain an ORDER BY clause.) When such a statement is encountered: - When using STATEMENT mode, a warning that the statement is not safe for statement-based replication is now issued. Currently, when using STATEMENT mode, warnings are issued for DML statements containing LIMIT even when they also have an ORDER BY clause (and so are made deterministic). This is a known issue. (Bug #42851) - When using MIXED mode, the statement is now automatically replicated using row-based mode. ------ i don't see a standard yet, any idea?