Help with php and MySQL coding

datona

I have a file that when executed is suppose to issue a file for download (mysql dump in csv format)
There are about 18,000 rows in the database and for some reason, it will stall out and NEVER issue the file.
What I see in MySQL is STATUS: writing to net
but the file never issued.

<?PHP
include 'config/db_connect.php';
$today = date("m-d-Y");
$resQuery = mysql_query("SELECT * FROM clients") or die(mysql_error());

$filename = "FD_client_database_$today.csv";
header("Content-Type: application/Excel");
header("Content-disposition: filename=$filename");

if (mysql_num_rows($resQuery) != 0) {
  $fields = mysql_num_fields($resQuery) or die(mysql_error());
  $i = 0;
  while ($i < $fields) {
    echo mysql_field_name($resQuery, $i).",";
    $i++;
  }
  echo "\n";

  while ($arrSelect = mysql_fetch_array($resQuery, MYSQL_ASSOC)) {
   foreach($arrSelect as $elem) {
    echo "$elem,";
   }
   echo "\n";
  }
}
?>

tomhath

What happens if you limit the query to only return a small number of rows? I'd get that working first.

Then if it doesn't work for the whole table you need to look at the server settings (timing out, or some other limit on the response).

datona

The thing that interests me though is that this will create the file and popup the download link in about 1-2 seconds on one linux box but on another, it will never popup.

tsinka

Hi,

maybe a performance problem or a firewall that messes up communication.

Create a test script to check what's going on:

<?PHP
  error_reporting(E_ALL);
?>
<html>
  <head>
    <title>DB Test</title>
  </head>
  <body>
<?PHP
include 'config/db_connect.php';
$today = date("m-d-Y");

$resQuery = mysql_query("SELECT COUNT(*) AS cnt FROM clients") or die(mysql_error());

$row = mysql_fetch_assoc($resQuery) or die("SQL error: ".mysql_error());

echo "number of rows in table clients: ".$row['cnt']."<br>\n";

$resQuery = mysql_query("SELECT * FROM clients LIMIT 10") or die(mysql_error());

if (mysql_num_rows($resQuery) > 0) {
  while ($row = mysql_fetch_assoc($resQuery)) {
    echo "<pre>\n";
    print_r($row);
    echo "</pre><br><br>\n";
  }
}
?>
  </body>
</html>

Thomas

datona

Thomas, thanks for the code.
Seems when I run it UP TO a limit set to 89, it works (and fast) but when I put a limit of 90, it just hangs there displaying ONLY:
number of rows in table clients: 15794

tsinka

Can you ssh/telnet to the MySQL server/web server ?

Thomas

datona

yes I can. I have root access

tsinka

Ok,

then do the following:

Check how full the disks are with "df -m"

Check how system resources and server load look like with "top".

Check if there are any network error with "ifconfig" (if web server and MySQL server are on different machines).

Check that with and without hanging query. Besides that, there are some limit settings but have first to check which ones could cause the problems. But I think that you should get an error message if you hit any limit. The script shouldn't just hang in that case.

Additionally, check the mysql error log and the web server error log for any error messages.

Which PHP and MySQL versions do you use ?

How is MySQL set up regarding the buffer and cache sizes (/etc/my.cnf).

EDIT: What happens, if you execute the query with a limit of e.g. 100 on the mysql console ?

Thomas

tsinka

One more:

Add
flush();
at the end of the while loop (but inside the while loop) so you can check if PHP hangs before entering the while loop or while looping through the result set.

Thomas

datona

Disk space is fine.
Server load is almost 0 so it is also fine
Script pulling mysql off same server. There is not problems.
Mysql error log shows nothing

I am running:

PHP: 4.3.2
MySQL: 3.23.58
OS: Ensim Redhat

/etc/my.cnf

[mysqld]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
datadir=/var/lib/mysql
log-slow-queries=/var/log/slow.log

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

[client]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock

Tried altering the file to:

<?PHP
error_reporting(E_ALL);
?>
<html>
<head>
<title>DB Test</title>
</head>
<body>
<?PHP
include 'config/db_connect.php';
$today = date("m-d-Y");
//$limit = "90";
$resQuery = mysql_query("SELECT COUNT(*) AS cnt FROM clients") or die(mysql_error());
flush();

$row = mysql_fetch_assoc($resQuery) or die("SQL error: ".mysql_error());
echo "number of rows in table clients: ".$row['cnt']." \n";
echo "Limiting search to: $limit \n";
flush();

$sql = "SELECT * FROM clients ORDER BY cust_id LIMIT $limit";
echo "Running: $sql ";
flush();
$resQuery = mysql_query("$sql") or die(mysql_error());
echo "Ran: $sql ";
flush();

if (mysql_num_rows($resQuery) > 0) {
while ($row = mysql_fetch_assoc($resQuery)) {
echo "<pre>\n";
print_r($row);
echo "</pre> \n";
flush();
}
}
?>
</body>
</html>

When I run the query: export.php?limit=89
I get:

number of rows in table clients: 15793
Limiting search to: 89
Running: SELECT FROM clients ORDER BY cust_id LIMIT 89
Ran: SELECT FROM clients ORDER BY cust_id LIMIT 89

Array
[Scolls out the information fine without delays]
.............
Which works fine BUT when I try running it with limit=90, I just get the page attempting to load with NOTHING posted to the screen.

tomhath

What happens if you put an OFFSET in the query as part of the LIMIT?

There might be something strange in row 90. Try offsetting by 80 and 100.

tsinka

Additionally,

execute the query

CHECK TABLE clients

MySQL will check the table for any errors. If that query doesn't return OK or "table is already up to date" then something is wrong with the table and you need to repair the table.

Shutdown the database and make a backup of the data directory before using check table and repair table.

Other tests are to try to mysqldump the clients table and to check if that works without any problems and to execute the query with the mysql console client.

Thomas