Bug #69397 Merge tables in v5.6 do not work with tables from v5.1 mysql.
Submitted: 4 Jun 2013 12:54 Modified: 23 Jul 2013 10:43
Reporter: Giles McArdell Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S2 (Serious)
Version:5.6/5.1 OS:Any (2.6.17-1.2157_FC5)
Assigned to: CPU Architecture:Any
Tags: merge

[4 Jun 2013 12:54] Giles McArdell
Description:
Merge tables in v5.6 do not work with tables from v5.1 mysql.

Problem has occurred during an upgrade from mysql 5.1.49 to 5.6.11.
Due to the number and size of tables involved a dump and reload of the data is impractical hence the tables have been raw copied.
All the tables are MyISAM.

Upon creating a merge table (on 5.6) to look at the 5.1 tables the following error is received:
"Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist"

I have used the 'ALTER TABLE .... ENGINE=MYISAM' on the 5.1 tables, but the same error occurs.
When trying a CHECK TABLE with the FOR UPGRADE option it returns OK for the 5.1 tables.
Running 'SHOW TABLE STATUS' shows the tables version as 10, the same as tables created under v5.6.

A very few of the merged tables do seem to work, I have studied the column formats and the table definitions carefully but have yet to spot any specific differences between the ones that work compared to those that do not.

Here is an example definition of a table which cannot be merged:
CREATE TABLE `tps_FX_Rates` (
  `ProviderID` INT(10) NOT NULL,
  `From_CurrencyID` VARCHAR(4) NOT NULL,
  `To_CurrencyID` VARCHAR(4) NOT NULL,
  `Date` DATETIME NOT NULL,
  `Rate` DECIMAL(28,8) DEFAULT NULL,
  PRIMARY KEY (`From_CurrencyID`,`To_CurrencyID`,`Date`,`ProviderID`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

To create a (test) merge table for this I just change the last line to be:
) ENGINE=MRG_MYISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC UNION=(tps_FX_Rates)

Here is an example definition for a table that does work:
CREATE TABLE `tpl_SizeBand` (
  `BandGroupID` INT(11) NOT NULL,
  `SizeBandID` CHAR(1) NOT NULL,
  `Description` VARCHAR(50) DEFAULT NULL,
  `GT` DECIMAL(28,8) DEFAULT NULL,
  `LTE` DECIMAL(28,8) DEFAULT NULL,
  `PCBased` INT(11) DEFAULT NULL,
  PRIMARY KEY (`BandGroupID`,`SizeBandID`),
  KEY `LTE` (`LTE`,`GT`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1

And again to create the merge table:
ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(tpl_SizeBand)

I have scanned the online manuals and can find nothing, outside what I have already tried, that may fix this or suggest that I may be doing something wrong.

How to repeat:

Create tables on mysql v5.1, use a variety of column formats (in case that has something to do with it).

Raw copy the tables to a mysql v5.6 installation.

Attempt to create Merge tables in the 5.6 installation looking at the 5.1 tables (use the original tables definition to ensure compatibility).

Attempt to access said merge tables.

Suggested fix:

Dump and reload all tables.
[4 Jun 2013 16:57] MySQL Verification Team
This does not have to be a problem in 5.1 vs 5.6.

Note that MERGE table can not have UNIQUE or PRIMARY KEY.

If you still have a problem after this is corrected, do send us the entire set of CREATE TABLE commands, together with the error message that you get.
[4 Jun 2013 17:28] MySQL Verification Team
for what it's worth i couldn't repeat this issue as described.

so, we may need a physical copy of one of the tables that doesn't work.
if you can compress the .frm, .MYI, .MYD of a small non-working table and upload it that will help :)
[5 Jun 2013 11:43] Giles McArdell
Thank you for your replies. Removing the PRIMARY for the key does not seem to help.
I have attached a file with both a 5.1 table and a 5.6 merge table pointing to it.

Hopefully you will see the issue as described.
[5 Jun 2013 13:24] MySQL Verification Team
This code in ha_myisammrg::attach_children() is triggering the problem:

==========
if (table->s->reclength != stats.mean_rec_length && stats.mean_rec_length)
{
  DBUG_PRINT("error",("reclength: %lu  mean_rec_length: %lu",
                  table->s->reclength, stats.mean_rec_length));
  if (test_if_locked & HA_OPEN_FOR_REPAIR)
  {
    /* purecov: begin inspected */
    myrg_print_wrong_table(file->open_tables->table->filename);
    /* purecov: end */
  }
  error= HA_ERR_WRONG_MRG_TABLE_DEF;
  goto err;
}
==========

The field descriptions of the 5.1 frm are as follows:

Field Start Length Nullpos Nullbit Type
1     1     1
2     2     4                      no zeros
3     6     8                      no zeros
4     14    13                     varchar
5     27    8      1       1       no zeros

A newly created 5.6 table has:
Field Start Length Nullpos Nullbit Type
1     1     1
2     2     4                      no zeros
3     6     5                      no zeros
4     11    13                     varchar
5     24    5      1       1       no zeros

Notice 8 bytes vs 5 bytes for the DATETIME fields.
The "fix" in this specific case is to alter table myisam table so that it gets newer style DATETIME.

alter table tpl_periodstartdates 
change ProcessDate ProcessDate datetime not null,
change PeriodStartDate PeriodStartDate datetime default null;
[5 Jun 2013 13:36] MySQL Verification Team
So, as CHECK TABLE .. FOR UPGRADE is not detecting this, and therefore mysql_upgrade doesn't take any action..  I think it is a MyISAM issue ?
[5 Jun 2013 14:28] MySQL Verification Team
Hi!

As our Shane Bester has explained, for certain column types there is no compatibility between 5.6 and 5.1. This is due to difference in various DATE/TIME column types.

MyISAM tables from 5.1 should work just fine in 5.6, unless you create a new MERGE tables for it. If you have MERGE table from 5.1, it should work too.

So, the only solution is to dump / reload if you want to create a MERGE table in 5.6 for MyISAM tables from 5.1.
[5 Jun 2013 14:56] Giles McArdell
Thank you for the help. Looks like I have allot of tables to alter, oh well.
[5 Jun 2013 15:01] MySQL Verification Team
You are truly welcome !!!
[23 Jul 2013 9:00] Rasmus Toelhoej
This also applies to TIMESTAMP columns, ALTER TABLE fixes the issue.

ALTER TABLE `monitor_dsa`.`1_runs_detailed`
  MODIFY COLUMN `end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00';

You can create a Stored Procedure that upgrades all tables:

-- Version: 2013.573
-- Upgrade 5.1 MyISAM tables to 5.6 format via a ALTER TABLE query.
DELIMITER $$

DROP PROCEDURE IF EXISTS `MigrateToMySQL56` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `MigrateToMySQL56`(
      IN db VARCHAR(50),
      IN dsa_db VARCHAR(50)
  )
BEGIN
    DECLARE done, projectId, columnExists INT DEFAULT 0;
    DECLARE projectCursor CURSOR FOR SELECT id FROM projects;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET done = 0;
    OPEN projectCursor;
    REPEAT
        FETCH projectCursor INTO projectId;
        IF NOT done THEN
            SET @sqlc = CONCAT('ALTER TABLE `',dsa_db,'`.`',projectId,"_runs_detailed` MODIFY COLUMN `end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'");
            PREPARE stmt FROM @sqlc; EXECUTE stmt; DEALLOCATE PREPARE stmt;

        END IF;
    UNTIL done END REPEAT;
    CLOSE projectCursor;

END $$

DELIMITER ;

CALL MigrateToMySQL56('monitor', 'monitor_dsa');
[23 Jul 2013 10:43] Giles McArdell
Thanks for the addition Rasmus, hopefully it`ll help other people with this problem.

I just want to say that I disagree with this being marked as "Not a Bug" - Either there is a bug with MERGE tables as they do not work as documented or there is a bug with CHECK TABLE ... FOR UPGRADE as it fails to report the need to update these tables.
Add to this the fact that this behavior is also not documented.
[23 Jul 2013 14:02] MySQL Verification Team
The problem will be documented in a near future, but I do not know when exactly.

Documentation will also contain a text that all pre-5.6 tables with TIME, TIMESTAMP and DATE TIME columns have to be dump from pre-5.6 versions and  loaded into 5.6 version.