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