Not the answer you need?
Register and ask your own question!

drop, recreate and restore individual innodb tables leads to corruption

alexfalexf EntrantInactive User Role Beginner
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.

Comments

  • alexfalexf Entrant Inactive User Role Beginner
    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
    
  • alexfalexf Entrant Inactive User Role Beginner
    To answer my own question:

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

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.