1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Backing up database problems (mySQL)

Discussion in 'General PHP and MySQL Discussions' started by faeronsayn, Oct 20, 2014.

  1. faeronsayn

    faeronsayn Well-Known Member

    I'm trying to export one of my databases from mysql, but for some reason it won't export through mysql dump or anything. However, the forum itself is running without any issues.

    I've tried a few things to have it connect

    Code:
    $ mysql -u root -p
    $ use my_database
    $ show tables
    
    ERROR 2006(HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    Connection id:1Currentdatabase: my_database
    
    ERROR 2006(HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    ERROR 2002(HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
    ERROR:
    Can't connect to the server
    
    I really am only trying to get the dump of the database

    Code:
    $ mysqldump -u root -p my_database | gzip > my_database.sql.gz
    
    this gives me the following error

    Code:
    mysqldump: Got error:2013: Lost connection to MySQL server during query whenusing LOCK TABLES
    
    I've also tried

    Code:
    mysqlcheck -u root -p -A
    
    It goes through all the tables until it tries to access the tables on my_database where it fails and throws this error

    Code:
    mysqlcheck: Got error:2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... '
    
    Looking at the logs, I get the following errors

    Code:
    InnoDB: File operation call: 'read'.
    InnoDB: Cannot continue operation.
    141019 23:53:43 mysqld_safe Number of processes running now: 0
    141019 23:53:43 mysqld_safe mysqld restarted
    141019 23:53:43 InnoDB: The InnoDB memory heap is disabled
    141019 23:53:43 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    141019 23:53:43 InnoDB: Compressed tables use zlib 1.2.3
    141019 23:53:43 InnoDB: Using Linux native AIO
    141019 23:53:43 InnoDB: Initializing buffer pool, size = 2.0G
    141019 23:53:43 InnoDB: Completed initialization of buffer pool
    141019 23:53:43 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!
    141019 23:53:43  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...
    141019 23:53:43  InnoDB: Waiting for the background threads to start
    141019 23:53:44 Percona XtraDB (http://www.percona.com) 5.5.38-MariaDB-35.2 started; log sequence number 10733071689
    141019 23:53:44 [Note] Plugin 'FEEDBACK' is disabled.
    141019 23:53:44 [Note] Server socket created on IP: '0.0.0.0'.
    141019 23:53:44 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
    141019 23:53:44 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
    141019 23:53:44 [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
    141019 23:53:44 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
    141019 23:53:44 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.5.39-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
    141019 23:53:46 [ERROR] mysqld: Table './my_database/xf_session' is marked as crashed and should be repaired
    141019 23:53:46 [Warning] Checking table:   './my_database/xf_session'
    141019 23:56:05 [ERROR] mysqld: Table './asdf/xf_session' is marked as crashed and should be repaired
    141019 23:56:05 [Warning] Checking table:   './asdf/xf_session'
    141019 23:56:39 [ERROR] mysqld: Table './my_database/xf_search_index' is marked as crashed and should be repaired
    141019 23:56:39 [Warning] Checking table:   './my_database/xf_search_index'
    
    Rebuilt the search index on xenForo, and it did it without much issue.

    I also tried running the mysql_upgrade command:

    Code:
    $ mysql_upgrade -u root - p
    
    Which goes through all the databases and then crashes right when it gets to my_database.

    Here is the error

    Code:
    ...
    ...
    ...
    asdf.xf_warning_action_trigger          OK
    asdf.xf_warning_definition              OK
    asdf.xf_widget                          OK
    asdf.xf_widgetframework_widget_page     OK
    my_database
    mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ...  FOR UPGRADE'
    FATAL ERROR: Upgrade failed
    
    I cannot figure out what else I can try to do to fix this. If anyone has an idea, it would be great to hear.
     
  2. MattW

    MattW Well-Known Member

    Did you fix the issue with the table that was throwing the errors when I had a look for you?

    Code:
    Enter password:
    mysqldump: Couldn't execute 'show table status like 'EWRporta\_categories'': Lost connection to MySQL server during query (2013)
     
  3. faeronsayn

    faeronsayn Well-Known Member

    After removing basically all EWR related addons and updating xenForo, i'm not sure it was fixed but I haven't received that error.

    Code:
    Enter password:
    mysqldump: Got error: 2013: "Lost connection to MySQL server during query" when using LOCK TABLES
    
     
  4. Mike

    Mike XenForo Developer Staff Member

    You can see errors about tables being crashed in that log file. That would need to be resolved.

    Behavior like this does generally indicate data corruption, likely in InnoDB tables. There should be some more info in the error log, though you may need to restart MySQL, which may not come back up. This is where InnoDB recovery may be needed: http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html
     
    faeronsayn likes this.
  5. faeronsayn

    faeronsayn Well-Known Member

    restarting mysql (mariadb) works without any issues. I added into my.cnf file

    Code:
    [mysqld]
    innodb_force_recovery = 1
    
    Which still didn't let me show tables, or select anything (crashes as usual).

    So I increased the number to 2, and then mysql(mariadb) wouldn't start.
     

Share This Page