I Problem description
When copying the table data of a large table, the waiting time is too long, and it stops at the foreground by CTRL+C.
mysql> create table fact_sale_new as select * from fact_sale; ^C^C -- query aborted ^C^C -- query aborted ERROR 2013 (HY000): Lost connection to MySQL server during query No connection. Trying to reconnect... Connection id: 16 Current database: test ^C^C -- query aborted ^C^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql>
Find the corresponding process through the show processlist, and then kill it. The result is that the process is killed and still in the process list, but it is marked as killed
mysql> show processlist; +----+-----------------+-----------+------+---------+-------+------------------------+-------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+-------+------------------------+-------------------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 13702 | Waiting on empty queue | NULL | | 8 | root | localhost | test | Query | 3760 | System lock | create table fact_sale_new as select * from fact_sale | | 10 | root | localhost | test | Query | 0 | init | show processlist | +----+-----------------+-----------+------+---------+-------+------------------------+-------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> mysql> kill 8; Query OK, 0 rows affected (0.00 sec) mysql> mysql> show processlist; +----+-----------------+-----------+------+------------+-------+-------------------------+-------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+------------+-------+-------------------------+-------------------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 13712 | Waiting on empty queue | NULL | | 8 | root | localhost | test | Killed | 3770 | System lock | create table fact_sale_new as select * from fact_sale | | 10 | root | localhost | test | Query | 0 | init | show processlist | | 16 | root | localhost | test | Field List | 2 | Waiting for table flush | NULL | +----+-----------------+-----------+------+------------+-------+-------------------------+-------------------------------------------------------+ 4 rows in set (0.00 sec) mysql>
II Solution
Check the transaction table of innodb and find that the kill statement is being rolled back.
trx_rows_modified represents the number of rows affected by the lock. When the value is 0, the lock will be released.
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 71735
trx_state: ROLLING BACK
trx_started: 2021-06-03 14:17:40
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 462200354
trx_mysql_thread_id: 8
trx_query: create table fact_sale_new as select * from fact_sale
trx_operation_state: rollback of SQL statement
trx_tables_in_use: 1
trx_tables_locked: 9
trx_lock_structs: 1370030
trx_lock_memory_bytes: 234823888
trx_rows_locked: 502015315
trx_rows_modified: 460830324 #Represents the number of rows affected by the lock. When the value is 0, the lock will be released
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
trx_schedule_weight: NULL
1 row in set (0.00 sec)
The best way is to wait for innodb to roll back the transaction. In addition, you can also use innodb_force_recovery=3 start the database without executing transaction rollback (use with caution)
INNODB_ Remarks in TRX table:
desc innodb_trx ; +----—-+---—+-+—–+---—+--+ | Field | Type | Null | Key | Default | Extra | +----—-+---—+-+—–+---—+--+ | trx_id | varchar(18) | NO | | | |#Transaction ID | trx_state | varchar(13) | NO | | | |#Transaction status: | trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#Transaction start time; | trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id | trx_wait_started | datetime | YES | | NULL | |#Time the transaction started waiting | trx_weight | bigint(21) unsigned | NO | | 0 | |# | trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#Transaction thread ID | trx_query | varchar(1024) | YES | | NULL | |#Specific SQL statement | trx_operation_state | varchar(64) | YES | | NULL | |#Current operation state of transaction | trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#How many tables are used in a transaction | trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#How many locks does the transaction have | trx_lock_structs | bigint(21) unsigned | NO | | 0 | |# | trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#Memory size locked by transaction | trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#Number of rows locked by the transaction | trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#Number of rows changed by the transaction | trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#Number of transaction invoices | trx_isolation_level | varchar(16) | NO | | | |#Transaction isolation level | trx_unique_checks | int(1) | NO | | 0 | |#Uniqueness check | trx_foreign_key_checks | int(1) | NO | | 0 | |#Foreign key check | trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#Last foreign key error | trx_adaptive_hash_latched | int(1) | NO | | 0 | |# | trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |# +----—-+---—+-+—–+---—+--+ --------
III An additional error is reported
During rollback, an error is reported in the error log:
------------------------------------- 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 Hash table size 138401, node heap has 0 buffer(s) Hash table size 138401, node heap has 0 buffer(s) Hash table size 138401, node heap has 0 buffer(s) Hash table size 138401, node heap has 0 buffer(s) Hash table size 138401, node heap has 1 buffer(s) Hash table size 138401, node heap has 0 buffer(s) Hash table size 138401, node heap has 1 buffer(s) Hash table size 138401, node heap has 1 buffer(s) 173.64 hash searches/s, 108167.44 non-hash searches/s --- LOG --- Log sequence number 338551704131 Log buffer assigned up to 338551704131 Log buffer completed up to 338551704131 Log written up to 338551704131 Log flushed up to 338551704131 Added dirty pages up to 338551704131 Pages flushed up to 338547869736 Last checkpoint at 338547869736 84290665 log i/o's done, 3753.20 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 548143104 Dictionary memory allocated 491209 Buffer pool size 32768 Free buffers 1026 Database pages 3896 Old database pages 1418 Modified db pages 246 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages made young 32417, not young 1432606662 0.00 youngs/s, 0.00 non-youngs/s Pages read 8962186, created 4242134, written 5485858 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 348 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 3896, unzip_LRU len: 0 I/O sum[21127]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue
From the error log, there is a problem with the mysql instance. It seems that there is an exception during rollback, and then an error is reported.
[root@hp2 ~]# service mysqld restart Shutting down MySQL...................................................................................................................................................................................................................................................................................................................................^C [root@hp2 ~]# [root@hp2 ~]#
We can only force the process to kill
[root@hp2 ~]# ps -ef | grep mysqld root 25761 1 0 11:31 pts/2 00:00:00 /bin/sh /home/mysql8/mysql/bin/mysqld_safe --datadir=/home/mysql8/mysql/data --pid-file=/home/mysql8/mysql/data/hp2.pid mysql 26015 25761 66 11:31 pts/2 03:21:22 /home/mysql8/mysql/bin/mysqld --basedir=/home/mysql8/mysql --datadir=/home/mysql8/mysql/data --plugin-dir=/home/mysql8/mysql/lib/plugin --user=mysql --log-error=/home/mysql8/mysql/mysql_error.log --pid-file=/home/mysql8/mysql/data/hp2.pid --port=3306 root 27307 26545 0 16:35 pts/5 00:00:00 grep --color=auto mysqld [root@hp2 ~]# [root@hp2 ~]# [root@hp2 ~]# kill -9 26015 [root@hp2 ~]# [root@hp2 ~]# ps -ef | grep mysqld root 27317 26545 0 16:35 pts/5 00:00:00 grep --color=auto mysqld [root@hp2 ~]# [root@hp2 ~]# [root@hp2 ~]# service mysqld start Starting MySQL SUCCESS! [root@hp2 ~]#
I thought there was no problem starting mysql after the forced killing process. As a result, I couldn't log in. The error log is as follows:
3 4 5 62021-06-03T08:37:45.806646Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release. 2021-06-03T08:37:45.808695Z 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory. 2021-06-03T08:37:45.808797Z 0 [System] [MY-010116] [Server] /home/mysql8/mysql/bin/mysqld (mysqld 8.0.25) starting as process 27941 2021-06-03T08:37:45.810327Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 2021-06-03T08:37:45.819286Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2021-06-03T08:37:45.879341Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:37:46.879804Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 72021-06-03T08:37:47.880540Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:37:48.881364Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:37:49.882210Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:37:50.883154Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:37:51.884083Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 82021-06-03T08:37:52.885124Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:37:53.885796Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:37:54.886681Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:37:55.887675Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 92021-06-03T08:37:56.888684Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:37:57.889721Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:37:58.890804Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:37:59.891943Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 102021-06-03T08:38:00.893099Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:38:01.894232Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:38:02.895428Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:38:03.896638Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 112021-06-03T08:38:04.897903Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:38:05.899242Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:38:06.900686Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:38:07.902096Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 122021-06-03T08:38:08.903490Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:38:09.904931Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:38:10.906337Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:38:11.907818Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 132021-06-03T08:38:12.909311Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:38:13.910935Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:38:14.912520Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 2021-06-03T08:38:15.914099Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11 142021-06-03T08:38:16.915254Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
Kill all mysql related processes, and then start the mysql service
[root@hp2 ~]# ps aux |grep mysql* root 27337 0.0 0.0 11828 1620 pts/5 S 16:35 0:00 /bin/sh /home/mysql8/mysql/bin/mysqld_safe --datadir=/home/mysql8/mysql/data --pid-file=/home/mysql8/mysql/data/hp2.pid mysql 27588 81.1 10.9 1749344 874532 pts/5 Sl 16:35 3:54 /home/mysql8/mysql/bin/mysqld --basedir=/home/mysql8/mysql --datadir=/home/mysql8/mysql/data --plugin-dir=/home/mysql8/mysql/lib/plugin --user=mysql --log-error=/home/mysql8/mysql/mysql_error.log --pid-file=/home/mysql8/mysql/data/hp2.pid --port=3306 root 28252 0.0 0.0 113560 1756 pts/5 S+ 16:39 0:00 /bin/sh /usr/sbin/service mysqld restart root 28259 0.0 0.0 11688 1480 pts/5 S+ 16:39 0:00 /bin/sh /etc/init.d/mysqld restart root 28275 0.1 0.0 11692 1544 pts/5 S+ 16:39 0:00 /bin/sh /etc/init.d/mysqld start root 28283 0.0 0.0 11824 1600 pts/5 S+ 16:39 0:00 /bin/sh /home/mysql8/mysql/bin/mysqld_safe --datadir=/home/mysql8/mysql/data --pid-file=/home/mysql8/mysql/data/hp2.pid mysql 28543 0.6 3.5 1128632 284168 pts/5 Sl+ 16:39 0:00 /home/mysql8/mysql/bin/mysqld --basedir=/home/mysql8/mysql --datadir=/home/mysql8/mysql/data --plugin-dir=/home/mysql8/mysql/lib/plugin --user=mysql --log-error=/home/mysql8/mysql/mysql_error.log --pid-file=/home/mysql8/mysql/data/hp2.pid --port=3306 root 28736 0.0 0.0 112824 992 pts/1 S+ 16:40 0:00 grep --color=auto mysql* [root@hp2 ~]# [root@hp2 ~]# [root@hp2 ~]# kill -9 27337 27588 28252 28259 28275 28283 28543 [root@hp2 ~]# [root@hp2 ~]# [root@hp2 ~]# ps aux |grep mysql* root 28832 0.0 0.0 112824 992 pts/1 S+ 16:41 0:00 grep --color=auto mysql* [root@hp2 ~]# [root@hp2 ~]# [root@hp2 ~]# service mysqld start Starting MySQL.......... SUCCESS! [root@hp2 ~]# [root@hp2 ~]# [root@hp2 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.25 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit Bye [root@hp2 ~]# [root@hp2 ~]#
IV Enable innodb_force_recovery=3
Through the third step, I started the mysql service normally, but still failed to create the table
mysql> CREATE TABLE `fact_sale_new` (
-> `id` bigint NOT NULL AUTO_INCREMENT,
-> `sale_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> `prod_name` varchar(200) NOT NULL,
-> `sale_nums` int DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
At this time, check the process and find no exceptions
mysql> SHOW FULL PROCESSLIST; +----+-----------------+-----------+------+---------+------+------------------------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+------+------------------------+-----------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 138 | Waiting on empty queue | NULL | | 8 | root | localhost | test | Query | 0 | init | SHOW FULL PROCESSLIST | +----+-----------------+-----------+------+---------+------+------------------------+-----------------------+ 2 rows in set (0.00 sec)
Then view the transaction table of innodb_trx:
Several times in a row, TRX_ rows_ The value of modified has not changed. It is preliminarily judged that there was a problem after the last mysql instance crash ed abnormally.
From performance_ schema. data_ In locks, you can see that all the locks are system tables.
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 71735
trx_state: RUNNING
trx_started: 2021-06-03 17:26:06
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 106524135
trx_mysql_thread_id: 0
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 7
trx_lock_structs: 8
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 106524127
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
trx_schedule_weight: NULL
1 row in set (0.00 sec)
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140421015241616:5:140420935904192
ENGINE_TRANSACTION_ID: 71735
THREAD_ID: 2
EVENT_ID: 1
OBJECT_SCHEMA: mysql
OBJECT_NAME: innodb_ddl_log
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904192
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140421015241616:12:140420935904280
ENGINE_TRANSACTION_ID: 71735
THREAD_ID: 2
EVENT_ID: 1
OBJECT_SCHEMA: mysql
OBJECT_NAME: columns
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904280
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140421015241616:16:140420935904368
ENGINE_TRANSACTION_ID: 71735
THREAD_ID: 2
EVENT_ID: 1
OBJECT_SCHEMA: mysql
OBJECT_NAME: index_column_usage
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904368
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140421015241616:19:140420935904456
ENGINE_TRANSACTION_ID: 71735
THREAD_ID: 2
EVENT_ID: 1
OBJECT_SCHEMA: mysql
OBJECT_NAME: indexes
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904456
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140421015241616:29:140420935904544
ENGINE_TRANSACTION_ID: 71735
THREAD_ID: 2
EVENT_ID: 1
OBJECT_SCHEMA: mysql
OBJECT_NAME: tables
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904544
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140421015241616:30:140420935904632
ENGINE_TRANSACTION_ID: 71735
THREAD_ID: 2
EVENT_ID: 1
OBJECT_SCHEMA: mysql
OBJECT_NAME: tablespace_files
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904632
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 7. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140421015241616:31:140420935904720
ENGINE_TRANSACTION_ID: 71735
THREAD_ID: 2
EVENT_ID: 1
OBJECT_SCHEMA: mysql
OBJECT_NAME: tablespaces
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904720
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 8. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140421015241616:18446744069414584331:140420935904808
ENGINE_TRANSACTION_ID: 71735
THREAD_ID: 2
EVENT_ID: 1
OBJECT_SCHEMA:
OBJECT_NAME: SDI_11
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904808
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 9. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140421015241616:4294967294:1043:179:140420935901088
ENGINE_TRANSACTION_ID: 71735
THREAD_ID: 45
EVENT_ID: 14
OBJECT_SCHEMA: mysql
OBJECT_NAME: tables
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: schema_id
OBJECT_INSTANCE_BEGIN: 140420935901088
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 5, 'fact_sale_new', 369
*************************** 10. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140421015245040:29:140420935928832
ENGINE_TRANSACTION_ID: 72198
THREAD_ID: 45
EVENT_ID: 19
OBJECT_SCHEMA: mysql
OBJECT_NAME: tables
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935928832
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 11. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140421015245040:4294967294:1043:179:140420935925728
ENGINE_TRANSACTION_ID: 72198
THREAD_ID: 45
EVENT_ID: 19
OBJECT_SCHEMA: mysql
OBJECT_NAME: tables
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: schema_id
OBJECT_INSTANCE_BEGIN: 140420935925728
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 5, 'fact_sale_new', 369
11 rows in set (0.01 sec)
Modify the configuration file and restart mysqld service
innodb_force_recovery=3
As a result, the problem remains the same. Only InnoDB can be deleted_ force_ Recovery = 3 parameter, and then restart the mysql service
ps aux |grep mysql* kill -9 pid1 pid2 service mysqld start
It took some time to start the mysql service this time, because the mysql service was started only after the rollback was completed.
[root@hp2 ~]# service mysqld start Starting MySQL................................................ SUCCESS!
This time, it's finally normal:
mysql> CREATE TABLE `fact_sale_new` (
-> `id` bigint NOT NULL AUTO_INCREMENT,
-> `sale_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> `prod_name` varchar(200) NOT NULL,
-> `sale_nums` int DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
reference resources:
- https://blog.csdn.net/m0_37827567/article/details/82979767
- https://blog.csdn.net/zhang123456456/article/details/72854467