WordPress.org

Ready to get started?Download WordPress

Forums

WP-UserOnline
WordPress database error Duplicate entry errors (27 posts)

  1. metal450
    Member
    Posted 2 years ago #

    This plugin seems to be generating a large number of database errors (in error_log):

    WordPress database error Duplicate entry '2010-10-06 06:35:31-xxx.xxx.xxx.xxx-Mozilla/4.0 (compatible;)' for key 'useronline_id' for query INSERT INTO wp_useronline (user_type,user_id,user_name,user_ip,user_agent,page_title,page_url,referral) VALUES ('guest','0','Guest','xxx.xxx.xxx.xxx','Mozilla/4.0 (compatible;)','xxxxxx','/a-night-in-a-slovenian-prison','') made by require, require_once, include, get_header, locate_template, load_template, require_once, wp_head, do_action, call_user_func_array, UserOnline_Core->record

    They don't occur all the time, but they are fairly consistent - i.e. maybe a few every day.

    http://wordpress.org/extend/plugins/wp-useronline/

  2. metal450
    Member
    Posted 2 years ago #

    It seems as though this problem has existed for more than four and a half years:

    http://wordpress.org/support/topic/plugin-wp-useronline-plugin-28-wp301-broken

    And was reported a year ago:

    http://wordpress.org/support/topic/plugin-wp-useronline-wp-useronline-developing-error-messages

    By a few different users:

    http://wordpress.org/support/topic/plugin-wp-useronline-plugin-28-wp301-broken

    ...Would be very nice to get this fixed up, as I find myself deleting error_logs virtually every time I ftp into my server :P

  3. scribu
    Member
    Plugin Author

    Posted 2 years ago #

    I can't replicate the issue, so patches welcome.

  4. metal450
    Member
    Posted 2 years ago #

    Any information I could provide from my setup that might help? I did delve into the code a bit (obviously it's coming from core.php, line 155 in v1.81), but was not able to figure out much...

  5. metal450
    Member
    Posted 2 years ago #

    Actually, perhaps this is overly obvious, but is that "Purge table" deletion immediately before the insert clearing previous records for the same user, so the new record can be inserted? If so, wouldn't the solution just be something like changing:

    OR timestamp < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL %d SECOND)

    to

    OR timestamp <= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL %d SECOND)

    so that if there's already a record of the current user at the SAME moment (or earlier) it'll be deleted?

  6. metal450
    Member
    Posted 2 years ago #

    ...No wait, that wouldn't be it. But what about just adding a WHERE to the insertion, i.e. something like:

    INSERT INTO wp_useronline (blahblah) VALUES (blahblah) WHERE not exists (SELECT * from wp_useronline WHERE user_ip = %s AND timestamp = CURRENT_TIMESTAMP AND user_type = $user_type);

    Seems like that should *always* prevent such a duplicate entry, should it not?

  7. scribu
    Member
    Plugin Author

    Posted 2 years ago #

    Alternatively, ON DUPLICATE KEY UPDATE could be used:

    http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

    If you manage to get a query that no longer produces errors, please let me know.

  8. metal450
    Member
    Posted 2 years ago #

    Honestly, I've never worked directly with the database and wouldn't really be comfortable modifying the queries for fear of breaking the functionality or causing some otherwise unknown side-effect; I was just hoping to provide you some more insight on what maybe causing this (clearly long-standing) issue with your otherwise flawless (as I can tell) plugin :P

    I came by all those other error reports just while Googling for a hopeful solution; the WHERE idea came from the first FAQ here: http://www.techonthenet.com/sql/insert.php

  9. scribu
    Member
    Plugin Author

    Posted 2 years ago #

  10. metal450
    Member
    Posted 2 years ago #

    Sorry? Was that meant to have a link to a different thread/post? :)

  11. scribu
    Member
    Plugin Author

    Posted 2 years ago #

  12. metal450
    Member
    Posted 2 years ago #

    Nice! Expanded it to 39, will let you know if I keep getting those duplicate error logs :)

  13. metal450
    Member
    Posted 2 years ago #

    I'm still getting these errors. I believe it's happening most often during moments of very high server load, so that likely has something to do with it. In any case, if the insert query itself takes into consideration the possibility of duplicates (as in yours and my proposed solutions above), it should eliminate the risk of this happening once and for all...

  14. KamalH
    Member
    Posted 2 years ago #

    It's been there forever.. To resolve the problem simply use the Mingle Users Online plugin or else the real-time beta feature on Google analytics..

  15. metal450
    Member
    Posted 2 years ago #

    Mingle Users Online is a branch of this plugin and uses the exact same insert query. I don't see how the bug wouldn't occur with that plugin but would with this.

    Google Analytics wouldn't able to lookup the actual WordPress user account, just tell you that "someone" is online.

  16. KamalH
    Member
    Posted 2 years ago #

    Right, Mingle and the original plugin are the same. But the fact is the original plugin is full of bugs whereas this is not.

    Analytics will of course not tell you who is online but otherwise it's a great resource to know how many people are online and who is reading what..

  17. metal450
    Member
    Posted 2 years ago #

    Again: this particular bug comes from the fact that the insert query doesn't verify that the line which is being inserted doesn't already exist. The Mingle version uses the exact same insert query, so the same bug will exist.

    True that Analytics is good, and I use that too. But this plugin is called USER online. Analytics can't tell you which USER is online, so it's in no way a replacement for this plugin...

  18. KamalH
    Member
    Posted 2 years ago #

    Hmmm anyways all I can tell you is, I was using wp-useronline and the main bug that I found was the gibberish characters in place of the user online info.

    But Mingle user online seems to be just perfect. My blogs are not multi-authored. Anyways what is the main problems you guys facing with the original plugin??

  19. metal450
    Member
    Posted 2 years ago #

    ...It's documented above. This thread has nothing to do with character encoding whatsoever.

  20. KamalH
    Member
    Posted 2 years ago #

    I hope that the developers of this plugin look it into all these problems some day and do something about it!

  21. metal450
    Member
    Posted 2 years ago #

    Plugins on WordPress.org are free and opensource. Developers contribute however much or little time they see fit, so if you're anxious to get things fixed you should probably submit your own specific solutions/proposals (as I've done above) or offer to pay for their time.

  22. KamalH
    Member
    Posted 2 years ago #

    Paying the developers seems to be the best way to get things done.. I hope you find a solution to your problems as well..

  23. scribu
    Member
    Plugin Author

    Posted 2 years ago #

    The duplicate errors still don't make any sense. The unique constraint looks like this:

    UNIQUE KEY useronline_id ( timestamp, user_type, user_ip )

    But, before the INSERT query, there's another query that clears all records with the same user_ip:

    DELETE FROM $wpdb->useronline
    WHERE user_ip = %s
    OR timestamp < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL %d SECOND)

    So, it's theoretically impossible to trigger a unique error, since there would be no records with the same user_ip, let alone with an exact combination of user_ip + user_type + timestamp.

    Could you paste the exact error text?

  24. metal450
    Member
    Posted 2 years ago #

    >>Could you paste the exact error text?

    One is copied in the first post above; unfortunately I deleted the most recent error_log, so if you need a new one I'd have to wait until it happens again. It's much less frequent now that the ip field was expanded, but it does still happen periodically.

    >>But, before the INSERT query, there's another query that clears all records with the same user_ip

    Well, as mentioned it seems to happen when the server is severely lagging. It also nearly always occurs in "chunks" - i.e. it happens 20 times in a few seconds...and then not again for a week. So perhaps i.e. while the server is lagging it falls behind on queries or something. Which is why, if the check for duplicates were done within one single query (rather than two closely-spaced but separate queries), it should be completely and utterly impossible for it to happen.

  25. scribu
    Member
    Plugin Author

    Posted 2 years ago #

    Ok, the development version (2.82-beta2) uses REPLACE instead of INSERT.

    Please try it out and report back.

  26. metal450
    Member
    Posted 2 years ago #

    Just installed it and will let you know if that error pops up again - however, in doing a quick post-update test, I found something that's definitely not right. Here's what I did:

    1) Connect to a VPN, login to my WordPress site as User1 via Chrome, view Page1
    2) Disconnect from the VPN, login to my WordPress site as Admin via Firefox, and view the "users online now" page.

    Thus, I explicitly viewed a known page from a different browser and IP address than the admin who is checking users online. The current page for User1 was *not* Page1. Furthermore, I noticed that it showed a GoogleBot online on Page1. It looks like signals are getting crossed - you might want to run a similar test.

  27. metal450
    Member
    Posted 2 years ago #

    Hang on - I just tried it a few more times and couldn't get it to happen again. Strange, maybe it was a fluke - though I am pretty certain I did the test right the first time. Maybe just something leftover because I'd JUST updated/activated the plugin or something...

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic