PHP Fatal error with PhpMyAdmin and APC

Posted by Matteo Mattei / 26th November 2011

If you are running PhpMyAdmin and APC can happens that you get some errors like these:
PHP Fatal error: Call to undefined function PMA_log_user() in /usr/share/webapps/phpMyAdmin/libraries/common.inc.php on line 914
PHP Fatal error: Call to undefined function PMA_select_language() in /usr/share/webapps/phpMyAdmin/libraries/auth/cookie.auth.lib.php on line 220
PHP Fatal error: Call to undefined function pma_generate_common_url() in /usr/share/webapps/phpMyAdmin/libraries/header_meta_style.inc.php on line 48
PHP Fatal error: Call to undefined function PMA_DBI_connect() in /srv/http/librolandia.it/test/phpmyadmin/libraries/common.inc.php on line 916
PHP Fatal error: Class 'PMA_Error_Handler' not found in /path/to/phpMyAdmin/libraries/common.inc.php on line 58
PHP Fatal error: Call to undefined function PMA_getenv() in /path/to/phpMyAdmin/libraries/common.inc.php on line 143


If this is the case, you need to make some little changes to disable apc in your phpmyadmin virtual host:

Alias /phpmyadmin "/usr/share/webapps/phpMyAdmin"
<Directory "/usr/share/webapps/phpMyAdmin">
      AllowOverride All
      Options FollowSymlinks
      Order allow,deny
      Allow from all
      php_admin_value open_basedir "/srv/:/tmp/:/usr/share/webapps/:/etc/webapps:/usr/share/pear/"
      php_admin_value apc.enabled 0
</Directory>

And also modify your apc filter in php.ini under the APC section:

[APC]
apc.filter="-/usr/share/webapps/phpMyAdmin/.*"

Now restart Apache and you phpMyAdmin should work regularly.

Export MySQL table in CSV format using PHP

Posted by Matteo Mattei / 30th July 2011

The following PHP code is intendend to be used to export a MySQL table in CSV format in order to be used with MS Excel.

$link = mysql_connect($mysql_host,$mysql_user,$mysql_pass) or die('Could not connect: '.mysql_error());
mysql_select_db($mysql_db,$link) or die('Could not select database: '.$mysql_db);

$query = "SELECT * FROM $tablename ORDER BY id";
$result = mysql_query($query) or die("Error executing query: ".mysql_error());
$row = mysql_fetch_assoc($result);
$line = "";
$comma = "";
foreach($row as $name => $value)
{
	$line .= $comma . '"' . str_replace('"', '""', $name) . '"';
	$comma = ";";
}
$line .= "\n";
$out = $line;

mysql_data_seek($result, 0);

while($row = mysql_fetch_assoc($result))
{
	$line = "";
	$comma = "";
	foreach($row as $value)
	{
		$line .= $comma . '"' . str_replace('"', '""', $value) . '"';
		$comma = ";";
	}
	$line .= "\n";
	$out.=$line;
}
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=listino.csv");
echo $out;
exit;

How to clone MySQL database schema in PHP

Posted by Matteo Mattei / 6th February 2011

For my client I needed to create a PHP script that can export a full MySQL database schema in another database. This script also need to keep and set constraints.

You only need to configure $DB_SRC_* and $DB_DST_* variables to fit your environment.

Here below you can find the code I created for this purpose:

<?php

/********************* START CONFIGURATION *********************/
$DB_SRC_HOST='localhost';
$DB_SRC_USER='root';
$DB_SRC_PASS='password';
$DB_SRC_NAME='database1';

$DB_DST_HOST='localhost';
$DB_DST_USER='root';
$DB_DST__PASS='password';
$DB_DST_NAME='database2';

/*********************** GRAB OLD SCHEMA ***********************/
$db1 = mysql_connect($DB_SRC_HOST,$DB_SRC_USER,$DB_SRC_PASS) or die(mysql_error());
mysql_select_db($DB_SRC_NAME, $db1) or die(mysql_error());

$result = mysql_query("SHOW TABLES;",$db1) or die(mysql_error());
$buf="set foreign_key_checks = 0;\n";
$constraints='';
while($row = mysql_fetch_array($result))
{
        $result2 = mysql_query("SHOW CREATE TABLE ".$row[0].";",$db1) or die(mysql_error());
        $res = mysql_fetch_array($result2);
        if(preg_match("/[ ]*CONSTRAINT[ ]+.*\n/",$res[1],$matches))
        {
                $res[1] = preg_replace("/,\n[ ]*CONSTRAINT[ ]+.*\n/","\n",$res[1]);
                $constraints.="ALTER TABLE ".$row[0]." ADD ".trim($matches[0]).";\n";
        }
        $buf.=$res[1].";\n";
}
$buf.=$constraints;
$buf.="set foreign_key_checks = 1";

/**************** CREATE NEW DB WITH OLD SCHEMA ****************/
$db2 = mysql_connect($DB_DST_HOST,$DB_DST_USER,$DB_DST_PASS) or die(mysql_error());
$sql = 'CREATE DATABASE '.$DB_DST_NAME;
if(!mysql_query($sql, $db2)) die(mysql_error());
mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
$queries = explode(';',$buf);
foreach($queries as $query)
        if(!mysql_query($query, $db2)) die(mysql_error());
?>

MySQL error 1153 in max_allowed_packet

Posted by Matteo Mattei / 22nd September 2008

Today I’ve got an anomalous error during a database import on mysql:

ERROR 1153 (08S01) at line 3854: Got a packet bigger than 'max_allowed_packet' bytes

To solve this is sufficient to edit the mysql configuration file (/etc/my.cnf on Linux) and fill a suitable big value for max_allowed_packet. In my case I’ve set 100M.

Restart mysqld daemon and now the import will gone fine!