Archive

Posts Tagged ‘SQL’

who is “Definer” in terms of MySQL

May 21st, 2009
No comments

Today I was playing with our database settings and trying to grant/ cut permissions of users from the development DB and production DB, changing passwords and so on.
Using our primitive hosting’s control panel I wanted to remove the main production DB user from Development DB, which I ended up loosing the user completely.
I created a new user/pass and in few seconds I changed the configurations. Evertyhing looked fine till I got a phone call from  by boss and I found that something is missing and not working properly.

The problem was on one of our scripts and the error was :

The user specified as a definer : (’no_such_user’@'no_such_host’) is invalid or unregistered

And thank to our primitive control panel, I was not able to recreate the user as it contains “_” [underscore] in the username.

All the settings were fine and I was wondering what’s causing the problem. At first rush I thought that it could be a cache, as I had requested recently to have xcache on our server.

But it wasn’t the case. The error was much more stupid than even one can imagine. That specific script uses a stored procedure to insert / fetch data to/from MySQL. The user who had created the sp was the one who was deleted. And that was the problem. The term “Definer” in terms of MySQL is the one who creates the stored procedure and for the stored procedure to be executed that user must exists.

A quick solution for that was dropping the old stored procedure and re-create it while logged in to MySQL with the new user.

Voila, it solved our issue. Not the best solution but at least I figured out what “Definer” means and it must exists when a sp is being called.

MySQL, SQL, Server , ,

MySQL - group results by second/minute/hour

April 27th, 2009
2 comments

This is a simple MySQL tip, but it helped me a lot.

We have a traffic table which monitors every single coming traffic to our applciation. I wanted to get the real values for how many requests I we have per Second / Minute / Hour and also what times of the the day are high traffic and what times are low traffic, to do some load-balancing on the application and server.

The query is very simple, just COUNT the number and do a GROUP BY and extract the minute/hour/…

Like :

SELECT tr_date_time,count(*) AS NUM FROM `traffic` WHERE DATE(`tr_date_time`) = ‘2009-04-25′ GROUP BY EXTRACT(HOUR_MINUTE FROM tr_date_time)

The script above extracts the number of hits per minute during the day.
Don’t forget that if you want to have the result by minutes, you have to add the hour as well to make it unique.

p.s: I hope your Date/Time/DateTime field is human friendly not Unix_timestamp, which will add some more extra calcuations on the result set.

General, PHP, SQL , ,