MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB

Question

I want to create a table of 325 column:

CREATE TABLE NAMESCHEMA.NAMETABLE 
(   
      ROW_ID TEXT NOT NULL ,        //this is the primary key

324 column of these types:
      CHAR(1), 
      DATE, 
      DECIMAL(10,0), 
      DECIMAL(10,7), 
      TEXT, 
      LONG,

) ROW_FORMAT=COMPRESSED;

I replaced all the VARCHAR with the TEXT and i have added Barracuda in the my.ini file of MySQL, this is the attributes added:

innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_file_format_check = ON

but i still have this error:

Error Code: 1118
 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.

EDIT: I can't change the structure of the database because it's legacy application/system/database. The create of a new table, it's an export of the legacy database.

EDIT2: i wrote this question that is similar to others but inside there are some solution that i found on internet like VARCHAR and Barracuda, but i still have that problem so i decided to open a new question with already the classic answer inside for seeing if someone have other answers

Centistere

A TEXT column as the primary key sounds really strange. Why not use an integer or a "regular" varchar column? – Kinna

@pathikrit i have wrote EDIT 2 where i explane why i have created this question. For me its similar but not a real duplicate, but if its a duplicate what i must to do? i must to delete it? – Centistere

Two many vague things in your description. Please provide SHOW CREATE TABLE, but you can leave out most of the 324. – Cyclotron

If you are "splaying an array across columns", see the 'right' way to fix the problem: https://mcmap.net/q/158442/-change-limit-for-quot-mysql-row-size-too-large-quot – Cyclotron

In case it were not the log file limit ... will changing the type from longtext to text or blob make a difference? – Erythroblastosis

In my.ini file, you can set innodb_log_file_size=512M and add innodb_strict_mode = 0 – Megrim

Answer 1

I struggled with the same error code recently, due to a change in MySQL Server 5.6.20. I was able to solve the problem by changing the innodb_log_file_size in the my.ini text file.

In the release notes, it is explained that an innodb_log_file_size that is too small will trigger a "Row size too large error."

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html

Bath

I used this settings in "my.ini" and than it worked for me. innodb_log_file_size = 500M innodb_log_buffer_size = 800M – Oshaughnessy

@Oshaughnessy are you sure it is ok for innodb_log_buffer_size to be set to such a high value? The default setting is 4M and the docs say "As it is flushed once per second anyway, it does not make sense to have it very large (even with long transactions)". There's another option innodb_buffer_pool_size (looks similar) for which 800M makes probably more sense (docs: "The bigger you set this the less disk I/O is needed to access data in tables"). – Aptitude

Increasing innodb_log_file_size fixed my problem too. Thanks. – Miltiades

@Oshaughnessy By the way the docs say, As a result of the redo log BLOB write limit introduced for MySQL 5.6, the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). You might not need to bump them up as much as you did. – Praenomen

innodb_log_file_size can't be modified in Amazon RDS. Does any one know how to get around this? – Azevedo

where this file is located in mac OS X? – Bilge

@Azevedo - RDS has a way of letting you set configuration values, then restart the server. – Cyclotron

Changing the innodb_log_file_size is not enough anymore please add innodb_strict_mode = 0, to your answer :) – Danelledanete

Confirmed that this answer no longer fully works in 2020. Look at Stefano's. – Tolerate

Answer 2

I tried all the solutions here, but only this parameter

innodb_strict_mode             = 0

solved my day...

From the manual:

The innodb_strict_mode setting affects the handling of syntax errors for CREATE TABLE, ALTER TABLE and CREATE INDEX statements. innodb_strict_mode also enables a record size check, so that an INSERT or UPDATE never fails due to the record being too large for the selected page size.

Drub

Thanks for this solution! You saved my day! I am working with doctrine 2 in symfony 3 project where we have around 200 columns and I was getting this error while generating the entity. – Kreis

Did you check to see if your text was truncated? – Cyclotron

Surely this doesn't actually solve the problem. You're just turning off the validation to check the record size. – Screw

disabling the check is not a solution – Desmund

this worked when all else failed or only worked temporarily. note* increasing my innodb_log_file_size froze my myswl server from starting... not certain why. this worked like a charm! – Francisco

Ugh, I want to upvote because this works but I'm not entirely sure I'm comfortable with this solution. – Lubberly

Man... this was it... solved my pain trying to import a database of 70 tables in HestiaCP... was breaking on the 45th table and didn't move forward, neither by PHPMyAdmin, or MySQL Console... – Nore

Without restarting the server setting SET GLOBAL innodb_strict_mode = 0; and SET innodb_strict_mode = 0; worked for me – Brucebrucellosis

is there have solution on shared server, we haven't privilege to run this sql or change mysql settings – Motch

SET GLOBAL innodb_strict_mode = 0; – Montevideo

Answer 3

ERROR 1118 (42000) at line 1852:    
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.

---

[mysqld]

innodb_log_file_size = 512M

innodb_strict_mode = 0

ubuntu 16.04 edit path:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

on MS Windows the path will be something like:

C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

Don't forget to retart the service (or restart your machine)

Car

This worked for me! Is it safe to remove these settings after import - or will it break? – Manson

Changing innodb_log_file_size (including removing it from my.cnf) after the files have been built -- this is a nontrivial process; do not plan on doing such. – Cyclotron

If I add innodb_log_file_size = 512M to my config file, MySQL fails to restart. – Screw

@Screw I had the same problem on XAMPP windows 10. I have provided my solution for future reference. – Convocation

i added hust innodb_strict_mode = 0 and it worked – Backsword

Disabling the innodb_strict_mode it's not the best solution. Check other answers (innodb_page_size), it's better to solve the problem instead of removing the security checks. – Damoiselle

this helped as was having an issue with a create table statement. – Louannlouanna

Answer 4

The key parameter is: innodb_page_size

Support for 32k and 64k page sizes was added in MySQL 5.7. For both 32k and 64k page sizes, the maximum row length is approximately 16000 bytes.

The trick is that this parameter can be only changed during the INITIALIZATION of the mysql service instance, so it does not have any affect if you change this parameter after the instance is already initialized (the very first run of the instance).

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. See Section 14.6.1, “InnoDB Startup Configuration”.

So if you do not change this value in my.ini before initialization, the default value will be 16K, which will have row size limit of ~8K. Thats why the error comes up.

If you increase the innodb_page_size, the innodb_log_buffer_size must be also increased. Set it at least to 16M. Also if the ROW_FORMAT is set to COMPRESSED you cannot increase innodb_page_size to 32k, or 64K. It should be DYNAMIC (default in 5.7).

ROW_FORMAT=COMPRESSED is not supported when innodb_page_size is set to 32KB or 64KB. For innodb_page_size=32k, extent size is 2MB. For innodb_page_size=64k, extent size is 4MB. innodb_log_buffer_size should be set to at least 16M (the default) when using 32k or 64k page sizes.

Furthermore the innodb_buffer_pool_size should be increased from 128M to 512M at least, otherwise you will get an error on initialization of the instance (I do not have the exact error).

After this, the row size error gone.

The problem with this is that you have to create a new MySql instance, and migrate data to your new DataBase instance, from old one.

Parameters that I changed and works (after creating a new instance and initialized with the my.ini that is first modified with these settings):

innodb_page_size=64k
innodb_log_buffer_size=32M
innodb_buffer_pool_size=512M

All the settings and descriptions in which I found the solution can be found here:

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

Hope this helps!

Regards!

Butyraldehyde

I legitimately answered a bunch of q's so I could get enough reputation to comment - THANK YOU FOR THIS FIX. For anyone else attempting this approach: I had an issue whereby on completion of MySQL installation the server would by started automatically (initialising the database with the default innodb parameters, preventing this fix from being possible). The way I got around this was to place the 'my.ini/my.conf' configuration file (with @fefe's innodb values) BEFORE installing MySQL. Thanks again you absolute legend. – Cobden

This is a much more useful answer than the ones just telling you to turn off strict mode. – Codex

Answer 5

Have similar issue this morning and following way saved my life:

Did you try to turn off the innodb_strict_mode?

SET GLOBAL innodb_strict_mode = 0;

and then try to import it again.

innodb_strict_mode is ON using MySQL >= 5.7.7, before it was OFF.

Stuccowork

for me its showing #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation – Heiress

Yes, your user need to have the required privileges. – Stuccowork

This worked perfectly for me, after upgrading to Zabbix 5. – Kalynkam

Answer 6

For MariaDB users (version >= 10.2.2) and MySQL (version >= 5.7), the simple solution is:

ALTER TABLE `table` ROW_FORMAT=DYNAMIC;

Mailable

You need to have a recent version. E.g. MariaDB version >= 10.2.2 or mysql version >= 5.7 ! – Mailable

My export failed because the creation of a few tables failed. Changing the tables then worked as expected, and I had no need for changing the innodb_strict_mode value. – Janicejanicki

Thanks, this saved my day during GLPI upgrade. github.com/glpi-project/glpi/issues/7697 – Russell

I am totally confused, 12 votes up till 2021, but I can't alter the table which is not yet created. – Retrograde

@AdiPrasetyo you can add that option on table creation CREATE TABLE some_table ( id int(11) NOT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC; – Behling

Answer 7

If InnoDB strict mode is enabled this error can show.

Check enabled or not

SHOW  variables LIKE '%strict%';

If enable then you can disable.

SET GLOBAL innodb_strict_mode=OFF;

For more detail information read here>>

Punishable

this just works for me. Thanks Nanhe – Kittle

This work for me too... tnx... – Delmydeloach

Answer 8

I had the issue when importing SQL-dumps (from MySQL 8) to MariaDB on MacOS (with Brew).

Start by editing your my.cnf.
If you use Brew, it's usually store at /usr/local/etc/:

pico /usr/local/etc/my.cnf

Add this to the config:

[mysqld]
innodb_log_file_size = 1024M
innodb_strict_mode = 0

Then restart MariaDB:

brew services restart mariadb

Please notice that this in a workaround and not a fix since turning of strict mode in not fixing the problem, but since it's my local environment and not a production environment i'm ok with that.

Incapacitate

Answer 9

I recently created a table with 82 columns and had the same error with InnoDB. To bypass the problem we switched the table format to MyISAM as it was just used for a basic form.

Runstadler

I have problems because of MyISAM, and I am getting this error trying to convert them to InnoDB... – Rigadoon

@Rigadoon - Shrink the limits to reasonable values; use ascii if you will only have ascii characters (eg, postal code, hex). – Cyclotron

I recently tried similar, 62 columns with Varchar 255 in InnoDB, I resolved it by using reasionable length for each column instead of the 255. I used the largest value from all rows and 2x the size and compared if that exceeds the 255 so I would use TEXT, rether than varchar 255. the trick did it. :) – Concepcionconcept

Answer 10

MySQL is pretty clear about its maximum row size:

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

. . .

Individual storage engines might impose additional restrictions that limit table column count. Examples:

InnoDB permits up to 1000 columns.

InnoDB restricts row size to something less than half a database page (approximately 8000 bytes), not including VARBINARY, VARCHAR, BLOB, or TEXT columns.

Different InnoDB storage formats (COMPRESSED, REDUNDANT) use different amounts of page header and trailer data, which affects the amount of storage available for rows.

If you have 325 repeating sets of columns, you are exceeding several of the restrictions. This is also a suspicious data format. You should have 325 rows for each row in the table you want, one for each group of columns.

Clupeid

First: THX for the advices! Second: What you are saying it's true, but i can't change the structure of the database because it's legacy application/system/database. The create of a new table, it's an export of the legacy database. – Centistere

@user3410465 . . . You have to. The database doesn't support what you are trying to do. You could use a different database, but still having hundreds of repeating blocks of columns seems like a bad idea. – Clupeid

Perhaps the export step could restructure and put the 324 sets of columns in a child table that has one row per non-empty set? – Fley

That quote needs fixing. MyISAM and InnoDB each allow rows to contain more than 64K bytes. – Cyclotron

Answer 11

Changing into MyISAM is not the solution. For innodb following worked for me on mysql 8.0.27 on a huge server.

set followings on my.cnf and initialize it. Make sure you have taken backups if databases exist as initializing needs to be remove the data directory.

innodb-strict-mode=OFF
innodb-page-size=64K
innodb-log-buffer-size=256M
innodb-log-file-size=1G
innodb-data-file-path=ibdata1:2G:autoextend

Statesmanship

Answer 12

I just want to provide some other people with help with a more serious variant of this problem. In some situations, the error ("Row size too large .. Changing some columns to TEXT or BLOB") will occur even with "alter table drop column" and "alter table modify column" statements!

Consequently you can become completely stuck, not able to change a varchar to a text, or drop columns (trying to solve the problem ironically results in the same message).

If you have this problem, the solution is to alter or drop multiple columns at once. You can do this in MySQL with the syntax "alter table example drop column a, drop column b, drop column c" and if you drop enough columns at once, it will actually execute rather than raising the error.

Passible

Answer 13

For MySQL 5.7 on Mac OS X El Capitan:

OS X provides example configuration files at /usr/local/mysql/support-files/my-default.cnf

To add variables, first stop the server and just copy above file to, /usr/local/mysql/etc/my.cnf

cmd : sudo cp /usr/local/mysql/support-files/my-default.cnf /usr/local/mysql/etc/my.cnf

NOTE: create 'etc' folder under 'mysql' in case it doesn't exists.

cmd : sudo mkdir /usr/local/mysql/etc

Once the my.cnf is created under etc. it's time to set variable inside that.

cmd: sudo nano my.cnf

set variables below [mysqld]

[mysqld]
innodb_log_file_size = 512M
innodb_strict_mode = 0

now start a server!

Bilge

Answer 14

innodb_log_file_size=512M

innodb_strict_mode=0

These two lines worked for me, in the mysql configuration !

Electroplate

Jose, you should always answer in english in stackoverflow.com. There's stackoverflow en español if you prefer to collaborate in spanish – Interpol

Answer 15

The following worked for me, nothing else -:

SET GLOBAL innodb_log_buffer_size = 80*1024*1024*1024;

and

SET GLOBAL innodb_strict_mode = 0;

Hope this helps someone because it wasted couple of days of my time as I was trying to do this in my.cnf with no joy.

Honkytonk

Executing SET GLOBAL innodb_log_buffer_size = 80*1024*1024*1024; I get en error Variable innodb_log_buffer_size is read only – Katlynkatmai

Answer 16

I also encountered that. Changing "innodb_log_file_size","innodb_log_buffer_size" and the other settings in "my.ini" file did not solve my problem. I pass it by changing my column types "text" to varchar(20) and not using varchar values bigger than 20 . Maybe you can decrease the size of columns, too, if it possible. text--->varchar(20) varchar(256) --> varchar(20)

Romilly

I thought that TEXT is smaller than VARCHAR, as it is just and pointer, isn't it? – Cazares

Blind use of VARCHAR(255) is the cause of most of the chatter on this Question. – Cyclotron

TEXT is implemented as a 2-byte length field plus up to 64KB of text. VARCHAR is a 1- or 2- byte length field, plus the number of bytes needed for the max length in the given CHARACTER SET. – Cyclotron

I solved it similair to this: When importing in phpmyAdmin I had as output also the scheme of the table, but without data, so I copied that and inserted the scheme adjusted types and max values. After that I imported the CSV data file in this table with columns defined and it worked. So first make structure. Also dont forget to remove the first row of the csv since it contains column names, you can do this after importing. BUt assing an index if there is not an index yet, give unique poperty then you can adjust data through phpmyadmin, otherwise you can adjust through sql queries. – Recombination

Answer 17

What fixed mine was to add

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;

At the beginning of my ".sql" file, as it is said in: https://gist.github.com/tonykwon/8910261

Rozanna

Answer 18

I was having same issue. I search "innodb_strict_mode" in my.ini but couldn't found.

I then added the same, it will still show you the warning, but you can continue. just add

innodb_strict_mode = 0;

Henotheism

Answer 19

I was using XAMPP on Windows 10 and had this issue using PHPMyAdmin.

when I added innodb_log_file_size = 500M and innodb_log_buffer_size = 800M to my my.ini file, MySQL would not start.

So I tried deleting ib_logfile0 and ib_logfile1 located in (C:\xampp\mysql\data) and this did not help at all.

luckily I could re-install (I needed to upgrade XAMPP anyway)

The simple solution in my case was to set innodb_strict_mode=0 in the my.ini file.

After this I was able to create the table.

STEPS:

1. Close XAMPP completely.
2. Edit the my.ini file (located in C:\xampp\mysql\data) add innodb_strict_mode=0 in the InnoDB section.
3. Start XAMPP and import the table again.

N.B complete these steps as ADMIN

Convocation

Answer 20

Tried many things but found the solution by adding the below line in my.ini and restarting the MySQL service.

innodb_strict_mode = 0

Daune

Answer 21

MariaDB has a fairly lengthy document specifically on this issue showing how and why with several ways to resolve it.

Troubleshooting Row Size Too Large Errors With InnoDB

Possible Options:

• Converting the Table to the DYNAMIC Row Format (This is default is newer versions, so may not work if you're already set to dynamic)
• Converting Some Columns to BLOB or TEXT
• Increasing the Length of VARBINARY Columns
• Increasing the Length of VARCHAR Columns
• Refactoring the Table into Multiple Tables
• Refactoring Some Columns into JSON
• Disabling InnoDB Strict Mode ("Unsafe" way)

Ashleighashlen

Answer 22

SET SESSION innodb_strict_mode=OFF;

This did it for me. None of the above fixed it, SET GLOBAL innodb_strict_mode=OFF; included and i don't have access or the right to modify exist database config since i'm working for an outside client.

While it did work when added to a script to with the problem to execute in workbench, you could run into this : according documentation : SET GLOBAL doesnt affect current session (the one where you write SET GLOBAL) so you could have problems if you insert it in the script where the lines that need innodb_strict_mode to OFF are included.

work the source : mariaDB documentation

=> GLOBAL / SESSION

When setting a system variable, the scope can be specified as either GLOBAL or SESSION.

A global variable change affects all new sessions. It does not affect any currently open sessions, including the one that made the change.

A session variable change affects the current session only.

If the variable has a session value, not specifying either GLOBAL or SESSION will be the same as specifying SESSION. If the variable only has a global value, not specifying GLOBAL or SESSION will apply to the change to the global value.

Araldo

Answer 23

• sql_mode=""
• innodb_strict_mode=0
• brew services stop mariadb
• brew services start mariadb

Limacine

Answer 24

None of the answers to date mention the effect of the innodb_page_size parameter. Possibly because changing this parameter was not a supported operation prior to MySQL 5.7.6. From the documentation:

The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a database page for 4KB, 8KB, 16KB, and 32KB page sizes. For example, the maximum row length for the default innodb_page_size of 16KB is about 8000 bytes. For an InnoDB page size of 64KB, the maximum row length is about 16000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB.

Note that increasing the page size is not without its drawbacks. Again from the documentation:

As of MySQL 5.7.6, 32KB and 64KB page sizes are supported but ROW_FORMAT=COMPRESSED is still unsupported for page sizes greater than 16KB. For both 32KB and 64KB page sizes, the maximum record size is 16KB. For innodb_page_size=32k, extent size is 2MB. For innodb_page_size=64k, extent size is 4MB.

A MySQL instance using a particular InnoDB page size cannot use data files or log files from an instance that uses a different page size. This limitation could affect restore or downgrade operations using data from MySQL 5.6, which does support page sizes other than 16KB.

Indiscipline

Also, all InnoDB tables on a single instance share the same page size. – Cyclotron

Answer 25

FIX FOR MYSQL IN DOCKER

I'm using @fefe's excellent answer here to show how to fix this problem within some minutes when using docker (via docker-compose). It's quite easy as you don't have to touch MySQL's configuration files, but it requires you to export and import your entire data:

The default situation of your MySQL setup probably looks like this. Your data is saved inside the data-mysql volume.

mysql:
  image: mysql:5.7.25
  container_name: mysql
  restart: always
  volumes:
    - data-mysql:/var/lib/mysql
  environment:
    - "MYSQL_DATABASE=XXX"
    - "MYSQL_USER=XXX"
    - "MYSQL_PASSWORD=XXX"
    - "MYSQL_ROOT_PASSWORD=XXX"
  expose:
    - 3306

1. Make a backup of your entire data/database via SQL export, so you have a .sql.gz or something. I'm using Adminer for this.

2. To fix (and as explained in @fefe's answer) we have to setup the MySQL instance from zero, meaning we have to delete the mysql docker container and the mysql volume docker container. Do a docker container ls and a docker volume ls to see all your containers and volumes, and pick the two names that are your mysql instance and your mysql volume, for me it's mysql (container) and docker_data-mysql (volume).

3. Stop your running instances via docker-compose down (or however you usually stop your docker stuff).

4. To delete them, I do docker container rm mysql and docker volume rm docker_data-mysql (note that there is an underscore AND a dash in the name).

5. Add these settings to your mysql block in your docker setup:

mysql:
  image: mysql:5.7.25
  command: ['--innodb_page_size=64k', '--innodb_log_buffer_size=32M', '--innodb_buffer_pool_size=512M']
  container_name: mysql
  # ...

6. Restart your instances, the mysql and mysql volume should be build automatically, now with the new settings.

7. Import your database dump file, maybe with:

gzip -dc < database.sql.gz | docker exec -i mysql mysql -uroot -pYOURPASSWORD

Voila! Worked very fine for me!

Beria

Answer 26

I have changed the length of value from varchar(255) to varchar(25) to all varchar columns and i get the solution.

Poundage

Answer 27

if you are using the MySQLWorkbench you have the option to change the to change the query_alloc_block_size= 16258 and save it.

Step 1. click on the options file at the left side.

Step 2: click on General and select the checkBox of query_alloc_block_size and increase their size. for example change 8129 --> 16258

Seriate

Answer 28

On my case it was casing from Limits on Table Column Count and Row Size and doing changes described in this answer saved my day.

1. Add the following to the my.cnf file under [mysqld] section.

   innodb_file_per_table
   innodb_file_format = Barracuda

2. ALTER the table to use ROW_FORMAT=COMPRESSED.

   ALTER TABLE table_name
   ENGINE=InnoDB
   ROW_FORMAT=COMPRESSED
   KEY_BLOCK_SIZE=8;

https://mcmap.net/q/158442/-change-limit-for-quot-mysql-row-size-too-large-quot

Hefter

Answer 29

If you're getting this error on Google Cloud SQL (mysql 5.7 for example) then it's probably not at this time going to be a simple fix as not all InnoDB flags are supported. If you're coming across from Mysql 5.5 as I was (for an old Wordpress setup) this could mean you need to wrangle some column types in the source database before you export.

Some more information can be found here.

Kwangchowan

Answer 30

Switch the table format InnoDB to MyISAM

for change table format

run this query

ALTER TABLE table_name ENGINE = MyISAM;

(replace table_name with actual table name)

Formwork

This don't solve the problem, you are doing a workaround with something that is totally different. I don't say that this won't work, but this won't help, you have to know what is the difference between InnoDB and MyISAM before do this, and is not a light difference. Your answer is like "I've a C# syntax error, forget, I will rewrite everything in PHP" – Shangrila

@LeandroBardelli , this will solved the problem for some, if you are creating the tables, the main difference is in the transactionality. InnoDB is a transactional storage engine, while MyISAM belongs to the non-transactional category. Your example does not have a comparison.... , it worked for me – Dog

My problem was a MySQL database in Cpanel, this was an alternative for me, of course considering the transactionality of the information – Dog

@ErikRoky I didn't say it didn't work, I said is totally overkill. If I replace MySQL with SQL Server for sure I won't have the error anymore. – Shangrila

Answer 31

I experienced the same issue on an import of a data dump. Temporarily disabling the innodb strict mode solved my problem.

-- shows the acutal value of the variable
SHOW VARIABLES WHERE variable_name = 'innodb_strict_mode';

-- change the value (ON/OFF)
SET GLOBAL innodb_strict_mode=ON;

Harts

Answer 32

In the case that this message appears when changing MariaDB version, I had exactly the same issue changing to MariaDB 10.6.5 and that's how I solved the issue:

1. Using PhpMyAdmin, I exported the .sql file from the old MariaDB version
2. Edited the .sql file using an editor such as Notepad++ and added the line SET GLOBAL innodb_default_row_format='dynamic'; on top as follows:

-- phpMyAdmin SQL Dump
-- version 5.1.1
-- https://www.phpmyadmin.net/
--
-- Host: (*Your host*)
-- Generation Time: Feb 12, 2022 at 05:22 PM
-- Server version: 10.6.4-MariaDB
-- PHP Version: 8.0.3

SET GLOBAL innodb_default_row_format='dynamic';
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

3.Imported the altered .sql file to MariaDB 10.6.5

All worked fine

Illustrational

Answer 33

I'm using mysql 5.7.36

The below changes to my.ini file worked for me:

max_allowed_packet = 900M
innodb-default-row-format = dynamic
innodb-lock-wait-timeout = 1200
innodb-log-buffer-size=512M
innodb-log-file-size = 1G

Although I cannot confirm, but I think the 1st 2 settings made the most impact.

Restart the service for the changes to take effect.

Litton

Answer 34

The innodb_strict_mode = 0 solution works but it's not secure to use it.

One solution it's to change the type of the tables of glpi to DYNAMIC.

I ran this command to get the ALTER TABLE commands

use information_schema;    
SELECT  CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ROW_FORMAT=DYNAMIC;")  AS MySQLCMD FROM TABLES  WHERE TABLE_SCHEMA = "glpi";

That command will return a list of commands you should run to change the row format of all glpi tables. Change the glpi within the quotes to fit the name of your database.

Damoiselle

Answer 35

This worked for me on shared hosting (Godaddy):

SET innodb_strict_mode = 0; ALTER TABLE your_table_name DROP columnA, DROP columnB, DROP columnC

Uppermost