SOLVED # 1118 Row size too large 8126. Changing some columns to TEXT or BLOB

  • 24-11-2022
  • 1695
  • MYSQL
  • Haresh Chauhan

Watch Youtube Video (HINDI) :

SOLVED # 1118 Row size too large 8126. Changing some columns to TEXT or BLOB, This error was faced when I was importing long database SQL files into the database. Everything I tried and search in various blogs also and try to find the solution but was not able to fix but in the end, finally found the solution. follow this solution and fix this error surely.

Solution 1

Add this line at the end of your SQL query file with the associated table throwing the error.

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

// REPLACE TO 

ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED;

Example 1

As below mentioned is the table that I was trying to insert in the database, but the error throws that the large row table gave the table that you want to import in the database. After a lot's research and finding out the final solution.

CREATE TABLE `clients` (
     `id` int(11) NOT NULL,
     `cl_unq_no` text NOT NULL,
     `f_name` text NOT NULL,
     `m_name` text NOT NULL,
     `l_name` text NOT NULL,
     `profile_pic` text NOT NULL,
     `gender` text NOT NULL,
     `dob` text NOT NULL,
     `state_id` int(11) NOT NULL,
     `region_id` int(11) NOT NULL,
     `unit_id` int(11) NOT NULL,
     `admin_id` text NOT NULL,
     `client_status` int(11) NOT NULL DEFAULT 1,
     `created` date NOT NULL,
     `updated` date NOT NULL,
     `folder` text NOT NULL,
     `client_type` text NOT NULL,
     `deceased_date` date DEFAULT NULL,
     `client_alert` text DEFAULT NULL,
     `client_alert_details` text NOT NULL,
     `clinical_diagnosis` text NOT NULL,
     `allergies_and_adverse_reactions` text NOT NULL,
     `allergies_and_adverse_reactions_notes` text NOT NULL,
     `asthma` text NOT NULL,
     `asthma_notes` text NOT NULL
     ...
     ...
     ...
     ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; // REPLACE THIS LINE

I just replace ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED, in the importing SQL database file, and finally it's imported in the database very quick.

CREATE TABLE `clients` (
     `id` int(11) NOT NULL,
     `cl_unq_no` text NOT NULL,
     `f_name` text NOT NULL,
     `m_name` text NOT NULL,
     `l_name` text NOT NULL,
     `profile_pic` text NOT NULL,
     `gender` text NOT NULL,
     `dob` text NOT NULL,
     `state_id` int(11) NOT NULL,
     `region_id` int(11) NOT NULL,
     `unit_id` int(11) NOT NULL,
     `admin_id` text NOT NULL,
     `client_status` int(11) NOT NULL DEFAULT 1,
     `created` date NOT NULL,
     `updated` date NOT NULL,
     `folder` text NOT NULL,
     `client_type` text NOT NULL,
     `deceased_date` date DEFAULT NULL,
     `client_alert` text DEFAULT NULL,
     `client_alert_details` text NOT NULL,
     `clinical_diagnosis` text NOT NULL,
     `allergies_and_adverse_reactions` text NOT NULL,
     `allergies_and_adverse_reactions_notes` text NOT NULL,
     `asthma` text NOT NULL,
     `asthma_notes` text NOT NULL
     ...
     ...
     ...
     ...
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED;

We always thanks to you for reading our blogs.


dharmesh-image

Dharmesh Chauhan

(Swapinfoway Founder)

Hello Sir, We are brothers origin from Gujarat India, Fullstack developers working together since 2016. We have lots of skills in web development in different technologies here I mention PHP, Laravel, Javascript, Vuejs, Ajax, API, Payment Gateway Integration, Database, HTML5, CSS3, and Server Administration. So you need our service Please Contact Us

haresh-image

Haresh Chauhan

(Co-Founder)


We Are Also Recommending You :