Description:
I have a big table (more than 500.000.000 of rows, whit more than 50 fields, partition by hash over the field idterminal in 64 partitions).
The table definition are:
CREATE TABLE `historico` (
`Nombre` varchar(50) DEFAULT NULL,
[...]
`Clave` varchar(64) DEFAULT NULL,
[...]
`IdTerminal` int(11) NOT NULL DEFAULT '0',
[...]
UNIQUE KEY `idx_Historico_Clave` (`IdTerminal`,`Clave`),
KEY `idx_historico_FechaRecepcion` (`IdTerminal`,`FechaRecepcion`),
KEY `idx_historico_idterminal_fechagps` (`IdTerminal`,`FechaGPS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (`IdTerminal`)
PARTITIONS 64 */
I am getting some strange results: Depending on the fields I put in a query, the number of rows I get is different:
mysql> select idterminal, clave from historico partition(p0) where clave = 'G20191016081555P41344';
+------------+-----------------------+
| idterminal | clave |
+------------+-----------------------+
| 41344 | G20191016081555P41344 |
+------------+-----------------------+
1 row in set (0.00 sec)
mysql> select nombre, idterminal, clave from historico partition(p0) where clave = 'G20191016081555P41344';
+----------+------------+-----------------------+
| nombre | idterminal | clave |
+----------+------------+-----------------------+
| A Name | 41344 | G20191016081555P41344 |
| A Name | 41344 | G20191016081555P41344 |
| A Name | 41344 | G20191016081555P41344 |
| A Name | 41344 | G20191016081555P41344 |
+----------+------------+-----------------------+
4 rows in set (5.07 sec)
This table has an unique index over the fields "idTerminal" and "clave", so I understand that the second query must return only one row. When I try to make a rebuild of this partition doing
alter table historico rebuild partition p0;
I get:
ERROR 1022 (23000) at line 1: Can't write; duplicate key in table 'historico'
Other crazy results:
mysql> select count(*) as how_many, clave, idterminal from historico partition(p0) group by clave, idterminal order by how_many desc limit 10;
+----------+----------------------------+------------+
| how_many | clave | idterminal |
+----------+----------------------------+------------+
| 4 | G20191021142024P700653 | 15680 |
| 4 | G20191001182332P700653 | 15680 |
| 4 | G20191001183144P700653 | 15680 |
| 4 | G20191005063231P830677 | 53824 |
| 4 | G20191005080409P830677 | 53824 |
| 4 | G20191005103808P830677 | 53824 |
| 4 | G20191005123548A830677-124 | 53824 |
| 4 | G20191005144959P830677 | 53824 |
| 4 | G20191005161853P830677 | 53824 |
| 4 | G20191005172114P830677 | 53824 |
+----------+----------------------------+------------+
10 rows in set (1 min 25.36 sec)
mysql> select count(*) as how_many, clave, idterminal from historico partition(p0) where idterminal = 15680 and clave = 'G20191021142024P700653' group by clave, idterminal order by how_many desc limit 10;
+----------+------------------------+------------+
| how_many | clave | idterminal |
+----------+------------------------+------------+
| 1 | G20191021142024P700653 | 15680 |
+----------+------------------------+------------+
1 row in set (0.00 sec)
My first suspicion was that the unique index are corrupted, but if I check the partition:
alter table historico check partition p0;
I get:
datos.historico check status OK
So, it´s look like a bug
How to repeat:
I don´t know how to repeat in the laboratory.
