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.81' 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]
