2

I'm trying to ALTER TABLE on a table with 3 million rows, on a local development database on my PC. No other queries are running at the time, in fact i tried it just after restart. The query runs with "copying to tmp table": enter image description here and in about 20 minutes, the query fails with

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Which is strange, since this is the only query running I see in process list.

I tried to set transaction-isolation = READ-COMMITTED, i tried with DISABLE KEY before ALTER statement. innodb_lock_wait_timeout is 10.

I'm really puzzled with this. I don't want to drop the table or delete records as this is one of the main tables and there is lots of foreign keys defined on it.

mysql -V
mysql  Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using  EditLine wrapper

This is fairly similar to this post, but no reason was found there.

Here is SHOW ENGINE INNODB STATUS:

=====================================
2021-04-02 12:57:50 0x7f11ec24a700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 21 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1054 srv_active, 0 srv_shutdown, 2955 srv_idle
srv_master_thread log flush and writes: 4009
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 84139
OS WAIT ARRAY INFO: signal count 73393
RW-shared spins 0, rounds 122971, OS waits 11295
RW-excl spins 0, rounds 4287078, OS waits 8693
RW-sx spins 1940, rounds 6499, OS waits 122
Spin rounds per wait: 122971.00 RW-shared, 4287078.00 RW-excl, 3.35 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2838178
Purge done for trx's n:o < 2838178 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421190241873552, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421190241872632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421190241871712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
4419208 OS file reads, 5061062 OS file writes, 383134 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.14 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3093, seg size 3095, 39551 merges
merged operations:
 insert 1871387, delete mark 14, delete 11
discarded operations:
 insert 93817, delete mark 0, delete 0
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 74791595141
Log flushed up to   74791595141
Pages flushed up to 74791595141
Last checkpoint at  74791595132
0 pending log flushes, 0 pending chkp writes
14348 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 4328873
Buffer pool size   8191
Free buffers       1024
Database pages     7159
Old database pages 2622
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 215329, not young 11158813
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4419104, created 145522, written 4838659
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: 7159, unzip_LRU len: 0
I/O sum[25]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1571, Main thread ID=139714966587136, state: sleeping
Number of rows inserted 3192025, updated 0, deleted 0, read 3193018
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

I don't understand why this is happening. I already created a copy and transferred records to new table so I can continue with my development. But this seem like a bug to me in MySQL.

I was adding a new column to the table, and it was exactly this error, as I was running this statement from console.

1
  • You should probably change in your configuration innodb_lock_wait_timeout back to the default of 50 (seconds) to give your machine a chance with this huge data table. And post the last 400 lines of your error log after getting timeout exceeded message, please. Consider ANALYZE TABLE tbl_name; prior to attempt to add column to ensure indexes are a fresh rebuild. Sep 10 at 5:36
0

Answered by Rick James in a comment:

MySQL 8.0 with ALTER TABLE ... ALGORITHM=INSTANT may allow ADD COLUMN.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.