XF 1.5 Duplicate entry 'usernamehere' for key 'username'

[Maester Aemon]

Hi. I am getting mysqli error multiple times here is the error.

Error Info
Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Duplicate entry 'ckyablyat6996' for key 'username' - library/Zend/Db/Statement/Mysqli.php:214
Generated By: Unknown Account, 3 minutes ago
Stack Trace
#0 /public/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /public/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /public/library/Zend/Db/Adapter/Abstract.php(574): Zend_Db_Adapter_Abstract->query('INSERT INTO `xf...', Array)
#3 /public/library/XenForo/DataWriter.php(1638): Zend_Db_Adapter_Abstract->insert('xf_user', Array)
#4 /home/nginx/domains/domain/public/library/XenForo/DataWriter.php(1627): XenForo_DataWriter->_insert()
#5 /home/nginx/domains/domain/public/library/XenForo/DataWriter.php(1419): XenForo_DataWriter->_save()
#6 /public/library/XenForo/ControllerPublic/Register.php(416): XenForo_DataWriter->save()
#7 /public/library/XenForo/FrontController.php(351): XenForo_ControllerPublic_Register->actionRegister()
#8 /public/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#9 /public/index.php(13): XenForo_FrontController->run()
#10 {main}
Request State
array(3) {
  ["url"] => string(46) "domain/register/register"
  ["_GET"] => array(1) {
    ["/register/register"] => string(0) ""
  }
  ["_POST"] => array(17) {
    ["username"] => string(0) ""

{USER INFO HERE}

  }
}

 

[Mike]

Was this a one off or are you getting it consistently? If you're getting it consistently, I would guess it could be an add-on. If it was a one off, you can likely disregard it.

 

[Maester Aemon]

Was this a one off or are you getting it consistently? If you're getting it consistently, I would guess it could be an add-on. If it was a one off, you can likely disregard it.

I am getting this over and over again.

I don't know which addon it would be as its not saying anything about addon in the error.

The addons I have are

Change Post Date 1.0.2a


Chat 1.21.7


Lazy Image Loader 1.1.1


MobileRead Profile BB Code 2.0


Resend Confirmation Mail 1.0


Smiley Manager 2.2.0


XenForo Enhanced Search 1.1.3


[AD] Donation Manager 1.0.4 Patch 1


[bd] Attachment Store 1.2.0


[bd] Medal 1.5.0b


[bd] Paygates 1.5.1


[bd] Tag Me 3.2.0


[bd] Widget Framework 2.5.9


[ITD] Embeded Flash Games in BBCode Media Sites. 1.0.0


[rellect] AdBlock Detector 1.7.3


[rellect] Nodes Grid 2.0.2


[XenMods] ElasticSearch Essentials 1.0.0

 

[Mike]

Nothing obvious, but you'll need to disable them all (via the ACP) and confirm whether the issue still happens.

 

[Xon]

I think there is a race condition in the registration code related to the username checking.

Are these legit accounts signing up? I used to get these all the time before I banned an IP range being used extensively by bots attempting (and failing) to register.

 

[Maester Aemon]

It is not related to an addon.
When a user tries to register it just loads (registers his account but does not say anything) and keeps loading, when the user tries to register again it will give him an error that something went wrong.

I think that this is because I'm using Akismet to check accounts, I guess it ****s it up.

 

[Maester Aemon]

Nevermind, its still happening but less frequent. I assume its all because of the long load time when registering, no idea why its slow nor how I can improve it.

I think there is a race condition in the registration code related to the username checking.

Are these legit accounts signing up? I used to get these all the time before I banned an IP range being used extensively by bots attempting (and failing) to register.

Yup its all legit users.

 

[Mike]

That does sound a race condition then and I'm certainly aware it's possible, but normally it would be a small window. Anti-spam checking could create potential challenges, particularly if they're very slow to trigger. (Note that Akismet is a content check, it's not involved in registration; StopForumSpam is though.)

Ideally, you'll want to figure out why it's that slow. It sounds like there could be an issue regarding outgoing connections, perhaps leading to them just timing out rather than being blocked quickly. This could be firewall related.

 

[TheLaw]

Don't know if this helps. I am noticing the same issue. I now realize that I have social media registration turned on and that is the only time it happens - with Facebook accounts. It might be that most will use that option to quick register. I'll review logs going forward since I've deleted the old and let you know if I see something. I have "Prevent Discouraged IP Addresses from Registering" turned on and had the SFS on but without a Honeypot key entered. I've found that there are so many issues with shared IP addresses coming from cable providers and would get too many requests for manual approval. It's not an XF problem but an Internet issue....

 

[markku]

I have this same issue. Get duplicate entries exactly like this multiple times a week. Sometimes a day goes by I don't get it, sometimes I get 3-4 per day.

Very annoying, and haven't been able to pinpoint it to anything.

 

[kontrabass]

I'm getting this a couple times a day as well. Only with Facebook registrations. Anyone else in this thread running the TPU anti-spam addon? @TheLaw @markku

 

[markku]

I'm getting this a couple times a day as well. Only with Facebook registrations. Anyone else in this thread running the TPU anti-spam addon? @TheLaw @markku

I am running TPU anti-spam.

 

[TheLaw]

Turned off TPU anti-spam (Detect Spam Registrations) a while ago. Now I'm not seeing the message. Way too many false positives being flagged among other things I enjoy tracking as they pass through (or try to) my system.

 

[markku]

I'm getting this a couple times a day as well. Only with Facebook registrations. Anyone else in this thread running the TPU anti-spam addon? @TheLaw @markku

Hmm, I've started paying attention to the error messages more, and you're right, this only happens for Facebook registrations.

I haven't yet tested disabling TPU since it's an essential add-on...

 

[kontrabass]

@W1zzard several of us seem to be having this same issue with your wonderful and essential TPU: Detect and Block Spam Registrations
add-on, and Facebook registrations causing these errors

Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Duplicate entry 'Vanzany' for key 'username' - library/Zend/Db/Statement/Mysqli.php:214
Generated By: Unknown Account, Today at 7:23 AM
Stack Trace
#0 /home/talkbass/html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/talkbass/html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/talkbass/html/library/Zend/Db/Adapter/Abstract.php(574): Zend_Db_Adapter_Abstract->query('INSERT INTO `xf...', Array)
#3 /home/talkbass/html/library/XenForo/DataWriter.php(1638): Zend_Db_Adapter_Abstract->insert('xf_user', Array)
#4 /home/talkbass/html/library/XenForo/DataWriter.php(1627): XenForo_DataWriter->_insert()
#5 /home/talkbass/html/library/XenForo/DataWriter.php(1419): XenForo_DataWriter->_save()
#6 /home/talkbass/html/library/XenForo/ControllerPublic/Register.php(740): XenForo_DataWriter->save()
#7 /home/talkbass/html/library/XenForo/FrontController.php(351): XenForo_ControllerPublic_Register->actionFacebookRegister()
#8 /home/talkbass/html/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#9 /home/talkbass/html/index.php(13): XenForo_FrontController->run()
#10 {main}
Request State
array(3) {
  ["url"] => string(50) "http://www.talkbass.com/register/facebook/register"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(11) {
    ["username"] => string(7) "Vanzany"
    ["dob_month"] => string(1) "1"
    ["dob_day"] => string(2) "16"
    ["dob_year"] => string(4) "1963"
    ["custom_fields_shown"] => array(2) {
      [0] => string(5) "cuser"
      [1] => string(11) "disclosures"
    }
    ["custom_fields"] => array(1) {
      ["disclosures"] => string(0) ""
    }
    ["timezone"] => string(14) "America/Denver"
    ["agree"] => string(1) "1"
    ["_xfToken"] => string(8) "********"
    ["redirect"] => string(24) "http://www.talkbass.com/"
    ["fb_token"] => string(0) ""
  }
}

I'll try disabling SFS within your add-on to see if that helps....

 

[Paul B]

If you believe the problem to be with the add-on, you should post in the dedicated resource thread.

 

[Mike]

I would guess it's just significantly extending the race condition window and people are submitting multiple times? Based on the user info in the latest error, I think that's a spammer (a real user wouldn't be submitting in those fields). If that's the case, it's not especially easy to fix but it isn't really harmful.

 

[W1zzard]

I would guess it's just significantly extending the race condition window and people are submitting multiple times? Based on the user info in the latest error, I think that's a spammer (a real user wouldn't be submitting in those fields). If that's the case, it's not especially easy to fix but it isn't really harmful.

I think that's what it is. The addon performs some checks (lookup AS/country, check open ports etc), which take time.

The form gets disabled on submit via JS, so a regular user won't be able to click it multiple times.

 

[Liam W]

The form gets disabled on submit via JS, so a regular user won't be able to click it multiple times

Unless it takes so long that the timeout passes and the button reactivates, which while unlikely is possible.

 

[markku]

Hmm, I was wrong.

For us, this is not just Facebook related.

Quite often also /register/register triggers this.

Example:

Error Info
Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Duplicate entry 'vinar' for key 'username' - library/Zend/Db/Statement/Mysqli.php:214

Generated By: Unknown Account, 1 minute ago

Stack Trace
#0 /home/domains/xxx.com/public_html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/domains/xxx.com/public_html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/domains/xxx.com/public_html/library/Zend/Db/Adapter/Abstract.php(574): Zend_Db_Adapter_Abstract->query('INSERT INTO `xf...', Array)
#3 /home/domains/xxx.com/public_html/library/XenForo/DataWriter.php(1638): Zend_Db_Adapter_Abstract->insert('xf_user', Array)
#4 /home/domains/xxx.com/public_html/library/XenForo/DataWriter.php(1627): XenForo_DataWriter->_insert()
#5 /home/domains/xxx.com/public_html/library/XenForo/DataWriter.php(1419): XenForo_DataWriter->_save()
#6 /home/domains/xxx.com/public_html/library/XenForo/ControllerPublic/Register.php(416): XenForo_DataWriter->save()
#7 /home/domains/xxx.com/public_html/library/UserEss/ControllerPublic/Register.php(68): XenForo_ControllerPublic_Register->actionRegister()
#8 /home/domains/xxx.com/public_html/library/XenForo/FrontController.php(351): UserEss_ControllerPublic_Register->actionRegister()
#9 /home/domains/xxx.com/public_html/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#10 /home/domains/xxx.com/public_html/index.php(13): XenForo_FrontController->run()
#11 {main}

Request State
array(3) {
["url"] => string(46) "https://www.xyz.com/register/register"
["_GET"] => array(1) {
["/register/register"] => string(0) ""
}
["_POST"] => array(16) {
["username"] => string(0) ""
["e3d958a62d0ac1a6f59ca3849936bcd7"] => string(5) "vinar"
["4802559bacb3f374577ff0d30dbf2ef6"] => string(23) "yyyyyy@xxxxx.com"
["9f62445fd4fa929c7806483b0174d875"] => string(0) ""
["77df88dd5f5b155cc78b5cc9f405f323"] => string(4) "male"
["b43fddfab61a08b7705ef4e3976d7c9f"] => array(1) {
["sincewhen"] => string(6) "2/2016"
}
["custom_fields_shown"] => array(1) {
[0] => string(13) "sincewhen"
}
["02ffe5f7c8065b2b3dd1905e55e21893"] => string(12) "Asia/Kolkata"
["slidercaptcha_validate_time"] => string(10) "1454734768"
["slidercaptcha_validate_hash"] => string(40) "d74e064730d13266d552cc5b909164ee78853706"
["slidercaptcha_loaded"] => string(4) "true"
["slidercaptcha_fallback"] => string(9) "ReCaptcha"
["agree"] => string(1) "1"
["_xfToken"] => string(8) "********"
["reg_key"] => string(32) "0b4a6493c4c14661baeeeab6a2b8d812"
["timetaken"] => string(5) "54764"
}
}



For some reason, I can always see "/library/UserEss/ControllerPublic/Register.php(68): XenForo_ControllerPublic_Register->actionRegister()" in the Server Error Log when this error happens.

And it happens around 10-20% for all of our registrations, which seems very high percentage? And these signups are from real people, from all over the world.

@Daniel Hood can User Essentials somehow be responsible for this error?