Skip to main content
Hello #MariaDB people, my #Friendica server is down since I installed the latest MariaDB update and it also switched from #PHP 7 to 8.

All I get in the php error log is this:
4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.

Any ideas are welcome! I need your help!

!Friendica Support #help
#friendica #help #php #MariaDB !Friendica Support
hoergen (Ai)
hoergen (Ai)  
•
@utzer (on squeet.me) giving the error message into a search enging brought me to this github issue https://github.com/nextcloud/docker/issues/1536 with some solutions.
@utzer (on squeet.me)
utzer (on squeet.me)
utzer (on squeet.me)  
•
@hoergen ok, which brought me here:
https://www.linuxquestions.org/questions/slackware-14/heads-up-mariadb-10-6-breaking-change-4175694396/

I think I will set this, but generally I wonder if this is a Friendica thing or how I can "convert" my database to not do compression.

@Michael Vogel I think, as you told you use compression, you want to check it and I hope I can follow your solution after. I am really no good in database management/setting.
@Michael Vogel @hoergen (Ai)
Michael Vogel
Michael Vogel  
•
But beware: The table worker-ipc must not be converted.
utzer (on squeet.me)
utzer (on squeet.me)  
•
@Michael Vogel
Michael Vogel
Michael Vogel  
•
Please use DYNAMIC for all tables except worker-ipc which is MEMORY.
utzer (on squeet.me)
utzer (on squeet.me)  
•
@Michael Vogel ok, got that.

Do you happen to know how I can run this query so it lists all tables with any format? 
SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE ROW_FORMAT IN('Redundant', 'Compressed', 'Compact', 'Dynamic', 'Memory')
AND NAME NOT IN('SYS_DATAFILES', 'SYS_FOREIGN', 'SYS_FOREIGN_COLS', 'SYS_TABLESPACES', 'SYS_VIRTUAL', 'SYS_ZIP_DICT', 'SYS_ZIP_DICT_COLS');
@Michael Vogel
utzer (on squeet.me)
utzer (on squeet.me)  
•
@Michael Vogel

script: 
#!/bin/sh
for tbl in tbl1 tbl2 ; do
    mysql --database=ylms_friendica --execute="ALTER TABLE ${tbl} ROW_FORMAT=DYNAMIC;"
done
exit 0


maybe someone can use this. Get a list of tables to convert with this query in mysql:
SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE ROW_FORMAT IN('Compressed')
AND NAME NOT IN('SYS_DATAFILES', 'SYS_FOREIGN', 'SYS_FOREIGN_COLS', 'SYS_TABLESPACES', 'SYS_VIRTUAL', 'SYS_ZIP_DICT', 'SYS_ZIP_DICT_COLS');


this lists all compressed tables, take all items from the list that do not have an @ in the name, add all those table names in the place where tbl1 tbl2 is above.
@Michael Vogel
utzer (on squeet.me)
utzer (on squeet.me)  
•
Remark, for some reason the script failed for the table called "group", so I ran if in mysql for group and had to enclose the name in back ticks like this `group`.
utzer (on squeet.me)
utzer (on squeet.me)  
•
@Tealk seit dem letzten Release glaube ich, aber es ist nicht so super getestet, aber Debian hat jetzt ja auch PHP 8 als Default, oder?
@Tealk
Tealk
Tealk  
•
@utzer (on squeet.me) anscheinend noch 7.4 https://packages.debian.org/bullseye/php

dann versuch ich auch mal auf php8 umzustellen, mal sehen was dann noch geht :P
@utzer (on squeet.me)
Tealk
Tealk  
•
also ich musste auf jeden falls den fastcgi_buffers anpassen:
fastcgi_buffers 16 16k; 
fastcgi_buffer_size 32k;


sonst bekam ich nur den Fehler:
2021/08/06 13:42:08 [error] 48172#48172: *42920 upstream sent too big header while reading response header from upstream, client: 212.211.191.216, server: rollenspiel.group, request: "GET /admin/ HTTP/2.0", upstream: "fastcgi://unix:/var/lib/fastcgi/6-anzah-rollenspiel.group-php-fpm.socket:", host: "rollenspiel.group", referrer: "https://rollenspiel.group/network"
⇧