Moodle in English: Error DDL sql execution error (mod_h5pactivity)

Hello all,

I have run into an error when completing the final step when trying to upgrade the Moodle database.

I am upgrading from 3.7 to Moodle 3.9 (Build: 20200615)

I first ran all the server checks good all green ticks.

Other checks found the: mysql_full_unicode_support - check only so i don't think this is a problem.

See error below:

mod_h5pactivity

DDL sql execution error

More information about this error

Debug info: Table 'mdl_h5pactivity' already exists
CREATE TABLE mdl_h5pactivity (
id BIGINT(10) NOT NULL auto_increment,
course BIGINT(10) NOT NULL,
name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
timecreated BIGINT(10) NOT NULL,
timemodified BIGINT(10) NOT NULL,
intro LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
introformat SMALLINT(4) NOT NULL DEFAULT 0,
grade BIGINT(10) DEFAULT 0,
displayoptions SMALLINT(4) NOT NULL DEFAULT 0,
enabletracking TINYINT(1) NOT NULL DEFAULT 1,
grademethod SMALLINT(4) NOT NULL DEFAULT 1,
reviewmode SMALLINT(4) DEFAULT 1,
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_h5pa_cou2_ix (course)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE = utf8_general_ci
COMMENT='Stores the h5pactivity activity module instances.'
;
CREATE TABLE mdl_h5pactivity_attempts (
id BIGINT(10) NOT NULL auto_increment,
h5pactivityid BIGINT(10) NOT NULL,
userid BIGINT(20) NOT NULL,
timecreated BIGINT(10) NOT NULL,
timemodified BIGINT(10) NOT NULL,
attempt MEDIUMINT(6) NOT NULL DEFAULT 1,
rawscore BIGINT(10) DEFAULT 0,
maxscore BIGINT(10) DEFAULT 0,
scaled NUMERIC(10,5) NOT NULL DEFAULT 0,
duration BIGINT(10) DEFAULT 0,
completion TINYINT(1),
success TINYINT(1),
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_h5paatte_tim2_ix (timecreated)
, KEY mdl_h5paatte_h5ptim2_ix (h5pactivityid, timecreated)
, KEY mdl_h5paatte_h5puse2_ix (h5pactivityid, userid)
, KEY mdl_h5paatte_h5p2_ix (h5pactivityid)
, UNIQUE KEY mdl_h5paatte_h5puseatt2_uix (h5pactivityid, userid, attempt)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE = utf8_general_ci
COMMENT='Users attempts inside H5P activities'
;
CREATE TABLE mdl_h5pactivity_attempts_results (
id BIGINT(10) NOT NULL auto_increment,
attemptid BIGINT(10) NOT NULL,
subcontent VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci,
timecreated BIGINT(10) NOT NULL,
interactiontype VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci,
description LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
correctpattern LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
response LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
additionals LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
rawscore BIGINT(10) NOT NULL DEFAULT 0,
maxscore BIGINT(10) NOT NULL DEFAULT 0,
duration BIGINT(10) DEFAULT 0,
completion TINYINT(1),
success TINYINT(1),
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_h5paatteresu_atttim2_ix (attemptid, timecreated)
, KEY mdl_h5paatteresu_att2_ix (attemptid)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE = utf8_general_ci
COMMENT='H5Pactivities_attempts tracking info'
Error code: ddlexecuteerror

Stack trace:

line 492 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown
line 1098 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 77 of /lib/ddl/database_manager.php: call to mysqli_native_moodle_database->change_database_structure()
line 427 of /lib/ddl/database_manager.php: call to database_manager->execute_sql_arr()
line 372 of /lib/ddl/database_manager.php: call to database_manager->install_from_xmldb_structure()
line 830 of /lib/upgradelib.php: call to database_manager->install_from_xmldb_file()
line 565 of /lib/upgradelib.php: call to upgrade_plugins_modules()
line 1917 of /lib/upgradelib.php: call to upgrade_plugins()
line 711 of /admin/index.php: call to upgrade_noncore()

Has anyone else had this issue? I would appreciate any assistance with this.

Cheers,

Ben

評比平均分數： -

Error DDL sql execution error (mod_h5pactivity)

由Сергей Ярмоленко發表於2020年 06月 22日(週一) 01:44

I have exactly the same problem.
I migrate from 3.8 to 3.9 and from centos 7 to sentos 8.
on centos 8 deployed:
apache 2.4.37
mariadb 10.3.17
php 7.2.11

Mariadb 5.5.64 works on the old version.
made a dump.
restored on a new service.
also rewrote to the new server
moodledata
and also updated the Moodle folder
analytic
auth
blocks
mod
local
theme

as soon as I delete the folder /moodle/mod/h5pactivity
updating occurs without errors.
I return the h5pactivity folder back - an error appears.

with mysql_full_unicode_support I have no errors

評比平均分數：Useful (1)

Re: Error DDL sql execution error (mod_h5pactivity)

由Benjamin Waller發表於2020年 06月 22日(週一) 10:55

Hello Сергей Ярмоленко,

So just to confirm that with mysql_full_unicode_support and return the h5pactivity folder back, you have no errors?
If that is the solution, could you help guide me through how to add mysql_full_unicode_support ? I am fairly new to running my own Moodle instance.

Cheers,
Ben

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Сергей Ярмоленко發表於2020年 06月 22日(週一) 14:59

Hello Benjamin Waller,

mysql_full_unicode_support error solution is not a DDL sql execution error solution

https://docs.moodle.org/39/en/MySQL_full_unicode_support
this article should solve your problem with mysql_full_unicode_support

The problem with DDL sql execution error is still relevant.

評比平均分數：Useful (1)

Re: Error DDL sql execution error (mod_h5pactivity)

由Leon Stringer發表於2020年 06月 22日(週一) 18:41

mdl_h5pactivity is a new table in Moodle 3.9.

If I try to upgrade a site from 3.7 to 3.9 but then restore the 3.7 database to return to this version and subsequently try to upgrade I get the same error with the same table:

DDL sql execution error: mod_h5pactivity

So this error is probably as a result of a previously failed upgrade attempt, the 3.7 database having been restored on top of the 3.9 database. By default MySQL/MariaDB will leave any new tables untouched that aren't in the backup file being restored.

It looks like the following are the new tables in 3.9 so you could see if they exist in the 3.7 database and delete (drop) them if they are. Ensure you have a database backup before making any changes.

mdl_badge_backpack_oauth2
mdl_contentbank_content
mdl_forum_grades
mdl_h5p
mdl_h5pactivity
mdl_h5pactivity_attempts
mdl_h5pactivity_attempts_results
mdl_h5p_contents_libraries
mdl_h5p_libraries
mdl_h5p_libraries_cachedassets
mdl_h5p_library_dependencies
mdl_quizaccess_seb_quizsettings
mdl_quizaccess_seb_template

Once you've checked that these aren't present then the upgrade can be attempted again.

評比平均分數：Useful (3)

Re: Error DDL sql execution error (mod_h5pactivity)

由Leon Stringer發表於2020年 06月 22日(週一) 18:47

Also: if the site isn't on shared hosting consider planning to switch to full Unicode support in the database (utf8mb4). It's the Moodle recommendation and MySQL's bodged utf8 implementation has no place in 2020. I'd recommend running through the process in a test environment before making the change on the live site.

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Benjamin Waller發表於2020年 06月 23日(週二) 20:28

Thank you Leon,

I logged in to phpMyAdmin to check and i can find those tables but i am a little confused about the 3.7 vs 3.9 database. So when i am in my phpMyAdmin account i should be seeing the 3.9 database which was the 3.7 one because i restored it in the progress of upgrading? Sorry, i am very new to this. Pls see a screenshot of one example table https://www.sugarsync.com/pf/D6922769_683_6154037578?_ga=2.112608964.1122925838.1592914742-1260109309.1592914742
And how would i go about deleting them?
Cheers,
Ben

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Leon Stringer發表於2020年 06月 23日(週二) 22:27

It looks like there's just one database there: bitnami_moodle, the others (information_schema, mysql, performance_schema and sys) are system databases and should be left alone.

So it should be a case of clicking on database bitnami_moodle in the left hand pane, then the tables should be listed in the main pane on the right:

phpMyAdmin showing Moodle tables

You should then be able to find the tables and click ⛔ Drop (which is the database word for delete). Again, ensure there's a database backup before making changes.

評比平均分數：Useful (1)

Re: Error DDL sql execution error (mod_h5pactivity)

由Benjamin Waller發表於2020年 06月 24日(週三) 07:46

Hello Leon,

Thank you very much for your help. That did the trick! I deleted all those tables and able to update moodle. Once again I am really appreciative.

On the topic of full Unicode support, what is the best method for doing this? When following the instructions from this page https://docs.moodle.org/39/en/MySQL_full_unicode_support do I need to :
1. add the following lines of code to the my.cnf file? See screenshot of file below.
[client]
default-character-set = utf8mb4

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake

[mysql]
default-character-set = utf8mb4

2. Then run the follwing scripts?
Restart your MySQL server.
Run the CLI script to convert to the new Character set and Collation (requires Moodle 3.1.5, 3.2.2 or newer):
$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
Make sure to repair and optimize all databases and tables.
mysqlcheck -u root -p --auto-repair --optimize --all-databases

Please let me know if this is the best and easiest way.

Best regards,

Ben

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Leon Stringer發表於2020年 06月 24日(週三) 23:51

Broadly, yes. I'd start by checking whether you even need to change the MySQL/MariaDB settings. This can be done in phpMyAdmin by pasting the statement:

SHOW GLOBAL VARIABLES WHERE variable_name IN ('innodb_file_format', 'innodb_large_prefix', 'innodb_file_per_table')

into the SQL tab and click Go:

phpMyAdmin SHOW GLOBAL VARIABLES

The values needed for utf8mb4 are:


Variable name	Value
innodb_file_format	Barracuda
innodb_file_per_table	ON
innodb_large_prefix	ON

You only need to make the changes to the MySQL/MariaDB server if any of these don't match. Different server OS types have the MySQL/MariaDB server configuration file in different locations. On my CentOS server it's in /etc/my.cnf.d/server.cnf. Your screenshot has an include for /etc/mysql/conf.d so check in there.

Once you have the above settings in place run:

php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci

This can take some time, e.g. hours if the site is > 10 GB. Then run:

mysqlcheck -u root -p --auto-repair --optimize --all-databases

Then edit the site's config.php changing dbcollation:

$CFG->dboptions = array (

  ⋮
  'dbcollation' => 'utf8mb4_unicode_ci',
);

Then check the site's Environment report to confirm that the Full UTF-8 Support warning no longer appears.

評比平均分數：Useful (1)

Re: Error DDL sql execution error (mod_h5pactivity)

由Benjamin Waller發表於2020年 06月 25日(週四) 09:46

Hello Leon,

After running that SQL query I only get the following variable:

Variable_name          Value
innodb_file_per_table   ON

So not having the 2 other values, what is the next step?

Add these values to this file? /etc/mysql/conf.d

(When I open this file it is empty)

Variable name	Value
innodb_file_format	Barracuda
innodb_large_prefix	ON

Sorry if this is painfully laborious but I have ever had to do anything like this before.
Cheers,
Ben

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Leon Stringer發表於2020年 06月 25日(週四) 19:37

From what you've said it sounds like whichever MySQL/MariaDB version you have is new enough that no changes are required.

You can probably just go ahead with the mysql_collation.php command. Take a database backup first! Also do the restore of the missing tables before this otherwise those tables will be restored with the old character encoding.

If you encounter any problems please also provide the database server type and version. phpMyAdmin shows this under Database server when you log in as does the mysql command line shell.

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Leon Stringer發表於2020年 06月 26日(週五) 18:24

The screenshot of missing.sql shows those tables as using utf8mb4 which indicates that MySQL/MariaDB is already configured for full Unicode support. So it should be a case of proceeding to the running of mysql_collation.php.

評比平均分數：Useful (1)

Re: Error DDL sql execution error (mod_h5pactivity)

由Benjamin Waller發表於2020年 06月 24日(週三) 09:11

Hello Leon,

Now I noticed I get an error from the database when trying to access the Content bank option for H5P in my course.. see screenshot below.

so I am guessing after deleting this table "mdl_contentbank_content" I get this error. Should I add back now all these tables I deleted? I would like to use the Contact bank and h5P related content.

Cheers,

Ben

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Leon Stringer發表於2020年 06月 24日(週三) 18:54

"Should I add back now all these tables I deleted" – these tables should have been created again during the 3.9 upgrade.

To recap to ensure we're both on the same page:

The site was originally 3.7.
There was an attempt to upgrade to 3.9.
This attempt was rolled back and the 3.7 database restored "over" the 3.9 database leaving the new 3.9 tables in place.
There was a subsequent attempt to upgrade to 3.9. This failed when Moodle tried to create the new 3.9 tables because they already existed. (This was the original question in this discussion thread).
The new 3.9 tables were deleted using phpMyAdmin. (My suggestion).
There was another attempt to upgrade to 3.9 which apparently succeeded.

So if the upgrade to 3.9 completed successfully in step 6. then the new 3.9 tables such as mdl_contentbank_content should have been created. If this table isn't there then that's concerning.

But you're correct: you would get "Error reading from database" if mdl_contentbank_content was missing.

So, can you confirm that mdl_contentbank_content is missing in phpMyAdmin? Are the other tables that were removed also still missing?

評比平均分數：Useful (2)

Re: Error DDL sql execution error (mod_h5pactivity)

由Benjamin Waller發表於2020年 06月 24日(週三) 22:03

Hello Leon,

These are my findings:
Tables missing:

mdl_badge_backpack_oauth2

mdl_contentbank_content

mdl_forum_grades

mdl_h5p

mdl_h5p_contents_libraries

mdl_h5p_libraries

mdl_h5p_libraries_cachedassets

mdl_h5p_library_dependencies

Tables present:

mdl_h5pactivity

mdl_h5pactivity_attempts

mdl_h5pactivity_attempts_results

mdl_quizaccess_seb_quizsettings

mdl_quizaccess_seb_template

Ben

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Leon Stringer發表於2020年 06月 24日(週三) 23:15

Thanks Ben, I've had a look into this. When Moodle upgrades:

for system tables and existing plugins it only attempts to create tables if they don't already exist;
for new plugins (including core plugins that are part of Moodle) it performs install steps which assume tables do not already exist.

Because the upgrade has been continued after the error (rather than rolling the site back to 3.7) it's already beyond point 1. and is now installing the new plugin tables in step 2.

But because I'd said to remove both the new system and new plugin tables, only the plugin tables were recreated when the upgrade continued.

So, firstly apologies for the misleading advice. The correct advice should have been to delete the following tables only:

h5pactivity
h5pactivity_attempts
h5pactivity_attempts_results
quizaccess_seb_quizsettings
quizaccess_seb_template

And secondly, if you can please go ahead and restore the missing tables from the database backup:

badge_backpack_oauth2
contentbank_content
forum_grades
h5p
h5p_contents_libraries
h5p_libraries
h5p_libraries_cachedassets
h5p_library_dependencies

(These lists correspond with the ones you provided).

There's advice on StackOverflow on restoring individual tables from a MySQL/MariaDB backup file. The backup file is a text file therefore it's possible to create a copy and edit this copy so it only contains the required tables. But if advice is needed, please ask.

評比平均分數：Useful (2)

Re: Error DDL sql execution error (mod_h5pactivity)

由Benjamin Waller發表於2020年 06月 25日(週四) 08:44

Hello Leon,

Thank you for your help with this.
I just want to check the command I will use is correct before i run it after reading the StackOverflow post.

This was the command I used to create a backup of that database.

mysqldump -u root -p bitnami_moodle > backup.sql

Therefore is the following command correct? and i guessing i will need to run it for each and every table?

$ sed -n -e '/CREATE TABLE.*`badge_backpack_oauth2`/,/CREATE TABLE/p' mysql.dump > badge_backpack_oauth2.dump

Thanks for your help.

Ben

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Leon Stringer發表於2020年 06月 25日(週四) 18:52

The field of computing rewards those who check the commands they will use before they run them and my does it punish those who do not!

The parameters for sed will vary depending on how the MySQL/MariaDB backup was taken. I'm assuming your backup contains DROP TABLE IF EXISTS statements so that's the pattern to match. You can have multiple matching patterns in sed so I think the command is:

sed -n \
-e '/DROP TABLE IF EXISTS `mdl_badge_backpack_oauth2`/,/UNLOCK TABLES/p' \
-e '/DROP TABLE IF EXISTS `mdl_contentbank_content`/,/UNLOCK TABLES/p' \
-e '/DROP TABLE IF EXISTS `mdl_forum_grades`/,/UNLOCK TABLES/p' \
-e '/DROP TABLE IF EXISTS `mdl_h5p`/,/UNLOCK TABLES/p' \
-e '/DROP TABLE IF EXISTS `mdl_h5p_contents_libraries`/,/UNLOCK TABLES/p' \
-e '/DROP TABLE IF EXISTS `mdl_h5p_libraries`/,/UNLOCK TABLES/p' \
-e '/DROP TABLE IF EXISTS `mdl_h5p_libraries_cachedassets`/,/UNLOCK TABLES/p' \
-e '/DROP TABLE IF EXISTS `mdl_h5p_library_dependencies`/,/UNLOCK TABLES/p' \
backup.sql > missing.sql

This assumes the database backup with the 3.9 tables in in backup.sql. You take all those lines and paste them at the command line, it's a single command. It should put the eight tables to be restored in missing.sql.

You then restore then with:

mysql -u root -p bitnami_moodle < missing.sql

After this the Content Bank page should work.

Caveats:

I have tested these steps and they worked on my system. However, there may be variations between our systems that need additional handling.
Before restoring missing.sql check the contents of this file. You should see DROP TABLE IF EXISTS statements followed by CREATE TABLE statements for the missing tables. My file is 213 lines long, yours should be similar.

評比平均分數：Useful (1)

Re: Error DDL sql execution error (mod_h5pactivity)

由Benjamin Waller發表於2020年 06月 25日(週四) 21:18

Hello Leon,
Thanks for hanging in there with me on this one.
I have now executed that command but I don't know where to find the missing.sql file! I am currently running a search using an FTP client (FileZilla) but it is taking ages. Do you know a likey folder it would be in?

Cheers,

Ben

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Leon Stringer發表於2020年 06月 25日(週四) 22:22

It should be in whichever folder you were in when you ran the command. Run pwd to see the current folder in the command line shell.

評比平均分數：Useful (1)

Re: Error DDL sql execution error (mod_h5pactivity)

由Benjamin Waller發表於2020年 06月 26日(週五) 07:16

Hi Leon. I was logged in as root user. Here is a screenshot of my terminal. Maybe the command didn't execute. I might have to use "sudo" in front of "sed" ?

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Leon Stringer發表於2020年 06月 26日(週五) 16:04

I'd expect sed to have created missing.sql in /home/ben as that's where you ran the command:

But your ls output is in /home which doesn't show us what files are in /home/ben:

cd /home, ls

So with pwd showing /home/ben, just run ls, don't run cd /home which switched to a different folder.

missing.sql should be listed. If so, check it actually contains CREATE TABLE statements for the missing tables.

Recap: The sed command should read backup.sql and create missing.sql. If there are no folder names these files are expected to be in the current folder. Or just prefix the files with their folder, e.g. /home/ben/backup.sql. Before you run sed use ls to check that the database backup file – which I'm assuming is backup.sql – is present. After you run sed run ls again to check that the output file – missing.sql – is now present. Check missing.sql contains the expected lines.

Let me know if this isn't making sense or if it still isn't working.

評比平均分數：Useful (1)

Re: Error DDL sql execution error (mod_h5pactivity)

由Benjamin Waller發表於2020年 06月 26日(週五) 16:50

Thanks Leon.
I am able to see both backup.sql and missing.sql but I'm a bit confused because there is no content in missing.sql and nothing in backup.sql

Am I opening these files correctly?

I also downloaded a backup.sql from PhpMyAdmin before I removed these tables, so there might be another option with that but i am curious to know why there is no content in these files.

Ben

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Leon Stringer發表於2020年 06月 26日(週五) 17:20

No, you're not opening these files correctly. You should probably run nano backup.sql and nano missing.sql with no '/' – the '/' is saying open the file in the top-level folder, and no sudo as you probably don't need to run this command with root (i.e. administrator user) privileges.

But you're absolutely correct in verifying the contents of these files before continuing.

評比平均分數：Useful (1)

Re: Error DDL sql execution error (mod_h5pactivity)

由Benjamin Waller發表於2020年 06月 26日(週五) 18:02

Hello Leon,
Yes how silly! here is a screenshot of the first page in the missing.sql. and the other tables seem to be there too.

So I guess the last step is to restore them with the command you mentioned:

mysql -u root -p bitnami_moodle < missing.sql

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Leon Stringer發表於2020年 06月 26日(週五) 18:22

That looks correct to me so yes, the next step is to run that command to restore the missing tables. Fingers crossed!

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Benjamin Waller發表於2020年 06月 26日(週五) 18:48

Yes success!! That worked! I checked that those tables were back in the database and also I don't get an error when going to the H5P Content bank. So all looks good!
Thank you so much for all your assistance with my issue, Leon. I really appreciate it.

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Elliot Bannister發表於2020年 12月 8日(週二) 23:31

Thanks for this useful conversation. I had the same error as OP, trying to update from 3.8 to 3.10.

I went into phpMyAdmin and deleted the following:

h5pactivity
h5pactivity_attempts
h5pactivity_attempts_results

I couldn't find the other two tables you suggested we delete. But, when I tried the upgrade again, and was given this error:

Error message

As a sanity check I went back into phpMyAdmin to search for "quizaccess" but no results:

Screenshot of phpMyAdmin

Thanks in advance for any recommendations!

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Elliot Bannister發表於2020年 12月 9日(週三) 00:13

Oh never mind... it's because I was only filtering Page 1!! I found the other two tables, dropped them, and 3.10 seems to have installed nicely. I'll leave my post up in case it helps anyone else.

Thanks for your post Leon Stringer.

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Elliot Bannister發表於2020年 12月 12日(週六) 01:47

Also, I later had to restore the three tables I had deleted as they were prerequisites for the migratehvp2h5p tool.
To restore the table: grab the SQL commands from XMLDB in Site Administration > Development and run (I ran them through phpMyAdmin).
For some reason I had to delete the tables to upgrade to Moodle 3.10, but then had to rebuild them to get migratehvp2h5p working.

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Nicolas Kuhl發表於2020年 06月 26日(週五) 18:29

Hey there,

I got the same issue while updating from 3.6 to 3.9.

I got it to work with updating from 3.6 to 3.7 to 3.8 and finally to 3.9

(While updating, always check the URL you are GETing after one successful iteration. (And noo that was not why I lost 30 Minutes..

))

It might not be the exact solution for your particular problem but I got the same error and now I have a 3.9 Moodle :D

Kind regards,

Nicolas

評比平均分數： -

Re: Error DDL sql execution error (mod_h5pactivity)

由Manfred Steger發表於2021年 02月 3日(週三) 15:56

Hi all, i read the whole thread and it helps me a lot to solve the same problem. It seems the problem is the upgrade from h5p as a plugin to core feature from moodle 3.x to 3.9. First time i tried the upgrade i deleted all h5p plugins from admin backend, and the upgrade was no problem. Serveral weeks later i mirrored my prod system and did the same upgrade procedure and it failed with Error DDL sql execution error (mod_h5pactivity).

I tried a fresh and clean install with version 3.8.3 and upgraded it to 3.9.2 without this issue. I think it's a problem with the used bitnami image. I think some of you have a dockerized environment like we have (Kubernetes). Here is a safe way to upgrade the live system and afterwards the docker image to the desired version. We had a lot of trouble after the moodle container upgrade (official Moodle Wiki Tutorial). The container failed to start without a proper failure message. Here's our way to upgrade Bitnami Moodle 3.x up to latest and solving the database error:

1. Download Moodle Version matching Bitnami (we need 3.9 so we catch 3.9.2) from official Moodle Repo but look at GitHub: https://github.com/bitnami/bitnami-docker-moodle/releases for supported Bitnami releases.
2. Download it on your server beside your moodle main folder. I.e. sudo wget https://download.moodle.org/download.php/direct/stable39/moodle-3.9.2.tgz
3. Activate Maintenance Mode in Moodle Backend or CLI
4. Backup Moodle: sudo mv moodle moodle.backup
5. Fresh Moodle folder: sudo mkdir moodle
6. sudo tar -xzvf moodle-3.9.2.tgz -C moodle --strip 1
7. Move needed files back:
8. sudo cp -a moodle.backup/config.php moodle
9. sudo cp -a moodle.backup/theme/snap moodle/theme (replace with your active theme - we use snap from blackboard)
10. (sudo cp -a moodle.backup/moodledata moodle/) make sure where' your moodledata folder. Bitnami fixed the wrong moodledata folder location with 3.9.2 version. Before that version the moodledata folder is inside the moodle folder! Keep that in mind because you have to change the config.php location if you move the moodledata folder to a different location.
11. Set permissions for Docker: sudo chown daemon:daemon -R moodle
12. sudo chmod -R g+rwX moodle
13. sudo chown root moodle/config.php
14. sudo chmod 640 moodle/config.php
15. Make DB changes:
16. if you have no phpMyAdmin you can install it in a few seconds with docker on local machine: docker run -it -d -e PMA_ARBITRARY=1 -p 8080:80 -v phpmyadmin_data:/sessions phpmyadmin:latest
17. Browser: localhost:8080
18. Server adsress of your database server with port: i.e. 192.168.177.12:2211 and login credentials
19. klick on bitnami_moodle
20. search for h5p -> all tables -> drop all tables
21. search mdl_quizaccess_seb_quizsettings and drop
22. search mdl_quizaccess_seb_template and drop
23. Navigate to your moodle instance with /admin and do the upgrade (disable CLI Maintenance Mode if you haven't set proper rights for admins)
24. Upgrade all plugins with a click on the check for updates button - if moodle can't find updates for mods and plugins, folder permissions are set wrong -> check step 11. to 14. and your docker user permissions
25. pull the image version used above and restart the container or bind it in kubernetes/rancher and redeploy.

We did serveral tests with UTF8/Unicode and this was not the problem, you don't need to convert tables.

評比平均分數：Useful (1)

Re: Error DDL sql execution error (mod_h5pactivity)

由Hermann Schwarz發表於2021年 10月 5日(週二) 20:05

The issue appears if a update process breakes.

Solution: just drop the DB tables :

h5pactivity
h5pactivity_attempts
h5pactivity_attempts_results
quizaccess_seb_quizsettings
quizaccess_seb_template

評比平均分數： -