0

I know this question is asked a lot and I also found a lot advice, but none that is practicable for me. My website is hostet on siteground.com. Everything works fine so far and suddenly I got

Error: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

on a "random" basis.

Actually my whole website (joomla 3.7.4 / PHP 7.0.22 /sql-server: 5.6.36-82.1-log - Percona Server (GPL), Release 82.1, Revision 1a00d79) with lot of custom code isn't usable anymore because of this.

Now I understand that this has something to do with the total Row size, but as an database and php-amateur I still don't get it.

  • Are there too much colums in one row, or is there too much Data in the columns?
  • What ist ment by "Row size too large (> 8126)"? Currently there are 81 Columns per row and according to phpMyAdmin the whole table needs 32KiB of Data, 16KiB of Index and alltogether 48 KiB. If I look at other Tables of the Joomla-System this is a pretty small amount of Data (consisting out of serialized arrays, some text and thats it)
  • Why do this issue come "over-night" - there was no update or any changes on db-strukture from my side.
  • How can I determine or see the actual Row size, so I can "try" arround to fit the db-settings needs?

Can I do something about that problem? Or am I "stranded" if the hoster can't or won't changed something in the settings?

What I have done so far:

Since I have no access to server-settings I startet to limit all "int" and "char". After no effect I startet to change all Typs to "medium-txt" and "tiny-txt". In round #3 I changed all Typs to "txt", but also no changed result. I additionally converted all utf8_general_ci to utf8mb4_general_ci. Basically I did all I could find but no successs.

As you can read out of the above text I'm like a blind guy lost in the woods who desperate tries everything without any expertise behind, to solve that issue.

Adittional Info (akkording to phpMyAdmin):

  • innodb log file size = 128MiB
  • innodb_strict_mode = OFF
  • Tabel-Typ = InnoDB

Any help would be appreciated. Either in possible solutions or understanding of the actuall issue iteself. Thx in advanced!

Jakob
  • 25
  • 1
  • 2
  • 10
  • Did you see [this question](https://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large) ? – James Aug 14 '17 at 21:03

2 Answers2

2

I don't know if anyone is still having this issue, Hope this helps.

When trying to add a new table using a script or SQL file upload. If your table engine is anything else change it to MYISAM

CREATE TABLE `your_table_name_here` (
  `id`  int(11)  NOT NULL,
 ..............
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

On a current table having an error when adding new columns

USE your_db_name_goes_here;
ALTER TABLE your_table_name_here ENGINE=MYISAM;
gabisajr
  • 146
  • 1
  • 5
0

Have a look to MySQL documentation about Row Size Limit here: https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html it might be useful.

Mukhammadsher
  • 182
  • 3
  • 21