Bug #21004 Dead lock when output of several mysqldumps piped to mysql client
Submitted: 12 Jul 2006 15:42 Modified: 20 Aug 2006 20:08
Reporter: Alexey Lebedeff Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.22-Debian_3-log OS:Linux (Debian/unstable)
Assigned to: Assigned Account CPU Architecture:Any
Tags: deadlock, mysqldump

[12 Jul 2006 15:42] Alexey Lebedeff
In shell I do something like this:

(mysqldump -R -d --skip-triggers some_big_database;
 mysqldump -t --skip-triggers some_big_database some tables from it;
 mysqldump -t --skip-triggers --where="some query" some_big_db other tables) | mysql new_empty_database_on_same_sql_server

And it locks at first statements about routines recreation, which are issued by first mysqldump instance.

In show processlist can be seen 'DROP FUNCTION IF EXISTS' or 'CREATE FUNCTION' with a function name that comes first in mysqldump output. It is in locked state, and other queries is all in sleep state.

Looks like the second mysqldump causes something important to be locked in mysql server, and then locks up on write to stdout. And mysql client can not read what is in stdout, because it waits response from server for 'DROP FUNCTION' or 'CREATE FUNCTION' query.

How to repeat:
I can't reproduce it on smaller databases, problem shows up only on our full production database. 

Suggested fix:
If mysqldump is issued with --single-transaction option, all works ok. Also works when mysqldumps output is saved to temporary file, and then feed to 'mysql'.
[19 Jul 2006 8:43] Sveta Smirnova
Thank you for the report.

Just to be sure engine of mysql.proc table is not changed from default MyISAM, please, provide output of SHOW CREATE TABLE mysql.proc statement.
[19 Jul 2006 8:47] Alexey Lebedeff
  `db` char(64) character set utf8 collate utf8_bin NOT NULL default '',
  `name` char(64) NOT NULL default '',
  `specific_name` char(64) NOT NULL default '',
  `language` enum('SQL') NOT NULL default 'SQL',
  `is_deterministic` enum('YES','NO') NOT NULL default 'NO',
  `security_type` enum('INVOKER','DEFINER') NOT NULL default 'DEFINER',
  `param_list` blob NOT NULL,
  `returns` char(64) NOT NULL default '',
  `body` longblob NOT NULL,
  `definer` char(77) character set utf8 collate utf8_bin NOT NULL default '',
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL default '0000-00-00 00:00:00',
  `comment` char(64) character set utf8 collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`db`,`name`,`type`)
[20 Jul 2006 20:08] Sveta Smirnova
I afraid I'll could not catch the problem without your data.

If it is possible, please, provide output of "mysqldump -R -d --skip-triggers some_big_database;" and exact commands "mysqldump -t --skip-triggers some_big_database some tables from it; mysqldump -t --skip-triggers --where="some query" some_big_db other tables". Not any data for the time present.

Thank you.
[20 Aug 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".