backscript slow in php5. mysql_escape_real_string problem?

leatherback

Hi all,

In one of my scripts I have a backup section. I upgraded to php 5, and now the backup script times out. I have narrowed it down to the mysql_real_escape_string function. If I leave that line out, and instead spit out the raw data, the script is done in a flash. Can anybody advice how to solve this (Or.. if you have a better way of doing this -without shell scripts-..)? The function I use is as follows:


function get_database($server, $user, $pass, $db)
  {
  mysql_connect($server, $user, $pass);
  mysql_select_db($db);
  $tables = mysql_query("show tables");
  $delete = "";

  while ($td = mysql_fetch_array($tables))
    {
    $table = $td[0];
    $delete .= "DROP TABLE IF EXISTS $table;
    ";
    $r = mysql_query("Show CREATE TABLE `$table`");
    if ($r)
      {
      $insert_sql = "";
      $d = mysql_fetch_array($r);
      $d[1] .= ";";
      $SQL[] = str_replace("\n", "", $d[1]);
      $table_query = mysql_query("SELECT * FROM `$table`");
      $num_fields = mysql_num_fields($table_query);
      while ($fetch_row = mysql_fetch_array($table_query))
        {
        $insert_sql .= "INSERT INTO $table VALUES(";
        for ($n=1;$n<=$num_fields;$n++)
          {
          $m = $n - 1;
          $insert_sql .= "'".mysql_real_escape_string($fetch_row[$m])."', ";
          }  

        $insert_sql = substr($insert_sql,0,-2);
        $insert_sql .= ");\n";
        }

  if ($insert_sql!= "")
    {
    $SQL[] = $insert_sql;
    }
  }
}
  $out = $delete.implode("\r", $SQL);
  return $out;
  }

MarkR

Don't use such a script to backup your database. Use the vendor recommended method instead (mysqldump).

Mark

leatherback

Hi Mark,

Thanks for your comment. I agree. I would prefer to use a mysql dump normally. However, to do that you require shell-scripts which get complex if you do not know where the script is running. I do not want to write code which is dependent on the server I am running code on.

Unless you know of a way to run a mysqldump from php directly?

MarkR

You should not rely on a PHP script to backup the database. The web server could time the script out and kill it before the backup is complete.

I consider backing up a database a systems management issue not a development issue - therefore, it's the sysadmin's job, not the developer's (or the application's).

Not every database will be backed up in the same way. MySQL has several backup methods, the simplest of which (mysqldump) may be used most of the time but not everywhere.

Your systems admin should do it. It is an activity which will never be independent of the server, because the exact backup process will be determined by local policy, application usage, etc.

Mark

leatherback

thanks mark, you opinion is noted, and as I said in my first reply: I'd also prefer to use another method. However that is not always realistic. -

Back to the question at hand. Why does does the script time out (30sec), when there is only 15-20 pages in the database?

MarkR

I have no idea. Does the script work on the CLI in your development environment? Are all warnings and errors being logged, and do any occur?

Mark

leatherback

Hey Mark,

The odd thing is.. As I said: All I need to do is comment out the mysql_real_escape_string, and place an echo statement in stead, and it runs just fine. I used to run it in php 4.3 just fine too.

Errors are set to reporting: on, level E_ALL, and non occur.

Roger_Ramjet

Why do you feel you need it in the first place? The data is being copied from one db to another without transformation or update.

Roger_Ramjet

If you want an independent backup then get the mysql admin tools and create a backup project. They install on your local system and you run them from there - needs your db to be remotely accessible which may not be the case with your host.