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 [email protected] 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