Comments - Troubleshooting Row Size Too Large Errors with InnoDB
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
The script rowcount.sh was very useful to me but it may fail with error 1064. I got
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-home' at line 1
This happened because a table of mine uses the hyphen (-) in its name. The script should be changed to use backticks (`) around table names. Unfortunately I'm not good enough with shell scripting to provide a fix myself.
You write "copy the code below to a shell-script named rowcount.sh", but it should be "copy the code below to a shell-script named rowsize.sh"
Thanks, fixed.
Your script after "Finding All Tables That Currently Have the Problem" fails if table names contain spaces.
This could be corrected if the line:
for t in $(mysql -h $1 -u $2 -p$3 -ABNe "show tables;" $d)
were replaced by:
mysql -h $1 -u $2 -p$3 -ABNe "show tables;" $d|while read t; do
and $t twice by \`$t\`
after "show create table" and "drop table if exists".
In our case the Solution was:
1. Export .sql file table data & structure 2. Open with your prefer text editor 3. Replace all Varchar type to text 4. Save 5. Go to your DBMS and Delete table 6. Upload or execute the new .sql query
Note: My problem started after to upgrade from MariaDB 10.1 to MariaDB 10.2
Regards!
We have this issue with tables that we cannot change the format of; we have changed to "dynamic" but this still produces the warnings, so there seems to be no real solution here.
However we are unable to reproduce any actual issue. The above paragraphs state "Regardless of whether InnoDB strict mode is enabled, if a DML statement is executed that attempts to write a row that the table's InnoDB row format can't store, then InnoDB will raise an error with this message. ". We cannot reproduce any error upon DML even though our tables emit this message, and I wrote a script to test the above sample table with "col1-col198" to see if there is in fact any DML which fails; I've inserted tens of thousands of rows into the table with all combinations of varchar data and no DML statement fails, all rows can be SELECTed back with complete accuracy. So it's unclear what this error at the DDL level actually means, can a concrete example of what will cause actual functionality to fail be added here?