V2.3 and AIKEN Question Import - Error writing to Database

V2.3 and AIKEN Question Import - Error writing to Database

by Paul Caliban -
Number of replies: 13

What is the mystery format for a VALID AIKEN question import file?

I have see many AIKEN variations in the forums:

I have used: EDITPAD LITE to get UTF-8 format as documented

I have tried a bunch and they All report the nasty: 

Parsing questions from import file.
Importing 2 questions from file

1. What is the correct answer to this question?

Error writing to database

 

What FORMAT SYNTAX WORKS?? Or is there are REAL Moodle XML creator tool for std. text?

Style 1(attached sample)

What is the correct answer to this question?
A. Is it this one?
B. Maybe this answer?
C. Possibly this one?
D. Must be this one!
ANSWER: D

A Second Quesiton?
A. Is it this one?
B. Maybe this answer?
C. Possibly this one?
D. Must be this one!
ANSWER: D

OR??????????Style 2 with extra params?????

All of the following are functions of the skeletal system EXCEPT
A. support
B. blood cell production
C. blood protein production
D. mineral storage
E. movement
ANSWER: C
GENERALFEEDBACK: Thanks for answering 'hey'!!
CORRECTFEEDBACK: That's right dude.
INCORRECTFEEDBACK: No!! Try again.

or??????????????Style 3??????????

 Which key would you use to delete characters to the right of the insertion point?
A. Tab
C. Delete
B. Backspace
D. Spacebar
ANSWER:C

 Jane Woods has asked you to modify the text in a marketing flyer she created yesterday. She does not want you to change the original but keep your own copy of this flyer. Which command should you use to accomplish this?
A. Save
B. Save As
ANSWER:B

or????????Style 4 with Q: Question?????

Q: In which box would you find the recipients signature?
A. A
B. B
C. C
D. D
ANSWER: A

Q: Which if the following is not included in this form?
A. Service Type
B. Article Number
C. Postmark
D. Date of Delivery
ANSWER: C

Average of ratings: -
In reply to Paul Caliban

Re: V2.3 and AIKEN Question Import - Error writing to Database

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

That is strange. It has been working for other people.

Can you try:

  1. Set Debugging to DEVELOPER level, to see if that gives a more informative error message.
  2. Upgrade to the latest 2.3.2.
Average of ratings: -
In reply to Tim Hunt

Re: V2.3 and AIKEN Question Import - Error writing to Database

by Paul Caliban -

Thanks. I did turn on DEBUG and GOT THIS:

I hesitate to upgrade with understanding the problem.  I am on build 2.3.1

I assumed the formating was my problem.

DEBUG FOLLOWS:

Parsing questions from import file.
Importing 2 questions from file

1. What is the correct answer to this question?

Error writing to database

More information about this error
Debug info: Field 'version' doesn't have a default value
INSERT INTO mdl_question (defaultmark,questiontextformat,generalfeedback,generalfeedbackformat,penalty,length,qtype,name,questiontext,category,stamp,createdby,timecreated,modifiedby,timemodified) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
[array (
0 => 1,
1 => '1',
2 => '',
3 => '1',
4 => 0.3333333,
5 => 1,
6 => 'multichoice',
7 => 'What is the correct answer to this question?',
8 => 'What is the correct answer to this question?',
9 => '67',
10 => 'localhost+120913150119+rFBdBO',
11 => '2',
12 => 1347548479,
13 => '2',
14 => 1347548479,
)]
Error code: dmlwriteexception
Stack trace:

    line 410 of \lib\dml\moodle_database.php: dml_write_exception thrown
    line 948 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()
    line 990 of \lib\dml\mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
    line 412 of \question\format.php: call to mysqli_native_moodle_database->insert_record()
    line 119 of \question\import.php: call to qformat_default->importprocess()

Average of ratings: -
In reply to Paul Caliban

Re: V2.3 and AIKEN Question Import - Error writing to Database

by Paul Caliban -

Based on this DEBUG message my early "GUESS" is:

1. Checking the Moodle Question table structure it has two fields: STAMP and VERSION. Neither have a default, because the DB expects the values to be included in the INSERT.  The data looks the same in both fields!

2. I see in the DEBUG MSG: The Question INSERT only has the one value for STAMP in the array and does not have "VERSION" field value.  So the bug and fix is:...  

Guess A.)  The AIKEN format needs the VERSION Param in the txt file with questions (low confidence)

Guess B.) The code for IMPORT differs from the std. functional USER Question Code (higer confidence).  (arrrrrg: find and fix code or see if fixed in 2.3.2-Upgrade!) sad

or...

Guess C.)  Note: This is a Legacy system DB Structure - upgraded from 1.9.x to 2.3.1 so perhaps just fixing the DB TABLE with a default will solve without harming code in GUESS "B"! (too easy!) smile

Average of ratings: -
In reply to Paul Caliban

Re: V2.3 and AIKEN Question Import - Error writing to Database

by Paul Caliban -

I decided to test my DEV server.... on GUESS "B"  Updgraded 2.3.2+

Sad to report the Debug message is the SAME!  OUCH!

I will explore Guess C., But must assume this can only be fixed in

/Question/Format.php    sad

Average of ratings: -
In reply to Paul Caliban

Re: V2.3 and AIKEN Question Import - Error writing to Database

by Jean-Michel Védrine -

Hello Paul,

I am unable to reproduce your problems on 2.3.2 and master.

Both syntax

What is the correct answer to this question?
A) Is it this one?
B) Maybe this answer?
C) Possibly this one?
D) Must be this one!
ANSWER: D

A Second Quesiton?
A) Is it this one?
B) Maybe this answer?
C) Possibly this one?
D) Must be this one!
ANSWER: D

-------------------------

and also

What is the correct answer to this question?
A. Is it this one?
B. Maybe this answer?
C. Possibly this one?
D. Must be this one!
ANSWER: D

A Second Quesiton?
A. Is it this one?
B. Maybe this answer?
C. Possibly this one?
D. Must be this one!
ANSWER: D

Import with no problem and don't produce any error.

You sample attached file 03NEWTEXT.txt is also imported with no problem and don't produce any error.

Average of ratings: -
In reply to Jean-Michel Védrine

Re: V2.3 and AIKEN Question Import - Error writing to Database

by Paul Caliban -

Thanks for the feed back! Very helpful!

Now the txt files are one less variable....

I inherited this this system and I am reloading it for the client.

SO.....I tried a basic CREATE. It failed on MULTI CHOICE (only) SURPRISE SURPRISE. 

SO..   yep- I tried True/False and Short Ans type and they both worked! emmmm OK - Progress!

Maybe the last DBA messed with DB?? I have no idea or the upgrade scripts failed!

I can edit MULTI CHOICE questions that already exist, but could not CREATE NEW!???

SO for FUN I set NULLS OK on both problem database tables: question and question_multichoice: (both tables had created DEBUG errors before the change, but none after this goofy alteration! ODD!! 

IT WORKS!  CraZy!!!
Multi Choice is the prob??

I might do a generic install on a dev server and check the code and database tables: question and question_multichoice  vs. the client base!!

It seems the tables related to type= multiQueston need some defaults set and that is not working! ODD!

Tables: Question,  Question_mulitchoice 

 

Thanks for the extra EYES!

Attachment Moodle2-3-1-question-table.png
Average of ratings: -
In reply to Paul Caliban

Re: V2.3 and AIKEN Question Import - Error writing to Database

by Paul Caliban -

One SideBar Question:

I have not seen this documented, but I will ask.....

Can this AIKEN method insert the text question with a related IMAGE?  I.E. +++HTML?

Original:

What is the correct answer to this question?
A. Is it this one?
B. Maybe this answer?
C. Possibly this one?
D. Must be this one!
ANSWER: D

 

Like -------------------------

Question: Can this AIKEN method insert the text for a related IMAGE? 

Description: <img  src="SomePIC.PNG" alt="Signature Confirmed Receipt Form" border="0" hspace="0" /> Can this AIKEN method insert the text for a related IMAGE?

A. Is it this one?
B. Maybe this answer?
C. Possibly this one?
D. Must be this one!
ANSWER: D

Average of ratings: -
In reply to Paul Caliban

Re: V2.3 and AIKEN Question Import - Error writing to Database

by Jean-Michel Védrine -

hello Paul,

If you want to verify that your table structures are OK, the full description of the question bank's tables is in lib/db/install.xml starting line 1256 for Moodle v 2.3 stable.

I don't think you should begin to modify things in db without fully understanding what your problems are.

If you are unsure of the current state of the Moodle server you should also upgarde all you files to the latest version  of the stable branch you are on (2.2 or 2.3).

Also activating debugging (Site Administration Development Debugging and set debug messages level to DEVELOPER) will print error messages that can help a lot to understand what is going on.

 

Average of ratings: -
In reply to Jean-Michel Védrine

Re: V2.3 and AIKEN Question Import - Error writing to Database

by Paul Caliban -

Thanks Jean-Michel.

Your directions have really been helpful! smile

YEP! The XML does show the NOTNULL Setting!

<FIELD NAME="stamp" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" PREVIOUS="length" NEXT="version"/>
        <FIELD NAME="version" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" PREVIOUS="stamp" NEXT="hidden"/>


So this tells me:

1. The past DBA/Moodle person did NOT mess with TBL!  Yippe! On less item!

2. I was testing on an isolated DEV server only: A.) Create Question works fine with DB HACK! :-0.  Meaning the fields are filled prefectly!  Fully filled fields!   I need to test more, but.....

This really makes NO Sense!  I would have expected a  NULL  Value. in Question and Question_Multichoice

Thinking......The import/create works fine with DB HACK in place....

So, for more FUN I did an upgrade on my crash Dev server to 2.3.2, reset the field to NOTNULL and the Problem persited.. 

This would indicate to me the OLDE DB has logical data issues!!!  It would seem the CODE shoule be OK.  smile one less thing to trace...

The code can edit existing, but cannot create!! emmm??????

The create vs. EDIT must be building the ARRAY from a "mashup" of table data and then writing Question and Question_Multichoice. 

So, There must be a legacy system DB setting table/field or Config.php item from the OLDE 1.9.x stuff.... that is not get set or did not get changed durning the long multistep upgrade path from 1.9 up to 2.3.2??

I am sure a fresh DB install vs. Upgrade the Legacy DB would expose this for me....

I am sure I will find this, but what a crunch!

On the Bright side: smile

I created a XLS formater for the clients xls/TXT input load file, which makes a proper MOODLE XML file that I can now use to get the IMAGE LINKS into place on the QUESTION TABLE (questiontext field) updated!   AIKEN seemed to be pure text!

 

Thanks again!

Average of ratings: -
In reply to Paul Caliban

Re: V2.3 and AIKEN Question Import - Error writing to Database

by jj v -

Uuhm, the same problems with my import of the Aiken stuff... Even the same errorfeedback

Average of ratings: -
In reply to jj v

Re: V2.3 and AIKEN Question Import - Error writing to Database

by Jean-Michel Védrine -

Hello,

Can you post the file producing the error and also tell us what version of Moodle you are using ?

As I said in response to the previous message I was unable to reproduce the problem in Moodle 2.3.2 and for me the Aiken import is working as it should !

But maybe we can increase its robustness so that incorrect files produce a more descriptive error message than "Error writting to database.

Average of ratings: -
In reply to Paul Caliban

Re: V2.3 and AIKEN Question Import - Error writing to Database

by Bill Galbraith -

I also problems importing Aiken questions. I traced the problem down to the encoding of the text file. My questions were generated in Microsoft Word, and exported in plain text. This was NOT UTF-8 format, so when changed the format to UTF-8 in Notepad++, it indicated that it didn't like the apostrophe in the word "won't". I fixed that, and it imported okay. You can also export from Word in a YTF-8 format that should also solve the problem.

Average of ratings: -
In reply to Bill Galbraith

Re: V2.3 and AIKEN Question Import - Error writing to Database

by Jean-Michel Védrine -

Hello Bill,

This is in fact a problem in several question import formats: AIKEN, GIFT, ... They expect valid utf-8 encoded files but as they make no check that the imported file is utf-8 encoded, they can fail very ungracefully, for instance if the first illegal character in in the first answer of a multichoice question, a completely broken question is imported because only part of the question is written to the database, and if you latter try to edit or attempt this question you get an error but no warning is given if you include it in a quiz surprise.

I think that a general function to check encoding should be added and relevant formats should use it. This function could use either preg_match('//u', $string) or mb_check_encoding.

But it should be the format responsibility to call it or not as there are formats designed to import non utf-8 files (for instance my https://github.com/jmvedrine/moodle-qformat_qcmdirect )

Average of ratings: -