MySQL Bugs: #7099: mysqld crashes when accessing a large view that crosses multiple schemas

Bug #7099	mysqld crashes when accessing a large view that crosses multiple schemas
Submitted:	8 Dec 2004 10:49	Modified:	23 May 2005 10:55
Reporter:	Andrew Torry	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.02-alpha-standard-log	OS:	Linux (Suse Linux Enterprise 8 SLES8)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
    From: sriandrew@hotmail.com
    To: mysql@lists.mysql.com
    Subject: [mysqld crashes when accessing a large view that crosses multiple schemas]

    >Description:
    I have a set of 5 schemas, each with identical table definitions, using the innodb engine.
    There is a 6th schema that contains a view for each table which is a UNION ALL of the base tables in the 5 other schemas.

 
    >Submitter-Id:  <submitter ID>
    >Originator:    Andrew Torry
    >Organization:  Universal Leaf
    >MySQL support: [none]
    >Synopsis:      mysqld crashes when accessing a large view that crosses multiple schemas
    >Severity:      [ non-critical ]
    >Priority:      [ low ]
    >Category:      mysql
    >Class:         [ sw-bug ]
    >Release:       mysql-5.0.2-alpha-standard (Official MySQL-standard binary)

    >C compiler:    2.95.3
    >C++ compiler:  2.95.3
    >Environment:
    Compaq ML350, Suse SLES8, Suse SLES8
    System: Linux roclbic1 2.4.21-198-smp #1 SMP Thu Mar 11 17:56:49 UTC 2004 i686 unknown
    Architecture: i686

    Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
    GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/3.2.2/specs
    Configured with: ../configure --enable-threads=posix --prefix=/usr --with-local-prefix=/usr/local --infodir=/usr/share/info --mandir=/usr/share/man --libdir=/usr/lib --enable-languages=c,c++,f77,objc,java,ada
    --enable-libgcj --with-gxx-include-dir=/usr/include/g++ --with-slibdir=/lib --with-system-zlib --enable-shared --enable-__cxa_atexit i486-suse-linux
    Thread model: posix
    gcc version 3.2.2
    Compilation info: CC='gcc'  CFLAGS='-O2 -mcpu=pentiumpro'  CXX='gcc'  CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors'  LDFLAGS=''  ASFLAGS=''
    LIBC:
    -rwxr-xr-x    1 root     root      1325063 2003-09-30 11:36 /lib/libc.so.6
    -rw-r--r--    1 root     root     23181700 2003-09-30 02:48 /usr/lib/libc.a
    -rw-r--r--    1 root     root          178 2003-04-02 14:58 /usr/lib/libc.so
    lrwxrwxrwx    1 root     root           20 2003-06-23 13:42 /usr/lib/libc-client.so -> libc-client.so.2001a
    -rwxr-xr-x    1 root     root       735696 2002-10-21 12:26 /usr/lib/libc-client.so.2001a
    Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=com
    plex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--w
    ith-readline' '--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc'

How to repeat:
    >How-To-Repeat:
    When I use the mysql command-line client to "select count(*) from redried_production" in this 6th schema, I get mysqld to crash with a stack trace when querying my largest
    view which consists of 832093 rows.
    Using any of the smaller tables works just fine, including the next-largest view "blend_transaction" with 583086 rows.

Suggested fix:
   >Fix:
    So far, I couldn't develop a fix or workaround.
    I have tried (with no luck):-

    1. Adjusting the my.cnf parameters based on the supplied samples, from my-medium.cnf to my-innodb-heavy-4G.cnf
    2. Changing my table type from innodb to MyISAM.
    3. Trying the same task on a different machine (also a Compaq ML series) but running Suse SLES9.

How big your tables are? Could you gzip them and upload the archive to  ftp://ftp.mysql.com/pub/mysql/upload/
?

The data compresses down to 39MB. 
However, I will need to obtain special permission in order to supply real data, as it 
contains very confidential material. 
Is there something else I can do as an alternative in the meantime if I can't get the 
required permission? 
[I have emailed for a support contract quotation, the signing of which may make it 
easier to supply sample data, but this will take some time to go through the admin.]

Could you upload at least output of SHOW CREATE TABLE and CREATE VIEW statement?

I have obtained permission to supply the data. 
I have uploaded a file "redried_production.cpio.gz" which contains the data for each of 
the 5 schemas I mentioned in the bug report. 
I would like to request that once you have finished with the data, could you please 
ensure that it's destroyed. 
 
Thanks.

I have attached the file "redried_production.sql" which has the "create table" DDL and 
the file "create_bicons03.sh" which creates the VIEW. 
 
Thanks!

Thank you for feedback.
But seems cpio archive is broken because I can't extract data from cpio archive.
Could you upload the same data but only gzipped?

I have uploaded each data set individually. 
Each is named "REDRIED_PRODUCTION.data.<schema>.gz" 
where the <schema> indicates which MySQL database/schema that file is intended for. 
 
Regards, 
 
Andrew.

Verified with 5.0.3-alpha-debug-log
I was able reproduce server crash only if I run "SELECT COUNT(*) FROM REDRIED_PRODUCTION" from two opened connections simultaneously.

FYI - I have tested again with 5.03-beta and this problem no longer shows up.
This bug report may be closed.

Thank you for bugreport!