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

Bug #1231088 reported by Jay Janssen on 2013-09-25
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL patches by Codership
Status tracked in 5.6
5.5
Medium
Alex Yurchenko
5.6
Medium
Alex Yurchenko
Percona XtraDB Cluster
Status tracked in 5.6
5.5
Undecided
Unassigned
5.6
Undecided
Unassigned

Bug Description

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) wrote :

This is PXC 5.5.33

Jay Janssen (jay-janssen) wrote :
Download full text (8.9 KiB)

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

Read more...

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.

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.

summary: - Xtrabackup SST does not copy empty schemas
+ Xtrabackup SST does not copy empty test database

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

summary: - Xtrabackup SST does not copy empty test database
+ Xtrabackup SST does not copy empty test database | Bump the minimum PXB
+ to 2.1.6

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.

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

tags: added: sst xtrabackup
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers