drop, recreate and restore individual innodb tables leads to corruption

MySQL & MariaDB Percona XtraBackup
1

Hi,

I do full streaming backups with innobackupex, piped to bzip2 innobackupex --galera-info --stream=tar $dump_to/tmp |bzip2. I also dump each table structure with mysqldump --opt -d $db_table

To transfer periodically some tables on another test server, i uncompress, run innobackupex --apply-log --export.
Then, on a live Percona cluster (only one node), i import my mysqldump which drops and recreates each table with the same structure it had on original server, then do ALTER TABLE $table DISCARD TABLESPACE;, copy the $table.{ibd,exp} files from the backup (as stated from documentation), and issue a ALTER TABLE $table IMPORT TABLESPACE;.

The resulting MySQL starts and stays alive, but after a few queries from our application, it crashes. I can restart it well, but once after few other queries, it crashes again, with these kind of messages.

The source database is not corrupted, the backup is fine because when i try to restore it fully with innobackupex --copy-back, the resulting MySQL works well.

What am i doing wrong ? Do i also need to copy the .frm and .cfg files ?


(...)
2014-12-06 21:19:22 8806 [Note] WSREP: Member 0 (node1) synced with group.
2014-12-06 21:19:22 8806 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 66213)
2014-12-06 21:19:22 8806 [Note] WSREP: New cluster view: global state: 5fc1d69f-7be8-11e4-80cb-8278baded779:66213, view# 1: Primary
, number of nodes: 1, my index: 0, protocol version 2
2014-12-06 21:19:22 8806 [Note] WSREP: SST complete, seqno: 66213
2014-12-06 21:19:22 8806 [Note] Plugin 'FEDERATED' is disabled.
2014-12-06 21:19:22 8806 [Note] InnoDB: The InnoDB memory heap is disabled
2014-12-06 21:19:22 8806 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-12-06 21:19:22 8806 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-12-06 21:19:22 8806 [Note] InnoDB: Using Linux native AIO
2014-12-06 21:19:22 8806 [Note] InnoDB: Using CPU crc32 instructions
2014-12-06 21:19:22 8806 [Note] InnoDB: Initializing buffer pool, size = 512.0M
2014-12-06 21:19:22 8806 [Note] InnoDB: Completed initialization of buffer pool
2014-12-06 21:19:22 8806 [Note] InnoDB: Highest supported file format is Barracuda.
2014-12-06 21:19:22 8806 [Note] InnoDB: 128 rollback segment(s) are active.
2014-12-06 21:19:22 8806 [Note] InnoDB: Waiting for purge to start
2014-12-06 21:19:22 8806 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.15-rel63.0 started; log sequence number 674654
14284
2014-12-06 21:19:22 8806 [Note] RSA private key file not found: /opt/mysql_data//private_key.pem. Some authentication plugins will
not work.
2014-12-06 21:19:22 8806 [Note] RSA public key file not found: /opt/mysql_data//public_key.pem. Some authentication plugins will no
t work.
2014-12-06 21:19:22 8806 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2014-12-06 21:19:22 8806 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2014-12-06 21:19:22 8806 [Note] Server socket created on IP: '0.0.0.0'.
2014-12-06 21:19:22 8806 [Warning] 'proxies_priv' entry '@ root@bla.bla.bla' ignored in --skip-name-resolve mo
de.
2014-12-06 21:19:22 8806 [Note] Event Scheduler: Loaded 0 events
2014-12-06 21:19:22 8806 [Note] WSREP: inited wsrep sidno 2
2014-12-06 21:19:22 8806 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.15-56-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona XtraDB Cluster (GPL), Release 25.5, Revision 759
, wsrep_25.5.r4061
2014-12-06 21:19:22 8806 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2014-12-06 21:19:22 8806 [Note] WSREP: Assign initial position for certification: 66213, protocol version: 2
2014-12-06 21:19:22 8806 [Note] WSREP: Synchronized with group, ready for connections
2014-12-06 21:19:22 8806 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
InnoDB: Error: trying to access page number 16777207 in space 92,
InnoDB: space name backoffice/stores,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
2014-12-06 21:19:45 7fa98806d700 InnoDB: Assertion failure in thread 140366108350208 in file fil0fil.cc line 5457
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
21:19:45 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Please help us make Percona XtraDB Cluster better by reporting any
bugs at https://bugs.launchpad.net/percona-xtradb-cluster

key_buffer_size=33554432
read_buffer_size=4194304
max_used_connections=5
max_threads=502
thread_count=5
connection_count=5
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2224983 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x2beb3c0
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 = 7fa98806cd38 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x902645]
/usr/sbin/mysqld(handle_fatal_signal+0x4c4)[0x6801e4]
/lib64/libpthread.so.0[0x3ff280f710]
/lib64/libc.so.6(gsignal+0x35)[0x3ff2432925]
/lib64/libc.so.6(abort+0x175)[0x3ff2434105]
/usr/sbin/mysqld[0xac672e]
/usr/sbin/mysqld[0xa9600c]
/usr/sbin/mysqld[0xa96ca3]
/usr/sbin/mysqld[0xa80a8e]
/usr/sbin/mysqld[0xa6d4d2]
/usr/sbin/mysqld[0xa11297]
/usr/sbin/mysqld[0x967a09]
/usr/sbin/mysqld[0x95d0d9]
/usr/sbin/mysqld(_ZN7handler14ha_index_firstEPh+0x66)[0x5c0d46]
/usr/sbin/mysqld(_Z15join_read_firstP13st_join_table+0x81)[0x6e0a81]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x15d)[0x6e172d]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x2aa)[0x6e1faa]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x250)[0x728540]
/usr/sbin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x187)[0x728dc7]
/usr/sbin/mysqld[0x70020d]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x3904)[0x704f54]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x608)[0x707908]
/usr/sbin/mysqld[0x707a31]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1ad4)[0x709cb4]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x1e3)[0x70b083]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x17f)[0x6d492f]
/usr/sbin/mysqld(handle_one_connection+0x47)[0x6d4b07]
/usr/sbin/mysqld(pfs_spawn_thread+0x12a)[0xb3c0da]
/lib64/libpthread.so.0[0x3ff28079d1]
/lib64/libc.so.6(clone+0x6d)[0x3ff24e8b5d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fa91c006d80): is an invalid pointer
Connection ID (thread ID): 5
Status: NOT_KILLED

You may download the Percona XtraDB Cluster operations manual by visiting
http://www.percona.com/software/percona-xtradb-cluster/. You may find information
in the manual which will help you identify the cause of the crash.
2

Both the source MySQL server and the destination MySQL of the restore have the same /etc/my.cnf:


# Galera monitoring:
# http://www.codership.com/wiki/doku.php?id=monitoring
# http://www.codership.com/wiki/doku.php?id=galera_status_0.8

[mysql]

###
# CLIENT #
###
port = 3306
#socket = /opt/mysql_data/mysql.sock

default-character-set = utf8


[mysqld]

###
# GENERAL #
###
bind-address = "0.0.0.0"
user = mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid

character-set-server = utf8
character-set-filesystem = utf8
collation-server = utf8_general_ci

###
# DATA STORAGE #
###
datadir = /opt/mysql_data
default-storage-engine = innodb

###
# BINARY LOGGING #
###
log-bin = /opt/mysql_data/mysql-bin
expire_logs_days = 14
sync_binlog = 1

# In order for Galera to work correctly binlog format should be ROW
binlog-format = ROW

###
# CACHES AND LIMITS #
###
tmp_table_size = 32M
max_heap_table_size = 32M
max_connections = 500
thread_cache_size = 500
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 2048

# must set both to 0 for Galera (no more valid with recent versions)
# nevertheless, it is recommended to disable it, especially for write oriented DBs)
query_cache_size = 0
query_cache_type = 0

###
# INNODB #
###
innodb_flush_method = O_DSYNC
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_file_per_table = 1
innodb_file_format = Barracuda

# On a dedicated server, you can pput that to 80% of RAM
# https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
innodb_buffer_pool_size = 512M

# Set innodb-flush-log-at-trx-commit=0 for even faster performance and reduced durability
# https://dba.stackexchange.com/questions/12611/is-it-safe-to-use-innodb-flush-log-at-trx-commit-2
#innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit = 2

# Performance tweaks
# https://dba.stackexchange.com/questions/2918/about-single-threaded-versus-multithreaded-databases-performance
# https://dba.stackexchange.com/questions/5926/multi-cores-and-mysql-performance
# https://dba.stackexchange.com/questions/5666/possible-to-make-mysql-use-more-than-one-core
innodb_thread_concurrency = 0
innodb_read_io_threads = 10 # If you have a strong I/O system or SSD
innodb_write_io_threads = 10 # If you have a strong I/O system or SSD
#innodb_io_capacity = 1000 # If you have a strong I/O system or SSD
innodb_use_sys_malloc = 1
#innodb_concurrency_tickets =
innodb_checksum_algorithm = strict_crc32
#innodb_open_files = 1000

join_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M

# ZFSonlinux does not support AIO yet (MySQL 5.1 InnoDB did not use AIO, but using Percona 5.5 here)
innodb_use_native_aio = 1

# Disable that for ZFS
innodb_doublewrite = true

innodb_lock_wait_timeout = 250

# This is a recommended tuning variable for performance
# and a must for parallel applying
transaction-isolation = READ-COMMITTED
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode = 2


###
# LOGGING #
###
log-error = /var/log/mysqld.log
log-queries-not-using-indexes = 1
slow-query-log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysqld-slow.log
log-slave-updates = 1


###
# GALERA CLUSTER #
###
server-id = 1

# Path to Galera library
wsrep_provider = /usr/lib64/libgalera_smm.so

# Cluster name
wsrep_cluster_name = mycluster

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
#wsrep_cluster_address = gcomm://
wsrep_cluster_address = gcomm://
wsrep_node_name = node1

# Node #1 address
wsrep_node_address = "xx.xx.xx.xx"

wsrep_slave_threads = 3

# SST method
wsrep_sst_method = xtrabackup

# Authentication for SST method
wsrep_sst_auth = "sst:xxxxxxxxxxx"

###
# MyISAM #
###
key_buffer_size = 32M
myisam-recover-options = FORCE,BACKUP

###
# SAFETY #
###
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1
3

To answer my own question:

Despites what the doc says (or my understanding), i copied all the $table.* files and it seems to work