Hi!
>>>>> "Patrick" == Patrick Verdon <patrick.verdon@stripped> writes:
Patrick> Hi,
Patrick> I have a serious deadlock problem occurring where
Patrick> mysqld dumps core. I'm making use of LOCK TABLES
Patrick> and I can reproduce the problem every time by
Patrick> clicking the submit button simultaneously on two
Patrick> different browsers (it's a Web application).
Patrick> mysqld dumps core and I get these messages in
Patrick> Apache's error_log:
Patrick> DBD::mysql::db do failed: Lost connection to MySQL server during query at
> /usr/local/kan_lib/SWW/CallLogging.pm line 12624.
Patrick> DBD::mysql::db do failed: Lost connection to MySQL server during query at
> /usr/local/kan_lib/SWW/CallLogging.pm line 12669.
Patrick> [Wed Aug 4 23:45:08 1999] [error] Couldn't do: Lost connection to MySQL
> server during query at
Patrick> /kan/www-dev/sww-cla-dev/document_root/perl/call_logging/call_logging.pl line
> 238
Patrick> [Wed Aug 4 23:45:08 1999] [error] Couldn't lock tables: Lost connection to
> MySQL server during query at
Patrick> /kan/www-dev/sww-cla-dev/document_root/perl/call_logging/call_logging.pl line
> 238
Patrick> The set of queries that are trying to run are as
Patrick> follows:
Patrick> LOCK TABLES keyword_tmp_table_1 WRITE, keyword_tmp_table_2 WRITE, call_log
> READ, call_log_product READ, contact READ, keyword READ,
Patrick> keyword_call_log READ;
Patrick> INSERT INTO keyword_tmp_table_1 SELECT distinct(call_log.call_log_id) FROM
> call_log, call_log_product, contact, keyword,
Patrick> keyword_call_log WHERE call_log.call_log_product_id =
> call_log_product.call_log_product_id AND call_log.contact_id = contact.contact_id
Patrick> AND keyword.keyword_id =
Patrick> keyword_call_log.keyword_id AND call_log.call_log_id =
> keyword_call_log.call_log_id AND keyword.keyword like 'oracle%';
Patrick> INSERT INTO keyword_tmp_table_2 SELECT
> distinct(keyword_tmp_table_1.call_log_id) FROM keyword_tmp_table_1, keyword,
> keyword_call_log
Patrick> WHERE keyword.keyword_id = keyword_call_log.keyword_id AND
> keyword_tmp_table_1.call_log_id = keyword_call_log.call_log_id AND
Patrick> keyword.keyword like 'magik%';
Patrick> SELECT call_log.call_log_id, call_log.load_date, call_log.load_time,
> call_log.open_date, call_log.open_time, call_log_product.product,
Patrick> call_log.brief_description, contact.first_name, contact.last_name FROM
> keyword_tmp_table_2, call_log, call_log_product, contact WHERE
Patrick> keyword_tmp_table_2.call_log_id
Patrick> = call_log.call_log_id AND call_log.call_log_product_id =
> call_log_product.call_log_product_id AND call_log.contact_id =
Patrick> contact.contact_id ORDER BY load_date DESC, load_time DESC LIMIT 0, 10;
Patrick> DELETE FROM keyword_tmp_table_2;
Patrick> DELETE FROM keyword_tmp_table_1;
Patrick> UNLOCK TABLES;
Patrick> I'm running MySQL 3.22.25 on Solaris x86 2.6, compiled with
Patrick> egcs 1.1.1.
Hi!
I was just able to reproduce this in MySQL 3.22.25; It works however
in MySQL 3.23, so I am just now trying to check what I can move back
from 3.23.
In the mean time:
Change the code:
DELETE FROM keyword_tmp_table_2;
DELETE FROM keyword_tmp_table_1;
to
DELETE FROM keyword_tmp_table_2 WHERE 1=1;
DELETE FROM keyword_tmp_table_1 WHERE 1=1;
and it will work.
(This is a bug in the optimization of deleting all rows in a table)
Regards,
Monty
