[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
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
Medium Priority
?
722 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 2000 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 35

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

650 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