一.问题描述
拷贝一个大表的表数据的时候，等待时间太久，就在前台通过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>
通过show processlist查找到对应的进程，然后进行kill，结果kill完了，依旧在进程列表里，只是被标记为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>
二.解决方案
查看innodb的事务表，发现刚才kill的语句正在进行回滚操作。
trx_rows_modified 代表锁影响的行数，当数值为0时，锁将会释放。
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 #代表锁影响的行数，当数值为0时，锁将会释放
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)
最好的办法就是等待innodb自己将事务进行回滚，除此之外，也可以innodb_force_recovery=3不执行事务回滚操作 启动数据库 (慎用)
INNODB_TRX表列的备注:
desc innodb_trx ;
+—————————-+———————+——+—–+———————+——-+
| Field | Type | Null | Key | Default | Extra |
+—————————-+———————+——+—–+———————+——-+
| trx_id | varchar(18) | NO | | | |#事务ID
| trx_state | varchar(13) | NO | | | |#事务状态：
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事务开始时间；
| trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id
| trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间
| trx_weight | bigint(21) unsigned | NO | | 0 | |#
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事务线程ID
| trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句
| trx_operation_state | varchar(64) | YES | | NULL | |#事务当前操作状态
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事务中有多少个表被使用
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事务拥有多少个锁
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事务锁住的内存大小（B）
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事务锁住的行数
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事务更改的行数
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事务并发票数
| trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别
| trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查
| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最后的外键错误
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#
+—————————-+———————+——+—–+———————+——-+
————————————————
三.额外的一个报错
在回滚的过程中，错误日志报错了:
-------------------------------------
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
从报错日志来看，是mysql实例出现了问题，看来是回滚的时候出现了异常，然后报错了。
[root@hp2 ~]# service mysqld restart
Shutting down MySQL...................................................................................................................................................................................................................................................................................................................................^C
[root@hp2 ~]#
[root@hp2 ~]#
只能强制杀进程了
[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 ~]#
我以为强制杀进程后，启动mysql就没问题了，结果登陆不上，错误日志报错如下:
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
杀掉所有mysql相关的进程，然后启动mysql服务
[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 ~]#
四.启用innodb_force_recovery=3
通过第三步，我将mysql服务正常启动，但是依旧无法创建表
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
此时查看进程，没发现异常
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)
然后查看innodb的事务表 innodb_trx:
连着查询了几次，trx_rows_modified的值没有发生变化，初步判断是上次mysql实例异常crash后出现了问题。
从performance_schema.data_locks中可以看到锁的都是系统的表。
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)
修改配置文件，然后重启mysqld服务
innodb_force_recovery=3
结果问题依旧,只能删除innodb_force_recovery=3参数，然后重新启动mysql服务
ps aux |grep mysql*
kill -9 pid1 pid2
service mysqld start
这次启动mysql服务花了一点时间，因为是回滚完成后才启动mysql的服务。
[root@hp2 ~]# service mysqld start
Starting MySQL................................................ SUCCESS!
这次，终于正常了:
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)
网友评论