Stupid Database Tricks

[Lantyssa]

I'm having an order of operations problem.  MySQL database being accessed by PHP code. [leaving out error checking and extraneous commands]

The idea is to get a field which matches the key from table(1).  Using that field, update a corresponding field in table(2).  Then delete the row from table(1) as it is no longer needed.  My code is all fine, but the row gets deleted before the value is retrieved.  So I need to do locking, or combine commands, or such.  Doing a join to combine the select and update would make sense, but that still wouldn't prevent the delete from happening first.


    $result = mysql_query("SELECT * FROM [table1] WHERE vkey='[key]';");
    ($row = mysql_fetch_object($result)) ? $field = $row->[field] : [error stuff];

    $result2 = mysql_query("UPDATE [table2] SET flag='[flag]' WHERE user='';");

    $result3 = mysql_query("DELETE FROM [table1] WHERE vkey='[key]';");

So how do I force the order?

Edit: last query to use same format.

[Morat20]

So running:

UPDATE [table2]
SET flag=[flag]
WHERE vkey in
  (SELECT flag
   FROM [table1]
   WHERE vkey='key'
   )

then run the delete * from table1 where vkey=key?

That runs the delete first?  I admit I'm confused why the delete is happening first. SQL commands should run sequentially, so I guess it's a PHP issue? (Something I'm not very familiar with).

[Lantyssa]

Well, it should be sequential, and the php uses the results of one to determine bits of the next.  I would think it could even step to the next line without getting the results.  That it's been running DELETE / SELECT / UPDATE instead of SELECT / UPDATE / DELETE seems bizarre, but that's what happens.  Because of it, the SELECT returns nothing, so the UPDATE doesn't happen.

Thus my confusion.

[Morat20]

That's just weird. I'm a bit rusty on PHP, so let me double-check -- what is this doing?

$row = mysql_fetch_object($result)) ? $field = $row->[field] : [error stuff];

I get the if/then/else operator, what's $field = $row->[field] doing?
 

[Miguel]

How do you know it's not running sequentially?  Is your error handling stuff being invoked?

Coincidentally the MySQL engine is free to reorder SQL statements provided there is no data dependency (RAW, WAR, etc) created.

[Lantyssa]

Morat: The if/then statement is setting the $field variable to the value of one of the db fields.  If no result was returned, then it throws an error.

Miguel: Yes, my error handling is invoked.  Basically it's saying it can't find the field and stops execution.  (If I tweak it to stop later, the subsequent UPDATE shows that the field is null.)  But the row in the database is deleted, which shouldn't be possible if it stopped execution at the point it throws an error.  If the DELETE is commented out, it performs everything as expected (except for removing the row, of course).

[Miguel]

I would be curious to know what mysql_num_rows() is returning from your first fetch (before you call mysql_fetch_object()).

EDIT: also, it would be helpful to check if mysql_error() would return anything, like:

Code:

$result = mysql_query("SELECT * FROM [table1] WHERE vkey='[key]';") or print(mysql_error());

[Morat20]

You are double-checking to be sure that:

1) The actual record you are SELECTing for does exist in the first place. (Your comment about commenting out the delete and it running fine implies you are checking the DB directly, so this seems unlikely).
2) Your SQL SELECT statement is syntactically correct -- no hanging quote marks or anything?

I realize those are pretty fundamental break points, but I've seen -- and done -- things myself that turned out to have been something like that.

[Lantyssa]

The syntax is good.  I'm looking both in the db (which is nearly empty as I'm designing this from scratch), and I'm having it spit out the query strings while I'm debugging this.

That's part of why it's weird.  With the DELETE present the output literally dies at the SELECT, yet it has to have run or that field wouldn't have been deleted to have SELECT thrown an error.

I'll try those things tonight or tomorrow, Miguel.  Have to run to a job fair thing now.

[Morat20]

At this point I'd personally remove the delete statement, and run the SELECT with every bit of bug checking I could think of, complete with printing out the results to make sure I was getting exactly the records I wanted. No duplicates, no out-of-order fields, etc.

That's just because I've had issues where I've done some subtle screwup (generally where the actual SQL call to the DB ended up wrong -- mostly from screwups putting strings together).

Once there, I'd forget the update entirely and just try a select and then a delete, doing a record-by-record printout. In short, I'd make the damn code explicitly tell me on output which way it was working.

mySQL shouldn't be reordering those queries as they're dependent, which means PHP is doing it somewhere. You don't have any nested statements, so it doesn't look like you're accidentally nesting the delete inside the select (I've done that before with a misplaced bracket). (Maybe your actual code does).

Hmm.
 Are you doing a global select, a global update, then a global delete? Is there any chance it's working once (Select big list, update single item, delete big list, then going on to try to update second item in list)?

[Salamok]

what is this showing?
 $result = mysql_query("SELECT * FROM [table1] WHERE vkey='[key]';") or die(mysql_error()."
query = SELECT * FROM [table1] WHERE vkey='[key]';");

[Miguel]

I just tested the following code on my machine (here's my mysql and PHP versions).  It works as expected (first select retrieves the key/value pair, and second table is updated, first key/value then deleted).

mysql  Ver 14.14 Distrib 5.1.51, for pc-linux-gnu (x86_64) using readline 5.1
PHP 5.3.5-pl0-gentoo (cli) (built: Jan 17 2011 11:18:42)

<?php

/*
 * create table test1 ( key1 integer primary key not null default 0, data integer not null default 0 );
 * create table test2 ( key2 integer primary key not null default 0, somedata integer not null default 0 );
 * insert into test1 values (0, 25);
 * insert into test1 values (1, 50);
 * insert into test2 values (0, 234245);
 * insert into test2 values (1, 43342);
*/

$database = "test";
$username = "testuser";
$password = "testpassword";

$mysql_conn = mysql_connect( "localhost", $username, $password ) or die(mysql_error());
$db_conn = mysql_select_db( $database, $mysql_conn ) or die(mysql_error());

/* Since where clause is by primary key, we can only return 1 or 0 result rows */
$result = mysql_query("SELECT * FROM test1 WHERE key1 = 0;") or die(mysql_error());
if (mysql_num_rows($result) == 1) {

    $row = mysql_fetch_object($result) or die(mysql_error());

    $result2 = mysql_query("UPDATE test2 SET somedata = " . ($row->key1 + 33473) . " WHERE key2 = " . $row->key1 . "") or die(mysql_error());

    $result3 = mysql_query("DELETE FROM test1 WHERE key1 = " . $row->key1 . "") or die(mysql_error());

} else
    die(mysql_error());

mysql_close($mysql_conn);

?>

EDIT: wow, those were funky markup colors....no more CODE tags for you SMF!

[Morat20]

Wait, job fair?

[Salamok]

IS the f13 code tag doing that? If I had to look at code coloring in that scheme all day i'd shoot myself.

[Lanei]

I don't have any on-topic help.  So have a thread derail that your subject line reminded me of.
Specific to IBM db2, but if you know any SQL it will hurt your brain.

From: IDUG DB2-L (http://www.idugdb2-l.org/archives/db2-l.html)
Subject: DB2 SQL Reserved word list

There's reserved words and there are Reserved Words :)

For the fun of it, I attempted the following  (DB2 V7 for z/OS):

CREATE TABLE FROM     
(SELECT  CHAR (7) NOT NULL, 
WHERE   CHAR (7) NOT NULL, 
AND     CHAR (7) NOT NULL, 
BETWEEN CHAR (7) NOT NULL, 
EQUALS  CHAR (7) NOT NULL, 
NULL    CHAR (7))           
IN TS0023 ;           

It was successful!  (This was quite a surprise. I thought you had to enclose
column names in quotes ("") if they were Reserved Words. I also thought
that 'CREATE TABLE FROM' would generate a syntax error.)

I then inserted this row:

INSERT INTO FROM  VALUES                               
('SELECT ', 'WHERE  ', 'AND    ', 'BETWEEN', 'EQUALS ', NULL) ;

And now, the finale, my Reserved Word Query executed in SPUFI, with the result:

SELECT  SELECT                                                   
FROM  FROM                                                     
WHERE NOT WHERE = EQUALS                                       
AND NOT AND NOT BETWEEN AND AND BETWEEN AND NOT NULL IS NOT NULL ;                                                               

SELECT         
---------+---
SELECT                                                             


Summary:  The consequences of using Reserved Words in place of identifiers
in some contexts can be: (1) Hilarious; (2) Unreadable; (3) Undebuggable;
(4) Other.

[cironian]

AND NOT AND NOT BETWEEN AND AND BETWEEN AND NOT NULL IS NOT NULL ;                                                               

There is a point of ambiguity where any sensible parser should just just throw an error instead of trying to guess its way through the syntax. This is far, far beyond that point.

[Sheepherder]

If I'm reading it correctly, that's the output.

[Tarami]

No, it's part of the predicate. It's really saying:

SELECT  s
FROM  f
WHERE NOT (w = e) AND NOT (a NOT BETWEEN a AND b) AND NOT (n IS NOT NULL)

Which can be reduced to;
WHERE w <> e AND n IS NULL
(BETWEEN is boundary-inclusive and will therefore always evaluate to true if the same operand exists on both sides.)

Which is true for the single row as;
'WHERE' is not equal to 'EQUALS' and;
[NULL] is NULL.

The string 'SELECT' in the column SELECT is the output.

[Lantyssa]

The bizarre behavior continues.

I rewrote the function so the UPDATE and DELETE are in an if() statement, which requires a row to be returned or it just echos an error message.  Also made the delete depend upon the data returned by SELECT as well.  Some of the time the queries go off in proper order and the debug statements are all correct.  Other times it echoes the error message, but the UPDATE and DELETE still happen!  The one change that re-writing caused was for the UPDATE to always work, despite not even supposed to be executed, nor having the proper values if the if() is not entered.

Debug messages print out based on the initial SELECT and whether it returns a row or not.  Values are null before reaching this point of execution.  Commenting out the DELETE still causes the rest to execute flawlessly with expected debug output.

I'm pretty sure php is feeding the database the values and it's trying to execute them simultaneously, so it's luck which goes first.  The question is how the stuff in the if() block gets the proper data when it requires the SELECT to run.  Maybe it processes in pre-parsing or something.

Going to try some other solutions.

[Morat20]

Wow. That sounds suspiciously like some sort of weird-ass hanging bracket. Like you have statements nested, which means the if/then/else and loops aren't flowing properly.

But if that was the case, it should be far more fucked up than it is.

[Lantyssa]

It's really, really weird, it's true.  The entire script won't run if things are mismatched, and commenting out about as simple a line as possible makes it work.

And it is its own function, in a separate file, so no weird nesting either.  Not that wouldn't have other drastic and noticeable consequences.

[Morat20]

Myabe your mySQL server is misconfigured? You're indicating it's reordering operations, which is manifestly shouldn't. (I mean yes, it does, but only non-conflicting ones. SELECT/UPDATE/DELETE is order dependent. A properly running mySQL installation shouldn't reorder those).

You only have one connection to the DB open, right? You're using it for all three commands (SELECT/UPDATE/DELETE)? PHP has to feed them sequentially. I suppose if it were threaded and you opened multiple connections you could get some wierd overlap.

[Lantyssa]

Rewrote it to split the call getting a value and then the calls to update/delete based upon that value.  (Which also means they are in separate db connection calls.)  It is definitely running through the commands twice.

I seeded my database with additional rows that matched the value it was looking up.  The first row is being updated, then the second row is being shown for my debug info as well as having the update/delete performed on them.

Once hidden, once as "expected".  So now I have to tear this stupid thing apart to figure out if it's something weird going on with sessions, or if the parser is just wonky.  I want to blame myself, because it's easier to feel stupid than declare a 'feature' is being troublesome, but the more I go over it and rewrite it with the same results I have to wonder.

[Morat20]

I can understand how frustrating that is. Sadly, I've always sucked at debugging if I can't just sit there and screw with the code myself.

*scratches head*. If it was ColdFusion or .NET I could probably be a bit more useful, since at the very least I could sit down and try it myself rather than theory-crafting.

What context are you running this in? Multiple users accessing a database through a web site? Multiple logins? Is this single-threaded, single session work? I think you're at the point where either you're looking at a feature that you didn't know was there -- or else the problem is larger than your single code snippet.

Like bad logic higher up the chain or some conflict between multiple sessions that sort of thing.

[Tarami]

Why not try some explicit locks to synchronize?

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

[Lantyssa]

I get to feel stupid.

Working back through everything on the page, the first code snippet redirects to https.  Most everything would just fail without session variables, except this one e-mail verification function.  Looking at the e-mail text I put "http://..." instead of "https://".  So it was running through twice.  Once hidden as http which was being refreshed before it was noticeable.

The times it seemed to inexplicably work were the ones where I refreshed instead of clicking the mail link.  But since I was frustrated and kept going back to manually re-enter data and tweak code, I never caught when the difference happened.

To answer your question about context, the website is doing account creation, e-mailing a verification link, then activating it through that.  It's probably primitive, but it's good to flex my coding muscles and it's teaching me a lot about security considerations.

[Morat20]

Ha! I've done something like that.

Security considerations are good to learn. You might want to go ahead -- if you haven't already -- get up to date on common attacks (like SQL injection) and go ahead and protect your DB. It's not hard (basically you just paramaterize your code so that clever buggers can't inject SQL code into, say, their account name) but it's good practice.

[Salamok]

So you moved the http->https redirect to the top of your code right?

[Lantyssa]

It was at the very top of the code.  There was, previously, no die() statement though, so this one function managed to run.  Everything else relies on session variables which wouldn't be present on an http page, but to get an e-mail verification to work it has to pass a POST statement.

A valuable lesson, which I have now gone back to adapt to a few other bits of code.

[Salamok]

sounds like you needed an exit; after the redirect.