We just upgraded Moodle from 1.3.1 to 1.4.3 (big upgrade). Everything looks fine except resources. Clicking on them in existing courses launches a new "blank" webpage...the link is ok, no data on the page.
I tried to add a new resource to a page and received this error:
Could not add a new instance or resource
Help!!
Thanks in advance!
Desiree Koons
After you changed the code (files), did you visit the Administration screen on your installation so that the database could update?
best,
Tom
best,
Tom
Yes, we did. For more details, here's a post made by my colleague:
I carefully backed up and followed the upgrade instruction and received only the following errors during the database upgrade:
(mysql): ALTER TABLE `mdl_user` ADD INDEX user_auth (auth)
1072: Key column 'auth' doesn't exist in table
adodb_mysql._execute( ALTER TABLE `mdl_user` ADD INDEX user_auth (auth) , false) % line 804, file: adodb.inc.php
adodb_mysql.execute( ALTER TABLE `mdl_user` ADD INDEX user_auth (auth) ) % line 30, file: datalib.php
execute_sql( ALTER TABLE `mdl_user` ADD INDEX user_auth (auth) ) % line 848, file: mysql.php
main_upgrade(2004083125) % line 161, file: index.php
Error
(mysql): ALTER TABLE `mdl_course` ADD INDEX idnumber (idnumber)
1072: Key column 'idnumber' doesn't exist in table
adodb_mysql._execute( ALTER TABLE `mdl_course` ADD INDEX idnumber (idnumber) , false) % line 804, file: adodb.inc.php
adodb_mysql.execute( ALTER TABLE `mdl_course` ADD INDEX idnumber (idnumber) ) % line 30, file: datalib.php
execute_sql( ALTER TABLE `mdl_course` ADD INDEX idnumber (idnumber) ) % line 852, file: mysql.php
main_upgrade(2004083125) % line 161, file: index.php
Error
Everything else seems to be fine except that when clicking on any Resources, they do not load. Running tail on the Apache logs gives me these (or very similar) errors:
[Mon Feb 14 20:20:23 2005] [error] PHP Warning: main(/Library/WebServer/rmhs/moodle/mod/resource/type/4/resource.class.php): failed to open stream: No such file or directory in /Library/WebServer/rmhs/moodle/mod/resource/view.php on line 20
[Mon Feb 14 20:20:27 2005] [error] PHP Warning: main(/Library/WebServer/rmhs/moodle/mod/resource/type/6/resource.class.php): failed to open stream: No such file or directory in /Library/WebServer/rmhs/moodle/mod/resource/view.php on line 20
[Mon Feb 14 20:20:33 2005] [error] PHP Warning: main(/Library/WebServer/rmhs/moodle/mod/resource/type/3/resource.class.php): failed to open stream: No such file or directory in /Library/WebServer/rmhs/moodle/mod/resource/view.php on line 20
Any idea what the problem here is? Is the database hosed? Seems that the problem here is related to the resources table.
Thanks for your help!
The good news is that these upgrade errors would explain the issues with the resources. 
Now that we've narrowed it down, developers could you lend a hand?
I wish I had the knowledge to figure it out for you. Essentially, we have to add those tables that are missing.
Now that we've narrowed it down, developers could you lend a hand?
I wish I had the knowledge to figure it out for you. Essentially, we have to add those tables that are missing.
Certainly was a big upgrade. The way resources are handled was changed in 1.4 - they now use a pluggable class system. By looking at the above errors it seems that there were problems in updating the resources database table as well. The lines above should read something like eg:
.../moodle/mod/resource/type/text/resource.class.php
If you are able to have a look at the database, the 'resource' table should have a column 'type' with entries such as 'text', 'html', 'file', 'directory'. If it doesn't then this is where the error is. If it does then error lies elsewhere, most likely a corrupted PHP file somewhere.
.../moodle/mod/resource/type/text/resource.class.php
If you are able to have a look at the database, the 'resource' table should have a column 'type' with entries such as 'text', 'html', 'file', 'directory'. If it doesn't then this is where the error is. If it does then error lies elsewhere, most likely a corrupted PHP file somewhere.
aha! What you are saying makes perfect sense. The values in the 'type' column are numbers such as 0, 2, 4, etc.
I setup a separate moodle test server to try to figure this database thing out and I imported a dump from the production server. So, I still have those numerical values for 'type' but--this is the good part--I see that some of the new resources I added when testing have values of "text", "html", etc. for 'type'. So, the fix should be to either manually or with a query script, change the numerical values to the new proper values.
I'll try some and see if this does the trick.
I setup a separate moodle test server to try to figure this database thing out and I imported a dump from the production server. So, I still have those numerical values for 'type' but--this is the good part--I see that some of the new resources I added when testing have values of "text", "html", etc. for 'type'. So, the fix should be to either manually or with a query script, change the numerical values to the new proper values.
I'll try some and see if this does the trick.
Indeed, this solves the problem.
We'll need to change the value in the 'type' column but it seems that we'll also need to populate the 'options' column too.
We'll need to change the value in the 'type' column but it seems that we'll also need to populate the 'options' column too.
What are valid values for the 'type' column?
Good question. I've been looking for a database model...there are some that were posted awhile ago in the Database schema forum.
But, looking at the moodle folder, in /moodle/mod/resource/db the file mysql.sql is a MySQL-Dump of the table structure for mdl_resource.
This line defines the type column:
type varchar(30) NOT NULL default '', (This is a variable-length string).
Our previous version was defined like this:
type tinyint(4) NOT NULL default '0', (This is a 1 byte integer).
That's a big difference. So, for us, the upgrade was a failure because the database did not upgrade properly and convert the values appropriately (I'm assuming it was supposed to); our current "resource types" have integer values but the new Moodle wants string values. Oy!
I have manually changed some of the values from integers to "file" as well as "" and was successful in viewing the resource. It would be nice to know exactly how to translate the integer values to the string values. In other words, what should "0" be and what should "2" be, etc. I may just change them all to "" because that's the easiest value and it works but I'm going to post in the developer forum to see if any of the database brains can answer this one.
But, looking at the moodle folder, in /moodle/mod/resource/db the file mysql.sql is a MySQL-Dump of the table structure for mdl_resource.
This line defines the type column:
type varchar(30) NOT NULL default '', (This is a variable-length string).
Our previous version was defined like this:
type tinyint(4) NOT NULL default '0', (This is a 1 byte integer).
That's a big difference. So, for us, the upgrade was a failure because the database did not upgrade properly and convert the values appropriately (I'm assuming it was supposed to); our current "resource types" have integer values but the new Moodle wants string values. Oy!
I have manually changed some of the values from integers to "file" as well as "" and was successful in viewing the resource. It would be nice to know exactly how to translate the integer values to the string values. In other words, what should "0" be and what should "2" be, etc. I may just change them all to "" because that's the easiest value and it works but I'm going to post in the developer forum to see if any of the database brains can answer this one.
I copied the mdl_resource.* files from a backup I'd made before upgrading to 1.4.3. I changed the column type to varchar(30). Things worked fine after that.
This is a complicated changeover - the reason is that when we were creating the new pluggable system we realised some of the old resource types were the same thing but with different options. You can find the database upgrade code in /mod/resource/db/mysql.php (lines 37-54). You could probably grab the SQL code from there.
It basically does the following:
HTH.
It basically does the following:
type = 1 : type = 'html', alltext = [reference field]I've attached a dump of the 'resource' table CREATE for reference.
type = 2 : type = 'file', options = 'frame'
type = 3 : type = 'file'
type = 4 : type = 'text', options = '0'
type = 5 : type = 'file'
type = 6 : type = 'html'
type = 7 : type = 'file'
type = 8 : type = 'text', options = '3'
type = 9 : type = 'directory'
HTH.
Great...this is helpful.
Is there a current ER diagram of the database available anywhere? This would be really helpful in understanding the relationships between tables.
Thanks for your help!
Is there a current ER diagram of the database available anywhere? This would be really helpful in understanding the relationships between tables.
Thanks for your help!
Something must have happened relatively recently that broke the upgrade from 1.3 
It used to work fine, because I upgraded stacks of sites from 1.3.x to 1.4.
A quick fix is to edit the "modules" table, and set the version for "resources" back to 2004070900, then visit the admin page. This will force the resource module to try upgrading itself again.
A quick fix is to edit the "modules" table, and set the version for "resources" back to 2004070900, then visit the admin page. This will force the resource module to try upgrading itself again.
Hi Martin. Thanks for this suggestion. This partially worked. The data type was changed and many of the values were changed from integers to strings (apparently all of the integers except for the ones with the value of "0"...unfortunately, not all of them...the script ran into some errors:
1060: Duplicate column name 'popup'
adodb_mysql._execute(ALTER TABLE mdl_resource ADD popup TEXT DEFAULT '' AFTER `allt..., false) % line 804, file: adodb.inc.php
adodb_mysql.execute(ALTER TABLE mdl_resource ADD popup TEXT DEFAULT '' AFTER `allt...) % line 30, file: datalib.php
execute_sql(ALTER TABLE mdl_resource ADD popup TEXT DEFAULT '' AFTER `allt...) % line 211, file: datalib.php
table_column(resource, , popup, text, , , , , alltext) % line 19, file: mysql.php
resource_upgrade(2004070900, Object:stdClass) % line 150, file: adminlib.php
1061: Duplicate key name 'course'
adodb_mysql._execute(ALTER TABLE mdl_resource ADD INDEX course (course), false) % line 804, file: adodb.inc.php
adodb_mysql.execute(ALTER TABLE mdl_resource ADD INDEX course (course)) % line 30, file: datalib.php
execute_sql(ALTER TABLE mdl_resource ADD INDEX course (course)) % line 152, file: datalib.php
modify_database(, ALTER TABLE prefix_resource ADD INDEX course (course);) % line 57, file: mysql.php
resource_upgrade(2004070900, Object:stdClass) % line 150, file: adminlib.php
I think I'll go ahead and change the remaining values in the type column and then spend some time checking the tables against the fresh install on my test server or an ER diagram to see if there's anything else that needs to be fixed.
Thanks for your help!
1060: Duplicate column name 'popup'
adodb_mysql._execute(ALTER TABLE mdl_resource ADD popup TEXT DEFAULT '' AFTER `allt..., false) % line 804, file: adodb.inc.php
adodb_mysql.execute(ALTER TABLE mdl_resource ADD popup TEXT DEFAULT '' AFTER `allt...) % line 30, file: datalib.php
execute_sql(ALTER TABLE mdl_resource ADD popup TEXT DEFAULT '' AFTER `allt...) % line 211, file: datalib.php
table_column(resource, , popup, text, , , , , alltext) % line 19, file: mysql.php
resource_upgrade(2004070900, Object:stdClass) % line 150, file: adminlib.php
1061: Duplicate key name 'course'
adodb_mysql._execute(ALTER TABLE mdl_resource ADD INDEX course (course), false) % line 804, file: adodb.inc.php
adodb_mysql.execute(ALTER TABLE mdl_resource ADD INDEX course (course)) % line 30, file: datalib.php
execute_sql(ALTER TABLE mdl_resource ADD INDEX course (course)) % line 152, file: datalib.php
modify_database(, ALTER TABLE prefix_resource ADD INDEX course (course);) % line 57, file: mysql.php
resource_upgrade(2004070900, Object:stdClass) % line 150, file: adminlib.php
I think I'll go ahead and change the remaining values in the type column and then spend some time checking the tables against the fresh install on my test server or an ER diagram to see if there's anything else that needs to be fixed.
Thanks for your help!
We did the exact same thing (upgrade from 1.3.1 to 1.4.3), and we are getting the exact same problems with resources: links are ok, but show blank screen, and when trying to add a new resource, it generates the same error: could not add a new instance of resource.
We checked the integrity of the database, and it's fine. We did a clean install of the files, which upgraded the database.
Any ideas anybody? we would appreciate any clues. Thanks!
We checked the integrity of the database, and it's fine. We did a clean install of the files, which upgraded the database.
Any ideas anybody? we would appreciate any clues. Thanks!