MySQL Forums
Forum List  »  Merge Storage Engine

Rename base tables problem
Posted by: Wenjie Zheng
Date: October 17, 2005 02:04PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Rename base tables problem
6881
October 17, 2005 02:04PM
4295
October 17, 2005 03:51PM
3906
October 18, 2005 10:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.