Database crashes and tablespace gets corrupted
Posted by: Bicho Verde
Date: August 08, 2023 07:01AM

I need help figuring out what is going on.

This is the 3rd time the database crashes and tablespace gets corrupted.

Since the 2nd time:

Updated MySQL from 3.0.33 to 3.0.34 (full reinstall).
Removed database and restored from a mysqldump backup.
Changed kernel from version 3 (default for centos 7) to version 5.4.
Checked SSDs with smartctl, fsck and badblocks.
Checked memory with memtest86+.

Yet, it crashed again and tablespace got corrupted.

Kernel version (centos 7):
Linux xxx 5.4.251-1.el7.elrepo.x86_64 #1 SMP Thu Jul 27 18:49:53 EDT 2023 x86_64 x86_64 x86_64 GNU/Linux

MySQL version:
mysql Ver 8.0.34 for Linux on x86_64 (MySQL Community Server - GPL)

/var/log/messages

Aug 8 01:15:02 xxx systemd: mysqld.service: main process exited, code=exited, status=2/INVALIDARGUMENT
Aug 8 01:15:02 xxx systemd: Unit mysqld.service entered failed state.
Aug 8 01:15:02 xxx systemd: mysqld.service failed.
Aug 8 01:15:03 xxx systemd: mysqld.service holdoff time over, scheduling restart.
Aug 8 01:15:03 xxx systemd: Stopped MySQL Server.
Aug 8 01:15:03 xxx systemd: Starting MySQL Server...
Aug 8 01:15:15 xxx systemd: Started MySQL Server.

/var/log/mysqld.log

2023-08-08T04:15:02Z UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=33c7e0aa240d478d45e6ee0a2d11828ba1deb657
Thread pointer: 0x7f698bbaae20
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 = 7f844c19bb30 thread_stack 0x100000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x211745d]
/usr/sbin/mysqld(print_fatal_signal(int)+0x37f) [0xfd7ebf]
/usr/sbin/mysqld(handle_fatal_signal+0xa5) [0xfd7f75]
/lib64/libpthread.so.0(+0xf630) [0x7f845dd84630]
/usr/sbin/mysqld(Item_field::fix_outer_field(THD*, Field**, Item**)+0xe1) [0x11286a1]
/usr/sbin/mysqld(Item_field::fix_fields(THD*, Item**)+0x452) [0x11296b2]
/usr/sbin/mysqld(setup_fields(THD*, unsigned long, bool, bool, bool, mem_root_deque<Item*> const*, mem_root_deque<Item*>*, Bounds_checked_array<Item*>)+0x201) [0xdc1231]
/usr/sbin/mysqld(Query_block::prepare(THD*, mem_root_deque<Item*>*)+0x368) [0xebbd18]
/usr/sbin/mysqld(Query_expression::prepare(THD*, Query_result*, mem_root_deque<Item*>*, unsigned long long, unsigned long long)+0x16f) [0xf4615f]
/usr/sbin/mysqld(Item_subselect::fix_fields(THD*, Item**)+0x77) [0x11fb647]
/usr/sbin/mysqld(setup_fields(THD*, unsigned long, bool, bool, bool, mem_root_deque<Item*> const*, mem_root_deque<Item*>*, Bounds_checked_array<Item*>)+0x201) [0xdc1231]
/usr/sbin/mysqld(Query_block::prepare(THD*, mem_root_deque<Item*>*)+0x368) [0xebbd18]
/usr/sbin/mysqld(Query_expression::prepare(THD*, Query_result*, mem_root_deque<Item*>*, unsigned long long, unsigned long long)+0x16f) [0xf4615f]
/usr/sbin/mysqld(Table_ref::resolve_derived(THD*, bool)+0x286) [0x1355c46]
/usr/sbin/mysqld(mysqld_show_create(THD*, Table_ref*)+0xd65) [0xee39e5]
/usr/sbin/mysqld(Sql_cmd_show_create_table::execute_inner(THD*)+0x102) [0xee6382]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0xb31) [0xe6a961]
/usr/sbin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x520) [0xe6e5f0]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xdbe) [0xe6f95e]
/usr/sbin/mysqld(do_command(THD*)+0x207) [0xe71cc7]
/usr/sbin/mysqld() [0xfc8180]
/usr/sbin/mysqld() [0x27bb575]
/lib64/libpthread.so.0(+0x7ea5) [0x7f845dd7cea5]
/lib64/libc.so.6(clone+0x6d) [0x7f845c396b0d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f699b74a710): show create table `xxx`
Connection ID (thread ID): 207
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_strict_mode = ON
innodb_dedicated_server = ON
innodb_file_per_table = ON
innodb_log_buffer_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_adaptive_hash_index = OFF
max_allowed_packet = 1G
tmp_table_size = 1G
max_heap_table_size = 1G
disable_log_bin
replicate-do-db = xxx
relay_log = xxx-relay-bin
slave_parallel_workers = 0
server_id = 123
default_authentication_plugin = mysql_native_password
skip_name_resolve
character_set_server = latin1
collation_server = latin1_swedish_ci
datadir=/ssd1/mysql
socket=/ssd1/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Options: ReplyQuote


Subject
Written By
Posted
Database crashes and tablespace gets corrupted
August 08, 2023 07:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.