A client's server is having a problem with a table that gets all
records overwritten in the table with the same data.

Server info:
MySQL 4.1.22
PHP 5.3.x (current)
WHM / CentOS
Dual Xeon with 4 gigs RAM

The table in question is data from a rather lengthy form (75+ fields),
the table itself containing over 125 fields. By the client's request,
I have an AJAX script running which saves the data frequently at
various points within the app onblur.

All of the update queries have a LIMIT 1 and the WHERE is based on the
auto-increment id field.

The "corruption" occurs periodically - every two to three months. I
have logs files recording the queries made by the applicable scripts
which are 100% correct - no bad queries. Afer the second time this
happenned, I created a backup table which is generated every 30
minutes making the restoration fairly fast and painless.

There is no corruption of data in any other tables or databases on the
server.

I am only guessing, but could there be a collision of two queries
being submitted at the same time which could cause this behavior?

If so, would it be a good idea to upgrade MySQL and use transactions
for these updates?

Any other ideas of the cause and solution?

TIA

Tyrone Slothrop wrote:
I cant see how..

" all records overwritten in the table with the same data." sounds to me
like UPDATE...SET with a missing WHERE

Or WHERE clause that occasionally matches the whole table.

But this is guesswork.


> TIA

On Jul 1, 10:47*pm, The Natural Philosopher <t... (AT) invalid (DOT) invalid>
wrote:
I have to say that that is my guess too.

On Thu, 1 Jul 2010 15:14:54 -0700 (PDT), Captain Paralytic
<paul_lautman (AT) yahoo (DOT) com> wrote:

That is what I thought as well but I have all queries logged. I have
pored over them and found no errors. All have complete SETs and WHERE
statements have the index of the record specified.

A few of the fields are encrypted. I recently discovered that
addslashes will not work properly in escaping these strings and that
mysql_real_escape_string() is the proper PHP function to call. It may
be possible that this data is corrupting the query. I may very well
be creating my own SQL Injection Attack, idiot that I am.

Comments?

Tyrone Slothrop wrote:
addslashes() has not been the "right" function to call since well before
PHP 4.0. But you should be able to see that in your query log.

However, I also agree with Paul - your WHERE clause is probably
incorrect in one of the queries. Search for the data which is being put
into your table in your query log and see what the clause says.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Tyrone Slothrop wrote:
Ive got a horrid bug in a piece of code i no longer use, that's as
depserately irritating as this is.

Sometimes the record with ID=0 gets wiped. Despite that I never ever
call the delete with 'ID=0' and I can see no way I ever could.


However in this case, something does occur to me.

How are you logging the queries? In terms of what is sent to the mysqld
or is the mysqld itself logging the queries it has recieved?

I dunno how the daemon responds if - say - due to heavy traffic, packet
loss or something else - bad comms software, it only receives say half
an SQL instruction..I would assume it does the sane thing,

I have to say my curiosity is piqued. Are you definite that every record
ends up identical - not trashed randomly? That has to be the Mysql or
upwards process and not some lower process doing it, as below that level
- driver or hardware - the concept of a record wont exist. Merely flat
files for an ISAM system.

Another possibility is a very obscure bug in mysql, like it runs out of
resource at some point and instead of failing gracefully..does something
nasty.

But if so one would not expect to be the first to encounter it.

What is the complete system? php? innodB? etc. Where is it logging the
queries? Do system logs show any memory limitations?

I fear you are in for a long haul tho.

On Fri, 02 Jul 2010 01:26:20 +0100, The Natural Philosopher
<tnp (AT) invalid (DOT) invalid> wrote:

LOL. Yeah, a very long haul. And, yes, I should have converted to
mysql_real_escape_string long ago but the habits of over 12 years of
PHP/MySQL coding die hard.

This is one of those beasts that has been evolving over years with a
client that does not completely understand the dependencies that are
created in a system. Right now the app and its supporting code is
well over 3000 lines. There is stringent valdation on nearly every
required field since it involves financial services. Once the app
validates, it converts to XML and uses SOAP to send to another server.
It works very well except when the data craps out.

I am logging to a text file from the PHP script that is called from
the AJAX script. Once the SQL statement is generated and before it is
executed, the query appends to a log file (which is archived daily in
case I need to refer to it in a fit of madness).

The thing that makes me crazy is that it is one in a million query
triggers this thing. It has occurred 5 times in the last year.

What happens is that not all fields are overwritten, but all 60,000+
records are. It is always a certain set of fields which are
overwritten and not always the same set. Since the PHP script called
by AJAX does incremental saves as the form is filled out, this is the
script that is suspect. Within the log I can track the queries of an
individual applicant as each query gets a bit longer as more fields of
the form are completed.

There are all sorts of validations and replacements run on the data
(lots of regular expressions) which will clean out any bad data. The
encrypted fields must only be numbers. The values are run through a
Blowfish encryption function and are saved in a varbinary field.

I track the corruption via running a select on a dummy record which
should result in a certain dataset. If the data has been changed, I
know there is a problem. If no problem, the data is copied to a
backup table. These backups are run every 30 minutes so I can focus
in on when it happened fairly accurately. Restoration is as easy as
renaming tables and appending any subsquent records to the backup.

The reason saving the data incrementally is very important to the
client is that many people just plain do not finish it since is so
damned long. They get reminders every couple days for two weeks to
come back.

This is probably more information that you or others should need but I
imagine I needed to rant a bit to anyone who might listen. ;-) I
have simply run out of ideas why this happens.

Tyrone Slothrop wrote:
Tyrone,

So you're actually guessing that this is the failing script, when it
might not be? For instance, someone might have another script out there
which is completely unrelated to the one you're troubleshooting, and
that one may be causing problems.

Normally I wouldn't, but in this case I would recommend you turn on the
MySQL log on. You want to ensure this log is on a secure system since
it logs every MySQL call - including connect requests with passwords in
plain text. OTOH, you should see every query. And since you can
detect the failure easily enough, you can purge the old log entries
daily (or more often if you think it's necessary).

It will also cause a performance hit on MySQL, so watch the system
closely. If it's too much of a problem, you may have to turn it off.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Do you have all queries logged *BY MYSQL*?

What protection do you have against malicious or simply erroneous
queries to your AJAX script? Are there any old and buggy versions
of your AJAX script still on your server?

Since it happens every couple of months, have you considered that
it might be triggered by Google's (or some other search engine's)
web spider?

I once set up an administrative page (accessible from localhost
only) for maintaining a SPAM filter. It (stupidly) used a url like:
http://mysystem.example.org/spamfilt...e.php?id=63683 on a
"DELETE" button when I examined a particular record. Later I ran
a program to check for broken links on my site, doing a web-spider-walk
(Google does much the same thing. However, this one was run from
localhost, bypassing the security protection). Gee, where did all
the spam filter entries go? Don't use GET on URLs intended to make
changes in a database (add/delete records, change passwords, buy
stuff, send email, etc.), except incidentally for things like hit
counters. Use POST. And use SSL where appropriate.

AJAX, if not carefully done, can open up the possibility of someone
reading your entire database (credit card numbers, passwords, email
addresses, etc. ...) or erasing it . Having JavaScript pass in a
query to run (the ultimate SQL injection) is definitely *not* doing
it carefully.

Think about web spiders. Think about viruses. Think about thieves
trying to break into your system. Think about browsers with a broken
implementation of JavaScript. Remember, anyone can take your Javascript,
edit it, and put it up on their own web server.

Beware trying to pass raw binary over HTTP (if that's what you are
doing). If the \0 character appears in a GET or POST variable, it
could get chopped.

Using VARBINARY does not protect you against problems with \0
(encrypted text) passed in as part of a SQL query, assuming the
encrypted text is raw binary. This can be done right, but it's
tricky. If you did the encryption *on the MySQL server*, you should
be OK. If you do the encryption with PHP, you may need to use the
"mysqli" interface and prepared statements.

Tyrone Slothrop <ts (AT) paranoids (DOT) org> wrote:

This is not enough. You should let MySQL log the queries.

If you're fine with just the query, the binlog would be enough. It has
the advantage that it is rotated automatically and that it logs only
queries that modify data.

The general log provides more info, i.e. which MySQL account was used
and from which machine the login was done. It also records *all*
queries and thus grows rapidly. You have to rotate it manually.

Finally I have a bit weird idea: use mysql-proxy to filter your
queries. You just have to write few lines of Lua code to identify
the problem query (i.e. with a regex). Then proxy can log it and/or
suppress the execution of this query.


XL