Re: need help from the list admin
Sorry for pm !
----- Am 1. Apr 2016 um 17:45 schrieb shawn l.green shawn.l.green@xxxxxxxxxx:
>>> You would be better served by first converting your MyISAM tables to
>>> InnoDB to stop mixing storage engine behaviors (transactional and
>>> non-transactional) within the scope of a single transaction. But if you
>>> cannot convert them, using MIXED will be a good compromise.
>> Is the mix of MyISAM and InnoDB a problem with Row-Based-Logging or
>> with Statement-Based-Logging or with both ?
Aah ! In the beginning i thought it's just a problem for RBL.
>>> Look at this sequence and think what would happen without that "stronger
>>> locking" you mentioned earlier.
>>> (connection 1)
>>> begin transaction
>>> INSERT myisam_table SELECT ... FROM InnoDB_table WHERE ...
>>> (connection 2)
>>> DELETE myisam_table WHERE ... (this removes one of the rows that
>>> connection 1 just added)
>> (end of connection 2)
>>> (connection 1)
>> I don't understand the example:
>> Does "begin transaction" and "COMMIT" have any influence on the insert ?
>> From what i understand a myisam table does not support transactions,
>> so it should not care about "begin transaction" and "commit".
>> So the insert should be done immediately. The select on the InnoDB also
>> should not wait, because it's applied without "LOCK IN SHARE MODE".
>> So x lines are added immediately. This is done on the master, written in the log
>> and then replicated to the slave, which also adds x lines.
>> Then connection 2 deletes 8 rows, one is from the previous insert.
>> First on the master and then on the slave.
>> I assume that the connections are established in the order they appear here
>> (connection 2 is established after the insert in connection 1).
>> So on both 8 rows are deleted.
> You said, "This is done on the master, written in the log and then
> replicated to the slave, "
> The INSERT would not appear in the Binary log until after session 1
So the INSERT take care about the transaction (begin transaction ... COMMIT)
although it's a MyISAM table ?
Because i read MyISAM does not care about it:
>> Does commit means "write now to the binlog" ?
> Effectively, it does (for InnoDB-based transactions). InnoDB first
> writes the entire transaction to the Binary Log (it was sitting in the
> Binlog cache up until this point) then it pumps the necessary data into
> the REDO log (for disaster recovery).
And when in that temporal sequence is the data written to the tablespace ?
> At that point the transaction is
> considered "committed". In the case of a rollback, there is nothing to
> log in either location, no permanent changes were made to the data.
> However if the transaction that rolled back contained statements that
> changed MyISAM tables, then the entire transaction (all of the work it
> did) needs to be written into the Binary Log and REDO log just to have
> the very last command be "ROLLBACK". What that will do is create the
> same sequence of data changes on the slave that happened on the master.
So when transaction is rollbacked, the inserted data in the MyISAM table remains ?
P.S. i tried several times to rename the subject into something like
"Replication - was "need help from the list admin"", but this mail
is always bounced back because it is recognized as spam !?!
I just renamed the subject !
Helmholtz Zentrum Muenchen
Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH)
Ingolstaedter Landstr. 1
Aufsichtsratsvorsitzende: MinDir'in Baerbel Brumme-Bothe
Geschaeftsfuehrer: Prof. Dr. Guenther Wess, Dr. Alfons Enhsen, Renate Schlusen (komm.)
Registergericht: Amtsgericht Muenchen HRB 6466
USt-IdNr: DE 129521671
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql