MySQL Bugs: #21004: Dead lock when output of several mysqldumps piped to mysql client

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:	None
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

Description:
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'.

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.

CREATE TABLE `proc` (
  `db` char(64) character set utf8 collate utf8_bin NOT NULL default '',
  `name` char(64) NOT NULL default '',
  `type` enum('FUNCTION','PROCEDURE') NOT NULL,
  `specific_name` char(64) NOT NULL default '',
  `language` enum('SQL') NOT NULL default 'SQL',
  `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL default 'CONTAINS_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',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE') NOT NULL default '',
  `comment` char(64) character set utf8 collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'

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.

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".