Mysqld 3.23.56 crash on concurrent ALTER TABLE and FLUSH TABLE

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:

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,

Fred.

--
Fred van Engen XB Networks B.V.
email: fred.van.engen@xbn.nl Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Mysqld 3.23.56 crash on concurrent ALTER TABLE and FLUSH TABLE

HI!

We need the exact tables and exact query that crashes MySQL server.

We can not fix a bug by having a broad or even highly detailed
description of the scenario.

We truly need a detailed and fully repeatable test case.

Also, there were some MERGE bugs fixed between 3.23.56 and .58.

--

Sincerely,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus

Want to swim with the dolphins? (April 14-16, 2004)
http://www.mysql.com/uc2004/


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Mysqld 3.23.56 crash on concurrent ALTER TABLE and FLUSH TABLE

Hi!

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

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.

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:
>
> 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,
>
> Fred.
>
> --
> Fred van Engen XB Networks B.V.
> email: fred.van.engen@xbn.nl Televisieweg 2
> tel: +31 36 5462400 1322 AC Almere
> fax: +31 36 5462424 The Netherlands

Regards,
Sergei

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Mysqld 3.23.56 crash on concurrent ALTER TABLE and FLUSH TABLE

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

Thank you for trying. After Sinisa's response I was about to try the
same myself. I'll probably do this anyway to be sure with my setup, but
are hesitant to crash a running operational MySQL daemon. I'll probably
run a different instance for this with another datadir.


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

Finally think I found it.

I checked everything before, including the credentials and limits of the
running mysqld process. But even though all s/r/euid are mysql, the fact
that it at one time called setuid, takes away its permission to dump
core files (on Solaris at least). This is described in the manual, but I
understood it as refering only to a process that has different e/r/suid's.

Now I start it with 'su - mysql safe_mysqld' and hope to get a core file
next time.


Thanks again for your help.



Regards,

Fred.

--
Fred van Engen XB Networks B.V.
email: fred.van.engen@xbn.nl Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Mysqld 3.23.56 crash on concurrent ALTER TABLE and FLUSH TABLE

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:
> >
> > 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,
> >
> > Fred.
> >
> > --
> > Fred van Engen XB Networks B.V.
> > email: fred.van.engen@xbn.nl Televisieweg 2
> > tel: +31 36 5462400 1322 AC Almere
> > fax: +31 36 5462424 The Netherlands
>
> Regards,
> Sergei
>
> --
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Sergei Golubchik
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
> /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
> <___/ www.mysql.com
>
> --
> MySQL Bugs Mailing List
> For list archives: http://lists.mysql.com/bugs
> To unsubscribe: http://lists.mysql.com/bugs?unsub=fred@xo.nl
>

--
Fred van Engen XB Networks B.V.
email: fred.van.engen@xbn.nl Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Mysqld 3.23.56 crash on concurrent ALTER TABLE and FLUSH TABLE

Sergei,

On Thu, Jan 15, 2004 at 02:42:20PM +0100, Fred van Engen wrote:
> 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_

I put the INSERT and SELECT here because you couldn't reproduce the
crash with just FLUSH and ALTER TABLE and the above exactly reflects
the queries performed by the operational scripts.

My minimal test case has just these two queries (and the DROP below)
to let mysqld dump core:

print <<_EOT_;
CREATE TABLE IF NOT EXISTS $table(id INT NOT NULL PRIMARY KEY);
ALTER TABLE a UNION=($union);
_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.
>


Regards,

Fred.

--
Fred van Engen XB Networks B.V.
email: fred.van.engen@xbn.nl Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Mysqld 3.23.56 crash on concurrent ALTER TABLE and FLUSH TABLE

Fred,

> I reproduced it on 3.23.56 with this script:

I am indeed seeing crashes (or lockups) with your script with several=20
versions, but:
=20
> ALTER TABLE a UNION=3D($union);

This shows a problem with multiple threads altering the UNION for the=20
same MERGE table, which is not quite the problem you described. =20

If I change this to:

ALTER TABLE $basetable UNION=3D($union);

then I am unable to reproduce any problem with 3.23.58.

Can you verify whether or not your connections are actually altering the=20
same MERGE table? Also, you should try your test with the above change=20
and see if you are still seeing crashes when the connections are not in=20
fact altering the same MERGE set.

Best regards,
--=20
Dean Ellis, Support Engineer & Software Developer
MySQL AB, www.mysql.com

Want to swim with the dolphins? (April 14-16, 2004)
http://www.mysql.com/uc2004/


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

Re: Mysqld 3.23.56 crash on concurrent ALTER TABLE and FLUSH TABLE

Dean,

On Thu, Jan 15, 2004 at 10:54:15AM -0600, Dean Ellis wrote:
> > I reproduced it on 3.23.56 with this script:
>
> I am indeed seeing crashes (or lockups) with your script with several
> versions, but:
>
> > ALTER TABLE a UNION=($union);
>
> This shows a problem with multiple threads altering the UNION for the
> same MERGE table, which is not quite the problem you described.
>

Oops, my mistake. Even so, it shouldn't crash even with those queries.


> If I change this to:
>
> ALTER TABLE $basetable UNION=($union);
>
> then I am unable to reproduce any problem with 3.23.58.
>
> Can you verify whether or not your connections are actually altering the
> same MERGE table? Also, you should try your test with the above change
> and see if you are still seeing crashes when the connections are not in
> fact altering the same MERGE set.
>

With your change I am still seeing crashes within seconds. I started
with a clean database (i.e. no 'test' database), just to be sure.

Gdb shows two threads handling an ALTER TABLE for different tables:

GNU gdb 4.18
Copyright 1998 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB. Type "show warranty" for details.
This GDB was configured as "sparc-sun-solaris2.7"...
Core was generated by `/opt/mysql-3.23.56/libexec/mysqld --defaults-file=/tmp/fen/my.cnf --basedir=/op'.
Program terminated with signal 9, Killed.
Reading symbols from /usr/lib/libdl.so.1...done.
Reading symbols from /usr/lib/libpthread.so.1...done.
Reading symbols from /usr/lib/libthread.so.1...done.
Reading symbols from /usr/lib/libcrypt_i.so.1...done.
Reading symbols from /usr/lib/libgen.so.1...done.
Reading symbols from /usr/lib/libsocket.so.1...done.
Reading symbols from /usr/lib/libnsl.so.1...done.
Reading symbols from /usr/lib/libm.so.1...done.
Reading symbols from /usr/lib/libc.so.1...done.
Reading symbols from /usr/lib/libmp.so.2...done.
Reading symbols from /usr/platform/SUNW,Ultra-250/lib/libc_psr.so.1...done.
#0 0xff339968 in __sigprocmask () from /usr/lib/libthread.so.1
(gdb) info threads
14 Thread 6 (LWP 15) 0xff339968 in __sigprocmask () from /usr/lib/libthread.so.1
13 Thread 5 0xff197a78 in _lwp_sema_wait () from /usr/lib/libc.so.1
12 Thread 4 0xff197a78 in _lwp_sema_wait () from /usr/lib/libc.so.1
11 Thread 3 0xff197a78 in _lwp_sema_wait () from /usr/lib/libc.so.1
10 Thread 2 (LWP 2) 0xff197448 in _signotifywait () from /usr/lib/libc.so.1
9 Thread 1 (LWP 1) 0xff195c94 in _poll () from /usr/lib/libc.so.1
8 LWP 6 0xff197a2c in ___lwp_cond_wait () from /usr/lib/libc.so.1
7 LWP 5 0xff195020 in door_restart () from /usr/lib/libc.so.1
6 LWP 4 0xff197a78 in _lwp_sema_wait () from /usr/lib/libc.so.1
5 LWP 14 0xff197a78 in _lwp_sema_wait () from /usr/lib/libc.so.1
4 LWP 2 0xff197448 in _signotifywait () from /usr/lib/libc.so.1
3 LWP 1 0xff195c94 in _poll () from /usr/lib/libc.so.1
2 LWP 8 0xff197a78 in _lwp_sema_wait () from /usr/lib/libc.so.1
* 1 LWP 15 0xff339968 in __sigprocmask () from /usr/lib/libthread.so.1
(gdb) info stack
#0 0xff339968 in __sigprocmask () from /usr/lib/libthread.so.1
#1 0xff32f1f0 in _resetsig () from /usr/lib/libthread.so.1
#2 0xff32e93c in _sigon () from /usr/lib/libthread.so.1
#3 0xff3317b4 in _thrp_kill () from /usr/lib/libthread.so.1
#4 0x13e38c in write_core (sig=10) at stacktrace.c:220
#5 0xc1948 in handle_segfault (sig=10) at mysqld.cc:1334
#6 0xff33b928 in __sighndlr () from /usr/lib/libthread.so.1
#7
#8 0xff329b30 in pthread_cond_signal () from /usr/lib/libthread.so.1
#9 0x2cdd58 in thr_abort_locks (lock=0xbc61e0) at thr_lock.c:936
#10 0xbd380 in mysql_lock_abort (thd=0xbc4410, table=0xbcc3a8) at lock.cc:287
#11 0x1238a8 in close_cached_table (thd=0xc3d3b0, table=0xbcc3a8) at sql_table.cc:780
#12 0x125fa0 in mysql_alter_table (thd=0xc3d3b0, new_db=0xbc4c18 "test", new_name=0xc3da98 "b",
create_info=0xc3d6e0, table_list=0x0, fields=@0x0, keys=@0xc3d5e0, drop_list=@0xc3d568,
alter_list=@0xc3d578, order=0x0, drop_primary=false, handle_duplicates=DUP_ERROR) at sql_table.cc:1643
#13 0xcad2c in mysql_execute_command () at sql_parse.cc:1556
#14 0xccc20 in mysql_parse (thd=0xc3d3b0, inBuf=0xc3da70 "ALTER TABLE b UNION=(b5,b6,b7,b8)", length=12834000)
at sql_parse.cc:2386
#15 0xc8f94 in do_command (thd=0xc3d3b0) at sql_parse.cc:840
#16 0xc83bc in handle_one_connection (arg=0xc3d3bc) at sql_parse.cc:558
(gdb) thread 5
[Switching to thread 5 (LWP 14 )]
#0 0xff197a78 in _lwp_sema_wait () from /usr/lib/libc.so.1
(gdb) info stack
#0 0xff197a78 in _lwp_sema_wait () from /usr/lib/libc.so.1
#1 0xff32b04c in _park () from /usr/lib/libthread.so.1
#2 0xff329a00 in cond_wait () from /usr/lib/libthread.so.1
#3 0xff329924 in pthread_cond_wait () from /usr/lib/libthread.so.1
#4 0x2ce798 in safe_cond_wait (cond=0xbcf198, mp=0xbc61f0, file=0x343330 "thr_lock.c", line=383)
at thr_mutex.c:148
#5 0x2cca58 in wait_for_lock (wait=0xbc6240, data=0xc56410, in_wait_list=0 '\000') at thr_lock.c:383
#6 0x2cd050 in thr_lock (data=0xc56410, lock_type=TL_WRITE_ALLOW_READ) at thr_lock.c:590
#7 0x2cda38 in thr_multi_lock (data=0xc53460, count=4) at thr_lock.c:830
#8 0xbcde8 in mysql_lock_tables (thd=0xbc2c80, tables=0xc413f0, count=1) at lock.cc:101
#9 0xdc21c in open_ltable (thd=0xbc2c80, table_list=0xc413d0, lock_type=TL_WRITE_ALLOW_READ) at sql_base.cc:1448
#10 0x12495c in mysql_alter_table (thd=0xbc2c80, new_db=0xc3d340 "test", new_name=0x0, create_info=0xbc2fb0,
table_list=0xc413d0, fields=@0xbc2ec0, keys=@0xbc2eb0, drop_list=@0xbc2e38, alter_list=@0xbc2e48, order=0x0,
drop_primary=false, handle_duplicates=DUP_ERROR) at sql_table.cc:1159
#11 0xcad2c in mysql_execute_command () at sql_parse.cc:1556
#12 0xccc20 in mysql_parse (thd=0xbc2c80, inBuf=0xc41380 "ALTER TABLE a UNION=(a10,a11,a12,a13)",
length=12332448) at sql_parse.cc:2386
#13 0xc8f94 in do_command (thd=0xbc2c80) at sql_parse.cc:840
#14 0xc83bc in handle_one_connection (arg=0xbc2c8c) at sql_parse.cc:558

Here is the point where is crashed:

(gdb) thread 1
[Switching to thread 1 (LWP 15 )]
#0 0xff339968 in __sigprocmask () from /usr/lib/libthread.so.1
(gdb) up
#1 0xff32f1f0 in _resetsig () from /usr/lib/libthread.so.1
(gdb) up
#2 0xff32e93c in _sigon () from /usr/lib/libthread.so.1
(gdb) up
#3 0xff3317b4 in _thrp_kill () from /usr/lib/libthread.so.1
(gdb) up
#4 0x13e38c in write_core (sig=10) at stacktrace.c:220
220 pthread_kill(pthread_self(), sig);
Current language: auto; currently c
(gdb) up
#5 0xc1948 in handle_segfault (sig=10) at mysqld.cc:1334
1334 write_core(sig);
Current language: auto; currently c++
(gdb) up
#6 0xff33b928 in __sighndlr () from /usr/lib/libthread.so.1
(gdb) up
#7
(gdb) up
#8 0xff329b30 in pthread_cond_signal () from /usr/lib/libthread.so.1
(gdb) up
#9 0x2cdd58 in thr_abort_locks (lock=0xbc61e0) at thr_lock.c:936
936 pthread_cond_signal(data->cond);
Current language: auto; currently c
(gdb) print *data
$1 = {thread = 2408550287, next = 0x8f8f8f8f, prev = 0x8f8f8f8f, lock = 0x8f8f8f8f, cond = 0x8f8f8f8f,
type = TL_UNLOCK, thread_id = 2408550287, status_param = 0x8f8f8f8f}
(gdb)


I'm not sure that the stack dump helps much, because it is slightly
different each time. Whenever I checked the cause of the bus error, the
pattern 0x8f8f8f8f was always there though, so memory got corrupted.

Also, it always crashed in the ALTER TABLE. The other thread could be
handling another query (e.g. an INSERT when I used that in my test.pl
script).

Can I send the core dump(s) somewhere? Are they of any use for you?

The server runs rock solid apart from this. Putting a GET_LOCK and
RELEASE_LOCK around the code prevents the crash, so it really seems to
be some concurrency/locking issue.


Regards,

Fred.

--
Fred van Engen XB Networks B.V.
email: fred.van.engen@xbn.nl Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Mysqld 3.23.56 crash on concurrent ALTER TABLE and FLUSH TABLE

Fred,

> Oops, my mistake. Even so, it shouldn't crash even with those queries.

Certainly not. We are investigating it further and I will be adding a
bug report for this. Thank you for the test case!

> > ALTER TABLE $basetable UNION=($union);
> With your change I am still seeing crashes within seconds. I started
> with a clean database (i.e. no 'test' database), just to be sure.

I will test it against 3.23.56 and see if I can repeat it with that
version.

> I'm not sure that the stack dump helps much, because it is slightly
> different each time. Whenever I checked the cause of the bus error, the
> pattern 0x8f8f8f8f was always there though, so memory got corrupted.

Any information is helpful, of course.

> Can I send the core dump(s) somewhere? Are they of any use for you?

You can ftp them to ftp://support.mysql.com/pub/mysql/secret if you
like, but it may not be necessary provided we can continue to repeat
the crash.

As I can very easily repeat the problem if the threads are operating on
the same table, it should only be necessary if I still cannot cause it
to fail when they are not using the same table. Even so, it is always
possible that these are caused by the same issue.

I will add the bug report after some more testing. Again, thank you for
reporting this and for providing a good test case.

Best regards,
--
Dean Ellis, Support Engineer & Software Developer
MySQL AB, www.mysql.com

Want to swim with the dolphins? (April 14-16, 2004)
http://www.mysql.com/uc2004/


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org