MySQL Information Schema – Queries to Some Tables Kill All Connections

MySQL

Running this query:

SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS

kills all connections to the server.

If I add a condition like where table_name = 'some table' it does not.

MySQL command line client gives this error:

ERROR 2013 (HY000): Lost connection to MySQL server during query

A jdbc-connected client gives this:

The last packet successfully received from the server was 766 milliseconds ago.  The last packet sent successfully to the server was 724 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3603)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3492)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4043)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2758)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732)
    at \\.\\.\\हिñçêČάй語简�?한\\.mꆯᜫꋱꐚassert.execute(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.lꑰꋘꏇᠣpublicbreak.a(Unknown Source)
    ... 1 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3052)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3503)
    ... 11 more

Things I've tried that I thought sounded relevant based on other people's questions that seemed similar:

grant all on . / flush privileges / restart
installing a newer patch level of mysql
repairing the table (it's more than just this one, but trying to repair gives the same error)

Any ideas on how to fix this?

Best Answer

It turns out there was some corrupt index.

First, I queried my server to find the log file location: show variables like 'log_error'

Then I tail -f /path/to/error_log and ran the offending query again.

It shows up in the logs like this:

2017-07-12T18:12:26.860805Z 3 [ERROR] InnoDB: Flag mismatch in page [page id: space=124998, page number=130] index `index_some_table_on_some_column` of table `some_database`.`some_table`
2017-07-12 13:12:26 0x700000d51000  InnoDB: Assertion failure in thread 123145316274176 in file btr0btr.cc line 173
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.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
18:12:26 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.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=402653184
read_buffer_size=2097152
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1013751 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fa999103c00
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 = 700000d50e90 thread_stack 0x40000
0   mysqld                              0x00000001061493e7 my_print_stacktrace + 61
1   mysqld                              0x00000001060c9965 handle_fatal_signal + 690
2   libsystem_platform.dylib            0x00007fff8caff52a _sigtramp + 26
3   ???                                 0x0000700000d48820 0x0 + 123145316239392
4   libsystem_c.dylib                   0x00007fff90ebc6df abort + 129
5   mysqld                              0x000000010632df11 _Z23ut_dbg_assertion_failedPKcS0_m + 161
6   mysqld                              0x0000000106171693 _ZL22btr_root_fseg_validatePKhm + 0
7   mysqld                              0x0000000106171f86 _Z12btr_get_sizeP12dict_index_tmP5mtr_t + 51
8   mysqld                              0x00000001061d2467 _ZL24dict_stats_analyze_indexP12dict_index_t + 305
9   mysqld                              0x00000001061d41a9 _Z17dict_stats_updateP12dict_table_t23dict_stats_upd_option_t + 558
10  mysqld                              0x00000001061d45cf _Z17dict_stats_updateP12dict_table_t23dict_stats_upd_option_t + 1620
11  mysqld                              0x0000000106227518 _ZN11ha_innobase4openEPKcij + 1236
12  mysqld                              0x0000000105b89394 _ZN7handler7ha_openEP5TABLEPKcii + 48
13  mysqld                              0x000000010608dc6d _Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb + 3442
14  mysqld                              0x0000000105f9f886 _Z10open_tableP3THDP10TABLE_LISTP18Open_table_context + 3292
15  mysqld                              0x0000000105fa1c7b _Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy + 1185
16  mysqld                              0x0000000105fa2d9c _Z21open_tables_for_queryP3THDP10TABLE_LISTj + 69
17  mysqld                              0x000000010603a570 _ZL25fill_schema_table_by_openP3THDP11st_mem_rootbP5TABLEP15st_schema_tableP19st_mysql_lex_stringS8_P18Open_tables_backupb + 603
18  mysqld                              0x000000010603a202 _Z14get_all_tablesP3THDP10TABLE_LISTP4Item + 4161
19  mysqld                              0x0000000106040c2a _Z24get_schema_tables_resultP4JOIN23enum_schema_table_state + 721
20  mysqld                              0x000000010602a583 _ZN4JOIN14prepare_resultEv + 129
21  mysqld                              0x0000000105fc1a95 _ZN4JOIN4execEv + 229
22  mysqld                              0x000000010602a0de _Z12handle_queryP3THDP3LEXP12Query_resultyy + 558
23  mysqld                              0x0000000105ffe9cb _ZL21execute_sqlcom_selectP3THDP10TABLE_LIST + 855
24  mysqld                              0x0000000105ff94f7 _Z21mysql_execute_commandP3THDb + 1959
25  mysqld                              0x0000000105ff8361 _Z11mysql_parseP3THDP12Parser_state + 842
26  mysqld                              0x0000000105ff7291 _Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command + 5090
27  mysqld                              0x0000000105ff7e18 _Z10do_commandP3THD + 414
28  mysqld                              0x00000001060b0941 handle_connection + 432
29  mysqld                              0x000000010638013c pfs_spawn_thread + 308
30  libsystem_pthread.dylib             0x00007fff8e60a99d _pthread_body + 131
31  libsystem_pthread.dylib             0x00007fff8e60a91a _pthread_body + 0
32  libsystem_pthread.dylib             0x00007fff8e608351 thread_start + 13

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fa99912ea30): SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS
Connection ID (thread ID): 3
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.

In the interest of time, and because this happened to be an old database whose data could not have changed since the last backup I had, I just reimported its data, and that solved the issue.

Related Solutions

Mysql – How to recover data from corrupted innoDB table from a crashed theSQL server

You could try to use MySQL's CHECK TABLE and REPAIR TABLE functions (but make sure you grab a copy of the table first):

mysql -uroot -p database1
CHECK TABLE table1

If that doesn't work, try using:

CHECK TABLE table1 EXTENDED

According to the CHECK TABLE documentation:

EXTENDED is to be used only after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key. This is very unlikely if a normal check has succeeded.

If any errors are reported by either of the above, try:

REPAIR TABLE table1

If that doesn't work, you can try:

REPAIR TABLE table1 EXTENDED

Finally, if that still doesn't work, you may be able to use myisamchk while MySQL is offline - according to the documentation:

REPAIR TABLE does not implement all the options of myisamchk

MySQL – How to Restore Tables from .ibd, .frm, and mysqllogbin Files

I finally figured out and resolved my problem through a lot of trial and error. For those who do not have their original ibdata1 file, and only have their .frm and .ibd files, here's how I restored my data.

Download and install the MySQL utilities at -> http://dev.mysql.com/downloads/utilities.
Go into your command/terminal to open the MySQL utility, mysqlfrm, and use it to find the structure of your table that you need to restore. How I did that, was I cd'ed into the file location of mysqlfrm, then entered "mysqlfrm --server=user:pwd@localhost --port=3307 "path_to_.frm_file" > table_name.txt". The .txt file should be saved in the same folder as where your utilities are saved if you didn't specify where you wanted it to go.
In the text file, you'll see the CREATE TABLE statements, that include all the columns and info (basically, the original structure) of your table. Copy that CREATE statement with all that info.
In your MySQL Command, create a new database (CREATE DATABASE database_name). Make it the same name as your original database name.
Create a new table inside the new database--it doesn't have to be the same name as the folder. You can create the new table inside the command prompt, but I created my table in PhpMyAdmin, a free software tool that handles the administration of MySQL over the web. I simply clicked on the database on PhpMyAdmin, then the SQL table, and pasted the table structure from #3. (As a side note, I always received errors if I named my tables "table" in my command prompt, so try to avoid that name).
On your MySQL Command, go into your database, and enter "ALTER TABLE table_name DISCARD TABLESPACE", which will essentially remove this table's .ibd file.
Copy your original table (the table you want to restore)'s .ibd file into the newly-created table to replace the .ibd file that you just removed. Change your initial .ibd file to the newly created table's name. This will mimic the old .ibd file that you just deleted. You can find this folder in the MySQL data folder, under the newly-created database folder on your computer.
Go back to your MySQL Command, go into your database, and enter "ALTER TABLE table_Name IMPORT TABLESPACE." You'll receive a "warning" (1) error-type statement, but just ignore that.
And done! if you try to access your new table, it should contain all the data from your old table.

I hope this helped, and let me know if you have any questions or comments! Also, check out http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file for other details.