首页 > 代码库 > 数据库修改字段导致宕机
数据库修改字段导致宕机
170614 23:28:56 [ERROR] Slave SQL: Error ‘Got error 64 ‘Temp file write failure‘ from InnoDB‘ on query. Default database: ‘loandb‘. Query: ‘ALTER TABLE ‘trd_loanapply DROP COLUMN LAP_SIGNRATE , Internal MariaDB error code: 1296
170614 23:28:56 [Warning] WSREP: RBR event 1 Query apply warning: 1, 127029889 –删除字段事物ID
170614 23:28:56 [Warning] WSREP: Ignoring error for TO isolated action: source: a2a9f28c-473e-11e7-828b-336b93b44447 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 150694279 trx_id: -1 seqnos (l: 128890633, g: 127029889, s: 127029888, d: 127029888, ts: 24153881134314845)
170614 23:30:01 [ERROR] Slave SQL: Column 38 of table ‘loandb.trd_loanapply‘ cannot be converted from type ‘decimal(18,5)‘ to type ‘decimal(8,5)‘, Internal MariaDB error code: 1677
170614 23:30:01 [ERROR] Slave SQL: Column 38 of table ‘loandb.trd_loanapply‘ cannot be converted from type ‘decimal(18,5)‘ to type ‘decimal(8,5)‘, Internal MariaDB error code: 1677
170614 23:30:01 [Warning] WSREP: RBR event 2 Update_rows_v1 apply warning: 3, 127029985 --修改字段宽度事物ID
170614 23:30:01 [Warning] WSREP: RBR event 2 Update_rows_v1 apply warning: 3, 127029978 --修改字段宽度事物ID
170614 23:30:01 [Warning] WSREP: Failed to apply app buffer: seqno: 127029978, status: 1
at galera/src/trx_handle.cpp:apply():351
Retrying 2th time
170614 23:30:01 [Warning] WSREP: Failed to apply app buffer: seqno: 127029985, status: 1
at galera/src/trx_handle.cpp:apply():351
Retrying 2th time
170614 23:30:01 [ERROR] Slave SQL: Column 38 of table ‘loandb.trd_loanapply‘ cannot be converted from type ‘decimal(18,5)‘ to type ‘decimal(8,5)‘, Internal MariaDB error code: 1677
170614 23:30:01 [Warning] WSREP: RBR event 2 Update_rows_v1 apply warning: 3, 127029978
170614 23:30:01 [ERROR] Slave SQL: Column 38 of table ‘loandb.trd_loanapply‘ cannot be converted from type ‘decimal(18,5)‘ to type ‘decimal(8,5)‘, Internal MariaDB error code: 1677
170614 23:30:01 [Warning] WSREP: RBR event 2 Update_rows_v1 apply warning: 3, 127029985
170614 23:30:01 [Warning] WSREP: Failed to apply app buffer: seqno: 127029978, status: 1
at galera/src/trx_handle.cpp:apply():351
Retrying 3th time
170614 23:30:01 [ERROR] Slave SQL: Column 38 of table ‘loandb.trd_loanapply‘ cannot be converted from type ‘decimal(18,5)‘ to type ‘decimal(8,5)‘, Internal MariaDB error code: 1677
170614 23:30:01 [ERROR] Slave SQL: Column 38 of table ‘loandb.trd_loanapply‘ cannot be converted from type ‘decimal(18,5)‘ to type ‘decimal(8,5)‘, Internal MariaDB error code: 1677
170614 23:30:01 [ERROR] WSREP: Failed to apply trx 127029978 4 times –修改字段宽度事物ID
170614 23:30:01 [ERROR] WSREP: Failed to apply trx 127029985 4 times –修改字段宽度事物ID
170614 23:30:01 [ERROR] WSREP: Node consistency compromized, aborting...
170614 23:30:01 [ERROR] WSREP: Node consistency compromized, aborting...
170614 23:30:01 [Note] WSREP: Closing send monitor...
170614 23:30:01 [Note] WSREP: /usr/local/mysql/bin/mysqld: Terminated. --导致数据库宕机
170614 23:30:02 mysqld_safe Number of processes running now: 0
170614 23:30:02 mysqld_safe WSREP: not restarting wsrep node automatically
170614 23:30:02 mysqld_safe mysqld from pid file /mysql/mysqld.pid ended
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
分析上面的日志信息：---------------------------------------------------------------------------------------
删除字段的操作对应的事物ID：127029889修改字段宽度的操作的事物ID：127029978 、127029985
---------------------------------------------------------------------------------------
导致数据库宕机的事物是：127029978 、127029985
--------------------------------------------------------------------------------------
说明：1. 我们的上线脚本里是没有修改列宽的操作，并且也和XXX确认过最近没有修改列宽的操作，所以本次宕机和数据库上线操作无关。2. 本着优先保障业务正常运行的原则，我们优先对数据库进行和恢复，并对相关联的从节点进行了修复。3. 并在当晚将数据库临时文件系统指向大的文件系统，避免再次产生临时文件系统不足现象。
本文出自 “晴空” 博客，谢绝转载！
数据库修改字段导致宕机