MySQL Forums
Forum List  »  Merge Storage Engine

upgrade from 4.1 to 5.0.1.8 - Merge Tables are not identically defined.
Posted by: Rob Burnett
Date: February 23, 2006 10:05AM

I've started to look at moving up to MySql 5. I copy my folder from 4.1 to the 5 data directory and all the tables except the merge work ok. What happens to the merge table is the file path is different so the merge work obviously work, If i change the path to the ocrrect path using notepad the merge table functions correctly. Here's the problem IF i drop the merge table and try to recreate the merge table with the following command as I always use

My tables:
weekly_charges --> merge table ---> merges weekly_charges_labor, weekly_charges_overhead.

create table weekly_charges like weekly_charges_labor

and then

ALTER TABLE weekly_charges UNION=(weekly_charges_labor,weekly_charges_overhead) TYPE=merge

I get the error:
All tables in the MERGE table are not identically defined

I've tried to repair both of the tables and then create the table and alter and get the same results.

The tables columns, types and indexes are exact. The tables work fine in 4.1.

One solution that does work is to to a show create table and recreate the table in mysql 5 and then do a select * from the old table into the newly created table for both tables then they will merge.

The problem is with this is I have an automated backupthat zips and ftp's and unzips as a static mysql site and it will not work. The grand plan is to have all of the servers runing ver 5 and this should not be a problem then.. I still have to verify that though. (zipping a ver 5 merge table(s) and unzip on another ver 5 server)

There must be a difference in the ver 5 table creation that although it will run version 4 tables there is a difference here.

I wanted to ADD a show table, maybe somthing in the definitions is the problem

CREATE TABLE `weekly_charges_overhead` (
`fn` varchar(20) default NULL,
`ln` varchar(20) default NULL,
`fy` year(4) default NULL,
`week_of` datetime default NULL,
`sd` char(2) default NULL,
`su` varchar(4) default NULL,
`cd` char(2) default NULL,
`ea` varchar(6) default NULL,
`PE` varchar(4) default NULL,
`Component` char(3) default NULL,
`Task` char(3) default NULL,
`sj` varchar(5) default NULL,
`specd` varchar(12) default NULL,
`msa` varchar(9) default NULL,
`fae` char(1) default NULL,
`ao` char(3) default NULL,
`od` char(3) default NULL,
`coe` varchar(6) default NULL,
`dlrs` float(13,2) NOT NULL default '0.00',
`hrs` float(13,1) NOT NULL default '0.0',
`wbs` varchar(15) default NULL,
`descr` varchar(100) default NULL,
`newcharge` int(11) NOT NULL default '0',
`ea5` varchar(5) default NULL,
`phase` char(1) default NULL,
`sb45category` varchar(10) default NULL,
KEY `ea` (`ea`),
KEY `week_of` (`week_of`),
KEY `od` (`od`),
KEY `cd_ea` (`cd`,`ea`),
KEY `cd_ea_sd_su` (`cd`,`ea`,`sd`,`su`),
KEY `msa` (`msa`),
KEY `cd_ea5` (`cd`,`ea5`),
KEY `ln` (`ln`),
KEY `ea5` (`ea5`),
KEY `phase` (`phase`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1






Edited 1 time(s). Last edit at 02/23/2006 10:31AM by Rob Burnett.

Options: ReplyQuote


Subject
Views
Written By
Posted
upgrade from 4.1 to 5.0.1.8 - Merge Tables are not identically defined.
6969
February 23, 2006 10:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.