Re: Mysqld 3.23.56 crash on concurrent ALTER TABLE and FLUSH TABLE - Fred van Engen - com.mysql.lists.bugs

9 messages in com.mysql.lists.bugsRe: Mysqld 3.23.56 crash on concurren...

From	Sent On	Attachments
Fred van Engen	14 Jan 2004 03:00
Sinisa Milivojevic	14 Jan 2004 06:20
Sergei Golubchik	14 Jan 2004 08:38
Fred van Engen	15 Jan 2004 02:16
Fred van Engen	15 Jan 2004 05:41
Fred van Engen	15 Jan 2004 05:59
Dean Ellis	15 Jan 2004 08:53
Fred van Engen	15 Jan 2004 09:28
Dean Ellis	15 Jan 2004 10:55

Subject:	Re: Mysqld 3.23.56 crash on concurrent ALTER TABLE and FLUSH TABLE
From:	Fred van Engen (fred...@xbn.nl)
Date:	01/15/2004 05:41:58 AM
List:	com.mysql.lists.bugs

Sergei,

On Wed, Jan 14, 2004 at 05:39:09PM +0100, Sergei Golubchik wrote:

I failed to repeat this. I had two processes constantly doing FLUSH+ALTER on two diffeent merge tables. Nothing crashed.

I reproduced it on 3.23.56 with this script:

#!/usr/local/bin/perl -w

my $basetable = shift @ARGV or die 'Need base table name';

print <<_EOT_; CREATE DATABASE IF NOT EXISTS test; use test; CREATE TABLE IF NOT EXISTS ${basetable}(id INT NOT NULL PRIMARY KEY) TYPE=MERGE; _EOT_

my @union;

for (my $n=1; $n; $n++) { my $table = "$basetable$n";

push @union, $table;

my $drop = @union > 4 ? shift @union : undef;

my $union = join(',', @union);

print <<_EOT_; CREATE TABLE IF NOT EXISTS $table(id INT NOT NULL PRIMARY KEY); FLUSH TABLES; ALTER TABLE a UNION=($union); FLUSH TABLES; INSERT INTO $table(id) VALUES($n); SELECT * FROM $basetable; _EOT_

print <<_EOT_ if $drop; DROP TABLE $drop; _EOT_ }

The commands that I ran were these:

./test.pl a | /opt/mysql/bin/mysql --defaults-file=/tmp/fen/my.cnf -u root -p ./test.pl b | /opt/mysql/bin/mysql --defaults-file=/tmp/fen/my.cnf -u root -p

The server dumped core within less than 10 seconds.

Do I need to test with 3.23.58, even though there seem to be no relevant bug fixes since 3.23.56? It was the same on an earlier 3.23.xx release.

Try to generate a core dump. There's command line switch --core-file. And pay attention to limits. Check the manual - there is a chapter there about coredumps.

I've got some core dumps now. Where can I send them?

One has a thread running ALTER TABLE and another running INSERT. The other has both user threads running ALTER TABLE.

Regards,

Fred.

On Jan 14, Fred van Engen wrote:

Hi,

I'm sorry to post a case that is not fully reproducible to bugs@lists, but since mysqld has crashed on this several times, posting here seems appropriate enough.

Please find mysqlbug details at the end of this mail. We upgraded to 3.23.56 after experiencing the same problem with an earlier 3.23.xx. The problem still occurs.

Once a week, several scripts expand the UNION of their MERGE table by doing this series of SQL queries:

0. INSERTs/UPDATEs on the base table and SELECTs on the MERGE table. 1. CREATE TABLE ... 2. FLUSH TABLES ... 3. ALTER TABLE ... UNION=(...) 4. FLUSH TABLES ... 5. INSERTs/UPDATEs on the base table and SELECTs on the MERGE table.

Every few weeks, mysqld crashes at exactly the time that this runs. No other activity takes place during that time.

The situation after the crash is like this:

# for f in */*.MRG; do echo $f: `tail -1 $f`; done dhcp/#sql-174c_1b.MRG: request0402 dhcp/request.MRG: request0401 radius/#sql-174c_3a682.MRG: session0402 radius/authentication.MRG: authentication0401 radius/session.MRG: session0401

As you can see, mysqld left some temporary #sql*.MRG files that were used for the ALTER TABLE and none of the ALTER TABLE queries have completed. Also important is, that all of the new tables were created (dhcp/request0402.MYI, radius/authentication0402.MYI and radius/session0402.MYI).

Therefore, at the time of the crash, the three processes and the query (see above) that they were running was:

Table Query dhcp.request 3 radius.session 3 radius.authentication after 1 and in or after 2, possibly starting 3

So the crash seems to be the results of either:

- concurrent ALTER TABLE ... UNION=(...) queries this seems unlikely because the tables are unrelated - concurrent ALTER TABLE ... UNION=(...) and FLUSH TABLES most likely IMHO

The error log show this (we don't use InnoDB):

mysqld got signal 10; 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=16773120 record_buffer=131072 sort_buffer=524252 max_used_connections=21 max_connections=100 threads_connected=4 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 80376 K bytes of memory Hope that's ok, if not, decrease some variables in the equation

Writing a core file 040111 00:00:01 mysqld restarted Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html /opt/mysql-3.23.56/libexec/mysqld: ready for connections

The core file is nowhere to be found. I would expect it in /opt/mysql/var but is isn't there or anywhere else. Any hints on this would be welcome.

Here is my.cnf:

[client] port=3306 #socket=/tmp/mysql-3.23.sock

[mysqld] port=3306 #socket=/tmp/mysql-3.23.sock set-variable = table_cache=200 tmpdir=/misc4/tmp/ log-slow-queries set-variable = long_query_time=4 core-file

Details from mysqlbug follow below:

Release: mysql-3.23.56 (Source distribution)

Environment:

<machine, os, target, libraries (multiple lines)> System: SunOS ***** 5.7 Generic_106541-07 sun4u sparc SUNW,Ultra-250 Architecture: sun4

Some paths: /usr/local/bin/perl /usr/ccs/bin/make /usr/local/bin/gcc GCC: Reading specs from /opt/gcc/lib/gcc-lib/sparc-sun-solaris2.7/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch
-Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare
-Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer'
CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W
-Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings
-Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy
-Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti
-mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' LIBC: -rw-r--r-- 1 bin bin 1693556 Aug 26 1999 /lib/libc.a lrwxrwxrwx 1 root root 11 Jun 30 1999 /lib/libc.so ->
./libc.so.1 -rwxr-xr-x 1 bin bin 1115304 Aug 26 1999 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1693556 Aug 26 1999 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Jun 30 1999 /usr/lib/libc.so ->
./libc.so.1 -rwxr-xr-x 1 bin bin 1115304 Aug 26 1999 /usr/lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--enable-assembler'
'--with-extra-charsets=complex' '--enable-thread-safe-client' '--with-innodb'
'--with-berkeley-db' 'CFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs
-Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare
-Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer'
'CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W
-Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings
-Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy
-Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti
-mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXX=gcc' Perl: This is perl, version 5.005_03 built for sun4-solaris

Thanks for your attention.

Regards,

Regards, Sergei