Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535 in MySQL or MariaDB

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535 in MySQL or MariaDB.

Error:

CREATE TABLE `Product` (
  `ProductID` varchar(15) NOT NULL,
  `SubProductID` varchar(15) NOT NULL,
  `DESCRIPTION` varchar(15000) NOT NULL,
  `NAME` varchar(150) NOT NULL,
  `SubCatagery` varchar(150) NOT NULL,
  `SubSubcatagery` varchar(150) DEFAULT NULL,
  `MainDectiption` varchar(15000) DEFAULT NULL
 )
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SQL Error [1118] [42000]: (conn=15) 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

Cause:

Cause: 
Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.


select @@innodb_page_size
 
 @@innodb_page_size|
------------------+
             16384|
 
set @@innodb_page_size=32568
 
SQL Error [1238] [HY000]: (conn=15) Variable 'innodb_page_size' is a read only variable

innodb_page_size can only be configured prior to initializing the MySQL instance and cannot be changed afterward. 
If no value is specified, the instance is initialized using the default page size. 

We cannot increase the size  of innodb_page_size parameter, it is read only, it configured once during creation of database for more detail refer:
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_page_size

Solution:

The first solution is to reduce the size of datatypes used while creating the index if you do not need then you should reduce the size of an existing datatype

-- Example you change the value from 15000 to 1000 for columntype then it works:
CREATE TABLE `Product` (
  `ProductID` varchar(15) NOT NULL,
  `SubProductID` varchar(15) NOT NULL,
  `DESCRIPTION` varchar(1000) NOT NULL,
  `NAME` varchar(150) NOT NULL,
  `SubCatagery` varchar(150) NOT NULL,
  `SubSubcatagery` varchar(1000) DEFAULT NULL,
  `MainDectiption` varchar(1000) DEFAULT NULL
 )
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The Second solution If you cannot reduce the size of datatypes then you should use for text or blog datatype to overcome this error.

--Example you use text column type then it works"
CREATE TABLE `Product` (
  `ProductID` varchar(15) NOT NULL,
  `SubProductID` varchar(15) NOT NULL,
  `DESCRIPTION` varchar(15000) NOT NULL,
  `NAME` varchar(150) NOT NULL,
  `SubCatagery` varchar(150) NOT NULL,
  `SubSubcatagery` text DEFAULT NULL,
  `MainDectiption` text DEFAULT NULL
 )
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.