Page MenuHomePhabricator

Error 1406: Data too long for column 'acr_storage_key' at row 1 in tables "account_requests" and "account_credentials"
Closed, ResolvedPublic

Description

Setup

  • MediaWiki 1.35.1 (3ff1919) 01:03, 7 February 2021
  • PHP 7.3.19-1~deb10u1 (apache2handler)
  • MariaDB 10.3.27-MariaDB-0+deb10u1 / MyISAM
  • Confirm User Accounts – (9b071fd) 13:50, 26 September 2020

Issue

[01f4a75d6b7732ae17051dae] /wiki/Special:RequestAccount Wikimedia\Rdbms\DBQueryError from line 1699 of /../w/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?

Error 1406: Data too long for column 'acr_storage_key' at row 1 (localhost:6363)
Function: UserAccountRequest::insertOn
Query: INSERT INTO `account_requests` (acr_id,acr_name,acr_email,acr_real_name,acr_registration,acr_bio,acr_notes,acr_urls,acr_type,acr_areas,acr_filename,acr_storage_key,acr_comment,acr_ip,acr_xff,acr_agent,acr_deleted,acr_email_token,acr_email_token_expires) VALUES (NULL,'Karsten Hoffmeyer','example@example.org','Karsten Hoffmeyer','20210223170501','I would like to test if account requesting works for this wiki. :)','','','0','',NULL,'DB99093932820210211:acctrequest:ip:2003%3Af1%3Ac718%3Ade00%3Aa5a0%3A793d%3Ac943%3A1324','','2001:f1:c716:de00:a5a0:793d:c943:1324','','Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.66 Safari/537.36',0,'86de58362674b2b26843370f869d51b1','20210524170501')

Backtrace

#0 /../w/includes/libs/rdbms/database/Database.php(1683): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /../w/includes/libs/rdbms/database/Database.php(1658): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /../w/includes/libs/rdbms/database/Database.php(1227): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /../w/includes/libs/rdbms/database/Database.php(2343): Wikimedia\Rdbms\Database->query(string, string, integer)
#4 /../w/includes/libs/rdbms/database/Database.php(2323): Wikimedia\Rdbms\Database->doInsert(string, array, string)
#5 /../w/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->insert(string, array, string)
#6 /../w/includes/libs/rdbms/database/DBConnRef.php(369): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#7 /../w/extensions/ConfirmAccount/includes/backend/UserAccountRequest.php(359): Wikimedia\Rdbms\DBConnRef->insert(string, array, string)
#8 /../w/extensions/ConfirmAccount/includes/business/AccountRequestSubmission.php(254): UserAccountRequest->insertOn()
#9 /../w/extensions/ConfirmAccount/includes/frontend/specialpages/actions/RequestAccount_body.php(343): AccountRequestSubmission->submit(RequestContext)
#10 /../w/extensions/ConfirmAccount/includes/frontend/specialpages/actions/RequestAccount_body.php(83): RequestAccountPage->doSubmit()
#11 /../w/includes/specialpage/SpecialPage.php(600): RequestAccountPage->execute(NULL)
#12 /../w/includes/specialpage/SpecialPageFactory.php(635): SpecialPage->run(NULL)
#13 /../w/includes/MediaWiki.php(307): MediaWiki\SpecialPage\SpecialPageFactory->executePath(Title, RequestContext)
#14 /../w/includes/MediaWiki.php(940): MediaWiki->performRequest()
#15 /../w/includes/MediaWiki.php(543): MediaWiki->main()
#16 /../w/index.php(53): MediaWiki->run()
#17 /../w/index.php(46): wfIndexMain()
#18 {main}

No I did not forget to run your application's database schema updater after upgrading.

Immediate mitigation
As long as no patch for this issue was merged the following database actions in MySQL will help the cause:

ALTER TABLE account_requests MODIFY acr_storage_key VARCHAR(100) NULL default '';
ALTER TABLE account_credentials MODIFY acd_storage_key VARCHAR(100) NULL default '';

Thanks go to @jcrespo for the suggestion!

Event Timeline

Hi,

I am not too familiar with this extension, but I think something like running:

ALTER TABLE account_requests MODIFY acr_storage_key VARCHAR(100) NULL default '';

should fix your immediate problem. There are no indexes on that column so it should be a "safe" change.

I think this table didn't have into account IPv6 format, so it doesn't have enough space for it. That, combined that you are probably running in strict mode (so it errors out rather than silently truncate the string) made it fail.

While I strongly recommend against MyISAM (specially in case of a crash), it was not the issue here.

For the maintainer, I am unsure what the maximum size should be, at the very least 86 bytes, but probably much more.

I am not too familiar with this extension, but I think something like running ... should fix your immediate problem.

Thanks a ton for this tip! I did and indeed requesting an account works now. My impression was that the account request process was a bit an the slow side in comparison to what I am used to but this might perhaps have had a different reason at that very moment.

I think this table didn't have into account IPv6 format, so it doesn't have enough space for it.

Indeed, it looks like this is the cause. I still need to get used to using IPv6 when browsing. :)

While I strongly recommend against MyISAM (specially in case of a crash), it was not the issue here.

Indeed, I believe MyISAM is a bit scary, however I follow the "Do not touch a running system strategy here". This combined with frequent backups ...

For the maintainer, I am unsure what the maximum size should be, at the very least 86 bytes, but probably much more.

Hoping for a fix including a back-port to REL1_35 rather sooner than later!

Kghbln triaged this task as High priority.Feb 23 2021, 8:56 PM
Kghbln added a subscriber: ashley.

I hope nobody minds me triaging this one with "High Priority". If yes please "revert".

@ashley You have done some work recently on this extension. Perhaps you would like to have a peep at this since ShoutWiki is probably affected in a much higher proportion? If not just disregard this ping.

Apparently table account_credentials has the very same field acd_storage_key

Issue

[6e0d41b72d5f8a956b3f3329] /w/index.php?title=Special:CreateAccount&returnto=Special:ConfirmAccounts/authors Wikimedia\Rdbms\DBQueryError from line 1699 of /../w/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?

Error 1406: Data too long for column 'acd_storage_key' at row 1 (localhost:5252)
Function: AccountConfirmSubmission::completeRequest
Query: INSERT INTO `account_credentials` (acd_user_id,acd_real_name,acd_email,acd_email_authenticated,acd_bio,acd_notes,acd_urls,acd_ip,acd_xff,acd_agent,acd_filename,acd_storage_key,acd_areas,acd_registration,acd_accepted,acd_user,acd_comment,acd_id) VALUES (835,'Karsten Hoffmeyer','example@example.org','20210223203932','I would like to test if account requesting works for this wiki.','','','2001:f1:c716:de00:a5a0:793d:c943:1324','','Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.66 Safari/537.36',NULL,'DB99093932820210211:acctrequest:ip:2003%3Af1%3Ac718%3Ade00%3Aa5a0%3A793d%3Ac943%3A1324','','20210223203844','20210224082655',825,'',NULL)

Backtrace

#0 /../w/includes/libs/rdbms/database/Database.php(1683): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /../w/includes/libs/rdbms/database/Database.php(1658): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /../w/includes/libs/rdbms/database/Database.php(1227): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /../w/includes/libs/rdbms/database/Database.php(2343): Wikimedia\Rdbms\Database->query(string, string, integer)
#4 /../w/includes/libs/rdbms/database/Database.php(2323): Wikimedia\Rdbms\Database->doInsert(string, array, string)
#5 /../w/extensions/ConfirmAccount/includes/business/AccountConfirmSubmission.php(284): Wikimedia\Rdbms\Database->insert(string, array, string)
#6 /../w/extensions/ConfirmAccount/includes/business/AccountConfirmSubmission.php(66): AccountConfirmSubmission->completeRequest(RequestContext)
#7 /../w/extensions/ConfirmAccount/includes/business/ConfirmAccountPreAuthenticationProvider.php(118): AccountConfirmSubmission->submit(RequestContext)
#8 /../w/includes/auth/AuthManager.php(2470): ConfirmAccountPreAuthenticationProvider->postAccountCreation(User, User, MediaWiki\Auth\AuthenticationResponse)
#9 /../w/includes/auth/AuthManager.php(1546): MediaWiki\Auth\AuthManager->callMethodOnProviders(integer, string, array)
#10 /../w/includes/auth/AuthManager.php(1163): MediaWiki\Auth\AuthManager->continueAccountCreation(array)
#11 /../w/includes/specialpage/AuthManagerSpecialPage.php(376): MediaWiki\Auth\AuthManager->beginAccountCreation(User, array, string)
#12 /../w/includes/specialpage/AuthManagerSpecialPage.php(502): AuthManagerSpecialPage->performAuthenticationStep(string, array)
#13 /../w/includes/htmlform/HTMLForm.php(707): AuthManagerSpecialPage->handleFormSubmit(array, VFormHTMLForm)
#14 /../w/includes/specialpage/AuthManagerSpecialPage.php(435): HTMLForm->trySubmit()
#15 /../w/includes/specialpage/LoginSignupSpecialPage.php(319): AuthManagerSpecialPage->trySubmit()
#16 /../w/includes/specialpage/SpecialPage.php(600): LoginSignupSpecialPage->execute(NULL)
#17 /../w/includes/specialpage/SpecialPageFactory.php(635): SpecialPage->run(NULL)
#18 /../w/includes/MediaWiki.php(307): MediaWiki\SpecialPage\SpecialPageFactory->executePath(Title, RequestContext)
#19 /../w/includes/MediaWiki.php(940): MediaWiki->performRequest()
#20 /../w/includes/MediaWiki.php(543): MediaWiki->main()
#21 /../w/index.php(53): MediaWiki->run()
#22 /../w/index.php(46): wfIndexMain()
#23 {main}

To mitigate this you have to also do ALTER TABLE account_credentials MODIFY acd_storage_key VARCHAR(100) NULL default ''; for this database table.

Note
The accounts are being created even if the above error is shown. Thus you get an "Account name already in use" message once you would like to confirm the account again.

I have updated the task description for others to easily see how to fix until a merged solution is available.

Kghbln renamed this task from DBQueryError from line 1699 of Database.php / Error 1406: Data too long for column 'acr_storage_key' at row 1 to Error 1406: Data too long for column 'acr_storage_key' at row 1 in tables "account_requests" and "account_credentials".Feb 24 2021, 9:26 AM

Change 674824 had a related patch set uploaded (by Vedmaka Wakalaka; author: Vedmaka Wakalaka):
[mediawiki/extensions/ConfirmAccount@master] Fixes the $key variable being shadowed for the UserAccountRequest by renaming it to $storageKey

https://gerrit.wikimedia.org/r/674824

One thing, the 100 bytes/chacters in length was just a supposition. Please suggest a better theoretical limit, if it makes sense to accomodate for long IPv6 addresses. It is ok for varchar maximum length to be quite long as dynamic length Myisam and Innodb storage engines will just use the actual one, and memory storage for temporary tables is slowly being phased out.

This suggests to reserve up to 45 characters just for the address: https://stackoverflow.com/questions/166132/maximum-length-of-the-textual-representation-of-an-ipv6-address

The fields that actually store the IP addresses are the acr_ip / acd_ip and both are varbinary(255) so I assume there are no issues with the length right now.

The origin of the problem was that $key variable was in certain conditions shadowed by another one leading to a name collision and making the acr_storage_key field written with a value that should never be there.

Change 674824 merged by jenkins-bot:
[mediawiki/extensions/ConfirmAccount@master] Fixes the $key variable being shadowed for the UserAccountRequest by renaming it to $storageKey

https://gerrit.wikimedia.org/r/674824

@Vedmaka Cool! Great effort. I guess it will make sense to back-port to MW 1.35 LTS?!

Change 675372 had a related patch set uploaded (by Kghbln; author: Vedmaka Wakalaka):
[mediawiki/extensions/ConfirmAccount@REL1_35] Fixes the $key variable being shadowed for the UserAccountRequest by renaming it to $storageKey

https://gerrit.wikimedia.org/r/675372

I just created the back-port to REL_35. I will be nice to get a review and ideally a +2 for this.

Change 675372 merged by jenkins-bot:
[mediawiki/extensions/ConfirmAccount@REL1_35] Fixes the $key variable being shadowed for the UserAccountRequest by renaming it to $storageKey

https://gerrit.wikimedia.org/r/675372

Thanks a bunch! Much appreciated!!