the mySQL database isn't retaining recent inputs.

[jongira]

Hi.  I'll start by giving a 'newbie alert': I need help.

 

On our host, an existing php/mySQL database recently started 'forgetting' new records.

The php/mySQL code has been operating correctly for a couple of years.

 

The server is running: MySQL version: 4.1.18-standard; PHP Version 5.2.6

(They recently updated the php version, about two months ago).

 

Just in the last week or so, new entries into an existing database disappear AFTER A WHILE.

 

When I use the input page, the record is successfully entered: it shows up in the display page, and in TOAD and phpMyAdmin.  After a couple of hours (or overnight) the new entries disappear.  They no longer show up on the display page, nor in TOAD's data browser.

 

The server admin told me "it must be an application error" (I thought maybe they were restoring an old DB or something.)

 

I suspect that perhaps the new entries are in a buffer which disappears after a while?  If so, how can I force the record to be completely written.  I'll post here the INPUT statement, but remember that it's been working for some years now, and works now -- for a while.  It's in the database, I see it there... and then it disappears.

 

Please help... the woman who does the event inputting is going crazy (and so am I). Thanks so much!

 

//Put into Events table in database
$insertSQL = sprintf("INSERT INTO tblEvents (start_date, end_date, no_start_time, no_end_time, id_band, performer, nonjsjbf_perf, id_venues, description, pricerange, event_special, event_nonjsjbf, event_memmtg, ID_EventSeries) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($start_date, "date"),
                       GetSQLValueString($end_date, "date"),
                       GetSQLValueString($no_start_time, "text"),
                       GetSQLValueString($no_end_time, "text"),
                       GetSQLValueString($_POST['id_band'], "int"),
                       GetSQLValueString($_POST['performer'], "text"),
                       GetSQLValueString($nonjsjbf_perf, "text"),
                       GetSQLValueString($_POST['id_venues'], "int"),
                       GetSQLValueString($_POST['description'], "text"),
                       GetSQLValueString($pricerange, "text"),
                       GetSQLValueString($event_special, "text"),
                       GetSQLValueString($event_nonjsjbf, "text"),
				   GetSQLValueString($event_memmtg, "text"),
				   GetSQLValueString($id_eventseries, "int"));

  mysql_select_db($database_JSJBF, $JSJBF);
  $Result1 = mysql_query($insertSQL, $JSJBF) or die(mysql_error());

 

[fenway]

There is a FLUSH TABLES command, but I doubt that's the issue.

[PFMaBiSmAd]

It would nice if you posted your table definition and a few complete rows of recent data to show what exactly is in the table.

 

It is more likely that you have a DELETE query that is doing this or that the rows are being overwritten in an UPDATE query, in a web page that is unconditionally executing a query anytime the page is visited with an row id on the end of the url.

[jongira]

Hello PFMaBiSmAd and Fenway, thanks for your responses.

 

Even if I enter several new items, they are all deleted by the next day.  I checked the php pages, and there is no DELETE query at all, so there can't be a mistaken input or parsing.

 

The table structure is:

CREATE TABLE `tblEvents` (
  `id_event`         int(10) AUTO_INCREMENT NOT NULL,
  `start_date`       datetime,
  `end_date`         datetime,
  `id_band`          int(10),
  `performer`        varchar(100),
  `nonjsjbf_perf`    char(3),
  `id_venues`        int(10),
  `description`      longtext,
  `pricerange`       varchar(100),
  `event_special`    char(3),
  `event_nonjsjbf`   char(3),
  `event_memmtg`     char(3),
  `title`            varchar(100),
  `category`         varchar(50),
  `event_type`       varchar(50),
  `insertedby`       varchar(50),
  `updatedlastby`    varchar(50),
  `graphic`          varchar(100),
  `time`             varchar(50),
  `remove`           varchar(45) NOT NULL DEFAULT 'No',
  `special_event`    tinyint(3),
  `non_jsjbf_event`  tinyint(3),
  `no_id_band`       tinyint(3),
  `no_start_time`    char(3),
  `no_end_time`      char(3),
  `ID_EventSeries`   int(10) UNSIGNED,
  /* Keys */
  PRIMARY KEY (`id_event`)
) ENGINE = MyISAM;

 

And some sample rows are:

 

id_event,start_date,end_date,id_band,performer,nonjsjbf_perf,id_venues,description,pricerange,event_special,event_nonjsjbf,event_memmtg,title,category,event_type,insertedby,updatedlastby,graphic,time,remove,special_event,non_jsjbf_event,no_id_band,no_start_time,no_end_time,ID_EventSeries
4131,12/13/2008 21:15 A12P12,12/13/2008 12:15 A12P12,160,,No,788,,,Yes,No,No,,,,,,,,No,,,,No,,
4130,11/22/2008 21:15 A11P11,11/22/2008 0:00 A11P11,160,,No,392,,,Yes,No,No,,,,,,,,No,,,,No,Yes,
4060,11/21/2008 21:30 A11P11,11/21/2008 0:00 A11P11,224,,No,58,,,Yes,No,No,,,,,,,,No,,,,No,Yes,
4129,11/3/2008 22:00 A11P11,11/3/2008 23:00 A11P11,160,,No,226,CD RELEASE CELEBRATION!,,Yes,No,No,,,,,,,,No,,,,No,,

 

To repeat the basic problem:  when new entries are entered, they persist for a while. You can see them on the output page and in TOAD.  Sometime during the night, the NEW entries disappear, the old ones are still there.  The folks at the server say there is no crontab or script running that would cause this. 

 

New Information: the auto-increment value remembers the missing records, and new entries are added with index numbers starting after the deleted entries.

 

There is no DELETE query.  There is (what appears to be) a well-formed update query, but it only acts on one record, not a range; and what is being deleted (or not moved into permanent memory?) can be more than one record.

 

Thanks for your help so far -- John

 

PS> Fenway, I read up on "FLUSH" and it doesn't seem to be a solution, but I tried it, and will report back tomorrow to see if my new test records mysteriously disappear.

 

[PFMaBiSmAd]

I would recommend changing your database access password. Perhaps another account accidentally or deliberately started accessing your database.

[PFMaBiSmAd]

The mysql query log file will show what queries, when they occurred, and what username (which would help pin down if it is a script or a utility program) they are being executed under.

[jongira]

Thanks again.  I will read up on how to access the query log.  The server host people said: "Since there is no log on transactions on the database, it is almost impossible for us to track what is removing the entry.  I highly suspect that the problem is within the application."

 

But the application hasn't been changed in years!  And the problem is new.

 

I will try your password change suggestion as well, tomorrow morning.

 

Further info:  New entries 'stuck' for a whole 30 hours, before getting mysteriously deleted.  So it's not a 24-hour crontab, there goes that theory.

 

Thanks for your suggestions

 

 

[fenway]

Is there replication involved? Are you the only one with access to this DB?

[jongira]

hi fenway.  The problem still goes on.  The man who wrote the original code has moved on, and this is my learning experience in mySQL/PHP.  There is no replication, just the one database, which I manually back up every week or so.  Others have access to the data entry, edit, and review (display) screens.  The php code for the entry and display screens do not have a DELETE query anwhere in them.  The edit screen code does have a DELETE query, but it looks properly written.  At any event, I asked the data input people to hold off doing any entries for a few days, and the problem persisted.  I changed the passwords to both the host web account and the database account, as PFMaBiSmAd suggested.  Even records entered manually with TOAD stick around for a day (it seems to work that the entries stick around for about a day, and disappear on the second day), and then disappear.  I was assured again by the host tech advisor that they have no process or crontab running that alters the database content in any way.  I suppose it's been a good experience, I know a hell of a lot more about php coding than I did before.  But it's still frustrating as heck!

 

Thanks for your suggestions. 

 

ps... the only delete code is:

if ($_GET['function'] == 'delete') {
$deleteSQL = "DELETE from tblEvents where id_event=" . $_GET['id_event'];
mysql_select_db($database_JSJBF, $JSJBF);
    $result = mysql_query($deleteSQL, $JSJBF) or die(mysql_error());
$location = "Location: [***mylocation***].php";
header($location);
exit;
}

 

I guess the next step is to disable this chunk of code and see if the problem still happens.

 

[PFMaBiSmAd]

If that is the only validation that the code is doing, anyone (browser or bot script) can visit that page with ?function=delete&id_event=any_number on the end of the URL and the row matching the id_event value will be deleted.

 

It would be necessary for the code on that page to test if the current visitor is authorized (logged in) and if they have permission (user access level or ownership) to delete the row.

 

The mysql query log would show some information, but any application should always log the when (datetime), who (ip address and if present user id information and any other information about the user that came with the page request such as HTTP_REFERER, HTTP_USER_AGENT, POST/GET/COOKIE values), what (the exact query that was executed), where (the file and line number of the query), and the results (error number/message or success status) for each query. The php error_log() function is intended for this purpose.

[jongira]

Hi fenway and PFMaBiSmAd...

 

First, I want to thank you again for you patience and help.

 

It seems that changing the passwords and disabling the DELETE subroutine fixed the problem... I wouldn't have thought of that without your help. 

 

Yes, the pages are protected by log-in, all the pages have SESSION and ISSET statements.

 

I'm a fool, for trusting human nature, and would never have thought that the malfunction I described could have been caused by a malicious third party... but changing the passwords seems to have done the trick.

 

You guys are great!  Thanks for helping a newbie in a time of need: you fixed my problem.  I don't know how to mark a post as "solved" with the green check-mark.  I think this one is solved, thanks to you guys.

 

Thanks so much, John (Jongira). (who learned a LOT from this exercise)

 

 

[PFMaBiSmAd]

Is the database login information (user/password) that the script uses stored as php code in a .php file or are they stored in a .inc or .txt file or some other extension?

 

If they are not stored in a .php file, if someone knows or guesses the file name they can browse to that file and see what is in it. If the file is a .php file, if they browse to it, they would only see any content that is output by that file.

[jongira]

The login info is in a .php file, which is called by a 'require once'.

 

I added a new table which records the ip, recordnumer and timestamp of any delete requests, because my host doesn't log transactions.  I've learned a lot about security because of you, and thanks again.  I was going to ask if storing the info in a .php file was secure, and you anticipated my question!

 

Changing the passwords seems to have done it, tho.  I do have one question for you: how long does the session data persist?  I don't see any code that calls for an 'end session' specifically (except for a log out on the main page).  But I don't think the input gal actually logs out, she just clicks out of the browser when she's done.  Does the session end when the web connection is closed?