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]
