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 helps 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 helps 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@stripped>
Visit my web laboratory at http://beta.unclassified.de