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:
> >         <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,
> > 
> > Fred.
> > 
> > -- 
> > Fred van Engen                              XB Networks B.V.
> > email: [email protected]                Televisieweg 2
> > tel: +31 36 5462400                         1322 AC  Almere
> > fax: +31 36 5462424                         The Netherlands
>  
> Regards,
> Sergei
> 
> -- 
>    __  ___     ___ ____  __
>   /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[email protected]>
>  / /|_/ / // /\ \/ /_/ / /__  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/[email protected]
> 

-- 
Fred van Engen                              XB Networks B.V.
email: [email protected]                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/[email protected]