Observing assertion failure in MySQL 5.6
Description
Environment
Attachments
Smart Checklist
Activity
Lalit Choudhary November 5, 2020 at 10:47 AM
Hi
Thank you for the report.
cause for this crash is Orphan Intermediate Tables.
If MySQL exits in the middle of an in-place ALTER TABLE operation (ALGORITHM=INPLACE), you may be left with an orphan intermediate table that takes up space on your system. This section describes how to identify and remove orphan intermediate tables.
Intermediate table names begin with an #sql-ib prefix (e.g., #sql-ib87-856498050). The accompanying .frm file has an #sql-* prefix and is named differently (e.g., #sql-36ab_2.frm).
Follow documentation steps to remove these Orphan tables.
Ref: https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
Chaitanya Tondlekar September 28, 2020 at 2:57 PM
We also observed that the space which was shown missing or doesn't exist, we found that in innodb_sys_tables.
Question arrises that why name of the table is # marked in starting ?
We observed that whenever assertion failures happens we are able to find the space in innodb_sys_tables with such names,
Details
Details
Assignee
Reporter
Chaitanya TondlekarAffects versions
Priority
MediumSmart Checklist
Open Smart Checklist
Smart Checklist
We are observing assertion failure anytime on MySQL 5.6.47.
===================MySQL error log============
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
2020-09-25 18:47:51 7ee0898da700 InnoDB: Error: trying to access tablespace 3279980 page no. 1,
InnoDB: but the tablespace does not exist or is just being dropped.
InnoDB: Error: Unable to read tablespace 3279980 page no 1 into the buffer pool after 100 attempts
InnoDB: The most probable cause of this error may be that the table has been corrupted.
InnoDB: You can try to fix this problem by using innodb_force_recovery.
InnoDB: Please see reference manual for more details.
InnoDB: Aborting...
2020-09-25 18:47:51 7ee0898da700 InnoDB: Assertion failure in thread 139502845536000 in file buf0buf.cc line 2867
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.
23:47:51 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 Server better by reporting any
bugs at https://bugs.percona.com/
key_buffer_size=33554432
read_buffer_size=131072
max_used_connections=705
max_threads=5002
thread_count=625
connection_count=624
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2023237 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x0
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 = 0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0x55dd37bf0d1b]
/usr/sbin/mysqld(handle_fatal_signal+0x49b)[0x55dd37963a1b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12890)[0x7f308251f890]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xc7)[0x7f308181be97]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x141)[0x7f308181d801]
/usr/sbin/mysqld(+0x85dc2f)[0x55dd37d8bc2f]
/usr/sbin/mysqld(+0x74c86f)[0x55dd37c7a86f]
/usr/sbin/mysqld(+0x757818)[0x55dd37c85818]
/usr/sbin/mysqld(+0x861dee)[0x55dd37d8fdee]
/usr/sbin/mysqld(+0x8adb88)[0x55dd37ddbb88]
/usr/sbin/mysqld(+0x7fa780)[0x55dd37d28780]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7f30825146db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f30818fe88f]
You may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.
2020-09-25 18:48:03 7f8af3bdc740 InnoDB: Warning: Using innodb_locks_unsafe_for_binlog is DEPRECATED. This option may be removed in future releases. Please use READ COMMITTED transaction isolation level instead, see http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html.
2020-09-25 18:48:03 8284 [Note] InnoDB: Using atomics to ref count buffer pool pages
2020-09-25 18:48:03 8284 [Note] InnoDB: The InnoDB memory heap is disabled
2020-09-25 18:48:03 8284 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-09-25 18:48:03 8284 [Note] InnoDB: Memory barrier is not used
2020-09-25 18:48:03 8284 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-09-25 18:48:03 8284 [Note] InnoDB: Using Linux native AIO
2020-09-25 18:48:03 8284 [Note] InnoDB: Using CPU crc32 instructions
2020-09-25 18:48:03 8284 [Note] InnoDB: Initializing buffer pool, size = 300.0G
2020-09-25 18:48:03 8284 [Note] InnoDB: Setting NUMA memory policy to MPOL_INTERLEAVE
2020-09-25 18:50:22 8284 [Note] InnoDB: Setting NUMA memory policy to MPOL_DEFAULT
2020-09-25 18:50:22 8284 [Note] InnoDB: Completed initialization of buffer pool
2020-09-25 18:50:24 8284 [Note] InnoDB: Highest supported file format is Barracuda.
2020-09-25 18:50:24 8284 [Note] InnoDB: Log scan progressed past the checkpoint lsn 175854505815456
2020-09-25 18:50:24 8284 [Note] InnoDB: Database was not shutdown normally!
2020-09-25 18:50:24 8284 [Note] InnoDB: Starting crash recovery.
2020-09-25 18:50:24 8284 [Note] InnoDB: Reading tablespace information from the .ibd files...
2020-09-25 18:50:54 8284 [Note] InnoDB: Restoring possible half-written data pages
2020-09-25 18:50:54 8284 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 175854511057920
InnoDB: Doing recovery: scanned up to log sequence number 175854516300800
InnoDB: Doing recovery: scanned up to log sequence number 175854521543680
InnoDB: Doing recovery: scanned up to log sequence number 175854526786560
InnoDB: Doing recovery: scanned up to log sequence number 175854532029440
InnoDB: Doing recovery: scanned up to log sequence number 175854537272320
InnoDB: Doing recovery: scanned up to log sequence number 175854542515200
InnoDB: Doing recovery: scanned up to log sequence number 175854547758080
InnoDB: Doing recovery: scanned up to log sequence number 175854553000960
InnoDB: Doing recovery: scanned up to log sequence number 175855884692480
InnoDB: Doing recovery: scanned up to log sequence number 175855889935360
InnoDB: Doing recovery: scanned up to log sequence number 175855895178240
InnoDB: Doing recovery: scanned up to log sequence number 175855900421120
InnoDB: Doing recovery: scanned up to log sequence number 175855905664000
InnoDB: Doing recovery: scanned up to log sequence number 175855910906880
InnoDB: Doing recovery: scanned up to log sequence number 175855916149760
InnoDB: Doing recovery: scanned up to log sequence number 175855921392640
InnoDB: Doing recovery: scanned up to log sequence number 175855926635520
InnoDB: Doing recovery: scanned up to log sequence number 175855931878400
InnoDB: Doing recovery: scanned up to log sequence number 175855937121280
InnoDB: Doing recovery: scanned up to log sequence number 175855942364160
InnoDB: Doing recovery: scanned up to log sequence number 175855947607040
InnoDB: Doing recovery: scanned up to log sequence number 175855952849920
InnoDB: Doing recovery: scanned up to log sequence number 175855958092800
InnoDB: Doing recovery: scanned up to log sequence number 175855963335680
InnoDB: Doing recovery: scanned up to log sequence number 175855968578560
InnoDB: Doing recovery: scanned up to log sequence number 175855971651021
2020-09-25 18:51:18 8284 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 180452, file name mysql-bin.002437
2020-09-25 18:51:34 8284 [Note] InnoDB: 128 rollback segment(s) are active.
2020-09-25 18:51:34 8284 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.47-87.0 started; log sequence number 175855971651021
2020-09-25 18:51:34 8284 [Note] Recovering after a crash using /var/log/mysql/mysql-bin
2020-09-25 18:51:34 8284 [Note] Starting crash recovery...
2020-09-25 18:51:34 8284 [Note] Crash recovery finished.
2020-09-25 18:51:34 8284 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2020-09-25 18:51:34 8284 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2020-09-25 18:51:34 8284 [Note] Server hostname (bind-address): '*'; port: 3306
2020-09-25 18:51:34 8284 [Note] IPv6 is available.
2020-09-25 18:51:34 8284 [Note] - '::' resolves to '::';
2020-09-25 18:51:34 8284 [Note] Server socket created on IP: '::'.
2020-09-25 18:51:34 8284 [Warning] 'user' entry 'root@ex121-ssd' ignored in --skip-name-resolve mode.
2020-09-25 18:51:34 8284 [Warning] 'user' entry '@ex121-ssd' ignored in --skip-name-resolve mode.
2020-09-25 18:51:34 8284 [Warning] 'proxies_priv' entry '@ root@ex121-ssd' ignored in --skip-name-resolve mode.
2020-09-25 18:51:34 8284 [Note] Event Scheduler: Loaded 0 events
2020-09-25 18:51:34 8284 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.47-87.0-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Percona Server (GPL), Release 87.0, Revision 9ad342b
2020-09-25 18:51:34 8284 [Note] Event Scheduler: scheduler thread started with id 1
================================
We are observing majorly this happens when we are running optimization on tables for databases.
This is intermittent.
What can be done for minimizing the issue ?