0

I have run out of disc space during a data import filling up the mysql disk to 100%. Since MySQL was not able to start from that point anymore, I deleted manually some old binlog files.

Now the server will not start regarless what I try. E.g. set innodb_force_recovery = 5.

Error log:

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2021-05-24T07:31:41.025108Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2021-05-24T07:31:41.026693Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.25-0ubuntu0.20.04.1) starting as process 1807
2021-05-24T07:31:41.027619Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2021-05-24T07:31:41.027635Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
2021-05-24T07:31:41.036907Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-05-24T07:31:41.642196Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: fil0fil.cc:10754:initial_fsize == (file->size * phy_page_size) thread 139991710930688
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/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
07:31:41 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x559704cddc40
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 = 7f525c338d20 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x5596fed6e681]
/usr/sbin/mysqld(handle_fatal_signal+0x31b) [0x5596fdbbef6b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x153c0) [0x7f52691a33c0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcb) [0x7f526880118b]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x12b) [0x7f52687e0859]
/usr/sbin/mysqld(+0xea257e) [0x5596fd8e857e]
/usr/sbin/mysqld(fil_tablespace_redo_extend(unsigned char*, unsigned char const*, page_id_t const&, unsigned long, bool)+0x510) [0x5596ff1fd7f0]
/usr/sbin/mysqld(+0x24e30e9) [0x5596fef290e9]
/usr/sbin/mysqld(+0x24e6d7e) [0x5596fef2cd7e]
/usr/sbin/mysqld(recv_recovery_from_checkpoint_start(log_t&, unsigned long)+0x271d) [0x5596fef329ad]
/usr/sbin/mysqld(srv_start(bool)+0x206e) [0x5596ff03466e]
/usr/sbin/mysqld(+0x2428a9f) [0x5596fee6ea9f]
/usr/sbin/mysqld(dd::bootstrap::DDSE_dict_init(THD*, dict_init_mode_t, unsigned int)+0x9e) [0x5596feafd17e]
/usr/sbin/mysqld(dd::upgrade_57::do_pre_checks_and_initialize_dd(THD*)+0x1a9) [0x5596fed418a9]
/usr/sbin/mysqld(+0x1234016) [0x5596fdc7a016]
/usr/sbin/mysqld(+0x28dfefa) [0x5596ff325efa]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x9609) [0x7f5269197609]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43) [0x7f52688dd293]

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

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

my.cnf:

[mysqld]
innodb_force_recovery = 5
default_authentication_plugin= mysql_native_password
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 32
innodb_buffer_pool_chunk_size = 201326592    
collation_server        = utf8_unicode_ci
character_set_server    = utf8    
max_allowed_packet = 1G

sql-mode="STRICT_TRANS_TABLES,IGNORE_SPACE,NO_ENGINE_SUBSTITUTION"
# NO_ZERO_IN_DATE, NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO

The data itself can be recovered from backups. Is there anything I can do from that point on to save me from reinstalling MySQL?

1
  • 1
    Not directly relevant to the question but you should have ONLY_FULL_GROUP_BY in your sql-mode settings - without it, results can be (ahem...) unreliable... May 24, 2021 at 11:35

1 Answer 1

0

If you are forced to use innodb_force_recovery with a value higher than 3, then the target database is pretty much untrustworthy. There is corruption somewhere in the InnoDB files and you'll need to start over (or forever wonder when — not if — bad data will rear its unwelcome head).

If there is nothing that you specifically need to save from the corrupted database, then log in with an admin account, drop the damaged database, create another one, and try again.

If the system ran out of storage due to the binary logs consuming the space, you may be able to prevent a repeat by temporarily disabling them:

mysql --init-command="SET SESSION sql_log_bin=0;" -uadmin -p db_name < big_import.sql

Update to handle Error 3508

Looks like there are some serious problems with the tablespace. You are going to have to do something really ugly to get out of this one. Assuming you're using a Debian-based Linux server with MySQL:

  1. Stop MySQL:
    sudo service mysqld stop
    
  2. Delete the affected database via the file system:
    cd /var/lib/mysql
    rm -Rf geodata_3
    
    Note: You will likely need to be sudo to do this. Be sure to change /var/lib/mysql to whatever directory you are using for the MySQL data.
  3. Comment out the InnoDB_force_recovery line from your configuration file:
    #innodb_force_recovery=
    
  4. Save the changes to the configuration file
  5. Cross fingers
  6. Restart MySQL:
    sudo service mysqld start
    

If you are using a different Operating System, be sure to adjust the steps accordingly. MySQL will complain when it cannot find the missing database, but it should start and update its internal tables correctly.

4
  • I was able to start MySQL Server by using innodb_force_recovery=6. Now I understand to drop the dbs one by one. But the talbes apear to be read only. mysql> drop database geodata_3; ERROR 3508 (HY000): Dictionary object id (549) does not exist.
    – merlin
    May 24, 2021 at 8:33
  • When you are forcing a recovery, the database is doing everything it can to preserve the data. Anything higher than 3 means "zOMG! DANGER WILL ROBINSON!", so MySQL takes the cautious approach and refuses to let you (or any process) write to the InnoDB tables. Doing so can result in permanent data loss. Using innodb_force_recovery values 4, 5, or 6 are for last-ditch efforts to export data before it's lost forever.
    – matigo
    May 24, 2021 at 8:36
  • Understood. But how can I get MySQL working again? It is a local test server machine where the data is not important. Just want to get it running again without too much work rebuilding LAMP again. MySQL only starts with level 6 now.
    – merlin
    May 24, 2021 at 8:38
  • Looks like I fixed it by rm the directory of the last db I tried to import. Then startet at level=0. Needed to create the dir again, then restart, then drop database. Strange...
    – merlin
    May 24, 2021 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.