Unable to restore courses - strange duplicate index error

Unable to restore courses - strange duplicate index error

by Howard Miller -
Number of replies: 4
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
My courses no longer restore, although it says complete all you get is the resource/activity icon and no link.

With debugging turned up I get loads of...

Creating sections
Duplicate entry '4294967295' for key 1

INSERT INTO mdl_backup_ids ( BACKUP_CODE, TABLE_NAME, OLD_ID, NEW_ID, INFO ) VALUES ( 1191417264, 'course_sections', 152, 2573, 's:0:"";' )

* line 1425 of lib/dmllib.php: call to debugging()
* line 472 of backup/lib.php: call to insert_record()
* line 947 of backup/restorelib.php: call to backup_putid()
* line 5867 of backup/restorelib.php: call to restore_create_sections()
* line 46 of backup/restore_execute.html: call to restore_execute()
* line 160 of backup/restore.php: call to include_once()


Duplicate entry '4294967295' for key 1

INSERT INTO mdl_backup_ids ( BACKUP_CODE, TABLE_NAME, OLD_ID, NEW_ID, INFO ) VALUES ( 1191417264, 'course_modules', 363, 4359, 's:2:"17";' )

* line 1425 of lib/dmllib.php: call to debugging()
* line 472 of backup/lib.php: call to insert_record()
* line 1005 of backup/restorelib.php: call to backup_putid()
* line 5867 of backup/restorelib.php: call to restore_create_sections()
* line 46 of backup/restore_execute.html: call to restore_execute()
* line 160 of backup/restore.php: call to include_once()

I can't quite get my head around what key 1 is, but I can't find any likely entries for the offending value in any field.

Slightly more oddly this site (1.8.2+ from a few weeks ago) has a rather different index profile to my latest cvs 1.8.2+ site, which restores the same courses fine..

Broken course:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
mdl_backup_ids 0 PRIMARY 1 id A 0 BTREE
mdl_backup_ids 0 backup_ids_uk 1 backup_code A BTREE
mdl_backup_ids 0 backup_ids_uk 2 table_name A BTREE
mdl_backup_ids 0 backup_ids_uk 3 old_id A 0 BTREE

Good course:
+----------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| mdl_backup_ids | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | |
| mdl_backup_ids | 0 | mdl_backids_bactabold_uix | 1 | backup_code | A | NULL | NULL | NULL | | BTREE | |
| mdl_backup_ids | 0 | mdl_backids_bactabold_uix | 2 | table_name | A | NULL | NULL | NULL | | BTREE | |
| mdl_backup_ids | 0 | mdl_backids_bactabold_uix | 3 | old_id | A | 0 | NULL | NULL | | BTREE | |
+----------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Average of ratings: -
In reply to Howard Miller

Re: Unable to restore courses - strange duplicate index error

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Answered my own question....

Never mind the indexes the whole table definition is different. The ID field went from INT to BIGINT at some point. My table has exceeded its maximum size for an INT index.


When did this change? Is there anything I can do? I don't know what this table is for really - can I truncate the table or somesuch??
In reply to Howard Miller

Re: Unable to restore courses - strange duplicate index error

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
In reply to Howard Miller

Ang: Re: Unable to restore courses - strange duplicate index error

by Morten Brydensholt -

Howard,

Did you find a solution to this severe problem?

It has been driving me nuts for a month now.

Best regards

Morten

In reply to Morten Brydensholt

Ang: Re: Unable to restore courses - strange duplicate index error

by Morten Brydensholt -

I have another newer site to compare with, and this database has the BIGINT overall instead of INT.

So I manually changed all the mdl_backup_... tables. Everywhere the data type was INT I changed that to BIGINT.

Et voila! Now I can restore a course.

But leaves with with some questions.
Is this safe? If so, can anyone supply a sql command to alter all the INT data types to BIGINT.
Who knows what else should be changed to bring the database up-to-date.

Morten