Upgrading from 1.5.2 to 1.7 database errors and a loop

Upgrading from 1.5.2 to 1.7 database errors and a loop

by Heather P -
Number of replies: 11
Hi
I am trying to upgrade from Moodle 1.5.2 to 1.7 (downloaded on the 17th January -latest build). I am using Windows XP Apache 2.052.
I have upgraded to php 5.1.2 and MySQL 5.0.27 - I did test Moodle after each of these two upgrades and all seemed to be well, but I could have missed something.
I installed Moodle copied back my config file and my additional block and modules (not sure what to do about the lang files as 1.7 seems to have moved all the lang to en_utf8 and all mine were in en, but I'll worry about that later).
The database upgraded with errors (about 21) similar at first glance to this thread http://moodle.org/mod/forum/discuss.php?d=62801. Then it got stuck in a loop trying to update the module tables with messages similar to this thread http://moodle.org/mod/forum/discuss.php?d=62646

This is the opening salvo of errors on the database upgrade
(mysql): DELETE FROM mdl_modules WHERE name = 'dialogue'
The Dialogue module has been discontinued and removed from your site. You weren't using it anyway. winkScroll to next warning


(mysql): CREATE TABLE mdl_post ( `id` int(10) unsigned NOT NULL auto_increment, `userid` int(10) unsigned NOT NULL default '0', `courseid` int(10) unsigned NOT NULL default'0', `groupid` int(10) unsigned NOT NULL default'0', `moduleid` int(10) unsigned NOT NULL default'0', `coursemoduleid` int(10) unsigned NOT NULL default'0', `subject` varchar(128) NOT NULL default '', `summary` longtext, `content` longtext, `uniquehash` varchar(128) NOT NULL default '', `rating` int(10) unsigned NOT NULL default'0', `format` int(10) unsigned NOT NULL default'0', `publishstate` enum('draft','site','public') NOT NULL default 'draft', `lastmodified` int(10) unsigned NOT NULL default '0', `created` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `id_user_idx` (`id`, `userid`), KEY `post_lastmodified_idx` (`lastmodified`), KEY `post_subject_idx` (`subject`) ) TYPE=MyISAM COMMENT='New moodle post table. Holds data posts such as forum entries or blog entries.' 1105: Too long comment for table 'mdl_post'

ADOConnection._Execute(CREATE TABLE mdl_post (
`id` int(10) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL de..., false) % line 889, file: adodb.inc.php
ADOConnection.Execute(CREATE TABLE mdl_post (
`id` int(10) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL de...) % line 71, file: dmllib.php
execute_sql(CREATE TABLE mdl_post (
`id` int(10) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL de...) % line 215, file: dmllib.php
modify_database(, CREATE TABLE prefix_post (
`id` int(10) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL...) % line 1694, file: mysql.php
main_upgrade(2005060220) % line 252, file: index.php

Scroll to previous warningErrorScroll to next warning

Then it goes on to
(mysql): ALTER TABLE mdl_post ADD module VARCHAR(20) DEFAULT '' not null AFTER `id` 1146: Table 'pinemoo.mdl_post' doesn't exist

I have all the errors about the comments being too long too, but I'm more worried about the fact that it keeps telling me whole tables don't exist.

I did see a thread mentioning about case sensitivity on the table prefix and wondered it that might have something to do with it as when I first logged into Moodle it told me that the lowercase c in the config file should be a capital C.

I'll start again and see what happens, but if anyone can suggest anything, I'd be delighted to hear as I think I have information overload. I don't really want to have to tinker with the code in the lib file for the comments too long as suggested by one of the threads if I don't have to as I have multiple installs of moodle to upgrade.

Many thanks
Heather



Average of ratings: -
In reply to Heather P

Re: Upgrading from 1.5.2 to 1.7 database errors and a loop

by Gordon Bateson -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Hi Heather,
I have seen this problem before and there is a solution posted on the following thread:
http://moodle.org/mod/forum/discuss.php?d=58264

Don't worry too much at this point about the messages reporting that certain tables "don't exist". The reason they don't exist is because they could not be created, which in turn is because the comments were too long. Once you fix the comments, the tables will be added OK and the "don't exist" messages will disappear too.

After you have made the fix suggested in the above post, you will need to remove all tables from the Moodle database before trying the install again.

good luck
Gordon

P.S. Here are some other people with the same error message:

Average of ratings: -
In reply to Gordon Bateson

Re: Upgrading from 1.5.2 to 1.7 database errors and a loop

by Heather P -
Thanks.
I've now carried out the items in the thread you suggested and I do think I have made progress but I still have 12 warnings and still get stuck in a loop at the end trying to update the quiz and hotpot modules.
I think all the can't add tables because the comments are too long have gone but I do still have can't alter tables because table 'x' doesn't exist (where x is changeable) e.g.

(mysql): ALTER TABLE `mdl_role` ADD INDEX `sortorder` (`sortorder`)

1146: Table 'pinemoo.mdl_role' doesn't exist

  ADOConnection._Execute(ALTER TABLE `mdl_role` ADD INDEX `sortorder` (`sortorder`), false) % line 889, file: adodb.inc.php

ADOConnection.Execute(ALTER TABLE `mdl_role` ADD INDEX `sortorder` (`sortorder`)) % line 71, file: dmllib.php

execute_sql(ALTER TABLE `mdl_role` ADD INDEX `sortorder` (`sortorder`), true) % line 2065, file: mysql.php

main_upgrade(2005060220) % line 252, file: index.php

I do have a lot of errors like this though

1101: BLOB/TEXT column 'data' can't have a default value

    ADOConnection._Execute(ALTER TABLE mdl_sessions CHANGE data data mediumtext DEFAULT '' not null , false) % line 889, file: adodb.inc.php

  ADOConnection.Execute(ALTER TABLE mdl_sessions CHANGE data data mediumtext DEFAULT '' not null ) % line 71, file: dmllib.php

execute_sql(ALTER TABLE mdl_sessions CHANGE data data mediumtext DEFAULT '' not null ) % line 127, file: ddllib.php

table_column(sessions, data, data, mediumtext, , , , not null) % line 1940, file: mysql.php

main_upgrade(2005060220) % line 252, file: index.php

which is somewhat akin to the item on the other thread which basically amounted to removing default from the 'sessdata' definition in the moodle/lib/db/install.xml file
My mySQL knowledge (and xml for that matter) isn't as good as it could be so I'm now wondering if I'm OK to go through and remove the default option from not only the 'sessdata' type but also'data' as in the above blob thing and then 'formattedtext' , 'description' 'text', 'submissioncomment', 'entrycomment', 'name', 'intro', 'conditions', 'sequence', 'answer1', 'summary' as they all appear in 1101 : BLOB/TEXT errors.

I've also just spotted one were I can't create a table because it already exists.

(mysql): CREATE TABLE `mdl_quiz_essay` ( `id` int(10) unsigned NOT NULL auto_increment, `question` int(10) unsigned NOT NULL default '0', `answer` varchar(255) NOT NULL default '', PRIMARY KEY (`id`), KEY `question` (`question`) ) TYPE=MyISAM COMMENT=''

1050: Table 'mdl_quiz_essay' already exists

      ADOConnection._Execute(CREATE TABLE `mdl_quiz_essay` (

 `id` int(10) unsigned NOT NULL auto_increment,

 `question` int(10..., false) % line 889, file: adodb.inc.php

    ADOConnection.Execute(CREATE TABLE `mdl_quiz_essay` (

 `id` int(10) unsigned NOT NULL auto_increment,

 `question` int(10...) % line 71, file: dmllib.php

  execute_sql(CREATE TABLE `mdl_quiz_essay` (

 `id` int(10) unsigned NOT NULL auto_increment,

 `question` int(10...) % line 215, file: dmllib.php

modify_database(,

 CREATE TABLE `prefix_quiz_essay` (

 `id` int(10) unsigned NOT NULL auto_increment,

 `...) % line 862, file: mysql.php

quiz_upgrade(2005060301, Object:stdClass) % line 274, file: adminlib.php

which may explain why the when it gets to altering the quiz module it gets stuck in a loop, as the table might not be what it was expecting.
Any advice gratefully accepted.
Heather
Average of ratings: -
In reply to Heather P

Re: Upgrading from 1.5.2 to 1.7 database errors and a loop

by Patrik Nilsson -

I had the same problem with the quiz module but I looked in \mod\quiz\version.php

$module->version = 2005060302; // The (date) version of this module 2006091901

$module->requires = 2006080900; // Requires this Moodle version

The first row said 2006091901 that was after the required date in second row. so I changed version to 2005060302 that is the date the error message talked about and then it continued the update.

Average of ratings: -
In reply to Heather P

Re: Upgrading from 1.5.2 to 1.7 database errors and a loop

by Ken Wilson -

Heather

If you don't want to change the code, try turning strict mode off in mysql. Use the mysql client or phpmyadmin and run this command:

mysql>SET @@global.sql_mode='';

See MDL-7450 for the background. As Gordon suggests, it's best to drop the database and start again - those errors should not recurr.

Ken

Average of ratings: -
In reply to Ken Wilson

Re: Upgrading from 1.5.2 to 1.7 database errors and a loop

by Heather P -
Well I'm stunned!
I've just tried what you suggest about the strict mode in MySQL- I do have the two lib hacks in place from the other thread (to get over the comments being too long and the 'sessdata' not being default) and hey presto I now have a Moodle1.7 sat looking at me. It appears to be working (haven't had chance to check every feature yet), not stuck in a loop etc.

I am now mildly concerned that I'm just stocking up trouble for the future - is that likely do you think?
Many thanks
Heather
Average of ratings: -
In reply to Heather P

Re: Upgrading from 1.5.2 to 1.7 database errors and a loop

by Ken Wilson -

Heather

We've always run with strict mode off and have not had any problems (see http://docs.moodle.org/en/Installing_Moodle#Requirements).

It might be a good idea to keep an eye on the tracker for the bugs which have been filed re the strict mode change for mysql >= 5.0.25 (MDL-7450, MDL-7765, MDL-7994). I'm told that patience is a virtue smile - so once these are fixed, it should be fine to go back to the default setting.

Ken

Average of ratings: -
In reply to Ken Wilson

Re: Upgrading from 1.5.2 to 1.7 database errors and a loop

by Heather P -
Many thanks for the help and information. It is always reassuring knowing that someone else has it set up in a similar fashion.
All I need to do now is make sure everything is working especially quizzes as I've seen so many posts on those, get a grip on this new role thing and then we are ready to roll!
Again, many thanks for your help.
I have to say once you have a moodle already installed and running and you come to upgrade you tend to miss the odd bit of info found in the install information, not the upgrade info - like the bit about strict mode off in mysql. I must put myself a reminder for next time we have to upgrade to re-read a basic install before proceeding to the upgrade.
Cheers
Heather
Average of ratings: -
In reply to Ken Wilson

Re: Upgrading from 1.5.2 to 1.7 database errors and a loop

by Mahendran Balakrishnan -
Hi there bro, Ken, how to turn it off thru phpmyadmin??

i cant find any link, i dunt have access to msqld ,or ssh, to the host
Average of ratings: -
In reply to Mahendran Balakrishnan

Re: Upgrading from 1.5.2 to 1.7 database errors and a loop

by Ken Wilson -

Hi Mahendran

Try clicking on the SQL tab and then entering the command from there. If the command is rejected (some webhosts may not allow setting of global variables), try Gordon's changes to the sql files.

Good luck!

Ken

Average of ratings: -
In reply to Heather P

Re: Upgrading from 1.5.2 to 1.7 database errors and a loop

by omer vural -
I upgraded my Moodle from 1.5 to 1.7 and after installation was completed, i get following errors. I am a new user of the moodle and have tried to fix those errors. Do you have any suggestion to come over of the problems. If i set up my moodle again, do i surmount the mistakes?

Warning: print_recent_activity(/Library/WebServer/Documents/moodle/mod/exam/lib.php): failed to open stream: No such file or directory in /Library/WebServer/Documents/moodle/course/lib.php on line 896

Warning: print_recent_activity(): Failed opening '/Library/WebServer/Documents/moodle/mod/exam/lib.php' for inclusion (include_path='.:/Library/WebServer/Documents/php/includes') in /Library/WebServer/Documents/moodle/course/lib.php on line 896

Warning: print_recent_activity(/Library/WebServer/Documents/moodle/mod/homework/lib.php): failed to open stream: No such file or directory in /Library/WebServer/Documents/moodle/course/lib.php on line 896

Warning: print_recent_activity(): Failed opening '/Library/WebServer/Documents/moodle/mod/homework/lib.php' for inclusion (include_path='.:/Library/WebServer/Documents/php/includes') in /Library/WebServer/Documents/moodle/course/lib.php on line 896

Warning: print_recent_activity(/Library/WebServer/Documents/moodle/mod/questionnaire/lib.php): failed to open stream: No such file or directory in /Library/WebServer/Documents/moodle/course/lib.php on line 896

Warning: print_recent_activity(): Failed opening '/Library/WebServer/Documents/moodle/mod/questionnaire/lib.php' for inclusion (include_path='.:/Library/WebServer/Documents/php/includes') in /Library/WebServer/Documents/moodle/course/lib.php on line 896

Nothing new since your last login

Average of ratings: -
In reply to omer vural

Re: Upgrading from 1.5.2 to 1.7 database errors and a loop

by Mauno Korpelainen -

Hi Omer,

did you miss my previous answer http://moodle.org/mod/forum/discuss.php?d=62428

You have installed modules "exam", "homework" and "questionaire" to moodle 1.5 and those are not standard modules of moodle 1.7

If they can't be upgraded to moodle 1.7 the only way is to come back to moodle 1.5 - 1.6 or delete those modules from mod folder and database and upgrade to moodle 1.7.

Average of ratings: -