Primary index issue
Posted by: JAIME Martin
Date: June 20, 2017 02:50AM

Now, I´ve found somthing that looks like a very big bug in MySQL 5.5.11, so before to think that choose MySQL is a bad decission, I prefer to ask the people that has a better knowled about MySQL...

mysql> show index from historico;
+-----------+------------+-----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| historico | 0 | idx_Historico_Clave | 1 | IdTerminal | A | 810 | NULL | NULL | | BTREE | | |
| historico | 0 | idx_Historico_Clave | 2 | Clave | A | 394315658 | NULL | NULL | YES | BTREE | | |
| historico | 1 | idx_historico_FechaRecepcion | 1 | IdTerminal | A | 810 | NULL | NULL | | BTREE | | |
| historico | 1 | idx_historico_FechaRecepcion | 2 | FechaRecepcion | A | 394315658 | NULL | NULL | YES | BTREE | | |
| historico | 1 | idx_historico_idterminal_fechagps | 1 | IdTerminal | A | 810 | NULL | NULL | | BTREE | | |
| historico | 1 | idx_historico_idterminal_fechagps | 2 | FechaGPS | A | 394315658 | NULL | NULL | YES | BTREE | | |
+-----------+------------+-----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (50.31 sec)

As it is possible to see, the "historico" table has a unique index named "idx_historico_clave" over the "idterminal" and "clave" fields.
The question are how is it possible the following:

mysql> select idterminal, clave from historico where idterminal = 38720 and fechagps = '20170611230057';
+------------+------------------------+
| idterminal | clave |
+------------+------------------------+
| 38720 | G20170611230057P102355 |
| 38720 | G20170611230057P102355 |
+------------+------------------------+


Aditional information:
- Table historico is a huge table, it has over 300.000.000 registers
- Table historico is partitioned by HASH(idterminal) in 64 partitions
- The database who has the problem is a "copy" of a production data base, obtained throw a "total" mysqldump (mysqldump -uroot -pXXXXXXX --skip-lock-tables datos historico, and then inject into the database using "mysql" command line.
then, I´ve done a partial backup of the production database using "mysqldump -uroot -pXXXXXXXX --skip-lock-tables --insert-ignore datos historico -t -w"fecharecepcion>'20170619230000'", and import in the copy database
Then, I´ve done a partial backup of the production database using "mysqldump -uroot -pXXXXXXXX --skip-lock-tables --insert-ignore datos historico -t -w"fecharecepcion>'20170619230000'", and import in the copy database

So, when I try to do "alter table historico rebuild partition p0;" I get the following error:
ERROR 1062 (23000) at line 1: Duplicate entry '38720-G20170611230057P102355' for key 'idx_Historico_Clave'

or if I try to do something like 'insert into historico (idterminal, clave) Values (1, 'pp'); twice, the second time I get a similar error message

Options: ReplyQuote


Subject
Written By
Posted
Primary index issue
June 20, 2017 02:50AM
June 20, 2017 04:03AM
June 20, 2017 04:29AM
June 20, 2017 02:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.