maria-developers team mailing list archive

[Pavel Ivanov <pivanof@xxxxxxxxxx>]

> ShouldI understand that MariaDB dos not prevent this (does not raise an
> error), but it is still unsafe as binlog - and thus replication - may be
> corrupted?

No, the set of statements you gave as example is safe. Barring of
course the standard MyISAM safety problem that if slave is stopped or
disconnected from master before it received COMMIT binlog event then
you'll have changes to tab1 present on the slave and changes to tab2
not present.
What I meant talking about replication unsafe statements is e.g. this:

BEGIN;
INSERT INTO tab1 VALUES (1);
INSERT INTO tab2 VALUES (2);
UPDATE tab1 SET a = (SELECT a FROM tab2 WHERE a = 2) WHERE a = 1;
COMMIT;

IIRC, these will give different results on master and slave if
binlog_format = statement.


Pavel


On Sun, May 11, 2014 at 3:57 AM, Peter Laursen <peter_laursen@xxxxxxxxxx> wrote:
> Thanks for the reply, but I am afraid I don't fully understand.
>
> Here is a simple test case with mySQL 5.6.17:
>
> 1)
> You need all those in configuration [mysqld) section
>
> log-bin
> log-slave-updates
> gtid_mode = ON
> enforce_gtid_consistency = ON
>
>
> 2)
> Then this small script will reproduce:
>
>
> DROP DATABASE IF EXISTS `gtid_test`;
> CREATE DATABASE `gtid_test`;
> USE `gtid_test`;
>
> CREATE TABLE `tab1` (
> `a` INT DEFAULT NULL
> ) ENGINE=MYISAM ;
>
> CREATE TABLE `tab2` (
> `b` INT
> ) ENGINE=INNODB;
>
> --start a transaction
> SET autocommit=0;
>
> INSERT INTO `tab1` VALUES (1);
> INSERT INTO `tab2` VALUES (1);
>
> -- now the first UPDATE to the MyISAM table in the transaction triggers this
> error:
>
> UPDATE `tab1` SET `a` = 5 WHERE `a` = 1;
>
> -- and you'll get:
> --
> -- Error Code: 1785
> -- When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional
> tables can only be done in either autocommitted statements or
> single-statement transactions, and never in the same statement as updates to
> transactional tables.
>
>
> 3)
> ShouldI understand that MariaDB dos not prevent this (does not raise an
> error), but it is still unsafe as binlog - and thus replication - may be
> corrupted?
>
>
> Thanks!
>
> -- Peter
>
>
> On Sat, May 10, 2014 at 6:09 PM, Pavel Ivanov <pivanof@xxxxxxxxxx> wrote:
>>
>> There is no such limitation in MariaDB as well as no limitation on
>> CREATE TABLE ... SELECT.
>>
>> Although beware that mixing statements changing MyISAM and InnoDB
>> tables in statement binlog mode you may get different results from the
>> statements on slaves.
>>
>> Pavel
>>
>> On Sat, May 10, 2014 at 3:48 AM, Peter Laursen <peter_laursen@xxxxxxxxxx>
>> wrote:
>> > GTID implementation in MySQL 5.6 has the limitation as described here:
>> >
>> > https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-restrictions.html
>> >
>> >
>> > "nontransactional storage engines such as MyISAM cannot be made in the
>> > same
>> > statement or transaction as updates to tables using transactional
>> > storage
>> > engines such as InnoDB."
>> >
>> > If you try you will get the error:
>> > Error Code: 1785
>> > When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional
>> > tables can only be done in either autocommitted statements or
>> > single-statement transactions, and never in the same statement as
>> > updates to
>> > transactional tables.
>> >
>> > Is there same or similar/other limitations with GTID in MariaDB 10?
>> >
>> > -- Peter Laursen
>> > -- Webyog
>> >
>> > _______________________________________________
>> > Mailing list: https://launchpad.net/~maria-developers
>> > Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
>> > Unsubscribe : https://launchpad.net/~maria-developers
>> > More help   : https://help.launchpad.net/ListHelp
>> >
>
>