0

I need to backup one of my databases from MySQL (Running MariaDB). The issue is that aside from all of my other databases that backup perfectly fine, this database has issues backing up and crashes the server whenever I try.

mysqldump shadygam_forum -u root -p > dump.sql
Enter password:
mysqldump: Got error: 2013: "Lost connection to MySQL server during query" when using LOCK TABLES

So I look in the error log found at /var/lib/mysql/myerrorlog.err

InnoDB: Error: tried to read 16384 bytes at offset 0 819200.
InnoDB: Was only able to read 0.
141219  4:32:54  InnoDB: Operating system error number 5 in a file operation.
InnoDB: Error number 5 means 'Input/output error'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: File operation call: 'read'.
InnoDB: Cannot continue operation.
141219 04:32:54 mysqld_safe Number of processes running now: 0
141219 04:32:54 mysqld_safe mysqld restarted
141219  4:32:54 InnoDB: The InnoDB memory heap is disabled
141219  4:32:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141219  4:32:54 InnoDB: Compressed tables use zlib 1.2.3
141219  4:32:54 InnoDB: Using Linux native AIO
141219  4:32:54 InnoDB: Initializing buffer pool, size = 2.0G
141219  4:32:54 InnoDB: Completed initialization of buffer pool
141219  4:32:54 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
141219  4:32:54  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
141219  4:32:55  InnoDB: Waiting for the background threads to start
141219  4:32:56 Percona XtraDB (http://www.percona.com) 5.5.38-MariaDB-35.2 started; log sequence number 12116884277
141219  4:32:56 [Note] Plugin 'FEEDBACK' is disabled.
141219  4:32:56 [Note] Server socket created on IP: '0.0.0.0'.
141219  4:32:56 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
141219  4:32:56 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
141219  4:32:56 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
141219  4:32:56 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A
141219  4:32:56 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
141219  4:32:56 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.39-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
141219  4:32:59 [ERROR] mysqld: Table './shadygam_forum/xf_session' is marked as crashed and should be repaired
141219  4:32:59 [Warning] Checking table:   './shadygam_forum/xf_session'

Looking at some of these errors I've tried the following

mysql_upgrade -u root -p

Which goes through all the tables saying OK until it starts to process the tables of that database and crashes.

.
.
.
phanime_xenForo.xf_user_upgrade_expired            OK
phanime_xenForo.xf_user_upgrade_log                OK
phanime_xenForo.xf_warning                         OK
phanime_xenForo.xf_warning_action                  OK
phanime_xenForo.xf_warning_action_trigger          OK
phanime_xenForo.xf_warning_definition              OK
phanime_xenForo.xf_widget                          OK
phanime_xenForo.xf_widgetframework_widget_page     OK
shadygam_forum
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ...  FOR UPGRADE'
FATAL ERROR: Upgrade failed

So I check the error log and it's the exact same error.

So I've also tried doing the command to repair the shadygam_forum.xf_session table like so

REPAIR TABLE xf_session while in the shadygam_forum database and that crashes the server as well.

Just a note, the database is definitely still salvage because the app that is using this database is running perfectly fine without any known issues.

4
  • InnoDB: Error: tried to read 16384 bytes at offset 0 819200. Getting an I/O error on a read suggests a physical disk defect. Can you, from the shell, make a copy of shadygam_forum/xf_session.ibd? If you get an error when you try, then I would suggest that you almost certainly have disk issues. Dec 22, 2014 at 3:37
  • @Michael-sqlbot I don't have a shadygam_forum/xf_session.ibd file in /var/lib/mysql, however, I do have a xf_session.frm file. I was able to copy that file without any errors being thrown at me. Dec 22, 2014 at 14:52
  • Oh... SELECT @@innodb_file_per_table; please. Does that return 1 or 0? Dec 22, 2014 at 19:17
  • @Michael-sqlbot that returns a 1. MariaDB [(none)]> SELECT @@innodb_file_per_table; +-------------------------+ | @@innodb_file_per_table | +-------------------------+ | 1 | +-------------------------+ 1 row in set (0.00 sec) Dec 23, 2014 at 3:21

0

You must log in to answer this question.

Browse other questions tagged .