Issue with row size error on create of table

[jwhite68]

I have a table that already exists in MySQL, and I want to add it to another database - but this time defined with the UTF-8 charset. This is part of some preparation to move from the default lation1 to utf8.

 

I have exported my existing table to a .sql file and changed the latin1 reference to utf8. But when I run the SQL script to create the table in my other database it generates a row size error.

 

Here is the SQL code:

CREATE TABLE `ai_commercial` ( 
`id` int( 15 ) NOT NULL AUTO_INCREMENT ,
`agentCode` varchar( 255 ) NOT NULL default '',
`tableName` varchar( 255 ) NOT NULL default '',
`existMature` varchar( 255 ) NOT NULL default '',
`existMatureVal` varchar( 255 ) NOT NULL default '',
`prpCode` varchar( 255 ) NOT NULL default '',
`dateAdded` varchar( 255 ) NOT NULL default '',
`prpType` varchar( 255 ) NOT NULL default '',
`officeVal` varchar( 255 ) NOT NULL default '',
`commBuildVal` varchar( 255 ) NOT NULL default '',
`hotelVal` varchar( 255 ) NOT NULL default '',
`industrialVal` varchar( 255 ) NOT NULL default '',
`wareHousVal` varchar( 255 ) NOT NULL default '',
`farmVal` varchar( 255 ) NOT NULL default '',
`shopVal` varchar( 255 ) NOT NULL default '',
`otherVal` varchar( 255 ) NOT NULL default '',
`prpAdvertTitle` varchar( 255 ) NOT NULL default '',
`prpAdvertDesc` varchar( 255 ) NOT NULL default '',
`prpSalePrice` varchar( 255 ) NOT NULL default '',
`curType` varchar( 255 ) NOT NULL default '',
`currency` varchar( 255 ) NOT NULL default '',
`curEuroVal` varchar( 255 ) NOT NULL default '',
`curDollarVal` varchar( 255 ) NOT NULL default '',
`curPoundVal` varchar( 255 ) NOT NULL default '',
`prpSize` varchar( 255 ) NOT NULL default '',
`sizeUnitType` varchar( 255 ) NOT NULL default '',
`prpUnit` varchar( 255 ) NOT NULL default '',
`prpSqmVal` varchar( 255 ) NOT NULL default '',
`prpSqfVal` varchar( 255 ) NOT NULL default '',
`landSize` varchar( 255 ) NOT NULL default '',
`lndUnitType` varchar( 255 ) NOT NULL default '',
`lndUnit` varchar( 255 ) NOT NULL default '',
`lndSqmVal` varchar( 255 ) NOT NULL default '',
`lndSqfVal` varchar( 255 ) NOT NULL default '',
`lndAcrVal` varchar( 255 ) NOT NULL default '',
`lndHctrVal` varchar( 255 ) NOT NULL default '',
`noRoom` varchar( 255 ) NOT NULL default '',
`noReceptionRoom` varchar( 255 ) NOT NULL default '',
`noReceptionRoomWC` varchar( 255 ) NOT NULL default '',
`noGarage` varchar( 255 ) NOT NULL default '',
`noParkingPlace` varchar( 255 ) NOT NULL default '',
`countryType` varchar( 255 ) NOT NULL default '',
`countryVal` varchar( 255 ) NOT NULL default '',
`cntryTable` varchar( 255 ) NOT NULL default '',
`cntryCodeField` varchar( 255 ) NOT NULL default '',
`cntryDescField` varchar( 255 ) NOT NULL default '',
`district` varchar( 255 ) NOT NULL default '',
`town` varchar( 255 ) NOT NULL default '',
`street` varchar( 255 ) NOT NULL default '',
`prpNo` varchar( 255 ) NOT NULL default '',
`prpPrimaryView` varchar( 255 ) NOT NULL default '',
`seaView` varchar( 255 ) NOT NULL default '',
`mainroadView` varchar( 255 ) NOT NULL default '',
`mountainView` varchar( 255 ) NOT NULL default '',
`lakeView` varchar( 255 ) NOT NULL default '',
`otherHousesView` varchar( 255 ) NOT NULL default '',
`parkView` varchar( 255 ) NOT NULL default '',
`otherView` varchar( 255 ) NOT NULL default '',
`notSpecifiedView` varchar( 255 ) NOT NULL default '',
`prpArea` varchar( 255 ) NOT NULL default '',
`coastalArea` varchar( 255 ) NOT NULL default '',
`cityArea` varchar( 255 ) NOT NULL default '',
`subrubsArea` varchar( 255 ) NOT NULL default '',
`countrySideArea` varchar( 255 ) NOT NULL default '',
`otherArea` varchar( 255 ) NOT NULL default '',
`notSpecifiedArea` varchar( 255 ) NOT NULL default '',
`streetNameAdvert` varchar( 255 ) NOT NULL default '',
`streetAdvertYes` varchar( 255 ) NOT NULL default '',
`streetAdvertNo` varchar( 255 ) NOT NULL default '',
`nearAirport` varchar( 255 ) NOT NULL default '',
`nearAirportVal` varchar( 255 ) NOT NULL default '',
`nearGolf` varchar( 255 ) NOT NULL default '',
`nearGolfVal` varchar( 255 ) NOT NULL default '',
`nearRestaurant` varchar( 255 ) NOT NULL default '',
`nearRestaurantVal` varchar( 255 ) NOT NULL default '',
`nearHospital` varchar( 255 ) NOT NULL default '',
`nearHospitalVal` varchar( 255 ) NOT NULL default '',
`nearKindergarten` varchar( 255 ) NOT NULL default '',
`nearKindergartenVal` varchar( 255 ) NOT NULL default '',
`nearSchool` varchar( 255 ) NOT NULL default '',
`nearSchoolVal` varchar( 255 ) NOT NULL default '',
`nearPark` varchar( 255 ) NOT NULL default '',
`nearParkVal` varchar( 255 ) NOT NULL default '',
`nearMedical` varchar( 255 ) NOT NULL default '',
`nearMedicalVal` varchar( 255 ) NOT NULL default '',
`nearUniversity` varchar( 255 ) NOT NULL default '',
`nearUniversityVal` varchar( 255 ) NOT NULL default '',
`nearShop` varchar( 255 ) NOT NULL default '',
`nearShopVal` varchar( 255 ) NOT NULL default '',
`investment` varchar( 255 ) NOT NULL default '',
`investmentVal` varchar( 255 ) NOT NULL default '',
`retail` varchar( 255 ) NOT NULL default '',
`retailVal` varchar( 255 ) NOT NULL default '',
`motorInd` varchar( 255 ) NOT NULL default '',
`motorIndVal` varchar( 255 ) NOT NULL default '',
`sportEnt` varchar( 255 ) NOT NULL default '',
`sportEntVal` varchar( 255 ) NOT NULL default '',
`hasTenant` varchar( 255 ) NOT NULL default '',
`hasTenantYes` varchar( 255 ) NOT NULL default '',
`hasTenantNo` varchar( 255 ) NOT NULL default '',
`rentalIncome` varchar( 255 ) NOT NULL default '',
`rentCurType` varchar( 255 ) NOT NULL default '',
`rentCur` varchar( 255 ) NOT NULL default '',
`rentCurEuroVal` varchar( 255 ) NOT NULL default '',
`rentCurDolVal` varchar( 255 ) NOT NULL default '',
`rentCurPndVal` varchar( 255 ) NOT NULL default '',
`rentalNotes` varchar( 255 ) NOT NULL default '',
`lastUpdated` varchar( 255 ) NOT NULL default '',
`dateFormat` varchar( 255 ) NOT NULL default '',
`imgDirPath` varchar( 255 ) NOT NULL default '',
`finPlan` varchar( 255 ) NOT NULL default '',
`imgCodeType` enum( '1', '2' ) NOT NULL default '1',
`mainImg` varchar( 255 ) NOT NULL default '',
`addImg1` varchar( 255 ) NOT NULL default '',
`addImg2` varchar( 255 ) NOT NULL default '',
`addImg3` varchar( 255 ) NOT NULL default '',
`addImg4` varchar( 255 ) NOT NULL default '',
`addImg5` varchar( 255 ) NOT NULL default '',
`imgPrefix1` varchar( 255 ) NOT NULL default '',
`imgPrefix2` varchar( 255 ) NOT NULL default '',
`imgPrefix3` varchar( 255 ) NOT NULL default '',
`imgSeparator` varchar( 255 ) NOT NULL default '',
`imgIndex` varchar( 255 ) NOT NULL default '',
`imgExt` varchar( 255 ) NOT NULL default '',
`pdfDirPath` varchar( 255 ) NOT NULL default '',
`pdfCodeType` enum( '1', '2' ) NOT NULL default '1',
`pdfFile` varchar( 255 ) NOT NULL default '',
`pdfPrefix1` varchar( 255 ) NOT NULL default '',
`pdfPrefix2` varchar( 255 ) NOT NULL default '',
`pdfPrefix3` varchar( 255 ) NOT NULL default '',
`pdfIndex` varchar( 255 ) NOT NULL default '',
`pdfExt` varchar( 255 ) NOT NULL default '',
`lstUpdatedDt` date NOT NULL default '0000-00-00',
PRIMARY KEY ( `id` ) 

) ENGINE = MYISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT =82;

 

And this is the error:

 

#1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

 

It cant be right, because otherwise how can it exist in my other database without errors?

Anyone got any idea?  If you add up the number of bytes, it doesnt even reach 65535.

[jwhite68]

Sorry I meant to add to MySQL forum. How can I do that now?

[per1os]

Wait till an admin comes along.

 

Wow do you really need all of those to be varchar(255) ??? Remember that UTF-8 is different than Latin1 I believe it requires a bit more space.

 

I would suggest looking at the fields and changing the varchar to something reasonable and not 255 for every single column.