MySQL Table getting crashed

[BeenaBhat]

Hi,

We are using a MYSQL table for storing User Click information. The table grows obviously, as the number of hits to our site increases. However, often, this table crashes and we are not able to get any information from the table. The error number is 144 and the message is "Can't open .MYI file". The only solution is that, we should repair the table. We are using mysql 4.0.20d.

Here is the table schema.

USCLKPF (
UKINTN INT (10),
UKSSNO CHAR (40),
UKSRVN CHAR (20),
UKURL CHAR (100),
UKBRND CHAR (2),
UKSTY CHAR (8),
UKSIZ CHAR (3),
UKSHP CHAR (3),
UKCLR CHAR (3),
UKDATE INT (8),
UKTIME INT (8),
UKACT CHAR (30),
UKRMK CHAR (50),
UKFLG CHAR (1),
UKSHFL CHAR (1))

Can anyone give some suggestion inorder to prevent table crash? Thanking you well in advance,

Regards,
Beena Bhat

 

[Bastien]

Certainly not enough info to guess why the table crashes.

What is the application that is filling in the data?

Is the sql compatible with the table structure?

Do you use the appropriate sql (ie using transactional sql with tranactional tables)?

Have you turned on General Query Loging to see if there is a certain statement that is crashing the db?

Have you checked the webserver log files to see if it is a certain ip or a certain time the crash happens?

Why use char instead of varchar for datatypes?

Which db engine are you using? MyIsam? InnoDB?

Bastien

Cat, the other other white meat

 

[BeenaBhat]

Hello Bastien,

I thank you for your interest.

It is a web application, where, we are trying to capture user visited pages. So, for
each page visited, we are inserting a record into the table.

Here is a sample SQL
INSERT INTO USCLKPF ( UKINTN, UKSRVN, UKURL, UKBRND, UKSTY, UKSIZ, UKSHP, UKCLR, UKDATE,
UKTIME, UKACT, UKRMK, UKFLG, UKSSNO) VALUES ( 19,'BWHome','BWHome', ' ','7171', ' ', ' ',
' ', 20030606, 120000, ' ',' ',' ', '100001'), generated in our application. I don't think,
we do require transactions, as we insert a single record into the table, for each visited page.

We started mysqld with --log option in the TEST environment and it keeps writing all
insert statements to the log file. How to turn on General Query Logging? Will it have
some overhead on PRODUCTION environment?

In webserver log files we do not get any relevant info.

We have used char instead of varchar because we happened to port it from AS400 db2.

I assume that, by default, it makes use of MyIsam.

Regards,
Beena

 

[Bastien]

Try reading the manual...Here is the relevant page

Bastien

Cat, the other other white meat