MySQL Bugs: #11526: MySQL crash on multi-table updates.

Bug #11526	MySQL crash on multi-table updates.
Submitted:	23 Jun 2005 10:57	Modified:	4 Dec 2005 14:25
Reporter:	Dimitrij HIlt	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.12-standard-log	OS:	Linux (Debian/Linux 2.4.26)
Assigned to:		CPU Architecture:	Any

Description:
Similar to bug 9670.
Mysqld dies with signal 11 on multi table update.

Errorlog:
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=16777216
read_buffer_size=131072
max_used_connections=126
max_connections=500
threads_connected=80
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 336380 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0xa6c2a048
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=0xbf5bee48, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x808b7a3
0x82e0a48
0x80881b5
0x8087c48
0x80b6507
0x80d70fd
0x80d74ed
0x809cf44
0x809fdef
0x809a2ce
0x8099c98
0x8099367
0x82de1fc
0x8307b8a
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
 trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x9593f70 = UPDATE
                        T1 inner join T2 on (T2.Child = T1.Id)
                  SET
                        T1.Status = 2,
                        T1.Fehlermeldung='Fehler               

Kiste ist tot Jim',
                        T1.Zeitpunkt=UNIX_TIMESTAMP(),
                        T1.GetestetWer = '0',
                        T1.GetestetIp = INET_ATON('127.0.0.1')
                  WHERE
                        T2.Kaputt=0 AND
                        T1.Id=3797
thd->thread_id=12379405
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.
050623  3:34:56  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.

Stack trace resolve:
0x808b7a3 handle_segfault + 423
0x82e0a48 pthread_sighandler + 184
0x80881b5 get_lock_data__FP3THDPP8st_tableUibT1 + 57
0x8087c48 mysql_lock_tables__FP3THDPP8st_tableUib + 360
0x80b6507 lock_tables__FP3THDP13st_table_listUi + 103
0x80d70fd mysql_multi_update_lock__FP3THDP13st_table_listPt4List1Z4ItemP13st_sel
ect_lex + 121
0x80d74ed mysql_multi_update__FP3THDP13st_table_listPt4List1Z4ItemT2P4ItemUl15en
um_duplicatesbP18st_select_lex_unitP13st_select_lex + 65
0x809cf44 mysql_execute_command__FP3THD + 7488
0x809fdef mysql_parse__FP3THDPcUi + 207
0x809a2ce dispatch_command__F19enum_server_commandP3THDPcUi + 1578
0x8099c98 do_command__FP3THD + 188
0x8099367 handle_one_connection + 615
0x82de1fc pthread_start_thread + 220
0x8307b8a thread_start + 4

How to repeat:
Difficult. It comes not on every update. Seems to be a race condition.

Hi. Could you provide the table structure that we can try to reproduce this bug?

Hi,

please provide your mailadress. I will send this information to you direct.

Dimi

Dimirij,

Upload info to our ftp and let us know the file name:
ftp://ftp.mysql.com/pub/mysql/upload/

This file will be visible only to MySQL developers.

Hello Victoria,

filename is mysql_error.dump and provides output from 'mysqldump -d' and erro_log with really names from tables.

Dimi

Update:

It happens only if OPTIMIZE TABLE locks table:

mysqlcheck --optimize --all-databases --silent
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query 
when executing 'OPTIMIZE TABLE ... '

Dimi

Dimitrij,

MySQL crashes on UPDATE query that involves tables T1 and T2, but I don't see structure of these two tables in the dump.

Victoria,

see error_log from mysqld ( was in upload ). T1 and T2 are anonymized table names. I'v provided full error message to you with DB structure.

Dimitrij

sorry I don't see appropriate table definitions. For example there is no table with 'GetestetWer' field.

Hi,

my bad. I will putt new error log ( we have crash every night ) on your frp server. Filename is mysql_err_bug_id_11526.log and nothing is anonimyzed.

Regards,

Dimi

Dimitrij,

I had not found your new error log on the ftp. Please, upload it as a private file for this bug report using File tab.

Have you tried to repeat the described behaviour using newer versions of MySQL? 4.1.14, for example? How many rows are there in tables you are updating?

Hi,

please use a new files uplloaded as privat file.

OK, we got the structure in the private files. But I still do not understand, what table is T1, and what is T2. There is no table with Child column in your dump.

So, please, provide (in a private file, if you want) the REAL update statement that fails. Some ideas on rows in your tables are welcomed too (how many, sample rows, in private files, if you want).

Please, try also to repeat this behaviour on a newer version of MySQL. 4.1.14 or just wait for 4.1.15 - should be released soon.

Hi Valeriy,

see please bad_query ( privat file ). 

Regrads,

Dimi

I had created all tables according to your file and pertformed a query you posted, on empty tables. It works OK on newest 4.1.16-BK (see the private file posted). No crashes.

So, please, provide some information about the real data (sample rows for the tables, or number of rows in each, at least). Please, test also on newer version of MySQL. 4.1.14 is available for a long time already. Inform about the results.

Your my.cnf settings and harware specifications (amount of RAM) may be of some use too.

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

Hi Valeriy,

sorry for bi delay. We have lot work here. I will provide now needed Information, but i can't update to new mysql version, because this system ist in production and we will update to mysql 5 asap.

Hardware:
CPU: 2 x Intel(R) Xeon(TM) CPU 2.80GHz ( HT enabled )
RAM: 2 GB

Wich settings from my.cnf are interesting for you?

Database things ( sample rows and rowcount ) will be provided in privat file database.txt

Regards,

Dimi

Thank you for the additional information. 

As for the my.cnf parameters, please, upload the whole file, as private, if you want. Those query_cache_% may be interesting, or others (to be able to ask more questions), if any non-default.

Hi Valeriy,

see all settings in my.cnf ( allraedy uploaded ).

Dimi

Dimitrij,

I've just reread your comments in http://bugs.mysql.com/bug.php?id=9670. Please, confirm clearly, are you getting this crashes with latest 4.0.26 and 4.1.15 versions? If no, then I believe the underlying problem is already fixed by Heikki.

If the crash is still obviously repeatable, please, try to repeat it without the query cache enabled (you have it 30M in size in your my.cnf). I want to check if this crash has something to do with query cache.

A more precise description of the steps that leads to crash will be also useful. Is it OPTIMIZE TABLE releated, simple UPDATE related, or multi-table UPDATE related (as I understood from your last comments here)?

Hi Valeriy,

it is for me unpossible to test with 4.0.26, because i will not downgrade my database. Next i will test is the new 5.X Version. We will update to 5.X ASAP.

If it is Ok by your tests with new 4.1.16, then this Bug is realy fixed by Heikki.

The more precise description ist, if "OPTIMIZE TABLE" is running and the multitable update ( see my logfile ) is running, the database dies with sig11 ( see logfile for errormessage ).

Regards,

Dimi