Re: need help from the list admin
On 3/30/2016 1:26 PM, Lentes, Bernd wrote: - On Mar 30, 2016, at 7:04 PM, Reindl Harald h.rei...@thelounge.net wrote: So i should use the default (autocommit=1)? no, you should what is appropriate for your application if you don't care about inserts/updates triggered by let say a webrequest are half written due a crash or restart use autocommit Autocommit means that every statement is committed implicitly. Right ? Commit works only in conjunction with InnoDB tables and transaction. That's what i understand. I thought when i make e.g. an insert into a InnoDB table, and that insert is not done completely (due to a crash, restart, what ever) it is rolled back automatically after the restart. Is that wrong ? it depends: If the transaction made it into the Binary Log (if it is enabled) and the REDO log as "committed", then InnoDB will finish the commit (put the actual data in its proper place in the data files) after recovery. If not, it will rollback and your data remains as it was. http://dev.mysql.com/doc/refman/5.6/en/innodb-recovery.html if you care that all or nothing is written use transactions if you care that way don't mix non-transactional tables with innodb I'm planning to convert the MyISAM tables to InnoDB. That will solve many of your data consistency problems (particularly those related to how things are recorded in the Binary Log), presuming you surround changes that involve multiple commands with transaction control commands. If your sets of data changes only need one command to complete, then the overhead of issuing explicit START TRANSACTION and COMMIT commands is just going to create work you don't need for your workflow. If you need more than one command to make a complete and consistent update to your data, then use a transaction. If not, operating in autocommit mode is ideal. 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
Am 30.03.2016 um 19:26 schrieb Lentes, Bernd: - On Mar 30, 2016, at 7:04 PM, Reindl Harald h.rei...@thelounge.net wrote: So i should use the default (autocommit=1)? no, you should what is appropriate for your application if you don't care about inserts/updates triggered by let say a webrequest are half written due a crash or restart use autocommit Autocommit means that every statement is committed implicitly. Right ? Commit works only in conjunction with InnoDB tables and transaction. That's what i understand. I thought when i make e.g. an insert into a InnoDB table, and that insert is not done completely (due to a crash, restart, what ever) it is rolled back automatically after the restart. Is that wrong ? transactions are not about single queries, transactions are all about multiple queries when you want them all or nothing written please do some homework and read https://en.wikipedia.org/wiki/ACID which is basic knowledge about databases the crash safety of innodb has nothing to do with commits signature.asc Description: OpenPGP digital signature
RE: need help from the list admin
- On Mar 30, 2016, at 7:04 PM, Reindl Harald h.rei...@thelounge.net wrote: >> So i should use the default (autocommit=1)? > > no, you should what is appropriate for your application > > if you don't care about inserts/updates triggered by let say a > webrequest are half written due a crash or restart use autocommit Autocommit means that every statement is committed implicitly. Right ? Commit works only in conjunction with InnoDB tables and transaction. That's what i understand. I thought when i make e.g. an insert into a InnoDB table, and that insert is not done completely (due to a crash, restart, what ever) it is rolled back automatically after the restart. Is that wrong ? > > if you care that all or nothing is written use transactions > if you care that way don't mix non-transactional tables with innodb I'm planning to convert the MyISAM tables to InnoDB. 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
Re: need help from the list admin
Am 30.03.2016 um 18:56 schrieb Lentes, Bernd: - On Mar 28, 2016, at 9:53 PM, shawn l.green shawn.l.gr...@oracle.com wrote: 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. So i should use the default (autocommit=1)? no, you should what is appropriate for your application if you don't care about inserts/updates triggered by let say a webrequest are half written due a crash or restart use autocommit if you care that all or nothing is written use transactions if you care that way don't mix non-transactional tables with innodb signature.asc Description: OpenPGP digital signature
Re: need help from the list admin
- On Mar 28, 2016, at 9:53 PM, shawn l.green shawn.l.gr...@oracle.com wrote: >> >> 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. So i should use the default (autocommit=1) ? > 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 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
