MRG_MyISAM – Blocking Inserts with myisampack Tables

mariadbmyisamMySQL

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.

Best Answer

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.

Related Question