mysql firstrow=2_MySQL大字段溢出导致主从复制中断

一、问题描述

主从复制中断，报错如下：

二、问题分析根据报错提示，innodb表数据行长度超过了最大限制，需要将某些字段更改为TEXT或者BLOB类型，又或者是更改innodb表行格式为DYNAMIC或者COMPRESSED......

查看该表的表结构，发现有大量blob字段：

mysql> show create table rolesfirst\G;

*************************** 1. row ***************************

Table: rolesfirst

Create Table: CREATE TABLE `rolesfirst` (

`dwUniqueId` int(4) unsigned NOT NULL AUTO_INCREMENT,

`Version` int(4) unsigned NOT NULL,

`ConfraternityId` int(4) NOT NULL DEFAULT '-1',

`ConfraternityThawTime` int(4) NOT NULL DEFAULT '0',

`BaoKuRewardCount` int(4) unsigned NOT NULL DEFAULT '0',

`SpiteKillNum` int(4) unsigned NOT NULL,

`TongGUID` varbinary(64) DEFAULT NULL,

`AccountName` varbinary(64) NOT NULL,

`RoleName` varbinary(64) NOT NULL,

`RoleSex` int(1) unsigned NOT NULL,

`RoleType` int(1) unsigned NOT NULL,

`Transmigration` int(1) unsigned NOT NULL,

`RoleLevel` int(1) unsigned NOT NULL,

`RoleExp` int(4) unsigned NOT NULL DEFAULT '0',

`TitleColor` int(4) NOT NULL,

`ContriDouTime` int(1) unsigned NOT NULL DEFAULT '0',

`SysDouTime` int(1) unsigned NOT NULL DEFAULT '0',

`IBDouTime` int(4) unsigned NOT NULL DEFAULT '0',

`DouTime` int(4) unsigned NOT NULL DEFAULT '0',

`PetIBDouTime` int(4) unsigned NOT NULL DEFAULT '0',

`PetDouTime` int(4) unsigned NOT NULL DEFAULT '0',

`Freeze` int(1) unsigned NOT NULL DEFAULT '0',

`ExpMultiple` int(1) unsigned NOT NULL DEFAULT '0',

`PetMultiple` int(1) unsigned NOT NULL DEFAULT '0',

`Money` int(4) unsigned NOT NULL,

`MoneyInBox` int(4) unsigned NOT NULL,

`BindMoney` int(4) unsigned NOT NULL DEFAULT '0',

`BirthMap` int(1) unsigned NOT NULL DEFAULT '0',

`HorseScore` int(4) unsigned NOT NULL DEFAULT '0',

`IBRewardPoint` int(4) NOT NULL DEFAULT '0',

`IBRewardPointPlus` int(4) NOT NULL DEFAULT '0',

`TotalJinshanbi` int(4) unsigned NOT NULL DEFAULT '0',

`RecentJinshanbi` int(4) unsigned NOT NULL DEFAULT '0',

`RecentTime` int(4) unsigned NOT NULL DEFAULT '0',

`InsuranceCurrentValue` int(4) NOT NULL DEFAULT '0',

`InsuranceMoneyTotal` int(4) NOT NULL DEFAULT '0',

`InsuranceMoneyLeft` int(4) NOT NULL DEFAULT '0',

`RecommenderRewardToAdd` int(4) NOT NULL DEFAULT '0',

`RecommenderRewardTicketAdded` int(4) NOT NULL DEFAULT '0',

`PlayedTime` int(4) unsigned NOT NULL,

`CreateDate` datetime NOT NULL,

`CreateIP` int(4) unsigned NOT NULL,

`CreateIPLocal` int(4) unsigned NOT NULL,

`ExchangeDate` datetime NOT NULL,

`LastPlayingDate` datetime NOT NULL,

`LastPlayingIP` int(4) unsigned NOT NULL,

`LastPlayingIPLocal` int(4) unsigned NOT NULL,

`NoChatDate` datetime NOT NULL,

`NoLoginDate` datetime NOT NULL,

`CombatOrg` int(4) unsigned NOT NULL DEFAULT '0',

`CombatScore` int(4) unsigned NOT NULL DEFAULT '0',

`UseRevivePosition` int(1) unsigned NOT NULL,

`ReviveID` int(4) NOT NULL,

`ReviveX` int(4) NOT NULL,

`ReviveY` int(4) NOT NULL,

`EnterMapID` int(4) NOT NULL,

`EnterMapX` int(4) NOT NULL,

`EnterMapY` int(4) NOT NULL,

`JinkMagic` int(4) NOT NULL DEFAULT '0',

`LuckCurse` int(4) NOT NULL DEFAULT '0',

`DmgAbsorb` int(4) NOT NULL DEFAULT '0',

`SchoolContribute` int(4) unsigned NOT NULL DEFAULT '0',

`MurderousLook` int(4) NOT NULL DEFAULT '0',

`Goodness` int(4) unsigned NOT NULL DEFAULT '0',

`StudentIn` int(4) NOT NULL DEFAULT '0',

`StudentOut` int(4) NOT NULL DEFAULT '0',

`Honour` int(4) unsigned NOT NULL DEFAULT '0',

`Energy` int(4) NOT NULL DEFAULT '0',

`MaxEnergy` int(4) NOT NULL DEFAULT '0',

`Vigor` int(4) NOT NULL DEFAULT '0',

`MaxVigor` int(4) NOT NULL DEFAULT '0',

`Life` int(4) NOT NULL DEFAULT '0',

`MaxLife` int(4) NOT NULL DEFAULT '0',

`Mana` int(4) NOT NULL DEFAULT '0',

`MaxMana` int(4) NOT NULL DEFAULT '0',

`Internal` int(4) NOT NULL DEFAULT '0',

`InternalLev` int(4) NOT NULL DEFAULT '0',

`Constitution` int(4) NOT NULL DEFAULT '0',

`EquipExtend` int(4) NOT NULL DEFAULT '0',

`MaterialExtend` int(4) NOT NULL DEFAULT '0',

`RepositoryExtend` int(4) NOT NULL DEFAULT '0',

`PKValue` int(4) NOT NULL DEFAULT '0',

`TotalPlusPoint` int(4) NOT NULL DEFAULT '0',

`CurPlusPoint` int(4) NOT NULL DEFAULT '0',

`EnergyRecoverSpeed` int(4) unsigned NOT NULL DEFAULT '0',

`VigorRecoverSpeed` int(4) unsigned NOT NULL DEFAULT '0',

`VigorDayRecover` int(4) NOT NULL DEFAULT '0',

`EnergyDayRecover` int(4) NOT NULL DEFAULT '0',

`MyInvestigateTimes` int(4) unsigned NOT NULL DEFAULT '0',

`IsReceiveReword` int(4) unsigned NOT NULL DEFAULT '0',

`ItemAddRoomTimes` int(4) unsigned NOT NULL DEFAULT '0',

`PkModeTimer` int(4) unsigned NOT NULL DEFAULT '0',

`IBPoint` int(4) unsigned NOT NULL DEFAULT '0',

`PkModeCurrent` int(4) NOT NULL DEFAULT '0',

`PlayingActivityID` int(4) NOT NULL DEFAULT '0',

`BaseInfo` tinyblob,

`NumericInfo` blob,

`PasswordInfo` tinyblob,

`DailyCommendInfo` tinyblob,

`EnergyActivityInfo` tinyblob,

`ExtractionCardData` blob,

`MeridiansData` tinyblob,

`TongTotemData` tinyblob,

`BattleSoulData` blob,

`LevExchangNum` int(4) unsigned NOT NULL DEFAULT '0',

`MineHoleTaskData` tinyblob,

`TrilokaTowerData` tinyblob,

`SpecialRingData` blob,

`BlueVipData` blob,

`EquipOperatorData` tinyblob,

`ShopintLimitBuy` blob,

`ActivityData` tinyblob,

`AdditionalActivityData` tinyblob,

`ReleaseActivityData` blob,

`HorseInfo` blob,

`BizActivityData` blob,

`ReserveInfo` tinyblob,

`SettingInfo` tinyblob,

`SkillList` blob,

`ItemList` mediumblob,

`TaskList` blob,

`EnhanceList` blob,

`FriendsList` blob,

`ReserveList` blob,

`GUID` varbinary(64) DEFAULT NULL,

`Portrait` int(4) NOT NULL,

`Online` int(4) NOT NULL,

`SpyLevel` int(4) NOT NULL,

`GlobalMailDate` datetime NOT NULL,

`DeleteDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

`TravelState` int(4) NOT NULL DEFAULT '0',

`TravelID` int(4) NOT NULL DEFAULT '0',

`TravelToken` int(4) NOT NULL DEFAULT '0',

`TravelTime` datetime NOT NULL,

`TravelReserved` blob,

`ReservedNum1` int(4) NOT NULL DEFAULT '0',

`ReservedBlob1` blob,

`ReservedBlob2` blob,

`CombatKill` int(11) unsigned NOT NULL DEFAULT '0',

`AdditionalOffLineExp` int(4) unsigned NOT NULL DEFAULT '0',

`HelmType` int(4) NOT NULL DEFAULT '0',

`ArmorType` int(4) NOT NULL DEFAULT '0',

`ShoulderType` int(4) NOT NULL DEFAULT '0',

`BootsType` int(4) NOT NULL DEFAULT '0',

`WeaponType` int(4) NOT NULL DEFAULT '0',

`CuffType` int(4) NOT NULL DEFAULT '0',

`OldCuffType` int(4) NOT NULL DEFAULT '0',

`OldHelmType` int(4) NOT NULL DEFAULT '0',

`OldArmorType` int(4) NOT NULL DEFAULT '0',

`OldShoulderType` int(4) NOT NULL DEFAULT '0',

`OldBootsType` int(4) NOT NULL DEFAULT '0',

`TimeLimited` int(64) NOT NULL DEFAULT '0',

`DefaultHelmType` int(4) NOT NULL DEFAULT '0',

`DefaultArmorType` int(4) NOT NULL DEFAULT '0',

`DefaultCuffType` int(4) NOT NULL DEFAULT '0',

`DefaultBootType` int(4) NOT NULL DEFAULT '0',

`DefaultShouderType` int(4) NOT NULL DEFAULT '0',

`PetRoomNumber` int(4) NOT NULL DEFAULT '0',

`SuitStatus` int(1) unsigned NOT NULL DEFAULT '1',

`FaceType` int(4) unsigned NOT NULL DEFAULT '0',

`UnlockDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

`IBShopLocked` int(4) NOT NULL DEFAULT '0',

`IBBuyHistory` blob,

`IBBuyHistoryVersion` int(4) unsigned NOT NULL,

`GoldIngots` int(4) unsigned NOT NULL DEFAULT '0',

`LastLoginTime` int(4) unsigned DEFAULT '0',

`contributionThisWeek` int(4) unsigned DEFAULT '0',

`AwardStep` int(4) unsigned DEFAULT '0',

`AchievementList` blob,

`AchievementValue` int(4) unsigned DEFAULT '0',

`AchievementCount` int(4) unsigned DEFAULT '0',

`AchievementTime` datetime DEFAULT '0000-00-00 00:00:00',

`ActivityDegree` int(4) unsigned DEFAULT '0',

`SignTime` int(4) unsigned DEFAULT '0',

`TakeReward` int(4) unsigned DEFAULT '0',

`TakeVipReward` int(4) unsigned DEFAULT '0',

`LeaveTime` int(4) unsigned DEFAULT '0',

`OfflineTotalTime` int(4) unsigned DEFAULT '0',

`DayPlayTime` int(4) unsigned DEFAULT '0',

`WeekPlayTime` int(4) unsigned DEFAULT '0',

`Objective` int(4) unsigned DEFAULT '0',

`PileIB` int(4) unsigned DEFAULT '0',

`DailyIB` int(4) unsigned DEFAULT '0',

`DailyConsume` int(4) unsigned DEFAULT '0',

`ExchangeIBGift` tinyblob,

`DailySalary` tinyblob,

`VipLv` int(4) DEFAULT '0',

`VipRemainTime` int(4) DEFAULT '0',

`VipBuff` int(4) DEFAULT '0',

`VipGift` int(4) unsigned DEFAULT '0',

`AttackScore` int(4) unsigned DEFAULT '0',

`MaxAssaultScore` int(4) unsigned DEFAULT '0',

`MinAssaultScore` int(4) unsigned DEFAULT '0',

`MetaPool` binary(32) DEFAULT NULL,

`BattlefieldHonour` int(4) DEFAULT '0',

`MarsCompeteHonour` int(4) DEFAULT '0',

`SoulMagic` tinyblob,

`SoulMagicFortune` int(4) DEFAULT NULL,

`ArenaSort` int(4) DEFAULT '0',

`ArenaDefyNum` int(4) DEFAULT '0',

`ArenaDefyIncNum` int(4) DEFAULT '0',

`ArenaAwardState` int(4) DEFAULT '0',

`ArenaCoolDownTime` int(4) unsigned DEFAULT '0',

`ArenaData` blob,

`WingInfo` blob,

`WingType` int(4) NOT NULL DEFAULT '0',

`WingScore` int(4) unsigned DEFAULT '0',

`Reputation` int(4) unsigned DEFAULT '0',

`Achievement` blob,

`YellowRewardState` tinyint(1) unsigned NOT NULL DEFAULT '0',

`VipPrivilegeState` blob,

`PlayerLevel` int(1) unsigned NOT NULL,

`MapExploreData` blob,

`MysteriousShopData` blob,

`MonthCardData` blob,

`BossKillData` blob,

`BossShardData` blob,

`CurExp` int(4) unsigned DEFAULT '0',

`CurTimes` int(4) unsigned DEFAULT '0',

`AdvancedState` int(4) unsigned DEFAULT '0',

`FollowerLevExchangNum` int(4) unsigned DEFAULT '0',

`AlreadyEnterExtraCount` int(4) unsigned DEFAULT '0',

`HuangchengTaskRefreshCount` int(4) unsigned DEFAULT '0',

`AlreadyEnterCount` blob,

`AlReadyBuyCount` blob,

`AlReadyCurBuyCount` blob,

`CurInstanceState` blob,

`SpecFashion` blob,

`AlreadyEnterBossKillCount` blob,

`AlreadyBuyKillCount` blob,

`PetActivate` blob,

`SplusCultivateCount` blob,

`SplusCallCount` int(4) unsigned DEFAULT '0',

`CurGetCallCount` int(4) unsigned DEFAULT '0',

`LevelReward` int(4) unsigned DEFAULT '0',

`AutoTeamState` int(4) unsigned DEFAULT '1',

`CurExchangeMoneyCount` int(4) unsigned DEFAULT '0',

`LijuanExchangeMoneyCount` int(4) unsigned DEFAULT '0',

`DigMineFinishCount` int(4) unsigned DEFAULT '0',

`DigMineCount` int(4) unsigned DEFAULT '0',

`DigMineBuyCount` int(4) unsigned DEFAULT '0',

`HuangChengTask` tinyblob,

`ServantData` tinyblob,

`PlayerSalesInfo` blob,

`GodsFurnaceData` tinyblob,

`JobTitleData` tinyblob,

`InternalPowerData` tinyblob,

`OtherCurrencyGroup` tinyblob,

`ExemptDeathDrop` tinyblob,

`ReputationLevel` int(4) DEFAULT '0',

`ReputationStage` int(4) DEFAULT '0',

`ReputationExp` int(4) DEFAULT '0',

`OpenBoxCountEveryday` int(1) unsigned NOT NULL,

`AutoFightInfo` tinyblob,

`WorshipNum` int(10) unsigned NOT NULL,

`RemainMakeUpSignTimes` int(10) unsigned NOT NULL,

`TotalSignTimes` int(10) unsigned NOT NULL,

`BossKillVipCounts` int(10) unsigned NOT NULL,

`RecoverCountsArr` blob,

`FocusBossArr` blob,

`LastWeekYuanBaoNum` int(10) unsigned NOT NULL,

`ThisWeekYuanBaoNum` int(10) unsigned NOT NULL,

`ThisWeekYuanBaoTimes` int(10) unsigned NOT NULL,

`ResourceTimesArr` blob,

`SeekLogArr` blob,

`NewPlayerStatusArr` blob,

`SDGRewardState` int(4) DEFAULT '0',

`DeltaDay` int(4) DEFAULT '0',

`TopEnterPrison` int(4) DEFAULT '0',

`ItemUseCount` blob,

`TowerAddExp` int(4) DEFAULT '0',

`InjectExpNum` int(4) DEFAULT '0',

`PlayNextExtendTime` int(4) unsigned DEFAULT '0',

`InstanceNpc` blob,

`SpiritStoneData` blob,

`FashionData` blob,

`WorshipMultiple` int(4) DEFAULT '0',

`WorshipTotalExp` int(4) DEFAULT '0',

`TotalCastellanExp` int(4) DEFAULT '0',

`PlayerBossForAllData` blob,

`PolyElementData` blob,

`PolyCompleteState` tinyblob,

PRIMARY KEY (`dwUniqueId`),

UNIQUE KEY `dwUniqueId` (`dwUniqueId`),

UNIQUE KEY `RoleNameIndex` (`RoleName`),

KEY `AccountNameIndex` (`AccountName`),

KEY `TongGUIDIndex` (`TongGUID`),

KEY `CreateDateIndex` (`CreateDate`),

KEY `CreateIPIndex` (`CreateIPLocal`),

KEY `LastPlayingDateIndex` (`LastPlayingDate`),

KEY `LastPlayingIPIndex` (`LastPlayingIPLocal`),

KEY `RoleLevelIndex` (`RoleLevel`),

KEY `EnterMapIDIndex` (`EnterMapID`),

KEY `RoleExpIndex` (`RoleExp`),

KEY `MaxAssaultScoreIndex` (`MaxAssaultScore`),

KEY `MinAssaultScoreIndex` (`MinAssaultScore`),

KEY `ReputationLevelIndex` (`ReputationLevel`),

KEY `ReputationStageIndex` (`ReputationStage`),

KEY `ReputationExpIndex` (`ReputationExp`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=binary

仅仅根据报错提示，我还是没能深入理解，google了一下，才渐渐明白其中的原理.

antelop文件格式和barracuda文件格式：

在InnoDB Plugin(1.0.6)配置文件中innodb_file_format支持两种：Antelope和Barracuda，他们分别是两种文件格式的代号。

Barracuda(梭鱼)：

Antelope(羚羊)：

Antelope是Build-in-InnoDB(MySQL内置的InnoDB)支持的文件格式代号，有两种数据表行格式(row_format)：Redundant、Compact . Redundant是为了兼容之前版本而留的.

Barracuda是InnoDB Plugin支持的文件格式，在原来的基础上新增了两种数据表行格式：Dynamic和Compressed

对应关系表：

(Barracuda文件格式一样支持Compact和Redundant行格式)

innodb_file_format是在配置文件中指定；row_format则在创建表时指定.

在MySQL 5.1中，row_format默认是compact格式(MySQL 5.5/5.6也是默认compact格式)，可以通过show table status like‘table_name’来查看当前表使用的行格式，row_format属性表示当前所使用的行记录格式.

InnoDB存储引擎和大多数数据库一样，记录是以行的形式存储的，也就是说InnoDB的数据页存放着表中一行一行的数据.

compact行记录格式是在MySQL 5.0中引入的，compact行记录的存储方式如下：

compact行记录格式的首部是一个“非NULL变长字段长度列表”，其长度为：

l如果列的长度小于255字节，用1字节表示

l如果列的长度大于255个字节，用2字节表示

变长字段长度列表之后的第二个部分是NULL标志位，该标志位指示了该行数据中是否有NULL值，有则用1表示，该部分所占用字节为1字节.

第三部分是记录头信息(record header)，固定占用5字节(40位).

1)、compact行格式，对于blob，text，varchar(8099)这样的大字段，innodb只会存放前768字节在数据页中，而剩余的数据则会存储在溢出段中(发生溢出情况时，才会将数据存储在溢出段中).

2)、innodb的每个数据页大小默认为16kb，innodb存储引擎表为索引组织表，叶子节点为一个双向链表，因此每个数据页至少应该有两行记录，这就决定了innodb在存储一行数据的时候不能够超过8KB.

3)、使用了blob、text数据类型，是不是一定会把数据存储在溢出段中呢？答案是不一定的，关键是看一个数据页page到底能否存放两行数据(如果单行数据长度没有超过8KB)，而varchar类型是有可能将数据存放在溢出页中(单行数据长度超过8KB，前768字节存放在数据页中).

4)、MySQL 5.1中的innodb_plugin引入了新的文件格式：barracuda，该文件格式拥有两种新的行格式：compress和dynamic，这两种格式对blob字段采用完全溢出方式，也就是数据也只存放20个字节，其余的数据都存放在溢出段中，这样行长度就不会轻易超出了！而之前的Antelope文件格式下的两种行格式Compact、Redundant则会在数据页中存放768个前缀字节.

三、解决方案

由上述的问题分析，我们根据主从同步的报错提示，可以很容易找到解决方案：

1)、查看file_format:

如果不是barracuda，则要更改innodb_file_format为barracuda.可以在线更改：

set global innodb_file_format=Barracuda(记得配置文件要修改，防止重启导致修改失效)

2)、查看表的行格式：show table status like‘rolefirst’，如果不是DYNAMIC或者COMPRESSED，则修改为dynamic或者compressed :

alter table rolefirst ROW_FORMAT=DYNAMIC;

3)、重启slave线程：

stop slave;

start slave;