mysql tables crashing

[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!!!

How is the debugging doing?

[B]jcn50 wrote on Mon, 17 September 2007 10:06[/B]
How is the debugging doing?

So far no crash…so that is good.

Still need to finish off the error output logs if I can’t
make a successful mysql query…

But I’m starting to feel a little better now…

Don’t want to jinx anything though!!!

Let’s make a party on October the 1st if no more crash! :smiley:

[B]jcn50 wrote on Mon, 17 September 2007 18:07[/B]
Let's make a party on October the 1st if no more crash! :D

I think you’re trying to jinx me!!!

thread_concurrency makes no difference on Linux.

It is also just a hint to the scheduler on how to schedule threads - it has nothing to do with a way MySQL runs threads internally.

Now you’ve tot the bug - I’d try to get our repeatable test case and get it to bugs.mysql.com

I know there ARE MyISAM corruption bugs out where.

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

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

[B]jcn50 wrote on Thu, 04 October 2007 18:40[/B]
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…

Repairing table using MySQL console

If got the shell access then connect to MySQL server and perfrom the following steps:

use my_database;
repair table my_crashed_table;

To repair crashed MySQL table using PHPMyAdmin follow the this steps

Log on PHPMyAdmin > choose affected database > choose affected table from right pane > from With selected menu, choose “Repair table”.

More info visit: http://mysql.filerepairtool.net/blog/mysql-table-marked-crashed-repaired

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.


[URL]http://plasticobiodegradable.com/polipropileno-todo-lo-que-tienes-que-saber/[/URL]

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.


Lampu Jalan Tenaga Surya