58

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.

Shiva Krishna Bavandla
  • 25,548
  • 75
  • 193
  • 313

9 Answers9

117

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?

Aziz
  • 20,065
  • 8
  • 63
  • 69
  • 1
    yup thats right, but i got this structure to create table(Actually this one of the table structure created in MSSQL database). I am trying to do the same in MYSQL – Shiva Krishna Bavandla Nov 08 '12 at 06:15
  • 1
    MSSQL **does not** have this limit. MySQL **does** have this limit. You cannot create such table in MySQL unless you change the types. – Aziz Nov 08 '12 at 06:18
  • 2
    `total size of *all* fields in the table` - and learned again something new. Thanks! – Avatar Jan 01 '18 at 09:34
  • You can use NVARCHAR also which allocates memory at runtime dynamically based on the data you inserted. but you need to mention max size at the time of creating a table, it doesn't mean it allocates memory. – Kishore Kolla Apr 15 '19 at 10:47
  • Total size of the row can vary due to character set - for instance, in MySQL 8, utf8 is an alias for utf8mb3 and it takes less than utf8mb4. I had a table which couldn't be created on utf8mb4 but could be created on utf8mb3. – Vlad Dinulescu Jul 19 '22 at 10:49
14

Use TEXT instead of VARCHAR. Because you're exceeding the maximum row size. if you use TEXT ,it is intended for large text columns. Max size of varchar is 65535. create a column with varchar(65535) but it would have to be the only column in the table.

or

problem is the row size limit for innodb tables, belowlinks you can find some approaches to solve this:

http://www.mysqlperformanceblog.com/2011/04/07/innodb-row-size-limitation/ https://dba.stackexchange.com/questions/6598/innodb-create-table-error-row-size-too-large

Community
  • 1
  • 1
Akhil
  • 1,073
  • 1
  • 9
  • 28
13

65535 bytes is the max row size for mysql.

With utf8mb4 charset, varchar(255) means this column at most uses 255 * 4 + 1 bytes. So it depends on what charset table use.

g10guang
  • 4,647
  • 3
  • 28
  • 22
6

This problem came out in Laravel and I used text instead of varchar.

ufuk
  • 367
  • 3
  • 16
5

I agree with the answer on using appropriately sized columns and TEXT over VARCHAR as the first step, but if you still hit limits you may want to change your collation settings for that table if you are using UTF-8 or another character set with more than one byte per character and do not need it (only storing English text for example). I did this to get around the limit you are hitting for a very wide table. Some more detail here.

Differences between utf8 and latin1

Community
  • 1
  • 1
Peter
  • 29,498
  • 21
  • 89
  • 122
3

I used 10.4.17-MariaDB MariaDB Server and I have fixed it by edited the MariaDB server config file (located in /etc/my.cnf.d/server.cnf) and added those 2 lines:

innodb_log_file_size=512M

innodb_strict_mode=0

David Duong
  • 143
  • 2
  • 11
2

It worked for me. I changed ROW_FORMAT from COMPACT TO DYNAMIC.

ALTER TABLE table_name ROW_FORMAT=DYNAMIC;

The DYNAMIC row format offers the same storage characteristics as the COMPACT row format but adds enhanced storage capabilities for long variable-length columns and supports large index key prefixes.

For for information refer official doc link

Puneet Verma
  • 1,373
  • 2
  • 19
  • 23
0
  • Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.
  • BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately from the rest of the row.
  • detailed information - Limits on Table Column Count and Row Size
R.Lee
  • 71
  • 1
  • 8
-10

Try changing the storage engine to CSV.

Sunit
  • 403
  • 1
  • 5
  • 6
  • 1
    This might technically work, but is generally not a great idea. I would never want to use the CSV storage engine on a busy production server. – rinogo Mar 06 '15 at 22:31