Tell me about yourself . . .
A little while ago MySQL introduced Stored Procedures thereby, in some peoples eyes, making it a ‘proper’ database (clearly a load of rubbish as it was totally fit for purpose anyway but that’s another argument).
For security reasons if you DROP a stored procedure in MySQL any grants to use it are also removed. This makes perfect sense as stored procedures can be set to run internally as a different user that the one that called it. If the GRANT wasn’t removed it would be possible to have a perfectly innocent procedure removed and, a while later a new one added that happened to have the same name (I know that this would require a deal of disorganisation but hey) that could drop the whole DB. The user who had access to the first procedure would then have access to the new one (bad times).
While it makes sense that the GRANT is removed when the stored procedure is removed it doesn’t really make sense that you can’t update stored procedures, you have to drop and recreate them, thereby losing all the grants applicable to them every time – damn annoying
The solution . . . Read more
How old are you???
Everyone knows how old they are, right?? Well OK, so sometimes I have to think about it a couple of times just to check (what with it nearly being my birthday) but still, it’s pretty easy.
However if you’re a database without a function to find out age it’s a tad more tricky.
This just came up as someone in the office was writing a report that included calculating the users ages . . . the solution they used was good enough for our purposes but was also one of those little things that bugs you because you know there’s a better way to do it.
The initial solution was as follows . . . btw, let’s also chuck the date of birth into a variable to simplify the queries 😉
SET @dateOfBirth=’1972-10-03′;
SELECT (DATEDIFF(NOW(), @dateOfBirth))/365;
