shiva krishna shiva krishna - 1 year ago 107
MySQL Question

Row size too large error in mysql create table query

I am trying to create a table with the below query

Create Table PerformaceReport
campaignID int,
keywordID bigint,
keyword varchar(8000),
avgPosition decimal(18,6),
cost int,
clicks int,
conv1PerClick int,
impressions int,
day datetime,
currency varchar(8000),
account varchar(8000),
timeZone varchar(8000),
adGroup varchar(8000),
adGroupState varchar(8000),
approvalStatus varchar(8000),
lowestPosition varchar(8000),
campaign varchar(8000),
campaignState varchar(8000),
convManyPerClick int,
totalConvValue decimal(18,6),
maxCPCSource varchar(8000),
clientName varchar(8000),
destinationURL varchar(8000),
device varchar(8000),
firstPageCPC int,
isNegative bit,
matchType varchar(8000),
maxCPC varchar(8000),
maxCPM varchar(8000),
highestPosition varchar(8000),
qualityScore int,
keywordState varchar(8000),
viewThroughConv int)

And i am getting the below 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

Can anyone please let me know how to avoid this error and make the query work to create a table.

Answer Source

The total size of all fields in the table is more than the limit, 65535, that's why you are getting this error.

You should use text type instead of varchar for long strings. Replace all varchar(8000) with text, and it should work.

Or, even better, use appropriate data types instead of the "too large" ones. You don't really need 8000 characters to store currency, do you?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download