News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

Corrupted mysql tables ...

Started by fractalbit, August 11, 2003, 08:23:59 PM

Previous topic - Next topic

fractalbit

I have developed a web application with php and mysql that allows users to submit wallpapers. The app seems to work fine but the tables become corrupted often. Sometimes I am able to repair them and some others I have to contact the support of the provider to restore the table for me, cause when I try to access the table via phpmyadmin it appears to be "in use" and I get the error :
Can't open file: 'wallpapers_photorating.MYI'. (errno: 145)

Also, when I am able to repair the tables through phpmyadmin I usually loose records! The last time I repaired the main table we lost 12 records (photos). As you understand this is a very stressfull situation that irrattes me and of course the users that submit the photos.

Could you tell me possible reasons for this and ways to prevent it? It is bacause of the code that I have wrote? Can I do something to improve my code? Or is this completely server and mysql related?

You can visit the app here :
http://www.dpgr.gr/index.php?page=wallpapers_index

[Unknown]

You're probably accessing the tables too often... might want to try reducing queries.

SMF, for example, automatically fixes broken tables.

-[Unknown]

fractalbit

Quote from: [Unknown] on August 11, 2003, 08:25:41 PM
SMF, for example, automatically fixes broken tables.
-[Unknown]

I forgot to add that this happened alot with the tables of the forums when we had the version yabbse 1.4.1 but seems to be fine with yabbse 1.5.4 (and smf as you say). So can you please give me some more info on how i can auto repair the tables? Or any other advice? Is there a page or something that i could read?

Thanks very much for your response and your time.

[Unknown]

How many queries does it run on a single page load?

-[Unknown]

fractalbit

The main page runs 4 queries.
1. To get the gategories of the photos
2. To get the usernames of the users that have submitted at least one photo and display them in the dropdown menu.
3. To get the data for the photos and display them.
4. The last one just counts the records of the table to properly display the links for the pages (1 | 2 |3 ... etc)

Looking again in the queries i think i could just put the categories in a php table since they dont change at all, so i could eliminate (1).

I could also improve the code for (4) since i use mysqlnumrows on the (3) query instead of using count.

But are those enough? Where can i find info about repairing tables through php?

[Unknown]

Odd... is your server being restarted often?  Windows or linux?

-[Unknown]

fractalbit

We recently moved to a faster and  more stable server that has 9 days to be restarted (info from phpinfo), which is basically from when we moved. But during this period of 9 days the tables have been corrupted 2 times. One time was the table that hold the data for the photos and repairing it we lost 12 records. The other was the table that hold the photo ratings and i had to contact the support to restore it.

[Unknown]

Hmm... that's crazy.  How often is this page accessed?

I think you have bigger problems than the code... something is causing the tables to get corrupted.  In my experience, this happens primarily if MySQL is shutdown unexpectedly.

Have you tried running a ANALYZE, OPTIMIZE, and REPAIR on all your tables?  You may even want to ANALYZE them daily.

-[Unknown]

Advertisement: