MySQL Bugs: #65384: Server gone away error occurs depending on the number of table/key relations

Bug #65384	Server gone away error occurs depending on the number of table/key relations
Submitted:	21 May 2012 16:37	Modified:	5 Apr 2013 13:00
Reporter:	Danny Foti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.5.24, 5.5.31, 5.7.1, 5.0.97, 5.1.69	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Error 2002, ERROR 2006, ERROR 2013, innodb

Description:
I created two databases that are nearly the same except for the number of tables in each database. A smaller database as 21 tables, and the larger one has 61 tables.  Each database has a number of foreign key relationships such that the tables are all connected to each other.   

After creating each database, it can be queried, etc without any issue. 

However, If the server is shutdown (for example, on fedora using, /etc/init.d/mysql.server stop or mysqladmin shutdown) and restarted again (such when the server reboots), the larger database will not be able to be queried. The smaller database does not have any issue.

The error given is 
mysql> select * from tblzbf7_3pkj_223_TableV3;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select * from tblzbf7_3pkj_223_TableV3;
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 '/tmp/mysql.sock' (2)

Log file from both success and failure are attached

Note: this has been tried on a few different versions 5.0, 5.1, and 5.5.  As well on both windows and linux machines. 
Also, no data is included in the sql script files, but the error appears regardless of data present.

How to repeat:
To repeat a success: 
1. Load mysql_no_error_create.sql (attached).  
2. Shutdown server (/etc/init.d/mysql.server stop)
3. Restart server (/etc/init.d/mysql.server start)
4. Try query (SELECT * from tbliatx_cv7i_215_Table13;)
    -- no error will occur

To repeat failure:
1 Load mysql_error_create.sql (attached).
2. Shutdown server (/etc/init.d/mysql.server stop)
3. Restart server (/etc/init.d/mysql.server start)
4. Try query (SELECT * from tblzrnt_3pme_141_ui;)
  -- error will occur

Script file to recreate the error

Attachment: mysql_error_create.sql (application/octet-stream, text), 141.18 KiB.

Log file after error created

Attachment: mysql_error.log (application/octet-stream, text), 4.79 KiB.

Script file to recreate the success

Attachment: mysql_no_error_create.sql (application/octet-stream, text), 42.37 KiB.

Log file after success

Attachment: mysql_no_error.log (application/octet-stream, text), 3.96 KiB.

If this is 32-bit mysqld, then 2GB innodb_buffer_pool_size may be too large.

Version: '5.5.24-debug-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
120521 11:03:25 mysqld_safe Number of processes running now: 0
120521 11:03:25 mysqld_safe mysqld restarted

mysqld is being killed.  Please check /var/log/messages for anything about OOM killer terminating mysqld ?

The version of mysql running is 64bit

In /var/log/messages:
    fedora kernel: [1047425.176270] mysqld[14601]: segfault at 7fc7feb57ff8 ip 0000000000a27c71 sp 00007fc7feb57ff0 error 6 in mysqld[400000+9b1000]

In /var/log/audit/audit.log:
    type=ANOM_ABEND msg=audit(1337701596.373:2470): auid=7505 uid=0 gid=0 ses=129 pid=22120 comm="mysqld" reason="memory violation" sig=11

I ran mysqld --debug=d,info,error,query,general,where:O,/tmp/mysqld.trace and tried a similar query as before and got with trace:
    dispatch_command: query: select * from tblzbf7_3pkj_223_TableV7
st_select_lex::add_item_to_list: info: Item: 0x7f2840004cf8
mysql_execute_command: info: derived: 0  view: 0
assign_new_table_id: info: table_id=33
open_binary_frm: info: default_part_db_type = 0
intern_plugin_lock: info: thd: 0x9b01a40, plugin: "InnoDB", ref_count: 1
intern_plugin_lock: info: thd: 0x9b01a40, plugin: "InnoDB", ref_count: 2
open_binary_frm: info: extra segment size is 34 bytes
intern_plugin_lock: info: thd: 0x9b01a40, plugin: "InnoDB", ref_count: 3
open_binary_frm: info: Found format section
open_binary_frm: info: format_section_length: 18, format_section_flags: 0
open_binary_frm: info: tablespace: '<null>'
open_binary_frm: info: i_count: 0  i_parts: 0  index: 5  n_length: 55  int_length: 0  com_length: 0

Lowering the buffer_pool_size has no effect on the error

You mentioned it was repeatable on Windows too right?. I couldn't repeat on Windows 7 Ultimate 64-bit 8 GB RAM. Please provide your my.ini file. Thanks.

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >exit
Bye

D:\dbs>net stop mysqld55
The MySQLD55 service is stopping.
The MySQLD55 service was stopped successfully.

D:\dbs>net start mysqld55
The MySQLD55 service is starting.
The MySQLD55 service was started successfully.

D:\dbs>55

D:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.25 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use xmt
Database changed
mysql 5.5 >SELECT * from tblzrnt_3pme_141_ui;
Empty set (0.20 sec)

Sorry, I thought it was on windows too, but the colleague of mine who also tried it was using a linux virtual machine on a windows desktop.  I misunderstood.  

However, the problem still persists in a linux environment

I wonder about all these foreign keys. they are opened in a recursive fashion.
Can you try some experiment?

1) set   thread_stack=3M  in my.cnf
2) always use "mysql -A -uroot" when logging in (to avoid auto-complete scanning all tables).

Does it crash then?  I wonder is it possible to run mysqld in gdb and get a "bt full" output if it crashes?

By setting the thread_stack=3M that solved the problem.  Thanks that is great!

The actual database I am using has a factor of 10 more tables and foreign keys as well as over a terabyte of data.  Do you think the thread_stack at this level will be high enough?

This should be fixed, perhaps more testing is needed.

http://bugs.mysql.com/bug.php?id=54582
(stack overflow when opening many tables linked with foreign keys at once)

Thank you for the feedback.

I can not repeat described behavior as well.

Bug #54582 was fixed in version 5.5.7. Which exact minor version do you use?

If you can see it in current version 5.5.24, please, provide full configuration file.

I know for certain this occurred in 5.1.61 and 5.5.24.  However if you read above, the error was fixed by increasing the thread_stack.

However, some sort of warning flag or exception would be nice

Thank you for the feedback.

Please provide your configuration file.

If you have troubles in sending it, output of SHOW GLOBAL VARIABLES will work as well.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

key_buffer=512M
wait_timeout=1000
max_allowed_packet=64MB

innodb_file_per_table=1
innodb_flush_log_at_trx_commit=1
innodb_buffer_pool_size=1G
innodb_log_file_size=512M
innodb_lock_wait_timeout=540
innodb_log_buffer_size=10M
innodb_fast_shutdown=0
innodb_log_files_in_group=3

thread_stack=3M

#innodb_force_recovery=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Thank you for the feedback.

I still can not repeat the issue.

Do you see error with script provided or do you have some data in tables?

I experience the error with just the tables and schema. No data is necessary.  I do not have the problem with the current configuration file.  If you remove the thread_stack variable and use the default value it will not work

Thank you for the feedback.

I removed thread_stack variable. Will think about what also can affect this.

Interesting "Log file after error created" does not contain information about crash. Is it correct file? Does `ps -ef | grep mysqld` show anything?

By removing that the error should occur

When I run ps -ef | grep mysqld Nothing changes between when the server is working and I can query the database and when the error occurs. 
 
However, when the database is corrupted and the error is occurring and I try to open up the mysql client to query the database, the process id file mysql.pid changes and mysqld has a new process id.  This does not happen with I open up a database that does not have this error because of the thread_stack variable change

Thank you for the feedback.

I finally repeated this issue. Problem was it was not repeatable with default thread_stack_size for me.

To repeat:

1. ./mtr --start innodb --mysqld=--thread_stack=131072 &
2. ../client/mysql -uroot -h 127.0.0.1 -P13000 test <mysql_error_create.sql 
3. ../client/mysqladmin shutdown -uroot -h 127.0.0.1 -P13000 
4. ./mtr --start-dirty innodb --mysqld=--thread_stack=131072 &
5. $ ../client/mysql -uroot -h 127.0.0.1 -P13000 test 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.1-m11-debug-log

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

worker[1] Server [mysqld.1 - pid: 8668, winpid: 8668, exit: 256] died
mysql-test-run: *** ERROR: Test suite aborted
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
ERROR: 
Can't connect to the server

Error log does not contain any information about shutdown.

Added changelog entry for 5.1.70, 5.5.32, 5.6.12, 5.7.2.

"When tables are linked by foreign key constraints, loading one table opens
other linked tables recursively. When there are numerous linked tables,
this may lead to a thread stack overflow causing the server to exit. This
fix modifies the "DICT_FK_MAX_RECURSIVE_LOAD" macro, reducing the number
of tables that can be opened recursively from 250 to 33. This reduction
does not affect InnoDB functionality. Linked tables will eventually load."

Revised changelog entry:

"When tables are linked by foreign key constraints, loading one table would
open other linked tables recursively. When numerous tables are linked by
foreign key constraints, this would sometimes lead to a thread stack
overflow causing the server to exit. Tables linked by foreign key
constraints are now loaded iteratively. Cascade operations, which were
also performed in a recursive manner, are now performed iteratively using
an explicit stack."