Re: MySQL 4.1 to 5.1 large scale upgrade

[Jcink Cink]

Hi Rick, thanks for the reply. I've been doing more testing and I have good faith the upgrade will go okay, it's mainly just that they're being flagged down for repair for an unknown reason is what's bothering me.

I also have about 25 other databases on this server mostly filled with random things. I've been investigating them as well.

The funny part is that even tables like this, in an entirely different database on the same server:

-- Server version: 5.1.47
-- PHP Version: 5.3.2

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


-- --------------------------------------------------------

--
-- Table structure for table `saxton_hit`
--

CREATE TABLE IF NOT EXISTS `saxton_hit` (
`count` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `saxton_hit`
--

INSERT INTO `saxton_hit` (`count`) VALUES
(920677);

-- --------------------------------------------------------

Are asking for table upgrades. This is a hit counter. It has a single row, it has *NO* indexes defined, but sure enough, run CHECK TABLE and it says the table should be repaired despite the hit counter script that went with it working just fine.

Just doesn't make sense unless I'm missing something big which is why I posted here.

About the 1.5 to 2 million tables, yes, that number is real. There are 60 to around 100 tables per hosted forum, depends on the site. Given around 28k registered, it's ~1.7 mil even at minimum so my original estimate is probably low balling it. It appears to be more than okay to have lots of tables in one database, or I wouldn't have gotten away with it so well for the past 4 years or so. :) I am currently using Linux CentOS4/RHEL4 and I'm working on an upgrade to SL6/RHEL6. Filesystem is ext3. For what it's worth: I would never recommend doing something like this with MySQL on windows, I think NTFS would fall over and die.

Anyway I would still appreciate any insight or things to check regarding this seemingly unnecessary REPAIR TABLE. I will likely be moving forward with this soon, so if it works or doesn't when everything goes live, I'll try to post back.