When using mysqldump with --single-transaction, the START TRANSACTION WITH CONSISTENT SNAPHOST is executed, thus server crash:
root@daniel-vbox:/etc/mysql# mysqldump --version
mysqldump Ver 10.13 Distrib 5.6.19-67.0, for debian-linux-gnu (x86_64)
root@daniel-vbox:/etc/mysql# mysqldump --single-transaction dani -uroot -ptoor
Warning: Using a password on the command line interface can be insecure.
-- MySQL dump 10.13 Distrib 5.6.19-67.0, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: dani
-- ------------------------------------------------------
-- Server version 5.6.19-67.0-56
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Couldn't execute 'START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */': Lost connection to MySQL server during query (2013)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0,00 sec)
Stack:
Thread pointer: 0x7f218681c2a0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f215c89ee00 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x7f2183d7c04c]
/usr/sbin/mysqld(handle_fatal_signal+0x3cb)[0x7f2183ac2a2b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7f2181f6d340]
/usr/sbin/mysqld(+0x6e4cd7)[0x7f2183d26cd7]
/usr/sbin/mysqld(+0x3a8a5a)[0x7f21839eaa5a]
/usr/sbin/mysqld(_Z24plugin_foreach_with_maskP3THDPFcS0_P13st_plugin_intPvEijS3_+0x1dd)[0x7f2183b66b8d]
/usr/sbin/mysqld(_Z28ha_start_consistent_snapshotP3THD+0x281)[0x7f21839efa51]
/usr/sbin/mysqld(_Z11trans_beginP3THDj+0x100)[0x7f2183bed910]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x2b9b)[0x7f2183b560fb]
/usr/sbin/mysqld(+0x519ee8)[0x7f2183b5bee8]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1280)[0x7f2183b5d680]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x16c)[0x7f2183b5f8fc]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x31d)[0x7f2183b20d3d]
/usr/sbin/mysqld(handle_one_connection+0x40)[0x7f2183b20de0]
/usr/sbin/mysqld(pfs_spawn_thread+0x140)[0x7f2183ff4fb0]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7f2181f65182]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f218147230d]
More info:
When using mysqldump with --single- transaction, the START TRANSACTION WITH CONSISTENT SNAPHOST is executed, thus server crash:
root@daniel- vbox:/etc/ mysql# mysqldump --version
mysqldump Ver 10.13 Distrib 5.6.19-67.0, for debian-linux-gnu (x86_64)
root@daniel- vbox:/etc/ mysql# mysqldump --single- transaction dani -uroot -ptoor ------- ------- ------- ------- ------- ------- -----
Warning: Using a password on the command line interface can be insecure.
-- MySQL dump 10.13 Distrib 5.6.19-67.0, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: dani
-- -------
-- Server version 5.6.19-67.0-56
/*!40101 SET @OLD_CHARACTER_ SET_CLIENT= @@CHARACTER_ SET_CLIENT */; SET_RESULTS= @@CHARACTER_ SET_RESULTS */; CONNECTION= @@COLLATION_ CONNECTION */; ZONE=@@ TIME_ZONE */; CHECKS= @@UNIQUE_ CHECKS, UNIQUE_CHECKS=0 */; KEY_CHECKS= @@FOREIGN_ KEY_CHECKS, FOREIGN_ KEY_CHECKS= 0 */; MODE=@@ SQL_MODE, SQL_MODE= 'NO_AUTO_ VALUE_ON_ ZERO' */; NOTES=@ @SQL_NOTES, SQL_NOTES=0 */;
/*!40101 SET @OLD_CHARACTER_
/*!40101 SET @OLD_COLLATION_
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_
/*!40014 SET @OLD_FOREIGN_
/*!40101 SET @OLD_SQL_
/*!40111 SET @OLD_SQL_
mysqldump: Couldn't execute 'START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */': Lost connection to MySQL server during query (2013)
mysql> show variables like 'log_bin'; ------- --+---- ---+ ------- --+---- ---+ ------- --+---- ---+
+------
| Variable_name | Value |
+------
| log_bin | OFF |
+------
1 row in set (0,00 sec)
Stack: mysqld( my_print_ stacktrace+ 0x2c)[0x7f2183d 7c04c] mysqld( handle_ fatal_signal+ 0x3cb)[ 0x7f2183ac2a2b] 64-linux- gnu/libpthread. so.0(+0x10340) [0x7f2181f6d340 ] mysqld( +0x6e4cd7) [0x7f2183d26cd7 ] mysqld( +0x3a8a5a) [0x7f21839eaa5a ] mysqld( _Z24plugin_ foreach_ with_maskP3THDP FcS0_P13st_ plugin_ intPvEijS3_ +0x1dd) [0x7f2183b66b8d ] mysqld( _Z28ha_ start_consisten t_snapshotP3THD +0x281) [0x7f21839efa51 ] mysqld( _Z11trans_ beginP3THDj+ 0x100)[ 0x7f2183bed910] mysqld( _Z21mysql_ execute_ commandP3THD+ 0x2b9b) [0x7f2183b560fb ] mysqld( +0x519ee8) [0x7f2183b5bee8 ] mysqld( _Z16dispatch_ command19enum_ server_ commandP3THDPcj +0x1280) [0x7f2183b5d680 ] mysqld( _Z10do_ commandP3THD+ 0x16c)[ 0x7f2183b5f8fc] mysqld( _Z24do_ handle_ one_connectionP 3THD+0x31d) [0x7f2183b20d3d ] mysqld( handle_ one_connection+ 0x40)[0x7f2183b 20de0] mysqld( pfs_spawn_ thread+ 0x140)[ 0x7f2183ff4fb0] 64-linux- gnu/libpthread. so.0(+0x8182) [0x7f2181f65182 ] 64-linux- gnu/libc. so.6(clone+ 0x6d)[0x7f21814 7230d]
Thread pointer: 0x7f218681c2a0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f215c89ee00 thread_stack 0x30000
/usr/sbin/
/usr/sbin/
/lib/x86_
/usr/sbin/
/usr/sbin/
/usr/sbin/
/usr/sbin/
/usr/sbin/
/usr/sbin/
/usr/sbin/
/usr/sbin/
/usr/sbin/
/usr/sbin/
/usr/sbin/
/usr/sbin/
/lib/x86_
/lib/x86_
http:// www.percona. com/doc/ percona- server/ 5.6/management/ start_transacti on_with_ consistent_ snapshot. html#mysqldump Documentation specifies that STWCS is used when --single- transaction AND --master-data are used, but i was able to reproduce this only with --single- transaction