maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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@xxxxxxxxxx) ...

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?