MySQL常見錯誤處理方法

原因是由于mysql對連接的客戶端進行DNS反向解析。

注意

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

msyql默認的bind-address是127.0.0.1

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

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

例如：

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

1 臨時解決方案：

2 更改配置項（my.cnf）

完整提示如下：

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

可以修改sql_mode

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

原因：

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

處理方法：

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

背景：

MySQL在索引變更時，支持對字符串字段進行前綴索引設置，設置的原因主要有兩點：

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

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

處理方法：

a) 確保非varchar類型的字段沒有設置前綴索引長度。

b) 確保設置的長度沒有超過列定義的長度。

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

處理方法：

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

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

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

處理方法：

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

原因1：

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

處理方法：

保持定義一致的書寫

原因2：

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

處理方法：

訂正表上對應字段數(shù)據(jù)的NULL值為某個默認值 或者 刪掉對應字段值為NULL的記錄

指定寫入該字段的值長度超過了表結(jié)構定義的對應字段長度；無法正確寫入導致了截斷的報錯

處理方法：

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

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

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

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

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

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

此類錯誤分為三種：

原因：

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

處理方法：

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

原因：

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

處理方法：

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

原因：

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

處理方法：

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

PS：

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

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

處理方法：

原因1

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

處理方法：

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

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

原因2：

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

處理方法：

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

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

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

處理方法：

utf8為3個字節(jié)存儲一個字符，gbk為2個字節(jié)存儲一個字符，依次類推得到對應字符串類型字段的前綴索引長度修正即可。結(jié)構設計修改路徑：索引=》包含列=》前綴長度，進行設置。

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

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

處理方法：

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

均為實例宕機引起。

處理方法：

此類錯誤一般情況下在10分鐘內(nèi)會自動修復，請在10分鐘后重試執(zhí)行任務即可。如果超時10分鐘仍然不成功，直接聯(lián)系對應業(yè)務DBA同學確認。

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

處理方法：

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

報這個錯誤表示列已經(jīng)存在了。

均為實例宕機引起。

處理方法：

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