I had a main issue when I am trying to deploy a PHP, MySQL project by using CPanel. This might be occurred on any hosting provider. The issue is I have stored procedures on MySQL and when deploying it says I cannot deploy procedures which contains ‘DEFINER’ in it.
So what I did is remove all the ‘DEFINER’ parts on procedures and executed the MySQL dump file. It went alright. But when we connect to PHPMyAdmin via CPanel the logged in user is same as CPanel user. On MySQL if there is no definer on a procedure it generates the currently logged in user.
Now I have procedures with a CPanel user but my PHP code is connected to that DB by using a MySQL defined user. When I am trying to browse the site it throws an error saying Definer is different. Then I was in the middle of a dead lock.
Then what I thought was to execute the create procedures by using PHP. Then definer will be same as PHP connected user to MySQL. So I had to write a PHP code to read the MySQL dump file and build the DB for me.
It was a success so far. My delimiter used id ‘$$’ and if your one is different please change that on code. And also I had to remove comment lines and ‘DELIMITER’ changing lines (this line gave me an error).
Please use this for good and if there was any modification you have done to improve this, I hope you will share with us.
And also if there is any part that you cannot understand, have any issues with please come to me.
$mysql_host = 'localhost';
$mysql_username = 'root';
$mysql_password = '';
$mysql_database = 'test';
mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting ' . mysql_error());
mysql_select_db($mysql_database) or die('Error MySQL database ' . mysql_error());
$delimiter = ';';
$currentQuery = '';
$lineCollection = file($filename);
foreach ($lineCollection as $line)
//in this my current delimiter was ; and then for procs it was $$
if(strpos($line,'DELIMITER') !== false)
$delimiter = '$$';
//removed comments and DELIMITER changing lines
if (substr($line, 0, 2) == '--' || $line == '' || substr($line, 0, 2) == '/*' || strrpos($line,'DELIMITER') )
//current query generated over line by line
$currentQuery .= $line;
//if delimiter was found then time to execute query
if (substr(trim($line), strlen(trim($line))-strlen($delimiter), strlen($delimiter)) == $delimiter)
if($delimiter != ';')
$currentQuery = substr($currentQuery, 0, strlen($currentQuery)-(strlen($delimiter)+1));
//if definer is there and you do not have super powers it gives an error
if(strpos($currentQuery,'DEFINER') !== false)
$firstpart = substr($currentQuery,0,strpos($currentQuery,'DEFINER'));
$secondpart = substr($currentQuery,strpos($currentQuery,'DEFINER'));
$thirdpart = substr($secondpart,strpos($secondpart,' '));
$currentQuery = $firstpart.$thirdpart;
mysql_query($currentQuery) or print('Error on - ' . $currentQuery . ' : ' . mysql_error() . '
$currentQuery = '';