Mike,
----- Original Message ----- From: "Mike Debnam" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Sunday, November 21, 2004 9:25 PM
Subject: temporary tables, replication, and innodb tables
I'm working on a new series of queries that share a temporary table. I've tried using both "create temporary table .... select" and "create temp table" then "insert into t ... select from y". Both seem to create a lock that (if the select portion runs long enough) causes a deadlock with the replication thread on that box (it's a slave).
do you have binlogging switched on in the slave? If yes, then CREATE ... SELECT ... will indeed take locks on the rows it scans in the SELECT. If not, then the SELECT is a consistent, non-locking read. Can you switch off binlogging?
Another solution is to use SELECT ... INTO OUTFILE. That is always processed as a consistent read.
When the select portion runs more than innodb_lock_wait_timeout seconds the slave replication thread dies with the errors:
041119 16:54:06 [ERROR] Slave: Error 'Lock wait timeout exceeded; try restarting transaction' on query. ........., Error_code: 1205 041119 16:54:06 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'db-bin.000081' position 65976472
Am I missing something here or is the only way to avoid potential problems with the slave replication thread is to increase innodb_lock_wait_timeout to a large enough value that it will be longer than any potential select for the temporary table?
That is a solution.
This locking problem is yet another case where the upcoming MySQL row-level binlogging will help. Then we do not need locks on the SELECT table ever, because execution does not need to be serializable.
All innodb tables, MySQL 4.1.7 for both master and slaves.
Thanks.
Mike
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php
Order MySQL technical support from https://order.mysql.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
