utzer (on squeet.me)

3 years ago •

utzer (on squeet.me)
3 years ago •

Hello !Friendica Support,
I tried to switch to the current develop, but it seems this did not work.

There was no output from bin/console dbstructure update, but when I run bin/console postupdate, I get:

Überprüfe ausstehende Update-Aktionen
ALTER IGNORE TABLE `contact` DROP INDEX `dfrn-id`, DROP INDEX `issued-id`, MODIFY `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `site-pubkey` text COMMENT 'Deprecated', MODIFY `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', MODIFY `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url', MODIFY `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', ADD INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRI

Überprüfe ausstehende Update-Aktionen
ALTER IGNORE TABLE `contact` DROP INDEX `dfrn-id`, DROP INDEX `issued-id`, MODIFY `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `site-pubkey` text COMMENT 'Deprecated', MODIFY `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', MODIFY `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url', MODIFY `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', ADD INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 4047 beim Update der Datenbank aufgetreten
InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
ALTER IGNORE TABLE `apcontact` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url', ADD UNIQUE INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 4047 beim Update der Datenbank aufgetreten
InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
ALTER IGNORE TABLE `delayed-post` ADD `wid` int unsigned COMMENT 'Workerqueue id', ADD INDEX `wid` (`wid`), ADD FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 4047 beim Update der Datenbank aufgetreten
InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
ALTER IGNORE TABLE `event` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the event uri', ADD INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 4047 beim Update der Datenbank aufgetreten
InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
ALTER IGNORE TABLE `fcontact` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the fcontact url', ADD UNIQUE INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 4047 beim Update der Datenbank aufgetreten
InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
Erledigt.
Ausstehende Post-Updates ausführen
.........................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................

and now running dots for a few minutes.

but bin/console dbstructure dryrun gives me:

ALTER IGNORE TABLE `contact` DROP INDEX `dfrn-id`, DROP INDEX `issued-id`, MODIFY `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `site-pubkey` text COMMENT 'Deprecated', MODIFY `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', MODIFY `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url', MODIFY `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', ADD INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 
ALTER IGNORE TABLE `apcontact` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url', ADD UNIQUE INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 
ALTER IGNORE TABLE `delayed-post` ADD `wid` int unsigned COMMENT 'Workerqueue id', ADD INDEX `wid` (`wid`), ADD FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 
ALTER IGNORE TABLE `event` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the event uri', ADD INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 
ALTER IGNORE TABLE `fcontact` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the fcontact url', ADD UNIQUE INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE;

So I am sure something is wrong...

!Friendica Support

utzer (on squeet.me)

3 years ago

Tried bin/console dbstructure update again, which now shows:

ALTER IGNORE TABLE `contact` DROP INDEX `dfrn-id`, DROP INDEX `issued-id`, MODIFY `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `site-pubkey` text COMMENT 'Deprecated', MODIFY `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', MODIFY `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url', MODIFY `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', ADD INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 4047 beim Update der Datenbank aufgetreten
InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
ALTER IGNORE TABLE `apcontact` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url', ADD UNIQUE INDEX `uri-id` (`uri-

Tried bin/console dbstructure update again, which now shows:

ALTER IGNORE TABLE `contact` DROP INDEX `dfrn-id`, DROP INDEX `issued-id`, MODIFY `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `site-pubkey` text COMMENT 'Deprecated', MODIFY `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', MODIFY `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url', MODIFY `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', ADD INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 4047 beim Update der Datenbank aufgetreten
InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
ALTER IGNORE TABLE `apcontact` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url', ADD UNIQUE INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 4047 beim Update der Datenbank aufgetreten
InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
ALTER IGNORE TABLE `delayed-post` ADD `wid` int unsigned COMMENT 'Workerqueue id', ADD INDEX `wid` (`wid`), ADD FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 4047 beim Update der Datenbank aufgetreten
InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
ALTER IGNORE TABLE `event` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the event uri', ADD INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 4047 beim Update der Datenbank aufgetreten
InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
ALTER IGNORE TABLE `fcontact` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the fcontact url', ADD UNIQUE INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 4047 beim Update der Datenbank aufgetreten
InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
Fehler beim Ändern der Datenbank aufgetretenALTER IGNORE TABLE `contact` DROP INDEX `dfrn-id`, DROP INDEX `issued-id`, MODIFY `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `site-pubkey` text COMMENT 'Deprecated', MODIFY `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', MODIFY `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url', MODIFY `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', ADD INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; <br />
Fehler beim Ändern der Datenbank aufgetretenALTER IGNORE TABLE `apcontact` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url', ADD UNIQUE INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; <br />
Fehler beim Ändern der Datenbank aufgetretenALTER IGNORE TABLE `delayed-post` ADD `wid` int unsigned COMMENT 'Workerqueue id', ADD INDEX `wid` (`wid`), ADD FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; <br />
Fehler beim Ändern der Datenbank aufgetretenALTER IGNORE TABLE `event` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the event uri', ADD INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; <br />
Fehler beim Ändern der Datenbank aufgetretenALTER IGNORE TABLE `fcontact` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the fcontact url', ADD UNIQUE INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; <br />

Thanks for any help.

cc @Admin (of friendica.utzer.de)@utzer

@utzer [Friendica] @Admin (of friendica.utzer.de)

Michael Vogel

3 years ago •

The important message is InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.

This is something with your system or the database. A quick search returned this here:
https://dba.stackexchange.com/questions/256427/unable-to-create-tables-with-row-format-compressed

utzer (on squeet.me)

3 years ago •

@Michael Vogel yes, been there too, no clue what to do.

not using compressed, is that possible?

@Michael Vogel

Michael Vogel

3 years ago •

utzer (on squeet.me)

3 years ago

@Michael Vogel ok... did a downgrade of mariadb, now this:

bin/console dbstructure update give nothing

in/console dbstructure dryrun gives:

ALTER IGNORE TABLE `contact` DROP INDEX `dfrn-id`, DROP INDEX `issued-id`, MODIFY `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `site-pubkey` text COMMENT 'Deprecated', MODIFY `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', MODIFY `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url', MODIFY `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', ADD INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 
ALTER IGNORE TABLE `apcontact` ADD `uri-id` int unsigned COMMENT 'Id of the item-ur

@Michael Vogel ok... did a downgrade of mariadb, now this:

bin/console dbstructure update give nothing

in/console dbstructure dryrun gives:

ALTER IGNORE TABLE `contact` DROP INDEX `dfrn-id`, DROP INDEX `issued-id`, MODIFY `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `site-pubkey` text COMMENT 'Deprecated', MODIFY `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', MODIFY `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url', MODIFY `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', MODIFY `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', ADD INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 
ALTER IGNORE TABLE `apcontact` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url', ADD UNIQUE INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 
ALTER IGNORE TABLE `delayed-post` ADD `wid` int unsigned COMMENT 'Workerqueue id', ADD INDEX `wid` (`wid`), ADD FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 
ALTER IGNORE TABLE `event` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the event uri', ADD INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 
ALTER IGNORE TABLE `fcontact` ADD `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the fcontact url', ADD UNIQUE INDEX `uri-id` (`uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE;

and bin/console postupdate gives:

Überprüfe ausstehende Update-Aktionen
Erledigt.
Ausstehende Post-Updates ausführen
[Error] SQLSTATE[42S22]: Column not found: 1054 Unknown column 'uri-id' in 'where clause'

@Michael Vogel

utzer (on squeet.me)

3 years ago

@Michael Vogel oh and Friendica gives me a WSoD, but that might be some other reason, due to this problem I was now also forced to upgrade PHP to php8. 😢

@Michael Vogel

Michael Vogel

3 years ago •

never ever execute the postupdate command when the update hasn't finished.

Please execute the SQL commands from the "dryrun" line by line in the MySQL console and report the errors.

utzer (on squeet.me) likes this.

utzer (on squeet.me)

3 years ago •

@Michael Vogel thanks, sorry.

@Michael Vogel

utzer (on squeet.me)

3 years ago •

@Michael Vogel all I get is:

ERROR 4047 (HY000): InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.

@Michael Vogel

utzer (on squeet.me)

3 years ago

@Michael Vogel I am considering to restore a backup from a few hours ago.

@Michael Vogel

Michael Vogel

3 years ago •

Weird. Well, you could try changing the row format to dynamic: https://mariadb.com/kb/en/innodb-row-formats-overview/#dynamic-row-format

Hans Wolters likes this.

utzer (on squeet.me)

3 years ago •

@Michael Vogel I think they all are.

According to this: https://blog.basilgohar.com/2019/11/10/upgrade-mariadb-table-row-formats-to-dynamic/

I did this:

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');

and got no output, when I replace Compact with Dynamic in that query I get some list:

+--------------------------------------------------------------+------------+
| NAME                                                         | ROW_FORMAT |
+--------------------------------------------------------------+------------+
| mysql/gtid_slave_pos

@Michael Vogel I think they all are.

According to this: https://blog.basilgohar.com/2019/11/10/upgrade-mariadb-table-row-formats-to-dynamic/

I did this:

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');

and got no output, when I replace Compact with Dynamic in that query I get some list:

+--------------------------------------------------------------+------------+
| NAME                                                         | ROW_FORMAT |
+--------------------------------------------------------------+------------+
| mysql/gtid_slave_pos                                         | Dynamic    |
| mysql/innodb_index_stats                                     | Dynamic    |
| mysql/innodb_table_stats                                     | Dynamic    |
| mysql/transaction_registry                                   | Dynamic    |
| phpmyadmin/pma__bookmark                                     | Dynamic    |
| phpmyadmin/pma__central_columns                              | Dynamic    |
| phpmyadmin/pma__column_info                                  | Dynamic    |
| phpmyadmin/pma__designer_settings                            | Dynamic    |
| phpmyadmin/pma__export_templates                             | Dynamic    |
| phpmyadmin/pma__favorite                                     | Dynamic    |
| phpmyadmin/pma__history                                      | Dynamic    |
| phpmyadmin/pma__navigationhiding                             | Dynamic    |
| phpmyadmin/pma__pdf_pages                                    | Dynamic    |
| phpmyadmin/pma__recent                                       | Dynamic    |
| phpmyadmin/pma__relation                                     | Dynamic    |
| phpmyadmin/pma__savedsearches                                | Dynamic    |
| phpmyadmin/pma__table_coords                                 | Dynamic    |
| phpmyadmin/pma__table_info                                   | Dynamic    |
| phpmyadmin/pma__table_uiprefs                                | Dynamic    |
| phpmyadmin/pma__tracking                                     | Dynamic    |
| phpmyadmin/pma__userconfig                                   | Dynamic    |
| phpmyadmin/pma__usergroups                                   | Dynamic    |
| phpmyadmin/pma__users                                        | Dynamic    |
| ylms_friendica/FTS_0000000000014c8d_000000000004a1ef_INDEX_1 | Dynamic    |
| ylms_friendica/FTS_0000000000014c8d_000000000004a1ef_INDEX_2 | Dynamic    |
| ylms_friendica/FTS_0000000000014c8d_000000000004a1ef_INDEX_3 | Dynamic    |
| ylms_friendica/FTS_0000000000014c8d_000000000004a1ef_INDEX_4 | Dynamic    |
| ylms_friendica/FTS_0000000000014c8d_000000000004a1ef_INDEX_5 | Dynamic    |
| ylms_friendica/FTS_0000000000014c8d_000000000004a1ef_INDEX_6 | Dynamic    |
| ylms_friendica/FTS_0000000000014c8d_BEING_DELETED            | Dynamic    |
| ylms_friendica/FTS_0000000000014c8d_BEING_DELETED_CACHE      | Dynamic    |
| ylms_friendica/FTS_0000000000014c8d_CONFIG                   | Dynamic    |
| ylms_friendica/FTS_0000000000014c8d_DELETED                  | Dynamic    |
| ylms_friendica/FTS_0000000000014c8d_DELETED_CACHE            | Dynamic    |
| ylms_friendica/application                                   | Dynamic    |
| ylms_friendica/application@002dtoken                         | Dynamic    |
| ylms_friendica/notification                                  | Dynamic    |
| ylms_friendica/post@002dcategory                             | Dynamic    |
| ylms_friendica/post@002dcontent                              | Dynamic    |
| ylms_friendica/post@002ddelivery@002ddata                    | Dynamic    |
| ylms_friendica/post@002dlink                                 | Dynamic    |
| ylms_friendica/post@002dmedia                                | Dynamic    |
| ylms_friendica/post@002dtag                                  | Dynamic    |
| ylms_friendica/post@002dthread                               | Dynamic    |
| ylms_friendica/post@002dthread@002duser                      | Dynamic    |
| ylms_friendica/post@002duser                                 | Dynamic    |
| ylms_friendica/post@002duser@002dnotification                | Dynamic    |
| ylms_friendica/user@002dcontact                              | Dynamic    |
+--------------------------------------------------------------+------------+

@Michael Vogel

Hans Wolters

3 years ago •

ariaDB [friendica]> 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');
Empty set (0.010 sec)

not here

utzer [Friendica] likes this.

utzer (on squeet.me)

3 years ago •

@Hans Wolters not here what?

@Hans Wolters

Hans Wolters

3 years ago •

It is not showing the outcome like you posted. Then again, I am not on a dev version.

utzer (on squeet.me)

3 years ago

@Hans Wolters
read this again:
and got no output, when I replace Compact with Dynamic in that query I get some list:

😜

@Hans Wolters

utzer (on squeet.me)

3 years ago •

@Michael Vogel ok, I restored the backup.

I still get a white screen for death and I get many of this in my php error log:

[05-Aug-2021 22:12:39 Europe/Berlin] PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error: 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE. in /var/www/src/Database/Database.php:560
Stack trace:
#0 /var/www/src/Database/Database.php(560): PDOStatement->execute()
#1 /var/www/src/Database/Database.php(738): Friendica\Database\Database->p()
#2 /var/www/src/Database/Database.php(1318): Friendica\Database\Database->e()
#3 /var/www/src/Core/Cache/DatabaseCache.php(108): Friendica\Database\Database->update()
#4 /var/www/src/App/Router.php(306): Friendica\Core\Cache\DatabaseCache->set()
#5 /var/www/src/App/Router.php(226): Friendica\App\Router->getCachedDispatchData()
#6 /var/www/src/App/Module.php(192): Friendica\App\Router->getModuleClass()
#7 /var/www/

@Michael Vogel ok, I restored the backup.

I still get a white screen for death and I get many of this in my php error log:

[05-Aug-2021 22:12:39 Europe/Berlin] PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error: 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE. in /var/www/src/Database/Database.php:560
Stack trace:
#0 /var/www/src/Database/Database.php(560): PDOStatement->execute()
#1 /var/www/src/Database/Database.php(738): Friendica\Database\Database->p()
#2 /var/www/src/Database/Database.php(1318): Friendica\Database\Database->e()
#3 /var/www/src/Core/Cache/DatabaseCache.php(108): Friendica\Database\Database->update()
#4 /var/www/src/App/Router.php(306): Friendica\Core\Cache\DatabaseCache->set()
#5 /var/www/src/App/Router.php(226): Friendica\App\Router->getCachedDispatchData()
#6 /var/www/src/App/Module.php(192): Friendica\App\Router->getModuleClass()
#7 /var/www/src/App.php(558): Friendica\App\Module->determineClass()
#8 /var/www/index.php(42): Friendica\App->runFrontend()
#9 {main}
  thrown in /var/www/src/Database/Database.php on line 560

I am back on stable and the database backup were both stable when I did the backup.

@Michael Vogel

utzer (on squeet.me)

3 years ago •

Edit not working, last sentence is wrong.
~~I am back on stable and the database backup were both stable when I did the backup.~~
I am back with the restored database from stable branch, restored together with the webroot.

As I wrote before I had to upgrade PHP, which is now 8.0.9 and was 7.x before, also mariadb was upgraded. So it is complicated.

utzer (on squeet.me)

3 years ago •

ah, I stopped the webserver, mariadb and php-fpm for now, as long as it is not working anyway.

Michael Vogel

3 years ago •

This "InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE" is a problem with the database server. And I really don't know how to fix it. Best would be to ask in some external MariaDB help forum.

utzer (on squeet.me)

3 years ago •

@Michael Vogel ok, so I need to figure out what changed in the latest #MariaDB version compared to the one before. Any tip which forum to use?

#MariaDB @Michael Vogel

Michael Vogel

3 years ago •

Sadly no.

utzer (on squeet.me)

3 years ago •

@Michael Vogel without irony, thanks so much for your help.

@Michael Vogel

Hans Wolters

3 years ago •

From what I recall in the past this might be due to being read/write. Compressed tables are (afaik) read only

Hans Wolters

3 years ago •

This does sound like a performance hit. It seems to be uncompressed and compressed again. So only if hd space is low this might be an option. Speeding this up might mean you need to drop and read a table often to make sure the buffer is ok.

With InnoDB page compression, compressed pages are immediately decompressed after being read from the tablespace file, and only uncompressed pages are stored in the buffer pool. In contrast, with InnoDB's COMPRESSED row format, compressed pages are decompressed immediately after they are read from the tablespace file, and both the uncompressed and compressed pages are stored in the buffer pool. This means that the COMPRESSED row format uses more space in the buffer pool than InnoDB page compression does.

https://mariadb.com/kb/en/innodb-page-compression/

utzer (on squeet.me)

3 years ago

@Hans Wolters no idea about compressed, thought that Friendica uses this, I think I did not set this.

@Hans Wolters

Michael Vogel

3 years ago •

Compressed is no problem, I'm using it on my machines.

Hans Wolters

3 years ago •

From what I read it might give you problems since it stores more data.

Hans Wolters

3 years ago •

Fehler 4047 beim Update der Datenbank aufgetreten
InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.

I do hope they are not using it, it would be a penalty. The error you posted show you are using a compressed format. Did you create the database with defaults like this?

utzer (on squeet.me)

3 years ago •

I will give up for now, need to get some sleep, copying the backup over to the server.

utzer (on squeet.me)

3 years ago •

thanks @Michael Vogel and @Hans Wolters for any effort you put in to help me.

@Michael Vogel @Hans Wolters

utzer (on squeet.me)

3 years ago •

OK, I have those tables left:

+-----------------------------------------+------------+
| NAME                                    | ROW_FORMAT |
+-----------------------------------------+------------+
| ylms_friendica/contact@002drelation     | Compressed |
| ylms_friendica/delayed@002dpost         | Compressed |
| ylms_friendica/diaspora@002dinteraction | Compressed |
| ylms_friendica/gserver@002dtag          | Compressed |
| ylms_friendica/inbox@002dstatus         | Compressed |
| ylms_friendica/item@002duri             | Compressed |
| ylms_friendica/notify@002dthreads       | Compressed |
| ylms_friendica/openwebauth@002dtoken    | Compressed |
+-----------------------------------------+------------+

For some reason I can not convert them. Anyone knows what the @ tables are and how to get them converted to Dynamic?

Michael Vogel

3 years ago •

You have to address these tables with backticks:
`contact-relation`

utzer (on squeet.me) likes this.

utzer (on squeet.me)

3 years ago •

@Michael Vogel and with a - instead of the @002d, maybe that is clear for you, but not for a N00b like me.

@Michael Vogel

utzer [Friendica]

3 years ago from Fediverse •

I am back.

Michael Vogel likes this.

Michael Vogel

3 years ago •

utzer [Friendica] likes this.

utzer [Friendica]

3 years ago from Fediverse •

@Michael Vogel thanks Michael.

@Michael Vogel

Michael Vogel likes this.

⇧