Re: Query log/binlog inconsistency

[Chris Tucker]

Thanks for your response (and yours also, Jeremy).  Both are spot on in terms of 
recognising it as a transaction commit issue.  It turns out a developer was 
opening a transaction long before the query in question executed and had 
neglected to either commit it or roll it back: subsequently, the PHP page 
completed execution, the connection was dropped, and the transaction was 
implicitly rolled back.  It's amazing how long it can take to find such a simple 
result: I only wish I had thought a little more a little earlier about what the 
binlog actually stores (namely records of transactions that have been committed!).

Thanks again,
Chris
Heikki Tuuri wrote:
Chris,

----- Original Message ----- 
From: "Jeremy Zawodny" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, May 29, 2003 10:17 AM
Subject: Re: Query log/binlog inconsistency



On Wed, May 28, 2003 at 05:05:38PM -0700, Chris Tucker wrote:

Hi,

I'm running into an issue on MySQL 4.0.12 (not tested on other
releases) using an InnoDB table type, where an update query is
getting written to the query log but never being propogated as far
as the binlog.  The query is also not updating the DB, though
according to the connection layer (PEAR DB) it is affecting rows as
one would expect.  Running the query through a command line (logged
in as the same user, from the same box, etc.) works as expected,
writing to the query log, updating the DB, and then writing to the
binlog.
Hmm.

The fact that the it doesn't show up in the binlog *and* it never
affects you data is good.  That means the binlog is working
properly. :-)

At present it seems the failure to write to the binlog is almost
certainly because something is failing between the arrival of the
query at the DB server (as signified by the entry in the query log)
and the committing of the data (as would be signified by the data
being appropriately modified and the binlog being written to).
Agreed.


My question is essentially: what could fail between these steps that
would: 1) not be reported back to the calling agent 2) not be logged
to the db error log 3) not happen when running directly through the
MySQL command-line client but happen when running through an
(admittedly rather questionable) PHP library when the queries
received by the DB are verifiably the same in every apparent aspect
(through inspection of the query log).
The first thing that comes to mind is that the abstraction layer
you're using "forgets" to COMMIT the data, so InnoDB rolls it back and
never write the query to the binlog.


Jeremy's explanation is plausible. If the PHP library runs in the
AUTOCOMMIT=0 mode, then the query is executed and reports modified rows, but
when the connection ends mysqld rolls back the transaction because it was
not explicitly committed.
Also note that a deadlock or a lock wait timeout error rolls back the WHOLE
current transaction. But I assume you did not get any of these errors or
other errors?
It would help if you could post the relevant query log excerpt.


Jeremy


Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/

--
Jeremy D. Zawodny     |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
MySQL 4.0.8: up 114 days, processed 3,574,615,610 queries (360/sec. avg)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]