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

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]