Web lists-archives.com

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.reindl@xxxxxxxxxxxxx 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