Update Above 3.22.2 Fails

ceylon23 · July 17, 2020, 3:47pm

I am not able to update ghost past 3.22.2 (current version on my server). I skip 3.23 as there is a notification that there is an issue with upgrading from earlier versions, but with both 3.24.0 and 3.25.0 I get the following error in the ghost-cli debug log each time I try to upgrade:

Debug Information:
OS: Debian GNU/Linux, v10
Node Version: v10.14.2
Ghost Version: 3.25.0
Ghost-CLI Version: 1.14.1
Environment: production
Command: ‘ghost update’
Message: alter table tags add twitter_image varchar(2000) null - ER_TOO_BIG_ROWSIZE: Row size too large. The maximum row size for the used ta$
Help: Error occurred while executing the following migration: 04-add-meta-columns-to-tags-table.js
Suggestion: journalctl -u ghost_blog-leavesoftea-net -n 50
Stack: Error: alter table tags add twitter_image varchar(2000) null - ER_TOO_BIG_ROWSIZE: Row size too large. The maximum row size for the us$
at Server.server.close (/usr/local/lib/node_modules/ghost-cli/lib/utils/port-polling.js:38:28)
at Object.onceWrapper (events.js:273:13)
at Server.emit (events.js:182:13)
at emitCloseNT (net.js:1629:8)
at process._tickCallback (internal/process/next_tick.js:63:19)

OS: Debian 10.4
Database Server: MariaDB v10.3.22-0+deb10u1
Web Server: NGINX v1.18.0
Install date of this Ghost instance: Manual Install - October 2013

Thank you in advance

egg · July 17, 2020, 5:08pm

Hey!

Would you be able to provide us with a little more information to help us, help you?

I noticed in the logs you’ve posted there’s a string like "The maximum row size for the used ta$" - did something get cut off when you were pasting that in? If so, the rest of it would be really useful!

If you’re able to connect to your database, the output of running describe tags; would also be really useful (it doesn’t contain any personal information, just the columns and data types in the table).

Lastly are you running a 32bit operating system? If you’re not sure would you be able to share the output of uname -m?

rmontagud · July 17, 2020, 6:00pm

Hi, I’m having the same issue with a similar system setup (Debian 10, etc etc…)

Message: alter table tags add twitter_image varchar(2000) null - ER_TOO_BIG_ROWSIZE: 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
Help: Error occurred while executing the following migration: 04-add-meta-columns-to-tags-table.js
Suggestion: journalctl -u ghost instance -n 50

Hannah · July 17, 2020, 6:37pm

Can you please share the output from

SHOW TABLE STATUS WHERE Name = 'tags';?

rmontagud · July 17, 2020, 6:43pm

Sure,

    MariaDB [ghost_prod]> SHOW TABLE STATUS WHERE Name = 'tags' \G
*************************** 1. row ***************************
            Name: tags
        Engine: InnoDB
        Version: 10
    Row_format: Compact
            Rows: 8
Avg_row_length: 2048
    Data_length: 16384
Max_data_length: 0
    Index_length: 16384
    Data_free: 0
Auto_increment: NULL
    Create_time: 2020-07-17 19:33:35
    Update_time: NULL
    Check_time: NULL
    Collation: utf8mb4_general_ci
        Checksum: NULL
Create_options:
        Comment:
Max_index_length: 0
    Temporary: N
1 row in set (0.000 sec)

Hannah · July 17, 2020, 6:50pm

Honestly I’m fascinated by this - the max row size in modern versions of MySQL is 65,535K, not 8126K, I’m not sure if you’re getting 8K errors because you’re on a too-old DB, a buggy version or a misconfigured DB - Google suggests it could be any of them.

The post table is twice as big as the update tags table (posts = 32K, updated tags = 16K) - so I don’t understand how you’ve managed to get this far with posts if your DB doesn’t support more than 8K per row.

What do you get with SHOW TABLE STATUS WHERE Name = 'posts'?

rmontagud · July 17, 2020, 6:58pm

Looks similar to me

MariaDB [ghost_prod]> SHOW TABLE STATUS WHERE Name = 'posts' \G
*************************** 1. row ***************************
            Name: posts
        Engine: InnoDB
        Version: 10
    Row_format: Compact
            Rows: 8
Avg_row_length: 8192
    Data_length: 65536
Max_data_length: 0
    Index_length: 16384
    Data_free: 0
Auto_increment: NULL
    Create_time: 2019-11-28 00:47:58
    Update_time: NULL
    Check_time: NULL
    Collation: utf8mb4_general_ci
        Checksum: NULL
Create_options:
        Comment:
Max_index_length: 0
    Temporary: N
1 row in set (0.000 sec)

Edit: I’m going ahead and list the row formats

MariaDB [ghost_prod]> SELECT `table_name`, `row_format`
    -> FROM `information_schema`.`tables`
    -> WHERE `table_schema`=DATABASE();
+----------------------------------------+------------+
| table_name                             | row_format |
+----------------------------------------+------------+
| permissions                            | Compact    |
| migrations                             | Compact    |
| app_settings                           | Compact    |
| integrations                           | Compact    |
| posts_tags                             | Compact    |
| apps                                   | Compact    |
| tags                                   | Compact    |
| mobiledoc_revisions                    | Compact    |
| invites                                | Compact    |
| settings                               | Compact    |
| roles_users                            | Compact    |
| users                                  | Compact    |
| members_labels                         | Dynamic    |
| permissions_roles                      | Compact    |
| permissions_users                      | Compact    |
| brute                                  | Compact    |
| api_keys                               | Compact    |
| posts_meta                             | Compact    |
| app_fields                             | Compact    |
| labels                                 | Dynamic    |
| emails                                 | Compact    |
| actions                                | Compact    |
| migrations_lock                        | Compact    |
| roles                                  | Compact    |
| posts_authors                          | Compact    |
| permissions_apps                       | Compact    |
| members_stripe_customers_subscriptions | Compact    |
| members_stripe_customers               | Compact    |
| members                                | Compact    |
| webhooks                               | Compact    |
| sessions                               | Compact    |
| posts                                  | Compact    |
+----------------------------------------+------------+
32 rows in set (0.015 sec)

ceylon23 · July 17, 2020, 7:10pm

My output is similar to @rmontagud

MariaDB [ghost_production]> SHOW TABLE STATUS WHERE Name = 'tags' \G
*************************** 1. row ***************************
            Name: tags
          Engine: InnoDB
         Version: 10
      Row_format: Compact
            Rows: 31
  Avg_row_length: 528
     Data_length: 16384
 Max_data_length: 0
    Index_length: 16384
       Data_free: 0
  Auto_increment: NULL
     Create_time: 2020-07-16 16:11:54
     Update_time: NULL
      Check_time: NULL
       Collation: utf8mb4_general_ci
        Checksum: NULL
  Create_options:
         Comment:
Max_index_length: 0
       Temporary: N
1 row in set (0.001 sec)

MariaDB [ghost_production]> SHOW TABLE STATUS WHERE Name = 'posts' \G
*************************** 1. row ***************************
            Name: posts
          Engine: InnoDB
         Version: 10
      Row_format: Compact
            Rows: 26
  Avg_row_length: 4411
     Data_length: 114688
 Max_data_length: 0
    Index_length: 16384
       Data_free: 0
  Auto_increment: NULL
     Create_time: 2020-01-06 09:13:09
     Update_time: NULL
      Check_time: NULL
       Collation: utf8mb4_general_ci
        Checksum: NULL
  Create_options:
         Comment:
Max_index_length: 0
       Temporary: N
1 row in set (0.001 sec)

rmontagud · July 17, 2020, 7:24pm

Ok, it seems I solved it at least in my case.

I dist-upgraded from Debian 9, so previous MariaDB installation was 10.1 (and the default row format “Compact”) so after upgrading to Debian 10 there was no default file format set as it should be Barracuda and the row format to dynamic

So, to avoid headaches in future installations set/make sure that the default row_format to dynamic and the file format to Barracuda. In already.existing installations you may have to change the table row format

ceylon23 · July 17, 2020, 8:31pm

Great catch on this one @rmontagud! I, like you, also dist-upgraded and so once I set barracuda and dynamic then altered the tables, the upgrade to 3.25.0 was able to succeed.

Thanks much!

Steps I used to fix this problem

In your my.cnf set the following under [mysqld]:

    innodb_default_row_format = dynamic
    innodb_file_format = barracuda

Then restart your database server

Then alter your tables with:
    ALTER TABLE `[table_name]` ROW_FORMAT=DYNAMIC

jeff · July 30, 2020, 8:40am

I had the same issue - kudos to @ceylon23 and @rmontagud for the solution!
I have many blogs hosted on various Debian servers - and make the following tweaks to @ceylon23’s fix:

Don’t change my.cnf - the Debian way is to add a new .cnf file in /etc/mysql/conf.d - e.g. /etc/mysql/conf.d/custom.cnf so that upgrades will not overwrite your change.
Don’t set innodb_file_format = barracuda' - that option is being deprecated - see the MySQL docs.
DO set innodb_default_row_format = dynamic
Instead of ALTER TABLE you can use the command-line to fix all tables in the database: e.g. one of:

sudo mysqlcheck -o my_ghost_database
sudo mysqlcheck -o --all-databases

Thanks for the fix guys!

jeff · July 30, 2020, 8:41am

Incidentally you can check if it worked using show table status SQL command.

Topic		Replies	Views
"ER_TOO_BIG_ROWSIZE" after updating to 4.44-alpine Bugs	0	744	April 20, 2022
Unable to update Ghost from 5.2.3 to 5.4.0 Self-hosting	14	1943	November 12, 2023
Unable to upgrade Ghost from v4.2.0 to v4.3.0 (CASCADE - UNKNOWN_CODE_PLEASE_REPORT) Developer help	68	7391	October 13, 2022
Update from 4.9.3 to 4.9.4 went wrong Developer help	3	1143	September 29, 2021
Can't Upgrade from v2.27 to v3.8 ER_NO_SUCH_TABLE error Installation	6	1146	March 3, 2020

Update Above 3.22.2 Fails

Steps I used to fix this problem

Related topics