MySQL Bugs: #54720: Mysql Server crashes and automatically restart where updating temporary table

Bug #54720	Mysql Server crashes and automatically restart where updating temporary table
Submitted:	23 Jun 2010 6:44	Modified:	27 Jul 2011 6:38
Reporter:	Satyapal Garhwal	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.1.50	OS:	Linux ( RED HAT Enterprise Linux 2.6.9-55.ELsmp)
Assigned to:		CPU Architecture:	Any

Description:
Mysql Server crashes and automatically restart where updating temporary table using join with permanent table 

The log file look like this 

100623 10:00:24  InnoDB: Started; log sequence number 90 1704544268
100623 10:00:24 [Note] Event Scheduler: Loaded 0 events
100623 10:00:24 [Note] /home/user/sw/mysql-5.1.30/libexec/mysqld: ready for connections.
Version: '5.1.30-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
 len 112; hex 68f05eb3fa5856b100000000019edf380100000002000000000000000300000001000000000000000100000000000000e08b55bb00000000010000008344510700000000306576616c2e63003c000000000000000000000060e1117704000000680c00b4480000000000000000000000; asc h ^  XV        8                                  U          DQ     0eval.c <           `  w    h   H           ;TRANSACTION 0 188172220, ACTIVE 0 sec, process no 29216, OS thread id 1638427 unlock_row, thread declared inside InnoDB 495
mysql tables in use 6, locked 6
11 lock struct(s), heap size 1024, 5 row lock(s)
MySQL thread id 25611, query id 57995222 optimussso.fjdc.com 192.168.0.147 tsduser Sending data
UPDATE  fg_reconciliation_details frd,
        inv_fg_stock_register ifsr,
        inv_location_to_inventory_location_mapping iltilm,
        inv_inventory_location_master iilm,
        core_location_master clm
SET     frd.issue_quantity = ifsr.quantity
WHERE   frd.design_no = ifsr.design_no
        AND frd.grn_no = ifsr.grn_no
        AND frd.stock_status = ifsr.stock_status 
        AND frd.to_location_id = ifsr.from_inv_location_id
        -- AND frd.receipt_request_id =ifsr.receipt_request_id
        AND ifsr.design_no = IFNULL( NAME_CONST('p_design_no',NULL),ifsr.design_no)
        AND ifsr.variant_no = IFNULL( NAME_CONST('p_variant_no',NULL),ifsr.variant_no)
        AND  CASE IFNULL( NAME_CONST('p_bag_number',NULL),'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.bag_number =  NAME_CONST('p_bag_number',NULL) END
        AND  CASE IFNULL( NAME_CONST('p_internal_order_no',NULL),'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.internal_order_no =  NAME_CONST('p_internal_order_no',NULL) END
        AND  CASE IFNULL( NAME_CONST('p_external_order_no',NULL),'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.external_order_no =  NAME_CONST('p_external_order_no',NULL) END
        AND  CASE IFNULL( NAME_CONST('p_customer_code',NULL),'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.customer_code =  NAME_CONST('p_customer_code',NULL) END
        AND  CASE IFNULL( NAME_CONST('p_internal_id',NULL),'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.internal_id =  NAME_CONST('p_internal_id',NULL) END
        AND  ifsr.stock_status = IFNULL( NAME_CONST('p_item_status',NULL),ifsr.stock_status)
        AND iltilm .location_code =  NAME_CONST('p_location_code',_latin1'FJML_MFG1' COLLATE 'latin1_swedish_ci')
        AND iltilm.location_inventory_location_mapping_id = IFNULL( NAME_CONST('p_inventory_mapping_id',12) ,iltilm .location_inventory_location_mapping_id)
        AND iltilm.inventory_location_code = iilm.inventory_location_code
        AND ifsr.from_inv_location_id = iltilm.location_inventory_location_mapping_id  
        AND clm.location_code = iltilm.location_code  
        AND ifsr.trans_type = (select parameter_id FROM core_parameter_master WHERE param_code = 'INVENTORY_TRANSACTION_TYPE'
		     AND sequence_number = 2)    
        AND  DATE(ifsr.transaction_date) <=  NAME_CONST('p_date_to',_binary'2010-06-23' COLLATE 'binary')
100623 11:00:39  InnoDB: Assertion failure in thread 1638427 in file btr/btr0pcur.c line 217
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.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
100623 11:00:39 - 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.

key_buffer_size=8388600
read_buffer_size=2097152
max_used_connections=28
max_threads=50
threads_connected=12
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 213307 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0xaa31208
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 = 0xbc5ff90c thread_stack 0x30000
[0x8362b9d]
[0x80eb931]
[0x824680c]
[0x8396008]
[0x8396510]
[0x82fd924]
[0x82c5204]
[0x825390b]
[0x814f53a]
[0x814f60e]
[0x814f4db]
[0x814f60e]
[0x814f4db]
[0x814f60e]
[0x814f932]
[0x8162336]
[0x8163463]
[0x81772c4]
[0x80fde8c]
[0x821ef7e]
[0x821ed3e]
[0x822115a]
[0x821c0ac]
[0x821d5f7]
[0x80fd4eb]
[0x810200c]
[0x8102dde]
[0x8103894]
[0x80f3a65]
[0x8241145]
[0x83b560a]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xae5d5408 is an invalid pointer
thd->thread_id=25611
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.

You are running a statically-linked LinuxThreads binary on an NPTL system.
This can result in crashes on some distributions due to LT/NPTL conflicts.
You should either build a dynamically-linked binary, or force LinuxThreads
to be used with the LD_ASSUME_KERNEL environment variable. Please consult
the documentation for your distribution on how to do that.
100623 11:00:39 mysqld_safe Number of processes running now: 0
100623 11:00:39 mysqld_safe mysqld restarted
100623 11:00:39 [Warning] The syntax '--log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '--slow_query_log'/'--slow_query_log_file' instead.
100623 11:00:39 [Warning] One can only use the --user switch if running as root

InnoDB: Unable to lock ./ibdata1, error: 11

How to repeat:
its repeating every time when i execute this store procedure

The store procedure first create temporary table 
then try to update temporary table with permanent 
that time this failure and crashed happen

Attaching server log file

Attachment: optimusdb.fjdc.com.err (application/octet-stream, text), 63.88 KiB.

Thank you for the report.

But version 5.1.30 is old and many bugs were fiexed since. Please try current version 5.1.48 and if problem still exists provide dump of problem tables and definition of stored procedure.

we are using mysql 5.1.30 for production server  at this moment we cant change to 5.1.48

This is store procedure where error occurs and servere crashes

Attachment: sp_inv_fg_reconciliation_proc.sql (application/unknown, text), 14.66 KiB.

Thank you for the feedback.

Please provide dump or, at least, output of SHOW CREATE TABLE and SHOW TABLE STATUS for all tables used in stored procedure.

These are ddl and dml for table created using mysql admin utility

Attachment: table_ddl_dml.rar (application/octet-stream, text), 128.94 KiB.

these are rar file u have to extract and check

Looks like table core_parameter_master is missed in the test case.

for core_parameter master u can pur value 1 and 2  directly 
of sequence

this is ddl and dml of core parameter master

Attachment: parameter_bkup.sql (application/unknown, text), 155.22 KiB.

Thank you for the feedback.

Table core_design_details also missed. Please upload.

i have uploaded a file bug_data-54720.rar that contains back up for core design table

i have uploaded a file bug_data-54720.rar on mysql ftp server 
that contains back up for core design table

Thank you for the feedback.

I can not repeat described behavior even with version 5.1.30. Also table inv_fg_stock_register_detail is missed, but looks this table is not related.

Please send us your configuration file and example of query you use when calling stored procedure.

snding my.cnf file (configuration file )

Attachment: my.cnf (application/octet-stream, text), 7.35 KiB.

Thank you for the feedback.

I still can not repeat error. Calling procedure as:

call sp_inv_fg_reconciliation_proc('FJML_MFG1',null,'2010-10-10', '2011-02-22', null, null, null, null, null, null, null, null, null);

Looks like parameters matter. Please send us parameters which cause crash.

its crashing some time no all time when mysql process takes memory more then 2.8 gb ....

I have seen in linux using top command when mysqld process taken more then 2.8 gb memory and i call this proc that time its crashing the server if mysql process mysqld memory is less then 2.8 gb then its ok .....
Before this problem over server is going out of memory se we tunned some parameter in my.cnf file after that this crashing happens

for parameter master i already added ddl and dml file which is 

 [24 Jun 8:33] satyapal garhwal

this is ddl and dml of core parameter master

Attachment: parameter_bkup.sql

in log file u can see its showing 

InnoDB: Assertion failure in thread 1638427 in file btr/btr0pcur.c line
217
 i dont know what is this  btr/btr0pcur.c

u can see outpput of linux top command if thsis this the situation then server creashes

Attachment: top.rar (application/octet-stream, text), 65.25 KiB.

this bug is same as 39320 can any body tell in which version this is fixed ...

so which version i have to use for production

Thank you for the feedback.

Fix surely exists in version 5.1.47. Please try this version or latest 5.1.48 and inform us if it solves problem for you.

can we use mysql-5.1.48 for production server .............
it is stable GA or development version ................
i think its so risks to use in production server ...............

Thank you for the feedback.

Version 5.1.48 is GA. To decide if there is a risk to use it on production server or not is up to you, but we do not backport bug fixes, so there is no sense to test bugs in older versions anyway.

within next 10 days i will upgrade mysql-5.1.48 and for 5-10 days of testing on 
stage server i will push to production and see what will happen ....................

if we comment out innodb_locks_unsafe_for_binlog=1 this portion in my.cnf then its working fine

This could be a duplicate of bug #48192.

Please try actual version MySQL server 5.1.48.

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".

i am trying to upgrade mysql5.1.30 to 5.1.48

Thank you for the feedback.

We will wait results of your upgrade. Better to version 5.1.49 now.

upgraded to Red Hat Enterprise Linux 3 (x86, 32-bit), RPM Package
MySQL Server still facing so many new problem on production server

can u tell me
which is better using Rpm or compile from source using gcc

SORRY Mistake in typing  that is linux 4 not 3

upgraded to Red Hat Enterprise Linux 4 (x86, 32-bit), RPM Package
MySQL Server still facing so many new problem on production server

Thank you for the feedback.

Which problems do you experience now? Do you experience same problem as in the initial description?

not same because i commented innodb_locks_unsafe_for_binlog=1 this part on my.cnf 

if new version will stable on production server then i have to start these type of experiment 

Now the main problem is out of memory

Thank you for the feedback.

Please add option innodb_locks_unsafe_for_binlog to the configuration file and inform us about problem still exists in new version.

Regarding to other problems this is either support request or, if you are able to prove this is MySQL bug, you should open new report.

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".

innodb_locks_unsafe_for_binlog till now using this variable problem exist

> upgraded to Red Hat Enterprise Linux 4 (x86, 32-bit), RPM Package

Thank you for the feedback. Which version of MySQL server do you use now?

now we are using 5.1.50 rpm installation

How many users are using this procedure at the same time?

some time more then 1 .................
but mostly 1 user at a time

Do you still have this error?

yes still we are facing problem

Please try to use FLUSH LOGS between dropping temp table and recreating it.

but flush log what will do with temporary table...
it will flush only log file

closing the bug ....
since not using 5.1 version upgraded to 5.5