Hello,
I noticed a bug today in multi-table delete which corrupts my tables and
indices. In the 4.0.2 changelog in the online-manual I saw a reference to a
bugfix for multi-table deletes, but since I can't yet download a binary of
4.0.2 for windows I can't yet check if this is the fix for 'my' bug.
I haven't yet made a comprehensive testcase for the bug, but here's the
query:
DELETE document_headers FROM document_headers, document_contents
WHERE document_headers.did = document_contents.did
AND document_headers.dcid = document_contents.dcid
AND document_contents.did = %(did)s
AND document_contents.version = %(version)s
The error is:
ERROR 1034: Incorrect key file for table: 'document_headers'. Try to repair
it
Before the delete-statement the table works; after the delete it needs to be
repaired. After I repair it and retry the query, the same error returns.
The error seems to occurs only when there actually are rows to delete; when
there's nothing to delete there's no error.
In attempting to repair the table I got the following messages:
C:\mysql\data\linksdb>myisamchk document_headers
Checking MyISAM file: document_headers
Data records: 11 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed
myisamchk: warning: 1 clients is using or hasn't closed the table properly
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found 10 keys of 11
- check record links
myisamchk: error: Keypointers and record positions doesn't match
MyISAM-table 'document_headers' is corrupted
Fix it using switch "-r" or "-o"
C:\mysql\data\linksdb>myisamchk -r document_headers
- recovering (with sort) MyISAM-table 'document_headers'
Data records: 11
- Fixing index 1
- Fixing index 2
myisamchk: Error on delete of 'document_headers.MYD' (Errcode: 13)
myisamchk: error: 13 when fixing table
MyISAM-table 'document_headers' is not fixed because of errors
Try fixing it by using the --safe-recover (-o) or the --force (-f) option
C:\mysql\data\linksdb>myisamchk -o document_headers
- recovering (with keycache) MyISAM-table 'document_headers'
Data records: 11
myisamchk: error: Can't create new tempfile: 'document_headers.TMD'
MyISAM-table 'document_headers' is not fixed because of errors
Try fixing it by using the --safe-recover (-o) or the --force (-f) option
C:\mysql\data\linksdb>myisamchk -r document_headers
- recovering (with sort) MyISAM-table 'document_headers'
Data records: 11
myisamchk: error: Can't create new tempfile: 'document_headers.TMD'
MyISAM-table 'document_headers' is not fixed because of errors
Try fixing it by using the --safe-recover (-o) or the --force (-f) option
C:\mysql\data\linksdb>myisamchk -o document_headers
- recovering (with keycache) MyISAM-table 'document_headers'
Data records: 11
myisamchk: error: Can't create new tempfile: 'document_headers.TMD'
MyISAM-table 'document_headers' is not fixed because of errors
Try fixing it by using the --safe-recover (-o) or the --force (-f) option
C:\mysql\data\linksdb>myisamchk -f document_headers
Checking MyISAM file: document_headers
Data records: 11 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed and last repair failed
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check record links
- recovering (with sort) MyISAM-table 'document_headers'
Data records: 11
- Fixing index 1
- Fixing index 2
(The %(did)s and %(version)s things are python string-interpolations,
similar to %s in c printf. The python MySQLdb library fills in the proper
values. When I run the query in the mysql-client with proper values filled
in it gives the same error.)
Does this ring a bell? Is this the same problem as which is fixed in 4.0.2?
Or do you want me to submit more data on this problem?
With regards,
--Tim
---------------------------------------------------------------------
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
