Internal Server Error problem

Legacy Dynamo
1

Hi,

I have recently come across this error when I click on a menu link to select a range of items (products) from the database.

To see this on the link, on the lefthand side under the Hydraulic Range heading, select HYDRAULIC COMPRESSION FITTINGS, CLAMPS & TUBE menu and the first item “Couplings”. This selects all the couplings in this range by looking for the word “Couplings” under the column heading ‘prodsection’

Here is the MySQL query: The $item is the search term from the menu, along with $line which is the product line.


"SELECT ceswebproducts.*, ceswebprices.listprice , ceswebprices.webprice, ceswebprices.yousave FROM ceswebproducts, ceswebprices
WHERE `ceswebproducts`.`prodsection` LIKE '%$item%'
AND `ceswebproducts`.`prodtitle` LIKE '%$inthere%'
AND `ceswebproducts`.`prodline` = '$line'
AND `ceswebproducts`.`partnumber` = `ceswebprices`.`partnumber`";

This query is working in on the current version of the site (the same url without the /2013-revised/test/), but now that I have created an updated version of the site (not yet live) using the same configuration of menu and database (although the product codes have changed the basic menu structure is the same), the query brings up this error.

I have run the same query on the server with phpadmin and it works fine.

It is the same on both the Freeway 5.65 version and the Freeway 6.12, which makes me think that it is something that is not quite right with the processing of the query from the HTML page.

I have found adding a space to the word Couplings on the database makes this work, but doesn’t help with other queries.

Can any one shed some light on this, it’s causing me some grief!

Thanks,

Steve

http://www.cotswoldengineeringsupplies.co.uk/2013-revised/test/

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

2

Can you point your form at Reflector, and see what the raw parameters being posted are? Your SQL seems fine, I don’t see anything out of place there.

I would caution you that you must escape any user input before you concatenate it into a SQL query as you are doing. At the very minimum, $item = mysql_real_escape_string(trim($_GET['input'])) or something like that. (http://php.net/manual/en/function.mysql-real-escape-string.php) Note that this is deprecated. Personally, I have switched to using PDO and bound variables in my (very limited) PHP work.

Walter

On Nov 20, 2013, at 8:13 AM, SteveB wrote:

Hi,

I have recently come across this error when I click on a menu link to select a range of items (products) from the database.

To see this on the link, on the lefthand side under the Hydraulic Range heading, select HYDRAULIC COMPRESSION FITTINGS, CLAMPS & TUBE menu and the first item “Couplings”. This selects all the couplings in this range by looking for the word “Couplings” under the column heading ‘prodsection’

Here is the MySQL query: The $item is the search term from the menu, along with $line which is the product line.


"SELECT ceswebproducts.*, ceswebprices.listprice , ceswebprices.webprice, ceswebprices.yousave FROM ceswebproducts, ceswebprices
WHERE `ceswebproducts`.`prodsection` LIKE '%$item%'
AND `ceswebproducts`.`prodtitle` LIKE '%$inthere%'
AND `ceswebproducts`.`prodline` = '$line'
AND `ceswebproducts`.`partnumber` = `ceswebprices`.`partnumber`";

This query is working in on the current version of the site (the same url without the /2013-revised/test/), but now that I have created an updated version of the site (not yet live) using the same configuration of menu and database (although the product codes have changed the basic menu structure is the same), the query brings up this error.

I have run the same query on the server with phpadmin and it works fine.

It is the same on both the Freeway 5.65 version and the Freeway 6.12, which makes me think that it is something that is not quite right with the processing of the query from the HTML page.

I have found adding a space to the word Couplings on the database makes this work, but doesn’t help with other queries.

Can any one shed some light on this, it’s causing me some grief!

Thanks,

Steve

http://www.cotswoldengineeringsupplies.co.uk/2013-revised/test/

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

3

Thanks, Walter

The input to the server from the web page is not a form but a query on a text link, your output says it is an array, and there is nothing extra getting in the way of it.

?item=Couplings&inthere=&line=Hydraulic%20Compression%20Fittings

All the input to the MySQL sever is from the webpage menu, there is no user input as all the links are “hardwired” to the links.

I’ll update the GET statement with the security input and see if that has any effect.

I’ll have to do that later as it’s now food time here in the UK.

Cheers,

Steve.

On 20 Nov 2013, at 16:29, Walter Lee Davis wrote:

Can you point your form at Reflector, and see what the raw parameters being posted are? Your SQL seems fine, I don’t see anything out of place there.

I would caution you that you must escape any user input before you concatenate it into a SQL query as you are doing. At the very minimum, $item = mysql_real_escape_string(trim($_GET['input'])) or something like that. (PHP: mysql_real_escape_string - Manual) Note that this is deprecated. Personally, I have switched to using PDO and bound variables in my (very limited) PHP work.

Walter

On Nov 20, 2013, at 8:13 AM, SteveB wrote:

Hi,

I have recently come across this error when I click on a menu link to select a range of items (products) from the database.

To see this on the link, on the lefthand side under the Hydraulic Range heading, select HYDRAULIC COMPRESSION FITTINGS, CLAMPS & TUBE menu and the first item “Couplings”. This selects all the couplings in this range by looking for the word “Couplings” under the column heading ‘prodsection’

Here is the MySQL query: The $item is the search term from the menu, along with $line which is the product line.


"SELECT ceswebproducts.*, ceswebprices.listprice , ceswebprices.webprice, ceswebprices.yousave FROM ceswebproducts, ceswebprices
WHERE `ceswebproducts`.`prodsection` LIKE '%$item%'
AND `ceswebproducts`.`prodtitle` LIKE '%$inthere%'
AND `ceswebproducts`.`prodline` = '$line'
AND `ceswebproducts`.`partnumber` = `ceswebprices`.`partnumber`";

This query is working in on the current version of the site (the same url without the /2013-revised/test/), but now that I have created an updated version of the site (not yet live) using the same configuration of menu and database (although the product codes have changed the basic menu structure is the same), the query brings up this error.

I have run the same query on the server with phpadmin and it works fine.

It is the same on both the Freeway 5.65 version and the Freeway 6.12, which makes me think that it is something that is not quite right with the processing of the query from the HTML page.

I have found adding a space to the word Couplings on the database makes this work, but doesn’t help with other queries.

Can any one shed some light on this, it’s causing me some grief!

Thanks,

Steve

http://www.cotswoldengineeringsupplies.co.uk/2013-revised/test/

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Design Artwork Illustrations & Websites
Steve Ballinger
SBDesign
email@hidden

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

4

An update:

I decided to create a new clean document with just that menu on the page. I created everything from scratch so that I knew that any error was down to the new configuration. At first I had the same error, but then I revised the query to add all the columns that I needed to choose from the ceswebproducts table:

SELECT ceswebproducts.prodline, ceswebproducts.prodsection, ceswebproducts.prodtitle, ceswebproducts.code, ceswebproducts.partnumber, ceswebproducts.proddescription, ceswebproducts.image, ceswebproducts.weight, ceswebprices.listprice, ceswebprices.webprice, ceswebprices.yousave 
FROM ceswebproducts, ceswebprices
WHERE `ceswebproducts`.`prodsection` LIKE '%$item%'
AND `ceswebproducts`.`prodtitle` LIKE '%$inthere%'
AND `ceswebproducts`.`prodline` = '$line'
AND `ceswebproducts`.`partnumber` = `ceswebprices`.`partnumber`

This query returned the correct products, so I thought if I used the same query on the other version it “should” work.
I copied the query to the page and uploaded, but, unfortunately the Internal Server Error reappeared. It seems I’m back to square one. Could it be something that is in the original Freeway file or uploaded page that is messing with the output and causing the error.

The working page is here, it’s very basic but at least I know that the query works as it should.

http://www.cotswoldengineeringsupplies.co.uk/fw6update/index.php

I am using the Actions MySQL Connect to connect to the database, MySQL Get Records to query the database, plus PHP Block surrounding a table to output the results from the query to the page. There is also some other custom php bits to do a few other things.

I could rebuild the site using the working version as a base for the revised site, but if I can find what part of the original site is messing with the output then I can rebuild from there.

I’m almost there, just need a bit of lateral thinking to solve this problem…

Steve

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

5

Hi all,

Just as a final update to this saga, I have found out the limit to how many rows the MySQL server will can handle before it fails; and that is 513, If you try to get 514 the server falls over with a 500 Internal Server Error.

This is something that shouldn’t happen as MySQL should be able to handle much more than this number of rows. I know that it should because it used to handle much more than this, which leads me to think that there is something wrong with the MySQL on the server.

I still haven’t been able to get a reply from James at Have Host as to whether there is a misconfiguration with the server.

The only way round this problem is to re configure the menus so that they don’t return more than 513 rows.

If I find a solution to this problem I’ll post an update, but until then I’ll struggle on with one hand tied behind my back.

Cheers,

Steve.

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

6

Steve, that’s just nutty. If those 513 rows are selected from a single table (not a big hairy join across bunches of tables) then they would have to be extraordinarily wide rows (tens of thousands of columns per row) before you would possibly be near any reasonable limit for request size. This doesn’t sound like it’s the real problem at all to me. Have you looked in your error logs – particularly in MySQL’s logs? What is the query, and what is the result when you ask for more than 512 rows?

Walter

On Nov 27, 2013, at 5:26 PM, SteveB wrote:

Hi all,

Just as a final update to this saga, I have found out the limit to how many rows the MySQL server will can handle before it fails; and that is 513, If you try to get 514 the server falls over with a 500 Internal Server Error.

This is something that shouldn’t happen as MySQL should be able to handle much more than this number of rows. I know that it should because it used to handle much more than this, which leads me to think that there is something wrong with the MySQL on the server.

I still haven’t been able to get a reply from James at Have Host as to whether there is a misconfiguration with the server.

The only way round this problem is to re configure the menus so that they don’t return more than 513 rows.

If I find a solution to this problem I’ll post an update, but until then I’ll struggle on with one hand tied behind my back.

Cheers,

Steve.

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

7

Hi Walter,

Thanks for the reply, I’m still no further forward, especially as I can’t locate the MySQL error logs on the server.

I did try the query on my SBDesign server, but as it is running on the same Have-Host server with the same configuration of MySQL (version 5.0.69), php etc, it still returns the same error. Also there aren’t any error logs either. Very Strange.

Here is the query I am using…

SELECT ceswebproducts.prodline, ceswebproducts.prodsection, ceswebproducts.prodtitle, ceswebproducts.code, ceswebproducts.partnumber, ceswebproducts.proddescription, ceswebproducts.image, ceswebproducts.weight, ceswebprices.listprice, ceswebprices.webprice, ceswebprices.yousave FROM ceswebproducts,ceswebprices
WHERE `ceswebproducts`.`prodsection` Like '%$item%'
AND `ceswebproducts`.`prodtitle` LIKE '%$inthere%'
AND `ceswebproducts`.`prodline` = '$line'
AND `ceswebproducts`.`partnumber` = `ceswebprices`.`partnumber`

The $item queries the Product Section column (prodsection), to refine the search the $inthere searches in the Product Title column (prodtitle) and the $line selects the Product Line (prodline) column, these results are then matched with the Part Number column on the Prices database which gives you the list price, web price and the saving.

It only uses 2 tables in one database, the longest line is about 153 characters long, and there are 6319 rows in both tables, so you can see that it isn’t a complex database or query.

I think I might have to move hosting for this client if I can’t get this resolved soon.

Steve.

On 28 Nov 2013, 12:31 am, waltd wrote:

Steve, that’s just nutty. If those 513 rows are selected from a single table (not a big hairy join across bunches of tables) then they would have to be extraordinarily wide rows (tens of thousands of columns per row) before you would possibly be near any reasonable limit for request size. This doesn’t sound like it’s the real problem at all to me. Have you looked in your error logs – particularly in MySQL’s logs? What is the query, and what is the result when you ask for more than 512 rows?

Walter

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

8

Do you have any indexes in this table? You may want to put an UNIQUE index on line and partnumber in ceswebproducts, and another on partnumber in ceswebprices. Other than that, I don’t see how it’s possible for this to die so specifically at > 512 results.

Walter

On Nov 28, 2013, at 12:39 PM, SteveB wrote:

Hi Walter,

Thanks for the reply, I’m still no further forward, especially as I can’t locate the MySQL error logs on the server.

I did try the query on my SBDesign server, but as it is running on the same Have-Host server with the same configuration of MySQL (version 5.0.69), php etc, it still returns the same error. Also there aren’t any error logs either. Very Strange.

Here is the query I am using…

SELECT ceswebproducts.prodline, ceswebproducts.prodsection, ceswebproducts.prodtitle, ceswebproducts.code, ceswebproducts.partnumber, ceswebproducts.proddescription, ceswebproducts.image, ceswebproducts.weight, ceswebprices.listprice, ceswebprices.webprice, ceswebprices.yousave FROM ceswebproducts,ceswebprices
WHERE `ceswebproducts`.`prodsection` Like '%$item%'
AND `ceswebproducts`.`prodtitle` LIKE '%$inthere%'
AND `ceswebproducts`.`prodline` = '$line'
AND `ceswebproducts`.`partnumber` = `ceswebprices`.`partnumber`

The $item queries the Product Section column (prodsection), to refine the search the $inthere searches in the Product Title column (prodtitle) and the $line selects the Product Line (prodline) column, these results are then matched with the Part Number column on the Prices database which gives you the list price, web price and the saving.

It only uses 2 tables in one database, the longest line is about 153 characters long, and there are 6319 rows in both tables, so you can see that it isn’t a complex database or query.

I think I might have to move hosting for this client if I can’t get this resolved soon.

Steve.

On 28 Nov 2013, 12:31 am, waltd wrote:

Steve, that’s just nutty. If those 513 rows are selected from a single table (not a big hairy join across bunches of tables) then they would have to be extraordinarily wide rows (tens of thousands of columns per row) before you would possibly be near any reasonable limit for request size. This doesn’t sound like it’s the real problem at all to me. Have you looked in your error logs – particularly in MySQL’s logs? What is the query, and what is the result when you ask for more than 512 rows?

Walter

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options