MySQL Bugs: #82244: ERROR 1118 when importing 5.1 mysqldump into 5.7.11

Bug #82244	ERROR 1118 when importing 5.1 mysqldump into 5.7.11
Submitted:	15 Jul 2016 11:39	Modified:	21 Jul 2016 11:59
Reporter:	Martin Elsmore	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.7.11	OS:	Windows (2012R2)
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump

Description:
413994 - -------------- 
413995 - SET character_set_client = utf8 
413996 - -------------- 
413997 -  
413998 - Query OK, 0 rows affected (0.00 sec) 
413999 -  
414000 - -------------- 
414001 - /*!50001 CREATE TABLE `ui_businessmemberview` ( 
414002 -   `LOYALTY_URN` varchar(20), 
414003 -   `LOAD_DATE` datetime, 
414004 -   `AMEND_DATE` datetime, 
414005 -   `MERGE_LOYALTY_URN` varchar(60), 
414006 -   `AGREEMENT_NUMBER` varchar(20), 
414007 -   `MEMBER_TYPE` varchar(20), 
414008 -   `ACCOUNT_STATUS` char(1), 
414009 -   `TITLE` varchar(1000), 
414010 -   `FIRST_NAME` varchar(1000), 
414011 -   `MIDDLE_NAME` varchar(1000), 
414012 -   `SURNAME` varchar(1000), 
414013 -   `NAME_SUFFIX` varchar(1000), 
414014 -   `INPUT_GENDER` char(1), 
414015 -   `INPUT_ADDRESS1` varchar(1000), 
414016 -   `INPUT_ADDRESS2` varchar(1000), 
414017 -   `INPUT_ADDRESS3` varchar(1000), 
414018 -   `INPUT_ADDRESS4` varchar(1000), 
414019 -   `INPUT_ADDRESS5` varchar(1000), 
414020 -   `INPUT_ADDRESS6` varchar(1000), 
414021 -   `INPUT_POSTCODE` varchar(1000), 
414022 -   `INPUT_COUNTRY_CODE` varchar(1000), 
414023 -   `DATE_OF_BIRTH` date, 
414024 -   `HOME_PHONE` varchar(1000), 
414025 -   `WORK_PHONE` varchar(1000), 
414026 -   `MOBILE_PHONE` varchar(1000), 
414027 -   `FAX_NUMBER` varchar(1000), 
414028 -   `INPUT_EMAIL_ADDRESS` varchar(1000), 
414029 -   `dpaMail1` char(1), 
414030 -   `dpaEmail1` char(1), 
414031 -   `dpaSMS1` char(1), 
414032 -   `dpaPhone1` char(1), 
414033 -   `dpaMail2` char(1), 
414034 -   `dpaEmail2` char(1), 
414035 -   `dpaSMS2` char(1), 
414036 -   `dpaPhone2` char(1), 
414037 -   `dpaMail3` char(1), 
414038 -   `dpaEmail3` char(1), 
414039 -   `dpaSMS3` char(1), 
414040 -   `dpaPhone3` char(1), 
414041 -   `APPLICATION_DATE` datetime, 
414042 -   `STORE_NUMBER` int(11), 
414043 -   `STAFF_PAYROLL_NUMBER` varchar(20), 
414044 -   `CLIENT_GONEAWAY` tinyint(4), 
414045 -   `CLIENT_ID` int(11), 
414046 -   `retailerRefId` varchar(100), 
414047 -   `preferredStoreNo` int(11), 
414048 -   `preferredStoreCountryCode` varchar(2), 
414049 -   `LoyaltyProgrammeActive` varchar(8), 
414050 -   `preferredLanguage` varchar(1000), 
414051 -   `department` varchar(20), 
414052 -   `maritalStatus` char(1), 
414053 -   `lastKioskAccessDate` datetime, 
414054 -   `APPLICATION_TYPE` varchar(10), 
414055 -   `businessName` varchar(1000), 
414056 -   `businessShortName` varchar(1000), 
414057 -   `BUSINESS_POSITION` varchar(1000), 
414058 -   `BUSINESS_TYPE` varchar(1), 
414059 -   `BUSINESS_FORMAT` varchar(1000), 
414060 -   `FLAG_ID` varchar(4), 
414061 -   `registrationNumber` varchar(20), 
414062 -   `yearBusinessEstablised` int(4), 
414063 -   `BUSINESS_BEING_ESTABLISHED` int(0), 
414064 -   `noOfEmployees` varchar(10), 
414065 -   `FAMILY_FLAG` int(0), 
414066 -   `familyUrn` varchar(20), 
414067 -   `APPLICATION_SIGNED` int(0), 
414068 -   `BUSINESS_ADDRESS_FLAG` int(0), 
414069 -   `BUSINESS_LOAD_DATE` datetime, 
414070 -   `BUSINESS_AMEND_DATE` datetime, 
414071 -   `Updatesource` varchar(20) 
414072 - ) ENGINE=MyISAM */ 
414073 - -------------- 
414074 -  
414075 - ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs 
414076 - -------------- 
414077 - SET character_set_client = @saved_cs_client 
414078 - -------------- 
414079 -  
414080 - Query OK, 0 rows affected (0.00 sec) 
414081 -  
414082 - -------------- 
414083 - DROP TABLE IF EXISTS `ui_businessmemberview_resend` 

How to repeat:
Run following SQL:
 /*!50001 CREATE TABLE `ui_businessmemberview` ( 
   `LOYALTY_URN` varchar(20), 
   `LOAD_DATE` datetime, 
   `AMEND_DATE` datetime, 
   `MERGE_LOYALTY_URN` varchar(60), 
   `AGREEMENT_NUMBER` varchar(20), 
   `MEMBER_TYPE` varchar(20), 
   `ACCOUNT_STATUS` char(1), 
   `TITLE` varchar(1000), 
   `FIRST_NAME` varchar(1000), 
   `MIDDLE_NAME` varchar(1000), 
   `SURNAME` varchar(1000), 
   `NAME_SUFFIX` varchar(1000), 
   `INPUT_GENDER` char(1), 
   `INPUT_ADDRESS1` varchar(1000), 
   `INPUT_ADDRESS2` varchar(1000), 
   `INPUT_ADDRESS3` varchar(1000), 
   `INPUT_ADDRESS4` varchar(1000), 
   `INPUT_ADDRESS5` varchar(1000), 
   `INPUT_ADDRESS6` varchar(1000), 
   `INPUT_POSTCODE` varchar(1000), 
   `INPUT_COUNTRY_CODE` varchar(1000), 
   `DATE_OF_BIRTH` date, 
   `HOME_PHONE` varchar(1000), 
   `WORK_PHONE` varchar(1000), 
   `MOBILE_PHONE` varchar(1000), 
   `FAX_NUMBER` varchar(1000), 
   `INPUT_EMAIL_ADDRESS` varchar(1000), 
   `dpaMail1` char(1), 
   `dpaEmail1` char(1), 
   `dpaSMS1` char(1), 
   `dpaPhone1` char(1), 
   `dpaMail2` char(1), 
   `dpaEmail2` char(1), 
   `dpaSMS2` char(1), 
   `dpaPhone2` char(1), 
   `dpaMail3` char(1), 
   `dpaEmail3` char(1), 
   `dpaSMS3` char(1), 
   `dpaPhone3` char(1), 
   `APPLICATION_DATE` datetime, 
   `STORE_NUMBER` int(11), 
   `STAFF_PAYROLL_NUMBER` varchar(20), 
   `CLIENT_GONEAWAY` tinyint(4), 
   `CLIENT_ID` int(11), 
   `retailerRefId` varchar(100), 
   `preferredStoreNo` int(11), 
   `preferredStoreCountryCode` varchar(2), 
   `LoyaltyProgrammeActive` varchar(8), 
   `preferredLanguage` varchar(1000), 
   `department` varchar(20), 
   `maritalStatus` char(1), 
   `lastKioskAccessDate` datetime, 
   `APPLICATION_TYPE` varchar(10), 
   `businessName` varchar(1000), 
   `businessShortName` varchar(1000), 
   `BUSINESS_POSITION` varchar(1000), 
   `BUSINESS_TYPE` varchar(1), 
   `BUSINESS_FORMAT` varchar(1000), 
   `FLAG_ID` varchar(4), 
   `registrationNumber` varchar(20), 
   `yearBusinessEstablised` int(4), 
   `BUSINESS_BEING_ESTABLISHED` int(0), 
   `noOfEmployees` varchar(10), 
   `FAMILY_FLAG` int(0), 
   `familyUrn` varchar(20), 
   `APPLICATION_SIGNED` int(0), 
   `BUSINESS_ADDRESS_FLAG` int(0), 
   `BUSINESS_LOAD_DATE` datetime, 
   `BUSINESS_AMEND_DATE` datetime, 
   `Updatesource` varchar(20) 
 ) ENGINE=MyISAM */

Hello Martin Elsmore,

Thank you for the report and test case.
Imho this is expected behavior because every table (regardless of storage engine) has a maximum row size of 65,535 bytes and with utf8 characters require up to three bytes per character. Even if you try in 5.1 with utf8 charset you would get the same error which you are seeing with 5.7.  Please see https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

5.1.77/5.7.13
======================

If you see, with lantin1 there is no issue but with utf8 you are exceeding the row size

mysql> DROP DATABASE IF EXISTS mydb;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE mydb DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb;
Database changed
mysql>
mysql> CREATE TABLE `ui_businessmemberview` (
    ->    `LOYALTY_URN` varchar(20),
 .
 .
    ->  ) ENGINE=MyISAM;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql>
mysql> DROP DATABASE IF EXISTS mydb;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE mydb DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_swedish_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb;
Database changed
mysql>
mysql> CREATE TABLE `ui_businessmemberview` (
    ->    `LOYALTY_URN` varchar(20),
.
.
    ->  ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

Thanks,
Umesh