MySQL Bugs: #33283: mysql crash unregularly after upgrade table strucat and stored procedure

Bug #33283	mysql crash unregularly after upgrade table strucat and stored procedure
Submitted:	17 Dec 2007 8:38	Modified:	30 Apr 2012 12:55
Reporter:	JinRong Ye	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.0.27/5.0.45	OS:	Linux (RedHat AS4U4/RedHat AS4U5)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any
Tags:	crash, innodb, stored procedure, unique key

Description:
after i upgraded my table struct(innodb tables),added a unique key cross two columns;and upgraded my stored procedure;server crashed unregularly,the error log such as:

071130 12:01:01  InnoDB: Error: cannot allocate 999424 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 2319029454 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
071130 12:02:01  InnoDB: We now intentionally generate a seg fault so that
InnoDB: on Linux we get a stack trace.
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=134217728
read_buffer_size=2093056
max_used_connections=47
max_connections=100
threads_connected=14
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1154671 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=(nil)
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...
Cannot determine thread, fp=0x2adb3f2c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x817e950
0x8379b72
0x8379bac
0x837e62c
0x834e70c
0x8295f95
0xaa3371
0xa0dffe
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trac
e. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
071130 12:02:02  mysqld restarted
071130 12:02:02  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
071130 12:02:02  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 11 3953468642
............
InnoDB: 4 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 109 row operations to undo
InnoDB: Trx id counter is 0 145490688
071130 12:08:04  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 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
071130 12:09:43  InnoDB: Started; log sequence number 12 549520232
InnoDB: Starting in background the rollback of uncommitted transactions
071130 12:09:43  InnoDB: Rolling back trx with id 0 145490389, 12 rows to undo

InnoDB: Rolling back of trx id 0 145490389 completed
071130 12:09:43  InnoDB: Rolling back trx with id 0 145490383, 60 rows to undo

InnoDB: Rolling back of trx id 0 145490383 completed
071130 12:09:43  InnoDB: Rolling back trx with id 0 145490378, 36 rows to undo

InnoDB: Rolling back of trx id 0 145490378 completed
071130 12:09:43  InnoDB: Rolling back trx with id 0 145490373, 1 rows to undo

InnoDB: Rolling back of trx id 0 145490373 completed
071130 12:09:43  InnoDB: Rollback of non-prepared transactions completed
071130 12:09:43 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.27-standard-log'  socket: '/home/mysql/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)
071130 12:09:46 [ERROR] /usr/sbin/mysqld: Table './mysql/proc' is marked as crashed and should be repaired
071130 12:09:46 [Warning] Checking table:   './mysql/proc'
071130 12:09:46 [ERROR] /usr/sbin/mysqld: Table './tlbbdb/t_global' is marked as crashed and should be repaired
071130 12:09:46 [Warning] Checking table:   './tlbbdb/t_global'

more error logs and the old , new table strucate / stored procedure are atteched.

How to repeat:
Not quite sure

server crash error log

Attachment: error.log (application/octet-stream, text), 259.76 KiB.

table struct and stored procedure

Attachment: table_struct_and_stored_procedure.txt (text/plain), 9.40 KiB.

Ye,

please monitor with SHOW INNODB STATUS the total memory allocated by InnoDB.

Can you see a memory leak, that is, does the memory consumption grow all the time?

You are apparently hitting a 2 GB limit on memory space.

Please monitor with the Linux 'top' the virtual memory size of the mysqld process. Does it grow constantly, indicating a memory leak?

Regards,

Heikki

hi,Heikki 

yes,the momery allocated by mysqld grow all the time according to result of SHOW ENGINE INNODB STATUS.
and the max value of the memor allocated by mysqld could be:

Total memory allocated 2319012948; in additional pool allocated 6185600

or

Total memory allocated 2319029454; in additional pool allocated 6324480

Ye,

please post your my.cnf.

Do you have many tables (like thousands)?

Regards,

Heikki

"my.cnf" and "show global status" and "show tables"

Attachment: table_struct_and_stored_procedure2.txt (text/plain), 30.28 KiB.

You have configured 2 GB = 2147483648 bytes of buffer pool. The memory gets exhausted when InnoDB has allocated about 150 MB on top of that.

Is the computer 32-bit or 64-bit?

How much physical RAM do you have?

It looks like that you are simply allocating too much memory for InnoDB's buffer pool, and you run out of memory. You can try setting:

innodb_buffer_pool_size=1800M

in my.cnf. Maybe the memory suffices then?

Regards,

Heikki

hi,Heikki

[@103_tc_10.11.54.126_CNC ~]# uname -a
Linux 103_tc_10.11.54.126_CNC 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:27:17 EDT 2006 i686 i686 i386 GNU/Linux

[@103_tc_10.11.54.126_CNC ~]# cat /proc/meminfo 
MemTotal:      4149144 kB

and i had tried to set innodb_buffer_pool_size=1800M,but it was not help :(

thanks much

Do I interpret right that you are running a 32-bit computer? Then the maximum process address space may be 2 GB or slightly more.

If you set the InnoDB buffer pool 1500 MB, do you still get errors? If yes, please post the error message.

--Heikki

hi,Heikki

i do a little change with the stored procedure,such as:
the old where clause:
  aid=raid and dbversion<=pdbversion;

the new where clause
  charguid=pcharguid and pos=ppos and dbversion<=pdbversion;

and the field "aid" is the primary key,this problem is solved.
is there any bug with primary key if it is used within stored procedure?

Regards,

JinrongYe

Hi!

This might be a memory leak associated with stored procedures.

Can you make a test table and a stored procedure that causes mysqld to run out of memory? If yes, please post them here so that we can test them.

Regards,

Heikki

hi,Heikki
i had uploaded the table struct and test table data to ftp.mysql.com/pub/upload/bug_33283_t_iteminfo_080123.tar.gz,check it please,thanks!

Setting not a bug here.  Probably a different query plan and join buffer or sort buffer are used with different versions of the SP.  solution: reduce mysqld memory footprint or get 64-bit build with more ram.