Toggle Submenu
  1. Forums
  2. Recent
  3. Categories
  4. Tags
  5. Users
  6. Badges
Support is currently Offline
Support Offline
It's past support hour. We are taking some rest and will come back stronger tomorrow. Promise!
Official support hours:
Monday To Friday except Saturday and Sunday
From 09:30 To 17:00
Our current time: Friday 12:59 am
Add a reply View Replies (14)

Resolved Database row size question

  1. agape
    agape
  2. Support
  3. Monday, July 13 2020, 05:37 PM
Our understanding is that the MariaDB global max row size is 65536 (64K), regardless of the storage engine. We use innodb as our storage engine.

Our innodb_page_size is set to 16K, but InnoDB has a maximum row size that is roughly equivalent to half of this value.

Innodb_strict_mode is OFF, so we're open to warnings without getting halted.

In Joomla, we continued to add fields to EasyProfile until we got the following 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

So we backed up the jsn_users table right up to that point just short of 65535 (without the above error) and then dropped the table.

Using PHPMyAdmin, we did an import of the jsn_users table back into Joomla and got a warning about >8126 bytes (1/2 the page size), but the system continued to import because innodb_strict_mode is set to OFF. It doesn't stop on warning. Everything seems to be working and looks good.

We are assuming that EasyProfile checks for the MariaDB global max of 65535 and generates the error within Joomla. Is that correct? If EasyProfile doesn't do the check, it seems we would be at risk and very "unsafe", getting a truncated table without knowing it.

As long as we are less than 65535, are we safe (even though we get an innodb warning using PHPmyAdmin) and can we trust the EasyProfile error message about 65535?
  1. Profiles
  2. easy profile
  3. custom fields
  4. database
Responses (14)
admin
admin Accepted Answer
Admin
Easy profile does not do any check, when you create a field then Easy Profile will create a column in the table. if you go over the maximum row size then the DB server return an error, it does not truncates the table, simply it does not create the column.

About your error, can you please post here the exactly message?

To avoid this you can change the DB column type of all your text field type (Easy Profile field type) from VARCHAR to TEXT (DB column type). TEXT column type does not affect the row size and works like VARCHAR but the performance for search could be a little worse.
  1. more than a month ago
  2. Support
  3. # 1
agape
agape Accepted Answer
Thank you for your reply. The error we encounter is:

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.

Thanks again!
  1. more than a month ago
  2. Support
  3. # 2
agape
agape Accepted Answer
Please ignore the above error. I should have given this response:

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

Thank you
  1. more than a month ago
  2. Support
  3. # 3
admin
admin Accepted Answer
Admin
Hi,
this happen in phpmyadmin?
  1. more than a month ago
  2. Support
  3. # 4
agape
agape Accepted Answer
The error is generated within Joomla when we try to create an EasyProfile field.
  1. more than a month ago
  2. Support
  3. # 5
admin
admin Accepted Answer
Admin
To avoid this you can change the DB column type of all your text field type (Easy Profile field type) from VARCHAR to TEXT (DB column type). TEXT column type does not affect the row size and works like VARCHAR but the performance for search could be a little worse.
  1. more than a month ago
  2. Support
  3. # 6
agape
agape Accepted Answer
We changed the size of varchar(255) to varchar(64) which allowed us to complete the form. Thank you!
  1. more than a month ago
  2. Support
  3. # 7
dlh
dlh Accepted Answer
To avoid this you can change the DB column type of all your text field type (Easy Profile field type) from VARCHAR to TEXT (DB column type). TEXT column type does not affect the row size and works like VARCHAR but the performance for search could be a little worse.

Hi there,
I have exactly the same problem when I try to create a new field. However, in the DB, I don't have any "VARCHAR" field. In the type column, all the "classical fields" are already "text".
Thanks in advance for your help,
Yann
  1. more than a month ago
  2. Support
  3. # 8
dlh
dlh Accepted Answer
To avoid this you can change the DB column type of all your text field type (Easy Profile field type) from VARCHAR to TEXT (DB column type). TEXT column type does not affect the row size and works like VARCHAR but the performance for search could be a little worse.

Hi there,
I have exactly the same problem when I try to create a new field. However, in the DB, I don't have any "VARCHAR" field. In the type column, all the "classical fields" are already "text".
Thanks in advance for your help,
Yann


Hey,
Any update please?
  1. more than a month ago
  2. Support
  3. # 9
admin
admin Accepted Answer
Admin
Hi,
which not "classical fields" do you have?
  1. more than a month ago
  2. Support
  3. # 10
dlh
dlh Accepted Answer
Hi,
You will find columns and rows in the attached files.
  1. more than a month ago
  2. Support
  3. # 11
admin
admin Accepted Answer
Admin
Hi,
this is the wrong table (#__jsn_fields), the correct table is #__jsn_users :)
  1. more than a month ago
  2. Support
  3. # 12
dlh
dlh Accepted Answer
Hi,
As it was regarding the field types, I thought it was in this table.
But here attached, you will find the users table. Should I change these "VARCHAR" types into "TEXT"?
Thanks
  1. more than a month ago
  2. Support
  3. # 13
admin
admin Accepted Answer
Admin
Hi,
yes, you can change some columns from VARCHAR to TEXT, only one thing: make this modification only on custom fields and leave core fields (firstname, avatar,…..) as VARCHAR
  1. more than a month ago
  2. Support
  3. # 14
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.