Hello everyone
I am currently testing the migration of my web server to PHP 8.2.13 and mariadb 11.2.2.
During my tests, I got stuck on a SELECT problem on tables using spatial indexes.
During a complex page chaining several CREATE TABLEs on temporary tables with spatial indexes, a SELECT planted with the message "update lock cannot be acquired during a READ UNCOMMITTED transaction".
After consulting Google, I first unblocked the situation by removing the spatial indexes on temporary tables but then the performance of intermediate queries collapsed.
In the end, after doing a test package, I definitely found a solution: the default database engine is innodb. By forcing the myisam engine on the temporary tables the page executes in a few seconds!
Questions:
- what to think of this use case of spatial indexes with innodb vs myisa?
- Is this a bug?
Below are the DMLs for queries with myisam explicitly indicated.
With innodb, the last query in the list crashes, either timeout if I execute via php, or "update lock cannot be acquired during a READ UNCOMMITTED transaction" if I execute the query via phpmyadmin
1 CREATE TABLE TEMPTABLE_localites_du_site(Localite_unik_commune int,
Localite_dept varchar(15), Localite_unik int, GPS_unik int,
Type_Localite varchar(1), Localite_p_coordonnees_spacial point not null)
ENGINE = myisam ;
0 0
2 ALTER TABLE TEMPTABLE_localites_du_site
ADD SPATIAL INDEX IDX_temptable_localites_du_site_Localite_p_coordonnees_spacial(Localite_p_coordonnees_spacial);
0 0
1 CREATE TABLE WKWNat_2A2B50F4_415A_443F_A959_38AB2F0EC1C6 (Localite_unik_commune int,
Localite_dept varchar(15),
Localite_unik int,
GPS_unik int,
Type_Localite varchar(1),
Localite_p_coordonnees_spacial point,
Maille_unik int,
Maille_unik_site int,
Maille_maille varchar(80),
Maille_taille int,
Maille_g_coordonnees_spacial geometry NOT NULL,
Maille_couleur_bordure int,
Maille_couleur_aire int,
Maille_transparence_aire int,
Maille_Exclusion bit NOT NULL,
Longitude_Centre double(10,6),
Latitude_Centre double(10,6),
in_use_by_leudit bit NOT NULL,
myTaille varchar(3)) ENGINE = myisam;
0 0
5 insert INTO
TEMPTABLE_localites_du_site (SELECT DISTINCT
wnat_lieuxdits.unik_commune,
wnat_communes.Dept,
wnat_lieuxdits.unik AS unik_lieudit,
NULL AS unik_gps,
'L' AS Type_Localite,
wnat_lieuxdits.p_coordonnees_spacial
FROM wnat_lieuxdits
INNER JOIN wnat_inventaires
ON wnat_inventaires.unik_lieudit = wnat_lieuxdits.unik
INNER JOIN wnat_communes
ON wnat_communes.unik = wnat_lieuxdits.unik_commune WHERE
wnat_inventaires.unik_organisme=10 AND
ST_CONTAINS(ST_PolygonFromText('POLYGON((41.36475929466526
8.54315203804477,43.0275854970405 8.54315203804477,43.0275854970405
9.555702267296203,41.36475929466526 9.555702267296203,41.36475929466526
8.54315203804477))'),wnat_lieuxdits.p_coordonnees_spacial) )
0 788
6
insert INTO TEMPTABLE_localites_du_site (SELECT DISTINCT
wnat_lieuxdits.unik_commune,
wnat_communes.Dept,
wnat_lieuxdits.unik AS unik_lieudit,
wnat_gps.unik AS unik_gps,
'G' AS Type_Localite,
wnat_gps.p_coordonnees_spacial
FROM wnat_lieuxdits
INNER JOIN wnat_gps
ON wnat_lieuxdits.unik = wnat_gps.unik_lieudit
INNER JOIN wnat_inventaires
ON wnat_gps.unik = wnat_inventaires.unik_gps
INNER JOIN wnat_communes
ON wnat_communes.unik = wnat_lieuxdits.unik_commune
WHERE wnat_inventaires.unik_organisme=10 AND
ST_CONTAINS(ST_PolygonFromText('POLYGON((41.36475929466526
8.54315203804477,43.0275854970405 8.54315203804477,43.0275854970405
9.555702267296203,41.36475929466526 9.555702267296203,41.36475929466526
8.54315203804477))'),wnat_gps.p_coordonnees_spacial) )
0 29566
7
DELETE TEMPTABLE_localites_du_site FROM TEMPTABLE_localites_du_site
INNER JOIN wnat_lieuxdits ON ( TEMPTABLE_localites_du_site.Localite_unik
= wnat_lieuxdits.unik)
INNER JOIN wnat_communes ON (wnat_lieuxdits.unik_commune =
wnat_communes.unik)
where wnat_communes.unik_organisme<>10
0 0
765 CREATE
TABLE TEMPTABLE_W1 ENGINE = myisam AS SELECT
MAX(wnat_geometries_mailles.taille) AS max_taille
FROM TEMPTABLE_localites_du_site
INNER JOIN wnat_geometries_mailles
ON ST_CONTAINS(wnat_geometries_mailles.g_coordonnees_spacial,
TEMPTABLE_localites_du_site.Localite_p_coordonnees_spacial)
WHERE wnat_geometries_mailles.unik_geometrie_objet IN
(121332,121333,121334,121335,121419,121385,121376,121383,121449,121450,121345,121373,121370,121371,121317,121379,121355,121362,121363,121367,121372,121346,121347,121348,121349,121365,121366,121378,121442,121443,121444,121448,121411,121404,121325,121406,121315,121392,121393,121394,121395,121396,121391,121343,121344,121407,121388,121389,121412,121413,121414,121415,121420,121421,121422,121423,121424,121322,121318,121319,121320,121321,121369,121360,121361,121426,121427,121428,121429,121430,121431,121432,121327,121356,121357,121405,121316,121408,121409,121425,121375,121351,121350,121323,121384,121390,121364,121324,121416,121417,121418,121368,121386,121387,121352,121447,121440,121441,121433,121434,121435,121328,121329,121330,121331,121446,121374,121451,121397,121377,121445,121382,121336,121337,121338,121339,121340,121341,121342,121436,121437,121438,121353,121354,121380,121381,121439,121326,121398,121399,121400,121401,121402,121403,121410,121358,121359,-1)
AND wnat_geometries_mailles.Exclusion = FALSE;
0 1
query_Recordset1
insert INTO WKWNat_2A2B50F4_415A_443F_A959_38AB2F0EC1C6 (SELECT
TEMPTABLE_localites_du_site.Localite_unik_commune,
TEMPTABLE_localites_du_site.Localite_dept,
TEMPTABLE_localites_du_site.Localite_unik,
TEMPTABLE_localites_du_site.GPS_unik,
TEMPTABLE_localites_du_site.Type_Localite,
TEMPTABLE_localites_du_site.Localite_p_coordonnees_spacial,
wnat_geometries_mailles.unik,
wnat_geometries_mailles.unik_geometrie_objet,
wnat_geometries_mailles.maille,
wnat_geometries_mailles.taille,
wnat_geometries_mailles.g_coordonnees_spacial,
wnat_geometries_mailles.couleur_bordure,
wnat_geometries_mailles.couleur_aire,
wnat_geometries_mailles.transparence_aire,
wnat_geometries_mailles.Exclusion,
wnat_geometries_mailles.longitude_centre,
wnat_geometries_mailles.latitude_centre,
1,
MAX_TAILLE.max_taille AS myTaille
FROM TEMPTABLE_localites_du_site
INNER JOIN wnat_geometries_mailles
ON ST_CONTAINS(wnat_geometries_mailles.g_coordonnees_spacial,
TEMPTABLE_localites_du_site.Localite_p_coordonnees_spacial)
INNER JOIN TEMPTABLE_W1 as MAX_TAILLE
ON wnat_geometries_mailles.taille = MAX_TAILLE.max_taille
WHERE wnat_geometries_mailles.unik_geometrie_objet IN
(121332,121333,121334,121335,121419,121385,121376,121383,121449,121450,121345,121373,121370,121371,121317,121379,121355,121362,121363,121367,121372,121346,121347,121348,121349,121365,121366,121378,121442,121443,121444,121448,121411,121404,121325,121406,121315,121392,121393,121394,121395,121396,121391,121343,121344,121407,121388,121389,121412,121413,121414,121415,121420,121421,121422,121423,121424,121322,121318,121319,121320,121321,121369,121360,121361,121426,121427,121428,121429,121430,121431,121432,121327,121356,121357,121405,121316,121408,121409,121425,121375,121351,121350,121323,121384,121390,121364,121324,121416,121417,121418,121368,121386,121387,121352,121447,121440,121441,121433,121434,121435,121328,121329,121330,121331,121446,121374,121451,121397,121377,121445,121382,121336,121337,121338,121339,121340,121341,121342,121436,121437,121438,121353,121354,121380,121381,121439,121326,121398,121399,121400,121401,121402,121403,121410,121358,121359,-1)
AND wnat_geometries_mailles.Exclusion = FALSE and
in_use_by_lieudit<>0);
8 insert INTO
WKWNat_2A2B50F4_415A_443F_A959_38AB2F0EC1C6 (SELECT
TEMPTABLE_localites_du_site.Localite_unik_commune,
TEMPTABLE_localites_du_site.Localite_dept,
TEMPTABLE_localites_du_site.Localite_unik,
TEMPTABLE_localites_du_site.GPS_unik,
TEMPTABLE_localites_du_site.Type_Localite,
TEMPTABLE_localites_du_site.Localite_p_coordonnees_spacial,
wnat_geometries_mailles.unik,
wnat_geometries_mailles.unik_geometrie_objet,
wnat_geometries_mailles.maille,
wnat_geometries_mailles.taille,
wnat_geometries_mailles.g_coordonnees_spacial,
wnat_geometries_mailles.couleur_bordure,
wnat_geometries_mailles.couleur_aire,
wnat_geometries_mailles.transparence_aire,
wnat_geometries_mailles.Exclusion,
wnat_geometries_mailles.longitude_centre,
wnat_geometries_mailles.latitude_centre,
1,
MAX_TAILLE.max_taille AS myTaille
FROM TEMPTABLE_localites_du_site
INNER JOIN wnat_geometries_mailles
ON ST_CONTAINS(wnat_geometries_mailles.g_coordonnees_spacial,
TEMPTABLE_localites_du_site.Localite_p_coordonnees_spacial)
INNER JOIN TEMPTABLE_W1 as MAX_TAILLE
ON wnat_geometries_mailles.taille = MAX_TAILLE.max_taille
WHERE wnat_geometries_mailles.unik_geometrie_objet IN
(121332,121333,121334,121335,121419,121385,121376,121383,121449,121450,121345,121373,121370,121371,121317,121379,121355,121362,121363,121367,121372,121346,121347,121348,121349,121365,121366,121378,121442,121443,121444,121448,121411,121404,121325,121406,121315,121392,121393,121394,121395,121396,121391,121343,121344,121407,121388,121389,121412,121413,121414,121415,121420,121421,121422,121423,121424,121322,121318,121319,121320,121321,121369,121360,121361,121426,121427,121428,121429,121430,121431,121432,121327,121356,121357,121405,121316,121408,121409,121425,121375,121351,121350,121323,121384,121390,121364,121324,121416,121417,121418,121368,121386,121387,121352,121447,121440,121441,121433,121434,121435,121328,121329,121330,121331,121446,121374,121451,121397,121377,121445,121382,121336,121337,121338,121339,121340,121341,121342,121436,121437,121438,121353,121354,121380,121381,121439,121326,121398,121399,121400,121401,121402,121403,121410,121358,121359,-1)
AND wnat_geometries_mailles.Exclusion = FALSE and
in_use_by_lieudit<>0);
0 17672