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
677 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 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 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
when to use sequences in mysql 4 43
Complex MySQL Query 2 46
MYSQL responding very slow 3 63
issue with DB import 1 48
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 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