Installation

Upgrading to 2.5.1.1: database schema mismatch

Upgrading to 2.5.1.1: database schema mismatch

by Dave Rosoff -
Number of replies: 9
Hi,

When I try to upgrade old courses after installing 2.5.1.1, I get variants of the following error message for several different tables.
  • problem Schema and database table definitions do not agree
    • Field problem_id => is ok
    • Field flags => missing in schema
I tried to go through with the upgrade process, and it didn't hang or throw further errors, but also didn't fix the tables. I've checked that the SQL password is set correctly in site.conf. Are there any other possible culprits? Thanks for any help you can give.

Dave
In reply to Dave Rosoff

Re: Upgrading to 2.5.1.1: database schema mismatch

by Robin Cruz -
I'm working with Dave on updating our WeBWorK. So, the admin course cannot update the courses made before our upgrade (see Dave's note), but we are getting errors when we try to build a new problem set from courses built before the update. After trying to build a new homework set, the course seems broken--it shows these messages when you login to it. It logs you into the course, but this is all that shows up.
--rac

WeBWorK error

An error occured while processing your request. For help, please send mail to this site's webmaster (webmaster@localhost), including all of the following information as well as what what you were doing when the error occured.

Fri Jan 04 21:03:33 2013

Warning messages

  • Use of uninitialized value in join or string at /opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm line 814.<br />Use of uninitialized value in join or string at /opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm line 814.<br />Use of uninitialized value in join or string at /opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm line 814.<br />Use of uninitialized value in join or string at /opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm line 814.<br />Use of uninitialized value in join or string at /opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm line 814.<br />Use of uninitialized value in join or string at /opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm line 814.<br />Use of uninitialized value in join or string at /opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm line 814.<br />Use of uninitialized value in join or string at /opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm line 814.<br />Use of uninitialized value in join or string at /opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm line 814.<br />

Error messages

DBD::mysql::st execute failed: Unknown column 'merge1.published' in 'field list'
WeBWorK::DB::Schema::NewSQL::Std
/opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm
380
WeBWorK::DB::Schema::NewSQL::Merge::_get_fields_where_prepex
1
0


1794
UUUUUUUUUUUUU

WeBWorK::DB::Schema::NewSQL::Std
/opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm
440
WeBWorK::DB::Schema::NewSQL::Std::get_fields_where
1
1


1794
UUUUUUUUUUUUU

WeBWorK::DB::Schema::NewSQL::Std
/opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm
740
WeBWorK::DB::Schema::NewSQL::Std::get_records_where
1
1


1538
UUUUUUUUUUUUU

Call stack

The information below can help locate the source of the problem.

  • in WeBWorK::DB::Schema::NewSQL::Std::handle_error called at line 198 of /opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Merge.pm
  • in WeBWorK::DB::Schema::NewSQL::Merge::_get_fields_where_prepex called at line 380 of /opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm
  • in WeBWorK::DB::Schema::NewSQL::Std::get_fields_where called at line 440 of /opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm
  • in WeBWorK::DB::Schema::NewSQL::Std::get_records_where called at line 740 of /opt/webwork/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm
  • in WeBWorK::DB::Schema::NewSQL::Std::gets called at line 1477 of /opt/webwork/webwork2/lib/WeBWorK/DB.pm
  • in WeBWorK::DB::getMergedSets called at line 144 of /opt/webwork/webwork2/lib/WeBWorK/ContentGenerator/ProblemSets.pm
  • in WeBWorK::ContentGenerator::ProblemSets::body called at line 152 of /opt/webwork/webwork2/lib/WeBWorK/Template.pm
  • in WeBWorK::Template::template called at line 496 of /opt/webwork/webwork2/lib/WeBWorK/ContentGenerator.pm
  • in WeBWorK::ContentGenerator::content called at line 200 of /opt/webwork/webwork2/lib/WeBWorK/ContentGenerator.pm
  • in WeBWorK::ContentGenerator::go called at line 371 of /opt/webwork/webwork2/lib/WeBWorK.pm

Request information

Method GET
URI /webwork2/MAT150_modelCourse/
HTTP Headers
User-Agent Mozilla/5.0 (X11; Ubuntu; Linux i686; rv:14.0) Gecko/20100101 Firefox/14.0.1
Accept text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Connection keep-alive
Referer https://docralphv.collegeofidaho.edu/webwork2/?user=rcruz&effectiveUser=rcruz&key=NPQXXAv6eZxBmlc3Tsz7ObROjddpndcR
Accept-Encoding gzip, deflate
Cookie WeBWorKCourseAuthen.MAT112_02_W13=rcruz%09FSwNDXNRMlhxQ5aaCwJJOJH81UkZCYvP%091357353343; WeBWorKCourseAuthen.MAT112_01_W13=rcruz%093MwWkD3trceg2EEI1gYnXp8kupVFnSgz%091357356713; WeBWorKCourseAuthen.MAT123_01_W13=rcruz%09pAE2la0kk00l6ps0ApOLTxswn8IMCUMa%091357356740; WeBWorKCourseAuthen.TestCourse2DWR20130102=rcruz%0953ZfEn15egLYjSEwoEUK6brsPi7J0IU9%091357356951; WeBWorKCourseAuthen.MAT150_modelCourse=rcruz%09NPQXXAv6eZxBmlc3Tsz7ObROjddpndcR%091357358145; WeBWorKCourseAuthen.admin=rcruz%09maU7IgM5ApZK8IFcKC3puhQY7DP92y7n%091357358221; WeBWorKCourseAuthen.PHY271_modelCourse=rcruz%09Mx6JOILKxC1tI1745BQV9KWeM1NGQ1kt%091357357839; WeBWorKCourseAuthen.BIO316_01_W13=rcruz%09b3icvs3UEqbMHdCTaYG6lvyL6bQ4XWSA%091357358122; __utma=33027372.845525900.1247692761.1357353511.1357358289.506; __unam=3dc1e7a-13531c801c8-115a4e15-16; __utmz=33027372.1332300972.420.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); __utmc=33027372; __utmb=33027372.3.9.1357358293630
Accept-Language en-us,en;q=0.5
Host docralphv.collegeofidaho.edu

In reply to Dave Rosoff

Re: Upgrading to 2.5.1.1: database schema mismatch

by Michael Gage -
Did you update your file conf/database.conf from database.conf.dist? That might be the problem. There is a new field called flags used by the achievements module.
In reply to Michael Gage

Re: Upgrading to 2.5.1.1: database schema mismatch

by Robin Cruz -
I believe we have updated database.conf file in place. There wasn't anything we needed to change in it, was there?

The problem is with the courses that were built before the update. We've built a few test courses with the updated version of pg-dev and webwork2-dev and they seem to work fine. However, the admin course will not update the courses which were built before the update and they are the ones giving the errors I listed above.

I'm including the output from when I try "Update Course" on an pre-update courses below.

Thanks--rac
----------------------------------Output from trying to update an older course--------
Report for course MAT112_01_W13:


Database:

  • achievement Table is ok
  • achievement_user Table is ok
  • global_user_achievement Table is ok
  • key Table is ok
  • password Table is ok
  • permission Table is ok
  • problem Schema and database table definitions do not agree
    • Field problem_id => is ok
    • Field flags => missing in schema
    • Field max_attempts => is ok
    • Field value => is ok
    • Field source_file => is ok
    • Field set_id => is ok
  • problem_user Schema and database table definitions do not agree
    • Field problem_seed => is ok
    • Field sub_status => is ok
    • Field flags => missing in schema
    • Field max_attempts => is ok
    • Field status => is ok
    • Field value => is ok
    • Field last_answer => is ok
    • Field source_file => is ok
    • Field set_id => is ok
    • Field problem_id => is ok
    • Field num_incorrect => is ok
    • Field num_correct => is ok
    • Field attempted => is ok
    • Field user_id => is ok
  • set Schema and database table definitions do not agree
    • Field enable_reduced_scoring => is ok
    • Field version_time_limit => is ok
    • Field restrict_ip => is ok
    • Field set_header => is ok
    • Field hardcopy_header => is ok
    • Field version_creation_time => is ok
    • Field open_date => is ok
    • Field hide_work => is ok
    • Field hide_score => is ok
    • Field relax_restrict_ip => is ok
    • Field problem_randorder => is ok
    • Field versions_per_interval => is ok
    • Field version_last_attempt_time => is ok
    • Field visible => missing in schema
    • Field time_interval => is ok
    • Field set_id => is ok
    • Field problems_per_page => is ok
    • Field assignment_type => is ok
    • Field due_date => is ok
    • Field answer_date => is ok
    • Field time_limit_cap => is ok
    • Field attempts_per_version => is ok
    • Field hide_score_by_problem => is ok
    • Field restricted_login_proctor => is ok
  • set_locations Table is ok
  • set_locations_user Table is ok
  • set_user Schema and database table definitions do not agree
    • Field enable_reduced_scoring => is ok
    • Field version_time_limit => is ok
    • Field restrict_ip => is ok
    • Field set_header => is ok
    • Field psvn => is ok
    • Field hardcopy_header => is ok
    • Field version_creation_time => is ok
    • Field open_date => is ok
    • Field hide_work => is ok
    • Field hide_score => is ok
    • Field relax_restrict_ip => is ok
    • Field problem_randorder => is ok
    • Field version_last_attempt_time => is ok
    • Field versions_per_interval => is ok
    • Field visible => missing in schema
    • Field time_interval => is ok
    • Field set_id => is ok
    • Field problems_per_page => is ok
    • Field assignment_type => is ok
    • Field due_date => is ok
    • Field answer_date => is ok
    • Field time_limit_cap => is ok
    • Field user_id => is ok
    • Field attempts_per_version => is ok
    • Field hide_score_by_problem => is ok
    • Field restricted_login_proctor => is ok
  • setting Table is ok
  • user Table is ok

Database tables are ok

There are extra database fields which are not defined in the schema for at least one table. They can only be removed manually from the database.

Directory structure

  • DATA => /opt/webwork/courses/MAT112_01_W13/DATA rwx
  • achievements => /opt/webwork/courses/MAT112_01_W13/templates/achievements rwx
  • achievements_html => /opt/webwork/courses/MAT112_01_W13/html/achievements rwx
  • email => /opt/webwork/courses/MAT112_01_W13/templates/email rwx
  • html => /opt/webwork/courses/MAT112_01_W13/html rwx
  • html_images => /opt/webwork/courses/MAT112_01_W13/html/images rwx
  • html_temp => /var/www/wwtmp/MAT112_01_W13 rwx
  • logs => /opt/webwork/courses/MAT112_01_W13/logs rwx
  • macros => /opt/webwork/courses/MAT112_01_W13/templates/macros rwx
  • mailmerge => /opt/webwork/courses/MAT112_01_W13/DATA/mailmerge rwx
  • root => /opt/webwork/courses/MAT112_01_W13 rwx
  • scoring => /opt/webwork/courses/MAT112_01_W13/scoring rwx
  • templates => /opt/webwork/courses/MAT112_01_W13/templates rwx
  • tmpEditFileDir => /opt/webwork/courses/MAT112_01_W13/templates/tmpEdit rwx

Directory structure is ok

do_upgrade_course subroutine

 

In reply to Robin Cruz

Re: Upgrading to 2.5.1.1: database schema mismatch

by Michael Gage -
Check some of the files in DB/Record.   For example Set.pm should look like
ackage WeBWorK::DB::Record::Set;
use base WeBWorK::DB::Record;

=head1 NAME

WeBWorK::DB::Record::Set - represent a record from the set table.

=cut

use strict;
use warnings;

BEGIN {
	__PACKAGE__->_fields(
		set_id                    => { type=>"TINYBLOB NOT NULL", key=>1 },
		set_header                => { type=>"TEXT" },
		hardcopy_header           => { type=>"TEXT" },
		open_date                 => { type=>"BIGINT" },
		due_date                  => { type=>"BIGINT" },
		answer_date               => { type=>"BIGINT" },
		visible                   => { type=>"INT" },
		enable_reduced_scoring    => { type=>"INT" },
		assignment_type           => { type=>"TEXT" },
		attempts_per_version      => { type=>"INT" },
		time_interval             => { type=>"INT" },
		versions_per_interval     => { type=>"INT" },
		version_time_limit        => { type=>"INT" },
		version_creation_time     => { type=>"BIGINT" },
		problem_randorder         => { type=>"INT" },
		version_last_attempt_time => { type=>"BIGINT" },
		problems_per_page         => { type=>"INT" },
		hide_score                => { type=>"ENUM('N','Y','BeforeAnswerDate')" },
		hide_score_by_problem     => { type=>"ENUM('N','Y')" },
		hide_work                 => { type=>"ENUM('N','Y','BeforeAnswerDate')" },
		time_limit_cap            => { type=>"ENUM('0','1')" },
		restrict_ip               => { type=>"ENUM('No','RestrictTo','DenyFrom') DEFAULT 'No'" },
		relax_restrict_ip         => { type=>"ENUM('No','AfterAnswerDate','AfterVersionAnswerDate') DEFAULT 'No'" },
		restricted_login_proctor  => { type=>"ENUM('No','Yes')" },
	);
}

1;


and DB/Record/Problem.pm should have a flags entry in the hash table.

Still seems like something didn't get updated quite right.

-- Mike

In reply to Michael Gage

Re: Upgrading to 2.5.1.1: database schema mismatch

by Dave Rosoff -
Hi Mike,

Set.pm looks just like what you've pasted above. Problem.pm is indeed missing a flags entry. Here's the complete text of the file.

package WeBWorK::DB::Record::Problem;
use base WeBWorK::DB::Record;

=head1 NAME

WeBWorK::DB::Record::Problem - represent a record from the problem table.

=cut

use strict;
use warnings;

BEGIN {
        __PACKAGE__->_fields(
                set_id       => { type=>"TINYBLOB NOT NULL", key=>1 },
                problem_id   => { type=>"INT NOT NULL", key=>1 },
                source_file  => { type=>"TEXT" },
                value        => { type=>"INT" },
                max_attempts => { type=>"INT" },
        );
}

1;

Thanks for looking into this.
Dave

In reply to Dave Rosoff

Re: Upgrading to 2.5.1.1: database schema mismatch

by Dave Rosoff -
I'm starting to put this together -- sorry, I'm just new to this and trying to fill in the gaps in the discussion -- and it seems like I just have to add a few entries to a few hash tables. Is that really all there is to it?

Dave
In reply to Dave Rosoff

Re: Upgrading to 2.5.1.1: database schema mismatch

by Michael Gage -
What is worrying me is that this doesn't happen on my installation so I'm wondering if you have an incomplete download or if there is something corrupted on the GitHub site

Mike
In reply to Michael Gage

Re: Upgrading to 2.5.1.1: database schema mismatch

by Robin Cruz -
Do you have any suggestions for what we should try next? The courses built since the update seem to be working, but it would be nice to get the older ones back too.
--rac
In reply to Robin Cruz

Re: Upgrading to 2.5.1.1: database schema mismatch

by Michael Gage -
The flags field is in the ww2.5.2 version and is used by the essayQuestions that Geoff Goehle wrote.  Were you using essayQuestions in those problems?  

Even if you weren't those courses might have been created with an experimental version of WW that created fields for "flags".  I think you can just ignore the error -- the extra field for those courses won't hurt anything. When ww2.5.2 is ready for prime time those upgrade errors will go away.  

I'm not sure about the visible flag but I think that won't cause serious errors either. All that might go wrong is that some homework sets that used to be visible will have the visibility box unchecked and the visibility checkbox has to be checked again.

The comparison between what's in the database and what ww2.5.1.1 is expecting can be fixed by doctoring the databases for the old courses -- I think you'll find they have extra fields for flags and visible that the current courses don't have.  -- but it's probably not worth the effort of fixing these tables by hand.

-- Mike