Out of memory error

[keepingbusy]

Hi

We have a developed an Oscommerce site and recently installed an add on called star product. This worked perfect when there were only 200 records but now we have over 50,000 when you click on the Star Product link in the admin panel we get the following message:

Warning: mysql_query() [function.mysql-query]: Unable to save result set in /homepages/X/XXXXXXXXXX/htdocs/admin/includes/functions/database.php on line 45
2008 - MySQL client run out of memory

select sp.product_id, pd.products_id, pd.products_name from star_product sp, products_description pd where pd.language_id = '1'

[TEP STOP]

2013 - Lost connection to MySQL server during query

select count(*) as total from sessions where sesskey = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

[TEP STOP]

As a fairly new user to all this, can someone please explain (if known) what the problem is.

If you guys need any futher information to help me resolve the above, please post it and I'll find out.

Many thanks
Lee

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[audiopro]

Is star_products a table?
What does this part of the query do?

select sp.product_id, pd.products_id, pd.products_name from star_product [red]sp, products_description pd [/red]where pd.language_id = '1'

Keith

http://www.studiosoft.co.uk

 

[keepingbusy]

Hi Kieth

star_product is a table.

My understanding of the sql statement is that it puts a pre-selected title which has been taken from another table as the "Star Product" into the star_product table (if that makes sense).

The error message has only appeared since populating the main tables with over 50,000 titles (everything else works ok)

Thank you for your reply
Lee

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[towerbase]

How many rows are there in each of the two tables?

I would have thought that there should be something in the WHERE clause of your SQL to link together the two tables otherwise you might get an enormous result set which could explain the Lost Connection.

Something like:

SELECT sp.product_id, pd.products_id, pd.products_name 
FROM star_product sp, products_description pd 
WHERE  pd.language_id = '1'
[red]AND sp.product_id = pd.products_id[/red]

Andrew
Hampshire, UK

 

[audiopro]

I am no expert with MySQL but the query just looks wrong.
I would break it apart and try the component bits to test the results set.
Try

select sp.product_id, pd.products_id, pd.products_name from star_product where pd.language_id = '1'

and see what you results you get.

I was thinking along the lines of Andrew's comments where the query is returning far too many records, possibly the same records multiple times and throwing its toys out of the pram as a result.
Try the same query with a limit set and look through the results to see if they are unique.
MySQL doesn't always throw an error even though a query is iffy. I have had queries on tables containing only a few records which have returned millions of matches. Clearly something wrong with those queries but no errors.
MySQL is great but during development I always check the number of rows returned just to see if the number is reasonable.

Keith

http://www.studiosoft.co.uk

 

[mbrooks]

What my.conf are you using? If you are running Apache and MySQL on the same server use the my-large.cnf.

M. Brooks

http://mbrooks.info

 

[keepingbusy]

towerbase, audiopro, mbrooks

Thank you for your replies guys.

After some research, it appears that the hosting package we were setting this website up on, did not have the necessary band width and couldn't cope with sql statements that involved large tables.

As we speak, the person we are doing the website for is switching to a different package (Business Server 1) which has a much improved specification and capable of handling anything.

I'm grateful for your time in posting on this thread.

Merry Christmas and Happy New Year to all

Lee

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk