Installation

Updating databases when upgrading to github version

Updating databases when upgrading to github version

by John Curran -
Number of replies: 5
I am trying to upgrade my webwork installation to the newest version.

When I tried to log in I got the message "You may need to upgrade your course tables. If this is the admin course then you will need to upgrade the admin tables using the upgrade_admin_db.pl script."

So I ran the latter. There were a number of warnings saying that WebWork::DB::Schema:NewSQL:(Various):DESTROY was an undefined subroutine.

I am getting very similar messages when I try to change my admin password from the command line, and I can't remember the old one. I assume the problem is still with the database format? Any thoughts appreciated. Thanks.
In reply to John Curran

Re: Updating databases when upgrading to github version

by Jim Fischer -
I had this same issue going from 2.9 to 2.10. I was able to resolve the admin course by going to the ...webwork2/bin directory, then running the script indicated in the error message.

After fixing course admin, you can use the upgrade courses tool within the admin course to fix the other courses.


In reply to Jim Fischer

Re: Updating databases when upgrading to github version

by Gavin LaRose -
Hi all,

I'm upgrading a server from 2.7 to 2.10, and running upgrade_admin_db.pl throws the error message: 

DBD::mysql::db do failed: BLOB/TEXT column 'set_id' used in key specification without a 
key length at
/var/www/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm 
line 826.

Initial experimentation suggests that the database tables were upgraded as needed (in the sense that I can now log in to the admin course). That said, trying to upgrade other courses throws the same error.

Stackoverflow suggests that the error is that indices have to have a specified length: http://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length>, but it surprises me that this is showing up in WeBWorK. My guess is therefore that I've got an error with my configuration somewhere.

Thanks,
Gavin

In reply to Gavin LaRose

Re: Updating databases when upgrading to github version

by Danny Glin -
It's possible that your tables were upgraded so that they have the up-to-date schemata, and yet the indexing failed afterward.  If the indices weren't created, then select queries may run slower, and you're also not protected against key collisions in the database.  Of course I don't know how much of an effect this will have, since most of the heavy lifting is done by the perl code rather than the SQL.

If you want to check what you have for indices, you can run 'describe [tablename]' from within mysql, and it will show you.

There is code in WeBWorK to specify a key length where necessary.  Based on a quick look, it is hard coded to only specify a key length for columns whose type contains 'text' or 'blob'.  My first guess is that your database columns are some other data type, which isn't matching that regular expression.  That means that it is suppressing the length option, leading to the error.

If the tables were properly upgraded, and are just missing keys/indices, then you should be able to add the appropriate keys with the correct ALTER TABLE command.

Danny
In reply to Danny Glin

Re: Updating databases when upgrading to github version

by Gavin LaRose -
Thanks, Danny---that's really helpful. I'm seeing set_ids as tinyblobs in the database, whether or not there has been an attempt to update them. (With a 'unique' key flag, as well.)

The odd thing is that I upgraded another server from 2.7 to 2.10 without seeing this error.

Gavin
In reply to Gavin LaRose

Re: Updating databases when upgrading to github version

by Gavin LaRose -
Another update: at least one problem is that I had a table which had lost a column (admin_set_locations_user was missing user_id). This resulted in the update routine trying to add the column. However, it appears that even though adding a table correctly adds a length specifier to blobs and therefore manages key addition correctly, the add_column_field routine in Std.pm does not.

In that there were no data in the table, I manually dropped it (from within mysql), at which point the admin update script ran (and added the table) and it then functions fine.

I'm now testing upgrading other courses, but my initial testing suggests that this is working.

Vaguely related: is there anything that is done differently by upgrade_admin_course.pl from when one runs an upgrade through the admin course? Our server has a lot of courses, and running upgrade_admin_course.pl after changing the variable $upgrade_courseID would be significantly easier than running upgrades through the web interface.

Thanks,
Gavin