>Description:
The table is corrupted after DELETE FROM <tablename>; command:
There is one table called tmprecord in my database used as temporary table
and for serialisation of changes in another table.
It's used under following schema only:
--------------------
LOCK tmprecord as tmprecord WRITE, (locking of other table(s) possible)
insert some data into tmprecord, change some data in tmprecord
DELETE FROM tmprecord;
UNLOCK TABLE;
There are several hundreds "rounds" of operation per day.
At random intervals the tmprecord become corrupted:
mysql> select * from tmprecord;
ERROR 1030: Got error 127 from table handler
The "repair table tmprecord" repair it with warning
"Number of rows changed from 1 to 0"
The corruption happen about ten times per year only, at random
intervals, with no known dependency to load, running processes and so on.
size of tmprecord.MYI changes from 4096 to 1024 by repair,
size of tmprecord.MYD is zero and doesn't change with repair
The today's sql command sequence that broke it (the tables tmprecord,
record and change log are locked for write already, the tmprecord table is
empty):
-----------------
SELECT refnum FROM tmprecord LIMIT 1
INSERT INTO changelog VALUES(NULL,1158164, 'CHvs', 'inconsistent', 'zuzana',
UPDATE record SET obsolete='Y' WHERE refnum=1158164 AND vs=2264048227 AND
INSERT INTO tmprecord SELECT * FROM record WHERE refnum=1158164 AND record.v
UPDATE tmprecord SET splatnost='2002-12-23', EUreply=NULL, EUreplytext=NULL,
INSERT INTO record SELECT * FROM tmprecord
UPDATE changelog SET status='done' WHERE refnum=15078
DELETE FROM tmprecord
UNLOCK TABLES
UNLOCK TABLES
----------------
Note the duplication of "UNLOCK TABLE" - it's bug in my code, but it
shouldn't broke a table.
>How-To-Repeat:
I can't repeat the error, althought it occured several times per
year at random intervals.
I has copy of broken tmprecord.* table files, so anybody can analyse it.
The problem is not only on the *.54 revision - i tryed to catch the bug for
more than year and did several upgrades to actually released versions
If someone has an idea how to catch more information about a problem, let
write to me. Note, it is production environment and only place where i see
the bug (althought I use mysql on several places).
>Fix:
No advice. Sorry.
>Submitter-Id: <submitter ID>
>Originator: Dan Lukes
>Organization:
>MySQL support: none
>Synopsis: DELETE FROM ...; broke the ...
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.54 (yes)
>Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.54, for unknown-freebsd4.5
>on i386
Server version 3.23.54-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 day 2 hours 31 min 30 sec
Threads: 9 Questions: 9140 Slow queries: 85 Opens: 211 Flush tables: 2 Open
tables: 48 Queries per second avg: 0.096
>Environment:
System: FreeBSD 4.5-RELEASE FreeBSD i386
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.3 20010315 (release) [FreeBSD]
Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1208074 Jan 28 2002 /usr/lib/libc.a
lrwxrwxrwx 1 root wheel 9 Jul 18 18:00 /usr/lib/libc.so -> libc.so.4
-r--r--r-- 1 root wheel 573760 Jan 28 2002 /usr/lib/libc.so.4
Configure command: ./configure '--with-charset=czech' '--enable-assembler'
'--without-bench' '--enable-thread-safe-client' '--with-comment' '--without-docs'
Perl: This is perl, version 5.005_03 built for i386-freebsd
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
