3

After upgrading to mariadb 10.5.11 I ran into a weird problem with the indexes.

Simple table with two colums Type(varchar) and Point(point)

An index on Type(Tindex) and a spatial index on Point(Pindex)

Now a query like

SELECT X(Point) as x,Y(Point) as y,hotels.Type FROM hotels WHERE (Type in ("acco")) AND MBRContains( GeomFromText( 'LINESTRING(4.922 52.909,5.625 52.483)' ), hotels.Point)
;

Results in a

Error in query (1207): Update locks cannot be acquired during a READ UNCOMMITTED transaction

While both

SELECT X(Point) as x,Y(Point) as y,hotels.Type FROM hotels USE INDEX (Pindex) WHERE (Type in ("acco")) AND MBRContains( GeomFromText( 'LINESTRING(4.922 52.909,5.625 52.483)' ), hotels.Point)
;

and

SELECT X(Point) as x,Y(Point) as y,hotels.Type FROM hotels USE INDEX (Tindex) WHERE (Type in ("acco")) AND MBRContains( GeomFromText( 'LINESTRING(4.922 52.909,5.625 52.483)' ), hotels.Point)
;

work fine. As mariadb 10.5.10 did

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

| 1 | SIMPLE | hotels | range|filter | Type,Pindex | Pindex|Type | 34|302 | NULL | 340 (4%) | Using where; Using rowid filter |

brbrbr
  • 31
  • 2

3 Answers3

2

The issue is now being tracked as MDEV-26123 (I guess you reported it there). The issue description says that the problem was introduced in MariaDB 10.2.39, 10.3.30, 10.4.20, 10.5.11, 10.6.1.

I ran into the issue after upgrading to MariaDB 10.6.4. I downgraded to 10.6.0, which was possible without having to do any migration of the data. It seems to have fixed the problem for now.

cdauth
  • 6,171
  • 3
  • 41
  • 49
  • 1
    I just delete the old index, then, create a new one with different name, magically the error is gone ... dunno why – Kampau Ocu Sep 09 '21 at 14:09
1

The cause of this appears to be the code fix for MDEV-25594.

I cannot see anything in the commit message or discussion there that indicates that a change to the READ UNCOMMITTED behavior was intentional.

There are no open bug reports on this so I recommend you create a new bug report.

danblack
  • 12,130
  • 2
  • 22
  • 41
  • 1
    MDEV-26123: Fixing this bug is not trivial. I fear that the locking for {{SPATIAL INDEX}} would need to be redesigned altogether. – brbrbr Jul 21 '21 at 15:45
1

select @@session.autocommit;

set @@session.autocommit=0;

select @@session.autocommit;

#add in my.cnf autocommit = 0

using mariadb 10.2.40 ( resolved ) https://developpaper.com/transaction-isolation-level-of-mariadb/

Hendi Fauzi
  • 106
  • 1
  • 2