Email based self-registration failure since upgrade.

Email based self-registration failure since upgrade.

by John W -
Number of replies: 11

Sorry if this is the wrong place to ask. Been at my wit's end for a while. Environment detail is at the end. When a user attempts to self-register I get the following error:


[25-Jun-2020 16:08:51 America/Los_Angeles] Default exception handler: Error writing to database Debug: Field 'province' doesn't have a default value

INSERT INTO mdl_user (username,password,email,firstname,lastname,city,country,confirmed,lang,firstaccess,timecreated,mnethostid,secret,auth,firstnamephonetic,lastnamephonetic,middlename,alternatename,calendartype,maildisplay,mailformat,maildigest,autosubscribe,trackforums,timemodified) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

[array (

0 => 'test2',

1 => '$2y$10$50e7zNmW48KbToLCc4CuDOWsRzCU4s3trDcosqPBaY5PT5a7QHDwy',

2 => 'email@redacted',

3 => 'Test',

4 => 'Munkey',

5 => 'Los Angeles',

6 => 'US',

7 => 0,

8 => 'en',

9 => 0,

10 => 1593126531,

11 => '3',

12 => '2DGArGbdkqN8DhU',

13 => 'email',

14 => '',

15 => '',

16 => '',

17 => '',

18 => 'gregorian',

19 => '0',

20 => '1',

21 => '0',

22 => '1',

23 => '0',

24 => 1593126531,

)]

Error code: dmlwriteexception


line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown

line 1357 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()

line 1403 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()

line 111 of /user/lib.php: call to mysqli_native_moodle_database->insert_record()

line 126 of /auth/email/auth.php: call to user_create_user()

line 99 of /auth/email/auth.php: call to auth_plugin_email->user_signup_with_confirmation()

line 92 of /login/signup.php: call to auth_plugin_email->user_signup() 


Server checks

version 3.5 is required and you are running 3.9+ (Build: 20200618)

 

OK

unicode

 

must be installed and enabled

 

OK

database

mariadb (10.2.32-MariaDB-log-cll-lve)

version 10.2.29 is required and you are running 10.2.32

 

OK

php

 

version 7.2.0 is required and you are running 7.2.31

 

OK

pcreunicode

 

should be installed and enabled for best results

 

OK

php_extension

iconv

must be installed and enabled

 

OK

php_extension

mbstring

must be installed and enabled

 

OK

php_extension

curl

must be installed and enabled

 

OK

php_extension

openssl

must be installed and enabled

 

OK

php_extension

tokenizer

should be installed and enabled for best results

 

OK

php_extension

xmlrpc

should be installed and enabled for best results

 

OK

php_extension

soap

should be installed and enabled for best results

 

OK

php_extension

ctype

must be installed and enabled

 

OK

php_extension

zip

must be installed and enabled

 

OK

php_extension

zlib

must be installed and enabled

 

OK

php_extension

gd

must be installed and enabled

 

OK

php_extension

simplexml

must be installed and enabled

 

OK

php_extension

spl

must be installed and enabled

 

OK

php_extension

pcre

must be installed and enabled

 

OK

php_extension

dom

must be installed and enabled

 

OK

php_extension

xml

must be installed and enabled

 

OK

php_extension

xmlreader

must be installed and enabled

 

OK

php_extension

intl

must be installed and enabled

 

OK

php_extension

json

must be installed and enabled

 

OK

php_extension

hash

must be installed and enabled

 

OK

php_extension

fileinfo

must be installed and enabled

 

OK

php_setting

memory_limit

recommended setting detected

 

OK

php_setting

file_uploads

recommended setting detected

 

OK

php_setting

opcache.enable

recommended setting detected


Average of ratings: -
In reply to John W

Re: Email based self-registration failure since upgrade.

by Ken Task -
Picture of Particularly helpful Moodlers

What is "Field 'province'" ... not a box in creating a new user manually nor default field (column) in mdl_user table.

Added/additional by you?   Can you make it 'not required' then user can 'fill it out' upon initial login?

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: Email based self-registration failure since upgrade.

by John W -

Yeah I can’t find any mention of ‘province’ anywhere either. It’s not a default option nor a custom field, it isn’t visible on the login form, it isn’t visible on the user profile. I‘m not sure where else it could be.

Average of ratings: -
In reply to John W

Re: Email based self-registration failure since upgrade.

by Ken Task -
Picture of Particularly helpful Moodlers

Well that's 'special'!!!

Version of Moodle?

Let's pretend I am a student.   Send to me (via forum here) directions for how I sign up for your site/courses.

'SoS', Ken


Average of ratings: -
In reply to Ken Task

Re: Email based self-registration failure since upgrade.

by John W -
Very special. The page doesn't give much info, just a generic 'Error writing to database' and a general troubleshooting link, but here it is:

https://yellowemperor.org/login/index.php
Average of ratings: -
In reply to John W

Re: Email based self-registration failure since upgrade.

by Ken Task -
Picture of Particularly helpful Moodlers

Thanks.  Well nothing looks strange ... but you do have recaptcha and it allows checking a box to confirm not a robot - no pics to click etc.

Any check on recaptcha set up?

Filled out an account ... and upon submitting got error screen ..

more info link pointed to

https://docs.moodle.org/39/en/error/moodle/dmlwriteexception

no help there ... sad

Since I filled on out, can you check your admin area of your Moodle users to see if there is an account you have to confirm?

Your web server is LiteSpeed.  Tried to Google for any issues with Moodle and LiteSpeed but found none - that doesn't mean there isn't with 3.9.x.

Please check error logs again for clues.

'SoS', Ken


Average of ratings: -
In reply to Ken Task

Re: Email based self-registration failure since upgrade.

by John W -
I see your signup attempt in the logs, same message as before, but nothing in my users list or pending. I'm not having a problem with the re-captcha showing images, I assume it just doesn't trust me. It will let you click through without images if it can trust you based on other things Google knows, probably if you use Chrome Google knows more about you.

It seems it's trying to write to mdl_users, I looked at the database to see if I could find such a table and I can't. In dumping the whole database I found it. But I don't know enough about MySQL / MariaDB to really know what it means yet.

--
-- Table structure for table `mdl_user`
--

DROP TABLE IF EXISTS `mdl_user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mdl_user` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`auth` varchar(20) NOT NULL DEFAULT 'manual',
`confirmed` tinyint(1) NOT NULL DEFAULT 0,
`policyagreed` tinyint(1) NOT NULL DEFAULT 0,
`deleted` tinyint(1) NOT NULL DEFAULT 0,
`suspended` tinyint(1) NOT NULL DEFAULT 0,
`mnethostid` bigint(10) NOT NULL DEFAULT 0,
`username` varchar(100) NOT NULL DEFAULT '',
`password` varchar(255) NOT NULL DEFAULT '',
`idnumber` varchar(255) NOT NULL DEFAULT '',
`firstname` varchar(100) NOT NULL DEFAULT '',
`lastname` varchar(100) NOT NULL DEFAULT '',
`email` varchar(100) NOT NULL DEFAULT '',
`emailstop` tinyint(1) NOT NULL DEFAULT 0,
`icq` varchar(15) NOT NULL DEFAULT '',
`skype` varchar(50) NOT NULL DEFAULT '',
`yahoo` varchar(50) NOT NULL DEFAULT '',
`aim` varchar(50) NOT NULL DEFAULT '',
`msn` varchar(50) NOT NULL DEFAULT '',
`phone1` varchar(20) NOT NULL DEFAULT '',
`phone2` varchar(20) NOT NULL DEFAULT '',
`institution` varchar(255) NOT NULL DEFAULT '',
`department` varchar(255) NOT NULL DEFAULT '',
`address` varchar(255) NOT NULL DEFAULT '',
`city` varchar(120) NOT NULL DEFAULT '',
`province` longtext NOT NULL,
`country` varchar(2) NOT NULL DEFAULT '',
`lang` varchar(30) NOT NULL DEFAULT 'en',
`theme` varchar(50) NOT NULL DEFAULT '',
`timezone` varchar(100) NOT NULL DEFAULT '99',
`firstaccess` bigint(10) NOT NULL DEFAULT 0,
`lastaccess` bigint(10) NOT NULL DEFAULT 0,
`lastlogin` bigint(10) NOT NULL DEFAULT 0,
`currentlogin` bigint(10) NOT NULL DEFAULT 0,
`lastip` varchar(45) NOT NULL DEFAULT '',
`secret` varchar(15) NOT NULL DEFAULT '',
`picture` bigint(10) NOT NULL DEFAULT 0,
`url` varchar(255) NOT NULL DEFAULT '',
`description` longtext DEFAULT NULL,
`descriptionformat` tinyint(2) NOT NULL DEFAULT 1,
`mailformat` tinyint(1) NOT NULL DEFAULT 1,
`maildigest` tinyint(1) NOT NULL DEFAULT 0,
`maildisplay` tinyint(2) NOT NULL DEFAULT 2,
`autosubscribe` tinyint(1) NOT NULL DEFAULT 1,
`trackforums` tinyint(1) NOT NULL DEFAULT 0,
`timecreated` bigint(10) NOT NULL DEFAULT 0,
`timemodified` bigint(10) NOT NULL DEFAULT 0,
`trustbitmask` bigint(10) NOT NULL DEFAULT 0,
`imagealt` varchar(255) DEFAULT NULL,
`lastnamephonetic` varchar(255) DEFAULT NULL,
`firstnamephonetic` varchar(255) DEFAULT NULL,
`middlename` varchar(255) DEFAULT NULL,
`alternatename` varchar(255) DEFAULT NULL,
`acclicense` longtext NOT NULL,
`calendartype` varchar(30) NOT NULL DEFAULT 'gregorian',
PRIMARY KEY (`id`),
UNIQUE KEY `mdl_user_mneuse_uix` (`mnethostid`,`username`),
KEY `mdl_user_del_ix` (`deleted`),
KEY `mdl_user_con_ix` (`confirmed`),
KEY `mdl_user_fir_ix` (`firstname`),
KEY `mdl_user_las_ix` (`lastname`),
KEY `mdl_user_cou_ix` (`country`),
KEY `mdl_user_las2_ix` (`lastaccess`),
KEY `mdl_user_ema_ix` (`email`),
KEY `mdl_user_aut_ix` (`auth`),
KEY `mdl_user_idn_ix` (`idnumber`),
KEY `mdl_user_cit_ix` (`city`),
KEY `mdl_user_las3_ix` (`lastnamephonetic`),
KEY `mdl_user_fir2_ix` (`firstnamephonetic`),
KEY `mdl_user_mid_ix` (`middlename`),
KEY `mdl_user_alt_ix` (`alternatename`)
) ENGINE=InnoDB AUTO_INCREMENT=23046 DEFAULT CHARSET=utf8 COMMENT='One record for each person';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `mdl_user`
--

LOCK TABLES `mdl_user` WRITE;
/*!40000 ALTER TABLE `mdl_user` DISABLE KEYS */;
INSERT INTO `mdl_user` (`id`, `auth`, `confirmed`, `policyagreed`, `deleted`, `suspended`, `mnethostid`, `username`, `password`, `idnumber`, `firstname`, `lastname`, `email`, `emailstop`, `icq`, `skype`, `yahoo`, `aim`, `msn`, `phone1`, `phone2`, `institution`, `department`, `address`, `city`, `province`, `country`, `lang`, `theme`, `timezone`, `firstaccess`, `lastaccess`, `lastlogin`, `currentlogin`, `lastip`, `secret`, `picture`, `url`, `description`, `descriptionformat`, `mailformat`, `maildigest`, `maildisplay`, `autosubscribe`, `trackforums`, `timecreated`, `timemodified`, `trustbitmask`, `imagealt`, `lastnamephonetic`, `firstnamephonetic`, `middlename`, `alternatename`, `acclicense`, `calendartype`)
Average of ratings: -
In reply to John W

Re: Email based self-registration failure since upgrade.

by Ken Task -
Picture of Particularly helpful Moodlers

In your Table structure for table `mdl_user
`province` longtext NOT NULL, there it is!

In your Dumping data for table `mdl_user
province is also present.

From CLI client in a fresh 3.9+ moodle - has NO reference to province:

mysql> explain mdl_user;
+-------------------+--------------+------+-----+-----------+----------------+
| Field             | Type         | Null | Key | Default   | Extra          |
+-------------------+--------------+------+-----+-----------+----------------+
| id                | bigint(10)   | NO   | PRI | NULL      | auto_increment |
| auth              | varchar(20)  | NO   | MUL | manual    |                |
| confirmed         | tinyint(1)   | NO   | MUL | 0         |                |
| policyagreed      | tinyint(1)   | NO   |     | 0         |                |
| deleted           | tinyint(1)   | NO   | MUL | 0         |                |
| suspended         | tinyint(1)   | NO   |     | 0         |                |
| mnethostid        | bigint(10)   | NO   | MUL | 0         |                |
| username          | varchar(100) | NO   |     |           |                |
| password          | varchar(255) | NO   |     |           |                |
| idnumber          | varchar(255) | NO   | MUL |           |                |
| firstname         | varchar(100) | NO   | MUL |           |                |
| lastname          | varchar(100) | NO   | MUL |           |                |
| email             | varchar(100) | NO   | MUL |           |                |
| emailstop         | tinyint(1)   | NO   |     | 0         |                |
| icq               | varchar(15)  | NO   |     |           |                |
| skype             | varchar(50)  | NO   |     |           |                |
| yahoo             | varchar(50)  | NO   |     |           |                |
| aim               | varchar(50)  | NO   |     |           |                |
| msn               | varchar(50)  | NO   |     |           |                |
| phone1            | varchar(20)  | NO   |     |           |                |
| phone2            | varchar(20)  | NO   |     |           |                |
| institution       | varchar(255) | NO   |     |           |                |
| department        | varchar(255) | NO   |     |           |                |
| address           | varchar(255) | NO   |     |           |                |
| city              | varchar(120) | NO   | MUL |           |                |
| country           | varchar(2)   | NO   | MUL |           |                |
| lang              | varchar(30)  | NO   |     | en        |                |
| calendartype      | varchar(30)  | NO   |     | gregorian |                |
| theme             | varchar(50)  | NO   |     |           |                |
| timezone          | varchar(100) | NO   |     | 99        |                |
| firstaccess       | bigint(10)   | NO   |     | 0         |                |
| lastaccess        | bigint(10)   | NO   | MUL | 0         |                |
| lastlogin         | bigint(10)   | NO   |     | 0         |                |
| currentlogin      | bigint(10)   | NO   |     | 0         |                |
| lastip            | varchar(45)  | NO   |     |           |                |
| secret            | varchar(15)  | NO   |     |           |                |
| picture           | bigint(10)   | NO   |     | 0         |                |
| url               | varchar(255) | NO   |     |           |                |
| description       | longtext     | YES  |     | NULL      |                |
| descriptionformat | tinyint(2)   | NO   |     | 1         |                |
| mailformat        | tinyint(1)   | NO   |     | 1         |                |
| maildigest        | tinyint(1)   | NO   |     | 0         |                |
| maildisplay       | tinyint(2)   | NO   |     | 2         |                |
| autosubscribe     | tinyint(1)   | NO   |     | 1         |                |
| trackforums       | tinyint(1)   | NO   |     | 0         |                |
| timecreated       | bigint(10)   | NO   |     | 0         |                |
| timemodified      | bigint(10)   | NO   |     | 0         |                |
| trustbitmask      | bigint(10)   | NO   |     | 0         |                |
| imagealt          | varchar(255) | YES  |     | NULL      |                |
| lastnamephonetic  | varchar(255) | YES  | MUL | NULL      |                |
| firstnamephonetic | varchar(255) | YES  | MUL | NULL      |                |
| middlename        | varchar(255) | YES  | MUL | NULL      |                |
| alternatename     | varchar(255) | YES  | MUL | NULL      |                |
| moodlenetprofile  | varchar(255) | YES  |     | NULL      |                |
+-------------------+--------------+------+-----+-----------+----------------+
54 rows in set (0.00 sec)

Let's see what data is in there:

mysql> select id,auth,mnethostid,username,firstname,lastname,email,province from mdl_user;

Above errors for me:

mysql> select id,auth,mnethostid,username,firstname,lastname,email,province from mdl_user;
ERROR 1054 (42S22): Unknown column 'province' in 'field list'

How long have you had this moodle?   I started back when moodle was 1.4 or 1.5 and I don't recall there ever being a 'province' ... that was years ago and my memory ain't so good anymore, however! :|

Code must have been modified at one point in time.  Site is about Chinese accupuncture isn't it?? so province would be logical!

You don't use the province data, do you?

https://www.mysqltutorial.org/mysql-drop-column/

Consider dropping that column ... after making a backup of the DB for insurance.

Anyone as ancient as me round that remembers province?

'SoS', Ken

Average of ratings: Useful (2)
In reply to Ken Task

Re: Email based self-registration failure since upgrade.

by John W -
We must have found the solution at the same time, I just saw your reply. This was originally 1.9... The "province" issue appears on the Moodle Tracker a few times from 2009-2019, it was shorthand for State/Province. Relevant tracker here. State was a required field because of state licensing continuing education requirements.

Edit: direct link to MDL-28452
Average of ratings: -
In reply to John W

Re: Email based self-registration failure since upgrade.

by John W -
Well, I can't believe this but I solved it. So for any poor souls who find this, I had to go into the database and remove some old custom form fields that were not migrated properly during the upgrade.

I'm going to have to re-enter the data I dropped by hand from my backup since I'm too burned out to figure out a better way.

Command:
ALTER TABLE mdl_user DROP customfieldname;

Rinse and repeat until the errors stop.

Thank you Ken for taking an interest and sanity checking.
Average of ratings: Useful (1)
In reply to John W

Re: Email based self-registration failure since upgrade.

by Ken Task -
Picture of Particularly helpful Moodlers

Well, I just posted suggesting something similar ... at least we were thinking alike! smile

Congrats!

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: Email based self-registration failure since upgrade.

by John W -
Version 3.9 - latest build
Average of ratings: -