Upload users: Column 'dataformat' cannot be null

Upload users: Column 'dataformat' cannot be null

by James Hamilton -
Number of replies: 13

Hi Folks,

I'm getting the following error when uploading (what I think) is a valid .csv via upload users.

The CSV is accepted and displays fine after initial upload, then after confirming details and choosing 'update existing users' the error comes up and nothing is written to the db.

Anyone have any idea's what the issue might be?

Moodle Version  3.1

Warning:
 Illegal string offset 'text' in 
.../admin/tool/uploaduser/index.php on line 236 
Warning: Illegal string offset 'format' in 
.../admin/tool/uploaduser/index.php on line 237 
Notice: Undefined index: format in 
.../user/profile/field/textarea/field.class.php on 
line 59 Notice: Undefined index: text in 
.../user/profile/field/textarea/field.class.php on 
line 60 


Error writing to database

More information about this error


Debug info: Column 'dataformat' cannot be null
INSERT INTO mdl_user_info_data (dataformat,userid,fieldid,data) VALUES(?,?,?,?)
[array (
0 => NULL,
1 => 11247,
2 => '11',
3 => NULL,
)]

Error code: dmlwriteexception

Stack trace: 
line 477 of /lib/dml/moodle_database.php: dml_write_exception thrown
line 1172 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 1218 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
line 157 of /user/profile/lib.php: call to mysqli_native_moodle_database->insert_record()
line 501 of /user/profile/lib.php: call to profile_field_base->edit_save_data()
line 824 of /admin/tool/uploaduser/index.php: call to profile_save_data()

Many thanks for your hints,
James

Average of ratings: -
In reply to James Hamilton

Re: Upload users: Column 'dataformat' cannot be null

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I suspect your database schema may be wrong,  here's mine...  (version 3.3.1)

CREATE TABLE `mdl_user_info_data` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `userid` bigint(10) NOT NULL DEFAULT '0',
  `fieldid` bigint(10) NOT NULL DEFAULT '0',
  `data` longtext COLLATE utf8_unicode_ci NOT NULL,
  `dataformat` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `mdl_userinfodata_usefie_uix` (`userid`,`fieldid`)
) ENGINE=InnoDB AUTO_INCREMENT=178 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Data for the customisable user fields';


That field is "DEFAULT '0'", so this error would not happen. Can you check the schema for this table?

Average of ratings: -
In reply to Howard Miller

Re: Upload users: Column 'dataformat' cannot be null

by James Hamilton -
Unfortunately not on this particular instance - it's hosted elsewhere (a story for another day). Perhaps there's a way to get at what your looking for via configurable reports or elsewhere in Moodle?
Average of ratings: -
In reply to James Hamilton

Re: Upload users: Column 'dataformat' cannot be null

by James Hamilton -

Schema is same as @Howard's. Perhaps I should log a bug? For the life of me I can't figure this one out.

Average of ratings: -
In reply to James Hamilton

Re: Upload users: Column 'dataformat' cannot be null

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
I'm quite surprised. 


You'll need to demonstrate how to reproduce this. You must have custom profile fields. Can you create a minimum CSV file that shows the problem, post it here, along with a description of your custom fields. I can then try it myself. 



Average of ratings: -
In reply to Howard Miller

Re: Upload users: Column 'dataformat' cannot be null

by James Hamilton -

Definitely have custom fields, will do some more testing using the minute Intel number instead of the actual text of these custom fields before I reach out again with an example borked CSV. Thanks for the helP smile

Average of ratings: -
In reply to James Hamilton

Re: Upload users: Column 'dataformat' cannot be null

by Guillermo Madero -

Hi James,

An "Illegal string offset" error refers to an array index error, meaning that the array index is undefined or has a wrong value.

The lines in question belong to the following piece of code:

231    if (strpos($key, 'profile_field_') === 0) {
232        //NOTE: bloody mega hack alert!!
233        if (isset($USER->$key) and is_array($USER->$key)) {
234            // this must be some hacky field that is abusing arrays to store content and format
235            $user->$key = array();
236            $user->{$key['text']}   = $value;
237            $user->{$key['format']} = FORMAT_MOODLE;
238        } else {
239            $user->$key = trim($value);
240        }
241    } else {

Given the comment in that code, I would definitely start by checking:

  1. The user upload documentation (https://docs.moodle.org/31/en/Upload_users), particularly the info regarding Custom profile field names, just to be completely sure about what kind of data and format is expected.
  2. The data in the upload file, to check that it matches what is expected from the upload module.

Cheers!

 

Average of ratings: -
In reply to Guillermo Madero

Re: Upload users: Column 'dataformat' cannot be null

by James Hamilton -
Thanks for the feedback Guillero, I'll double check my CSV column names and report back. 


If one has a dropdown menu though, should one use the "array" number or the actual text of the field? The docs seem to suggest the actual text is acceptable, but perhaps the docs and the behaviour don't match. I'll try it out and report back.


Appreciate the help. J

Average of ratings: -
In reply to James Hamilton

Re: Upload users: Column 'dataformat' cannot be null

by Guillermo Madero -

The actual text, it seems:

https://docs.moodle.org/26/en/Upload_users#Fields_that_can_be_included

For custom profile fields that are a menu, use the corresponding value (new in Moodle 2.3 onwards).

Example: A custom field 'Department' with one of three values 'HR', 'Marketing' or 'Training'. Just insert one of those three words (e.g. 'Training') as the value for that field.

Anyway, take a look at the following tracker entries:

https://tracker.moodle.org/browse/MDL-31654

https://tracker.moodle.org/browse/MDL-16764

 

 

Average of ratings: -
In reply to James Hamilton

Re: Upload users: Column 'dataformat' cannot be null

by James Hamilton -

Report back: the actual text of the field works, the array number does not.

Issue remains unresolved, removing column by column now to find out which one is might be the culpret.

Average of ratings: -
In reply to James Hamilton

Re: Upload users: Column 'dataformat' cannot be null

by James Hamilton -

Right, update time:

The offending field is a textarea custom profile field that is used to store an address.

Over time, some people have entered <p> and other formatting tags, however even without those no data in this field is being accepted.

I did a small user upload with 3 users to test.

The only way the upload was successful was if the field was omitted entirely from the .csv.

Users can continue to input data to this field when creating their profiles successfully, but on uploading the very same data (or even modified to exclude formatting tags), it fails.

Any ideas on a fix?

Average of ratings: -
In reply to James Hamilton

Re: Upload users: Column 'dataformat' cannot be null

by Guillermo Madero -

Have you tried uploading with some simple data? Any particular differences with any other textarea field? From my point of view, it is either the format or the data. Start with the basics and start building the path toward your actual data.

Average of ratings: -