MySQL이 ALTER 탭에 행업하다Leep.

MySQL이 ALTER 탭에 행업하다Leep.

별로 크지 않은 내 테이블은 ALTER 명령에 걸려 있다.무슨 일이지?

총계 142 MByte의 42개 필드, 15k 행만.InnoDB 스토리지 엔진 및 서버 버전: 5.5.44-MariaDB MariaDB Server. 1 필드, 'slotindex'가 기본 키: bigint(20) 및 BTREE 유형입니다.

명령어는 다음과 같습니다.

    MariaDB [mydb]> ALTER TABLE `runs` CHANGE `p_w_trans_x` `p_w_tran_x` FLOAT NOT NULL;
    Stage: 1 of 2 'copy to tmp table'   65.7% of stage done
    Stage: 2 of 2 'Enabling keys'      0% of stage done

이 스테이지 2에서는 영원히 매달릴 것이다.

프로세스 리스트는 다음과 같습니다.

MariaDB [(none)]> show full processlist;
+--------+------+-----------------+-----------+---------+-------+---------------------------------+---------------------------------------------------------------------+----------+
| Id     | User | Host            | db        | Command | Time  | State                           | Info                                                                | Progress |
+--------+------+-----------------+-----------+---------+-------+---------------------------------+---------------------------------------------------------------------+----------+
| 274226 | root | localhost:45423 | edc_proxy | Sleep   | 16043 |                                 | NULL                                                                |    0.000 |
| 274319 | root | localhost       | myDB      | Query   |    99 | Waiting for table metadata lock | ALTER TABLE `runs` CHANGE `p_w_trans_x` `p_w_tran_x` FLOAT NOT NULL |    0.000 |
| 274416 | root | localhost       | NULL      | Query   |     0 | NULL                            | show full processlist                                               |    0.000 |
+--------+------+-----------------+-----------+---------+-------+---------------------------------+---------------------------------------------------------------------+----------+

이 답변에서는 information_schema 테이블을 체크할 것을 제안하고 있습니다.

MariaDB [INFORMATION_SCHEMA]> SELECT * FROM INNODB_LOCK_WAITS;
Empty set (0.00 sec)

MariaDB [INFORMATION_SCHEMA]> SELECT * FROM INNODB_LOCKS ;
Empty set (0.00 sec)

MariaDB [INFORMATION_SCHEMA]> SELECT * FROM INNODB_TRX;
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| trx_id   | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout |
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| 83A8B36E | RUNNING   | 2016-12-08 11:13:02 | NULL                  | NULL             |          0 |              274226 | NULL      | NULL                |                 0 |                 0 |                0 |                   376 |               0 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                     10000 |
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)

그리고 거래 섹션은show engine innodb status;:

------------
TRANSACTIONS
------------
Trx id counter 83A8F071
Purge done for trx's n:o < 83A8CA86 undo n:o < 0
History list length 1490
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 274543, OS thread handle 0x7fbb863e6700, query id 85356480 localhost root
show engine innodb status
---TRANSACTION 83A8EB07, not started
mysql tables in use 1, locked 2
MySQL thread id 274542, OS thread handle 0x7fbb843f6700, query id 85354935 localhost root Waiting for table metadata lock
ALTER TABLE `runs` CHANGE `p_w_trans_x` `p_w_tran_x` FLOAT NOT NULL
---TRANSACTION 83A8B36E, ACTIVE 24627 sec
MySQL thread id 274226, OS thread handle 0x7fbb845f5700, query id 85337236 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 83A8B36F, sees < 83A8B36D
----------------------------
END OF INNODB MONITOR OUTPUT
============================

추가 조사, 문제 회피 및 문제 해결을 위한 조언은 모두 감사합니다!

메타데이터 잠금은 (사용자의 관점에서) 암묵적인 잠금으로 테이블에 대한 DDL을 방지합니다.이것은 다른 무언가가 테이블을 현재 형태로 유지해야 하기 때문입니다.이 경우 실행 중인 트랜잭션입니다.

작업 1: 스레드 274226의 연결을 끊으면 변경 작업이 성공합니다.

mysql> KILL 274226;

여기서의 문제는 information_schema.innodb_trx에서 알 수 있듯이 이 스레드가 몇 시간 동안 트랜잭션을 실행한 상태로 유지되고 있으며 이 테이블이 해당 트랜잭션에 의해 참조된 것으로 추측할 수 있습니다.MVCC 보기 또는 테이블과 관련된 잠금이 아직 남아 있지 않을 때까지 테이블을 변경할 수 없습니다.이 트랜잭션에는 뷰가 있으며, 마지막 줄에서 보듯이 이 표에 영향을 줄 수 있습니다.

--TRANSACTION 83A8B36E, ACTIVE 24627 sec
MySQL thread id 274226, OS thread handle 0x7fbb845f5700, query id 85337236 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 83A8B36F, sees < 83A8B36D

주의:Sleep는 실제 명령어가 아닙니다.이 문맥에서는 아이돌 접속의 플레이스 홀더 상태일 뿐입니다.모든 접속이 동작하고 있습니다.이 경우 "something"은 sleep 상태입니다.즉, 아이돌 상태이며 다른 쿼리를 기다립니다.그러나 유휴 연결은 여전히 연결이며, 코드(또는 쿼리 브라우저 도구)가 트랜잭션을 실행 중인 상태로 두면 계속 실행됩니다.

태스크 2: 트랜잭션을 실행시킨 오류 또는 오류를 찾습니다.실제 애플리케이션에서는 트랜잭션을 실행 상태로 두면 더 큰 혼란이 발생할 수 있습니다.

테이블 잠금도 비슷한 문제가 있었지만 결국 MySQL Workbench가 되었습니다.조금도RENAME TABLE또는ALTER TABLEMySQL Workbench의 명령어는 메타 잠금을 요구하기 위해 그대로 있습니다.저는 서버에 접속하여 그러한 종류의 쿼리를 문제없이 실행할 수 있었습니다.Workbench의 현재 버전은8.0.22.

나는 이 모든 질문들을 꼼꼼히 살펴봤지만 아무런 문제가 없을 때 완전히 당황했다.

SHOW OPEN TABLES;

SHOW ENGINE inndodb STATUS;

SELECT * FROM INNODB_LOCK_WAITS;

SELECT * FROM INNODB_LOCKS;

SELECT * FROM INNODB_TRX;

SHOW FULL PROCESSLIST;

언급URL : https://stackoverflow.com/questions/41051284/mysql-hangs-on-alter-table