Can’t Find H5P-Content

@icc0rz I think it’s the same issue. The content is not showing on the web page and can’t be opened on the backend. In the web server log file I get the following:

AH01071: Got error 'PHP message: WordPress-Datenbank-Fehler Unknown column 'hc.a11y_title' in 'field list' f\xc3\xbcr Abfrage SELECT hc.id
              , hc.title
              , hc.parameters AS params
              , hc.filtered
              , hc.slug AS slug
              , hc.user_id
              , hc.embed_type AS embedType
              , hc.disable
              , hl.id AS libraryId
              , hl.name AS libraryName
              , hl.major_version AS libraryMajorVersion
              , hl.minor_version AS libraryMinorVersion
              , hl.embed_types AS libraryEmbedTypes
              , hl.fullscreen AS libraryFullscreen
              , hc.authors AS authors
              , hc.source AS source
              , hc.year_from AS yearFrom
              , hc.year_to AS yearTo
              , hc.license AS license
              , hc.license_version AS licenseVersion
              , hc.license_extras AS licenseExtras
              , hc.author_comments AS authorComments
              , hc.changes AS changes
   ...'

@frederic3 That is strange, the database upgrade should happen automatically when the version number for the plugin changes. I will retest the upgrade once more. Thank you.

@icc0rz I just enabled WordPress logging and tried 1.15.2 again. I can see the following message, which I guess is where the database upgrade fails:

[07-May-2021 13:06:08 UTC] WordPress-Datenbank-Fehler 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 für Abfrage ALTER TABLE wp_pws_h5p_contents ADD COLUMN a11y_title VARCHAR(255) NULL von require('wp-blog-header.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('init'), WP_Hook->do_action, WP_Hook->apply_filters, H5P_Plugin::check_for_updates, H5P_Plugin::update_database, dbDelta

(sorry for the German locale, “WordPress-Datenbank-Fehler” just means WordPress database error, “für Abfrage” means for query)

Thank you @frederic3 then it makes sense why it is failing. It appears tables created using 10.2.1 and before of MariaDB (and probably MySQL as well) use a deprecated file format for storing the data that has some stricter limitations: https://mariadb.com/kb/en/innodb-file-format/#setting-a-tables-file-format

I will try to see if I can find an easy way to get around or warn about this issue in the plugin. Currently, the old way to get around it would be to change the file format and then dump and recreate the table.

I’m following this very interesting discussion because I run in the same problem when I updated the plugin from 1.15 to higher. I was lucky to go back with a backup.

Thank you for focussing the issue !

I also tried again update from 1.15.0 to 1.15.2 and enabled logs:

On the productive server,
Wordpress 5.5.3
PHP 7.4.16 (64bit)
MySQL 10.3.29-MariaDB-0ubuntu0.20.04.1

… I got the error:

WordPress-Datenbank-Fehler: [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]
ALTER TABLE qz_h5p_contents ADD COLUMN a11y_title VARCHAR(255) NULL

WordPress-Datenbank-Fehler: [Unknown column 'hc.a11y_title' in 'field list']
SELECT hc.id , hc.title , hc.parameters AS params , hc.filtered , hc.slug AS slug , hc.user_id , hc.embed_type AS embedType , hc.disable , hl.id AS libraryId , hl.name AS libraryName , hl.major_version AS libraryMajorVersion , hl.minor_version AS libraryMinorVersion , hl.embed_types AS libraryEmbedTypes , hl.fullscreen AS libraryFullscreen , hc.authors AS authors , hc.source AS source , hc.year_from AS yearFrom , hc.year_to AS yearTo , hc.license AS license , hc.license_version AS licenseVersion , hc.license_extras AS licenseExtras , hc.author_comments AS authorComments , hc.changes AS changes , hc.default_language AS defaultLanguage , hc.a11y_title AS a11yTitle FROM qz_h5p_contents hc JOIN qz_h5p_libraries hl ON hl.id = hc.library_id WHERE hc.id = 11

With my local site
Wordpress 5.5.3
PHP 7.4.3 (64bit)
MySQL 8.0.25-0ubuntu0.20.04.1

… update works fine.

Thanks a lot for looking at the problem !

Finally I found a solution which works for me:

As I’m not fit enough with sql queries I exported MariaDB database by phpMyAdmin, then imported it into a mysql8-server by phpMyAdmin, exported it there again and importet it in the end into the MariaDB-Server. After this I updated the H5P plugin from version 1.15.0 to 1.15.2 with success.

@frederic3 how do you roll back? if the plugin is uninstalled, the content is erased.

I am having the same problem after updating, @icc0rz

This is my DB server info:

Extensión	mysqli
Versión del servidor	10.5.9-MariaDB-1:10.5.9+maria~focal-log
Versión del cliente	mysqlnd 7.4.5

Is there a workaround other that returning to a precious backup? is this gonna be fixed in a future update of the plugin?

I used wp-rollback plugin to go back to a previous version. But only allows to go back to version 1.14

It worked because I can see the content, however I can’t edit any of them.

UPDATE: I worked with my developer. We created the row a11y_title in the DB manually, then updated the plugin from 1.14 to 1.15.3 and now everything is working (so far)

• This reply was modified 3 years, 3 months ago by marbaque.
• This reply was modified 3 years, 3 months ago by marbaque.
• This reply was modified 3 years, 3 months ago by marbaque.

As I think there will be no other solution, I decided to migrate my database to the new format. Here’s a short rundown of what I did.

Beware this is a dangerous operation where you could loose your website content! Only do what you understand!

1. I upgraded my server to Debian 11.0 Bullseye. This means, I am now on MariaDB 10.5.11.

2. I made a Backup of the H5P plugin version 1.15 (path needs to be adapted):
tar cvf h5p.tar /var/my-website/cms/wp-content/plugins/h5p

3. Just to be safe and able to quickly recover, I made an image copy of all database files (path is standard location in Debian):

systemctl stop mariadb
tar cvf mysql.tar /var/lib/mysql
systemctl start mariadb

4. Create a SQL script dump of the WordPress database, which includes H5P (the database name needs to be adapted):
mysqldump --add-drop-database wordpress >wordpress.sql

5. Completely erase the WordPress database and recreate it from scratch using the dump file (database name and database user needs to be adapted):

mysql -u root 
drop database wordpress;
create database wordpress;
grant all privileges on wordpress.* to wordpress@localhost;
flush privileges;
use wordpress;
source wordpress.sql;
exit

6. Upgrade H5P Plugin to Version 1.15.3. This worked without a problem now. In my case, the Row Format of the WordPress tables in MariaDB has changed from “Compact” to “Dynamic” (can be verified with select * from information_schema.INNODB_SYS_TABLES;)

Thanks, frederic3 and icc0rz, I just tried your hints. The solution works, I created a new mariadb database, using phpmyadmin. Very easy at the end: Export the old database, create new one, import data. Update h5p. All fine .-)
Guido