Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

[Amey Kolapkar]

Hello all,

I am trying to create dynamic tables for excel file. Number of columns varies according to file uploading. When I tried to upload a file having 572 columns, MySQL server gave an error with an error code 1118 Row size too large. I figured that the query was too large so I tried creating table with 100 columns and add remaining columns with alter query. It works that way up to certain limit (196 columns). After that it still gives the same error. Although, it works fine on other machine. Is there any configuration setting that is needed to be changed?

Query for create table is as given below,

CREATE TABLE `project11_module79` (
`insertdate` text,
`createdby` text,
`createddate` text,
`updatedby` text,
`updateddate` text,
`inserttimestamp` text,
`status` text,
`TestCase_ID_col` text,
`RunMode_col` text,
`DE1_col` text,
`PlanName_col` text,
`proposal_number_col` text,
`Description_col` text,
`Expected_Result_col` text,
`Status_col` text,
`Remarks_col` text,
`ScreenShotPath_col` text,
`ProposalIndividual_col` text,
`ChannelDetails_col` text,
`ProposalDetails_col` text,
`DetailsOfProposer_col` text,
`DetailsOfMinor_col` text,
`NominationDetails_col` text,
`BasicPlanDetails_col` text,
`LifeInsuranceDetails_col` text,
`FamilyHistoryOfLA_col` text,
`MedicalDetailsForLA_col` text,
`BankAccountDetailsOfLA_col` text,
`EinsuranceAccountDetails_col` text,
`PayerDetails_col` text,
`Form60_col` text,
`FatcaDetails_col` text,
`ProposalSubmition_col` text,
`P1_col` text,
`Prop_Category_col` text,
`P2_col` text,
`DisMarketing_col` text,
`DisMarketMode_col` text,
`P3_col` text,
`ProposalDate_col` text,
`Received_Date_col` text,
`Plan_Name_col` text,
`ExistSbiCust_col` text,
`Ext_SBI_Pol_Cust_col` text,
`AssignPolicy_col` text,
`PreferredLanguage_col` text,
`Proposal_Under_col` text,
`SimulProposalNo1_col` text,
`SimulProposalNo2_col` text,
`SimulProposalNo3_col` text,
`P4_col` text,
`Salutation_PA_col` text,
`PA_FirstName_col` text,
`PA_MiddleName_col` text,
`PA_LastName_col` text,
`PA_Gender_col` text,
`PA_DOB_col` text,
`PA_MaritalStatus_col` text,
`Maiden_FirstName_col` text,
`Maiden_MiddleName_col` text,
`Maiden_LastName_col` text,
`PA_Father_FN_col` text,
`PA_Father_MN_col` text,
`PA_Father_LN_col` text,
`PA_Mother_Name_col` text,
`PA_Spouse___Name_col` text,
`PA_Nationality_col` text,
`ProposerCountryResidence_col` text,
`Passport_Number_col` text,
`Passport_Issuance_Date_col` text,
`Passport_Valid_Upto_col` text,
`Age_Proof_col` text,
`Qualification_col` text,
`Identity_Proof_col` text,
`Identification_Number_col` text,
`Occupation_col` text,
`Company_Name_col` text,
`empDutyNature_col` text,
`empForceNumber_col` text,
`HazardOccupation_col` text,
`HazardDutyNature_col` text,
`PoliticallyExposed_col` text,
`Details_for_PEP_col` text,
`ConvictionHistory_col` text,
`CriminalDetails_col` text,
`SpouseInSBG_col` text,
`WorkinginSBG_col` text,
`Self_PF_Pension_Index_Employee_No_col` text,
`SpousePFPensionEmployeeNumber_col` text,
`Annual_Income_col` text,
`Source_Income_col` text,
`Incom_Prf_col` text,
`PAN_Num_col` text,
`AADHAR_Num_col` text,
`Submitted_Income_proof_col` text,
`Domicile_col` text,
`CmAddrTitle_col` text,
`CmAddrLine1_col` text,
`CmAddrLine2_col` text,
`CmAddrLine3_col` text,
`CmAddrPincode_col` text,
`CmAddrState_col` text,
`CmAddrCity_col` text,
`CmAddrDistrict_col` text,
`CmAddrProof_col` text,
`PaAddrsSameAsCm_col` text,
`PM_Add1_col` text,
`PM_Add2_col` text,
`PM_Add3_col` text,
`PM_Country_col` text,
`PM_Pincode_col` text,
`PM_AddrDistrict_col` text,
`PM_AddressProof_col` text,
`STD_Code_col` text,
`Home_Tel_No_col` text,
`OffSTDCode_col` text,
`Office_Tel_No_col` text,
`CountryCode1_col` text,
`MobileNumber1_col` text,
`CountryCode2_col` text,
`MobileNumber2_col` text,
`CountryCode3_col` text,
`MobileNumber3_col` text,
`Email_ID_Provided_col` text,
`Email_Id_col` text,
`LASameAsPR_col` text,
`P5_col` text,
`Salutation_LA_col` text,
`LA_FirstName_col` text,
`LA_MiddleName_col` text,
`LA_LastName_col` text,
`LA_Gender_col` text,
`LA_DOB_col` text,
`LA_MaritalStatus_col` text,
`LA_Father_FN_col` text,
`LA_Father_MN_col` text,
`LA_Father_LN_col` text,
`LA_Mother_Name_col` text,
`LA_Spouse_Name_col` text,
`MinorLArelationwithPR_col` text,
`LA_Nationality_col` text,
`LA_CountryResidence_col` text,
`LA_Passport_Number_col` text,
`LA_Passport_Issuance_Date_col` text,
`LA_Passport_Valid_Upto_col` text,
`LA_Age_Proof_col` text,
`LA_Qualification_col` text,
`LA_CmAddSameAsProp_col` text,
`LA_Domicile_col` text,
`LA_CmAddrTitle_col` text,
`LA_CmAddrLine1_col` text,
`LA_CmAddrLine2_col` text,
`LA_CmAddrLine3_col` text,
`LA_CmAddCountry_col` text,
`LA_CmAddrPincode_col` text,
`LA_CmAddrDistrict_col` text,
`LA_CmAddressProof_col` text,
`LA_PaAddsameAsCm_col` text,
`LA_PaAddrLine1_col` text,
`LA_PaAddrLine2_col` text,
`LA_PaAddrLine3_col` text,
`LA_PaAddCountry_col` text,
`LA_PaAddPincode_col` text,
`LA_PaAddrDistrict_col` text,
`LA_PaAddressProof_col` text,
`LA_STD_Code_col` text,
`LA_Home_Tel_No_col` text,
`LA_OffSTDCode_col` text,
`LA_Office_Tel_No_col` text,
`LA_CountryCode1_col` text,
`LA_MobileNumber1_col` text,
`LA_CountryCode2_col` text,
`LA_MobileNumber2_col` text,
`LA_CountryCode3_col` text,
`LA_MobileNumber3_col` text,
`LA_Email_ID_Provided_col` text,
`LA_Email_Id_col` text,
`P6_col` text,
`NM_Title_col` text,
`NM_FN_col` text,
`NM_MN_col` text,
`NM_LN_col` text,
`NM_DOB_col` text,
`NM_MaritalStatus_col` text,
`NM_Nationality_col` text,
`NMAgeProof_col` text,
`NM_Father_FN_col` text,
`NM_Father_MN_col` text,
`NM_Father_LN_col` text,
`NMRelationshipWithPR_col` text,
`NM_share_col` text,
`NMAddrssSameasPR_col` text,
`NM_Domicile_col` text,
`NM_Add1_col` text,
`NM_Add2_col` text,
`NM_Add3_col` text
);

This creates table with 196 columns but if I try to add one more column in same table for example,

alter table project11_module79 add column newCol text;

It gives an error saying,

Error Code: 1118. Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs


Thank you.