Sunday, January 16, 2011

Moodle 2.0 Course Backup: DDL SQL Execution Error

Moodle 2.0 fails to backup courses (and quizzes) returning the following error: "DDL sql execution error".

It appears that this error occurs if the database user does not have the permission to create temporary tables.

I did not have time to dig deeper, but adding the CREATE_TMP_TABLE privilege (in addition to the obvious SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER) appears to have fixed the problem.

If you are not using a GUI tool (e.g., MySQL Administrator), here is the SQL script for MySQL:
GRANT CREATE TEMPORARY TABLES ON `databasename`.* TO 'username';

In an ideal world, the installation script would have checked if the database user had sufficient permissions. In a semi-ideal world, the installation script (or the documentation) would have explicitly specified the minimum privileges necessary to install and run the software (I can do the checking myself). In the real world, however, most testers seem to just use the "root" user (who has all imaginable rights) or the "dbo" user (who is pretty much like "root", but for the database/s he/she owns), so they never catch such "annoyances" (or "bugs" - depends on the point of view). Unfortunately, this is not just a Moodle-specific issue - lots of other products (including commercial ones) have the same problem (sloppy testing and/or sloppy documentation).

No comments: