Skip to main content

Definer issue on MySQL

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());

RestoreDumpFile('mySQLdumpfile.sql');

function RestoreDumpFile($filename)
{
$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 = '$$';
continue;
}

//removed comments and DELIMITER changing lines
if (substr($line, 0, 2) == '--' || $line == '' || substr($line, 0, 2) == '/*' || strrpos($line,'DELIMITER') )
{
continue;
}

//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));
}

//remove definer
//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;
}
echo $currentQuery.'
----------------------------------------------------------------------------
';

mysql_query($currentQuery) or print('Error on - ' . $currentQuery . ' : ' . mysql_error() . '
');
$currentQuery = '';
}
}
}

Comments

Post a Comment

Popular posts from this blog

Google API v3 with PHP using Blogger service

It was really hard for me to understand how the Google APIs are working at the first point and took few days for me to figure out. But after a successful working prototype it seems very easy. And also when I am searching for a simple example I was unable to find a good one that I can understand. So let me list down step by step what I have done with URLs and as simple as I can. Create a Google app location -  https://code.google.com/apis/console Switch on the "Blogger API v3" Get the latest APIs client library for PHP location -  https://code.google.com/p/google-api-php-client/downloads/list Upload the files to your host location on on localhost Extract the files to folder  named "GoogleClientApi" Create your php file outside of the folder  Copy paste following code into the file and do the changes as needed  By changing the scope and the service object you can access all the services that is given by Google APIs through the PHP API library se

My two cents on new year resolution

What is the plan for the new year ? - need to think on what are we trying achieve during next year 2018 - basically the life goals - may be personal or professional - and also it should be realistic (not something like going to Mars ;)) Why we need a plan for the new year ? - basically a goal without a plan is a DREAM - And also should be able to measure (what you cannot measure, you cannot manage) How to prepare a new Year resolution/plan ? - Leave some buffer time - Make changes during the year (life is changing/evolving) - Plan is only for you (do not share it) - When a milestone is achieved, celebrate - Try to stick to the plan - otherwise no point of planing

Assets and Liabilities as Rich Dad, Poor Dad explains

I was reading "The rich dad poor dad by Robert Kiyosaki" here is a one point that he mentions on that. Basically Asset as he says is little bit different than on books. If something puts money in your pocket it is a asset. And Liabilities are the ones that takes money out of your pocket. OK for example a house or a car may seems like an Asset but it takes money out of you pocket to maintain them. But if you rent them or make them to make money at the end of the day you can convert it to a asset. Basically that what rich people do. They buy assets. Middle class buy liabilities (thinking those are assets) and stuff (a lot of them that not used or that not needed). Lower class buy to consume (basic needs like foods).