MySQL error 1118: Row size too large

đź’¬ General Discussion
,
1

Hello all,

We have more than 300 columns in a particular table. Now when we are trying to add new columns to the table. We are getting the below error. Anyone knows how to resolve it?

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

v7.13 | MariaDB 10.6 | PHP 7.4

I already tried below solutions.

SET GLOBAL innodb_strict_mode = 0;

ALTER TABLE tableName ROW_FORMAT=DYNAMIC;

2

Sorry, but if you have more than 300 columns in a table, you’re not doing relational database design right…

You have to ask yourself, from those 300 columns, that’s not all undifferentiated chaos, is it? Find the logic and organize your information in related tables as rows.

3

Thank you so much!

Find the logic and organize your information in related tables as rows .

How could I achieve that? Is there a way in CRM to store data in new table and display data at particular module?

4

It’s very hard to have this discussion without some notion of what the 300 columns are about.

Note that extra related tables in the DB will be reflected as extra (related) custom modules in the CRM.

5

Got it!

Does anyone know to change varchar fields to text fields in the SuiteCRM?

image
image613Ă—750 53.2 KB

Maybe I could change my fields from varchar(255) to text/varchar(127).

Otherwise, I will have to add new custom module as suggested by pgr.

6

What about change it to “Text Area” ? And then re-deploy it from Studio?

7

Worked!

8

If I were you, I would still revise my DB design. Having a ton of disorganized information in a single row means that you won’t be able to reap the benefits of a proper organization. You will have trouble searching and filtering, you will waste space needlessly repeating data, etc.

9

Yes, I need to learn how to create custom module in CRM. Maybe there are another ways to achieve it.

If I find out anything, I will post here.

1 Like