MySQL Bugs: #48862: show procedure status

Bug #48862	show procedure status
Submitted:	18 Nov 2009 10:58	Modified:	19 Mar 2010 12:37
Reporter:	Suresh Kuna	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S1 (Critical)
Version:	5.1.40-log MySQL Community Server (GPL)	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	show procedure status or mysqldump --routines

Description:
The procedure status has a bug in the version 5.1.40 and mysqld server got restarted when we do the below.

mysql> show procedure status where Db='blog';
ERROR 2013 (HY000): Lost connection to MySQL server during query

# /usr/local/mysql/bin/mysqldump --all-databases --routines  > /mnt/suresh/alldb_core_181109.dmp
mysqldump: Couldn't execute 'SHOW PROCEDURE STATUS WHERE Db = 'blog'': Lost connection to MySQL server during query (2013)

The is the error log output :

Version: '5.1.40-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
091118  3:22:09 - mysqld got signal 11 ;
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.

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

thd: 0x12f08d80
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 = 0x45089100 thread_stack 0x40000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x2e)[0x8aa18e]
/usr/local/mysql/bin/mysqld(handle_segfault+0x322)[0x5df7f2]
/lib64/libpthread.so.0[0x2aaaaacd5540]
/usr/local/mysql/bin/mysqld(_Z9get_fieldP11st_mem_rootP5FieldP6String+0x1e)[0x6315ae]
/usr/local/mysql/bin/mysqld(_Z17store_schema_procP3THDP8st_tableS2_PKcbS4_+0x6e0)[0x6f4100]
/usr/local/mysql/bin/mysqld(_Z16fill_schema_procP3THDP10TABLE_LISTP4Item+0x1e1)[0x6f43b1]
/usr/local/mysql/bin/mysqld(_Z24get_schema_tables_resultP4JOIN23enum_schema_table_state+0x200)[0x6f29d0]
/usr/local/mysql/bin/mysqld(_ZN4JOIN4execEv+0x605)[0x658685]
/usr/local/mysql/bin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x124)[0x654a34]
/usr/local/mysql/bin/mysqld(_Z13handle_selectP3THDP6st_lexP13select_resultm+0x16c)[0x65a32c]
/usr/local/mysql/bin/mysqld[0x5ececa]
/usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x395a)[0x5f341a]
/usr/local/mysql/bin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x357)[0x5f55e7]
/usr/local/mysql/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xe93)[0x5f6483]
/usr/local/mysql/bin/mysqld(_Z10do_commandP3THD+0xe6)[0x5f6d46]
/usr/local/mysql/bin/mysqld(handle_one_connection+0x246)[0x5e96e6]
/lib64/libpthread.so.0[0x2aaaaaccd407]
/lib64/libc.so.6(clone+0x6d)[0x2aaaab88eb0d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x12f19f80 = SHOW PROCEDURE STATUS WHERE Db = 'blog'
thd->thread_id=1
thd->killed=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.
091118 03:22:09 mysqld_safe Number of processes running now: 0
091118 03:22:09 mysqld_safe mysqld restarted
091118  3:22:09 [Warning] The syntax 'for replication startup options' is deprecated and will be removed in MySQL 6.0. Please use 'CHANGE MASTER' instead.
091118  3:22:09 [Note] Plugin 'FEDERATED' is disabled.
091118  3:22:15  InnoDB: Started; log sequence number 1 873735802
091118  3:22:15 [Note] Recovering after a crash using mysql-bin
091118  3:22:15 [Note] Starting crash recovery...
091118  3:22:15 [Note] Crash recovery finished.
091118  3:22:15 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=domU-12-31-39-0A-31-61-relay-bin' to avoid this problem.
091118  3:22:15 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. The table is probably corrupted
091118  3:22:15 [ERROR] mysql.user has no `Event_priv` column at position 29
091118  3:22:15 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
091118  3:22:15 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.1.40-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

Regards,
Suresh Kuna

How to repeat:
Install the 5.1.40 mysql version and do the above mentioned statements.

Suggested fix:
It is working fine in 5.0.45, that which i have tested. Initial i thought its a memory error and after reducing all the memory related variables also, the same is repeating.

Thank you for the problem report. This command works for me on one of my databases:

77-52-1-11:5.1 openxs$ bin/mysql -uroot 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.1.42-debug Source distribution

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

mysql> show procedure status where db='test';
+------+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name  | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | p1    | PROCEDURE | root@localhost | 2009-11-17 13:59:11 | 2009-11-17 13:59:11 | DEFINER       |         | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
| test | proc1 | PROCEDURE | root@localhost | 2009-11-16 16:11:08 | 2009-11-16 16:11:08 | DEFINER       |         | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
+------+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.06 sec)

So, I have two questions to clarify:

1. Is it a new installation of 5.1.40 or you had upgraded from some newer version, 5.x.y?
2. Do you have any procedures created in this database?

Hi Valeriy,

There are no procedures created on the database, and the mysqld server 5.1.40 is newly installed and imported the dump taken from older version.

I did a mysql_upgrade of the database and it go resolved now.

Thanks
Suresh Kuna

Nice to know the problem is solved. 

These messages:

091118  3:22:15 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. The
table is probably corrupted
091118  3:22:15 [ERROR] mysql.user has no `Event_priv` column at position 29

and the fact that mysql_upgrade fixed the problem proves that you had a couple of "corrupted" system tables. If they were NOT from 5.0.x you had previously in the same data directory, and if you had NOT restored entire mysql database from 5.0.x, then we have a problem in our binaries.

What exact binaries you had installed? Please, provide download URL.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Thanks...

So, do you agree that this is not a bug?

Yep Valeriy Kravchuk. 

Thanks
Suresh Kuna

duplicate of bug #41726