ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
Delayed transactions INSERT/UPDATE with PHP
I have a PHP script on our internet server that sends either an INSERT or UPDATE SQL statement to a table in a small local database. I've tried this with mySQL and Postgress but get exactly the same weird effect.
Firstly, my PC based app sends the data to the PHP script which loads it into the table every 10 seconds in a sequence of about 50 transactions. I then close the app.
In the following hour new records are magically inserted or updated even though nothing is calling the PHP script.
I can only assume that PHP transactions that were made have somehow been delayed by up to an hour coming in randomly, out of sequence, every few minutes and changing the table.
Any help with understanding what's going on and how to solve it much appreciated.
Firstly, my PC based app sends the data to the PHP script which loads it into the table every 10 seconds in a sequence of about 50 transactions. I then close the app.
In the following hour new records are magically inserted or updated even though nothing is calling the PHP script.
I can only assume that PHP transactions that were made have somehow been delayed by up to an hour coming in randomly, out of sequence, every few minutes and changing the table.
Any help with understanding what's going on and how to solve it much appreciated.
Who is Participating?
It would take some additional details to figure out what is going wrong here.
Specifically, I need more detail on what is meant here: "my PC based app sends the data to the PHP script which loads it into the table every 10 seconds in a sequence of about 50 transactions. I then close the app. "
(1) It's not clear to me how your "PC based app" sends data.
(2) The PHP on the server runs the SQL queries? [i.e. not your PC based app]
(3) What starts the PC-based app?
With just what you've stated, I can say the following:
(1) Neither PHP nor MySQL randomly do things out of order or wait for hours.
(2) This means either (a) something is taking a significant amount of time [like transferring large data or extremely complex processing is causing some entries to appear later] or (b) something else is writing to the database or (c) there's an out of order execution happening where it is not that data happens late so much as data and processing cross awkwardly.
Specifically, I need more detail on what is meant here: "my PC based app sends the data to the PHP script which loads it into the table every 10 seconds in a sequence of about 50 transactions. I then close the app. "
(1) It's not clear to me how your "PC based app" sends data.
(2) The PHP on the server runs the SQL queries? [i.e. not your PC based app]
(3) What starts the PC-based app?
With just what you've stated, I can say the following:
(1) Neither PHP nor MySQL randomly do things out of order or wait for hours.
(2) This means either (a) something is taking a significant amount of time [like transferring large data or extremely complex processing is causing some entries to appear later] or (b) something else is writing to the database or (c) there's an out of order execution happening where it is not that data happens late so much as data and processing cross awkwardly.
> .. new records ..
are these records with the same data as already added?
I assume that your clients (browser) sends the same request again and again, for example if a browser is restarted
are these records with the same data as already added?
I assume that your clients (browser) sends the same request again and again, for example if a browser is restarted
I've been doing more testing in the meantime so I'll put the results here and hopefully answer some of the requests for more info above. To clarify one point before I begin, the PC based app sends the data to the php script via a .php?QUERY_STRING. The data it's sending is only 4 fields with minimal data.
The sequence I've now followed in testing is:
1. I have an empty table. Via the php script I INSERT one record:
UserID: int; DateTime stamp; Duration: int; DocumentName: varchar(100)
2. Every 10 seconds after that an UPDATE query, sent via the php script, updates the Duration field by 10 seconds. It does this 30 times. After each UPDATE I do a SELECT query on that record and the database reports that the information updated is in fact there.
3. I then close any app or anything else that can talk to the database. I do a DELETE FROM myTable. To double check it's empty I do a SELECT * FROM myTable and it reports no rows.
4. I then at 2 or 3 minute intervals over the next hour do a SELECT * FROM myTable. Each time I do a select, random values from the sequence of 30 updates appear. There is no particular order or sequence to them. Finally, they settle down to a value that does not change. It's usually somewhere in the middle of the sequence.
I think from this I can surmise that it is in fact not the PHP scripts being delayed: an update on an empty table would not produce a record. It seems more like inexplicable behaviour of the database. However, what I cannot fathom is that I have set this up on both mySQL and Postgres and get exactly the same behaviour. The server is not ours, we rent the space on it so I am a bit vague about the underlying setup.
The sequence I've now followed in testing is:
1. I have an empty table. Via the php script I INSERT one record:
UserID: int; DateTime stamp; Duration: int; DocumentName: varchar(100)
2. Every 10 seconds after that an UPDATE query, sent via the php script, updates the Duration field by 10 seconds. It does this 30 times. After each UPDATE I do a SELECT query on that record and the database reports that the information updated is in fact there.
3. I then close any app or anything else that can talk to the database. I do a DELETE FROM myTable. To double check it's empty I do a SELECT * FROM myTable and it reports no rows.
4. I then at 2 or 3 minute intervals over the next hour do a SELECT * FROM myTable. Each time I do a select, random values from the sequence of 30 updates appear. There is no particular order or sequence to them. Finally, they settle down to a value that does not change. It's usually somewhere in the middle of the sequence.
I think from this I can surmise that it is in fact not the PHP scripts being delayed: an update on an empty table would not produce a record. It seems more like inexplicable behaviour of the database. However, what I cannot fathom is that I have set this up on both mySQL and Postgres and get exactly the same behaviour. The server is not ours, we rent the space on it so I am a bit vague about the underlying setup.
How are you doing the later selects (i.e. your step 4)?
i.e. are you using an ODBC connector, MS Access, phpmyadmin, or some other means?
I can imagine either an issue with cached DB results due to a faulty connector OR a system where in fact they are using a large number of slave copies of the database that are delayed in getting the updates.
i.e. are you using an ODBC connector, MS Access, phpmyadmin, or some other means?
I can imagine either an issue with cached DB results due to a faulty connector OR a system where in fact they are using a large number of slave copies of the database that are delayed in getting the updates.
The step 4 SELECTs are done using myPHPAdmin or myPGAdmin in either case.
Whatever ends up there after about an hour becomes the permanent state of the table, that's to say, even following the DELETE FROM an hour later there will be a permanent record in the table that then doesn't change.
Whatever ends up there after about an hour becomes the permanent state of the table, that's to say, even following the DELETE FROM an hour later there will be a permanent record in the table that then doesn't change.
I've turned off AUTOCOMMIT and I do an explicit commit in the php directly following the INSERT/UPDATE.
If I do SHOW STATUS in phpMyAdmin and check Threads_connected it says 1 both during the process and following it.
If I do SHOW STATUS in phpMyAdmin and check Threads_connected it says 1 both during the process and following it.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
I've finally resolved it simply by using POST rather than GET as the method for transferring data to the php script.
I'm canceling my subscription to this site as questions like this never get answered.