My organization recently upgraded our database server from MySQL 3.23 to MySQL 4.0.17. Everything seemed to go well, however, recently I've seen the number of table crashes skyrocket!
Prior to the upgrade, I think I only saw one or two table crashes over a six month period. Now, it is daily occurrence. It only seems to happen on our higher volume tables although I don't have any specifc numbers at this point, but in the context of our system, they are accessed more than any others. I run 'check table' and sometimes, oddly, that seems to do the trick. Yes, without 'repair table'. Anyway, thought that was odd or maybe just a coincident (that the table fixed itself).
I was not part of the migration, but part of the daily administration now....any ideas why this may happen? Any remedies to fix? It has something to do with the upgrade or new hardware....just don't know what!?
I can't tell you for shure but it appears that that version was not a mature, stable version. I would recomend downloading the latest mature version (4.0.18). If that doesn't work. write back about the config files, and any odd or unusual things you are doing with hardware, and non-mysql software (OS and so on).
Running MySQL 4.0.17-standard....I belive our sys admins installed this because it was distributed with RH Advanced Server 3.0....meaning it would be stable....I think/hope. Also, using phpMyAdmin 2.5.6 with PHP 4.3.2 with Zend Engine v1.30. The tables are MyISAM.
Curious thing just happend....as I issued 'php -v' to find the PHP version, I received a number of errors about registering some of the 'mysql_...' functions. Some of which I posted below....perhpaps this is the problem? At least I have something to go on...although all of our web apps (DB intensive) are working fine?
PHP Warning: Function registration failed - duplicate name - mysql_tablename in Unknown on line 0
PHP Warning: Function registration failed - duplicate name - mysql_table_name in Unknown on line 0
PHP Warning: mysql: Unable to register functions, unable to load in Unknown on line 0
It sounds like your whole configuration is messed up, not though any one person's fault but by one simple mistake somewhere along the line. Is it possible to burn the web site to a disk and also create a sql dump of all the data and database tables, and configuration and all that good stuff? Pretty much if you could take all the data and put it on a test machine and put only the data, and site on there, I mean to say dont copy anything that has any sort of configurable settings (apache, php, RH, MySQL ...). Then test away! It sounds like you might just need a clean install of everything. Another thing to look into is that you said that you where getting high amounts of table crashes. Is this happening on the same tables everytime or does it appear to be random? Perhaps there is a pattern of some sort (everytime a page is accessed, everytime a program is run, ect.)? As much info as you can give would be nice!
Thanks for the contributions. This has actually come about after a fresh install. Anyway, there are two tables in different databases. It must be something in the PHP code when updating or insterting on these tables because I have found that the crashes occur due to these specific actions (I think). Anyone know of any quirks with the PHP/MySQL hooks between PHP 4.1 and PHP 4.3 against MySQL 3.23 and 4.0.17?