No announcement yet.

Syntax error or access violation: 1118 Row size too large Limit 65535

  • Filter
  • Time
  • Show
Clear All
new posts

  • Syntax error or access violation: 1118 Row size too large Limit 65535

    Can I change the column to use text type instead of varchar for long strings on the backend? Replace all varchar(255) with text.

    Or, even better, use appropriate data types instead of the "too large" ones. You don't really need 255 characters to store currency, do we?

    The problem is that when the rebuild occurs it changes certain columns back to default maximum length.
    When making new fields in espo and even if i set a limit it still uses the maximum size for the column.
    Even if I change the column length on the back manually and rebuild it takes a column that I only need a length max of 10 and changes it to 255.
    This is severly limiting how many fields I can add.

    This has been brought up in the developer section but nobody seems to reply.

    I have 180 fields in Entity "Accounts" so 65535/180 = an average of 364 characters per field.... that is insane especially for certain fields like US STATE(2), PHONE(12), CURRENCY(20 - MAX with decimals?), DATETIME(25 - MAX?) - Between the 4 fields with my num counts it should be less than (59) in length not (255)plus * 4 fields of (1000+) characters.

    1000 - 59 = 941 spaces saved/not utilized.
    Last edited by khopper; 06-01-2019, 03:46 PM.

  • #2
    yuri Thoughts?
    I even sent an email to PAID support regarding this.


    • #3
      Hello Khopper.

      We don't recommend you to change the field type from VARCHAR to TEXT. It may affect the searching of data.
      Indeed you are right that for some data you don't need the length that equals to 255. You can set it according to your purposes.
      Here is the one thing that you should know and I hope it will shed light on this situation.
      If you will create the new VARCHAR field and set for it the max_lenght = 25, it will create a record in the DB with the max_lenght = 25.
      If after this you will change the max_lenght of this field to default, it will change the max_lenght parameter in the DB to the default one which equals to 255.
      If after you will decrease the max_lenght parameter in the Entity Manager to 50, it will change nothing.
      Why it is happening? Cause the max_length parameter that is recording to the DB, can be changed automatically only to the bigger value. That's why the max_value parameter will not be changed from 255 to 50.
      You can solve this manually, by changing in your DB this parameter to 50.


      • #4
        Not all fields have the option in the GUI to limit the field length.
        For Example, I have an ENUM field that shows as a VARCHAR on the DB back end.
        I need a max length of 3 for this field.
        I Changed it on the DB backend and rebuilt the DB from the Administration menu.
        The act of Rebuilding modified the varvhar filed from 3 to 255.


        • #5
          You have to manually set the maxLength parameter for this field in the EspoCRM/custom/Espo/Custom/Resources/metadata/entityDefs/YOUR_ENTITY.json like this:
          "maxLength": 3,
          After, change it to 3 in your DB.


          • khopper
            khopper commented
            Editing a comment
            Maximus It would be a nice enhancement to see this option in the GUI/web interface for these types of fields.