[B]jcn50 wrote on Sat, 15 September 2007 12:04[/B]
Ok, don't forget to change the thread_concurrency to 1 too.
I made a boo-boo. I repaired the table and changed my script to DELETE QUICK.
Walked away, made some coffee, 20 mins later I came back and I got an email again my table got corrupted. This time it was FAST. Usually 2-3 days, but never within 20 mins of REPAIRING a table.
So I said, that’s it, I am dropping this INDEX because it’s causing too many problems…
Low and behold, I forgot to clear out my mysql error log file, so when my script ran, it emailed me because it thought the table was corrupted!!!
The key is to drink coffee first!!!
So now I have a DELETE QUICK AND I dropped the Index on the ID column.
Now if the table never gets corrupted again, I will not know which one was the actual solution!!!
When we cast request such as “update user lastactyvity=12131241234 WHERE userid in (1,2,3,4,5,6,7…)” we got error “Table ‘%-.64s’ is marked as crashed and should be repaired” and our replication is fault.
What thing can produse this error with unknown table ‘%-.64s’? How can we relieve from this bug?
Tables is on MyISAM, MySQL version is 5.0.32-Debian_7etch1
Well after a successful 3 weeks, I came to work only to
find that my table has crashed. This is what MYSQL error log
shows:
[ERROR] /usr/local/mysql/libexec/mysqld: Incorrect key file for table ‘./collector/data_day.MYI’; try to repair it
I think part of the cure is for me to separate this table
into multiple tables so that it is not being hammered so much,
part of the problem I think is that I have many types of data
in it and perhaps the scripts are all fighting for the table’s resources???
I’ll suggest a last attempt: a conversion to INNODB.
If an INNODB conversion doesn’t change anything, it’s an hardware problem (hard drive is having bad sectors/blocks).
We’ve tried everything on this. INNODB is the last (software) solution I could think of, since we’ve done our homework.
Sorry man, we didn’t reply because you need to follow this thread from the beginning to know where your errors come from…
If you think it’s a bug, update for the last version of the mySQL engine.
I’m not aware of any known “Table ‘%-.64s’” error…
Good luck!
[B]ymik wrote on Sun, 30 September 2007 16:15[/B]
Hello All.
We have some throuble with our DB.
When we cast request such as “update user lastactyvity=12131241234 WHERE userid in (1,2,3,4,5,6,7…)” we got error “Table ‘%-.64s’ is marked as crashed and should be repaired” and our replication is fault.
What thing can produse this error with unknown table ‘%-.64s’? How can we relieve from this bug?
Tables is on MyISAM, MySQL version is 5.0.32-Debian_7etch1
I'll suggest a last attempt: [URL="http://forum.mysqlperformanceblog.com/s/mv/msg/394/1824/"]a conversion to INNODB[/URL].
If an INNODB conversion doesn't change anything, it's an hardware problem (hard drive is having bad sectors/blocks).
We’ve tried everything on this. INNODB is the last (software) solution I could think of, since we’ve done our homework.
Your code is really coming in handy. I am seeing a lot of duplicate errors. But in my script the code is something like…
INSERT INTO … IF DUPLICATE THAN UPDATE…
That’s basically what I’m doing in my code and your code is pinpointing exactly that line from my code…
I am going to try one more thing before I have to convert to INNODB because I just don’t know INNODB that well…
I have begun separating the table into many tables, this in
a way will share the load of INSERTS and balance it out…
Also, I should run OPTIMIZE TABLE once a week like on a Saturday…
The DB can keep up, simply because all INSERTs are put on a waiting list, but some scripts may hang the server (waiting for the INSERT query to complete), and then timeout.
In that case you can use “INSERT DELAYED”: your scripts will not wait for the INSERT to be completed anymore, and your mySQL server will execute the INSERT query when the load will be lower.
What I meant by “overflow INSERT” is something like this: - having a table with a TEXT (or BLOB) field/column - INSERT a variable, let’s call it $in - make the mySQL query with a PHP script using $in [where it should be using substr($in,65530)] - your mySQL query crash (not the server, only the query) so your INSERT is not recorded into the DB - next / others queries are proceeded.
