Hi,

today I have noticed a strange bug with MySQL and PHP. I'm developing a
PHP application, using the MySQL database server 5.0.45 on Windows XP
and the PDO connection objects (PHP Data Objects). The PHP application
works on InnoDB tables and uses transactions and persistent connections.
One of the tasks is to check whether a cached field is set and if not,
the value is generated from another field and then stored in the
database for later use.

To validate my application's work, I've watched the database with
phpMyAdmin in the second browser tab. PMA doesn't use persistent
connections, I assume.

Now when that field, "HtmlContent" is NULL, my application reads the
value from the field "Content", converts it to HTML and writes it back
to "HtmlContent". The next time this page is requested, the data is
already there and doesn't need to be converted again. I have enough
debug output in my PHP application to see what it's doing and what
queries it's running.

The strange behaviour is the following: Initially the field
"HtmlContent" is NULL and I have restarted both MySQL and Apache
services. My application now converts the data and writes it to the
database in the first request. The next time(s), it won't do that again
because the data is already there. But when I set that column to NULL
with phpMyAdmin, my application still reads the old data from the
database. phpMyAdmin keeps telling me that the value is actually NULL,
which I just entered. Whereas the persistent PHP connection doesn't see
the new data and keeps reading the previous one. My application will
only get back to the truth when I restart the Apache or MySQL services
which effectively closes the connection. Also, not using persistent
database connections in my application s to always read current data.

I could not find any transaction that was left open. But executing a
ROLLBACK query at the very beginning of my application also s to
read current data. Now what can be the reason for that inconsistency?
How can I find the problem that is causing this bug?

--
Yves Goergen "LonelyPixel" <nospam.list@unclassified.de>
Visit my web laboratory at http://beta.unclassified.de

On 20.12.2007 19:42 CE(S)T, Yves Goergen wrote:
> But when I set that column to NULL
> with phpMyAdmin, my application still reads the old data from the
> database. phpMyAdmin keeps telling me that the value is actually NULL,
> which I just entered. Whereas the persistent PHP connection doesn't see
> the new data and keeps reading the previous one.

Here's more facts: My application disables autocommit mode right at the
beginning. I thought this would be a good compatibility measure to make
MySQL more similar to the "big" DBMS where my app should also run later.
And each of my writing operations is done in a separate transaction that
is started and commited (or rolled back) with PDO's methods which should
not be too much different from the corresponding SQL statements.

I thought that disabling autocommit mode makes no difference at all,
when I only write to the DB inside of transactions, but when I remove
that line from my code, the bug seems to go away. Here's my theory:

* Disabling autocommit starts a new transaction, according to the MySQL
manual. When I then start my own transaction, I'm at level 2. MySQL
needs to support nested transactions for this to work out. A COMMIT
statement will only commit the innermost transaction.

* When I start a transaction and then write something to a table which
is later overwritten from another thread, I still see my own data as
long as the outermost transaction is not finished. Also, data that I
write in a transaction must not be locked and can be overwritten from
another thread.

If this is both true, I see that this is my fault. I write the new
HtmlContent, then phpMyAdmin sets it NULL again but my app still sees
what it has just written (because of the persistent transaction over
multiple requests).

However, if MySQL doesn't support nested transactions or data written in
a transaction will be locked, this is not an explanation for what I
experience.

--
Yves Goergen "LonelyPixel" <nospam.list@unclassified.de>
Visit my web laboratory at http://beta.unclassified.de

Yves,


> On 20.12.2007 19:42 CE(S)T, Yves Goergen wrote:
> > But when I set that column to NULL
> > with phpMyAdmin, my application still reads the old data from the
> > database. phpMyAdmin keeps telling me that the value is actually NULL,
> > which I just entered. Whereas the persistent PHP connection doesn't see
> > the new data and keeps reading the previous one.
>
> Here's more facts: My application disables autocommit mode right at the
> beginning. I thought this would be a good compatibility measure to make
> MySQL more similar to the "big" DBMS where my app should also run later.
> And each of my writing operations is done in a separate transaction that
> is started and commited (or rolled back) with PDO's methods which should
> not be too much different from the corresponding SQL statements.
>
> I thought that disabling autocommit mode makes no difference at all,
> when I only write to the DB inside of transactions, but when I remove
> that line from my code, the bug seems to go away. Here's my theory:
>
> * Disabling autocommit starts a new transaction, according to the MySQL
> manual. When I then start my own transaction, I'm at level 2. MySQL
> needs to support nested transactions for this to work out. A COMMIT
> statement will only commit the innermost transaction.
>
> * When I start a transaction and then write something to a table which
> is later overwritten from another thread, I still see my own data as
> long as the outermost transaction is not finished. Also, data that I
> write in a transaction must not be locked and can be overwritten from
> another thread.
>
> If this is both true, I see that this is my fault. I write the new
> HtmlContent, then phpMyAdmin sets it NULL again but my app still sees
> what it has just written (because of the persistent transaction over
> multiple requests).
>
> However, if MySQL doesn't support nested transactions or data written in
> a transaction will be locked, this is not an explanation for what I
> experience.

The transaction could very well be committed for writing the data,
but you could be reading the data in a "snapshot" transaction, which
means you get the same data despite other transactions writing the
data.

Are your tables InnoDB? If so, the snapshot transaction is giving you
a static view on the data and your own changes, while your PHPMyAdmin
commits the NULL write. Your application keeps on seeing your own
changes, cause it did not end the snapshot transaction.

Hope this s.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Hi,

On Dec 20, 2007 2:26 PM, Yves Goergen <nospam.list@unclassified.de> wrote:
> On 20.12.2007 19:42 CE(S)T, Yves Goergen wrote:
> > But when I set that column to NULL
> > with phpMyAdmin, my application still reads the old data from the
> > database. phpMyAdmin keeps telling me that the value is actually NULL,
> > which I just entered. Whereas the persistent PHP connection doesn't see
> > the new data and keeps reading the previous one.
>
> Here's more facts: My application disables autocommit mode right at the
> beginning. I thought this would be a good compatibility measure to make
> MySQL more similar to the "big" DBMS where my app should also run later.
> And each of my writing operations is done in a separate transaction that
> is started and commited (or rolled back) with PDO's methods which should
> not be too much different from the corresponding SQL statements.
>
> I thought that disabling autocommit mode makes no difference at all,
> when I only write to the DB inside of transactions, but when I remove
> that line from my code, the bug seems to go away. Here's my theory:
>
> * Disabling autocommit starts a new transaction, according to the MySQL
> manual. When I then start my own transaction, I'm at level 2. MySQL
> needs to support nested transactions for this to work out. A COMMIT
> statement will only commit the innermost transaction.
>
> * When I start a transaction and then write something to a table which
> is later overwritten from another thread, I still see my own data as
> long as the outermost transaction is not finished. Also, data that I
> write in a transaction must not be locked and can be overwritten from
> another thread.
>
> If this is both true, I see that this is my fault. I write the new
> HtmlContent, then phpMyAdmin sets it NULL again but my app still sees
> what it has just written (because of the persistent transaction over
> multiple requests).
>
> However, if MySQL doesn't support nested transactions or data written in
> a transaction will be locked, this is not an explanation for what I
> experience.

It doesn't support nested transactions. What you're seeing is the
effects of MVCC. The InnoDB section of the MySQL manual explains it.

On 20.12.2007 20:34 CE(S)T, Martijn Tonies wrote:
> Are your tables InnoDB? If so, the snapshot transaction is giving you
> a static view on the data and your own changes, while your PHPMyAdmin
> commits the NULL write. Your application keeps on seeing your own
> changes, cause it did not end the snapshot transaction.

Yes, all tables are InnoDB.

So MySQL does support nested transaction and both "SET AUTOCOMMIT = 0"
and "START TRANSACTION" start a new transaction level, is that true?

--
Yves Goergen "LonelyPixel" <nospam.list@unclassified.de>
Visit my web laboratory at http://beta.unclassified.de

On 20.12.2007 21:14 CE(S)T, Baron Schwartz wrote:
> It doesn't support nested transactions. What you're seeing is the
> effects of MVCC. The InnoDB section of the MySQL manual explains it.

I wasn't able to find MVCC-related information (I assume it means Multi
Version Concurrency Control, not sure whether that's correct) in the
MySQL manual. But Martijn's explanation gives me an idea what it could
be about. But then again, if MySQL doesn't support nested transactions,
I don't see how any information can be frozen until a point when the
transaction has already been commited.

--
Yves Goergen "LonelyPixel" <nospam.list@unclassified.de>
Visit my web laboratory at http://beta.unclassified.de

> On 20.12.2007 20:34 CE(S)T, Martijn Tonies wrote:
> > Are your tables InnoDB? If so, the snapshot transaction is giving you
> > a static view on the data and your own changes, while your PHPMyAdmin
> > commits the NULL write. Your application keeps on seeing your own
> > changes, cause it did not end the snapshot transaction.
>
> Yes, all tables are InnoDB.
>
> So MySQL does support nested transaction and both "SET AUTOCOMMIT = 0"
> and "START TRANSACTION" start a new transaction level, is that true?

I didn't say it supports nested transactions, I said that if your
application
starts a single transaction and does not finish it, it will continue seeing
the same data despite other transactions (PHPMyAdmin) changing your
data.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Hi,

On Dec 20, 2007 3:33 PM, Yves Goergen <nospam.list@unclassified.de> wrote:
> On 20.12.2007 21:14 CE(S)T, Baron Schwartz wrote:
> > It doesn't support nested transactions. What you're seeing is the
> > effects of MVCC. The InnoDB section of the MySQL manual explains it.
>
> I wasn't able to find MVCC-related information (I assume it means Multi
> Version Concurrency Control, not sure whether that's correct) in the
> MySQL manual. But Martijn's explanation gives me an idea what it could
> be about. But then again, if MySQL doesn't support nested transactions,
> I don't see how any information can be frozen until a point when the
> transaction has already been commited.

http://dev.mysql.com/doc/refman/5.0/...ion-model.html

On 20.12.2007 22:18 CE(S)T, Baron Schwartz wrote:
> On Dec 20, 2007 3:33 PM, Yves Goergen <nospam.list@unclassified.de> wrote:
>> I wasn't able to find MVCC-related information (I assume it means Multi
>> Version Concurrency Control, not sure whether that's correct) in the
>> MySQL manual. But Martijn's explanation gives me an idea what it could
>> be about. But then again, if MySQL doesn't support nested transactions,
>> I don't see how any information can be frozen until a point when the
>> transaction has already been commited.
>
> http://dev.mysql.com/doc/refman/5.0/...ion-model.html

Yes, this is where I was looking.

--
Yves Goergen "LonelyPixel" <nospam.list@unclassified.de>
Visit my web laboratory at http://beta.unclassified.de

On 20.12.2007 21:34 CE(S)T, Martijn Tonies wrote:
>> So MySQL does support nested transaction and both "SET AUTOCOMMIT = 0"
>> and "START TRANSACTION" start a new transaction level, is that true?
>
> I didn't say it supports nested transactions, I said that if your
> application
> starts a single transaction and does not finish it, it will continue seeing
> the same data despite other transactions (PHPMyAdmin) changing your
> data.

Okay, I got that. So a COMMIT statement after disabling autocommit mode
and another START TRANSACTION does not finish my transaction. (But then,
what does?) Interesting view, I didn't know that. But now all's clear: I
won't touch autocommit mode anymore and everything works as expected.
Thanks for your .

--
Yves Goergen "LonelyPixel" <nospam.list@unclassified.de>
Visit my web laboratory at http://beta.unclassified.de

> >> So MySQL does support nested transaction and both "SET AUTOCOMMIT = 0"
> >> and "START TRANSACTION" start a new transaction level, is that true?
> >
> > I didn't say it supports nested transactions, I said that if your
> > application
> > starts a single transaction and does not finish it, it will continue
seeing
> > the same data despite other transactions (PHPMyAdmin) changing your
> > data.
>
> Okay, I got that. So a COMMIT statement after disabling autocommit mode
> and another START TRANSACTION does not finish my transaction. (But then,
> what does?) Interesting view, I didn't know that. But now all's clear: I
> won't touch autocommit mode anymore and everything works as expected.
> Thanks for your .

A COMMIT _should_ finish your transaction yes.

Are you sure you're executing the COMMIT on the same connection?

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On 20.12.2007 22:46 CE(S)T, Martijn Tonies wrote:
>> Okay, I got that. So a COMMIT statement after disabling autocommit mode
>> and another START TRANSACTION does not finish my transaction. (But then,
>> what does?) Interesting view, I didn't know that. But now all's clear: I
>> won't touch autocommit mode anymore and everything works as expected.
>> Thanks for your .
>
> A COMMIT _should_ finish your transaction yes.
>
> Are you sure you're executing the COMMIT on the same connection?

Yes, I am. I open up only a single connection in my application, so it
must be that one. Most of the time, further HTTP requests even get back
the same connection again. I can see the sleeping connection on my
application database from phpMyAdmin between the requests, with
reasonable sleep time values.

--
Yves Goergen "LonelyPixel" <nospam.list@unclassified.de>
Visit my web laboratory at http://beta.unclassified.de