Bug script de régénération des index SQL (MySQL) depuis IHM d'objet métier

bmo · August 16, 2022, 3:08pm

Problem description

Le script de proposition d’index ne restitue pas les instructions dans le bon ordre pour les index concernant les FK.

Steps to reproduce

I have reproduced the problem on an up-to-date Simplicité instance
and those are the steps to do it:

Accéder au formulaire de définition d’un objet
Actionner “Régénérer les index”
Les instructions suivantes sont proposées (extrait du script proposé):

DROP INDEX bcsi_legal_entity_dep_dep_legal_referent_person_id_fk ON bcsi_legal_entity_dep;
CREATE INDEX bcsi_legal_entity_dep_dep_legal_referent_person_id_fk ON bcsi_legal_entity_dep (dep_legal_referent_person_id);

ALTER TABLE bcsi_legal_entity_dep DROP FOREIGN KEY bcsi_legal_entity_dep_dep_legal_referent_person_id_fkc;
ALTER TABLE bcsi_legal_entity_dep ADD CONSTRAINT bcsi_legal_entity_dep_dep_legal_referent_person_id_fkc FOREIGN KEY (dep_legal_referent_person_id) REFERENCES m_user(row_id);

L’erreur suivante est restituée dans les logs :

Error: Cannot drop index 'bcsi_legal_entity_dep_dep_legal_referent_person_id_fk': needed in a foreign key constraint

La séquence d’instruction corrigée ci-dessous fonctionne (DROP FOREIGN KEY avant DROP INDEX) :

ALTER TABLE bcsi_legal_entity_dep DROP FOREIGN KEY bcsi_legal_entity_dep_dep_legal_referent_person_id_fkc;

DROP INDEX bcsi_legal_entity_dep_dep_legal_referent_person_id_fk ON bcsi_legal_entity_dep;
CREATE INDEX bcsi_legal_entity_dep_dep_legal_referent_person_id_fk ON bcsi_legal_entity_dep (dep_legal_referent_person_id);

ALTER TABLE bcsi_legal_entity_dep ADD CONSTRAINT bcsi_legal_entity_dep_dep_legal_referent_person_id_fkc FOREIGN KEY (dep_legal_referent_person_id) REFERENCES m_user(row_id);

Ce problème est général à toutes les propositions d’index associé à une foreign key.

Technical information

Instance /health


[Platform]
Status=OK
Version=5.2.11
BuiltOn=2022-07-22 22:18
Git=5.2/bc1f6e720f5df119336af65fa1b502bd40ac47d4
Encoding=UTF-8
EndpointIP=21.0.9.3
EndpointURL=http://c43d2fa7d007:8080
TimeZone=Europe/Paris
SystemDate=2022-08-16 16:59:58

[Application]
ApplicationVersion=1.0.0
ContextPath=
ContextURL=https://bca.dok-dev.intra.renault.fr
ActiveSessions=3
TotalUsers=8315
EnabledUsers=1867
LastLoginDate=2022-08-16 16:14:57

[Server]
ServerInfo=Apache Tomcat/9.0.65
ServerType=WEB
ServerActiveSessions=3
ServerSessionTimeout=30

[OS]
Name=Linux
Architecture=amd64
Version=3.10.0-1160.71.1.el7.x86_64
DockerImageName=centos7
SystemEncoding=UTF-8

[JavaVM]
Version=17.0.3
Vendor=Eclipse Adoptium
VMName=OpenJDK 64-Bit Server VM
VMVersion=17.0.3+7
ScriptEngine=rhino
ScriptEngineVersion=Rhino 1.7.13 2020 09 02
HeapFree=752670
HeapSize=1520512
HeapMaxSize=3040896
TotalFreeSize=2273054

[Cache]
ObjectCache=475
ObjectCacheMax=10000
ObjectCacheRatio=4
ProcessCache=0
ProcessCacheMax=10000
ProcessCacheRatio=0
APIGrantCache=0
APIGrantCacheMax=1000
APIGrantRatio=0

[Database]
Vendor=2
ProductName=MySQL
ProductVersion=5.6.39-log
DriverName=MySQL Connector/J
DriverVersion=mysql-connector-java-8.0.29 (Revision: dd61577595edad45c398af508cf91ad26fc4144f)
DBDate=2022-08-16 16:59:58
DBDateOffset=0
DBPatchLevel=5;P02;061b2c28d6156a42239626b53ced124f
UsingBLOBs=true

[Healthcheck]
Date=2022-08-16 16:59:58
ElapsedTime=12

Simplicité logs

	Aug 16, 2022 @ 16:33:22.450	java.sql.SQLException: Cannot drop index 'bcsi_legal_entity_dep_dep_legal_referent_person_id_fk': needed in a foreign key constraint
	Aug 16, 2022 @ 16:33:22.450	e[me[31m2022-08-16 16:33:21,286|SIMPLICITE|ERROR||http://c43d2fa7d007:8080||ECOREDB001|system|com.simplicite.util.engine.GrantManager|update||Error SQL query: jdbc/simplicite: DROP INDEX bcsi_legal_entity_dep_dep_legal_referent_person_id_fk ON bcsi_legal_entity_dep
	Aug 16, 2022 @ 16:33:22.450	e[m2022-08-16 16:33:21,540|GLOBAL|INFO|e[32m2022-08-16 16:32:24,530|SIMPLICITE|INFO||http://c43d2fa7d007:8080||ICORED0001|system|com.simplicite.util.engine.GrantManager|dropIndex||Info: DROP INDEX bcsi_legal_entity_dep_dep_legal_referent_person_id_fk ON bcsi_legal_entity_dep
	Aug 16, 2022 @ 16:33:22.451		at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
	Aug 16, 2022 @ 16:33:22.451		at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
	Aug 16, 2022 @ 16:33:22.451		at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	Aug 16, 2022 @ 16:33:22.451		at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
	Aug 16, 2022 @ 16:33:22.451		at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	Aug 16, 2022 @ 16:33:22.452		at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:135)
	Aug 16, 2022 @ 16:33:22.452		at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
	Aug 16, 2022 @ 16:33:22.452		at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:135)
	Aug 16, 2022 @ 16:33:22.452		at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
	Aug 16, 2022 @ 16:33:22.452		at com.simplicite.util.engine.DBAccess.update(DBAccess.java:1542)
	Aug 16, 2022 @ 16:33:22.453		at com.simplicite.util.engine.DBAccess.update(DBAccess.java:1490)
	Aug 16, 2022 @ 16:33:22.453		at com.simplicite.objects.System.ObjectInternal.generateIndexes(ObjectInternal.java:1113)
	Aug 16, 2022 @ 16:33:22.453		at com.simplicite.util.engine.GrantDirect.dropIndex(GrantDirect.java:542)
	Aug 16, 2022 @ 16:33:22.453		at com.simplicite.util.engine.DBAccess.dropIndex(DBAccess.java:2394)
	Aug 16, 2022 @ 16:33:22.453		at com.simplicite.util.Grant.dropIndex(Grant.java:2081)
	Aug 16, 2022 @ 16:33:22.453		at com.simplicite.objects.System.ObjectInternal.buildIndex(ObjectInternal.java:1177)
	Aug 16, 2022 @ 16:33:22.454		at com.simplicite.objects.System.ObjectInternal.generateIndexes(ObjectInternal.java:1080)

Browser logs

NA

Other relevant information

NA

david · August 16, 2022, 4:06pm

Les mystères de MySQL qui doit “optimiser” les FK en s’appuyant sur les indexes…

En tout cas la (re)génération d’index est faite dans l’ordre des fields de l’objet.

Pour un field donné cela génère (si pertinent) des couples DROP+CREATE d’abord en tant qu’index et ensuite en tant que FK

Il est facile d’inverser les couples DROP+CREATE pour index et FK mais en l’état faire d’abord les DROP index et FK et ensuite les CREATE index et FK nécessite un refactoring plus poussé.

Dans ton cas que se passe-t-il si tu lance la séquence suivante ?

ALTER TABLE bcsi_legal_entity_dep DROP FOREIGN KEY bcsi_legal_entity_dep_dep_legal_referent_person_id_fkc;
ALTER TABLE bcsi_legal_entity_dep ADD CONSTRAINT bcsi_legal_entity_dep_dep_legal_referent_person_id_fkc FOREIGN KEY (dep_legal_referent_person_id) REFERENCES m_user(row_id);

DROP INDEX bcsi_legal_entity_dep_dep_legal_referent_person_id_fk ON bcsi_legal_entity_dep;
CREATE INDEX bcsi_legal_entity_dep_dep_legal_referent_person_id_fk ON bcsi_legal_entity_dep (dep_legal_referent_person_id);

bmo · August 16, 2022, 4:10pm

Merci bcp pour ton retour rapide…

le DROP INDEX plante car la FOREIGN KEY a déjà été recréée…

david · August 16, 2022, 4:51pm

OK ça plaide donc pour une première passe de DROPs suivi d’une seconde passe de CREATEs.
On va regarder ça.

david · August 19, 2022, 1:22pm

La révision 5.2.12 releasée ce soir embarque un changement dans l’ordre des DROP/CREATE.

bmo · August 19, 2022, 1:37pm

Super, je ne testerai a priori que lundi matin. Je reviens vers toi pour confirmer la résolution du problème.

bmo · August 22, 2022, 7:38am

Bonjour David,

tests KO ce matin :

Voici le script proposé sur un autre objet (cas plus simple que le précédent)

DROP INDEX bcsi_legal_entity_uk ON bcsi_legal_entity;

CREATE UNIQUE INDEX bcsi_legal_entity_uk ON bcsi_legal_entity (legal_entity_name);

DROP INDEX bcsi_legal_entity_legal_entity_name_idx ON bcsi_legal_entity;

DROP INDEX bcsi_legal_entity_legal_entity_code_idx ON bcsi_legal_entity;

DROP INDEX bcsi_legal_entity_legal_entity_general_referent_person_id_fk ON bcsi_legal_entity;

ALTER TABLE bcsi_legal_entity DROP FOREIGN KEY bcsi_legal_entity_legal_entity_general_referent_person_id_fkc;

DROP INDEX bcsi_legal_entity_legal_entity_country_id_fk ON bcsi_legal_entity;

ALTER TABLE bcsi_legal_entity DROP FOREIGN KEY bcsi_legal_entity_legal_entity_country_id_fkc;

CREATE INDEX bcsi_legal_entity_legal_entity_code_idx ON bcsi_legal_entity (legal_entity_code);

CREATE INDEX bcsi_legal_entity_legal_entity_general_referent_person_id_fk ON bcsi_legal_entity (legal_entity_general_referent_person_id);

ALTER TABLE bcsi_legal_entity ADD CONSTRAINT bcsi_legal_entity_legal_entity_general_referent_person_id_fkc FOREIGN KEY (legal_entity_general_referent_person_id) REFERENCES m_user(row_id);

CREATE INDEX bcsi_legal_entity_legal_entity_country_id_fk ON bcsi_legal_entity (legal_entity_country_id);

ALTER TABLE bcsi_legal_entity ADD CONSTRAINT bcsi_legal_entity_legal_entity_country_id_fkc FOREIGN KEY (legal_entity_country_id) REFERENCES bcsi_country(row_id);

Cette séquence pose deux problèmes :

Le DROP INDEX bcsi_legal_entity_legal_entity_name_idx qui n’est jamais recréé (j’ai supprimé l’instruction pour passer)
→ ce cas n’est pas bloquant car la plateforme semble outrepasser l’erreur et continuer à dérouler le script; seules les exécutions manuelles (via DBAccess ou requêteur) s’arrêtent sur cette instruction… Néanmoins, il semble qu’il manque un CREATE INDEX quelque part…
Les instructions du type DROP INDEX *_fk et ALTER TABLE * DROP FOREIGN KEY *_fkc sont inversées (j’ai placé les DROP FOREIGN KEY avant les DROP INDEX pour passer)

J’ai contourné les deux problèmes avec cette séquence :

DROP INDEX bcsi_legal_entity_uk ON bcsi_legal_entity;

CREATE UNIQUE INDEX bcsi_legal_entity_uk ON bcsi_legal_entity (legal_entity_name);

-- DROP INDEX bcsi_legal_entity_legal_entity_name_idx ON bcsi_legal_entity;

DROP INDEX bcsi_legal_entity_legal_entity_code_idx ON bcsi_legal_entity;

ALTER TABLE bcsi_legal_entity DROP FOREIGN KEY bcsi_legal_entity_legal_entity_general_referent_person_id_fkc;

DROP INDEX bcsi_legal_entity_legal_entity_general_referent_person_id_fk ON bcsi_legal_entity;

ALTER TABLE bcsi_legal_entity DROP FOREIGN KEY bcsi_legal_entity_legal_entity_country_id_fkc;

DROP INDEX bcsi_legal_entity_legal_entity_country_id_fk ON bcsi_legal_entity;

CREATE INDEX bcsi_legal_entity_legal_entity_code_idx ON bcsi_legal_entity (legal_entity_code);

CREATE INDEX bcsi_legal_entity_legal_entity_general_referent_person_id_fk ON bcsi_legal_entity (legal_entity_general_referent_person_id);

ALTER TABLE bcsi_legal_entity ADD CONSTRAINT bcsi_legal_entity_legal_entity_general_referent_person_id_fkc FOREIGN KEY (legal_entity_general_referent_person_id) REFERENCES m_user(row_id);

CREATE INDEX bcsi_legal_entity_legal_entity_country_id_fk ON bcsi_legal_entity (legal_entity_country_id);

ALTER TABLE bcsi_legal_entity ADD CONSTRAINT bcsi_legal_entity_legal_entity_country_id_fkc FOREIGN KEY (legal_entity_country_id) REFERENCES bcsi_country(row_id);

david · August 23, 2022, 12:23pm

Les deux points ont été réglés, ce sera poussé avec la prochaine révision 5.2.13

bmo · August 23, 2022, 12:48pm

Super, merci bcp. Idem, je te fais un retour dèq que j’ai pu la déployer…
Aucun urgence de mon côté mais tu as une idée de la date de publication sur dockerhub ?

david · August 23, 2022, 12:53pm

D’ici fin Aout normalement

david · September 2, 2022, 7:42am

Bonjour, la 5.2.13 a été releasée hier (donc déjà disponibles sur les SIMs) et ses images Docker seront poussées dans la journée

bmo · September 2, 2022, 10:07am

Hello, j’ai déjà commencé à check sur le SIM
Pour l’instant c’est OK

david · September 2, 2022, 10:10am

Normalement les images Docker sont poussées

bmo · September 2, 2022, 4:10pm

ça a l’air OK sur un docker monté sur notre conf métier “à vide” (i.e. sans données métier significatives).
Je déploie sur des environnements avec des données métier significatives pour confirmer tout ça.
La séquence de suppression/création des index et fk semble ok en tout cas.

bmo · September 2, 2022, 6:52pm

Bon, tout est OK sur les autres environnements.
merci bcp.

system · September 2, 2022, 7:53pm

This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.