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

navdeepghai · June 28, 2022, 3:19pm

I am not able to add fields more than 250 using DocType, I am getting following error when I am trying to do so:

11:17:32 web.1                          | Traceback (most recent call last):
11:17:32 web.1                          |   File "apps/frappe/frappe/app.py", line 69, in application
11:17:32 web.1                          |     response = frappe.api.handle()
11:17:32 web.1                          |   File "apps/frappe/frappe/api.py", line 55, in handle
11:17:32 web.1                          |     return frappe.handler.handle()
11:17:32 web.1                          |   File "apps/frappe/frappe/handler.py", line 38, in handle
11:17:32 web.1                          |     data = execute_cmd(cmd)
11:17:32 web.1                          |   File "apps/frappe/frappe/handler.py", line 76, in execute_cmd
11:17:32 web.1                          |     return frappe.call(method, **frappe.form_dict)
11:17:32 web.1                          |   File "apps/frappe/frappe/__init__.py", line 1454, in call
11:17:32 web.1                          |     return fn(*args, **newargs)
11:17:32 web.1                          |   File "apps/frappe/frappe/desk/form/save.py", line 25, in savedocs
11:17:32 web.1                          |     doc.save()
11:17:32 web.1                          |   File "apps/frappe/frappe/model/document.py", line 310, in save
11:17:32 web.1                          |     return self._save(*args, **kwargs)
11:17:32 web.1                          |   File "apps/frappe/frappe/model/document.py", line 364, in _save
11:17:32 web.1                          |     self.run_post_save_methods()
11:17:32 web.1                          |   File "apps/frappe/frappe/model/document.py", line 1085, in run_post_save_methods
11:17:32 web.1                          |     self.run_method("on_update")
11:17:32 web.1                          |   File "apps/frappe/frappe/model/document.py", line 941, in run_method
11:17:32 web.1                          |     out = Document.hook(fn)(self, *args, **kwargs)
11:17:32 web.1                          |   File "apps/frappe/frappe/model/document.py", line 1259, in composer
11:17:32 web.1                          |     return composed(self, method, *args, **kwargs)
11:17:32 web.1                          |   File "apps/frappe/frappe/model/document.py", line 1241, in runner
11:17:32 web.1                          |     add_to_return_value(self, fn(self, *args, **kwargs))
11:17:32 web.1                          |   File "apps/frappe/frappe/model/document.py", line 938, in fn
11:17:32 web.1                          |     return method_object(*args, **kwargs)
11:17:32 web.1                          |   File "apps/frappe/frappe/core/doctype/doctype/doctype.py", line 355, in on_update
11:17:32 web.1                          |     raise e
11:17:32 web.1                          |   File "apps/frappe/frappe/core/doctype/doctype/doctype.py", line 352, in on_update
11:17:32 web.1                          |     frappe.db.updatedb(self.name, Meta(self))
11:17:32 web.1                          |   File "apps/frappe/frappe/database/mariadb/database.py", line 338, in updatedb
11:17:32 web.1                          |     db_table.sync()
11:17:32 web.1                          |   File "apps/frappe/frappe/database/schema.py", line 43, in sync
11:17:32 web.1                          |     self.alter()
11:17:32 web.1                          |   File "apps/frappe/frappe/database/mariadb/schema.py", line 126, in alter
11:17:32 web.1                          |     raise e
11:17:32 web.1                          |   File "apps/frappe/frappe/database/mariadb/schema.py", line 110, in alter
11:17:32 web.1                          |     frappe.db.sql(query)
11:17:32 web.1                          |   File "apps/frappe/frappe/database/database.py", line 180, in sql
11:17:32 web.1                          |     self._cursor.execute(query)
11:17:32 web.1                          |   File "env/lib/python3.9/site-packages/pymysql/cursors.py", line 148, in execute
11:17:32 web.1                          |     result = self._query(query)
11:17:32 web.1                          |   File "env/lib/python3.9/site-packages/pymysql/cursors.py", line 310, in _query
11:17:32 web.1                          |     conn.query(q)
11:17:32 web.1                          |   File "env/lib/python3.9/site-packages/pymysql/connections.py", line 548, in query
11:17:32 web.1                          |     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
11:17:32 web.1                          |   File "env/lib/python3.9/site-packages/pymysql/connections.py", line 775, in _read_query_result
11:17:32 web.1                          |     result.read()
11:17:32 web.1                          |   File "env/lib/python3.9/site-packages/pymysql/connections.py", line 1156, in read
11:17:32 web.1                          |     first_packet = self.connection._read_packet()
11:17:32 web.1                          |   File "env/lib/python3.9/site-packages/pymysql/connections.py", line 725, in _read_packet
11:17:32 web.1                          |     packet.raise_for_error()
11:17:32 web.1                          |   File "env/lib/python3.9/site-packages/pymysql/protocol.py", line 221, in raise_for_error
11:17:32 web.1                          |     err.raise_mysql_exception(self._data)
11:17:32 web.1                          |   File "env/lib/python3.9/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
11:17:32 web.1                          |     raise errorclass(errno, errval)
11:17:32 web.1                          | pymysql.err.OperationalError: (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')

brian_pond · June 28, 2022, 4:44pm

MariaDB/MySQL has maximum sizes that cannot be exceeded:

You either need to remove columns/fields. Or edit the existing ones, and use smaller sizes.

navdeepghai · June 28, 2022, 5:28pm

I went through the documentation as you suggested and also explored some other solutions as below:

The issue isn’t with total number columns and it’s size but with changing fieldtype from “Check” to fieldtype “Data”

I tried it on Mariadb console and it’s throwing the same error.

mysql> ALTER table `tabHomeowners` MODIFY COLUMN swimming_pool_type VARCHAR(100);
ERROR 1118 (42000): 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
mysql>

I believe we’ve some existing records for Homeowners form due to which system isn’t letting me change the fieldtype, even though I tried to use patch to update the existing records before I change the fieldtype but still throwing the same error.

navdeepghai · June 28, 2022, 5:30pm

and yeah It does work when I am changing fieldtype from “Check” to “Int”.

brian_pond · June 28, 2022, 5:36pm

When you create a Check, the SQL column is an int(1). This only consumes 4 bytes of space.
However, when you change that to Data? Then the SQL column is a varchar(140). This is much larger SQL datatype. Consuming more of your total available space.
When you change from Check to Int, they are both integers. So there’s no difference in size.

250 columns is a lot for a single table.

You can try to reducing the size of some of your string columns. Or, if you change some of them to Text, then SQL treats it very differently, and it won’t count against your maximum row size.

navdeepghai · June 28, 2022, 5:58pm

I tried reducing the columns to 200 and still giving me the same error.

Nivedha · July 3, 2023, 11:23am

Any solution for this folks

Regards
Nivedha

Nivedha · July 8, 2023, 6:35am

Hello People,
Any solution for this guys

Regards
Nivedha

crazy_explorer · May 17, 2024, 7:24am

I faced the same issue, doctype recreation with less number of fields is the only option.

You can duplicate the doctype and delete the unwanted fields then save it.

Hope this helps!

Also check this Row size too large - update error - #6 by rmehta