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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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://…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

808 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