Details
Description
We've been getting an error when running some SELECT queries. It's rare, but some queries trigger it most of the time (not all). Trying to whittle it down to a minimal test case turned out to be very difficult, as sometimes it happens, sometimes not. I wish I had more useful info to provide, I'm sorry to be vague.
The error is "INSERT command denied to user '<username redacted>'@'<ip address redacted>' for table `production`.`program_stable_id`", but I suspect it's not related to this at all, as the queries in question do not insert into this table. (This is a sequence table that is used as the default expression for a table referenced in these queries, though, in the form of 'nextval(`program_stable_id`)'. I don't know if that is part of the issue or just a coincidence.
This seems to have started after we did some work that involved creating very large tables and doing big updates on them, no idea if it's related to that. Another unusual thing that happened recently prior to this is that I killed a process that was in the middle of a transaction.
We're planning to restart our server soon to see if that makes the problem go away, if it's a case of it being in a bad state or something.
Here's the output of `show engine innodb status` in case that's useful.
| InnoDB | |
|
=====================================
|
2025-05-23 14:47:22 0x7f499267c640 INNODB MONITOR OUTPUT
|
=====================================
|
Per second averages calculated from the last 16 seconds
|
-----------------
|
BACKGROUND THREAD
|
-----------------
|
srv_master_thread loops: 62 srv_active, 0 srv_shutdown, 202142 srv_idle
|
srv_master_thread log flush and writes: 202200
|
----------
|
SEMAPHORES
|
----------
|
------------
|
TRANSACTIONS
|
------------
|
Trx id counter 101100474
|
Purge done for trx's n:o < 101100451 undo n:o < 0 state: running but idle
|
History list length 23
|
LIST OF TRANSACTIONS FOR EACH SESSION:
|
---TRANSACTION (0x7f5dd01c1680), ACTIVE 6 sec
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
MariaDB thread id 37723, OS thread handle 139953952921152, query id 84737904 <ip redacted> email-queue
|
Trx read view will not see trx with id >= 101100474, sees < 101100474
|
---TRANSACTION (0x7f5dd01c0b80), ACTIVE 19 sec
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
MariaDB thread id 37722, OS thread handle 139953932756544, query id 84737898 <ip redacted> email-queue
|
Trx read view will not see trx with id >= 101100474, sees < 101100474
|
---TRANSACTION (0x7f5dd01ce780), ACTIVE 129 sec
|
mysql tables in use 17, locked 0
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
MariaDB thread id 37714, OS thread handle 139953929070144, query id 84737782 <ip redacted> Removing duplicates
|
<query redacted>
|
Trx read view will not see trx with id >= 101100450, sees < 101100450
|
---TRANSACTION (0x7f5dd01cf280), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01d1380), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01d0880), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01c8f80), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01cc680), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01c4d80), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01cdc80), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01cbb80), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01cb080), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01c9a80), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01c5880), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01c4280), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01c6e80), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01c8480), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01ca580), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01c6380), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01cd180), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01c3780), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01c2c80), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01c7980), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
---TRANSACTION (0x7f5dd01cfd80), not started
|
0 lock struct(s), heap size 1128, 0 row lock(s)
|
--------
|
FILE I/O
|
--------
|
Pending flushes (fsync) log: 0; buffer pool: 0
|
4447274 OS file reads, 684710 OS file writes, 168651 OS fsyncs
|
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
|
-------------------------------------
|
INSERT BUFFER AND ADAPTIVE HASH INDEX
|
-------------------------------------
|
Ibuf: size 1, free list len 0, seg size 2, 0 merges
|
merged operations:
|
insert 0, delete mark 0, delete 0
|
discarded operations:
|
insert 0, delete mark 0, delete 0
|
0.00 hash searches/s, 0.00 non-hash searches/s
|
---
|
LOG
|
---
|
Log sequence number 572808604253
|
Log flushed up to 572808604253
|
Pages flushed up to 572786138602
|
Last checkpoint at 572786138602
|
0 pending log flushes, 0 pending chkp writes
|
102584 log i/o's done, 0.00 log i/o's/second
|
----------------------
|
BUFFER POOL AND MEMORY
|
----------------------
|
Total large memory allocated 85932900352
|
Dictionary memory allocated 513349304
|
Buffer pool size 5191680
|
Free buffers 685485
|
Database pages 4506195
|
Old database pages 1663401
|
Modified db pages 3345
|
Percent of dirty pages(LRU & free pages): 0.064
|
Max dirty pages percent: 90.000
|
Pending reads 0
|
Pending writes: LRU 0, flush list 0
|
Pages made young 6394386, not young 147902999
|
0.00 youngs/s, 0.00 non-youngs/s
|
Pages read 4432061, created 198405, written 564988
|
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
|
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
|
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
|
LRU len: 4506195, unzip_LRU len: 0
|
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
|
--------------
|
ROW OPERATIONS
|
--------------
|
3 read views open inside InnoDB
|
Process ID=0, Main thread ID=0, state: sleeping
|
Number of rows inserted 2079472, updated 680245, deleted 82961, read 22323994527
|
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 24417.91 reads/s
|
Number of system rows inserted 0, updated 0, deleted 0, read 0
|
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
|
----------------------------
|
END OF INNODB MONITOR OUTPUT
|
============================
|
Attachments
Issue Links
- is caused by
-
MDEV-36413 User without any privileges to a sequence can read from it and modify it via column default
-
- Closed
-
- is duplicated by
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-