Moodle in English: Cannot add an instance of choice

Hello,
I've recently installed Moodle and have had few problems. However, when I try to add a Choice resource to a course topic, I get the following error: "Could not add a new instance of choice"

I'm using the following:

Linux - Suse Linux Enterprise Server v.9
Moodle - v. 1.5.3+
MySQL - v.5.0.18-standard
PHP - v.4.3.4
Apache - v.2.0.49

I've read the posting Bug #3768 - Could not add a new instance of choice with MySQL 5.0.7b - 5.0.9b but that seems to deal with a different version of MySQL. Furthermore, I'm not very skilled in using MySQL as this is my first encounter with it. Please help.

Thanks,
Corey

Average of ratings: -

Re: Cannot add an instance of choice

by Dan Marsden - Friday, 10 February 2006, 8:07 AM

Hi Corey,

the bug you linked to is the right one! - it deals with all versions of Mysql 5+ unfortunately! - the fix is in the HEAD development release of Moodle, it will be in the next version 1.6 -flick me an e-mail if you're interested in a patch for 1.5.3 - I've got a version we've been running in our install.

Dan

Average of ratings: -

Re: Cannot add an instance of choice

by Robert Brenstein - Friday, 10 February 2006, 9:02 PM

Could you just post this patch here or with the bug entry? Not everyone will switch to 1.6 right away.

Average of ratings: -

Re: Cannot add an instance of choice

by Anna Vanova - Friday, 17 February 2006, 1:14 AM

I would also appreciate if you posted it. We have troubles with choice module since database server upgrade and we won't switch to new moodle right away either. Thanks.

Average of ratings: -

Re: Cannot add an instance of choice

by Dan Marsden - Friday, 17 February 2006, 3:29 AM

hmmmmm... I'm reluctantly posting this. - it is not a "clean" patch, although we are running this in a production environment. - it is likely that small patches applied in 1.5.3 aren't present in these files.

To use these files, follow these instructions
take a copy of the files in your moodle install located here:
moodle\mod\choice
save these files to disk for later in case you want to replace the changed files.

replace this choice directory with the files in the attached zip file.

you will need to manually rename a field in your database - find the mdl_choice table and rename the field 'release' to showresults - use this if you are using the command line:

ALTER TABLE mdl_choice CHANGE `release` showresults tinyint(2)
unsigned not null default '0';

you must make sure you have deleted the old release field (don't copy it, make sure you rename it) or it will likely cause problems with backup/restore.

If you use this patch, and later on upgrade to 1.6 you will possibly receive an error from the choice upgrade where it trys to rename the database field from release to showresults. - this error can be ignored.

thanks,

Dan

choice.zip

Average of ratings: Useful (1)

Re: Cannot add an instance of choice

by Marco Bro - Friday, 24 February 2006, 11:49 PM

FINALLY!

Thank you really much for your fix, now choices works perfectly.

Marco

Average of ratings: -

Re: Cannot add an instance of choice

by Andreas Petrou - Thursday, 2 March 2006, 3:35 AM

hi dan,

i´ve also the named problem and tried to renew my sql-database. i´m not very fit in database-handling, so i´ve some beginner-questions.
when i open my phpmysql i´ve got several options and can´t align them with your introduction.
am i right, when the first step is to delete the field "release"? or have i just to rename it to "showresults"?
is the second step to create a new field with the command ALTER TABLE mdl_choice CHANGE `release` showresults tinyint(2)
unsigned not null default '0'; or is this just the command to rename the original field?
i was a little confused because of your addition: you must make sure you have deleted the old release field (don't copy it, make sure you rename it) ...

thanks for your support

andy

Average of ratings: -

Re: Cannot add an instance of choice

by Dan Marsden - Thursday, 2 March 2006, 4:43 PM

Hi Andy,

if you use the sql command, you'll be fine! - it renames the field (this way you don't lose any previous data!)

If someone just added a column "showresults" instead of renaming the column using the command I list, or by renaming using a different method, the choice backup/restore will end up being corrupt.

good luck!

Dan

Average of ratings: -

Re: Cannot add an instance of choice

by Andreas Petrou - Thursday, 2 March 2006, 6:49 PM

hi dan,

thank for the speedy reply.
one more question: i could magage my database via "phpmyadmin". there i could rename the field directly (- i hope i could do so wink

). so it´ll work work this way or ist the command obligatory?

thank again for supporting

greetings andy

Average of ratings: -

Re: Cannot add an instance of choice

by Dan Marsden - Monday, 6 March 2006, 4:37 AM

yes - you can rename your field via phpmyadmin instead of running the command.

Dan

Average of ratings: -

Re: Cannot add an instance of choice

by Andreas Petrou - Monday, 6 March 2006, 11:10 PM

hi dan,

i did it!! ... and it works!! thanks a lot approve

andy

Average of ratings: -

Re: Cannot add an instance of choice

by Graeme Byrne - Tuesday, 11 April 2006, 11:40 PM

Hi,
After you have applied Dans script and If you are using Moodle 1.5.* and are attempting to restore a course, you may very well see the course creation just stop when you try a restore.

There is a function being called in Dans restorelib.php (restore_userdata_selected) that doesnt exist in Moodle 1.5 if you replace that if statement with the code below it should import ok.

//now restore the answers for this choice.
//if (restore_userdata_selected($restore,'choice',$mod->id)) {

if ($restore->mods['choice']->userinfo) {
//Restore choice_answers
$status = choice_answers_restore_mods($newid,$info,$restore);
}

Average of ratings: -

Re: Cannot add an instance of choice

by Dan Marsden - Wednesday, 12 April 2006, 5:08 AM

good spotting graeme! - thanks for the details!

Dan

Average of ratings: -

Re: Cannot add an instance of choice

by Eric Schewe - Thursday, 20 April 2006, 3:50 AM

Does this apply if you upgrade to the latest release of the Choice Mod. instead of using the above posted fixed? I upgraded to the latest version of the Choice Mod and now backups appear to be failing at the Choice Mod. every night.

Average of ratings: -

Re: Cannot add an instance of choice

by Dan Marsden - Thursday, 20 April 2006, 4:27 AM

Hi Eric,

yes - the latest version of the choice mod uses some calls to some backup/restore functions that don't exist in 1.5

good luck

Dan

Average of ratings: -

Re: Cannot add an instance of choice

by Eric Schewe - Thursday, 20 April 2006, 4:51 AM

Is there any way to resolve this without upgrading to Moodle 1.6?

Average of ratings: -

Re: Cannot add an instance of choice

by Dan Marsden - Thursday, 20 April 2006, 5:42 AM

follow Graemes instructions above - that might fix the issue. otherwise have a look at this link:
http://cvs.sourceforge.net/viewcvs.py/moodle/moodle/mod/choice/restorelib.php?tr1=1.19&tr2=1.22&r1=text&r2=text&diff_format=l

the changes you want to revert are the ones below lines 104/108

- as graeme mentions above, you probably only need to alter this line though:
if (restore_userdata_selected($restore,'choice',$mod->id)) {

to this:
if ($restore->mods['choice']->userinfo) {

hope that helps!

thanks,

Dan

Average of ratings: -

Re: Cannot add an instance of choice

by Eric Schewe - Friday, 21 April 2006, 2:27 AM

The error isn't in restores yet. Backups won't complete either.

Redhat Enterprise Linux AS 4
PHP 5.0.5
Mysql 5.0.18
Moodle 1.5.2
Choice Module 2006020900

Error: Fatal error: Call to undefined function: backup_mod_selected() in /data/sites/moodle/mod/choice/backuplib.php on line 39

Average of ratings: -

Re: Cannot add an instance of choice

by Dan Marsden - Friday, 21 April 2006, 7:42 AM

Hi Eric, try replacing your backuplib with this one.

(be careful - I haven't tested this one very much!)

thanks,

Dan

backuplib.php

Average of ratings: -

Re: Cannot add an instance of choice

by Graeme Byrne - Friday, 21 April 2006, 5:07 PM

Eric,
If your Moodle Version is 1.5.2 the backup_mod_selected() function does not exist (lives in backup/backuplib.php in moodle 1.6) so you will need to replace the body of the foreach on line : 36 - 66

with
//Start mod
                fwrite ($bf,start_tag("MOD",3,true));
                //Print choice data
                fwrite ($bf,full_tag("ID",4,false,$choice->id));
                fwrite ($bf,full_tag("MODTYPE",4,false,"choice"));
                fwrite ($bf,full_tag("NAME",4,false,$choice->name));
                fwrite ($bf,full_tag("TEXT",4,false,$choice->text));
                fwrite ($bf,full_tag("FORMAT",4,false,$choice->format));
                fwrite ($bf,full_tag("PUBLISH",4,false,$choice->publish));
                fwrite ($bf,full_tag("RELEASE",4,false,$choice->release));
                fwrite ($bf,full_tag("DISPLAY",4,false,$choice->display));
                fwrite ($bf,full_tag("ALLOWUPDATE",4,false,$choice->allowupdate));
                fwrite ($bf,full_tag("SHOWUNANSWERED",4,false,$choice->showunanswered));
                fwrite ($bf,full_tag("LIMITANSWERS",4,false,$choice->limitanswers));
                fwrite ($bf,full_tag("TIMEOPEN",4,false,$choice->timeopen));
                fwrite ($bf,full_tag("TIMECLOSE",4,false,$choice->timeclose));
                fwrite ($bf,full_tag("TIMEMODIFIED",4,false,$choice->timemodified));

                //Now backup choice_options
                $status = backup_choice_options($bf,$preferences,$choice->id);

                //if we've selected to backup users info, then execute backup_choice_answers
                if ($preferences->mods["choice"]->userinfo && $status) {
                    $status = backup_choice_answers($bf,$preferences,$choice->id);
                }
                //End mod
                $status =fwrite ($bf,end_tag("MOD",3,true));

This change is pushing the backup and restore proceedures back to a 1.5.2 state and may not be fully functional with choice for 1.6.

Some of the tags may have changed with the new choice module these changes should be straightworward but this should get you backing up and restoring choices.

let me know how you get on
Graeme

Average of ratings: -

Re: Cannot add an instance of choice

by Anna Vanova - Thursday, 2 March 2006, 4:06 PM

You are not the one who should say thanks : ) I thank you. Bye.

Average of ratings: -

Re: Cannot add an instance of choice

by Ron Brown - Saturday, 1 April 2006, 6:05 AM

Thank you so much for this fix, works perfectly.

Average of ratings: -

Απάντηση: Re: Cannot add an instance of choice

by Αθανάσιος Ψωρομύτης - Saturday, 6 May 2006, 9:40 PM

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id)) TYPE=MyISAM COMMENT='strings used in Hot Potatatoes questio

Error

Hi ! what should i do to resolve the above error???

Average of ratings: -

Re: Cannot add an instance of choice

by Dan Marsden - Wednesday, 10 May 2006, 4:51 AM

hi there,

sounds like you have some issues with the hot potatoes module - this forum is for the choice module within Moodle. - try looking at the hot potatoes forum there might be someone there with teh same issue. - one possible problem could be that you might be using mysql 5+ which isn't supported by Moodle 1.5 very well. - Moodle 1.6 (soon to be released) should support mysql 5 much better.

thanks,

Dan

Average of ratings: -

Re: Cannot add an instance of choice

by James Dugal - Thursday, 1 June 2006, 3:47 AM

Dan, I found a misspelling in restorelib.php, in both your choice.zip and in the
1.6beta5 sources, that would prevent proper restoration of the showresults field.
Around line 63, I believe SHOWANSWERS should be instead SHOWRESULTS.
I submitted bug #5662 for this.
-- James

Average of ratings: -

Re: Cannot add an instance of choice

by Dan Marsden - Thursday, 1 June 2006, 4:33 AM

Hi James,

thanks for the bug report! - I have fixed it in HEAD and 1.6STABLE - it may take a wee while for the public cvs and downloads to update with the patch.

Dan

Average of ratings: -

Re: Cannot add an instance of choice

by Greg LeePow - Friday, 2 June 2006, 5:29 AM

Hi Dan
Did I do something wrong applying this patch? I added the choice directory you posted. I renamed the table release to showresults. now i get the following errors when adding or changing the site also if a new user register he or she gets a blank page but the user is added to the list.

Copy of choice

(mysql): CREATE TABLE mdl_choice ( id int(10) unsigned NOT NULL auto_increment, course int(10) unsigned NOT NULL default '0', name varchar(255) NOT NULL default '', text text NOT NULL, format tinyint(2) unsigned NOT NULL default '0', publish tinyint(2) unsigned NOT NULL default '0', `release` tinyint(2) unsigned NOT NULL default '0', display tinyint(4) unsigned NOT NULL default '0', allowupdate tinyint(2) unsigned NOT NULL default '0', showunanswered tinyint(2) unsigned NOT NULL default '0', limitanswers tinyint(2) unsigned NOT NULL default '0', timeopen int(10) unsigned NOT NULL default '0', timeclose int(10) unsigned NOT NULL default '0', timemodified int(10) unsigned NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY id (id), KEY course (course)) TYPE=MyISAM COMMENT='Available choices are stored here.'

1050: Table 'mdl_choice' already exists

       ADOConnection._Execute(CREATE TABLE mdl_choice ( id int(10) unsigned NOT NULL auto_increment, course int(10) unsigned NOT NULL default '0', name var..., false) % line 854, file: adodb.inc.php

     ADOConnection.Execute(CREATE TABLE mdl_choice ( id int(10) unsigned NOT NULL auto_increment, course int(10) unsigned NOT NULL default '0', name var...) % line 55, file: datalib.php

   execute_sql(CREATE TABLE mdl_choice ( id int(10) unsigned NOT NULL auto_increment, course int(10) unsigned NOT NULL default '0', name var...) % line 199, file: datalib.php

modify_database(E:\Programs\Apache2\htdocs\CoP\data/mod/Copy of choice/db/mysql.sql) % line 205, file: adminlib.php

upgrade_activity_modules(http://community.serve.org//admin/index.php) % line 243, file: index.php

Error

(mysql): CREATE TABLE mdl_choice_answers ( id int(10) unsigned NOT NULL auto_increment, choiceid int(10) unsigned NOT NULL default '0', userid int(10) unsigned NOT NULL default '0', optionid int(10) NOT NULL default '0', timemodified int(10) NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY id (id), KEY userid (userid), KEY choiceid (choiceid)) TYPE=MyISAM

1050: Table 'mdl_choice_answers' already exists

       ADOConnection._Execute(CREATE TABLE mdl_choice_answers ( id int(10) unsigned NOT NULL auto_increment, choiceid int(10) unsigned NOT NULL default '0',..., false) % line 854, file: adodb.inc.php

     ADOConnection.Execute(CREATE TABLE mdl_choice_answers ( id int(10) unsigned NOT NULL auto_increment, choiceid int(10) unsigned NOT NULL default '0',...) % line 55, file: datalib.php

   execute_sql(CREATE TABLE mdl_choice_answers ( id int(10) unsigned NOT NULL auto_increment, choiceid int(10) unsigned NOT NULL default '0',...) % line 199, file: datalib.php

modify_database(E:\Programs\Apache2\htdocs\CoP\data/mod/Copy of choice/db/mysql.sql) % line 205, file: adminlib.php

upgrade_activity_modules(http://community.serve.org//admin/index.php) % line 243, file: index.php

Error

(mysql): CREATE TABLE mdl_choice_options ( id int(10) unsigned NOT NULL auto_increment, choiceid int(10) unsigned NOT NULL default '0', `text` TEXT, maxanswers int(10) unsigned NULL default '0', timemodified int(10) NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY id (id), KEY choiceid (choiceid)) TYPE=MyISAM

1050: Table 'mdl_choice_options' already exists

       ADOConnection._Execute(CREATE TABLE mdl_choice_options ( id int(10) unsigned NOT NULL auto_increment, choiceid int(10) unsigned NOT NULL default '0',..., false) % line 854, file: adodb.inc.php

     ADOConnection.Execute(CREATE TABLE mdl_choice_options ( id int(10) unsigned NOT NULL auto_increment, choiceid int(10) unsigned NOT NULL default '0',...) % line 55, file: datalib.php

   execute_sql(CREATE TABLE mdl_choice_options ( id int(10) unsigned NOT NULL auto_increment, choiceid int(10) unsigned NOT NULL default '0',...) % line 199, file: datalib.php

modify_database(E:\Programs\Apache2\htdocs\CoP\data/mod/Copy of choice/db/mysql.sql) % line 205, file: adminlib.php

upgrade_activity_modules(http://community.serve.org//admin/index.php) % line 243, file: index.php

Error

(mysql): INSERT INTO mdl_log_display VALUES ('choice', 'view', 'choice', 'name')

Success

(mysql): INSERT INTO mdl_log_display VALUES ('choice', 'update', 'choice', 'name')

Success

Average of ratings: -

Re: Cannot add an instance of choice

by Dan Marsden - Friday, 2 June 2006, 12:27 PM

Hi Greg,

you should have replaced the files in the existing Choice directory, not add a new folder called "copy of choice" - do not delete this module using the standard Admin Modules page, you will screw up your site even further. - it will delete ALL previously created Choices in all your courses.

you will need to delete the new directory you have created manually and then delete the new record in one of the modules tables that lists "copy of choice"

If you don't feel comfortable doing this - in the admin\modules page, hide the new module "copy of choice" and replace the exisiting choice module files with those posted above. -

If you are happy with losing all choice's in all courses on your site, try this:
in the admin/modules page, - delete the copy of choice and the choice modules, and then remove the new directory you created earlier - then visit the admin page again and it will install the choice correctly!

Otherwise I suggest you leave this until 1.6 is released and upgrade to 1.6

thanks,

Dan

Average of ratings: -

Re: Cannot add an instance of choice

by Greg LeePow - Friday, 2 June 2006, 11:20 PM

Thanks Deleting copy of choices worked.
I don't know if this is the area for this but the second question is the white page after a user register. If a user register after entering the registration information and submitting , the user is presented with a whit page wit no links to continue or next steps. Any idea , again thanks.

Average of ratings: -

Re: Cannot add an instance of choice

by Dan Marsden - Tuesday, 6 June 2006, 4:11 AM

HI Greg,

my guess is that you don't have a clean set of Moodle files. - I suggest you download the latest 1.5 stable version and replace your files with those - then add the patches like the choice module one.

good luck!

Dan

Average of ratings: -

Re: Cannot add an instance of choice

by Juan Fidel Cornejo - Wednesday, 19 July 2006, 12:47 PM

Hi Dan!!!!!

Thanks for your help.

Average of ratings: -

Re: Cannot add an instance of choice

by Huw Smith - Tuesday, 3 October 2006, 11:13 PM

I hope someone can help with this.

When I type in the command I get the following error message:

mysql> ALTER TABLE mdl_choice CHANGE 'release' showresults tinyint(2) unsigned not null default '0';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''release' showresults tinyint(2) unsigned not null default '0'' at line 1

I'm stuck now, I've done a little searching and discovered that release is a reserved word in mysql now so I am assuming it is something to do with that.

Any advice would be greatly appreciated.

Average of ratings: -

Re: Cannot add an instance of choice

by Matt Campbell - Tuesday, 10 October 2006, 11:05 PM

As noted above, the proper command is:

ALTER TABLE mdl_choice CHANGE `release` showresults tinyint(2) unsigned not null default '0';

Fields should be ecapsulated by a `, not '. You'll find the ` in the upper left of your standard US keyboard (not sure if the UK is different....)

Thanks,
Matt

Average of ratings: -

Re: Cannot add an instance of choice

by Huw Smith - Thursday, 9 November 2006, 11:47 PM

I can change the name of the field in mdl_choice, that's worked fine.

The problem I have now is that when I try to go to the admin page i get the following message:

Version mismatch: choice can't downgrade 2005041501 -> 2005041500 !

Any sugestions?

Average of ratings: -

Re: Cannot add an instance of choice

by J Handler - Friday, 16 February 2007, 7:18 AM

Thank you soo much, this fixed it in 5 minutes.

Average of ratings: -