PHP remote MySQL data fetching size limit issue

Question

I'm trying to remote connect to MySQL database of a server and pull a row. But I'm having this issue :

I am able to pull some specific columns data but not able to pull the whole row.

Example :

This works :

-- Query 1
SELECT  id, name, link, cat, image, title, features
FROM products 
WHERE data_ready=1 
ORDER BY id ASC
LIMIT 0,1

But this doesn't work :

-- Query 2
SELECT *
FROM products
WHERE data_ready=1
ORDER BY id ASC
LIMIT 0,1  

Nor does this

-- Query 3
SELECT  id, name, link, cat, image, title, features, description
FROM products
WHERE data_ready=1
ORDER BY id ASC
LIMIT 0,1

Please note that in query number three I'm trying to pull just one more column description which was not in query 1. The LENGTH of the description field is 1314.

Something is limiting me to the amount of data that can be fetched but how do I find what and how to fix it?

By the way, SELECT @@max_allowed_packet yields 1048576.

---

EDIT

By 'dont work' means the query is not returning anything but page keeps loading and eventually timeout.

Yes, I have tested the queries directly in Mysql and it works fine,

Actually I have like a central database server and many client servers who are all fetching the data from the central server. I am able fetch whole data to all the client servers except one ,where I am facing this issue. So I believe its something to do with some kind of php.ini settings. I compared the phpinfo() settings from all the servers but not able to figure out what might be causing this limitation.

Result of SHOW TABLE STATUS like 'products';

Space usage
Data    438.8   MiB
Index   7.5 MiB
Total   446.3   MiB

Row Statistics
Format  Compact
Collation   latin1_swedish_ci
Next autoindex  2,015,640
Creation    May 19, 2014 at 01:46 PM

--- More INFO --- my Php code is simple :

<?php

$conn=@mysql_pconnect("xx.xx.xx.xx","DBNAME","DBPASS")or die("ERROR CONNECTING TO MYSQL SERVER");
@mysql_select_db("DBUSERNAME",$conn)or die("ERROR CONNECTING TO DATABASE");

$sql="select  id,pid,name,link,cat,image,title,features,price,description from products order by id asc limit 0,1";
$execute=mysql_query($sql,$conn);
$row=mysql_fetch_assoc($execute);
print_r($row);

?>

Also Below two queries return data :

Query 4 - select  id,pid,name,link,cat,image,title,features from products order by id asc limit 0,1
Query 5 - select  id,pid,name,link,cat,image,title,description from products order by id asc limit 0,1

But If i try to fetch both Features and Description Together then the script never finished and eventually timeout. i.e.

Query 6 - select  id,pid,name,link,cat,image,title,features ,description from products order by id asc limit 0,1

The same codes are working in other two servers i have. but i am not able to figure out what is wrong with this server configuration :/

Answer 1

I think you should escape the fields you're querying, with backquotes ` :

-- Query 3
SELECT  `id`, `name`, `link`, `cat`, `image`, `title`, `features`, `description`
FROM products
WHERE data_ready=1
ORDER BY id ASC
LIMIT 0,1

Because keywords like description, title or match may be wrongly interpreted by MySQL.

Answer 2

Some troubleshooting questions

It is possible, but not certain, that you are running into trouble with a MySQL server setting called max_allowed_packet.

Try this to find out the size of your description field for the particular row you're failing to fetch.

SELECT id, name, link, cat, image, title, features, 
       LENGTH(description)
  FROM products
 WHERE data_ready=1
 ORDER BY id ASC
 LIMIT 0,1

If the length of description is surprisingly large, this might be your problem. It's 1314 which is not a problem

Give this command:

SELECT @@max_allowed_packet : **it's 1 048 576 which is plenty** 

Is it too small, or nearly too small, for your description? See this:

http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

Next step: Look at the specific php code that's driving MySQL

Why are you using mysql_pconnect? Do you have a good reason for that? If not, switch to mysql_connect. At any rate, get this code working with mysql_connect and then switch back.

Why are you giving the database name in pconnect, and the user name in select_db? Are you sure those are right?

The code as posted in the question needs to be cleaned up, to check correctly for errors and keep track of where things might be getting stuck. You also need to free up the result set when you're done. Try code like this. Notice that I have not debugged it, that's up to you.

 $execute=mysql_query($sql,$conn);
 if (!$execute) {
     die "Query ($sql) failed: " . mysql_error();
 }
 while ($row=mysql_fetch_assoc($execute)) {
    /* loop through a result set even if you don't think you need to. */
    print_r($row);
 }
 mysql_free_result($execute);