MariaDB: General error: 1034 Duplicate key 1 for record at XXXX against records at YYYY for a Count/Group Query

Question

We have an odd error this morning.

there are a bunch of SQLs that count unique values in table columns. One of these returns with:

Error: SQLSTATE[HY000]: General error: 1034 Duplicate key 1 for record at 16044 against record at 15485

The SQL is:

SELECT COUNT(*) as numb, `AddressLine1` FROM <table> GROUP BY `AddressLine1`

The column is varchar(180)
The table is an InnoDb table
There are no keys or indexes on this column.
Table and column collations are both utf8mb4_unicode_ci
MariaDB 5.5.68 (I am aware it's old; it's 3rd party)

What this Isn't

I search "Duplicate key 1 for record at ..." and get many answers to similar sounding error labels but which do not seem to relate to this issue, such as INSERT KEY UPDATE and collation issues.

Typically answers reference the phrase "ERROR 1034: Incorrect key file" but this isn't what our error says.

Further MariaDBs error code listings show:

1034 HY000 ER_NOT_KEYFILE Incorrect key file for table '%s'; try to repair it

What I have done

I have run CHECK TABLE <table> and it returns ok.

I also try to run repairing the table but this is InnoDB so table repairs are much more fiddly. Nothing else suggests the table is problematic aside from this one error.

I have removed the Primary Key from the column (and the auto_increment) and then re-added both the Primary Key and auto_increment (in that order). The error still appears and the numbers are consistent (always the same 15485 / 16044 reference points).

Further, removing all indexes (keys) from the table and then readding them does not resolve the issue. The same "rows" throw up this same error.

There are foriegn keys on the table but they don't / should not relate to this column.

My Conclusion So Far

I suspect the table keys need rebuilding. I have seen this Question with numerous answers saying the issue is server space, but that doesn't appear to be the case in our situation.

InnoDB does not allow table repair and reading up about it this looks like a right pain in the poops to carry out (disk imaging etc.). Any advise on repairing InnoDb tables would probably be useful here.

What can I do to fix this issue?

Optimize Table:

As per comment, running

OPTIMIZE TABLE <table>;

Gives

| optimize | note | Table does not support optimize, doing recreate + analyze instead
| optimize | status | OK

As requested, rows 16044 and 15485 are as below:

SELECT * FROM <table> WHERE id IN (16044, 15485)

Results (The data has had to have been cleaned for public display but all <data / AppId / RefId> values are varchar ):

15485 | <refid> | <AppId> | Rejected | 2012-07-19 | 2012/2013 | 0  | NULL | NULL | NULL | 7 | BAME | Female | Regular | <data> | <data> | 2008-09-01  | 4 | University | University of Hertfordshire | HATFIELD | Club de Havilland | De Havilland Campus | Mosquito Way | United Kingdom | AL10 9EU | 24 | 0 | 0 |

16044 | <refid> | <AppId> | Withdrawn | 2012-08-01 | 2012/2013 | 0 | <data> | 35 | 6 | 2 | NON-BAME | Female | Regular | <data> | <data> | 2009-09-01 | 1 | NULL | The Henley College | HENLEY-ON-THAMES | Deanfield Avenue | NULL | NULL | United Kingdom | RG9 1UH | 19 | 0 | 0

AddressLine1 On each record is:

15485 | Club de Havilland
16044 | Deanfield Avenue

Update re Rick James' Answer;
Dumping, dropping and then reimporting the table does not solve the problem.

And because someone always complains that the whole SQL construction isn't added into the Question, here's the table DDL:

create table <table>
(
    id int(8) auto_increment primary key,
    various_string_columns varchar(180) null,
    various_string_columns varchar(180) null,
    ...
    an_id tinyint unsigned null comment 'reference id ',
    a_region tinyint(3) null comment 'A region id from the a_area table ',
    CandidateEthnicityId int(3) null,
    various_string_columns varchar(180) null,
    ee_fromdate date null,
    EstablishmentName varchar(180) null,
    Establishment_TownCity varchar(180) null,
    AddressLine1 varchar(180) null,
    AddressLine2 varchar(180) null,
    AddressLine3 varchar(180) null,
    Country varchar(180) null,
    Postcode varchar(180) null,
    AgeAtRecord tinyint unsigned null,
    ATC_Membership varchar(180) null,
    ...
    various_string_columns varchar(180) null,
    ...
    see_id smallint(6) unsigned null,
    constraint a_regionarea
        foreign key (a_region) references a_areas (area_id)
            on update cascade on delete set null,
    constraint afconame
        foreign key (a_id) references afco (id)
            on update cascade on delete set null
);

create or replace index ApplicationRef
    on <table> (ApplicationRef);

create or replace index a_id
    on <table> (a_id);

create or replace index a_region
    on <table> (a_region);

create or replace index ee_fromdate_2
    on <table> (ee_fromdate);

Thanks.

Can you run `OPTIMIZE TABLE ;` and update your question with the output of the optimize table statement? After attempting the optimize table could you share whether you are receiving the same error when running your query? — ggordon, Sep 11 '21 at 02:59
Removing `AUTO_INCREMENT` and reapplying it is likely to generate different numbers. This will drastically corrupt any other tables that reference this table by `id`. — Rick James, Sep 12 '21 at 06:49
@ggordon I have updated my question, please take a look. Thanks. — Martin, Sep 12 '21 at 13:42
@RickJames Yes I was aware of this with auto_increment value and this is ok as no individual row is referened elesewhere. — Martin, Sep 12 '21 at 13:43
@RickJames I have run this repair query and it's executed ok but the same error still occurs. — Martin, Sep 12 '21 at 13:44
@RickJames the max id is `424429`. Also the `COUNT(*)` of the table is `424429` as well. — Martin, Sep 12 '21 at 17:29
@RickJames Please see the update to the question. Some of the data has had to be cleaned but it's all basic VARCHAR anywho. — Martin, Sep 12 '21 at 18:22

Rick James · Answer 1 · 2021-09-13T02:33:38.600

0

Plan A:

mysqldump of that one table
DROP that table
Reload that table

If that fails to solve it...

Plan B:

File a bug report. (But don't expect an answer any time soon.) If they do respond, it will probably start with an insistence on upgrading since 5.5 is probably no longer supported.

edited Sep 13 '21 at 02:33

answered Sep 12 '21 at 17:45

Rick James

135,179
13
127
222

Dumping, dropping and then reimporting the table does not solve the problem. – Martin Sep 13 '21 at 10:37
I did explore the migration guide for MariaDB and 5.5 was expired April 2020 so should be updated on the system, so I don't see much worth filing a bug report. As an aside unlike PHP MaraiDB doesn't seem to have a canon location for finding out an expiry of a version, instead using the wikipedia page. – Martin Sep 13 '21 at 10:40
MariaDB seems to have settled on issuing a new release every year and supporting it for 5 years. 10.6. was released in mid-2021; to expire in 2026. 5.5 had a short reprieve because some Linux versions are still releasing with it. – Rick James Sep 13 '21 at 17:22

score 0 · Answer 2 · answered Sep 17 '21 at 15:24

0

Try

check table <table> extended

then

repair <table> extended

answered Sep 17 '21 at 15:24

K-attila-

101
2

Sorry no, this doesn't work for InnoDB tables. – Martin Sep 17 '21 at 21:36
Indeed. Sorry. It's exciting when the dump and restore isn't work too... – K-attila- Sep 21 '21 at 07:49
Can you drop the foreign keys? Or just one. – K-attila- Sep 22 '21 at 11:20

MariaDB: General error: 1034 Duplicate key 1 for record at XXXX against records at YYYY for a Count/Group Query

What can I do to fix this issue?

Optimize Table:

2 Answers2