Getting Corrupt MyISAM Table

"Héctor Villafuerte D." Tue, 11 Nov 2003 09:00:02 -0800

Hi all,
In the following lines I'll try to describe the situation the best I can.
Thanks in advance for your help!
Hector

-> Here's the situation:

* I have repeatedly got a corrupt table (which is pretty big, like 25M rows)
* MyISAM tables (the one with the problem is *otr_hist*)
* MySQL 4.0.16-nt
* Windows XP PRO (using NTFS, so file size shouldn't be a problem, right?)


-> Here's the table structure:
CREATE TABLE `otr_hist` (
 `tel` char(8) default NULL,
 `telefb` char(14) default NULL,
 `rutaentran` char(7) default NULL,
 `rutasalien` char(7) default NULL,
 `minutos` int(16) default NULL,
 KEY `tel` (`tel`,`telefb`,`rutaentran`,`rutasalien`,`minutos`)
) TYPE=MyISAM


-> Here's the error:
mysql> show index from otr_hist;
ERROR 1016: Can't open file: 'otr_hist.MYI'. (errno: 144)


-> Here's the python script which should be doing all the job,
I think it's pretty easy to follow, if you like you can see the
thread "Additive UPDATE":

# Operators Traffic Report (otr)
# Hector Villafuerte D.
# 20031106
#
# otr.py : v.0.1 : Prepares the monthly Operators Traffic Report

import MySQLdb, string, os db = MySQLdb.connect(host = "localhost", user = "villaf", passwd = "secret", db = "otr") cursor = db.cursor()

def update_hist(csv): # Loads new data cursor.execute("""TRUNCATE TABLE otr_new""") cursor.execute("""ALTER TABLE otr_new DISABLE KEYS""") cursor.execute("""LOAD DATA INFILE '%s' INTO TABLE otr_new FIELDS TERMINATED BY ',' """ """ENCLOSED BY '\"' IGNORE 1 LINES""" % (csv)) cursor.execute("""ALTER TABLE otr_new ENABLE KEYS""")

# Intersection between *otr_hist* & *otr_new* cursor.execute("""DROP TABLE IF EXISTS otr_tmp1""") cursor.execute("create table otr_tmp1 " "select a.tel, a.telefb, a.rutaentran, a.rutasalien, a.minutos + b.minutos as minutos " "from otr_hist as a join otr_new as b using(tel, telefb, rutaentran, rutasalien)") cursor.execute("""ALTER TABLE otr_tmp1 ADD INDEX (tel, telefb, rutaentran, rutasalien, minutos)""")

# Difference between *otr_new* & *otr_hist* cursor.execute("""DROP TABLE IF EXISTS otr_tmp2""") cursor.execute("create table otr_tmp2 " "select a.* from otr_new as a left join otr_hist as b using(tel, telefb, rutaentran, rutasalien) " "where b.minutos is null")

# Updates *otr_hist* with *otr_tmp1* cursor.execute("UPDATE otr_hist AS A, otr_tmp1 AS B " "SET A.minutos = B.minutos WHERE " "A.tel = B.tel AND A.telefb = B.telefb AND " "A.rutaentran = B.rutaentran AND A.rutasalien = B.rutasalien")

   # Updates *otr_hist* with *otr_tmp2*
   cursor.execute("""ALTER TABLE otr_hist DISABLE KEYS""")
   cursor.execute("""INSERT INTO otr_hist SELECT * FROM otr_tmp2""")
   cursor.execute("""ALTER TABLE otr_hist ENABLE KEYS""")

update_hist("c:\\\\tmp\\\\20031014.csv")
update_hist("c:\\\\tmp\\\\20031015.csv")
update_hist("c:\\\\tmp\\\\20031016.csv")
update_hist("c:\\\\tmp\\\\20031017.csv")
update_hist("c:\\\\tmp\\\\20031018_21.csv")
update_hist("c:\\\\tmp\\\\20031022.csv")
update_hist("c:\\\\tmp\\\\20031023.csv")
update_hist("c:\\\\tmp\\\\20031024.csv")
update_hist("c:\\\\tmp\\\\20031025_27.csv")
update_hist("c:\\\\tmp\\\\20031028.csv")
update_hist("c:\\\\tmp\\\\20031029.csv")
update_hist("c:\\\\tmp\\\\20031030.csv")
update_hist("c:\\\\tmp\\\\20031031.csv")
update_hist("c:\\\\tmp\\\\20031101.csv")

-> And here are the questions :) * Is this a bug in 4.0.16? * Would you suggest me another approach for this task, in order to avoid the corruption?

-> Strange stuff! I've just noticed this weird behaviour: I have cygwin so I did a *ls -l* and a *dir* in the database directory. Now see the size of *otr_hist.MYI* in *ls*! Weird, isn't it?

C:\mysql\data\otr>dir
Volume in drive C has no label.
Volume Serial Number is 4C83-B7BD
Directory of C:\mysql\data\otr
11/11/2003  08:45 AM    <DIR>          .
11/11/2003  08:45 AM    <DIR>          ..
06/11/2003  05:50 PM             8,682 otr_hist.frm
11/11/2003  01:22 AM     1,089,602,716 otr_hist.MYD
11/11/2003  01:22 AM     2,607,325,184 otr_hist.MYI
11/11/2003  08:45 AM       703,332,352 otr_hist.TMD
07/11/2003  10:13 AM             8,682 otr_new.frm
11/11/2003  12:07 AM       133,080,834 otr_new.MYD
11/11/2003  12:08 AM       113,425,408 otr_new.MYI
11/11/2003  12:15 AM             8,682 otr_tmp1.frm
11/11/2003  12:16 AM        78,052,905 otr_tmp1.MYD
11/11/2003  12:16 AM        69,991,424 otr_tmp1.MYI
11/11/2003  12:16 AM             8,682 otr_tmp2.frm
11/11/2003  12:20 AM        61,965,965 otr_tmp2.MYD
11/11/2003  12:20 AM             1,024 otr_tmp2.MYI
             13 File(s)  4,856,812,540 bytes
              2 Dir(s)   1,481,801,728 bytes free

C:\mysql\data\otr>ls -l total 4743753 -rwxrwx---+ 1 Administ SYSTEM 1089602716 Nov 11 01:22 otr_hist.MYD -rwxrwx---+ 1 Administ SYSTEM 18446744072021909504 Nov 11 01:22 otr_hist.MYI -rwxrwx---+ 1 Administ SYSTEM 704118784 Nov 11 08:45 otr_hist.TMD -rwxrwx---+ 1 Administ SYSTEM 8682 Nov 6 17:50 otr_hist.frm -rwxrwx---+ 1 Administ SYSTEM 133080834 Nov 11 00:07 otr_new.MYD -rwxrwx---+ 1 Administ SYSTEM 113425408 Nov 11 00:08 otr_new.MYI -rwxrwx---+ 1 Administ SYSTEM 8682 Nov 7 10:13 otr_new.frm -rwxrwx---+ 1 Administ SYSTEM 78052905 Nov 11 00:16 otr_tmp1.MYD -rwxrwx---+ 1 Administ SYSTEM 69991424 Nov 11 00:16 otr_tmp1.MYI -rwxrwx---+ 1 Administ SYSTEM 8682 Nov 11 00:15 otr_tmp1.frm -rwxrwx---+ 1 Administ SYSTEM 61965965 Nov 11 00:20 otr_tmp2.MYD -rwxrwx---+ 1 Administ SYSTEM 1024 Nov 11 00:20 otr_tmp2.MYI -rwxrwx---+ 1 Administ SYSTEM 8682 Nov 11 00:16 otr_tmp2.frm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Getting Corrupt MyISAM Table

Reply via email to