Solved

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

Posted on 2014-02-27
4
624 Views
Last Modified: 2014-03-01
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?
0
Comment
Question by:Robert Silver
  • 2
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39893244
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
 
LVL 2

Author Comment

by:Robert Silver
ID: 39893326
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39893358
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
 
LVL 2

Author Closing Comment

by:Robert Silver
ID: 39896958
sorry I thought this was granted to you Dan! No idea why it was not
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now