Moodle in English: DDL sql execution error - occurs when backing up because (I think) CREATE TEMPORARY TABLE is not permitted

DDL sql execution error - occurs when backing up because (I think) CREATE TEMPORARY TABLE is not permitted

by Joe Cape - Tuesday, 22 February 2011, 12:44 AM

I am not able to use the backup facility for my Moodle 2.0.1 website. After turning debugging on, I see that the cause of the problem:

DDL Execution Error

Debug info: Access denied for user 'heart257-talkin'@'%' to database 'heart257-talkin'

The specific DDL instruction that it is unable to execute is the following:

CREATE TEMPORARY TABLE mdl_backup_ids_temp (
id BIGINT(10) unsigned NOT NULL auto_increment,
backupid VARCHAR(32) NOT NULL DEFAULT '695c7e8980c045afd33d50bd0803d761',
itemname VARCHAR(160) NOT NULL DEFAULT '',
itemid BIGINT(10) unsigned NOT NULL,
newitemid BIGINT(10) unsigned NOT NULL DEFAULT 0,
parentitemid BIGINT(10) unsigned,
info MEDIUMTEXT,
CONSTRAINT PRIMARY KEY (id)
)

When I try to execute the same SQL statement in phpMyAdmin I get the same error coming up. However, I was able to create the same table after removing the 'TEMPORARY' clause so

CREATE TEMPORARY TABLE mdl_backup_ids_temp ( ...etc. )

Did work. However, when I return to try to back up the site I get a different error coming up. Unsurprisingly, when it tries to create the backup table it complains that it already exists:

Table "backup_ids_temp" already exists

So I'm guessing there must be some way to set up user access rights to allow the creation of temporary tables. Does anyone know where and how this can be configured? Thanks.

Average of ratings: -

Re: DDL sql execution error - occurs when backing up because (I think) CREATE TEMPORARY TABLE is not permitted

by Eloy Lafuente (stronk7) - Tuesday, 22 February 2011, 1:58 AM

Hi Jos,

it seems that the user you are using to connect to the database from Moodle lacks the "CREATE TEMPORARY TABLES" permission. So you need to grant it.

See this comment in the Tracker for some more details for MySQL. Also the Documentation shows that requirement.

Hope it helps, ciao

PD: Of course, drop the "mdl_backup_ids_temp" table that you've created manually or you'll run under problems.

Average of ratings: Useful (1)

Re: DDL sql execution error - occurs when backing up because (I think) CREATE TEMPORARY TABLE is not permitted

by Joe Cape - Tuesday, 22 February 2011, 8:30 AM

Hi Eloy,

That seems like the way to go! I haven't done any user admin with MySQL so that just didn't occur to me.

I have tried:

GRANT CREATE TEMPORARY TABLES ON  `my-database` . * TO  'my-username'@'localhost';

Yet that resulted in the same error:

Access denied for user 'my-username'@'%' to database 'my-database'

I then tried executing this with IDENTIFIED BY 'my-password':

GRANT CREATE TEMPORARY TABLES ON  `my-database` . * TO  'my-username'@'localhost' IDENTIFIED BY 'my-password';

Which again returned the same result. Finally I have tried creating a new user for the database and trying to grant them all privileges but this still tells me that they do not have access to the database. It seems like the error message is itself in error, since clearly my original 'user' does have access to the database, but CREATE TEMPORARY TABLE rights have not been granted and SQL isn't letting me do this.

Average of ratings: -

Re: DDL sql execution error - occurs when backing up because (I think) CREATE TEMPORARY TABLE is not permitted

by Joe Cape - Wednesday, 23 February 2011, 12:41 AM

This has been resolved after my hosting provider changed some permissions.

Average of ratings: -

"backup_ids_temp" already exists

by Darren Dickson - Wednesday, 31 October 2012, 11:09 AM

Problem: Table "backup_ids_temp" already exists

Moodle Version: 2.2.2.

The initial problem was a stall in the backup process for a course with a large amount of activities. (MDL-34388,http://tracker.moodle.org/browse/MDL-34388). The backup process for all other courses worked fine.

I then tried to have a go at fixing the backup stall by making some changes in this file (line 159.) /backup/util/dbops/backup_controller_dbops.class.php which I had read about

This didn't fix the problem, so I restored the original /backup/util/dbops/backup_controller_dbops.class.php file (which I had copied).

This precipitated my second problem, which appears to be that I now don't have permission to write a temp file in the database. When I try to backup any course now, I get this problem;

Table "backup_ids_temp" already exists

Stack trace:

line 492 of /lib/ddl/database_manager.php: ddl_exception thrown

line 159 of /backup/util/dbops/backup_controller_dbops.class.php: call to database_manager->create_temp_table()

line 106 of /backup/util/dbops/backup_controller_dbops.class.php: call to backup_controller_dbops::create_temptable_from_real_table()

line 39 of /backup/moodle2/backup_stepslib.php: call to backup_controller_dbops::create_backup_ids_temp_table()

line 34 of /backup/util/plan/backup_execution_step.class.php: call to create_and_clean_temp_stuff->define_execution()

line 153 of /backup/util/plan/base_task.class.php: call to backup_execution_step->execute()

line 148 of /backup/util/plan/base_plan.class.php: call to base_task->execute()

line 106 of /backup/util/plan/backup_plan.class.php: call to base_plan->execute()

line 304 of /backup/controller/backup_controller.class.php: call to backup_plan->execute()

line 111 of /backup/util/ui/backup_ui.class.php: call to backup_controller->execute_plan()

line 89 of /backup/backup.php: call to backup_ui->execute()

There is a clear reference to line 159 which I had tried to fiddle with.

Here is the backup error log...

[23-Oct-2012 09:17:40] instantiating backup controller 88a8288453ff6678411114e08db08f8e

[23-Oct-2012 09:17:40] setting controller status to 100

[23-Oct-2012 09:17:40] loading controller plan

[23-Oct-2012 09:17:40] setting controller status to 300

[23-Oct-2012 09:17:40] applying plan defaults

[23-Oct-2012 09:17:40] setting controller status to 400

[23-Oct-2012 09:17:40] checking plan security

[23-Oct-2012 09:17:40] setting controller status to 500

[23-Oct-2012 09:17:40] checking plan security

[23-Oct-2012 09:17:40] saving controller to db

[23-Oct-2012 09:17:40] calculating controller checksum 856643f47aba7dae5b2d7293143442c4

[23-Oct-2012 09:17:52] loading controller from db

[23-Oct-2012 09:17:52] checking plan security

[23-Oct-2012 09:17:52] saving controller to db

[23-Oct-2012 09:17:52] calculating controller checksum 856643f47aba7dae5b2d7293143442c4

[23-Oct-2012 09:18:00] loading controller from db

[23-Oct-2012 09:18:00] checking plan security

[23-Oct-2012 09:18:00] saving controller to db

[23-Oct-2012 09:18:00] calculating controller checksum 856643f47aba7dae5b2d7293143442c4

[23-Oct-2012 09:18:07] loading controller from db

[23-Oct-2012 09:18:07] checking plan security

[23-Oct-2012 09:18:07] setting controller status to 700

[23-Oct-2012 09:18:07] saving controller to db

[23-Oct-2012 09:18:07] calculating controller checksum b4f78fd53c9a9265301aae8eb775db0c

[23-Oct-2012 09:18:07] loading controller from db

[23-Oct-2012 09:18:07] setting controller status to 800

[23-Oct-2012 09:18:09] Default exception handler: Table "backup_ids_temp" already exists Debug:

* line 492 of /lib/ddl/database_manager.php: ddl_exception thrown

* line 159 of /backup/util/dbops/backup_controller_dbops.class.php: call to database_manager->create_temp_table()

* line 106 of /backup/util/dbops/backup_controller_dbops.class.php: call to backup_controller_dbops::create_temptable_from_real_table()

* line 39 of /backup/moodle2/backup_stepslib.php: call to backup_controller_dbops::create_backup_ids_temp_table()

* line 34 of /backup/util/plan/backup_execution_step.class.php: call to create_and_clean_temp_stuff->define_execution()

* line 153 of /backup/util/plan/base_task.class.php: call to backup_execution_step->execute()

* line 148 of /backup/util/plan/base_plan.class.php: call to base_task->execute()

* line 106 of /backup/util/plan/backup_plan.class.php: call to base_plan->execute()

* line 304 of /backup/controller/backup_controller.class.php: call to backup_plan->execute()

* line 111 of /backup/util/ui/backup_ui.class.php: call to backup_controller->execute_plan()

* line 89 of /backup/backup.php: call to backup_ui->execute()

[Further observation, there also appears to be some minor formatting problems in my other courses now (a problem related to my custom CSS?). Also, I created a clone of my site using c Panel, but when I open the clone site, all formatting has been removed. Only text and links seem to be there on the front page. Also, when I try to log in I get a message that the login file doesn't exist, even though it does. I have checked that permissions on the file are 644. The backup process seems to work fine in the old clone versions]

This moodle doc seems to be related to a fix, but goes to another level of complexity which I hope to avoid.

My next strategy is to ask for help in this forum.

Failing that I will try to upgrade to 2.3 (using CPanel upgrade) and see if that fixes the problem (although that caused another lot of errors last time).

If anyone can point me in the right direction, I would be most appreciative.

Average of ratings: -

Backup and restore

DDL sql execution error - occurs when backing up because (I think) CREATE TEMPORARY TABLE is not permitted

DDL sql execution error - occurs when backing up because (I think) CREATE TEMPORARY TABLE is not permitted

Re: DDL sql execution error - occurs when backing up because (I think) CREATE TEMPORARY TABLE is not permitted

Re: DDL sql execution error - occurs when backing up because (I think) CREATE TEMPORARY TABLE is not permitted

Re: DDL sql execution error - occurs when backing up because (I think) CREATE TEMPORARY TABLE is not permitted

"backup_ids_temp" already exists

Empowering educators to improve our world

Moodle

Support

Get Involved

Contributions

Downloads

Tracker

Development

Empowering educators to improve our world