While upgrading Nextcloud from 28 to 31 recently, a warning was given on the Administration page:
Incorrect row format found in your database. ROW_FORMAT=Dynamic offers the best database performances for Nextcloud. Please update row format on the following list: oc_talk_sessions. For more details see the documentation ↗.
The list of tables was much more extensive though: about 224 tables!
Reason # $too_many_to_count for PostgreSQL being superior to MySQL. And one more to top that off – PostgreSQL’s CLI supports readline properly for tab-completion and other goodies much better than MySQL’s CLI.
To fix the issue, I wrote this little script:
#!/usr/bin/env bash
## Generate list of tables in nextcloud DB
## Filter out table borders
## Remove tables already using ROW_FORMAT=Dynamic
## Parse out table names
## Remove column header "Name"
## Update ROW_FORMAT from COMPRESSED to DYNAMIC
table=nextcloud
for T in $(
mysqlshow --status ${table} \
| grep -v "\-\-" \
| grep -iv "Dynamic" \
| awk -F "|" '{print $2}' \
| grep -Ev "^ +Name +$"
) ; do
echo "Table to update ROW_FORMAT: ${T}" ;
## echo "mysql ${table} --execute 'ALTER TABLE ${T} ROW_FORMAT=Dynamic;'"
mysql ${table} --execute "ALTER TABLE ${T} ROW_FORMAT=Dynamic;"
done