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: | |
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
[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.