Re: need help from the list admin
Am 28.03.2016 um 21:36 schrieb Lentes, Bernd: - On Mar 27, 2016, at 2:49 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 27.03.2016 um 14:34 schrieb Lentes, Bernd: 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 this a big problem ? Something to take care of ? Currently we have a mix. I will ask the girl who developed it why we have both kinds. I hope i can convert surely - when you have non-transactional tables involved in updates/inserts you can go and forget using transactions at all since interruption or rollback would not rollback already written changes in MyISAM tables transactions are all about consistency - impossible with a mix of InnoDB and MyISAM tables I read that the converting is not difficult. But has the code of our webapp to be changed ? It's written in php and perl. What i understand is that inserts/updates/deletions in InnoDB tables have to be commited. Yes ? first please stop place a space before "?" - it hurts :-) NO - it only has to be commited if you START A TRANSACTION at the begin This has to be done in the code ? Or can we use the system variable autocommit ? if you are using automcommit you lose any purpose of having transactions That means that everything is commited immediately ? Is this a good solution ? if you don't care about consistency yes What means "By default, client connections begin with autocommit set to 1" in the doc ? that nobody is starting a transaction automatically because nobody can tell when it is finished automatically end hence you need to tell it the db server That every client connection established via perl/php is started with autocommit=1 ? surely And when does the commit happen ? When the connection is closed ? Is that helpful ? depends - maybe you should start to read what transactions in the database world are because than you can answer all of your questions above at your own signature.asc Description: OpenPGP digital signature
Re: need help from the list admin
Hello Bernd, On 3/28/2016 3:36 PM, Lentes, Bernd wrote: - On Mar 27, 2016, at 2:49 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 27.03.2016 um 14:34 schrieb Lentes, Bernd: 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 this a big problem ? Something to take care of ? Currently we have a mix. I will ask the girl who developed it why we have both kinds. I hope i can convert surely - when you have non-transactional tables involved in updates/inserts you can go and forget using transactions at all since interruption or rollback would not rollback already written changes in MyISAM tables transactions are all about consistency - impossible with a mix of InnoDB and MyISAM tables I read that the converting is not difficult. But has the code of our webapp to be changed ? It's written in php and perl. What i understand is that inserts/updates/deletions in InnoDB tables have to be commited. Yes ? No. The server's default is to have --autocommit=1, which means that there is an implicit commit at the end of every command. You do not need to state explicitly "COMMIT" every time you want this to happen. In fact, disabling autocommit has gotten many new users into trouble because they did not understand the behavior they changed. This has to be done in the code ? Or can we use the system variable autocommit ? You should need to change nothing. That means that everything is commited immediately ? Is this a good solution ? It is going to behave better than the data you have now. The changes to the tables you will convert from MyISAM to InnoDB will not become visible to other sessions until after the COMMIT (implicit or explicit) completes. For finer-grained control over data visibility, you need to understand the broader topic of transaction isolation. What means "By default, client connections begin with autocommit set to 1" in the doc ? It means that every command is already running in its own private mini-transaction. To start a multi-statement transaction you do not need to disable autocommit, you simply need to use the START TRANSACTION command. Here is a reference from the 5.0 manual to illustrate that this behavior has been around for a long time: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-transactions.html That every client connection established via perl/php is started with autocommit=1 ? It is as long as: 1) the global variable autocommit=1 2) the client does nothing to change its own session variable to autocommit=0 And when does the commit happen ? When the connection is closed ? Is that helpful ? The commit happens at the end of each command. If you need to contain multiple commands within a single transaction, use START TRANSACTION and COMMIT. Bernd -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: need help from the list admin
- On Mar 27, 2016, at 2:49 PM, Reindl Harald h.rei...@thelounge.net wrote: > Am 27.03.2016 um 14:34 schrieb Lentes, Bernd: >>> 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 this a big problem ? Something to take care of ? Currently we have a mix. >> I will ask the girl who developed it why we have both kinds. I hope i can >> convert > > surely - when you have non-transactional tables involved in > updates/inserts you can go and forget using transactions at all since > interruption or rollback would not rollback already written changes in > MyISAM tables > > transactions are all about consistency - impossible with a mix of InnoDB > and MyISAM tables I read that the converting is not difficult. But has the code of our webapp to be changed ? It's written in php and perl. What i understand is that inserts/updates/deletions in InnoDB tables have to be commited. Yes ? This has to be done in the code ? Or can we use the system variable autocommit ? That means that everything is commited immediately ? Is this a good solution ? What means "By default, client connections begin with autocommit set to 1" in the doc ? That every client connection established via perl/php is started with autocommit=1 ? And when does the commit happen ? When the connection is closed ? Is that helpful ? Bernd Helmholtz Zentrum Muenchen Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH) Ingolstaedter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de 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
