0

We have a mysql 5.1.73 server and a myisam table (we're migrating to later version of mysql and innodb as part of this eventually).

We do a mysqldump every night of a table.

Basic table structure is like

CREATE TABLE `view` (
  `view_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,
  `person_id` int(10) unsigned NOT NULL,
...
PRIMARY KEY (`view_id`)
) ENGINE=MyISAM ...

There are over 300 million entries.

When we try and import into a different database we get an error (after its imported already 95% of the table fine).

ERROR 1062 (23000) at line 24941: Duplicate entry '303732287' for key 'PRIMARY'

I've searched on that error, and there's a few potentially misleading reasons. So I thought I would check the file direct first.

On inspection of the file, there is indeed a duplicate

grep -o -P '\(303732287.{0,2000}' stats_view_20231101.sql
(303732287,'2023-03-26 00:54:27',1623240508 ... (then some other unique entries)
(303732287,'2023-03-26 00:54:27',1623240508 ... (then some other unique entries different to above).

(if I grep other id's I only get one entry, so I think the grep is ok)

I'm trying to understand why the dump has duplicate entries for a primary key ?

If I look at the live table with

select * from view where view_id=303732284;

303732287 | 2023-03-26 00:54:27 | 1623240508...
1 row in set (0.00 sec)

So the original has 1 entry, but the mysqldump has 2. Any idea what is happening here (and how to get around it)?

edit: I can restore past the error using mysql -f and there's only 1 error out of 300 million entries, but I'd still like to understand what may have happened.

5
  • Are changes occurring on this table while you are creating the dump file? And have you used command-line options of mysqldump to skip locking the table? E.g. --skip-lock-tables? Nov 1 at 19:28
  • Hi, I'm pretty sure changes aren't happening whilst the dump is created, as it's always the same entry from March. We aren't using skip-lock-tables.
    – Ian
    Nov 2 at 7:47
  • 1
    Okay I have never seen mysqldump do this, so it was just a guess. If I had this issue, I would convert the table to InnoDB and try again. Actually I would convert the table to InnoDB in any case. InnoDB has better performance and scalability when tuned for your workload, and MyISAM fails to support any of the ACID properties. Nov 2 at 15:44
  • Yes, that's our goal, we're restoring backups and then converting to innodb. I just wanted to make sure the data is as good as it can be, and this threw me. Out of all the tables so far, there's only one out of 3 million entries for that table, so it's hard to want to spend too much time on it, but it's an itch that needs scratching not knowing what's wrong.
    – Ian
    Nov 2 at 17:14
  • Run REPAIR TABLE view;
    – Rick James
    Nov 10 at 3:04

0

Your Answer

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

Browse other questions tagged or ask your own question.