XF 2.2 Registration error seen after site move and upgrade.

[Cethlenn]

Hi!

I had to find a new host for my forum and succeeded, for the most part, in moving it unscathed.
I'm seeing a new error in registrations, where users are unable to register. They see "There was an error, check back later", and we get the following:

XF\Db\Exception: MySQL query error [1048]: Column 'user_id' cannot be null src/XF/Db/AbstractStatement.php:228
Generated by: Unknown account May 10, 2021 at 1:28 AM
[1:32 AM]
Stack trace
INSERT INTO xf_user (visible, activity_visible, user_group_id, timezone, language_id, last_summary_email_date, username, email, user_state, privacy_policy_accepted, terms_accepted, display_style_group_id, secondary_group_ids, secret_key, user_id, username_date, username_date_visible, style_id, permission_combination_id, message_count, question_solution_count, alerts_unviewed, alerts_unread, conversations_unread, register_date, last_activity, trophy_points, avatar_date, avatar_width, avatar_height, avatar_highdpi, gravatar, security_lock, is_moderator, is_admin, is_staff, is_banned, reaction_score, vote_score, custom_title, warning_points, xfmg_album_count, xfmg_media_count, xfmg_media_quota, xfrm_resource_count) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
------------

#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL query err...', 1048, '23000')
#1 src/XF/Db/Mysqli/Statement.php(77): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1048, '23000')
#2 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(218): XF\Db\AbstractAdapter->query('INSERT INTO `x...', Array)
#4 src/XF/Mvc/Entity/Entity.php(1510): XF\Db\AbstractAdapter->insert('xf_user', Array, false)
#5 src/XF/Mvc/Entity/Entity.php(1242): XF\Mvc\Entity\Entity->_saveToSource()
#6 src/XF/Service/User/Registration.php(299): XF\Mvc\Entity\Entity->save()
#7 src/XF/Service/ValidateAndSavableTrait.php(40): XF\Service\User\Registration->_save()`
[1:32 AM]
#8 src/XF/Pub/Controller/Register.php(422): XF\Service\User\Registration->save()
#9 src/addons/SV/SignupAbuseBlocking/XF/Pub/Controller/Register.php(64): XF\Pub\Controller\Register->actionRegister()
#10 src/XF/Mvc/Dispatcher.php(350): SV\SignupAbuseBlocking\XF\Pub\Controller\Register->actionRegister(Object(XF\Mvc\ParameterBag))
#11 src/XF/Mvc/Dispatcher.php(257): XF\Mvc\Dispatcher->dispatchClass('XF:Register', 'Register', Object(XF\Mvc\RouteMatch), Object(SV\SignupAbuseBlocking\XF\Pub\Controller\Register), Object(XF\Mvc\Reply\Reroute))
#12 src/XF/Mvc/Dispatcher.php(113): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(SV\SignupAbuseBlocking\XF\Pub\Controller\Register), Object(XF\Mvc\Reply\Reroute))
#13 src/XF/Mvc/Dispatcher.php(55): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#14 src/XF/App.php(2337): XF\Mvc\Dispatcher->run()
#15 src/XF.php(488): XF\App->run()
#16 index.php(20): XF::runApp('XF\\Pub\\App')
#17 {main}

Request state
array(4) {
["url"] => string(34) "/frphforum/login/register-register"
["referrer"] => string(35) "https://furryrphaven.com/frphforum/"
["_GET"] => array(0) {
}
["_POST"] => array(20) {
["username"] => string(0) ""
["2489cf55374203e2059313eca76d1329463b12d1"] => string(6) "skreee"
["1a649dba6f2f347d3a4c50cd717b3b6e01539a5b"] => string(0) ""
["c7b98d91d5c404e91ef53d533d16ebcbd944488d"] => string(13) "test@test.com"
["email"] => string(0) ""
["1b2f07223cbca936799afbed967fab54ee3dfe62"] => string(8) "********"
["dob_month"] => string(1) "1"
["dob_day"] => string(1) "3"
["dob_year"] => string(4) "2000"
["location"] => string(2) "US"
["captcha_question_answer"] => string(4) "blue"
["captcha_question_hash"] => string(40) "4baf5c73c45dba45b6f8127548ecb9f90bd734e2"
["accept"] => string(1) "1"
["reg_key"] => string(16) "veZxbsrW5WxiSzZf"
["95a1db45877d50d3beb5fd350d70211a2a4a567e"] => string(19) "America/Los_Angeles"
["_xfToken"] => string(8) "********"
["timetaken"] => string(6) "35.292"
["_xfRequestUri"] => string(11) "/frphforum/"
["_xfWithData"] => string(1) "1"
["_xfResponseType"] => string(4) "json"
}
}

Any help would be appreciated. The forum was at 2.2.4 prior to the move and upgraded to 2.2.5 afterward.

Best,

Cethlenn

 

[thetechguy]

Can you please turn off the "SignupAbuseBlocking" addon and try again ?

 

[Cethlenn]

Yes. That was one I hadn't disabled yet. One moment..

 

[Cethlenn]

I disabled it and the error persists.

Server error log​

Server error log

• XF\Db\Exception: MySQL query error [1048]: Column 'user_id' cannot be null
• src/XF/Db/AbstractStatement.php:228
• Generated by: Unknown account
• May 10, 2021 at 2:35 AM

Stack trace​

INSERT INTO xf_user (visible, activity_visible, user_group_id, timezone, language_id, last_summary_email_date, username, email, user_state, privacy_policy_accepted, terms_accepted, display_style_group_id, secondary_group_ids, secret_key, user_id, username_date, username_date_visible, style_id, permission_combination_id, message_count, question_solution_count, alerts_unviewed, alerts_unread, conversations_unread, register_date, last_activity, trophy_points, avatar_date, avatar_width, avatar_height, avatar_highdpi, gravatar, security_lock, is_moderator, is_admin, is_staff, is_banned, reaction_score, vote_score, custom_title, warning_points, xfmg_album_count, xfmg_media_count, xfmg_media_quota, xfrm_resource_count) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
------------

#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL query err...', 1048, '23000')
#1 src/XF/Db/Mysqli/Statement.php(77): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1048, '23000')
#2 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(218): XF\Db\AbstractAdapter->query('INSERT INTO `x...', Array)
#4 src/XF/Mvc/Entity/Entity.php(1510): XF\Db\AbstractAdapter->insert('xf_user', Array, false)
#5 src/XF/Mvc/Entity/Entity.php(1242): XF\Mvc\Entity\Entity->_saveToSource()
#6 src/XF/Service/User/Registration.php(299): XF\Mvc\Entity\Entity->save()
#7 src/XF/Service/ValidateAndSavableTrait.php(40): XF\Service\User\Registration->_save()
#8 src/XF/Pub/Controller/Register.php(422): XF\Service\User\Registration->save()
#9 src/XF/Mvc/Dispatcher.php(350): XF\Pub\Controller\Register->actionRegister(Object(XF\Mvc\ParameterBag))
#10 src/XF/Mvc/Dispatcher.php(257): XF\Mvc\Dispatcher->dispatchClass('XF:Register', 'Register', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Register), NULL)
#11 src/XF/Mvc/Dispatcher.php(113): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Register), NULL)
#12 src/XF/Mvc/Dispatcher.php(55): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#13 src/XF/App.php(2337): XF\Mvc\Dispatcher->run()
#14 src/XF.php(488): XF\App->run()
#15 index.php(20): XF::runApp('XF\\Pub\\App')
#16 {main}

Request state​

array(4) {
["url"] => string(28) "/frphforum/register/register"
["referrer"] => string(517) "https://furryrphaven.com/frphforum/...IVyM6JWTX5tZGdPQMyPv1OVfgzUYn40dRClkZPbFerXSA"
["_GET"] => array(0) {
}
["_POST"] => array(18) {
["username"] => string(0) ""
["30fc1c0e070e0575d7bbd6844022878f1dbede34"] => string(6) "skreee"
["96a6dde855102b9af9c3efff4dd7526aded562a5"] => string(13) "test@test.com"
["email"] => string(0) ""
["6bb2780c9e6a4ec0de1d6d26f8330007c7e24cd7"] => string(8) "********"
["dob_month"] => string(1) "1"
["dob_day"] => string(1) "3"
["dob_year"] => string(4) "2000"
["location"] => string(2) "US"
["g-recaptcha-response"] => string(1438) "P0_eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJwYXNza2V5IjoiczZuQ1JGZ3BzODU0djRWV0lFMFY0Z1R1ODRpOVJVdldNTnV4YUNYaVN1YUlwYlp3TFg3Y1pscDZ0MkhuOXNtd1pIZGNQV2xNb1I2aVdSWS9FS3ZpYVJ5YUhkSGJUdnU1TVo4OTFxWFRpeE5UbDBrUE1oMS93ODBkNVpTUXIzMkNNd0luVStsSDVCYURGcEs0MW1oMlgrRjZJZVpONFNaOG94TjB5WFgwQTBkRGFNSnhQaGtUZ1E1bjkwN3VyNzd3a2RTOVZ6UHhhNDVWei95NkFZcktHclhyZURxQms3T3JWYUdLaHJiL0U2QUo2WElDYnZCUlZBaEYvTXRuNXJ1azBJeit2RVVjK0Vqdm9LZnNUcFRxS3NRSkZjUE51WEtHdlRwZWozVExHUkZEd1N2L0JjWXVuUHZGY2crUzU2ejJjdWlhK2dER1VIMVYvWlI3YVVTd01UaFFpb3BxWVJOc2RrdGpUeG1rMGNRQ0dUZ2pHdGZQVmQ2cnp0RVAxaUkxY2ZNQ2NPVml4TER6SlNnL3ZrVDNXTUFpTldxWVFaZmtYUXJJWkFmSTE2ektIcjJMM2RmMUJKb2xwa1h2bytwMk5ROUVwRjFPbE10ZFQyVUJsZndLSlhpNDljTkRWeERjSUdkd2VyVHZiQUJmeVJIRDdFOHNZMElwTng2VmZwSXR2VEh4YUtuMGJwRXFveWg1dUxjTDdYblZPME80VUdsUWgyQTZVTFpGdUh3aERlbVdaN2JseXkxWDJTd1VBeHhvK2VTdi9Zem1YczJlUTJ4QThFazNjL0praHhWRWVvRnAwMDdHSFo1THh6T0txSkZoaUxOdExoOFhZRnpBMnF6dklFZ3d2U3VvcE8xL1pLWnJ0VlR6QTVncHBHejcwRmFnWlF1YkdkcGYwSWJwVGhPaVlnTVZ4L2JlWGxsQjBtS2luMWR6Y0JYRWNDdzVwblZPTjN3ai9KMUhlenN5S2ViaWN5VnhkME42VE93Rk5GYVowSGxXNWI2RThRMk9lazdhSHFwUEdVOEdsdmFIc2pZOHBtYi9mb2Rkdkg1OStJalk4QjFCQ3E3VWdaems3TVhCUGNhaU9MNmJxUzRCdllUOVlNdXBFYm5MU0xzWHdTZjZEeXEvMVo2YnlRY2FGVHk4a1JBbGt0Nys0dGxRd3h6Q0d3cVJCWHpvdnJmdjV2UHNqNDBXcGd0bXJDUFEvT09uUWw4Tk5qbHlqVitnYzNpMWZkaSt1WC91ZWpZPXlKenZCeUZqVklNcHI4VXYiLCJleHAiOjE2MjA2Mzk0NzMsInNoYXJkX2lkIjoyMjE5OTYwNzMsInBkIjowfQ.xdy0QNXpsJRlPcxd9JZB92mj7Gm3F2OH11OvGCYmr0Y"
["h-captcha-response"] => string(1438) "P0_eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJwYXNza2V5IjoiczZuQ1JGZ3BzODU0djRWV0lFMFY0Z1R1ODRpOVJVdldNTnV4YUNYaVN1YUlwYlp3TFg3Y1pscDZ0MkhuOXNtd1pIZGNQV2xNb1I2aVdSWS9FS3ZpYVJ5YUhkSGJUdnU1TVo4OTFxWFRpeE5UbDBrUE1oMS93ODBkNVpTUXIzMkNNd0luVStsSDVCYURGcEs0MW1oMlgrRjZJZVpONFNaOG94TjB5WFgwQTBkRGFNSnhQaGtUZ1E1bjkwN3VyNzd3a2RTOVZ6UHhhNDVWei95NkFZcktHclhyZURxQms3T3JWYUdLaHJiL0U2QUo2WElDYnZCUlZBaEYvTXRuNXJ1azBJeit2RVVjK0Vqdm9LZnNUcFRxS3NRSkZjUE51WEtHdlRwZWozVExHUkZEd1N2L0JjWXVuUHZGY2crUzU2ejJjdWlhK2dER1VIMVYvWlI3YVVTd01UaFFpb3BxWVJOc2RrdGpUeG1rMGNRQ0dUZ2pHdGZQVmQ2cnp0RVAxaUkxY2ZNQ2NPVml4TER6SlNnL3ZrVDNXTUFpTldxWVFaZmtYUXJJWkFmSTE2ektIcjJMM2RmMUJKb2xwa1h2bytwMk5ROUVwRjFPbE10ZFQyVUJsZndLSlhpNDljTkRWeERjSUdkd2VyVHZiQUJmeVJIRDdFOHNZMElwTng2VmZwSXR2VEh4YUtuMGJwRXFveWg1dUxjTDdYblZPME80VUdsUWgyQTZVTFpGdUh3aERlbVdaN2JseXkxWDJTd1VBeHhvK2VTdi9Zem1YczJlUTJ4QThFazNjL0praHhWRWVvRnAwMDdHSFo1THh6T0txSkZoaUxOdExoOFhZRnpBMnF6dklFZ3d2U3VvcE8xL1pLWnJ0VlR6QTVncHBHejcwRmFnWlF1YkdkcGYwSWJwVGhPaVlnTVZ4L2JlWGxsQjBtS2luMWR6Y0JYRWNDdzVwblZPTjN3ai9KMUhlenN5S2ViaWN5VnhkME42VE93Rk5GYVowSGxXNWI2RThRMk9lazdhSHFwUEdVOEdsdmFIc2pZOHBtYi9mb2Rkdkg1OStJalk4QjFCQ3E3VWdaems3TVhCUGNhaU9MNmJxUzRCdllUOVlNdXBFYm5MU0xzWHdTZjZEeXEvMVo2YnlRY2FGVHk4a1JBbGt0Nys0dGxRd3h6Q0d3cVJCWHpvdnJmdjV2UHNqNDBXcGd0bXJDUFEvT09uUWw4Tk5qbHlqVitnYzNpMWZkaSt1WC91ZWpZPXlKenZCeUZqVklNcHI4VXYiLCJleHAiOjE2MjA2Mzk0NzMsInNoYXJkX2lkIjoyMjE5OTYwNzMsInBkIjowfQ.xdy0QNXpsJRlPcxd9JZB92mj7Gm3F2OH11OvGCYmr0Y"
["accept"] => string(1) "1"
["reg_key"] => string(16) "sn2GE4fLKhrDe87C"
["59f2265089781ba9a19f43ef2c213e8e9b31e9bf"] => string(19) "America/Los_Angeles"
["_xfToken"] => string(8) "********"
["xfRequestUri"] => string(493) "/frphforum/?__cf_chl_jschl_tk_=887f88e7b0eecf658425125d63c5927db8ea915d-1620638831-0-AeBgdrRe_7VIc6kRO93rWTn7ZlwLpahhkl9ji15JIsn-FnzchDVeqKXf-4QdLiIa0Tg_jiP1kttS-UynAMM2hYib4w89EszGISS3oypaqcDaK9_HS970-X8wuan1eeZ4X7S5LUgGJeB5GeKSPixpY-oAs52vLRvc4KfDl17Jg7sO0qb0xBRvMjdNDTqX-kPfKrZg9N6BuIjlMW6EQwMVXMiCONrDHdhRwx0HU1QczuYjwZRhMOzToVkEvC1z2kPkCmeXdsVwpr6T6Wux5xolsrMljSs7DbsrjltKKEgnPN-2odfSRj0K6H1A7amsjh49Cbn8qA4hcOIaZLKeSBZlvwWLeBZPy3AwF220M3kBToIVyM6JWTX5tZGdPQMyPv1OVfgzUYn40dRClkZPbFerXSA"
["_xfWithData"] => string(1) "1"
["_xfResponseType"] => string(4) "json"
}
}

 

[thetechguy]

If you can check it by turning of all the listeners, or by disabling every addon, so that you can confirm if its causing by some addons modifications or there is some issue on XF side.

 

[Cethlenn]

The only addons I have enabled right now are Xenforo Media Gallery and Xenforo Resource Manager.

 

[Mike]

The issue here is actually the moving process. The tool you used to dump the database from your old host hasn't brought over the data structure correctly -- it's missing AUTO_INCREMENT from at least some tables. I believe we have seen this in some cases. I think we've seen this issue with some versions of phpMyAdmin?

Realistically, you need to redo the dump process from the old site, ideally using a different tool (mysqldump via the command line is the official one) to ensure that the structure is brought over exactly as it was.

 

[Cethlenn]

The issue here is actually the moving process. The tool you used to dump the database from your old host hasn't brought over the data structure correctly -- it's missing AUTO_INCREMENT from at least some tables. I believe we have seen this in some cases. I think we've seen this issue with some versions of phpMyAdmin?

Realistically, you need to redo the dump process from the old site, ideally using a different tool (mysqldump via the command line is the official one) to ensure that the structure is brought over exactly as it was.

I am unable to do that, unfortunately, but I may be able to redo the dump process from the backup db.

 

[Cethlenn]

[Now that finals are over and I have brain space for this] How can I fix this issue if I am unable to dump from the old site?

 

[Kruzya]

Only with restoring all keys what XF creates into Database. Basically you can perform fresh install, dump ONLY DATA from already imported backup and import into fresh database.

Of course, add-ons can add own columns and keys, so ideally you need also install all add-ons. You can do this without any configuration.

 

[Mike]

Kruzya's idea is roughly right, though it can be quite awkward to actually attempt and you have to be quite careful how you do the restores.

The biggest problem is the fact that the tables affected are unknown (and the full effects are unknown). In this particular case, you could probably use something like phpMyAdmin to modify the definition of the user_id column in the xf_user table to have the AUTO_INCREMENT option enabled. (Note, it also needs to be the primary key, but I think this will likely have been maintained; if not, then the problems certainly go deeper.)

If done properly, that should fix this issue, but I would imagine there are going to be other tables with a similar issue

(Note: always take a backup before modifying the DB manually. If possible, its strongly recommended to use a command line tool like mysqldump to take the backup.)