to better understand MERGE STORAGE ENGINE in Ver. 5.6 MySQL but can not even desc table see errors

CREATE TABLE NACountry SELECT Code, Name  FROM Country WHERE Continent = 'North
alter nacountry engine=MyIsam charset=latin1;

CREATE TABLE SACountry SELECT Code, Name  FROM Country WHERE Continent = 'South
alter nacountry engine=MyIsam charset=latin1;

America';
CREATE TABLE NorthAndSouth  (Code CHAR( 3) NOT NULL, Name CHAR( 52) NOT NULL) ENGINE = MERGE UNION = (NACountry, SACountry) charset=latin1;

select count(1) from northandsouth;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
desc northandsouth;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist


So far  no idea why this simple example from sample world  world db does not work Any ideas?
LVL 2
Robert SilverSr. Software EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dan CraciunIT ConsultantCommented:
Repeated on XAMPP, with phpmyadmin.
CREATE TABLE IF NOT EXISTS `country` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` char(3) NOT NULL,
  `name` char(52) NOT NULL,
  `continent` char(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `country`
--

INSERT INTO `country` (`id`, `code`, `name`, `continent`) VALUES
(1, 'USA', 'United States', 'North America'),
(2, 'CAN', 'Canada', 'North America'),
(3, 'BRA', 'Brasil', 'South America'),
(4, 'VEN', 'Venezuela', 'South America');

Open in new window

CREATE TABLE NACountry SELECT Code, Name  FROM Country WHERE Continent = 'North America';
ALTER TABLE  `nacountry` ENGINE = MYISAM DEFAULT CHARACTER SET latin1;

CREATE TABLE SACountry SELECT Code, Name  FROM Country WHERE Continent = 'South America';
ALTER TABLE  `sacountry` ENGINE = MYISAM DEFAULT CHARACTER SET latin1;

Open in new window

CREATE TABLE NorthAndSouth  (Code CHAR( 3) NOT NULL, Name CHAR( 52) NOT NULL) ENGINE = MERGE UNION = (NACountry, SACountry) charset=latin1;

Open in new window

select count(1) from northandsouth;
count(1)
4

desc northandsouth;
Field    Type       Null    Key    Default    Extra
Code    char(3)    NO               NULL
Name    char(52)   NO               NULL

Open in new window


HTH,
Dan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robert SilverSr. Software EngineerAuthor Commented:
Okay dan what version of MySQL ? 5.6?

Never mind  question how can adding the charset=latin1 fix things?
It does I just need to understand what was happening. I can live with I must specify the charset to use but wow the whole table is unusable with the wrong charset definition
I originally though MERGE STORAGE engine works as VIEW in that it essentially unions the
tables.
Are there a lot of production uses of MERGE? Its top uses seem to be combines of columns into a single

Are you aware of any other uses it has been said with this engine you can span tables across disks  and compressed and uncompressed tables.  Might  you have any  samples of those applications of MERGE?
0
Dan CraciunIT ConsultantCommented:
Never used it in practice.

That being said, it looks like a good idea for archiving: http://mustalikachwala.blogspot.ro/2011/08/keeping-your-database-fresh-using-merge.html

Another interesting thing is that the component tables don't need to be in the same database as the merged table, so in theory this can simplify working with different sources.

HTH,
Dan
0
Robert SilverSr. Software EngineerAuthor Commented:
sorry I thought this was granted to you Dan! No idea why it was not
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.