PHP Language - Out of memory PHP / MySQL problem? Is memory not clearing itself after sub routing??

Author

Out of memory PHP / MySQL problem? Is memory not clearing itself after sub routing??

cluthz

2007-05-07, 6:58 pm

Hi there,

I have written a script to update some tables. There are 15000 rows in my
table and the table has about 35 fields.

My PHP script first of all completes a select on the main table like so:

select * from maintable

Then I enter a for each statements which for each row in the main table,
updates another table with a value from maintable. E.g.(psudoe-ish code)

*************START*******
requireonce Db.php

DB connect(to db)

select name, email, id from maintable

For each (Row in maintable)
Call subroutine which - DB connects to same database again (As in
subroutine)
subroutine performs an update to another (new) table
all done and free to move onto next maintable row.
End for each

********END**************

When first tested on a sample of 150 rows all worked perfectly. However,
when I run it on the full 15000, I got an error message:
Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to
allocate 39 bytes) in /usr/share/pear/DB/common.php on line 682

Therefore I amended the code to make it echo to screen a counter on each
iteration of the loop that runs through each.

When I do this it does not give an error message, but the counter only gets
to about 5300 and then just seems to stop without reason or reporting any
errors. The browser just says DONE.

So what can I do. I did try "unset"ing variables at the end of the
subroutine to no avail

OK I'm thinking I can write this code to only perhaps address 5000 rows at a
time. But I don't think I should have to as each time the subroutine
shouldn't any memory and connections be freed up?

So maybe this means I have another problem with my code, either way I
thought I would s

an expert opinion. Experts... any help appreciated.

thanks in advance.

peter

2007-05-07, 9:58 pm

> Hi there,
>
> I have written a script to update some tables. There are 15000 rows in my
> table and the table has about 35 fields.
>
> My PHP script first of all completes a select on the main table like so:
>
> select * from maintable
>
> Then I enter a for each statements which for each row in the main table,
> updates another table with a value from maintable. E.g.(psudoe-ish code)
>
> *************START*******
> requireonce Db.php
>
> DB connect(to db)
>
> select name, email, id from maintable
>
> For each (Row in maintable)
> Call subroutine which - DB connects to same database again (As in
> subroutine)
> subroutine performs an update to another (new) table
> all done and free to move onto next maintable row.
> End for each
>
> ********END**************

When you use foreach unless it is a reference to the original array it work
on a copy of the array. Also do you actually use all 35 fields if not then
you should change your query so that it only retrieves what you need to use
(not doing so wastes memory).

You may consider using a where clause if you do not actually need to work
with all 15000 rows.

Not knowing what db package you are using (but presuming mysql as the
engine) but it is possible to do a mysql query without loading all of the
results into memory by using the mysql_unbuffered_query() function.

cluthz

2007-05-07, 9:58 pm

Further details of the query was as follows.
It was PHP4/ MYsql 3 (unfortunlety).

I did not actually request all 35 fields, only about 4 of them. I needed to
do all rows as this is a one off change. Basically on a customer details we
were moving from storing the email address into the main customer details
into a separate table so that the customer can have more then one email
address stored.

In fact it has caused all sorts of other challenges which not doubt you guys
will hear about, but I fixed this problem by just rather then making it
update the new table in a sub routine which called again to a connection to
a database, I just done it within the main routine itself. Made it bigger
but as one off seems to work fine when I do this.

Really I would still expect it to work with the sub routine, and would like
to fully understand why it didn't. but I guess I'm just working with alot of
data.

Thanks

Michael Fesser

2007-05-08, 6:58 pm

..oO(cluthz)

>I did not actually request all 35 fields, only about 4 of them. I needed to
>do all rows as this is a one off change. Basically on a customer details we
>were moving from storing the email address into the main customer details
>into a separate table so that the customer can have more then one email
>address stored.

Moving data from one table to another can often be done with pure SQL.
Have a look at SELECT INTO or INSERT SELECT statements for example.

Micha

Tom

2007-05-10, 9:58 pm

"cluthz" <WHATEEVVEERR@by.co.uk> wrote in message
news:BuR%h.14704$Ro3.500@text.news.blueyonder.co.uk...
>
> Further details of the query was as follows.
> It was PHP4/ MYsql 3 (unfortunlety).
>
> I did not actually request all 35 fields, only about 4 of them. I needed
to
> do all rows as this is a one off change. Basically on a customer details
we
> were moving from storing the email address into the main customer details
> into a separate table so that the customer can have more then one email
> address stored.
>
> In fact it has caused all sorts of other challenges which not doubt you
guys
> will hear about, but I fixed this problem by just rather then making it
> update the new table in a sub routine which called again to a connection
to
> a database, I just done it within the main routine itself. Made it bigger
> but as one off seems to work fine when I do this.
>
> Really I would still expect it to work with the sub routine, and would
like
> to fully understand why it didn't. but I guess I'm just working with alot
of
> data.
>
> Thanks
>
>

There is a "mysql_free_result()" function that may help you out if the size
of your results is affecting you. I don't think you could use it on your
initial query with your primary connection, since you need to work from that
list, but may be able to use the on you secondary connection to clear the
results after each mysql_query().

Tom
--
Newsguy.com
90+ Days Retention
Higher levels of article completion
Broader newsgroups coverage

Sponsored Links