CODEWITHSUNDEEP
mysql
Junaid Usmani
Junaid Usmani

Reputation: 139

SQL Error (1118):Row size too large. The maximum row size for the used table type, not counting BLOBs

I have create one MySQL database table. I have to create 195 columns in one table. I have to give the data types of 190 columns is VARCHAR and 5 columns data type are text. Each columns have length 500.

Actually I want to create 1000 columns in one database table and I want to use data type is VARCHAR in each table columns. Now when I am creating new columns in database table. I am getting this error:

SQL Error (1118):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

Upvotes: 10

Views: 37424

Answers (3)

Vasanth L
Vasanth L

Reputation: 11

You need to change the data type from VARCHAR to TEXT which will overcome the row limitation in MySQL.

The query below creates an error due to row limitation:

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;

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

But when you change it to TEXT datatype it works. In the following MyISAM example, changing a column to TEXT avoids the 65,535-byte row size limit and permits the operation to succeed because BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size.

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

Further reading

Upvotes: 0

GMKHussain
GMKHussain

Reputation: 4681

Run SQL query/queries on database

SET GLOBAL innodb_strict_mode=OFF;

Upvotes: 5

david strachan
david strachan

Reputation: 7228

You database design is flawed.

Maximum row size with MySQL = 65,535 bytes.

Assuming 1 byte per character 500 X 1000 = 50,000 bytes per row which is approaching 65,535.

For VARCHAR(L) using latin1 1 byte per column is required to hold length L so now we are at 501,000.

For a VARCHAR column that stores multibyte characters, the effective maximum number of characters is less. For example, utf8 characters can require up to three bytes per character.

500 X 4 X 1000 = 2,000,000 bytes (3 per char + 1 for length)

For more information MySQL Documentation MySQL Maximum Number of Columns AND Data Type Storage Requirements.

I suggest you learn more about database design before proceeding.

Upvotes: 7

Related Questions