官方文檔：https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html
創(chuàng)新互聯(lián)公司主營久治網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,重慶APP軟件開發(fā),久治h5微信小程序定制開發(fā)搭建,久治網(wǎng)站營銷推廣歡迎久治等地區(qū)企業(yè)咨詢slave_type_conversions 這個參數(shù)在mysql5.5.3引入，目的是啟用row格式的bin-log的時候，如果主從的column的數(shù)據(jù)類型不一致，會導(dǎo)致復(fù)制失敗，mysql5.5.3之后支持，主庫是int從庫是bigint這種類型的復(fù)制，
這個參數(shù)的意義就是控制些類型轉(zhuǎn)換容錯性。
如果從庫的字段類型范圍比主庫類型大，那么設(shè)置slave_type_conversions=ALL_NON_LOSSY后復(fù)制沒有問題的。
如果從庫類型比主庫類型小，比如從int復(fù)制到tinyint，雖然可以通過設(shè)置slave_type_conversions=ALL_LOSSY,ALL_NON_LOSSY讓主從不出問題，但是實(shí)際上會造成數(shù)據(jù)丟失的風(fēng)險。
幾種值的設(shè)置：
ALL_LOSSY：允許數(shù)據(jù)截斷
ALL_NON_LOSSY：不允許數(shù)據(jù)截斷，如果從庫類型大于主庫類型，是可以復(fù)制的，反過了，就不行了，從庫報復(fù)制錯誤，復(fù)制終止。
ALL_LOSSY,ALL_NON_LOSSY:所有允許的轉(zhuǎn)換都會執(zhí)行，而不管是不是數(shù)據(jù)丟失。
空值（不設(shè)置）：要求主從庫的數(shù)據(jù)類型必須嚴(yán)格一致，否則都報錯。
Mode | Effect |
ALL_LOSSY | In this mode, type conversions that would mean loss of information are permitted. This does not imply that non-lossy conversions are permitted, merely that only cases requiring either lossy conversions or no conversion at all are permitted; for example, enabling only this mode permits an INT column to be converted to TINYINT (a lossy conversion), but not a TINYINT column to an INT column (non-lossy). Attempting the latter conversion in this case would cause replication to stop with an error on the slave. |
ALL_NON_LOSSY | This mode permits conversions that do not require truncation or other special handling of the source value; that is, it permits conversions where the target type has a wider range than the source type.【確保從庫的列類型更寬泛些也不會導(dǎo)致復(fù)制報錯】 Setting this mode has no bearing on whether lossy conversions are permitted; this is controlled with the ALL_LOSSY mode. If only ALL_NON_LOSSY is set, but not ALL_LOSSY, then attempting a conversion that would result in the loss of data (such as INT to TINYINT, or CHAR(25) to VARCHAR(20)) causes the slave to stop with an error. |
ALL_LOSSY,ALL_NON_LOSSY | When this mode is set, all supported type conversions are permitted, whether or not they are lossy conversions. |
ALL_SIGNED | Treat promoted integer types as signed values (the default behavior). |
ALL_UNSIGNED | Treat promoted integer types as unsigned values. |
ALL_SIGNED,ALL_UNSIGNED | Treat promoted integer types as signed if possible, otherwise as unsigned. |
[empty] | When slave_type_conversions is not set, no attribute promotion or demotion is permitted; this means that all columns in the source and target tables must be of the same types. This mode is the default. |
從庫是有個oracle系統(tǒng)去查數(shù)據(jù)的，原先從庫的cp_shop_activity表示utf8mb4字符集，oracle不支持。因此在這個從庫上手動修改了mode字段的字符類型為varchar(200) utf8，修改后的字段類型如下圖：
但是沒幾天后，發(fā)現(xiàn)主從復(fù)制報錯了，錯誤碼1677。從庫上記錄的錯誤日志如下：
我們看下主庫的mode字段是varchar(50)，從庫的mode字段是varchar(200)。
在從庫設(shè)置：
stop slave;
set global slave_type_conversions=ALL_NON_LOSSY;
#默認(rèn)slave_type_conversions為空，表示強(qiáng)制從庫和主庫的字段類型一致，不然就停止復(fù)制。設(shè)置為ALL_NON_LOSSY就稍微寬泛些同時確保不會造成類型轉(zhuǎn)換的問題。
start slave;
show slave status\G
這樣即可解決這個問題。
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價比高”等特點(diǎn)與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。
重慶分公司
重慶分公司
