Rename base tables problem
Hello,
--- System spec -----
mysql : Ver 14.7 Distrib 4.1.14, for pc-linux-gnu (i686)
system : RedHat Linux 2.4.21-32.0.1.EL
-----------------------------------
I had four identical base tables: T1 T2 T3 and T4
--- create base table T1 (same for T2, T3 and T4)---
CREATE TABLE `T1` (
`DAY_TIME` datetime NOT NULL default '0000-00-00 00:00:00',
`HOST` varchar(50) NOT NULL default '',
`PROXY_IP` varchar(20) NOT NULL default '',
`STATE` varchar(20) NOT NULL default '',
`USER` varchar(70) NOT NULL default '',
`USER_DOMAIN` varchar(50) NOT NULL default '',
`HEADERS` varchar(50) NOT NULL default '',
`BODY` int(11) NOT NULL default '0',
`TIME` int(11) NOT NULL default '0',
KEY `USER_DOMAIN` (`USER_DOMAIN`),
KEY `USER` (`USER`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
T1 and T2 had one (1) row each and T3 and T4 had 100,000 rows each
I created a merge table M based on T1 and T2.
--- create merge table M ---
CREATE TABLE `M` (
`DAY_TIME` datetime NOT NULL default '0000-00-00 00:00:00',
`HOST` varchar(50) NOT NULL default '',
`PROXY_IP` varchar(20) NOT NULL default '',
`STATE` varchar(20) NOT NULL default '',
`USER` varchar(70) NOT NULL default '',
`USER_DOMAIN` varchar(50) NOT NULL default '',
`HEADERS` varchar(50) NOT NULL default '',
`BODY` int(11) NOT NULL default '0',
`TIME` int(11) NOT NULL default '0',
KEY `USER_DOMAIN` (`USER_DOMAIN`),
KEY `USER` (`USER`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`T1`,`T2`);
There were two rows in the merge table M.
Then I dropped T1 and T2 and renamed T3 to T1 and T4 to T2.
mysql>select count(*) from T1;
it returned 1 (one)
mysql>select * from T1;
it returned 1 row
mysql>select count(*) from T2;
it returned 1 (one) too
mysql>select * from T2;
it returned 1 row
mysql>select count(*) from M;
it returned 2 (two)
mysql>select * from M;
it only returned two rows
I thought the merge table probably still pointed to the old address, so I dropped M, and created M again, this time:
mysql>select count(*) from T1;
it returned 1 (one)
mysql>select * from T1;
it returned 1 row
mysql>select count(*) from T2;
it returned 1 (one) too
mysql>select * from T2;
it returned 1 row
mysql>select count(*) from M;
it returned 2 (two)
HOWEVER,
mysql>select * from M;
it returned 100,001 rows
Then I went back and did "select count(*) from M" again, still got 2.
I also tried "select sql_no_cache count(*) from M", got 2 again.
Thanks