I assume whoever closed this because it “lacked detail or clarity” didn’t actually read the whole thing. Or, perhaps, thought I was just an idiot.

I know, the subject line makes no sense — and neither is my situation. However, the subject line is, in fact, a perfect explanation of my situation. Without a code change, records simply started vanishing — I provide info on that, below.

I have a simple table, named “removed” that has three fields: rowid int not null autoincrement, visitor_id int not null, tstamp timedate not null current_timestamp. It has been working without a hitch for a couple of years. When a visitor is deleted, I add the associated rowid from the visitors table to the removed table. This is because I don’t actually delete anything from the tables, I simply flag it as deleted so it doesn’t show up.

When the code wants to check to see if the visitor has been removed, it checks twp places: 1) the removed table — if the visitor_id exists, the visitor was removed, and 2) the tstamp_del field in the visitor record. Like I said, the code has been working for several years.

Something “funky” happened three days ago — and, no, I have not yet been able to figure out what “funky” means. But one of the symptoms is that records I add or update no longer “take.” The reason I’m starting with the removed table is that is it one place where this is happening the most, and the easiest to duplicate.

I have three browser windows open while I am testing:

• In the first window, I am running the app, which is where I flag a visitor to be removed, and click submit, then check to see if it is gone. To do this, I go to the list of active visitors, select the visitor, click the remove checkbox, then click submit. The app restarts, going to the same visitor list page, and the visitor is still there.

• In the second window, I run the SQL commands to check the tables. I include the SQL commands, below. I have a couple of stop points that allow me to check this status during the process.

• In the third window, I watch the errors. I am adding a ton of debugging output, all going to error_log, and this window is running a web app I have that is essentially a glorified tail -f of the file.

Here is the MySQL code I run in the second window:

    SELECT 
      COUNT(*) AS isGone,
      R.tstamp_add AS WhenRemoved
    FROM removed R
    WHERE visitor_id = :id;
    
    SELECT * 
    FROM removed R
    ORDER BY R.rowid DESC 
    LIMIT 5;
    
    SELECT V.rowid, V.visitor_name, V.tstamp_del
    FROM visitors V
    ORDER BY V.tstamp_del DESC
    LIMIT 5;

After removing the test visitor, here is what I get from running these commands:
(I tried putting the salient data in bold, but that didn’t work very well):

    **FIRST QUERY**
        isGone    WhenRemoved
             1    2024-07-16 23:24:04    <=-- notice timestamp
    
    **SECOND QUERY: REMOVED TABLE**
    
     rowid  visitor_id  tstamp_add  
     1471   213         2024-07-16 23:24:04 <=-- this is the line
     1463   210         2024-07-16 18:06:46 <=-- notice rowid gap
     1460   827         2024-07-16 17:33:34 <=-- notice rowid gap
     1446   676         2024-07-16 03:24:05 <=-- notice rowid gap
     1445   677         2024-07-16 03:23:50 <=-- no rowid gap
    
    **THIRD QUERY: VISITORS TABLE**
    
     rowid  visitor_name  tstamp_del
     213    Daniel Berry  2024-07-16 23:24:04  <=-- this is the line
     212    Daniel Berry  2024-07-16 19:09:46
     211    Daniel Berry  2024-07-16 18:36:58
     210    Daniel Berry  2024-07-16 18:06:46
     827    Josie Wales   2024-07-16 17:33:34

Here is the line from my error_log output related to this:

    [17-Jul-2024 03:24:04 UTC] 2024-07-17 03:24:04.905109
    MSG: rval after archiving:
    {"del":1,"remove":"1471","checking":"2024-07-16 23:24:04"}

    [17-Jul-2024 03:24:04 UTC] 2024-07-17 03:24:04.901710
    MSG: doing archive vid:[213]

So at this point, in the middle of the process, the phpMyAdmin MySQL commands show that:

• The record has been flagged as removed. In the first log message, the “del”:1 shows that after the delete process changed one row in the “removed” table, as reported in the first SQL output.

• The “removed” table has the next key, with the proper visitor_id and date.

• The “visitors” table has the proper date in the tstamp_del field.

I’m sure some astute folks will notice that the auto_increment field for the new record (1471) is 8 more than the very next record (1463), which is 3 more than the next one (1460), with another large skip before the next one (1446).

What is happening is that after I return to the visitor list in the app, the tstamp_del in the visitor record returns to NULL, and the record with rowid 1471 simply vanishes from the table.

No errors are reported, nada.

I have 6 test records I am trying to work my way through: 827, 210, 211, 212, 213, and 214.

To try to figure out where the “problem” is, I am adding more debugging code. And when I have a typo or some other such sillyness and the program crashes, then the remove “sticks.” That is why you can see 827, 210, and 213. Actually, you only see 213 because this snapshot is in the middle of the processing — once I refresh the page, it will vanish.

It kind of feels like PDO is doing a transaction/rollback — but I have zero transaction code. In fact, I’ve spent a couple of hours trying to find out how to guarantee that PHP/PDO/MySQL are prohibited from doing transactions, with no luck.

Just for giggles and grins, here is the method that adds the item to the removed table:

public function setRemoved($visitor_id)
    {
    $sql = 'INSERT INTO removed (visitor_id) VALUES (:v)';
    $data = [ 'v' => $visitor_id ];

    $sqlmsg = "Setting removed for prospect [$visitor_id]:" .
        ' for: ' . __METHOD__ . '::' . __LINE__ .
        " // SQL: [$sql] // DATA:[" . $this->const->printr($data) . "]";
    $this->log->log(logTRACE, __METHOD__, __LINE__, $sqlmsg);

    try {
        if ($stmt = $this->db->prepare($sql))
            {
            $stmt->execute($data);
            return($this->db->lastInsertId());
            }
        else
            { throw new Exception('prepare == false', __LINE__); }
        }
    catch (PDOException $e)
        {
        $this->log->logsqlerr(__METHOD__, __LINE__,
            $sqlmsg, $e);
        return(false);
        }
    catch (Exception $e)
        {
        $this->log->logsqlerr(__METHOD__, __LINE__,
            $sqlmsg, $e);
        return(false);
        }
    }

Since the error_log message contains the correct lastInserId() value, I know this function worked properly.

Just to be complete, here’s what sets the visitor record:

public function delVisitor($visitor_id = NULL)
    {
    if (empty($visitor_id)) { return(0); }

    $sql = 'UPDATE visitors SET tstamp_del = :t WHERE rowid = :v';
    $data = [
        't' => $this->const->fixDateTime('now', 'Y-m-d H:i:s'),
        'v' => $visitor_id
        ];

    $sqlmsg = 'Deleting prospect record:' .
        ' for: ' . __METHOD__ . ' at: ' . __LINE__ .
        " // SQL: [$sql] // DATA:[" . $this->const->printr($data) . "]";
    $this->log->log(logTRACE, __METHOD__, __LINE__, $sqlmsg);
    try {
        if ($stmt = $this->db->prepare($sql))
            {
            $stmt->execute($data);
            return(true);
            }
        else
            { throw new Exception('prepare == false', __LINE__); }
        }
    catch (PDOException $e)
        {
        $this->log->logsqlerr(__METHOD__, __LINE__,
            $sqlmsg, $e);
        return(false);
        }
    catch (Exception $e)
        {
        $this->log->logsqlerr(__METHOD__, __LINE__,
            $sqlmsg, $e);
        return(false);
        }
    }

Let me reiterate — this code has not changed in over two years. But starting at/after this timestamp (2024-07-16 03:24:05), these records started vanishing. Just to be clear on that point, there are zero gaps prior to rowid=1446.

I don’t mind researching; I don’t mind reading up on things — I actually want to understand and learn. But I have never seen this kind of thing — outside of transactions which were rolled back. I have gone out of my way to NOT use transactions on this app. It is by far too simple to need them.

A few other details: MariaDB 11.4.x, PHP 8.x

Please: has anyone else seen this kind of thing? What was the issue?