Description:
mysqldump issues a FLUSH TABLES WITH READ LOCK (global locking) SQL command although --lock-all-tables has not been specified. This makes other sessions "hang" during the dump.
We're using the 5.0.90 32-bit binary distribution from the MySQL website.
We're using a mix of MyISAM and InnoDB tables on our system so unfortunately we cannot use --single-transaction.
Specifying --skip-lock-all-tables or --databases db1 (insted of just specifying db1 as the database name) on the command line does not seem to help.
How to repeat:
/etc/my.cnf:
# MySQL client options
[mysql]
pager=/usr/bin/less -S
prompt=\\u@\\h\\_\\d>\\_
show-warnings
~root/.my.cnf:
[mysqldump]
add-drop-database
add-drop-table
add-locks
allow-keywords
comments
complete-insert
compress
create-options
flush-logs
flush-privileges
force
master-data=2
quick
quote-names
routines
triggers
# For MyISAM or MyISAM + InnoDB
lock-tables
Command line:
mysqldump largedb > out.sql
From a mysql client session, issue:
SHOW PROCESSLIST
...which will show:
| 978412 | root | localhost | NULL | Query | 29 | Flushing tables | FLUSH TABLES WITH READ LOCK
| 978418 | uucms | pica.its.uu.se:58315 | uucmsdb | Query | 28 | Waiting for release of readlock | REPLACE INTO pcms_pubsession (sid, data) VALUE
| 978419 | uucms | pica.its.uu.se:58316 | uucmsdb | Query | 28 | Waiting for release of readlock | REPLACE INTO pcms_pubsession (sid, data) VALUE
| 978420 | uucms | pica.its.uu.se:58317 | uucmsdb | Query | 28 | Waiting for release of readlock | REPLACE INTO pcms_pubsession (sid, data) VALUE
| 978421 | uucms | pica.its.uu.se:58318 | uucmsdb | Query | 27 | Waiting for release of readlock | REPLACE INTO pcms_pubsession (sid, data) VALUE
...i.e. the global flushing with read lock and more and more sessions waiting for it (the dump) to complete.
The general query log shows:
965266 Connect root@localhost on
965266 Query /*!40100 SET @@SQL_MODE='' */
965266 Query /*!40103 SET TIME_ZONE='+00:00' */
965266 Query FLUSH TABLES
965266 Query FLUSH TABLES WITH READ LOCK
965266 Refresh
965266 Query SHOW MASTER STATUS
965266 Init DB abmdb
965266 Query SHOW CREATE DATABASE IF NOT EXISTS `abmdb`
965266 Query show tables
965266 Query show table status like 'PublikationerTypDef'
/../
Running mysqldump --help shows:
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
all TRUE
all-databases FALSE
add-drop-database TRUE
add-drop-table TRUE
add-locks TRUE
allow-keywords TRUE
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert TRUE
compress TRUE
create-options TRUE
databases FALSE
debug-info FALSE
default-character-set utf8
delayed-insert FALSE
delete-master-logs FALSE
disable-keys TRUE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
first-slave FALSE
flush-logs TRUE
flush-privileges TRUE
force TRUE
hex-blob FALSE
host (No default value)
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
log-error (No default value)
master-data 2
max_allowed_packet 25165824
net_buffer_length 1046528
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 0
quick TRUE
quote-names TRUE
routines TRUE
set-charset TRUE
single-transaction FALSE
dump-date TRUE
socket (No default value)
tab (No default value)
triggers TRUE
tz-utc TRUE
user root
verbose FALSE
where (No default value)
Suggested fix:
If --lock-tables is specified, but not --lock-all-tables (-x), mysqldump should not lock all tables across all databases but instead only all tables in the selected or affected database(s), as specified by the database name or database name(s) following --databases on the command line.
(I leave the decision whether or not --databases db1 db2 should lock all tables in both db1 and db2 during the full duration of the dump, or first all tables in db1 and then all tables in db2, to you.)
Description: mysqldump issues a FLUSH TABLES WITH READ LOCK (global locking) SQL command although --lock-all-tables has not been specified. This makes other sessions "hang" during the dump. We're using the 5.0.90 32-bit binary distribution from the MySQL website. We're using a mix of MyISAM and InnoDB tables on our system so unfortunately we cannot use --single-transaction. Specifying --skip-lock-all-tables or --databases db1 (insted of just specifying db1 as the database name) on the command line does not seem to help. How to repeat: /etc/my.cnf: # MySQL client options [mysql] pager=/usr/bin/less -S prompt=\\u@\\h\\_\\d>\\_ show-warnings ~root/.my.cnf: [mysqldump] add-drop-database add-drop-table add-locks allow-keywords comments complete-insert compress create-options flush-logs flush-privileges force master-data=2 quick quote-names routines triggers # For MyISAM or MyISAM + InnoDB lock-tables Command line: mysqldump largedb > out.sql From a mysql client session, issue: SHOW PROCESSLIST ...which will show: | 978412 | root | localhost | NULL | Query | 29 | Flushing tables | FLUSH TABLES WITH READ LOCK | 978418 | uucms | pica.its.uu.se:58315 | uucmsdb | Query | 28 | Waiting for release of readlock | REPLACE INTO pcms_pubsession (sid, data) VALUE | 978419 | uucms | pica.its.uu.se:58316 | uucmsdb | Query | 28 | Waiting for release of readlock | REPLACE INTO pcms_pubsession (sid, data) VALUE | 978420 | uucms | pica.its.uu.se:58317 | uucmsdb | Query | 28 | Waiting for release of readlock | REPLACE INTO pcms_pubsession (sid, data) VALUE | 978421 | uucms | pica.its.uu.se:58318 | uucmsdb | Query | 27 | Waiting for release of readlock | REPLACE INTO pcms_pubsession (sid, data) VALUE ...i.e. the global flushing with read lock and more and more sessions waiting for it (the dump) to complete. The general query log shows: 965266 Connect root@localhost on 965266 Query /*!40100 SET @@SQL_MODE='' */ 965266 Query /*!40103 SET TIME_ZONE='+00:00' */ 965266 Query FLUSH TABLES 965266 Query FLUSH TABLES WITH READ LOCK 965266 Refresh 965266 Query SHOW MASTER STATUS 965266 Init DB abmdb 965266 Query SHOW CREATE DATABASE IF NOT EXISTS `abmdb` 965266 Query show tables 965266 Query show table status like 'PublikationerTypDef' /../ Running mysqldump --help shows: Variables (--variable-name=value) and boolean options {FALSE|TRUE} Value (after reading options) --------------------------------- ----------------------------- all TRUE all-databases FALSE add-drop-database TRUE add-drop-table TRUE add-locks TRUE allow-keywords TRUE character-sets-dir (No default value) comments TRUE compatible (No default value) compact FALSE complete-insert TRUE compress TRUE create-options TRUE databases FALSE debug-info FALSE default-character-set utf8 delayed-insert FALSE delete-master-logs FALSE disable-keys TRUE extended-insert TRUE fields-terminated-by (No default value) fields-enclosed-by (No default value) fields-optionally-enclosed-by (No default value) fields-escaped-by (No default value) first-slave FALSE flush-logs TRUE flush-privileges TRUE force TRUE hex-blob FALSE host (No default value) insert-ignore FALSE lines-terminated-by (No default value) lock-all-tables FALSE lock-tables TRUE log-error (No default value) master-data 2 max_allowed_packet 25165824 net_buffer_length 1046528 no-autocommit FALSE no-create-db FALSE no-create-info FALSE no-data FALSE order-by-primary FALSE port 0 quick TRUE quote-names TRUE routines TRUE set-charset TRUE single-transaction FALSE dump-date TRUE socket (No default value) tab (No default value) triggers TRUE tz-utc TRUE user root verbose FALSE where (No default value) Suggested fix: If --lock-tables is specified, but not --lock-all-tables (-x), mysqldump should not lock all tables across all databases but instead only all tables in the selected or affected database(s), as specified by the database name or database name(s) following --databases on the command line. (I leave the decision whether or not --databases db1 db2 should lock all tables in both db1 and db2 during the full duration of the dump, or first all tables in db1 and then all tables in db2, to you.)