Re: [Maria-developers] Question about GTIDs in MariaBD
With autocommit=1 every statement runs in its own transaction, so obviousluy .. yes! MyISAM is/used to be completely 'transaction-agnosstic'. But GTIDs seem to confict with non-transactional storage engines (as far as I can understand). MySQL/Oracle have "solved" (!) this by not allowing updates to tables using transactional engines and non-transactional storage engines in same transaction. However to me it is nonsense that transactions apply (have any effect at all) for non-transactional storage engines. As a consequence I consider this solution (what must have been enforced in the server layer and not the storeage engine layer) by Oracle 'a dirty hack' (unless somewone can explain me why it it necessary). -- Peter On Sun, May 11, 2014 at 4:42 PM, Roberto Spadim <roberto@spadim.com.br>wrote:
With autoclmmit=1 works?
Em domingo, 11 de maio de 2014, Peter Laursen <peter_laursen@webyog.com> escreveu:
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@google.com> 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@webyog.com> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
Op zondag 11 mei 2014 16:59:10 schreef Peter Laursen:
With autocommit=1 every statement runs in its own transaction, so obviousluy .. yes!
MyISAM is/used to be completely 'transaction-agnosstic'. But GTIDs seem to confict with non-transactional storage engines (as far as I can understand). MySQL/Oracle have "solved" (!) this by not allowing updates to tables using transactional engines and non-transactional storage engines in same transaction.
I don't get this... if MyISAM is transaction-agnostic, why would you use it in a transaction to begin with and even combined with a transactional database? it seems to me that if you rely on something like this, one shouldn't use MyISAM, or expect it to cope with this... (even though this example should be safe), (well, because it's transaction-agnostic, one can't asume it uses the same values when using transactional storage engines? of course, i'm not an expert, so i probably get a wrong idea of the problem. Is there some kind of example that _is_ unsafe somehow? wrt GTID being conflicting? since you speak of GTID, i assume you mean the replication safety? (ie: different results?)
However to me it is nonsense that transactions apply (have any effect at all) for non-transactional storage engines. As a consequence I consider this solution (what must have been enforced in the server layer and not the storeage engine layer) by Oracle 'a dirty hack' (unless somewone can explain me why it it necessary).
-- Peter
On Sun, May 11, 2014 at 4:42 PM, Roberto Spadim <roberto@spadim.com.br>wrote:
With autoclmmit=1 works?
Em domingo, 11 de maio de 2014, Peter Laursen <peter_laursen@webyog.com> escreveu:
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@google.com> 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@webyog.com>
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.h tml
"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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
@AL13N ... it affects scripts generated by an automatic maintenance tool. The tool does updates to multiple tables in one transaction and does not check for the storage engine. It used to work, but now fails on schemas that have a mix of MyISAM and InnoDB tables. (simplifed explanation!) -- Peter On Sun, May 11, 2014 at 10:44 PM, AL13N <alien@mageia.org> wrote:
Op zondag 11 mei 2014 16:59:10 schreef Peter Laursen:
With autocommit=1 every statement runs in its own transaction, so obviousluy .. yes!
MyISAM is/used to be completely 'transaction-agnosstic'. But GTIDs seem to confict with non-transactional storage engines (as far as I can understand). MySQL/Oracle have "solved" (!) this by not allowing updates to tables using transactional engines and non-transactional storage engines in same transaction.
I don't get this... if MyISAM is transaction-agnostic, why would you use it in a transaction to begin with and even combined with a transactional database?
it seems to me that if you rely on something like this, one shouldn't use MyISAM, or expect it to cope with this... (even though this example should be safe), (well, because it's transaction-agnostic, one can't asume it uses the same values when using transactional storage engines?
of course, i'm not an expert, so i probably get a wrong idea of the problem.
Is there some kind of example that _is_ unsafe somehow? wrt GTID being conflicting? since you speak of GTID, i assume you mean the replication safety? (ie: different results?)
However to me it is nonsense that transactions apply (have any effect at all) for non-transactional storage engines. As a consequence I consider this solution (what must have been enforced in the server layer and not the storeage engine layer) by Oracle 'a dirty hack' (unless somewone can explain me why it it necessary).
-- Peter
With autoclmmit=1 works?
Em domingo, 11 de maio de 2014, Peter Laursen <
escreveu:
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
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@google.com> 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
statements on slaves.
Pavel
On Sat, May 10, 2014 at 3:48 AM, Peter Laursen <peter_laursen@webyog.com>
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.h
tml
"nontransactional storage engines such as MyISAM cannot be made in
On Sun, May 11, 2014 at 4:42 PM, Roberto Spadim <roberto@spadim.com.br>wrote: peter_laursen@webyog.com> triggers the 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
Peter Laursen <peter_laursen@webyog.com> writes:
MyISAM is/used to be completely 'transaction-agnosstic'. But GTIDs seem to confict with non-transactional storage engines (as far as I can understand). MySQL/Oracle have "solved" (!) this by not allowing updates to tables using transactional engines and non-transactional storage engines in same transaction.
However to me it is nonsense that transactions apply (have any effect at all) for non-transactional storage engines. As a consequence I consider this solution (what must have been enforced in the server layer and not the storeage engine layer) by Oracle 'a dirty hack' (unless somewone can explain me why it it necessary).
Yes, MySQL/Oracle introduced a lot of limitations with their GTID implementation in 5.6. But these are specific to their choice of design of GTID. It is not intrinsic to the concept of global transaction ID. The whole motivation for doing GTID differently in MariaDB is exactly to avoid all these limitations and other problems that come with the MySQL 5.6 design. Your observation with limitations on non-transactional engines is one example. (IIRC, originally _any_ update to non-transactional tables was disallowed in MySSQL 5.6 GTID). In MariDB 10.0 GTID, I tried hard to make it work without any limitations compared to not using GTID. So in MariaDB there is no need to explicitly enable any gtid_mode=ON or enforce_gtid_consistency=ON options. GTID is available automatically after upgrade to 10.0, and can be used or not as and when desired. So yes, there are others that consider Oracle's design of GTID 'a dirty hack', but MariaDB 10.0 GTID is not affected by that design. Hope this helps, - Kristian.
participants (3)
-
AL13N -
Kristian Nielsen -
Peter Laursen