Hi there,
On Fri, 8 Mar 2002, Paul DuBois wrote:
> At 21:14 +0100 3/8/02, Roger Baklund wrote:
> >* Paul DuBois
> >> At 17:00 +0000 3/8/02, Corin Hartland-Swann wrote:
> >[...]
> >> >This works for a varying number of rows (usually 5,000 to 10,000) before
> >> >it silently exits the loop. I'm using MySQL 3.23.49a
> >[...]
> >>
> >> That's also your problem. With mysql_use_result, it's *required*
> >> that you completely finish the query before issuing another one.
> >>
> >> I suppose you could write the IDs to a file, then read them back in
> >> and use them to issue the UPDATE statements.
> >
> >Why not make two connections, SELECT from one and UPDATE through the other?
Roger/Paul - sorry - I was half asleep when I wrote the paraphrased code.
It should have featured _two_ database handles, as suggested:
--------------------------------------------------------------------------
$sth = $dbh1->prepare("SELECT id,num FROM table", { 'mysql_use_result' => 1 });
$sth->execute;
$sth->bind_columns(\$id, \$num);
while ($sth->fetch)
{
$new_num = $hash{$id};
$dbh2->do("UPDATE table SET num = $new_num WHERE id = $id")
if $num != $new_num;
}
$sth->finish;
--------------------------------------------------------------------------
> I thought of that, too, but I don't think it would work. The SELECT locks
> the table until it's done. That will lock out the UPDATE, which needs a
> WRITE lock no matter which connection it's done over.
>
> Of course, if anyone tries it and find that it works, I'd be interested
> to know that.
Well, as above, it works - but then it just stops after 5k-10k rows.
I've checked the database log and the UPDATE queries do NOT stall waiting
for the SELECT to complete - they appear in the log at a rate of about 300
per second. In checking the logs, I realised I should probably point out
that although 5k-10k rows are /scanned/, only 600 or so rows are
/updated/.
This is weird, and I really can't work out what is going on. If someone
conclusively tells me (Monty?) that things don't work this way then I'll
do it another way (as discussed above) but otherwise I'd like to know what
might be going wrong.
For the record, I am not using UPDATE LOW_PRIORITY - just a normal one.
Thanks again,
Corin
/------------------------+-------------------------------------\
| Corin Hartland-Swann | Tel: +44 (0) 20 7491 2000 |
| Commerce Internet Ltd | Fax: +44 (0) 20 7491 2010 |
| 22 Cavendish Buildings | Mobile: +44 (0) 79 5854 0027 |
| Gilbert Street | |
| Mayfair | Web: http://www.commerce.uk.net/ |
| London W1K 5HJ | E-Mail: [EMAIL PROTECTED] |
\------------------------+-------------------------------------/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
