1

I'm using INSERT_METHOD=FIRST and compressing all tables that are members of the union but when I try to insert into the MRG_MyISAM table it tries to insert into the second one (instead of the first). Why? When I uncompress the 2nd table and insert again, it tries to insert into the 3rd. It gives an error that the table is read-only which I'm fully aware. This behavior is unexpected.

mysql Ver 15.1 Distrib 5.5.37-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1

My MRG_MyISAM table looks like:

CREATE TABLE `ah_sales` ( `server` tinyint(3) unsigned NOT NULL DEFAULT '0', `itemId` smallint(5) unsigned NOT NULL DEFAULT '0', `countof` tinyint(1) NOT NULL DEFAULT '0', `seller` mediumint(8) unsigned NOT NULL DEFAULT '0', `buyer` mediumint(8) unsigned NOT NULL DEFAULT '0', `price` int(10) unsigned NOT NULL DEFAULT '0', `saleon` int(10) unsigned NOT NULL DEFAULT '0', KEY `buyer` (`buyer`), KEY `itemserver` (`countof`,`server`,`itemId`), KEY `seller` (`seller`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`ah_sales_4_2014`,`ah_sales_4_2013`,`ah_sales_8_2012`,`ah_sales_4_2012`,`ah_sales_12_2011`,`ah_sales_6_2011`,`ah_sales_3_2011`,`ah_sales_12_2010`,`ah_sales_6_2010`,`ah_sales_3_2010`,`ah_sales_11_2009`,`ah_sales_8_2009`,`ah_sales_5_2009`,`ah_sales_2_2009`,`ah_sales_11_2008`,`ah_sales_8_2008`,`ah_sales_5_2008`,`ah_sales_2_2008`,`ah_sales_11_2007`,`ah_sales_9_2007`,`ah_sales_8_2007`,`ah_sales_6_2007`,`ah_sales_5_2007`,`ah_sales_3_2007`,`ah_sales_2_2007`,`ah_sales_1_2007`,`ah_sales_12_2006`,`ah_sales_11_2006`,`ah_sales_10_2006`,`ah_sales_9_2006`,`ah_sales_8_2006`)

ah_sales_4_2014 is uncompressed.

1 Answer 1

1

This might be a bug. In essence, it sounds like the Mrg_MyISAM definition must be using the structure of the first member of the UNION clause as the basis for the rest the tables in the list.

Before you report the bug, try reversing the order of the UNION clause so that all the compressed tables are first and the uncompressed one is last

CREATE TABLE `ah_sales` (
 `server` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `itemId` smallint(5) unsigned NOT NULL DEFAULT '0',
 `countof` tinyint(1) NOT NULL DEFAULT '0',
 `seller` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `buyer` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `price` int(10) unsigned NOT NULL DEFAULT '0',
 `saleon` int(10) unsigned NOT NULL DEFAULT '0',
 KEY `buyer` (`buyer`),
 KEY `itemserver` (`countof`,`server`,`itemId`),
 KEY `seller` (`seller`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
INSERT_METHOD=LAST
UNION=(`ah_sales_4_2013`,`ah_sales_8_2012`,
`ah_sales_4_2012`,`ah_sales_12_2011`,`ah_sales_6_2011`,
`ah_sales_3_2011`,`ah_sales_12_2010`,`ah_sales_6_2010`,
`ah_sales_3_2010`,`ah_sales_11_2009`,`ah_sales_8_2009`,
`ah_sales_5_2009`,`ah_sales_2_2009`,`ah_sales_11_2008`,
`ah_sales_8_2008`,`ah_sales_5_2008`,`ah_sales_2_2008`,
`ah_sales_11_2007`,`ah_sales_9_2007`,`ah_sales_8_2007`,
`ah_sales_6_2007`,`ah_sales_5_2007`,`ah_sales_3_2007`,
`ah_sales_2_2007`,`ah_sales_1_2007`,`ah_sales_12_2006`,
`ah_sales_11_2006`,`ah_sales_10_2006`,`ah_sales_9_2006`,
`ah_sales_8_2006`,`ah_sales_4_2014`);

Then, see if the problem persists. Either way, you may have to submit a short or long bug report.

IMHO, running myisampack across all MyISAM tables is a good idea PROVIDED YOU ONLY SELECT FROM ALL THE TABLES. I do not believe it was meant to be used the way you are doing it (performing INSERTs when there is a mix of compressed and uncompressed). I do not think you will have this problem if all the MyISAM tables were uncompressed.

1
  • Thanks for the feedback. A quote from the docs: dev.mysql.com/doc/refman/5.5/en/merge-storage-engine.html "However, any or all of the MyISAM tables can be compressed with myisampack" I am going to uncompress them and see if it works. I have been using this structure (uncompressed) for many years as you can see. I just thought I would try to save some space with myisampack since only the first partition get inserts. Commented Jul 9, 2014 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.