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_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
