Upgrade MariaDB table row formats to DYNAMIC

I noticed a while back from warnings in my MariaDB error logs:

Version: '10.4.10-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
2019-11-10  8:37:11 42 [Warning] InnoDB: Cannot add field `custom_var_k4` in table `hidayahtech_matomo`.`matomo_log_visit` because after adding it, the row size is 8682 which is greater than maximum allowed size (8126) for a record on index leaf page.
2019-11-10  8:37:11 42 [Warning] InnoDB: Cannot add field `custom_var_v4` in table `hidayahtech_matomo`.`matomo_log_conversion` because after adding it, the row size is 8391 which is greater than maximum allowed size (8126) for a record on index leaf page.

A quick search through the tubes lead me to this page describing the nature of the problem:

https://mariadb.com/kb/en/library/troubleshooting-row-size-too-large-errors-with-innodb/

Quoting MariaDB’s documentation:

The root cause is that InnoDB has a maximum row size that is roughly equivalent to half of the value of the innodb_page_size system variable. See InnoDB Row Formats Overview: Maximum Row Size for more information.

https://mariadb.com/kb/en/library/troubleshooting-row-size-too-large-errors-with-innodb/#root-cause-of-the-problem

But how do we solve it? A quick glance further down the page:

If the table is using either the REDUNDANT or the COMPACT row format, then one potential solution to this problem is to convert the table to use the DYNAMIC row format instead.

https://mariadb.com/kb/en/library/troubleshooting-row-size-too-large-errors-with-innodb/#converting-the-table-to-the-dynamic-row-format

Okay…so how do I know which tables are affected?

You can use the INNODB_SYS_TABLES table in the information_schema database to find all tables that use the REDUNDANT or the COMPACT row formats. This is helpful if you would like to convert all of your tables that you still use the older row formats to the DYNAMIC row format. For example, the following query can find those tables, while excluding InnoDB’s internal system tables:

https://mariadb.com/kb/en/library/troubleshooting-row-size-too-large-errors-with-innodb/#converting-the-table-to-the-dynamic-row-format

SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE ROW_FORMAT IN('Redundant', 'Compact')
AND NAME NOT IN('SYS_DATAFILES', 'SYS_FOREIGN', 'SYS_FOREIGN_COLS', 'SYS_TABLESPACES', 'SYS_VIRTUAL', 'SYS_ZIP_DICT', 'SYS_ZIP_DICT_COLS');

However, this quote returns table names in a format that are not readily accessible to use in an ALTER TABLE statement.

So, I used the following, which reads from the information_schema.TABLES table instead:

SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.TABLES WHERE ENGINE = 'InnoDB' AND ROW_FORMAT IN('Redundant', 'Compact') AND TABLE_NAME NOT IN('SYS_DATAFILES', 'SYS_FOREIGN', 'SYS_FOREIGN_COLS', 'SYS_TABLESPACES', 'SYS_VIRTUAL', 'SYS_ZIP_DICT', 'SYS_ZIP_DICT_COLS');

And then I fed that into a Bash script to print out just a list of the database.table names that I could easily copy-and-paste into a MariaDB shell:

for
  table in `mariadb --batch --skip-column-names --user=root -p --execute="SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.TABLES WHERE ENGINE = 'InnoDB' AND ROW_FORMAT IN('Redundant', 'Compact') AND TABLE_NAME NOT IN('SYS_DATAFILES', 'SYS_FOREIGN', 'SYS_FOREIGN_COLS', 'SYS_TABLESPACES', 'SYS_VIRTUAL', 'SYS_ZIP_DICT', 'SYS_ZIP_DICT_COLS');"`;
  do echo "ALTER TABLE ${table} ROW_FORMAT=DYNAMIC;";
done;

And that outputs something like this:

[root@burtuqaal ~]# time for table in `mariadb --batch --skip-column-names --user=root -p --execute="SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.TABLES WHERE ENGINE = 'InnoDB' AND ROW_FORMAT IN('Redundant', 'Compact') AND TABLE_NAME NOT IN('SYS_DATAFILES', 'SYS_FOREIGN', 'SYS_FOREIGN_COLS', 'SYS_TABLESPACES', 'SYS_VIRTUAL', 'SYS_ZIP_DICT', 'SYS_ZIP_DICT_COLS');"`; do echo "ALTER TABLE ${table} ROW_FORMAT=DYNAMIC;"; done;
Enter password: 
ALTER TABLE hidayahtech_invoiceninja.lookup_invitations ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_invoiceninja.lookup_proposal_invitations ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_invoiceninja.proposal_invitations ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_invoiceninja.scheduled_reports ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_invoiceninja.account_gateway_tokens ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_invoiceninja.lookup_users ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_invoiceninja.proposal_templates ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_invoiceninja.user_accounts ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_invoiceninja.account_gateway_settings ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_invoiceninja.frequencies ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_invoiceninja.lookup_accounts ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_invoiceninja.lookup_contacts ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_invoiceninja.recurring_expenses ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_user ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_twofactor_recovery_code ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_access ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_site ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_plugin_setting ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_site_setting ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_site_url ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_goal ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_logger_message ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_log_action ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_log_conversion_item ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_log_profiling ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_option ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_session ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_sequence ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_brute_force_log ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_tracking_failure ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_segment ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_log_conversion ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_user_language ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_user_dashboard ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_report ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_report_subscriptions ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_privacy_logdata_anonymizations ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_log_link_visit_action ROW_FORMAT=DYNAMIC;
ALTER TABLE hidayahtech_matomo.matomo_log_visit ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_users ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_usermeta ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_termmeta ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_terms ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_term_taxonomy ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_term_relationships ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_commentmeta ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_comments ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_links ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_options ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_postmeta ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_posts ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_yoast_seo_links ROW_FORMAT=DYNAMIC;
ALTER TABLE letslearnandrise_wordpress.wp_yoast_seo_meta ROW_FORMAT=DYNAMIC;

real    0m5.308s
user    0m0.015s
sys     0m0.011s

(NOTE: I prefix almost every shell command I run with time simply because I like to. Nothing more to that).

Now, in another terminal, I can copy-and-paste these lines into a mariadb client connection, and this is what you get:

[root@burtuqaal ~]# mariadb --user=root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 607
Server version: 10.4.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_users ROW_FORMAT=DYNAMIC;
Query OK, 2 rows affected (0.759 sec)              
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_usermeta ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.514 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_termmeta ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.498 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_terms ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.532 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_term_taxonomy ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.523 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_term_relationships ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.473 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_commentmeta ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.515 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_comments ROW_FORMAT=DYNAMIC;
Query OK, 1 row affected (0.565 sec)               
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_links ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.499 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_options ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (1.517 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_postmeta ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (1.682 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_posts ROW_FORMAT=DYNAMIC;
Query OK, 262 rows affected (0.748 sec)            
Records: 262  Duplicates: 0  Warnings: 0

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_yoast_seo_links ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.465 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [(none)]> ALTER TABLE letslearnandrise_wordpress.wp_yoast_seo_meta ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.430 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [(none)]>

Neat! Use with caution, but I can say, at least for the few minutes after running these commands, I didn’t have any problems. 😀