Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.22
-
None
-
OpenSUSE 13.2 x64
Description
Have Master with multiple slaves replication setup. Have numerous MyISAM tables that are being imported from flat files in about this way:
drop table if exists table_tmp; |
drop table if exists table_old; |
create table_tmp like table; |
load data local infile 'file.csv' into table table_tmp; |
rename table to table_old, table_tmp to table; |
drop table_old. |
On master indexes appears to be correct:
> show index from ipcBlocks;
|
+-----------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-----------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| ipcBlocks | 0 | PRIMARY | 1 | ipcBlockId | A | 1449711 | NULL | NULL | | BTREE | | |
|
| ipcBlocks | 1 | parentId | 1 | parentIpcBlockId | A | 17679 | NULL | NULL | | BTREE | | |
|
| ipcBlocks | 1 | blockTypeId | 1 | ipcBlockTypeId | A | 48 | NULL | NULL | | BTREE | | |
|
| ipcBlocks | 1 | blockName | 1 | blockName | A | 1449711 | NULL | NULL | YES | BTREE | | |
|
+-----------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
However on slave indexes for the same table look this way:
show index from ipcBlocks;
|
+-----------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-----------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| ipcBlocks | 0 | PRIMARY | 1 | ipcBlockId | A | 1449711 | NULL | NULL | | BTREE | | |
|
| ipcBlocks | 1 | parentId | 1 | parentIpcBlockId | A | NULL | NULL | NULL | | BTREE | | |
|
| ipcBlocks | 1 | blockTypeId | 1 | ipcBlockTypeId | A | NULL | NULL | NULL | | BTREE | | |
|
| ipcBlocks | 1 | blockName | 1 | blockName | A | NULL | NULL | NULL | YES | BTREE | | |
|
+-----------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
In fact, I have 3 slaves replicating from same master. And on all of the salves indexes look exactly the same.
And yes, I do have "myisam_repair_threads = 1" on all servers.
The table create SQL:
CREATE TABLE `ipcBlocks` ( |
`ipcBlockId` INT(10) UNSIGNED NOT NULL, |
`parentIpcBlockId` INT(10) UNSIGNED NOT NULL, |
`ipcBlockTypeId` INT(10) UNSIGNED NOT NULL, |
`blockName` VARCHAR(255) NULL DEFAULT NULL, |
`blockSize` TINYINT(3) UNSIGNED NULL DEFAULT NULL, |
`blockStatus` INT(10) UNSIGNED NOT NULL, |
`createAdmin` VARCHAR(255) NULL DEFAULT NULL, |
`createReason` VARCHAR(255) NULL DEFAULT NULL, |
`isPrimary` BIT(1) NOT NULL DEFAULT b'0', |
`createDate` DATETIME NULL DEFAULT NULL, |
PRIMARY KEY (`ipcBlockId`), |
INDEX `parentId` (`parentIpcBlockId`), |
INDEX `blockTypeId` (`ipcBlockTypeId`), |
INDEX `blockName` (`blockName`) |
)
|
COLLATE='latin1_swedish_ci' |
ENGINE=MyISAM
|
This is just one out of 22 tables that have exactly the same problem. Running analyze table <table>; fixes it temporarily until next data import.