I recently began using MERGE tables in MySQL and it seems that HeidiSQL doesn't quite know how to handle them when performing copy or export operations.
When doing an export, or copy, or other type of operation that involves the data in a MERGE table, it would be best if HeidiSQL didn't try to copy the actual data. The table definition needs to be copied, but any attempt to copy the data will result in duplicated data in the destination database. This is because MERGE tables are virtual in that they combine data from two real tables... sort of like a view might do. So none of the data that appears to be in a MERGE table is actually there; it is coming from another table.
I can see some situations where someone might want to copy data in a MERGE table, but generally speaking if data is included, it is going to be duplicated.
Handling of MERGE tables
Yes, I should be able to exclude tables with ENGINE=MRG_MYISAM in data exporting. For the "Create table copy" dialog, there is a checkbox which should be fine if the user really wants data. Only the export dialog should have this self-intelligence. Only I'm having problems creating such a merge table...
This way, I get a broken merge3 table, when firing SHOW TABLE STATUS: "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist"
CREATE TABLE `merge1` (`id` INT(10)) ENGINE=MYISAM;
CREATE TABLE `merge2` (`id` INT(10)) ENGINE=MYISAM;
CREATE TABLE `merge3` (`id` INT(10)) ENGINE=MRG_MYISAM UNION=(merge1,merge2);
This way, I get a broken merge3 table, when firing SHOW TABLE STATUS: "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist"
Code modification/commit
5d4a331
from ansgar.becker,
13 years ago,
revision 6.0.0.4035
Do not export data from MRG_MYISAM tables. See http://www.heidisql.com/forum.php?t=9842
Please login to leave a reply, or register at first.