The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 16, 2019, 09:40:36 PM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: php mysql quirk? wtf again  (Read 3553 times)
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« on: February 12, 2008, 07:16:08 PM »

whats with all the quirks I am finding? Obviously the ASP methodology and the PHP methodology are less than compatible...

stripped down to the junk that does it. should only update 5 rows, but oh no, its gotta update like 30 (actually its random). And its all because of the sleep(60).
I assume the connection is resetting, but I don't get why it wouldn't just blow up? Why does it just go ahead and start processing more rows in the database?!?!
Code:

public function gettargets()
{
$sql = "select Phrase,PhraseID from phrases limit 5";
$rs = mysql_query($sql,$GLOBALS['database']->conn);
while ($row = mysql_fetch_assoc($rs))
{
$sql = "update phrases set PhraseDate='".date('Y-m-d H:i:s')."' where PhraseID=".$row['PhraseID'];
mysql_query($sql,$GLOBALS['database']->conn);
sleep(60);
}
}
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #1 on: February 12, 2008, 11:20:30 PM »

Dude I don't understand what you're trying to accomplish... you've put a re-query inside of a "while you're getting rows" loop... this code is guaranteed to fubar you nasty.

If what you are trying to do is re-query for 5 more every 60 seconds, then your loop should look more like:

while (my master work statement is true)
{
   mysql_query(for the 5 rows)
   while(mysql_fetch_object/assoc/row)
   {
      do code
      do code
   }
   sleep 60 seconds
}

or something like that... gotta run for the night, more tomorrow. In case you're confused, you only do the query once... you fetch until it comes back false, then you know you've exhausted the set returned by the query.

/p

<edit>
Looking a second time it's clear... you're using the same database connection that should be returning rows to do the update. You'll need two database connections to do this, or the way I do it is collect my 5 rows into an array, then do my 5 updates - that's lighter and faster. You can't use a database connection to do two things at once... if you're mid-query and returning rows, you can't execute an update with it, it'll fuck your dataset cursor.
</edit>
« Last Edit: February 12, 2008, 11:24:15 PM by perkiset » Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
DangerMouse
Expert
****
Offline Offline

Posts: 244



View Profile
« Reply #2 on: February 13, 2008, 03:41:47 AM »

This is quite interesting - thats something that I would have attempted myself. The only reason I can see for it not working, is that you're updating the same data that could be stored in your result set, which I guess could lead to problems - although I'm not sure this explains the random results.

I'm suprised the DB connection can't be reused, as ultimately the query has taken place and you're just looping a resultset resource which I'd have thought was already dragged into memory within the while statement.

DM
Logged
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #3 on: February 13, 2008, 07:56:33 AM »

The query function returns a handle to a MySQL result set, aka a cursor. As you fetch, you are literally requesting another row from that result set. This makes sense because if you have a result set of 10MM rows you would not want to wait for the result set to all DL before you could start processing. This behavior is the same as Oracle's.

If you retask <your connection> to the db, you are releasing the result set and all that would be left is a cached random mess of the original query. If you choose to update while connected, then the best way is multiple DB connections - otherwise, as I mentioned and if you know your result set is going to be small enough, the lightest way to go is to grab all rows into an array and then act on them.

(It's all much clearer when I am awake  ROFLMAO )
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #4 on: February 13, 2008, 11:01:46 AM »

thats what I figured, and i also had worked out that you would probably say that... so...
Code:
$database = new database();
$sql = "select * from phrases limit 5";
$rs = mysql_query($sql,$database->conn);
$phrasearray = array();
while ($row = mysql_fetch_assoc($rs))
{
echo $row['Phrase'].'<br>';
$phrasearray[] = array('Phrase' => $row['Phrase'],'PhraseID' => $row['PhraseID']);
}
unset($row); //just in case there is some wierd latency.
echo '----------<br>'; //just a separator.
foreach ($phrasearray as $row)
{
echo $row['Phrase'].'<br>';
$sql = "update phrases set PhraseScrapeDate='".date('Y-m-d H:i:s')."' where PhraseID=".$row['PhraseID'];
mysql_query($sql,$database->conn);
sleep(30);
}

it seeems to be the sleep(30).
obviously this is overly simplified, and you can guess what I am actually doing, but this oversimplification has the same problem. So this is the barebones example of what is going on.

This new version dumps the results into an array, then does the processing.

I understand what the issue is when I run a loop inside a loop, processing records from a record set, while it is "open", updating those records. It causes the original query to become invalid, when the next fetch is run, so it rebuilds it, and gets another 5 rows. The reason its random is because the script is timing out, and sometimes the queries run fast, others it runs slow, resulting in different numbers of records being processed.

DUMB

But i understand. ASP was sheltering me from this via ADO. I could open a recordset and just walk around in it, willy nilly, making changes to it while doing so. Each record would commit the changes once i did a .update, but I would still have the same records in the recordset, regardless of the changes I make.

In php/mysql, you apparently don't actually grab a whole recordset. you only grab the record for which your current cursor is located. So when you do a mysql_fetch_assoc, it increments the cursor counter by 1, and reruns the query from scratch, which means that if you have changed any of the criteria of the records from the prior result set which causes them to no longer be included in the result, you no longer get that record, and as such, you get a brand new one in this limit 5 scenario.

So you mean to tell me mysql_fetch_assoc runs the query 5 times against the database when I do
select * from table limit 5
while ($row = mysql_fetch_assoc($rs))
{
   echo 'wtf
';
}

I get it, makes sense. dumb, but makes sense.
So then answer me this sports fans...
Why the hell does it do it still when I have moved on, using a single loop to dump it all into an array. The new example, does just this. should process 5, but it instead processes about 15 give  or take.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #5 on: February 13, 2008, 11:43:24 AM »

Some quickies:
  • No latency on a row variable - it is a straight-ahead array reference that will be killed when you reuse it
  • The sleep 30 is probably a problem because of MySQL thinking you have died. I believe that the TTL parameter is setable somewhere, but if you're putting this stuff on multiple servers where you don't have access to the MySQL configs then this would become problematic over time.

As an efficiency measure, you could do your loop as tightly as this:

$set = mysql_query("select phrase, phraseid from phrases limit 5");
while ($phrases[] = mysql_fetch_array($set));

and the phrases array will have name=>value for each of the 5 rows.

Quote
So you mean to tell me mysql_fetch_assoc runs the query 5 times against the database when I do

select * from table limit 5
while ($row = mysql_fetch_assoc($rs))
   echo 'wtf
';

I get it, makes sense. dumb, but makes sense.
Doesn't run the query 5 times... just like Oracle, there is a temp set created for you (it's actually a temp table of pointers to records, but it is under the hood) and as you move the cursor you retrieve another record based on the pointer... so the query is done once and the cursor is bound to your connection to MySQL... when you die, the cursor becomes invalid and the set goes away. It's actually wicked fast.

Quote
So then answer me this sports fans...
Why the hell does it do it still when I have moved on, using a single loop to dump it all into an array. The new example, does just this. should process 5, but it instead processes about 15 give  or take.

If you are receiving more than 5 rows from a query where you explicitly state you want 5 then I am clueless, unless you're getting the entire table and there's some way to inhibit/unconfigure the LIMIT directive in MySQL which would be fantastically stupid.

Where are you getting the "15ish?"
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #6 on: February 13, 2008, 02:39:27 PM »

NBs - new posts up, as we discussed in PHP repository and PHP discussions.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #7 on: February 13, 2008, 03:24:49 PM »

or maybe not... LOL
still doing it!?!?!? dammit im gonna test more.

per our offline conversation.

the problem seems to be the connection to the database timing out, but PHP continuing along its merry way. What must be happening is that php, after 30 seconds, tries to do the next record, but Mysql is like "WTF are you talking about?" PHP must respond, "this recordset: select * from table limit 5" and mysql returns that queryset, which of course is now an updated recordset. Thats why i was getting an increase in processed records.

this is purely a "i used to be an ASP guy so i get my head stuck in the stairway railing" kind of thing.

again. move along, nothing to see here. other than how not to do something.
The solution is to open and close the connection each pass. Close before the sleep, and open when you loop again.
« Last Edit: February 13, 2008, 03:27:18 PM by nutballs » Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!