address_update_9201() can fail if row format is not set to dynamic

Created on 11 April 2024, 7 months ago
Updated 10 July 2024, 4 months ago

Problem/Motivation

This is more for documentation that a real request. We did run into troubles with address_update_9201():


W: > [error] SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs: ALTER TABLE "node__FIELD_NAME" ADD "FIELD_NAME_address_line3" VARCHAR(255) DEFAULT NULL; Array

This was on some old installations that were created with row format compact, so those tables had to be converted to dynamic. See https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/#converting-the-table-to-the-dynamic-row-format

Steps to reproduce

Proposed resolution

Remaining tasks

User interface changes

API changes

Data model changes

πŸ’¬ Support request
Status

Active

Version

2.0

Component

Code

Created by

πŸ‡¨πŸ‡­Switzerland berdir Switzerland

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

  • Issue created by @berdir
  • πŸ‡ΊπŸ‡¦Ukraine andriic

    We had the same issue.
    Our database version was updated from mariadb:10.0 to mariadb:10.3.
    row_format of tables was changed:

    ALTER TABLE commerce_store_field_data ROW_FORMAT=DYNAMIC;
    ALTER TABLE profile__address ROW_FORMAT=DYNAMIC;
    ALTER TABLE profile_revision__address ROW_FORMAT=DYNAMIC;
    

    And columns were dropped before running update.

    ALTER TABLE commerce_store_field_data DROP COLUMN address__address_line3;
    ALTER TABLE profile__address DROP COLUMN address_address_line3;
    ALTER TABLE profile_revision__address DROP COLUMN address_address_line3;
  • πŸ‡ΊπŸ‡ΈUnited States rjustin

    +1 to the resolution by @andriic.

    I will add that there was a table in the db I was working on that required more work. For this table, I had to manually resize some columns from the default `varchar(255)`.

    I used this command to return a integer for each row found in a column that exceeded a specific limit:
    `SELECT COUNT(*) AS count_exceeding_XX FROM your_table WHERE LENGTH(your_column) > 50;`
    Note: `count_exceeding_XX` is an arbitrary value. It can be named anything.

    Then once I noted the lowest reasonable size I could set a column, I set it to the desired value by:
    `ALTER TABLE your_table MODIFY COLUMN your_column VARCHAR(50);`

Production build 0.71.5 2024