MySQL常見錯(cuò)誤處理方法

原因是由于mysql對(duì)連接的客戶端進(jìn)行DNS反向解析。

注意

在增加該配置參數(shù)后，mysql的授權(quán)表中的host字段就不能夠使用域名而只能夠使用 ip地址了，因?yàn)檫@是禁止了域名解析的結(jié)果。

msyql默認(rèn)的bind-address是127.0.0.1

解決方法:bind-address后面增加遠(yuǎn)程訪問IP地址或者禁掉。

查看配置是否字符集統(tǒng)一，不統(tǒng)一根據(jù)自行調(diào)整即可。

例如：

MySQL默認(rèn)讀取執(zhí)行的SQL文件最大為16M

1 臨時(shí)解決方案：

2 更改配置項(xiàng)（my.cnf）

完整提示如下：

only_full_group_by的語義就是確定select target list中的所有列的值都是明確語義，在此模式下，target list中的值要么是來自于聚合函數(shù)（sum、avg、max等）的結(jié)果，要么是來自于group by list`中的表達(dá)式的值。

可以修改sql_mode

如果是只查詢某個(gè)字段出現(xiàn)可以使用any_value()函數(shù)來抑制ONLY_FULL_GROUP_BY值被拒絕.

原因：

max_binlog_cache_size這個(gè)參數(shù)指定了全部可以使用的binlog的cache(包括內(nèi)存和硬盤)，也就是單個(gè)事務(wù)最大允許的binlog大小，當(dāng)超出這個(gè)值時(shí)，SQL會(huì)出現(xiàn)當(dāng)前報(bào)錯(cuò)。

處理方法：

1.拆分單個(gè)SQL的影響行數(shù)進(jìn)行分批提交，控制單個(gè)SQL語句產(chǎn)生的binlog日志大小a）常規(guī)的有在SQL語句后加上limit，如每個(gè)SQL訂正影響行數(shù)limit 1000；b） 一個(gè)數(shù)據(jù)變更可以提交多個(gè)SQL，即工單仍為一個(gè)審批也僅一次；但SQL需要拆分為多條。2.如果是整個(gè)表的數(shù)據(jù)清理，可以考慮轉(zhuǎn)換為truncate table {your_table_name};

背景：

MySQL在索引變更時(shí)，支持對(duì)字符串字段進(jìn)行前綴索引設(shè)置，設(shè)置的原因主要有兩點(diǎn)：

1：MySQL對(duì)索引內(nèi)單個(gè)字段的存儲(chǔ)字節(jié)數(shù)有長(zhǎng)度767字節(jié)的限制，具體可回復(fù)關(guān)鍵詞“767字節(jié)”詳細(xì)了解2：該索引字段在實(shí)際場(chǎng)景中通過一定長(zhǎng)度的前綴數(shù)據(jù)即可進(jìn)行有效索引，不需要完整字段創(chuàng)建索引可一定程度節(jié)省索引空間。設(shè)置前綴索引報(bào)錯(cuò)的原因：

MySQL只針對(duì)varchar字符類型的字段支持，對(duì)其他數(shù)值、時(shí)間等類型是不支持的要確保類型準(zhǔn)確否則會(huì)遇到失敗。MySQL對(duì)varchar字符類型的字段定義長(zhǎng)度不能超過字段本身的定義。例如字段定義“column_a varchar(128)”定義索引是“key idx_a(column_a(129))”那會(huì)遇到失敗。

處理方法：

a) 確保非varchar類型的字段沒有設(shè)置前綴索引長(zhǎng)度。

b) 確保設(shè)置的長(zhǎng)度沒有超過列定義的長(zhǎng)度。

在做DDL變更時(shí)，遇到這個(gè)錯(cuò)誤可以檢查一下目標(biāo)字段的結(jié)構(gòu)定義長(zhǎng)度，當(dāng)前表上該字段存儲(chǔ)的內(nèi)容長(zhǎng)度已大于將要修改的字段長(zhǎng)度（一般出現(xiàn)在字段長(zhǎng)度改小的場(chǎng)景）

處理方法：

確認(rèn)表字段是否要改小長(zhǎng)度，原則上對(duì)已經(jīng)上線的表在【結(jié)構(gòu)設(shè)計(jì)】?jī)?nèi)是不支持改小長(zhǎng)度的。其他途徑改小的話需要先把表上超長(zhǎng)的數(shù)據(jù)先 DELETE掉。

由于元數(shù)據(jù)無法切換到主庫實(shí)例進(jìn)行變更或本來注冊(cè)在DMS的實(shí)例就是只讀備庫，所操作的數(shù)據(jù)庫為備庫或者開了只讀配置，無法執(zhí)行DML及DDL操作。

查詢或變更所涉及的數(shù)據(jù)字符集存在不兼容（需要的字符集大于實(shí)際支持的字符集），在數(shù)據(jù)寫入和數(shù)據(jù)查詢時(shí)都有可能遇到。

處理方法：

1）檢查確保所寫SQL無隱藏字符（一般在從其他地方拷貝SQL執(zhí)行時(shí)容易出現(xiàn)）

原因1：

遇到此類報(bào)錯(cuò)的原因是表上的字段定義和執(zhí)行的SQL存在類型不符合，常見的場(chǎng)景為表上定義是字符串類型，SQL中用了數(shù)值類型的寫法

處理方法：

保持定義一致的書寫

原因2：

遇到此類報(bào)錯(cuò)的原因表上該字段存在NULL值記錄無法直接被改為NOT NULL

處理方法：

訂正表上對(duì)應(yīng)字段數(shù)據(jù)的NULL值為某個(gè)默認(rèn)值 或者 刪掉對(duì)應(yīng)字段值為NULL的記錄

指定寫入該字段的值長(zhǎng)度超過了表結(jié)構(gòu)定義的對(duì)應(yīng)字段長(zhǎng)度；無法正確寫入導(dǎo)致了截?cái)嗟膱?bào)錯(cuò)

處理方法：

檢查表結(jié)構(gòu)定義及DML需求，確認(rèn)是調(diào)整表結(jié)構(gòu)該字段的長(zhǎng)度還是修改DML語句的字段內(nèi)容使其長(zhǎng)度滿足原有定義

innodb_online_alter_log_max_size參數(shù)是MySQL5.6.6新加入的一個(gè)參數(shù)，用以指定對(duì)InnoDB表做在線DDL操作時(shí)所使用的臨時(shí)日志文件的最大大?。ㄒ宰止?jié)為單位，默認(rèn)128M）。在創(chuàng)建索引或者ALTER表時(shí)會(huì)使用該臨時(shí)文件。該文件記錄了DDL操作期間插入、更新、刪除的數(shù)據(jù)。在必要的時(shí)候該日志文件的大小會(huì)根據(jù)innodb_sort_buffer_size的值增加容量直至達(dá)到innodb_online_alter_log_max_size指定的最大值。若臨時(shí)表的大小超出此上限則ALTER表的操作會(huì)失敗，當(dāng)前所有未提交的DML操作會(huì)回滾。因此，一個(gè)較大的值允許在線DDL操作期間有更多的DML被執(zhí)行，但是過大的值會(huì)使DDL操作結(jié)束后表被鎖定起來以應(yīng)用日志中的數(shù)據(jù)時(shí)花很長(zhǎng)的時(shí)間。

也就是說，在任務(wù)執(zhí)行過程中有過多的新增數(shù)據(jù)進(jìn)來，導(dǎo)致臨時(shí)文件放不下了，臨時(shí)修改該參數(shù)的大小SET GLOBAL innodb_online_alter_log_max_size=1280000000;

雖然InnoDB內(nèi)部支持行長(zhǎng)大于65,535字節(jié)，但是MySQL限制了所有列的組合長(zhǎng)度；

1)將表上的一些varchar大字段改類型為TEXT或者BLOB類型

2)將表上的一些varcahr大字段根據(jù)業(yè)務(wù)實(shí)際需求縮小長(zhǎng)度定義節(jié)省行長(zhǎng)

此類錯(cuò)誤分為三種：

原因：

DML數(shù)據(jù)insert、update遇到，此時(shí)是表上存在的唯一約束/索引已有對(duì)應(yīng)數(shù)據(jù)；

處理方法：

確認(rèn)唯一約束/索引的合理性、原唯一鍵值數(shù)據(jù)是否合理，若均合理的話再確認(rèn)當(dāng)前需求是否需要調(diào)整。

原因：

DDL的加唯一約束/索引、調(diào)整唯一約束/索引（包含在唯一約束/索引內(nèi)的組成字段的調(diào)整），此時(shí)要看表上調(diào)整或新增的唯一約束/索引已存在重復(fù)數(shù)據(jù)；

處理方法：

確認(rèn)唯一約束/索引的合理性，合理則需要先清理好重復(fù)數(shù)據(jù)再繼續(xù)重啟失敗的任務(wù)執(zhí)行

原因：

DDL不涉及唯一約束/索引的調(diào)整（也不涉及唯一約束/索引的組成字段的調(diào)整），此時(shí)屬于mysql的onlineDDL機(jī)制在目標(biāo)表存在高并發(fā)訪問情況下出現(xiàn)的BUG。

處理方法：

RDS實(shí)例 在業(yè)務(wù)低峰期下反復(fù)重試或者非RDS實(shí)例 可以使用無鎖數(shù)據(jù)變更，也可以請(qǐng)聯(lián)系 DBA 幫你處理。

PS：

唯一索引的沖突計(jì)算的是包含在索引定義內(nèi)的長(zhǎng)度，即假如字段定義為 "name varchar(255) "但是定義在該字段上的唯一索引定義了前綴為"uk(name(5))"，那么表上存在記錄name='abcdef.........' 再寫入name='abcdef'就會(huì)因?yàn)榍?個(gè)字符相同而失敗。

當(dāng)前數(shù)據(jù)庫實(shí)例版本限制了 log_bin_use_old_datetime_format=on；此時(shí)不能定義datetime類型增加默認(rèn)值。

處理方法：

原因1

如果was XXX milliseconds ago的XXX是0，那么意味數(shù)據(jù)庫連不上了?？赡艿脑蛴袃蓚€(gè)：1、數(shù)據(jù)庫發(fā)生了遷移，原數(shù)據(jù)源地址不可用 2、數(shù)據(jù)庫掛了。

處理方法：

1、確認(rèn)數(shù)據(jù)源是否已存在，或者發(fā)生配置更新

2、檢查數(shù)據(jù)庫本身是否異常導(dǎo)致不可用直接聯(lián)系DBA確認(rèn)。

原因2：

如果was XXX milliseconds ago的XXX是大于0的一個(gè)值，那么當(dāng)前執(zhí)行的SQL可能被數(shù)據(jù)庫KILL掉了。

處理方法：

直接聯(lián)系你的DBA確認(rèn)數(shù)據(jù)庫情況。如果數(shù)據(jù)庫是ob類型，并且XXX約等于30S，請(qǐng)告訴你的DBA集群信息，他會(huì)對(duì)數(shù)據(jù)庫進(jìn)行擴(kuò)容。

mysql的“字符串類型”(varchar、char等)字段作為索引時(shí)，有一個(gè)約束單個(gè)索引字段存儲(chǔ)長(zhǎng)度不能超過767字節(jié)。

按照表為utf8mb4字符集時(shí)，一個(gè)字符需要4個(gè)字節(jié)存儲(chǔ)。那么最大定義索引前綴為 767/4=191.即字段a varchar(500)要建立索引時(shí)需要定義前綴索引 a(191)，不能超過191的一個(gè)值。

處理方法：

utf8為3個(gè)字節(jié)存儲(chǔ)一個(gè)字符，gbk為2個(gè)字節(jié)存儲(chǔ)一個(gè)字符，依次類推得到對(duì)應(yīng)字符串類型字段的前綴索引長(zhǎng)度修正即可。結(jié)構(gòu)設(shè)計(jì)修改路徑：索引=》包含列=》前綴長(zhǎng)度，進(jìn)行設(shè)置。

如果是【庫表同步】請(qǐng)直接聯(lián)系你的DBA修改為和【源】數(shù)據(jù)庫一致的編碼。

當(dāng)前實(shí)例不允許當(dāng)前執(zhí)行的操作。多數(shù)為主備角色錯(cuò)誤導(dǎo)致不可讀、或不可寫。

處理方法：

此類錯(cuò)誤一般情況下在10分鐘內(nèi)會(huì)自動(dòng)修復(fù)，請(qǐng)?jiān)?0分鐘后重試執(zhí)行任務(wù)即可。如果超時(shí)10分鐘仍然不成功，請(qǐng)?zhí)峁┕蝘d、對(duì)應(yīng)數(shù)據(jù)庫的連接串文本信息，直接聯(lián)系對(duì)應(yīng)業(yè)務(wù)DBA同學(xué)確認(rèn)是否有運(yùn)維操作導(dǎo)致主備角色的改變。

均為實(shí)例宕機(jī)引起。

處理方法：

此類錯(cuò)誤一般情況下在10分鐘內(nèi)會(huì)自動(dòng)修復(fù)，請(qǐng)?jiān)?0分鐘后重試執(zhí)行任務(wù)即可。如果超時(shí)10分鐘仍然不成功，直接聯(lián)系對(duì)應(yīng)業(yè)務(wù)DBA同學(xué)確認(rèn)。

多出現(xiàn)在日常庫，業(yè)務(wù)同學(xué)調(diào)試或者代碼有缺陷導(dǎo)致鏈接被占滿。

處理方法：

如果本地有調(diào)試，或者測(cè)試環(huán)境有代碼缺陷，可以嘗試把連上這個(gè)DB的服務(wù)重啟，這樣服務(wù)端就會(huì)釋放掉一些鏈接。服務(wù)重啟仍然不解決問題，直接聯(lián)系對(duì)應(yīng)業(yè)務(wù)DBA同學(xué)kill掉服務(wù)器上的鏈接或者重啟DB。

報(bào)這個(gè)錯(cuò)誤表示列已經(jīng)存在了。

均為實(shí)例宕機(jī)引起。

處理方法：

下一篇：MySQL數(shù)據(jù)庫“十宗罪”十大經(jīng)典錯(cuò)誤案例