recovering h5p activites after mod_hvp bug on Moodle

This was noticed on July 24th - lots of pages on our Moodle instances were showing "The version of the H5P library H5P.Column used in this content is not valid. Content contains H5P.Column 1.18, but it should be H5P.Column 1.22."

At that time, I replied that, according to Gemini, this is a known H5P bug - https://github.com/h5p/moodle-mod_hvp/issues/632 - and the H5P core development team is actively working on a fix (Pull Request #633) - https://github.com/h5p/moodle-mod_hvp/pull/633

Gemini private URL for my reference - https://aistudio.google.com/prompts/1Ddl2aYqln8Y8wTIPtlLXG_8Fipeay_5F

Unfortunately, the next version of mod_hvp Moodle plugin was released, but it only had code to prevent such errors in future, but it did not automatically fix the existing problems.

So, took the help of Gemini again, and took steps to fix our Moodle instances. Private URL for my reference - https://aistudio.google.com/prompts/1LJYJaO3R8mjgpsd3w67TDlACd3Ni8JhL

Workflow was:

0. First download a "good copy" of the database backup - Since the buggy mod_hvp was released after Feb 2026, my choice was the backup of 22 Feb 2026. Unfortunately, this backup was 500+ MB as a .sql.gz file, where the logstore_standard_log table had not yet been cleaned up. Setting up this database on my local machine took 4+ hours for the database import. Private URL for my reference - https://chatgpt.com/c/6a8d13c5-1988-83ee-816b-082af1f2a874

sudo apt install mysql-client (was already installed)
sudo apt install mysql-server

systemctl status mysql

sudo mysql

CREATE DATABASE restore_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
quit;

zcat your_backup.sql.gz | sudo restore_test

(this took 4+ hours - should probably have done the gunzip separately? Also, 11+ GB database at /var/ from a 9+ GB sql file - sudo du -sb /var/lib/mysql/restore_test)

sudo mysql

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'your_password_here';

GRANT ALL PRIVILEGES ON restore_test.* TO 'myuser'@'localhost';

FLUSH PRIVILEGES;

Then to fix "Public Key Retrieval is not allowed" error in DBeaver, right-click to edit the connection, 

Go to Driver Properties.

Modify the properties,

allowPublicKeyRetrieval to true

useSSL to false

Then, by using a different port in a tunnel to the remote database, we can copy and paste data with DBeaver.

1. Fix the main library reference (e.g., Column or Interactive Book containers):

UPDATE vv_hvp h

JOIN vv_hvp_libraries old_lib ON old_lib.id = h.main_library_id

JOIN vv_hvp_libraries new_lib ON new_lib.machine_name = old_lib.machine_name

  AND new_lib.major_version = 1 AND new_lib.minor_version = 22

SET h.main_library_id = new_lib.id, h.filtered = NULL

WHERE old_lib.machine_name = 'H5P.Column'

  AND old_lib.major_version = 1 AND old_lib.minor_version = 18;

Updated rows 0

2.  Fix nested library references

UPDATE vv_hvp 

SET json_content = REPLACE(json_content, 'H5P.Column 1.18', 'H5P.Column 1.22'),

    filtered = NULL

WHERE json_content LIKE '%H5P.Column 1.18%';

UPDATE vv_hvp 

SET json_content = REPLACE(json_content, 'H5P.InteractiveVideo 1.27', 'H5P.InteractiveVideo 1.28'),

    filtered = NULL

WHERE json_content LIKE '%H5P.InteractiveVideo 1.27%';

Updated rows 7

3. Recovering "Wiped" Activities (Data Loss)

SELECT id, name, course FROM vv_hvp WHERE json_content LIKE '%"content":{"params":{}}}%';

37 rows on our instance.

4. Fixing one of the data-missing json-content fields - 

SELECT json_content FROM vv_hvp WHERE id = 30851 (from the backup)

to find the current libraries, look for H5P string in the json, 

and add to the ones below.

SELECT machine_name, major_version, minor_version

FROM vv_hvp_libraries

WHERE machine_name IN (

    'H5P.Video', 

    'H5P.MultiChoice', 

    'H5P.AdvancedText',

    'H5P.InteractiveVideo',

'H5P.Column',

'H5P.Image',

'H5P.CoursePresentation'

)

ORDER BY machine_name, major_version DESC, minor_version DESC;

Only had to find/replace CoursePresentation version, others had current versions. Then pasted the "corrected" json content in the same field of the live database.

5. Run a find/replace on the entire live database - starting first with one course,

UPDATE vv_hvp 

SET json_content = REPLACE(json_content, 'H5P.Column 1.18', 'H5P.Column 1.22'),

    filtered = NULL

WHERE course = 606 

  AND json_content LIKE '%H5P.Column 1.18%';

Updated rows 42.

Update main library id just in case.

UPDATE vv_hvp h

JOIN vv_hvp_libraries old_lib ON old_lib.id = h.main_library_id

JOIN vv_hvp_libraries new_lib ON new_lib.machine_name = old_lib.machine_name

SET h.main_library_id = new_lib.id, 

    h.filtered = NULL

WHERE h.course = 606

  AND old_lib.machine_name = 'H5P.Column'

  AND old_lib.major_version = 1 

  AND old_lib.minor_version = 18

  AND new_lib.major_version = 1 

  AND new_lib.minor_version = 22;

Updated rows 0.

Then, did for all courses,

UPDATE vv_hvp 

SET json_content = REPLACE(json_content, 'H5P.Column 1.18', 'H5P.Column 1.22'),

    filtered = NULL

WHERE json_content LIKE '%H5P.Column 1.18%';

Updated 221.

Then asked Gemini if this can be automated for data recovery for the 30+ instances of data loss - private URL for my reference -
 https://aistudio.google.com/prompts/14Bv6L5AfaOglFuUY_VJZ6F7A1cchR-FQ

That resulted in the python scripts at https://github.com/hn-88/fix-hvp/

which seem to have fixed all the data-loss in this instance.

Then, a new problem was reported on another Moodle instance - youtube videos were playing with audio only, and black screen. Putting that in a separate post here.