Xtrabackup SST does not copy empty test database | Bump the minimum PXB to 2.1.6

[Jay Janssen]

If I do a trivial 2 node setup with simple mysql_install_db default datadir, and Xtrabackup SST, my first node will have the 'test' database, but the second node will not:

node1:

total 2.2G
drwxr-xr-x. 5 mysql root 4.0K Sep 25 18:57 .
drwxr-xr-x. 16 root root 4.0K Sep 25 18:10 ..
-rw-rw----. 1 mysql mysql 20K Sep 25 18:59 error.log
-rw-------. 1 mysql mysql 129M Sep 25 18:57 galera.cache
-rw-rw----. 1 mysql mysql 104 Sep 25 18:57 grastate.dat
-rw-rw----. 1 mysql mysql 18M Sep 25 18:49 ibdata1
-rw-rw----. 1 mysql mysql 1.0G Sep 25 18:50 ib_logfile0
-rw-rw----. 1 mysql mysql 1.0G Sep 25 18:49 ib_logfile1
-rw-rw----. 1 mysql mysql 3.0K Sep 25 18:57 innobackup.backup.log
-rw-rw----. 1 mysql mysql 6 Sep 25 18:50 ip-10-238-195-117.pid
drwx------. 2 mysql root 4.0K Sep 25 18:45 mysql
srwxrwxrwx. 1 mysql mysql 0 Sep 25 18:50 mysql.sock
drwx------. 2 mysql mysql 4.0K Sep 25 18:45 performance_schema
drwx------. 2 mysql root 6 Sep 25 18:45 test
-rw-rw----. 1 mysql mysql 39 Sep 25 18:57 xtrabackup_galera_info

node2 (after SST):
total 2.2G
drwxr-xr-x. 4 mysql root 4.0K Sep 25 18:59 .
drwxr-xr-x. 16 root root 4.0K Sep 25 18:11 ..
-rw-rw----. 1 mysql mysql 263 Sep 25 18:57 backup-my.cnf
-rw-------. 1 mysql mysql 129M Sep 25 18:57 galera.cache
-rw-rw----. 1 mysql mysql 104 Sep 25 18:59 grastate.dat
-rw-rw----. 1 mysql mysql 18M Sep 25 18:58 ibdata1
-rw-rw----. 1 mysql mysql 1.0G Sep 25 18:58 ib_logfile0
-rw-rw----. 1 mysql mysql 1.0G Sep 25 18:58 ib_logfile1
-rw-rw----. 1 mysql mysql 5.3K Sep 25 18:58 innobackup.prepare.log
-rw-rw----. 1 mysql mysql 6 Sep 25 18:59 ip-10-149-26-184.pid
drwx------. 2 mysql mysql 4.0K Sep 25 18:57 mysql
srwxrwxrwx. 1 mysql mysql 0 Sep 25 18:59 mysql.sock
drwx------. 2 mysql mysql 4.0K Sep 25 18:57 performance_schema
-rw-rw----. 1 mysql mysql 13 Sep 25 18:57 xtrabackup_binary
-rw-rw----. 1 mysql mysql 77 Sep 25 18:58 xtrabackup_checkpoints
-rw-rw----. 1 mysql mysql 39 Sep 25 18:57 xtrabackup_galera_info
-rw-rw----. 1 mysql mysql 2.0M Sep 25 18:58 xtrabackup_logfile

This causes node2 to crash if I try to create a table in the 'test' schema on node1.

[Jay Janssen (jay-janssen)]

This is PXC 5.5.33

[Jay Janssen (jay-janssen)]

Log from SST of node2:

130925 18:57:38 [Note] WSREP: State transfer required:
 Group state: 4823adcb-2613-11e3-85b0-d2640a4c137f:1
 Local state: 00000000-0000-0000-0000-000000000000:-1
130925 18:57:38 [Note] WSREP: New cluster view: global state: 4823adcb-2613-11e3-85b0-d2640a4c137f:1, view# 4: Primary, number of nodes: 2, my index: 1, protocol version 2
130925 18:57:38 [Warning] WSREP: Gap in state sequence. Need state transfer.
130925 18:57:40 [Note] WSREP: Running: 'wsrep_sst_xtrabackup --role 'joiner' --address 'ec2-50-17-114-69.compute-1.amazonaws.com' --auth 'sst:secret' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --parent '10758''
WSREP_SST: [INFO] Streaming with xbstream (20130925 18:57:40.811)
WSREP_SST: [INFO] Using socat as streamer (20130925 18:57:40.815)
WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20130925 18:57:40.839)
130925 18:57:41 [Note] WSREP: Prepared SST request: xtrabackup|ec2-50-17-114-69.compute-1.amazonaws.com:4444/xtrabackup_sst
130925 18:57:41 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
130925 18:57:41 [Note] WSREP: Assign initial position for certification: 1, protocol version: 2
130925 18:57:41 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (4823adcb-2613-11e3-85b0-d2640a4c137f): 1 (Operation not permitted)
  at galera/src/replicator_str.cpp:prepare_for_IST():436. IST will be unavailable.
130925 18:57:41 [Note] WSREP: Node 1 (ip-10-149-26-184) requested state transfer from '*any*'. Selected 0 (ip-10-238-195-117)(SYNCED) as donor.
130925 18:57:41 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 1)
130925 18:57:41 [Note] WSREP: Requesting state transfer: success, donor: 0
WSREP_SST: [INFO] Proceeding with SST (20130925 18:57:41.263)
WSREP_SST: [INFO] Cleaning the existing datadir (20130925 18:57:41.268)
removed `/var/lib/mysql/error.log'
removed `/var/lib/mysql/mysql/db.frm'
removed `/var/lib/mysql/mysql/db.MYI'
removed `/var/lib/mysql/mysql/db.MYD'
removed `/var/lib/mysql/mysql/host.frm'
removed `/var/lib/mysql/mysql/host.MYI'
removed `/var/lib/mysql/mysql/host.MYD'
removed `/var/lib/mysql/mysql/user.frm'
removed `/var/lib/mysql/mysql/user.MYI'
removed `/var/lib/mysql/mysql/user.MYD'
removed `/var/lib/mysql/mysql/func.frm'
removed `/var/lib/mysql/mysql/func.MYI'
removed `/var/lib/mysql/mysql/func.MYD'
removed `/var/lib/mysql/mysql/plugin.frm'
removed `/var/lib/mysql/mysql/plugin.MYI'
removed `/var/lib/mysql/mysql/plugin.MYD'
removed `/var/lib/mysql/mysql/servers.frm'
removed `/var/lib/mysql/mysql/servers.MYI'
removed `/var/lib/mysql/mysql/servers.MYD'
removed `/var/lib/mysql/mysql/tables_priv.frm'
removed `/var/lib/mysql/mysql/tables_priv.MYI'
removed `/var/lib/mysql/mysql/tables_priv.MYD'
removed `/var/lib/mysql/mysql/columns_priv.frm'
removed `/var/lib/mysql/mysql/columns_priv.MYI'
removed `/var/lib/mysql/mysql/columns_priv.MYD'
removed `/var/lib/mysql/mysql/help_topic.frm'
removed `/var/lib/mysql/mysql/help_topic.MYI'
removed `/var/lib/mysql/mysql/help_topic.MYD'
removed `/var/lib/mysql/mysql/help_categ...

[Raghavendra D Prabhu (raghavendra-prabhu)]

This is a limitation of Xtrabackup itself - https://bugs.launchpad.net/percona-xtrabackup/+bug/1217426
This is in turn due to mysql's handling of 'test' database - in creating a database without db.opt by default. All other empty databases created have a db.opt in them, hence won't be affected by this.

Also documented here http://www.percona.com/doc/percona-xtradb-cluster/errata.html with workarounds.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Fixed the title since 'empty' schemas other than default test database have a db.opt in them and are still backed up.

Also, there won't be any fix for this from PXC end, it has been fixed in Xtrabackup and be released in its next 2.1.x release.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Fixed in https://bugs.launchpad.net/bugs/1217426, will be released around 19th November. I will also bump minimum versions after this.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Currently, it has been bumped to 2.1.4, but further bumping will require another release after 2.1.6 (since there are a few issues in 2.1.6 wrt. centos5). The documentation recommends to use latest PXB, even otherwise.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Marking as 'Fix Released' since PXB has been released with the fix.

[Alex Yurchenko (ayurchen)]

Fix pushed in:
http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3962
http://bazaar.launchpad.net/~codership/codership-mysql/5.6/revision/4058

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1449