mysql tables crashing

MySQL & MariaDB Other MySQL® Questions
1

Hi All,
I developed DB of one portal in mysql 5.0.26.
since last 20 days, hosted site on server win2k3. I found problem with few maximum used tables of Database. Those tables getting crashed on daily basis. daily I need to repair tables of database. I would like to fix this problem asap.

If anyone can guide me what changes I can apply in mysql configuration to stop table crashing probelm?
Or if anyone have idea how to set query log to detect problemtic queries of my php scipts.

thanks n regards.

2

What do you mean by “table crashing”??.. What kind of error do you have?..

The only way to get a mySQL daemon stalled when using scripts, is when you try to INSERT overflow data…
For example, let’s say that you have a TEXT field, but in your scripts you try to INSERT a variable, having a length > 65535 characters… So you will need to use the substr() function.

Hope this helps~.

3

My main table has been crashing as well.

I beleive it is due to a lot of INSERTS and I need
to start tracking this down.

It crashes about 2-3 a week so that’s not to bad but
really shouldn’t be happening at all…

The issue is that my table is indexed and I think
all the inserts and possibly deletes is causing the index
to be out of sync…

4
[B]allworknoplay wrote on Sat, 08 September 2007 17:56[/B]
The issue is that my table is indexed and I think all the inserts and possibly deletes is causing the index to be out of sync...

How big is your index?..
What kind of “crash error” do you have?.. I really think it has to do with some overflow INSERTs…

You can use “DELETE QUICK” instead of “DELETE”.

5

Oh and my MySQL version is 5.0.23

6

Hello All,

thanks for reply.

I am using phpMyAdmin frontend to access mysql databases.
Table crash mean when I select database and see the list of tables. In action with specific tables status displayed “in use” instead of operation command like browse/select/insert/properties…etc.

I set manual restrictions while insert records into tables. trim the contact as per field size. though atleast one table i can see daily with status “in use”.

anyone have idea can i trace the problem using error log files??
where and how can i read:
–>The Slow Query Log
–>The General Query Log

Please help me.

regards.

-Rashmi

7
[B]rashmirani wrote on Mon, 10 September 2007 05:18[/B]
anyone have idea can i trace the problem using error log files?? where and how can i read: -->The Slow Query Log -->The General Query Log

See my previous post for error logging. Apart from SHOW PROCESSLIST, you can make your own “slow query log” taking a timestamp before and after your query.

For example:
$query = “whatever you want to INSERT / DELETE”;
$time_before=date(“U”);
$result = mysql_query($query);
$time_after=date(“U”);
// let’s say you want to have all the queries that took more than a minute:
if ($time_afer-$time_before>60) error(“Slow query”,$query,LINE);

Now I’m gonna rest after I took on your job!

8

HI All,
Thanks for all reply.
let me explain my problem again here.

My site hosted on linux server before and run well.
before a month site hosted on windows server.
we are using pear class to execute query and using phpmyadmin as mysql frontend.

Problem of mysql table “in use” started after the day when site hosted on windows server. daily more than 3 tables show status “in use” frequently.

I applied required changes in my.ini file. after mysql configuration changes applied now 1 or 2 tables shows status “in use” when see table list in phpmyadmin.
Once i repair table then all run well.

now please guide me how to detect the problem area and solve this problem.

-Rashmi

9
[B]rashmirani wrote on Tue, 11 September 2007 06:32[/B]
HI now please guide me how to detect the problem area and solve this problem.

Man, you’re not into it: the fact that your tables are “in use” is because they crashed. And, like you said, you need to make a repair.
Before doing a repair, you can use CHECK TABLE and open your mySQL error_log.

Check the previous posts in this thread for error reporting and logging, otherwise you won’t find the cause. How can you find the origin of something if you only know/see the consequence?? How do you want us to help you if you only tell us that you need to repair your tables??

Update your scripts and post the error(s) you got here in this post. There is no way someone else does it for you, especially when I already wrote the code you need to add…

As allworknoplay suggested, check your read_rnd_buffer value, you may need to lower it to 2MB; same for the wait_timeout value.

:mad:

Alternatively, you can tell us:

  • what is the version your Windows OS?
  • what is the filesystem used? (FAT32 or NTFS)
  • how big is your DB?
  • how big is your indexes?
  • the brand of your HD?
10

Well it was only a matter of time my table crashed…

Here it is…

mysql> check table data_day;
±-------------------±------±---------±------------------ ------------+
| Table | Op | Msg_type | Msg_text |
±-------------------±------±---------±------------------ ------------+
| collector.data_day | check | warning | Table is marked as crashed |
| collector.data_day | check | error | Found 2434089 keys of 2434281 |
| collector.data_day | check | error | Corrupt |
±-------------------±------±---------±------------------ ------------+
3 rows in set (2.24 sec)

11

Here is the repair and error log…

mysql> repair table data_day;
±-------------------±-------±---------±---------+
| Table | Op | Msg_type | Msg_text |
±-------------------±-------±---------±---------+
| collector.data_day | repair | status | OK |
±-------------------±-------±---------±---------+
1 row in set (59.68 sec)

070912 19:40:06 [ERROR] /usr/local/mysql/libexec/mysqld: Incorrect key file for table ‘./collector/data_day.MYI’; try to repair it

12

A- No error report from the scripts’ side?
B- Can you post an extract of one row of “data_day” with full types used and auto_increment or timestamp if any? (you can remove any confidential data if any).
C- Did you use DELETE or DELETE QUICK?
D- Can you confirm that you’re using MyISAM ?
E- Are you ready to sacrifice some disk space to gain a crash-free DB?

After you answered that, we’ll see which next step you could do.

13
[B]jcn50 wrote on Wed, 12 September 2007 22:58[/B]

A- No error report from the scripts’ side?
B- Can you post an extract of one row of “data_day” with full types used and auto_increment or timestamp if any? (you can remove any confidential data if any).
C- Did you use DELETE or DELETE QUICK?
D- Can you confirm that you’re using MyISAM ?
E- Are you ready to sacrifice some disk space to gain a crash-free DB?

After you answered that, we’ll see which next step you could do.

A - I began to implement the error output into a logfile, but I haven’t had time to finish, I have over 4000 lines of code I need to go through…but rest assured it will be implemented as it is an excellent idea…

B - There is nothing too sensitive with my data so here it is in it’s full glory. This is AFTER I’ve already repaired the table.

mysql> select * from data_day limit 1;
±-----------±-----------±----------±-------±—±------ -±------±-------+
| device_id | timestamp | data_type | object | id | actual | gdata | gdata1 |
±-----------±-----------±----------±-------±—±------ -±------±-------+
| 1175214041 | 1189629601 | disk | C | | 1 | 31 | 0 |
±-----------±-----------±----------±-------±—±------ -±------±-------+
1 row in set (0.01 sec)

C - I don’t want to use the DELETE QUICK yet and the reason why, and I think you will agree, is that from a programming/troubleshooting standpoint, you want to do one method first. Find out if that has relieved or resolved the issue.

If I go with DELETE QUICK and also the BULK INSERTS, I won’t know which one was the actual fix. Since there are a lot more INSERTS, I think I want to try BULK INSERTS first…

D - Yes my tables are MYISAM.

E - If I can sleep better at night knowing my systems is crash-free, then bring it on!!!

Compared to what I’ve seen out there, others have a MYSQL DB of greater than 20GIGs and are running fine, so the fact that I am less than 1GIG, I feel it’s not so much the size of the DB causing the issue, it’s just improper or inefficient methods that I’m using to access the DB…

14

A- Ok, perfect. I bet it will be useful cool:.

B- You forgot to put me the types of your fields. You can easily have them by exporting the your table without including the data.
So I guess your only key is “device_id”, type is INT and UNSIGNED? Is “device_id” generated in an AUTO_INCREMENT style?

C- Sure, no problem. Just wanted to know if the DELETE where queued or not. So the script actually awaits for the DELETE query to finish before doing any other DELETE.

As for D and E, I have a suggestion (see my next post). :smiley:

15

OK guys,

Let’s try something.

1- Be sure that NO script is running, so that your DB is not in use.
2- We are going to convert your DB engine. You don’t have to update your scripts, as it will only change how your DB server handles the data. The SQL command is:
ALTER TABLE your_table_name TYPE=InnoDB;
If you have more than a table, you need to do this for every table. I don’t know how long the conversion will take, but you can check with SHOW PROCESSLIST when it’s done.
So, allworknoplay, let’s try:
ALTER TABLE data_day TYPE=InnoDB;
3- InnoDB is a crash-free engine. The tables are repaired automatically if a crash occurs. You can revert to myISAM again anytime. As I’m new to InnoDB, I don’t know how to tweak the options: if you want to do that, please open another topic.

16

More about MyISAM vs InnoDB here:

[URL]网站改版中
[URL]InnoDB vs. MyISAM - PHP Developers Network

17

Thanks dude, here is the description…

±----------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±----------±-------------±-----±----±--------±------+
| device_id | int(10) | NO | PRI | | |
| timestamp | int(10) | NO | PRI | | |
| data_type | varchar(32) | NO | PRI | | |
| object | varchar(120) | NO | PRI | | |
| id | varchar(10) | NO | MUL | | |
| actual | int(1) | NO | | 0 | |
| gdata | bigint(20) | NO | | 0 | |
| gdata1 | bigint(20) | NO | | 0 | |
±----------±-------------±-----±----±--------±------+

I’d like to try out some of your other suggestions first before
I do any conversion to InnoDB. I spent so much time learning all the in’s and out’s of MyISAM, I would first have to learn InnoDB too…

But the idea of crash-free is great, I had no idea that that was a built in function of InnoDB…

Plus this stuff is PRODUCTION. If I mess up at all in any which way, I am in TROUBLE!!!

18
[B]allworknoplay wrote on Fri, 14 September 2007 01:28[/B]

±----------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±----------±-------------±-----±----±--------±------+
| device_id | int(10) | NO | PRI | | |
| timestamp | int(10) | NO | PRI | | |
| data_type | varchar(32) | NO | PRI | | |
| object | varchar(120) | NO | PRI | | |
| id | varchar(10) | NO | MUL | | |
| actual | int(1) | NO | | 0 | |
| gdata | bigint(20) | NO | | 0 | |
| gdata1 | bigint(20) | NO | | 0 | |
±----------±-------------±-----±----±--------±------+

Wait! eek: As per mySQL standards, you should have only ONE primary key per table! [URL="http://dev.mysql.com/doc/refman/5.0/en/create-table.html"]http://dev.mysql.com/doc/refman/5.0/en/create-table.html[/URL] [I]"A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). [B]A table can have only one PRIMARY KEY."[/B][/I]

So you should have only one PRI, the other indexes should be noted as MUL, just like the field “id”. confused:
That’s why your table was crashing man! I think we found out! Because the “timestamp” may not be unique (as I said earlier in message #1777 in this topic), same for the “data_type” and “object” fields! So be sure that “device_id” is UNIQUE in your design! (i.e: using AUTO_INCREMENT, not being generated by your script)

If you had had the scripting error_reporting, you would have had an error like “can’t INSERT duplicate keys” or “can’t DELETE duplicate keys”. cool:

[B]allworknoplay wrote on Fri, 14 September 2007 01:28[/B]

Plus this stuff is PRODUCTION. If I mess up at all in any which way, I am in TROUBLE!!!

Well, it can't be worse than MyISAM )....at least if you design your tables & write scripts [U]properly[/U]! D
19

Well I was hoping I didn’t have to explain what the table was all about but here goes…

The first 4 columns are unique as a WHOLE when I built the table, at the end I basically said,
UNIQUE(device_id, timestamp,data_type,object) so data in all 4 columns have to be a unique mix. Below is the SHOW CREATE TABLE.

CREATE TABLE data_day (
device_id int(10) NOT NULL,
timestamp int(10) NOT NULL,
data_type varchar(32) NOT NULL,
object varchar(120) NOT NULL,
id varchar(10) NOT NULL default ‘’,
actual int(1) NOT NULL default ‘0’,
gdata bigint(20) NOT NULL default ‘0’,
gdata1 bigint(20) NOT NULL default ‘0’,
UNIQUE KEY device_id (device_id,timestamp,data_type,object),
KEY id (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Let me ask you this, say I am inserting a lot of data that is already unique so mysql has to reject them, do you think that in itself could be causing the crash?

20

You can have an INDEX that is NOT UNIQUE. But you can’t have more than ONE PRIMARY INDEX, and that PRIMARY INDEX should be UNIQUE.

UNIQUE = there is no duplicate (in the same column). You can’t consider 4 columns being unique as a whole for a PRIMARY INDEX.

I don’t know if I answered your question?..

For example, if I do:
SELECT * FROM data_day WHERE device_id = 1175214041;
it should always return 0 or 1 result, even if the other columns are different as a whole.

The idea behind a PRIMARY INDEX is that it identifies each row uniquely. Let’s imagine you have a list of prices, and your items have a bar code. The bar code is a PRIMARY INDEX because you can only have one price per bar code (even if the packaging changes, or the name of the product changes, or where it comes from changes; it doesn’t matter, because we’re talking about the same product).

What cause the crash is not the INSERT, it’s the DELETE. I don’t know how you make your query, but I guess MySQL wants to delete every row that as the same device_id (or any of the other 3 PRIMARY INDEXes being the same), resulting in a crash. When mySQL uses a PRIMARY INDEX for deleting, only ONE ROW can be deleted… whereas your table may contains more than a row with the same device_id (or more than a row with the same timestamp, etc).

Imagine that I ask you: “delete the product which has the bar code 1175214041”, then you start to search on your list “which product has the bar code 1175214041?” and you end up with more than a single result, which one would you delete? (as you can only delete one) eek: … => crash :o

UNIQUE(device_id, timestamp,data_type,object) means:

  • device_id contains no duplicate ;
  • timestamp contains no duplicate ;
  • data_type contains no duplicate ;
  • object contains no duplicate.

UNIQUE(device_id, timestamp,data_type,object) doesn’t mean: “there’s only ONE UNIQUE row having a defined: device_id AND timestamp AND data_type AND object”.