SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Duplicate INSERT Problem

    I have a "Words Made" table for a Facebook word game application. Recently a user has built a word using his letters at hand, resulting a duplicate INSERT to the database, with identical timestamps.

    Code:
    ID	USERID		WORD	SCORE	DATE
    569	702953774  	APPLE  	66  	2009-11-04 01:50:45
    570	702953774 	APPLE 	66 	2009-11-04 01:50:45
    The application checks the letters that the user has and rejects the entry if the user does not have all letters needed to build the word.

    But this time the second request is sent before the letters (A,P,P,L,E) removed from the user letters list. It's a duplicate record entered just at the same second anyway.

    What might be the reason?

    I'm not sure but this might not be related with my application at all; maybe Facebook application wrapper made two simultaneous requests.
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter

  2. #2
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    Are you able to provide a bit more information, such as the script you're running?

    Cheers

  3. #3
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure.

    -The application uses PHP.
    -User enters words into an HTML input box.
    -The input is verified (check user has the letters in his inventory, then check the word in the dictionary).
    -A valid input is processed in a function, first used letters removed from the inventory with an UPDATE query then relevant results are inserted into the "words made" table with an INSERT query, like:

    INSERT INTO tableName ( id, userid, word, score, date ) VALUES ( NULL, '$userid', '$word', '$score', NOW() )
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter

  4. #4
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    Without seeing the actual code I can't really think of much other than accidental looping, and functions being called twice.

    Perhaps after the sql is run get it to print something to the screen, and trace it back through function calls until it stops printing twice?

  5. #5
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well it's hard to test. I have around 600 words made by around 120 users and this happened just once.

    And even if the function executed twice, the validation would fail second time as the user most probably won't have the needed letters.

    But if the UPDATE query (which removes the used letters) is delayed until the second request (is it possible?) only then it's possible to pass validation. But this time there must be two different timestamps recorded I guess.
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter

  6. #6
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    If a query after that insert failed, could the whole thing possibly have been called a second time?

    Perhaps look at implementing transactions, so if something later fails it just rolls back?

  7. #7
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it standart behaivour (in PHP or MySQL) to call functions or queries again if something goes wrong? I really don't know.

    A quick question: If I had recorded the time in miliseconds would there be a difference between two?
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter

  8. #8
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    It's interesting that this only happened once at that would imply that it was a one off thing, and is difficult to reproduce.

    Trying in milliseconds would probably show a different value, but it wouldn't actually be of much use as you don't know whether that means two separate page calls, or an error in the coding :S

    Without seeing the actual code I'm not really sure.

  9. #9
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now I wonder is it possible that the following code inserts two identical records.

    I think only possibility is, if the first UPDATE query fails.

    PHP Code:
    $validFlag=validateWord('APPLE');
    if (
    $validFlag) {
        
    removeUsedLetters('APPLE'); //UPDATE query in function
        
    insertValidWord('APPLE'); //INSERT query in function
    }

    //DO THE SAME SECOND TIME
    $validFlag=validateWord('APPLE');
    if (
    $validFlag) {
        
    removeUsedLetters('APPLE'); //UPDATE query in function
        
    insertValidWord('APPLE'); //INSERT query in function

    Still, what makes the script running twice is not clear to me.
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter

  10. #10
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    I agree, if the update fails it would cause that issue. Perhaps do a check on the update.

    PHP Code:
    function useWord($word){
     if (!
    validateWord($word)) return false;
     if(! 
    removeUsedLetters($word)) return false;
     if(!
    insertValidWord($word)) return false;
     return 
    true;
    }
    echo 
    useWord('APPLE') ? 'Used apple' 'Problem occurred'

  11. #11
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay sounds good. I know I need to catch errors when a query returns false but I did not add error checking to the UPDATE query.

    I think this should be enough to avoid such errors in the future.

    But what if the user has two P's two E's and two T's in inventory, entered the word 'PET' and somehow two calls made again?

    We still have no clue what made the script run twice, do we? By the way I'm not disabling the submit button; can two very quick mouse clicks or ENTERs cause the POST data sent twice?
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter

  12. #12
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    Well if they do that then just get get double points for being lucky

    Two submits could cause it, however the chances of it being in the same second are relatively low - but I would definitely suggest disabling the submit after being clicked once.

  13. #13
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help. In case you wonder how it looks:

    Word Game Page: http://gamevase.com/screen.png
    Application Home: http://gamevase.com/screen2.png
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter

  14. #14
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    Pity I don't understand the language

  15. #15
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for that I would invite you to play if it's in English.
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •