MySQL Bugs: #26917: MySQL table crash when deleting records using temporary tables

Bug #26917	MySQL table crash when deleting records using temporary tables
Submitted:	7 Mar 2007 12:11	Modified:	27 Apr 2007 11:20
Reporter:	Laszlo KAROLYI	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.0.33-log Source distribution	OS:	Linux (Linux 2.6.19.2-grsec)
Assigned to:		CPU Architecture:	Any
Tags:	table index crash

Description:
Hello,

Since i changed kernel from 2.4 to 2.6, mysql tables are crashing when i use temporary tables to delete records. I code a forum in php, and it has a background job running at 2am. This backgound job creates temporary tables and uses them to delete records from the end of the comment table in the forum. I'll try to describe the problem, showing the php code snippet, in which you can see the runned SQL queries. They are very complex, and must be therefore carefully examined. File attached with the code and table stucture.

The forum is in hungarian language, and i had to translate field names to be understandable, so some error could appear at trying to reproduce the same table conditions, please keep in mind that.

How to repeat:
Simply reproduce the tables and try to run that script on it. Table crashes with:

myisamchk: MyISAM file crxforum/hsz_188.MYI
myisamchk: warning: Table is marked as crashed
myisamchk: error: Found 2013 keys of 2014
myisamchk: error: Keypointers and record positions doesn't match
MyISAM-table 'crxforum/hsz_188.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file crxforum/hsz_8.MYI
myisamchk: warning: Table is marked as crashed
myisamchk: error: Found 5208 keys of 5209
myisamchk: error: Keypointers and record positions doesn't match
MyISAM-table 'crxforum/hsz_8.MYI' is corrupted

Suggested fix:
Maybe some code fixing around temporary table handling with nested selects ...

Code and table structure for the bug.

Attachment: bugreport.txt (text/plain), 9.44 KiB.

Thank you for a problem report. Please, send the exact ./configure command line used. Send also the results of the following Linux commands:

uname -a
getconf GNU_LIBPTHREAD_VERSION

Configure line:

./configure --prefix=/usr --exec-prefix=/usr --libexecdir=/usr/sbin --datadir=/usr/share --localstatedir=/var/lib/mysql --includedir=/usr/include --infodir=/usr/share/info --mandir=/usr/share/man --enable-shared --enable-static --enable-thread-safe-client --enable-local-infile --with-big-tables --with-raid --with-unix-socket-path=/tmp/mysqld.sock --with-mysqld-user=mysqld --with-libwrap --with-vio --with-yassl --with-bench --with-innodb --with-isam --with-archive-storage-engine --with-csv-storage-engine --with-federated-storage-engine --with-ndbcluster --with-ndb-shm --with-embedded-privilege-control --with-charset=latin2 --with-extra-charsets=all --with-collation=latin2_hungarian_ci

getconf GNU_LIBPTHREAD_VERSION :
NPTL 0.60

Linux flix 2.6.19.2-grsec #5 Thu Mar 1 19:28:57 CET 2007 i686 GNU/Linux

Please, upgrade your Linux (glibc) first. You need something like:

openxs@suse:~> getconf GNU_LIBPTHREAD_VERSION
NPTL 2.3.4
openxs@suse:~> getconf GNU_LIBC_VERSION
glibc 2.3.4

MySQL will not work properly with NPTL 0.60 (on 2.6.x kernels).

Hello,

seems like this fixed the problem.

But Debian's last stable release has the older version of lipthread, and i had to upgrade to the testing version. So users who would like to use the newer mysql on the latest stable debian, have to upgrade their linux.

Thanks,
Laszlo KAROLYI

So, I am closing this as not a bug in MySQL's code.

Hello,

I think you have to reopen this bug, because it is still existing.

As far i could examine, tables are crashing only when there is overhead in the table, which can be fixed by an "optimize/repair table" command. If there is no overhead in the table, the background job runs correctly, with no table crash.

Regards,
Laszlo KAROLYI

Please, send your my.cnf and the results of:

free
uname -a
df -k

Linux commands.

Hello.

free:
             total       used       free     shared    buffers     cached
Mem:       1036408    1018932      17476          0       5168     307092
-/+ buffers/cache:     706672     329736
Swap:       779112     353756     425356

uname -a (still the same:):
Linux flix 2.6.19.2-grsec #5 Thu Mar 1 19:28:57 CET 2007 i686 GNU/Linux

df -k:
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda2             76153632  40656496  31628684  57% /
tmpfs                   518204         0    518204   0% /dev/shm
/dev/hdb1             76920416  30646448  42366560  42% /home/ftpw/cuccok
/dev/mapper/truecrypt0
                       1941784    717332   1224452  37% /mnt

getconf GNU_LIBPTHREAD_VERSION is now:
NPTL 2.3.6

here is grep -v "^#" /etc/my.cnf |grep -v "^$":

[client]
port    = 3306
socket  = /tmp/mysql.sock
[mysqld_safe]
socket  = /tmp/mysql.sock
nice    = 0
[mysqld]
user    = mysqld
pid-file        = /var/run/mysqld/mysqld.pid
socket  = /tmp/mysql.sock
port    = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir  = /tmp
language        = /usr/share/mysql/english
skip-external-locking
log-slow-queries = /var/log/mysql-slow.log
bind-address            = 127.0.0.1
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
query_cache_limit       = 1M
query_cache_size        = 16M
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
skip-bdb
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
[mysql]
[isamchk]
key_buffer              = 16M

Please, try to repeat with a newer version 5.0.37/5.0.38, and inform about the results.

Hi,

I think you can close the bug. I've tried it with 5.0.37, and i have no problems, weeks runned by without table crash.

Thank you,
Laszlo

This bug is not repeatable with 5.0.37 any more.