Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 1.6.1
-
Fix Version/s: None
-
Component/s: Quiz
-
Labels:None
-
Environment:All
-
Database:MySQL
-
Affected Branches:MOODLE_16_STABLE
Description
I don't know if anyone else has found this (several bug tracker searches came up empty) but we've recently encountered a problem trying to create quizzes in Moodle 1.6.1 using MySQL 5.0.22 - adding a quiz fails with the error Could not add a new instance of quiz.
The error is caused by insert_record in quiz_add_instance failing with the sql error: Field 'questions' doesn't have a default value
This error seems reasonable enough, 'questions' doesn't have a default value (and since it is a text type it can't have a default value) and there is no value supplied for the questions field in the insert query.
A possible fix could be to use
$quiz->questions = '';
in quiz_add_instance?
Interestingly
it looks like the query only fails with certain settings of the sql_mode [1] server variable. On the server where the error happens sql_mode has the value
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
it's the STRICT_TRANS_TABLES setting that triggers the error. I'm guessing this is the default value (new in MySQL 5?) since it's unlikely our IT guys would've done any configuration work on MySQL beyond a simply installing it 
[1] http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
Activity
- All
- Comments
- History
- Activity
- Source
- Test Sessions
From Tim Hunt (T.J.Hunt at open.ac.uk) Thursday, 3 August 2006, 09:07 PM:
If you look in mod/quiz/db/mysql.sql, you will see that the questions column in the quiz table should be created with default ''.
So the mystery is, why does your database not know about this default?
From (andrew.walker at altoncollege.ac.uk) Thursday, 3 August 2006, 09:15 PM:
according to the mysql docs TEXT and BLOB types aren't allowed to have a default value
http://dev.mysql.com/doc/refman/5.0/en/blob.html
From Tim Hunt (T.J.Hunt at open.ac.uk) Friday, 4 August 2006, 12:32 AM:
What a lame excuse for a DB MySQL is. Why can't everyone just use a real database like Postgres?
Anyway, fix checked in to MOODLE_1.6_STABLE and HEAD.
Thank you very much for finding and diagnosing the problem.
From (andrew.walker at altoncollege.ac.uk) Friday, 4 August 2006, 07:34 PM:
No problem - thanks for fixing it
I have to admit it was news to me that TEXT couldn't have a default too. I'm struggling to think of a reason as to why they would make it work like that...
One thing I did notice is that a quick regexp search of the .sql files moodle 1.6.1 finds quite a number of places that setup tables using a default value for a column with the text type - I guess that's something for a new bug report.
Thanks again.