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.
--skip-lock-tables
?REPAIR TABLE view;