Categorized | MySQL, News

Debugging MySQL

Posted on 27 June 2007 by Demian Turner

Had an interesting problem this morning, a chain of SQL tasks where the last one was mysteriously failing, ie, the result returned from $dbh->query($sql) was the expected integer 1, however the UPDATE was not reflecting in the data.

I suspected there was a transaction somewhere that was incomplete and that the query was valid, just not being committed, and this turned out to be correct. Best way to demonstrate this is to run MySQL with logging enabled. Following details are for OS X where MySQL was installed as a port:

mysqladmin shutdown

mysqld_safe5 –log &

tail -f /opt/local/var/db/mysql5/demian-turners-computer.log

and output demonstrated the problem, first of all a correct sequence:

111 Init DB foo
111 Query SET AUTOCOMMIT=0
111 Query BEGIN
111 Query UPDATE user_preference
SET value = ‘UTC’
WHERE preference_id = ‘2’
AND usr_id = 1
111 Init DB foo
111 Query COMMIT
111 Query SET AUTOCOMMIT=1

and then the incorrect one:

070627 12:43:23 111 Init DB foo
111 Query SET AUTOCOMMIT=0
111 Query BEGIN
111 Query UPDATE usr set person_id = 1 WHERE usr_id = 1

ie, no commit.

Bookmark and Share

Leave a Reply

Categories

Books

Demian Turner's currently-reading book recommendations, reviews, favorite quotes, book clubs, book trivia, book lists

Facebook