Details
-
Bug
-
Status: Stalled (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.6.1, 10.2.39, 10.3.30, 10.4.20, 10.5.11
-
None
Description
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 |
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- is caused by
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Open
-
-
-
- Confirmed
-
- links to
Activity
This has basically completely broken SPATIAL indexes for plain SELECTs...
In the hope a simple testcase will help (tested on 10.4.21):
CREATE TABLE `1_test` ( |
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, |
`point` point NOT NULL, |
PRIMARY KEY (`id`), |
SPATIAL KEY `point` (`point`) |
) ENGINE=InnoDB;
|
|
|
INSERT INTO `1_test` (point) VALUES (POINT(1, 2)); |
|
|
SELECT * FROM `1_test` WHERE ST_Equals(point, POINT(1,2)); |
# 1207 - Update locks cannot be acquired during a READ UNCOMMITTED transaction |
For those encountering the bug the workarounds seem to be either:
1. Explicitly start a transaction:
START TRANSACTION; |
SELECT * FROM `1_test` WHERE ST_Equals(point, POINT(1,2)); |
# 1 row found
|
2. Use FOR UPDATE to acquire an exclusive lock
SELECT * FROM `1_test` WHERE ST_Equals(point, POINT(1,2)) FOR UPDATE; |
# 1 row found
|
Has there been any progress on this one?
It's a really big issue for us, since it was introduced in a patch-update.
Same here, we are stuck on 10.3.29 because 10.3.30 completely broke our application.
Is there a plan to get spatial indexes working again? The comment "Fixing this bug is not trivial. I fear that the locking for SPATIAL INDEX would need to be redesigned altogether." and the lack of updates in this issue do not give me much hope. If there is no plan of fixing this, we may have to look at other databases for spatial data.
An update about the issue would really be appreciated. We might be forced to migrate as well.
The latest working version for us (10.3.29) is not available anymore in apt repos, so we are now unable to create a new server/dev environment with a working database. Any update would be greatly appreciated.
Failed to repeat it on the latest 10.6, 10.4.20, 10.4.21 with the above test. I also tried to change `id` to varchar and do query with two indexes in "WHERE" and MBRContains() for `point` as in initial description, and still could not repeat it.
If the cause of the bug is the code above, then to repeat it we need to make the following condition true: index->is_spatial() && !m_prebuilt->trx->will_lock && trx_is_started(m_prebuilt->trx). Trx is started in row_search_mvcc(), so the "SELECT" from the above test can't cause HA_ERR_READ_ONLY_TRANSACTION error in that code, because the code is executed before row_search_mvcc() call, and if trx_is_started(m_prebuilt->trx) returns false, m_prebuilt->trx->will_lock will be set to true, and the next ha_innobase::index_read() call will not cause HA_ERR_READ_ONLY_TRANSACTION error in the above code.
marko, do you have any hints how to cause HA_ERR_READ_ONLY_TRANSACTION in the above code?
I have a similar issue when after upgrading (to 10.4.24) some of the queries using spatial indexes end with the ERROR 1207 Update locks cannot be acquired during a READ UNCOMMITTED transaction ..
I can't reproduce it with the simple case in the comments but I according to my own tests it depends on the query plan.
For example in my case:
MariaDB [test]> SELECT o.`id` FROM `g_objects` AS o WHERE o.`company_id` = 8180 AND o.`id` IN (488303,488447,500473,575867,580203,580212,623637,623859,628425,677985,706993,721783,721941,721944,728740,728741,729841,742282,790548,916178,917083) AND ST_Intersects(`o`.`bounds`, GEOMFROMTEXT('POLYGON((57.725530004693 23.513320460916,56.3249044718 23.513320460916,56.3249044718 25.10170519352,57.725530004693 25.10170519352,57.725530004693 23.513320460916))')); |
|
|
ERROR 1207 (25000): Update locks cannot be acquired during a READ UNCOMMITTED transaction |
|
|
+------+-------------+-------+--------------+-----------------------+---------------+---------+------+---------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------------+-----------------------+---------------+---------+------+---------+---------------------------------+
|
| 1 | SIMPLE | o | range|filter | PRIMARY,bounds,index1 | bounds|index1 | 34|4 | NULL | 19 (0%) | Using where; Using rowid filter | |
+------+-------------+-------+--------------+-----------------------+---------------+---------+------+---------+---------------------------------+
|
|
now if I force to use just the spatial index (or I can remove any single value from the o.id IN ()) the execution plan changes and query finishes without error:
MariaDB [test]> SELECT o.`id` FROM `g_objects` AS o FORCE INDEX(bounds) WHERE o.`company_id` = 8180 AND o.`id` IN (488303,488447,500473,575867,580203,580212,623637,623859,628425,677985,706993,721783,721941,721944,728740,728741,729841,742282,790548,916178,917083) AND ST_Intersects(`o`.`bounds`, GEOMFROMTEXT('POLYGON((57.725530004693 23.513320460916,56.3249044718 23.513320460916,56.3249044718 25.10170519352,57.725530004693 25.10170519352,57.725530004693 23.513320460916))')); |
Empty set (0.245 sec) |
|
|
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
|
| 1 | SIMPLE | o | range | bounds | bounds | 34 | NULL | 19 | Using where | |
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
|
1 row in set (0.000 sec) |
Table:
CREATE TABLE `g_objects` (
|
`id` int(11) NOT NULL AUTO_INCREMENT, |
`company_id` int(11) NOT NULL, |
`bounds` polygon NOT NULL,
|
PRIMARY KEY (`id`),
|
SPATIAL KEY `bounds` (`bounds`),
|
KEY `index1` (`company_id`)
|
) ENGINE=InnoDB
|
Hope that helps to pinpoint the cause.
The issue manifest itself when we force a spatial index while joining a table.
e.g.
straight_join <table> FORCE INDEX(<spatial index>) on
|
MDEV-26204 has a test case with 2 tables + data and a query to reproduce. This is tested in 10.3.30, I'm not sure if it's applicable to 10.4 and newer, since I don't have an environment with those versions installed.
Thanks to richardh, I found quite a simple way to reproduce it:
--source include/have_innodb.inc
|
|
CREATE TABLE `t1` (`p` point NOT NULL, SPATIAL KEY (`p`)) ENGINE=InnoDB;
|
INSERT INTO `t1` VALUES (POINT(1, 2));
|
|
CREATE TABLE `t2` LIKE `t1`;
|
INSERT INTO `t2` VALUES (POINT(1, 2));
|
|
SELECT * FROM t2 WHERE ST_Contains(t2.p, (SELECT p FROM t1));
|
|
DROP TABLE t1, t2;
|
As we know that the code causes this issue, and, as I understood from the comments, the root case of MDEV-25594 was not found, I removed the code, trying to reproduce MDEV-25594, which the code is supposed to fix. I used RQG with the following grammar based on the above test:
thread1_init:
|
CREATE TABLE t1 (p point NOT NULL, SPATIAL KEY (p)) ENGINE=InnoDB;
|
CREATE TABLE t2 LIKE t1;
|
|
|
query:
|
BEGIN; INSERT INTO table_name VALUES (POINT(my_int, my_int)); commit_rollback |
|
BEGIN; DELETE FROM table_name WHERE ST_Contains(table_name.p, POINT(my_int, my_int)); commit_rollback |
|
SELECT * FROM t2 WHERE ST_Contains(t2.p, (SELECT p FROM t1 LIMIT 1));
|
|
|
table_name:
|
t1 |
|
t2 ;
|
|
|
my_int: # Something like below which generates a biased value distribution
|
# where small values will get more frequent generated.
|
{ $my_int= 1 } |
|
{ $my_int= $prng->int( 2, 8) } |
|
{ $my_int= $prng->int( 9, 64) } |
|
{ $my_int= $prng->int( 65, 512) } |
|
{ $my_int= $prng->int(513, 4096) };
|
# or assign direct in the INSERT statement something like
|
# _smallint_unsigned or _int or ...
|
|
|
commit_rollback:
|
COMMIT |
|
ROLLBACK ;
|
I did not catch anything in 10.5.9, 10.5.10 and the latest 10.5. But in 10.6 I caught the following crash:
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
|
#1 0x00001a5b1a2a0859 in __GI_abort () at abort.c:79
|
#2 0x00001a5b1a2a0729 in __assert_fail_base (fmt=0x1a5b1a436588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n",
|
assertion=0x55a760fbab20 "!writer.load(std::memory_order_relaxed)",
|
file=0x55a760fbab80 "./storage/innobase/include/sux_lock.h", line=79, function=<optimized out>) at assert.c:92
|
#3 0x00001a5b1a2b2006 in __GI___assert_fail (assertion=0x55a760fbab20 "!writer.load(std::memory_order_relaxed)",
|
file=0x55a760fbab80 "./storage/innobase/include/sux_lock.h", line=79,
|
function=0x55a760fbb760 "void sux_lock<ssux>::free() [with ssux = ssux_lock_impl<true>]") at assert.c:101
|
#4 0x000055a75fe8be5c in sux_lock<ssux_lock_impl<true> >::free (this=0x497d1082d8a8)
|
at ./storage/innobase/include/sux_lock.h:79
|
#5 0x000055a76005fe68 in rtr_copy_buf (matches=0x497d1080d888, block=0x6fd456b75630)
|
at ./storage/innobase/gis/gis0sea.cc:1501
|
#6 0x000055a76006053a in rtr_init_match (matches=0x497d1080d888, block=0x6fd456b75630, page=0x6fd457ec0000 "")
|
at ./storage/innobase/gis/gis0sea.cc:1537
|
#7 0x000055a760061b21 in rtr_cur_search_with_match (block=0x6fd456b75630, index=0x616001de0008, tuple=0x6200001af4c8, mode=PAGE_CUR_WITHIN,
|
cursor=0x6200001af2e0, rtr_info=0x61d000a09680) at ./storage/innobase/gis/gis0sea.cc:1861
|
#8 0x000055a75fcac50e in sel_set_rtr_rec_lock (pcur=0x6200001af2d8, first_rec=0x497d108100aa "", index=0x616001de0008, offsets=0x757423481ac0, mode=3,
|
type=1024, thr=0x6200001af7d0, mtr=0x757423481da0) at ./storage/innobase/row/row0sel.cc:1228
|
#9 0x000055a75fcad16d in sel_set_rec_lock (pcur=0x6200001af2d8, rec=0x497d108100aa "", index=0x616001de0008, offsets=0x757423481ac0, mode=3, type=1024,
|
thr=0x6200001af7d0, mtr=0x757423481da0) at ./storage/innobase/row/row0sel.cc:1344
|
#10 0x000055a75fcc3527 in row_search_mvcc (buf=0x619000417dc8 "", mode=PAGE_CUR_WITHIN, prebuilt=0x6200001af108, match_mode=0, direction=0)
|
at ./storage/innobase/row/row0sel.cc:5185
|
#11 0x000055a75f8c3ac8 in ha_innobase::index_read (this=0x61d00077d8b8, buf=0x619000417dc8 "", key_ptr=0x621000186200 "", key_len=34,
|
find_flag=HA_READ_MBR_WITHIN) at ./storage/innobase/handler/ha_innodb.cc:9092
|
#12 0x000055a75ee237c7 in handler::index_read_map (this=0x61d00077d8b8, buf=0x619000417dc8 "", key=0x621000186200 "", keypart_map=1,
|
find_flag=HA_READ_MBR_WITHIN) at ./sql/handler.h:3921
|
#13 0x000055a75efcd90a in handler::ha_index_read_map (this=0x61d00077d8b8, buf=0x619000417dc8 "", key=0x621000186200 "", keypart_map=1,
|
find_flag=HA_READ_MBR_WITHIN) at ./sql/handler.cc:3474
|
#14 0x000055a75f3a752d in QUICK_RANGE_SELECT_GEOM::get_next (this=0x613000398d80) at ./sql/opt_range.cc:12822
|
#15 0x000055a75f3d7a64 in rr_quick (info=0x757423482790) at ./sql/records.cc:403
|
#16 0x000055a75e654cb2 in READ_RECORD::read_record (this=0x757423482790) at ./sql/records.h:81
|
#17 0x000055a75f4253a5 in mysql_delete (thd=0x62b00017a218, table_list=0x62b0001813b8, conds=0x62b000181ed8, order_list=0x62b00017ee60,
|
limit=18446744073709551615, options=0, result=0x0) at ./sql/sql_delete.cc:797
|
The crash happens even if the code is not removed. I don't know yet if the crash is relevant to MDEV-25594.
vlad.lesin, I introduced that debug check when reimplementing the buffer page latches in MariaDB Server 10.6. The assertion fails because an exclusively held latch is being freed. This is related to the question that was left unanswered in MDEV-14059. Why are buffer page descriptors being copied in some SPATIAL INDEX operations? Could it be avoided?
That is not the only peculiarity in SPATIAL INDEX locking. What are the tweaks in the purge of history needed for? Can some garbage in SPATIAL INDEX be left behind by the purge of history if there was some locking conflict when the purge was attempted? Why is there a race condition between MVCC and ROLLBACK (MDEV-15284)?
I think that we must first understand how the locking on MySQL 5.7 SPATIAL INDEX was supposed to work. This is not an easy task.
Edit: it seems like updating to MariaDB 10.11 LTS and deleting and re-creating the index fixed it for me!
To solve this, I:
- Upgraded to the latest MariaDB
- Ran `ALTER TABLE DROP INDEX your_spatial_index; ALTER TABLE ADD SPATIAL(your_column)`
The error seems to stop occurring after that!
---------
Old comment:
I just wanted to mention that we're still running into this issue in 10.4.26. So far, our workaround has been to install an earlier version of the mariadb-server-10.4 packages (10.4.19) and pinning those on apt, since we're on Ubuntu.
Since the MariaDB repositories only keep the latest two versions available, we have a version saved for Ubuntu 20.04 LTS (focal):
- mariadb-server-10.4_10.4.19+maria~focal_amd64.deb
- mariadb-server-core-10.4_10.4.19+maria~focal_amd64.deb
To install these, just place them onto the servers and run:
dpkg -i mariadb-server-10.4_10.4.19+maria~focal_amd64.deb
dpkg -i mariadb-server-core-10.4_10.4.19+maria~focal_amd64.deb
apt-mark hold mariadb-server-10.4
apt-mark hold mariadb-server-core-10.4
I just test latest 10.11 and 11.0-rc using docker images from https://hub.docker.com/_/mariadb and the problem still exist... Tested with the test case from https://jira.mariadb.org/browse/MDEV-26123?focusedCommentId=221221&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-221221
I encountered this error with spatial index on point, and trying to run the following query
CREATE TABLE `profile` (
`location` point NOT NULL DEFAULT point(0,0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `profile`
ADD SPATIAL KEY `profile_location_spatial` (`location`);
insert into profile(location) VALUES(point(41, 12));
SELECT profile.location from profile
WHERE
ST_WITHIN(profile.location, X'SOME INLINE GEOMETRY HERE')
I also tried the latest version of MariaDB with no help.. Latest version I can run now is 10.5.10, but at least 10.4.19 works too
Any updates on this?
I have the same on 11.8.1-MariaDB-ubu2404 - mariadb.org binary distribution
SELECT r.id
FROM records r IGNORE INDEX(coordinates)
JOIN areas a ON ST_Contains(a.coordinates, r.coordinates)
WHERE a.id = 13863 LIMIT 100;
works fine but
SELECT r.id
FROM records r
JOIN areas a ON ST_Contains(a.coordinates, r.coordinates)
WHERE a.id = 13863 LIMIT 100;
gives: Error Code: 1207. Update locks cannot be acquired during a READ UNCOMMITTED transaction
UPDATE: tried the failing query on 8.4.4 - MySQL Community Server - GPL and it works without problem
The SPATIAL INDEX implementation that was copied from MySQL 5.7 to MariaDB 10.2.2 is ridden with locking problems, such as MDEV-15284.
The fix of
MDEV-25594prevents corruption of InnoDB transaction and lock metadata. A transaction that was created as read-only is not supposed to change mode to read-write later.diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.ccindex e431b3f3595..8bdc0a9e478 100644--- a/storage/innobase/handler/ha_innodb.cc+++ b/storage/innobase/handler/ha_innodb.cc@@ -9399,8 +9399,12 @@ ha_innobase::index_read(/* For R-Tree index, we will always place the page lock topages being searched */- if (dict_index_is_spatial(index)) {- ++m_prebuilt->trx->will_lock;+ if (index->is_spatial() && !m_prebuilt->trx->will_lock) {+ if (trx_is_started(m_prebuilt->trx)) {+ DBUG_RETURN(HA_ERR_READ_ONLY_TRANSACTION);+ } else {+ m_prebuilt->trx->will_lock = true;+ }}Fixing this bug is not trivial. I fear that the locking for SPATIAL INDEX would need to be redesigned altogether.