Error: Database Duplicate entry in woocommerce_sessions

  • Resolved nminkov

    (@nminkov)


    10 years, 3 months ago

    Hi,

    For one week, its the second time I get Mysql error in my logs: Duplicate entry in woocommerce_sessions

    WordPress Duplicate entry 'aa45d9a84b52fe973d32615b35712d0b' for key 'PRIMARY' pour la requ\xc3\xaate INSERT INTOXXX_woocommerce_sessions(session_key,session_value,session_expiry) VALUES ('aa45d9a84b52fe973d32615b35712d0b', 'a:19:{s:21:\\&quot;removed_cart_contents\\&quot;;s:6:\\&quot;a:0:{}\\&quot;;s:10:\\&quot;wc_notices\\&quot;;s:345:\\&quot;a:1:{s:7:\\&quot;success\\&quot;;a:2:{i:0;s:148:\\&quot;<a href=\\&quot;http://www.somewebsite.com/store\\&quot; class=\\&quot;button wc-forward\\&quot;>Poursuivre les achats</a> &laquo;CopyTrans&raquo; a \xc3\xa9t\xc3\xa9 ajout\xc3\xa9 \xc3\xa0 votre panier.\\&quot;;i:1;s:145:\\&quot;<a href=\\&quot;http://www.somewebsite.com/store\\&quot; class=\\&quot;button wc-forward\\&quot;>Poursuivre les achats</a> &laquo;4 Pack&raquo; a \xc3\xa9t\xc3\xa9 ajout\xc3\xa9 \xc3\xa0 votre panier.\\&quot;;}}\\&quot;;s:4:\\&quot;cart\\&quot;;s:403:\\&quot;a:1:{s:32:\\&quot;c4851e8e264415c4094e4e85b0baa7cc\\&quot;;a:9:{s:10:\\&quot;product_id\\&quot;;i:1227;s:12:\\&quot;variation_id\\&quot;;i:0;s:9:\\&quot;variation\\&quot;;a:0:{}s:8:\\&quot;quantity\\&quot;;i:1;s:10:\\&quot;line_total\\&quot;;d:29.989999999999998436805981327779591083526611328125;s:8:\\&quot;line_tax\\&quot;;i:0;s:13:\\&quot;line_subtotal\\&quot;;d:29.989999999999998436805981327779591083526611328125;s:17:\\&quot;line_subtotal_tax\\&quot;;i:0;s:13:\\&quot;line_tax_data\\&quot;;a:2:{s:5:\\&quot;total\\&quot;;a:0:{}s:8:\\&quot;subtotal\\&quot;;a:0:{}}}}\\&quot;;s:15:\\&quot;applied_coupons\\&quot;;s:6:\\&quot;a:0:{}\\&quot;;s:23:\\&quot;coupon_discount_amounts\\&quot;;s:6:\\&quot;a:0:{}\\&quot;;s:27:\\&quot;coupon_discount_tax_amounts\\&quot;;s:6:\\&quot;a:0:{}\\&quot;;s:19:\\&quot;cart_contents_total\\&quot;;d:29.989999999999998436805981327779591083526611328125;s:5:\\&quot;total\\&quot;;i:0;s:8:\\&quot;subtotal\\&quot;;d:29.989999999999998436805981327779591083526611328125;s:15:\\&quot;subtotal_ex_tax\\&quot;;d:29.989999999999998436805981327779591083526611328125;s:9:\\&quot;tax_total\\&quot;;i:0;s:5:\\&quot;taxes\\&quot;;s:6:\\&quot;a:0:{}\\&quot;;s:14:\\&quot;shipping_taxes\\&quot;;s:6:\\&quot;a:0:{}\\&quot;;s:13:\\&quot;discount_cart\\&quot;;i:0;s:17:\\&quot;discount_cart_tax\\&quot;;i:0;s:14:\\&quot;shipping_total\\&quot;;i:0;s:18:\\&quot;shipping_tax_total\\&quot;;i:0;s:9:\\&quot;fee_total\\&quot;;i:0;s:4:\\&quot;fees\\&quot;;s:6:\\&quot;a:0:{}\\&quot;;}', 1455958760) faite par shutdown_action_hook, do_action('shutdown'), call_user_func_array, WC_Session_Handler->save_data, W3_Db->insert, W3_DbCache->insert, W3_DbCallUnderlying->insert, W3_Db->insert, W3_DbProcessor->insert, W3_Db->default_insert, W3_Db->query, W3_DbProcessor->query, W3_Db->default_query

    In my particular setup, Mysql replication is activated between 2 servers. What I highly suspect, is that your new session ID generation does not take that into account. I.e. it does not use Mysql for ID generation, but has its own engine and therefore can cause ID conflicts between 2 or more servers in replication array. If that turns out to be the case, its a very nasty and important bug.

    Thanks for the great support.

    https://ww.wp.xz.cn/plugins/woocommerce/

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Contributor Mike Jolley

    (@mikejolley)

    10 years, 3 months ago

    There is code i place to pull the value, for example aa45d9a84b52fe973d32615b35712d0b, from the DB before inserting another sessions with key aa45d9a84b52fe973d32615b35712d0b so this should not occur.

    That said, Gerhard occasionally sees this too but we’ve been unable to narrow down the problem.

    Did you use the plugin for sessions we released a while back? Can you post your system report and details of your host?

    Thread Starter nminkov

    (@nminkov)

    10 years, 3 months ago

    Hi,

    Thanks for the quick reply.

    No, I have not used the session plugin downloaded separately. That is, new sessions came when released in the the official release.

    The website is distributed between 2 physical locations, each withs own MySQL database server. Website files are sync in one direction with rsync, database is synced in both directions using MySQL master-master replication. In that setup MySQL auto-generated IDs are always unique to server, one is using odd, the other even ID numbers (automatically handled by MySQL, configured in its config file)

    While writing, figured out that the issue is not caused by the replication, otherwise the replication will panic. I think I know what happen (supposing, haven’t checked your code).

    Your script generates a session ID in PHP, checks it against the DB if exists, if exits recreates the ID, if does not, inserts it in the DB.

    Now what happends if there are 2 concurrent session start, both can generate the same ID, and both can check in the same time that it does not exist, and therefore both will try to add it to the DB, but only one will succeed.

    What’s worst, if that same issues happens but on 2 different server using MySQL replication, the result will be replication panic. That is, the replication will stop, and the administrator must check what happened, skip the faulty sql command (if he manages to understand the implications), with all the consequences of following commands depending of the faulty session ID

    If this is indeed the way the session ID is generated, here is an ideas of how you can change it.

    Add normal auto_increment ID column to your Sessions table + another long_session_id mimicking the one that you use now
    On session create:
    Insert new sessing table entry
    Get the auto_incremented ID from last insert, concatenate it with another secret constants/variables, then hash it to get your final “long_session_id”
    Update the session table with the “long_session_id”
    Use the “long_session_id” as you use it now

    The way you create the “secret constant/variables” concatenated with the ID, and the actual hashing algorithm will deterine the risk of collision.

    You can also use SQL triggers and generate your ID in SQL function http://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix 44

    In the worst case you need make sure that your ID generator always creates 2 different IDs, even for 2 calls in the same moment and now count on searching manually Mysql table. Google UUID generation.

    I hope it helps

    Plugin Contributor Mike Jolley

    (@mikejolley)

    10 years, 3 months ago

    Logged it here thanks https://github.com/woothemes/woocommerce/issues/10389

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘Error: Database Duplicate entry in woocommerce_sessions’ is closed to new replies.