@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 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
> >
> > On Sun, May 11, 2014 at 4:42 PM, Roberto Spadim
> wrote:
> > > With autoclmmit=1 works?
> > >
> > > Em domingo, 11 de maio de 2014, Peter Laursen <
> peter_laur...@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
> 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
> > >>>
> > >>>
> > >>> 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
