Re: Problem with SELECT SQL_CALC_FOUND_ROWS
A quick look at the PHP site... http://www.php.net/manual/en/faq.databases.php#faq.databases.upgraded Regards, Jigal. - Original Message - From: Matt Babineau [EMAIL PROTECTED] To: 'MySQL General' mysql@lists.mysql.com Sent: Tuesday, February 01, 2005 9:20 PM Subject: Problem with SELECT SQL_CALC_FOUND_ROWS Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] !DSPAM:41ffe4c9213322003044064! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] RE: Problem with SELECT SQL_CALC_FOUND_ROWS
Matt Babineau wrote: Ok I installed PHP 4.3.10 and it still has not fixed the problem. If I remove the SQL_CALC_FOUND_ROWS from the query, it works no problems! This is very strange behavior! Not really that strange, I think... While you might want to read this: http://us4.php.net/manual/en/faq.databases.php#faq.databases.upgraded It sounds like your problem is more closely related to this: http://bugs.php.net/bug.php?id=16906edit=1 paying particular attention to this bit: [1 Oct 2002 4:37am CEST] g at firebolt dot com I was able to solve this bug by doing the following... granted, the bug only existed for me once I had a table with 9 rows. Run a: SET SQL_BIG_TABLES=1; And MySQL will utilize more memory and be able to save the result set. Optionally, when done, do this: SET SQL_BIG_TABLES=0; (tip courtesy of: http://www.faqts.com/knowledge_base/view.phtml/aid/9824) Keep in mind that when you do SQL_CALC_FOUND_ROWS MySQL has to do a BUNCH more work and MySQL and PHP have to save a TON of temporary somewhere for a large table. So if your tables are large, or if you are doing a JOIN between two moderate sized tables, it seems quite possible to me that SQL_CALC_FOUND_ROWS will trigger a problem with running out of storage space, when the same query without it won't trigger that problem. -- Like Music? http://l-i-e.com/artists.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with SELECT SQL_CALC_FOUND_ROWS
Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED]
Re: Problem with SELECT SQL_CALC_FOUND_ROWS
Matt Babineau [EMAIL PROTECTED] wrote on 02/01/2005 03:20:49 PM: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] I am by not a PHP expert but it seems that your error message is coming from PHP and not from MySQL (based on the name of the function in the message). Please post the code that surrounds this statement and try to determine and indicate which line is throwing the error, please. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
Ok here is the code chunk: $rows = is_numeric($_GET['rows']) ? $_GET['rows'] : 5; $stRow = 0; // SEARCH CODE $sql = SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = '$state' limit $stRow, $rows; $search = mysql_query($sql); echo $sql; $sql = SELECT FOUND_ROWS(); $ctTotalResults = mysql_fetch_row(mysql_query($sql)); It errors out on the first $search = mysql_query(); statement. Matt Babineau Criticalcode w: http://www.criticalcode.com http://www.criticalcode.com/ p: 858.733.0160 e: [EMAIL PROTECTED] _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:25 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt Babineau [EMAIL PROTECTED] wrote on 02/01/2005 03:20:49 PM: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] I am by not a PHP expert but it seems that your error message is coming from PHP and not from MySQL (based on the name of the function in the message). Please post the code that surrounds this statement and try to determine and indicate which line is throwing the error, please. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Problem with SELECT SQL_CALC_FOUND_ROWS
Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
Ok I installed PHP 4.3.10 and it still has not fixed the problem. If I remove the SQL_CALC_FOUND_ROWS from the query, it works no problems! This is very strange behavior! Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Matt Babineau [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:52 PM To: 'Michael Dykman' Cc: 'MySQL General' Subject: RE: Problem with SELECT SQL_CALC_FOUND_ROWS Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
Matt, If you go through the various build options tried in that bug report, you will see that it is a subtle bug and apparently still unresolved. I couldn't tell you with any degree of confidence will fix it. The bug report does show a couple of configure options for PHP which seem to elimiate it, without shedding much light on the underlaying cause. Possible conflict in zlib versions or some other common library between PHP and MySQL build.. I would closely examine the dependant libraries via $ ldd... Hard to tell without knowing the OS, the specific software builds and the various library versions especially any shared ones.. If you have a test system you can use, you might consider experimentally building both PHP and MySQL from source and explicitly set them to use the same versions of any shared libraries? Might be a bit of overkill... - michael dykman On Tue, 2005-02-01 at 15:51, Matt Babineau wrote: Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with SELECT SQL_CALC_FOUND_ROWS
I'm confused. That bug was closed over 2 years ago. What makes you think it applies here, and why do you say it is still unresolved? Michael Michael Dykman wrote: Matt, If you go through the various build options tried in that bug report, you will see that it is a subtle bug and apparently still unresolved. I couldn't tell you with any degree of confidence will fix it. The bug report does show a couple of configure options for PHP which seem to elimiate it, without shedding much light on the underlaying cause. Possible conflict in zlib versions or some other common library between PHP and MySQL build.. I would closely examine the dependant libraries via $ ldd... Hard to tell without knowing the OS, the specific software builds and the various library versions especially any shared ones.. If you have a test system you can use, you might consider experimentally building both PHP and MySQL from source and explicitly set them to use the same versions of any shared libraries? Might be a bit of overkill... - michael dykman On Tue, 2005-02-01 at 15:51, Matt Babineau wrote: Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
I do see that the bug was closed over 2 years ago - I guess then it was a persistent problem, but now it is limited to the configuration of the MySQL Server. As I have found out today, Quadrupling the stock memory limits on the MySQL Server solved the problem...not sure what will happen when the database starts growing but, my query is solid, and I am only returning a few rows at a time, so hopefully over a couple hundred queries wont make this thing die! :) Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 3:55 PM To: Michael Dykman Cc: Matt Babineau; 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS I'm confused. That bug was closed over 2 years ago. What makes you think it applies here, and why do you say it is still unresolved? Michael Michael Dykman wrote: Matt, If you go through the various build options tried in that bug report, you will see that it is a subtle bug and apparently still unresolved. I couldn't tell you with any degree of confidence will fix it. The bug report does show a couple of configure options for PHP which seem to elimiate it, without shedding much light on the underlaying cause. Possible conflict in zlib versions or some other common library between PHP and MySQL build.. I would closely examine the dependant libraries via $ ldd... Hard to tell without knowing the OS, the specific software builds and the various library versions especially any shared ones.. If you have a test system you can use, you might consider experimentally building both PHP and MySQL from source and explicitly set them to use the same versions of any shared libraries? Might be a bit of overkill... - michael dykman On Tue, 2005-02-01 at 15:51, Matt Babineau wrote: Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
