how to set innodb_strict_mode = 0 in phpmyadmin or xammp

Admin 1
Viewed 553

How can to set innodb_strict_mode = 0 in phpmyadmin because i'm having a problem in MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB.

I got this error when i set 96 entities in my table with a random datatypes consisting of int, varchar, text and date.

--
-- Table structure for table `tbl_student`
--

CREATE TABLE `tbl_student` (
  `s_id` int(11) NOT NULL,
  `ss_id` varchar(20) DEFAULT NULL,
  `sfname` varchar(50) DEFAULT NULL,
  `smname` varchar(50) DEFAULT NULL,
  `slname` varchar(50) DEFAULT NULL,
  `snext` varchar(10) DEFAULT NULL,
  `sdbirth` varchar(10) DEFAULT NULL,
  `sgender` varchar(10) DEFAULT NULL,
  `saddress` text DEFAULT NULL,
  `szcode` varchar(10) DEFAULT NULL,
  `scontact` varchar(20) DEFAULT NULL,
  `semail` varchar(50) DEFAULT NULL,
  `sctship` varchar(20) DEFAULT NULL,
  `scivilstat` varchar(20) DEFAULT NULL,
  `spbirth` text DEFAULT NULL,
  `sdisability` varchar(20) DEFAULT NULL,
  `s4psno` varchar(20) DEFAULT NULL,
  `spwdid` varchar(20) DEFAULT NULL,
  `srappsship` text DEFAULT NULL,
  `srappnas` text DEFAULT NULL,
  `sbos` text DEFAULT NULL,
  `ssskills` text DEFAULT NULL,
  `stwinterest` text DEFAULT NULL,
  `ssdfile` varchar(10) DEFAULT NULL,
  `sgfname` varchar(50) DEFAULT NULL,
  `sgmname` varchar(50) DEFAULT NULL,
  `sglname` varchar(50) DEFAULT NULL,
  `sgnext` varchar(10) DEFAULT NULL,
  `sglstatus` varchar(10) DEFAULT NULL,
  `sgeduc` varchar(30) DEFAULT NULL,
  `sgcontact` varchar(20) DEFAULT NULL,
  `sgaddress` text DEFAULT NULL,
  `sgoccu` varchar(30) DEFAULT NULL,
  `sgcompany` varchar(30) DEFAULT NULL,
  `sffname` varchar(50) DEFAULT NULL,
  `sfmname` varchar(50) DEFAULT NULL,
  `sflname` varchar(50) DEFAULT NULL,
  `sfnext` varchar(10) DEFAULT NULL,
  `sflstatus` varchar(10) DEFAULT NULL,
  `sfeduc` varchar(30) DEFAULT NULL,
  `sfcontact` varchar(20) DEFAULT NULL,
  `sfaddress` text DEFAULT NULL,
  `sfoccu` varchar(30) DEFAULT NULL,
  `sfcompany` varchar(30) DEFAULT NULL,
  `smfname` varchar(50) DEFAULT NULL,
  `smmname` varchar(50) DEFAULT NULL,
  `smlname` varchar(50) DEFAULT NULL,
  `smnext` varchar(10) DEFAULT NULL,
  `smlstatus` varchar(10) DEFAULT NULL,
  `smeduc` varchar(30) DEFAULT NULL,
  `smcontact` varchar(20) DEFAULT NULL,
  `smaddress` text DEFAULT NULL,
  `smoccu` varchar(30) DEFAULT NULL,
  `smcompany` varchar(30) DEFAULT NULL,
  `snsibling` varchar(10) DEFAULT NULL,
  `spcyincome` varchar(50) DEFAULT NULL,
  `spschname` text DEFAULT NULL,
  `spsaddress` text DEFAULT NULL,
  `spstype` varchar(20) DEFAULT NULL,
  `spscourse` varchar(50) DEFAULT NULL,
  `spsyrlvl` varchar(20) DEFAULT NULL,
  `spsgwa` int(10) DEFAULT NULL,
  `spsraward` text DEFAULT NULL,
  `spsdawardrceive` date DEFAULT NULL,
  `scsintend` text DEFAULT NULL,
  `scsadd` text DEFAULT NULL,
  `scschooltype` varchar(20) DEFAULT NULL,
  `sccourse` varchar(50) DEFAULT NULL,
  `sccourseprio` varchar(50) DEFAULT NULL,
  `scsyrlvl` varchar(20) DEFAULT NULL,
  `spass` varchar(100) DEFAULT NULL,
  `sdsprc` varchar(20) DEFAULT NULL,
  `sdsprcstat` varchar(20) DEFAULT NULL,
  `sdspgm` varchar(20) DEFAULT NULL,
  `sdspgmstat` varchar(20) DEFAULT NULL,
  `sdspcr` varchar(20) DEFAULT NULL,
  `sdspcrstat` varchar(20) DEFAULT NULL,
  `sdstbytpic` varchar(20) DEFAULT NULL,
  `sdstbytpicstat` varchar(20) DEFAULT NULL,
  `sdsbrgyin` varchar(20) DEFAULT NULL,
  `sdsbrgyinstat` varchar(20) DEFAULT NULL,
  `sdscef` varchar(20) DEFAULT NULL,
  `sdscefstat` varchar(20) DEFAULT NULL,
  `sdspsa` varchar(20) DEFAULT NULL,
  `sdspsastat` varchar(20) DEFAULT NULL,
  `sdsobr` varchar(20) NOT NULL,
  `sdsobrstat` varchar(20) NOT NULL,
  `student_profile_image` varchar(100) DEFAULT NULL,
  `s_added_on` date DEFAULT NULL,
  `s_applied_on` date DEFAULT NULL,
  `s_verification_code` varchar(100) DEFAULT NULL,
  `s_email_verify` varchar(10) DEFAULT NULL,
  `s_account_status` varchar(20) DEFAULT NULL,
  `s_grant_stat` varchar(20) NOT NULL,
  `s_scholar_stat` varchar(20) NOT NULL,
  `s_scholarship_type` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Here are my entities in my table. I tried to change something to data types and number I can't change and nothing happens.

mysql sql
1 Answers

You can set innodb_strict_mode to OFF with the SQL statement

SET innodb_strict_mode=OFF;

But that's kind of like catching and ignoring exceptions: all you're doing is concealing serious errors from your application.

The row-length limit in MySQL / MariaDB / InnoDB is a hard limit. If you try to exceed it your INSERT and UPDATE statements will fail.

You said your requirement is to extend a table with a few extra columns. And, in doing that you've hit the row-length limit. It happens (Remarks about the programmer who came before you removed ... this is a family-friendly web site.)

What can you do?

An easy fix: Change the table's CHARSET from utf8mb4 to utf8. That way you need three, rather than four bytes, per VARCHAR character. But utf8 is deprecated: it can't represent emojis and some other characters.

Next possible fix. Identify the VARCHAR columns that don't need Unicode. sdbirth? s_verification_code? student_profile_image? Declare those colums with

CHARACTER SET latin1 COLLATE latin1_bin

If you make this change to s_verification_code and student_profile_image you'll save 600 bytes-- one byte per character rather than four, for two 100-character columns. That's enough that you can probably finish this task and pass off this table to your (unsuspecting) successor.

Another idea Change some of your VARCHAR() columns to TEXT columns. But beware! this slows down queries. If your application has mostly SELECT * queries in it you probably don't want to do this. If it has mostly SELECT column, column, column queries this is probably OK.

A slightly harder fix: Reduce the width of some of your fields. Does every personal name have to be stored as VARCHAR(50)? Will VARCHAR(30) do the trick? Do you need 10 characters for gender? Can you represent date-of-birth with a DATE datatype? It will take three bytes rather than the 40 (VARCHAR(10)) it now uses.

The right fix Rework your app so some of that data (contact information, etc) for each student lives in different tables. Or, create a new table for the new data you're adding.

user_2056080
Related