Does MySQL delay DELETE actions? I am scratching my head on this.

When we load a "cart" page, we give the user the chance to "estimate shipping". If they move from "cart" to "checkout", this shipping charge is added to their cart (InnoDB table "cart_items").

If they push the "back to cart link", the "cart" script has the Shipping object remove itself from cart_items. This occurs before the cart queries the cart_items table for its contents in order to display the cart. The cart uses SQL_NO_CACHE in its query....

Whenever I check the DB from the terminal after re-loading the cart page, there's no shipping line-item in the cart_items table. But at the time the cart queries the DB there apparently is, because it ALWAYS shows up on the page. I've got logs showing the order of operations are proceeding as expected (e.g. removeShipping() is being run before getItems()). If I REFRESH the cart after it loads, the shipping line-item is gone.

So this feels like some kind of delayed write on the DELETE operation. Is that possible? What am I not understanding?

Off the top of my head, there isn't some transaction thing going on is there? So that while you're looking from the terminal the transaction that added the shipping line-item hasn't yet been committed so wouldn't be visible from another session?

    dalecosp "back to cart link"

    Is that an actual link that causes a http request for the page or is it a browser back history operation? It sounds like you are seeing a cached web page. Have you setup the web server so that dynamically produced pages, such as .php pages, have told the browser to not cache them?

      It's a link to /cart, not a JS "back" operation or the like. Causes an HTTP/200 response from the server.

      Browser cache is definitely a possibility, I guess. I know FireFox has definitely shown itself to be guilty of this sort of thing in the past.

      The cart's getItems() method does use SQL_NO_CACHE in the select statement, so I think that we can rule out transactions, although that was the gist of my question, until I can determine if the browser is caching....

        When you execute a query with php, your script waits until the query is finished. Php sits in a loop, keeping track of execution time (ticks) and allows any other instances of php to run during the idle time. When the query finishes, a software interrupt causes your script to continue. So, any query you execute has completed or failed when your script continues past the point of the query. Queries are not sitting in the background waiting to be executed (an INSERT DELAYED ... query can be, but isn't supported for InnoDB tables and would produce a query error if attempted.)

        Transactions are per database session, i.e. a single client connection. When any instance of your script ends, that connection is closed and the corresponding database session is closed. There's no cross talk between different instances of your script.

        From the documentation - "The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed." Using SQL_NO_CACHE would only cause there to be no entry in the cache for that query/result set.

        Are you using php session variables to hold the cart data or to pass any of this data between pages?

          No; the only involvement with $_SESSION is the cart's identity itself---for anonymous users (logged-in user carts use their UID).

          I will probably turn on the MySQL general_log and see if that gives any insight.

            That trigged a thought. What if you have two different sessions, due to things like variations in the URL host-name or path in reaching a page, and you are seeing two different carts depending on the current variation of the URL being used?

            Another possibility for unexplained operation is a redirect that doesn't halt php execution and there's some following code that's performing an unintended operation on the cart data. Similarly, you could be seeing the last result of multiple requests to a page.

              I'll start by saying that I decided to fish with dynamite as I wasn't able to get any bites. A JS function will hide any "shipping cost" line-items on the /cart page if they continue to show up.

              I did some analysis of interaction and timing with Apache/PHP and MySQL. I'm not sure I'm any closer to what's really causing the behavior, but I did discover some things I'd done that make it feel sloppy when you review the activity.

              The biggest one of those is that most of these objects extend the Cart class and can call its members with impunity as most of the methods in parent classes are public. I think that's why we have some weirdness like ->getItems() being called twice in a row. It's also likely that ->update() needs to happen before ->getItems() and ->getItemData().

              I've already made some changes to reflect these thoughts and will likely make more if possible. Here's a dump:

              Apache: [16/May/2024:08:48:14 -0700] "GET /cart HTTP/1.1" 200]
              MySQL: 2024-05-16T13:48:14.918250Z 15638 Connect foobar@localhost on foobar_devel using Socket
              PHP: $Session->login_check()
              MySQL: 2024-05-16T13:48:14.918619Z 15638 Query select SQL_NO_CACHE s.user_id as user_id, s.securityString as string, l.status from sessions s inner join logins l on l.id = s.user_id where user_session =''
              PHP: $User->countShopList();
              MySQL: 2024-05-16T13:48:14.918895Z 15638 Query select SQL_NO_CACHE items from cart where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->init();
              MySQL: 2024-05-16T13:48:14.919076Z 15638 Query select SQL_NO_CACHE * from cart where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->setStatus();
              MySQL: 2024-05-16T13:48:14.919481Z 15638 Query update cart set status = 'working', timestamp=UNIX_TIMESTAMP() where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->getItems();
              MySQL: 2024-05-16T13:48:14.920526Z 15638 Query FLUSH TABLES cart_items
              PHP: $Cart->getItems();
              MySQL: 2024-05-16T13:48:14.921088Z 15638 Query select SQL_NO_CACHE id, item_id, qty, price from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->getItemData();
              MySQL: 2024-05-16T13:48:14.921804Z 15638 Query select SQL_NO_CACHE itemnum as name, description from products where id = 17352
              PHP: $Cart->getItems();
              MySQL: 2024-05-16T13:48:14.922170Z 15638 Query FLUSH TABLES cart_items
              MySQL: 2024-05-16T13:48:14.922544Z 15638 Query select SQL_NO_CACHE id, item_id, qty, price from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->getItemData();
              MySQL: 2024-05-16T13:48:14.922927Z 15638 Query select SQL_NO_CACHE itemnum as name, description from products where id = 17352
              PHP: $Cart->update();
              MySQL: 2024-05-16T13:48:14.923062Z 15638 Query delete from cart_items where qty = 0
              MySQL: 2024-05-16T13:48:14.923179Z 15638 Query select SQL_NO_CACHE SUM(qtyprice) as total, sum(qty) as itemct from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              MySQL: 2024-05-16T13:48:14.923334Z 15638 Query update cart set timestamp = 1715867294, items = 1, total = 46.80 where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              $Shipping->removeFromCart();
              MySQL: 2024-05-16T13:48:14.923509Z 15638 Query delete from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber' and itemnum='SHIPCOST'
              PHP: $Cart->getItems();
              MySQL: 2024-05-16T13:48:14.923625Z 15638 Query FLUSH TABLES cart_items
              MySQL: 2024-05-16T13:48:14.924057Z 15638 Query select SQL_NO_CACHE id, item_id, qty, price from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->getItemData();
              MySQL: 2024-05-16T13:48:14.924378Z 15638 Query select SQL_NO_CACHE itemnum as name, description from products where id = 17352
              PHP: $Cart->update();
              MySQL: 2024-05-16T13:48:14.924500Z 15638 Query delete from cart_items where qty = 0
              MySQL: 2024-05-16T13:48:14.924616Z 15638 Query select SQL_NO_CACHE SUM(qty
              price) as total, sum(qty) as itemct from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              MySQL: 2024-05-16T13:48:14.924770Z 15638 Query update cart set timestamp = 1715867294, items = 1, total = 46.80 where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Session->login_check()
              MySQL: 2024-05-16T13:48:14.925028Z 15638 Query select SQL_NO_CACHE s.user_id as user_id, s.securityString as string, l.status from sessions s inner join logins l on l.id = s.user_id where user_session =''
              PHP: $User->countShopList()
              MySQL: 2024-05-16T13:48:14.925184Z 15638 Query select SQL_NO_CACHE items from cart where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              MySQL: 2024-05-16T13:48:14.926176Z 15638 Quit
              Apache: [16/May/2024:08:48:16 -0700] "GET /checkout HTTP/1.1" 200
              MySQL: 2024-05-16T13:48:16.923897Z 15639 Connect foobar@localhost on foobar_devel using Socket
              PHP: $User->login_check()
              MySQL: 2024-05-16T13:48:16.924478Z 15639 Query update sessions set last_time = UNIX_TIMESTAMP() where user_session = 'ekRyRnpYSjgzcEdKM2ltdG4vM1dGWTJmVHArbjFEMUtSK1p6VWlGcWdOc3BxTHJLMzIvRXUzRmVWanM3U0Z1NA=='
              MySQL: 2024-05-16T13:48:16.926207Z 15639 Query select user_id, user_session, securityString from sessions where user_session = 'ekRyRnpYSjgzcEdKM2ltdG4vM1dGWTJmVHArbjFEMUtSK1p6VWlGcWdOc3BxTHJLMzIvRXUzRmVWanM3U0Z1NA==' and ip_ssl = '173.25.70.145'
              MySQL: 2024-05-16T13:48:16.926458Z 15639 Query select user_id from sessions where user_session = 'ekRyRnpYSjgzcEdKM2ltdG4vM1dGWTJmVHArbjFEMUtSK1p6VWlGcWdOc3BxTHJLMzIvRXUzRmVWanM3U0Z1NA=='
              MySQL: 2024-05-16T13:48:16.926678Z 15639 Query select SQL_NO_CACHE s.user_id as user_id, s.securityString as string, l.status from sessions s inner join logins l on l.id = s.user_id where user_session =''
              PHP: $User->countShopList()
              MySQL: 2024-05-16T13:48:16.926818Z 15639 Query select SQL_NO_CACHE items from cart where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->init();
              MySQL: 2024-05-16T13:48:16.927134Z 15639 Query select SQL_NO_CACHE * from cart where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->setStatus();
              MySQL: 2024-05-16T13:48:16.927288Z 15639 Query update cart set status = 'working', timestamp=UNIX_TIMESTAMP() where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->getItems();
              MySQL: 2024-05-16T13:48:16.928445Z 15639 Query FLUSH TABLES cart_items
              MySQL: 2024-05-16T13:48:16.928986Z 15639 Query select SQL_NO_CACHE id, item_id, qty, price from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->getItemData();
              MySQL: 2024-05-16T13:48:16.929419Z 15639 Query select SQL_NO_CACHE itemnum as name, description from products where id = 17352
              PHP: $Cart->update();
              MySQL: 2024-05-16T13:48:16.929554Z 15639 Query delete from cart_items where qty = 0
              MySQL: 2024-05-16T13:48:16.929714Z 15639 Query select SQL_NO_CACHE SUM(qty*price) as total, sum(qty) as itemct from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              MySQL: 2024-05-16T13:48:16.929894Z 15639 Query update cart set timestamp = 1715867296, items = 1, total = 46.80 where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->getItems();
              MySQL: 2024-05-16T13:48:16.930017Z 15639 Query FLUSH TABLES cart_items
              MySQL: 2024-05-16T13:48:16.930442Z 15639 Query select SQL_NO_CACHE id, item_id, qty, price from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->getItemData();
              MySQL: 2024-05-16T13:48:16.930814Z 15639 Query select SQL_NO_CACHE itemnum as name, description from products where id = 17352
              PHP: $Cart->is_order OR PHP: $Order->is_valid_id();
              MySQL: 2024-05-16T13:48:16.930963Z 15639 Query select * from orders where order_id = 2024051601
              PHP: $Cart->init BOGUS NOT USING SESSION???
              MySQL: 2024-05-16T13:48:16.931100Z 15639 Query select SQL_NO_CACHE * from cart where uid = 0
              MySQL: 2024-05-16T13:48:16.931239Z 15639 Query replace into cart values (0, '0',0, 'init', UNIX_TIMESTAMP(), 0.00)
              PHP: $Cart->getItems();
              MySQL: 2024-05-16T13:48:16.931967Z 15639 Query FLUSH TABLES cart_items
              MySQL: 2024-05-16T13:48:16.932720Z 15639 Query select SQL_NO_CACHE id, item_id, qty, price from cart_items where cart_id = 0
              PHP: $Cart->getItemData() ON INVALID CART!
              MySQL: 2024-05-16T13:48:16.933066Z 15639 Query select SQL_NO_CACHE itemnum as name, description from products where id = 18506
              MySQL: 2024-05-16T13:48:16.933205Z 15639 Query select SQL_NO_CACHE itemnum as name, description from products where id = 17298
              MySQL: 2024-05-16T13:48:16.933314Z 15639 Query select SQL_NO_CACHE itemnum as name, description from products where id = 14420
              MySQL: 2024-05-16T13:48:16.933440Z 15639 Query select SQL_NO_CACHE itemnum as name, description from products where id = 17298
              MySQL: 2024-05-16T13:48:16.933545Z 15639 Query select SQL_NO_CACHE itemnum as name, description from products where id = 17352
              PHP: $Order->__construct() ... existing order. Check error_log to see if there was an error (e.g. multiple order_statuses with this id).
              MySQL: 2024-05-16T13:48:16.933708Z 15639 Query select distinct(status) from orders where order_id = 2024051601
              PHP: $Cart->getItems();
              MySQL: 2024-05-16T13:48:16.933917Z 15639 Query FLUSH TABLES cart_items
              MySQL: 2024-05-16T13:48:16.934301Z 15639 Query select SQL_NO_CACHE id, item_id, qty, price from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              MySQL: 2024-05-16T13:48:16.934649Z 15639 Query select SQL_NO_CACHE itemnum as name, description from products where id = 17352
              PHP: $Order->already_has_shipping();
              MySQL: 2024-05-16T13:48:16.934795Z 15639 Query select * from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber' and itemnum='SHIPCOST'
              PHP: $Order->addShipping (previous function returned FALSE)
              MySQL: 2024-05-16T13:48:16.934938Z 15639 Query insert into cart_items values(NULL,0,'061gfh5oov60dkbfhdqfmd6ber',11111111,'SHIPCOST','Ship via USPS',1,6.74)
              PHP: $Cart->getItems();
              MySQL: 2024-05-16T13:48:16.935727Z 15639 Query FLUSH TABLES cart_items
              MySQL: 2024-05-16T13:48:16.936363Z 15639 Query select SQL_NO_CACHE id, item_id, qty, price from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->getItemData()
              MySQL: 2024-05-16T13:48:16.936689Z 15639 Query select SQL_NO_CACHE itemnum as name, description from products where id = 17352
              PHP: $Cart->update();
              MySQL: 2024-05-16T13:48:16.936831Z 15639 Query delete from cart_items where qty = 0
              MySQL: 2024-05-16T13:48:16.937001Z 15639 Query select SQL_NO_CACHE SUM(qtyprice) as total, sum(qty) as itemct from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              MySQL: 2024-05-16T13:48:16.937172Z 15639 Query update cart set timestamp = 1715867296, items = 2, total = 53.54 where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Order->setOrderStatus();
              MySQL: 2024-05-16T13:48:16.937843Z 15639 Query update orders set status = 'incart' where order_id = 2024051601
              PHP: $Cart->getTotal();
              MySQL: 2024-05-16T13:48:16.937970Z 15639 Query select SQL_NO_CACHE SUM(qty
              price) as total, sum(qty) as itemct from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $User->login_check();
              MySQL: 2024-05-16T13:48:16.938233Z 15639 Query select SQL_NO_CACHE s.user_id as user_id, s.securityString as string, l.status from sessions s inner join logins l on l.id = s.user_id where user_session =''
              PHP: $User->countShopList();
              MySQL: 2024-05-16T13:48:16.938418Z 15639 Query select SQL_NO_CACHE items from cart where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              MySQL: 2024-05-16T13:48:16.939324Z 15639 Quit
              Apache: [16/May/2024:08:48:19 -0700] "GET /cart?from_checkout=1 HTTP/1.1" 200
              MySQL: 2024-05-16T13:48:19.318200Z 15640 Connect foobar@localhost on foobar_devel using Socket
              PHP: $User->login_check()
              MySQL: 2024-05-16T13:48:19.318409Z 15640 Query select SQL_NO_CACHE s.user_id as user_id, s.securityString as string, l.status from sessions s inner join logins l on l.id = s.user_id where user_session =''
              PHP: $User->countShopList()
              MySQL: 2024-05-16T13:48:19.318656Z 15640 Query select SQL_NO_CACHE items from cart where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              MySQL: 2024-05-16T13:48:19.319479Z 15640 Quit
              Apache: [16/May/2024:08:48:21 -0700] "GET /cart HTTP/1.1" 200
              MySQL: 2024-05-16T13:48:21.263083Z 15641 Connect foobar@localhost on foobar_devel using Socket
              PHP: $User->login_check();
              MySQL: 2024-05-16T13:48:21.263543Z 15641 Query select SQL_NO_CACHE s.user_id as user_id, s.securityString as string, l.status from sessions s inner join logins l on l.id = s.user_id where user_session =''
              PHP: $User->countShopList()
              MySQL: 2024-05-16T13:48:21.263853Z 15641 Query select SQL_NO_CACHE items from cart where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->init();
              MySQL: 2024-05-16T13:48:21.264077Z 15641 Query select SQL_NO_CACHE * from cart where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->setStatus();
              MySQL: 2024-05-16T13:48:21.264259Z 15641 Query update cart set status = 'working', timestamp=UNIX_TIMESTAMP() where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->getItems();
              MySQL: 2024-05-16T13:48:21.265692Z 15641 Query FLUSH TABLES cart_items
              MySQL: 2024-05-16T13:48:21.266271Z 15641 Query select SQL_NO_CACHE id, item_id, qty, price from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              MySQL: 2024-05-16T13:48:21.266809Z 15641 Query select SQL_NO_CACHE itemnum as name, description from products where id = 17352
              PHP: $Cart->getItems();
              MySQL: 2024-05-16T13:48:21.266984Z 15641 Query FLUSH TABLES cart_items
              MySQL: 2024-05-16T13:48:21.267445Z 15641 Query select SQL_NO_CACHE id, item_id, qty, price from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->getItemData()
              MySQL: 2024-05-16T13:48:21.268004Z 15641 Query select SQL_NO_CACHE itemnum as name, description from products where id = 17352
              PHP: $Cart->update();
              MySQL: 2024-05-16T13:48:21.268156Z 15641 Query delete from cart_items where qty = 0
              MySQL: 2024-05-16T13:48:21.268286Z 15641 Query select SQL_NO_CACHE SUM(qtyprice) as total, sum(qty) as itemct from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              MySQL: 2024-05-16T13:48:21.268535Z 15641 Query update cart set timestamp = 1715867301, items = 2, total = 53.54 where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Shipping->removeFromCart();
              MySQL: 2024-05-16T13:48:21.268707Z 15641 Query delete from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber' and itemnum='SHIPCOST'
              MySQL: 2024-05-16T13:48:21.269733Z 15641 Query flush tables cart_items
              PHP: $Cart->getItems();
              MySQL: 2024-05-16T13:48:21.270340Z 15641 Query FLUSH TABLES cart_items
              MySQL: 2024-05-16T13:48:21.270733Z 15641 Query select SQL_NO_CACHE id, item_id, qty, price from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Cart->getItemData()
              MySQL: 2024-05-16T13:48:21.271229Z 15641 Query select SQL_NO_CACHE itemnum as name, description from products where id = 17352
              PHP: $Cart->update();
              MySQL: 2024-05-16T13:48:21.271374Z 15641 Query delete from cart_items where qty = 0
              MySQL: 2024-05-16T13:48:21.271520Z 15641 Query select SQL_NO_CACHE SUM(qty
              price) as total, sum(qty) as itemct from cart_items where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              MySQL: 2024-05-16T13:48:21.271731Z 15641 Query update cart set timestamp = 1715867301, items = 1, total = 46.80 where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              PHP: $Session->login_check()
              MySQL: 2024-05-16T13:48:21.273008Z 15641 Query select SQL_NO_CACHE s.user_id as user_id, s.securityString as string, l.status from sessions s inner join logins l on l.id = s.user_id where user_session =''
              PHP: $User->countShopList();
              MySQL: 2024-05-16T13:48:21.273203Z 15641 Query select SQL_NO_CACHE items from cart where sessid = '061gfh5oov60dkbfhdqfmd6ber'
              MySQL: 2024-05-16T13:48:21.274009Z 15641 Quit
              MySQL: 2024-05-16T13:48:27.667329Z 15637 Query set GLOBAL general_log="OFF"

                Write a Reply...