SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘indexacion-2-blockTitle’ for key ‘plugin_settings_pkey’

Software Support
1

Im trying to update from version 3.2.1.2 to 3.3.0.8, but during the process i have this database error:

Se ha producido un error en la base de datos: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘indexacion-2-blockTitle’ for key ‘plugin_settings_pkey’ (SQL: insert into plugin_settings (context_id, plugin_name, setting_name, setting_type, setting_value) values (2, redes sociales, blockTitle, object, a:1:{s:5:“es_ES”;s:14:“redes sociales”;}), (2, normas para colaboradores, blockTitle, object, a:1:{s:5:“es_ES”;s:25:“normas para colaboradores”;}), (2, open source, blockTitle, object, a:1:{s:5:“es_ES”;s:11:“open source”;}), (2, facebook, blockTitle, object, a:1:{s:5:“es_ES”;s:8:“facebook”;}), (2, basesdedatos, blockTitle, object, a:1:{s:5:“es_ES”;s:12:“basesdedatos”;}), (2, normas, blockTitle, object, a:1:{s:5:“es_ES”;s:6:“normas”;}), (2, indexacion, blockTitle, object, a:1:{s:5:“es_ES”;s:10:“indexacion”;}), (2, indexacion, blockTitle, object, a:1:{s:5:“es_ES”;s:10:“indexacion”;}), (2, idiomas_new, blockTitle, object, a:1:{s:5:“es_ES”;s:11:“idiomas_new”;}))

Do you have any idea of what the problem is ?

Thank’s for you help

1 Like
2

Hi @iicmuseoIT ,
I was having the same problem as you. I was trying to upgrade from version 3.2.1-4 to 3.3.0-8 but this error was preventing.
I found the solution and came to contribute with you, in case you still need it and it is useful for the next ones that come here looking for the solution, as I did.

CREATE TEMPORARY TABLE IF NOT EXISTS plugin_settings_2_delete AS (
    SELECT s.context_id
    FROM plugin_settings s
        LEFT JOIN rt_contexts j ON (s.context_id = j.context_id)
    WHERE j.context_id IS NULL
);
DELETE FROM plugin_settings  WHERE context_id IN (SELECT context_id   FROM plugin_settings_2_delete);
DROP TABLE plugin_settings_2_delete;
1 Like
3

Hi, I am having this issue;

ERROR: Upgrade failed: DB: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘google-10-blockTitle’ for key ‘plugin_settings_pkey’ (SQL: insert into plugin_settings (context_id, plugin_name, setting_name, setting_type, setting_value) values (1, crossref, blockTitle, object, a:1:{s:5:“en_US”;s:8:“crossref”;}), (2, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (2, issn, blockTitle, object, a:1:{s:5:“fr_FR”;s:4:“issn”;}), (3, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (3, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (4, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (4, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (5, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (5, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (6, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (6, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (7, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (7, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (7, rgrhmetrics, blockTitle, object, a:1:{s:5:“fr_FR”;s:11:“rgrhmetrics”;}), (8, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (8, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (9, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (9, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (10, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (10, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (10, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (11, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (11, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (12, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (12, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (13, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (13, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (14, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (14, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (15, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (15, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (16, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (16, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (17, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (17, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (18, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (18, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (19, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (19, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (20, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (20, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (21, google, blockTitle, object, a:1:{s:5:“fr_FR”;s:6:“google”;}), (21, crossref, blockTitle, object, a:1:{s:5:“fr_FR”;s:8:“crossref”;}), (22, google, blockTitle, object, a:1:{s:5:“en_US”;s:6:“google”;}), (22, crossref, blockTitle, object, a:1:{s:5:“en_US”;s:8:“crossref”;}), (23, google, blockTitle, object, a:1:{s:5:“en_US”;s:6:“google”;}), (23, crossref, blockTitle, object, a:1:{s:5:“en_US”;s:8:“crossref”;}), (24, google, blockTitle, object, a:1:{s:5:“en_US”;s:6:“google”;}), (24, crossref, blockTitle, object, a:1:{s:5:“en_US”;s:8:“crossref”;}))

Can you please suggest me any solution? Please list the steps involved in implementing the above solution. I do not know, if it is the solution, or how can we implement this.

4

Hello @esciencepress .
Try to use the solution posted above.

1 Like
5

@murilodbva can you please guide me the steps to use this solution. Where to paste this code, please.

6

@asmecher Can you please help me how to use the code.

7

I do the script but, all the content on tables was deleted 1703 rows,

image

and its destroy all the plugins setting content

9

@acahya , so sorry for my response in Mar/2022, please.
After that, I also had some problem. Maybe was becouse with my solution above.

So, If you still need a solution,
today (august/2023) I think different and believe the solution below is better:

1º) Before the UPGRADE

-- remove the unique key, for allow to include repeated registers
ALTER TABLE `plugin_settings` DROP INDEX `plugin_settings_pkey`;


2º) After the UPGRADE

-- remove the repeated registers

CREATE TEMPORARY TABLE tmp_plugin_settings AS
SELECT *
FROM plugin_settings
GROUP BY plugin_name, context_id, setting_name;

DELETE FROM plugin_settings;

INSERT INTO plugin_settings
SELECT * FROM tmp_plugin_settings;

DROP TABLE tmp_plugin_settings;

-- return the unique key
ALTER TABLE `plugin_settings` ADD UNIQUE KEY `plugin_settings_pkey` (`plugin_name`,`context_id`,`setting_name`);

ps.: in my case, because I upgraded to 3.3.0.11 with the response I posted in Mar/2022, I solved my problem upgrading to version 3.3.0.15.

10

Sorry @esciencepress for didn’t response you hear.
Maybe isn’t that, but it seems you need to study the basic of SQL language.

1 Like