title: Data is being deleted from table
body: <p>I've got a really horrible problem on my hands, for some reason data is being wiped from a particular table's column and I have no idea why.</p>
  
  <p>The table is called <code>events</code>.  Inside the table there are a number of columns, name, date etc however recently I put a field in called 'schedule' this is of type TEXT.</p>
  
  <p>On the web page, you can edit the event in different tabs and create a schedule (fields using jQuery clone / etc) and then when the schedule data gets saved into th database the HTML $_POST data gets converted into a JSON array using <code>json_encode</code>:</p>
  
  <pre><code>$db-&gt;prepared['schedule'] = json_encode(
      array(
          "scheduleday" =&gt; ($_POST['scheduleday'] ?? array()), 
          "scheduletime" =&gt; ($_POST['scheduletime'] ?? array()), 
          "scheduledescription" =&gt; ($_POST['scheduledescription'] ?? array()), 
          "schedulevenue" =&gt; ($_POST['schedulevenue'] ?? "")
      )
  );
  </code></pre>
  
  <p>The resulting json array could look like this, for example:</p>
  
  <pre><code>{"scheduleday":{"1":"2020-08-25","2":"2020-08-26"},"scheduletime":{"1":["19:30","20:00 - 20:50"],"2":["14:00 - 14:50","14:50 - 15:00","15:00 - 15:50","15:50 - 16:00","16:00 - 16:50"]},"scheduledescription":{"1":["Introduction","John Smith"],"2":["John Smith","Break","John Smith teaching","Break","John Smith teaching"]},"schedulevenue":"1 Acia Avenue"}
  </code></pre>
  
  <p>Straight after this is INSERTED or UPDATEd into the mysql database it is then sent to a function that uses fpdf to turn the json array into a PDF which can be downloaded.</p>
  
  <p>This works well, apart from one problem.  Random (or what appears to be random) schedules are going missing.  We can create a schedule one day and then a week later, or a few days later, suddenly the file will have been deleted and the column in the table removed.  No other data in the table is getting deleted, nothing apart from the schedule.</p>
  
  <p>Here's what I've tried to find out whats going on:</p>
  
  <p><strong>SQL Trigger</strong></p>
  
  <p>I've set a trigger on the event table which dumps data into a table called <code>event_trigger</code> AFTER an update is run.  This is the code for the trigger:</p>
  
  <pre><code>BEGIN
  INSERT INTO `event_trigger` (oldID,name,old_schedule,new_schedule) VALUES
  (OLD.id,
   OLD.name,
   OLD.schedule,
   NEW.schedule);
  END
  </code></pre>
  
  <p>This has been helpful in the sense that I now know if there is an event with a schedule missing, because the NEW.schedule will be empty.  However that last two times a schedule has gone missng (yesterday and today, in fact) its been outside of office hours, 18:45 and 19:22 so no one should be performing any updates on events in those times.</p>
  
  <p><strong>.txt File Log</strong></p>
  
  <p>The other thing i've done is on the page where the events are updated I've put a text log, which dumps the prepared variables, the SQL statement and the user and the user ID to a text file.  Unfortunately this isnt working because when a schedule goes missing, its not getting logged in there.  All that tells me is that its happening somewhere else.</p>
  
  <p>I dont know how to narrow this down further.  There is no user activity at the times when an event is deleted.  The closest i've gotten is using the TRIGGER.  But I am so limited as the information from the trigger is not enough; I can't get IP, SQL statement, user ID or anything like that.  Just the OLD and NEW variables.</p>
  
  <p>Can anyone help me think of ways to investigate, I'd be so grateful.  This has been going on for over a month now, and its infuriating because I just cannot see why it is happening.</p>
  
  <p>The only extra option I can think of doing is turning on full SQL logs, but I am reluctant to do that as it will slow the server down immensly.</p>