Archive for the “mysql” Category


Aug 07 2008

MySQL Stored Procedure Not Working as Expected Puzzler

Posted by: kent in mysql, tags: , ,

I recently spent 3 hours pulling my hair out trying to fix a MySQL stored procedure that wasn’t working as expected. I’ve boiled the ultimate problem down to a simple example below. Can you spot the problem?

# First, let's create some sample data
CREATE TABLE TrendicsTest (
DateTime DATETIME NOT NULL,
Value INT NOT NULL,
UNIQUE KEY DateTime (DateTime)
);
INSERT INTO TrendicsTest VALUES ('2008-01-01 01:00', 1);
INSERT INTO TrendicsTest VALUES ('2008-01-01 02:00', 2);

# Next, lets' define a stored procedure to query the sample data
DELIMITER |
DROP PROCEDURE IF EXISTS summarizeTrendicsTest|
CREATE PROCEDURE summarizeTrendicsTest(dateTime DATETIME)
BEGIN
SELECT @endDateTime := DATE_ADD(dateTime, INTERVAL 1 HOUR);
SELECT * FROM TrendicsTest WHERE DateTime=@endDateTime;
END;
|
DELIMITER ;

# Now, let's call the stored procedure and expect
# to get back the first row of data
call summarizeTrendicsTest('2008-01-01 00:00');

What the hell? The first SELECT that sets @endDateTime variable echoes a result but the main SELECT returns an empty set. Just to check our query, let's make sure the query runs ok...

# Add one hour to the time just like the stored procedure
SELECT * FROM TrendicsTest WHERE DateTime='2008-01-01 01:00';

Yes, running the query returns data so why doesn't the stored procedure return any data that appears to be running the same SELECT? Evidently, using "dateTime" for the stored procedure param and using "DateTime" for the field in the WHERE clause confuses MySQL. The stored procedure can be fixed by renaming the param name to something else...

DELIMITER |
DROP PROCEDURE IF EXISTS summarizeTrendicsTest|
CREATE PROCEDURE summarizeTrendicsTest(dateTimeXyz DATETIME)
BEGIN
SELECT @endDateTime := DATE_ADD(dateTimeXyz, INTERVAL 1 HOUR);
SELECT * FROM TrendicsTest WHERE DateTime=@endDateTime;
END;
|
DELIMITER ;

# Now, let's call the stored procedure and expect
# to get back the second row of data
call summarizeTrendicsTest('2008-01-01 00:00');

Bah, in a complex stored procedure that is really not so easy to figure out. So, be careful with your choice of stored procedure parameter names — avoid using a parameter name that is the same as a field name in your tables. Seems like the MySQL stored procedure compiler should at least warn you when this happens if not throw an error.

Comments 1 Comment »

Jul 20 2008

MySQL Prompt Bar Charts

Posted by: kent in development, mysql, tags:

You can generate simple bar charts directly from a MySQL prompt that look like this...

+-------+-----------------------+-----------------------------------------------+
| Month | Uptime                | Latency                                       |
+-------+-----------------------+-----------------------------------------------+
| Jan   | %%%%%%%%%%%%%%%%%%%%% | ####################                          |
| Feb   | %%%%%%%%%%%%%%%%%%%   | ##############################                |
| Mar   | %%%%%%%%%%%%%%%%%%%%  | #########################################     |
| Apr   | %%%%%%%%%%%%%%%%%%%%% | #####################                         |
| May   | %%%%%%%%%%%%%%%%%%%%% | ###############                               |
| Jun   | %%%%%%%%%%%%%%%%%%    | ############################################# |
| Jul   | %%%%%%%%%%%%%%%%%%%%% | ################################              |
| Aug   | %%%%%%%%%%%%%%%%%%%%% | ##############################                |
| Sep   | %%%%%%%%%%%%%%%%%%%%% | ###############                               |
| Oct   | %%%%%%%%%%%%%%%%%%    | ####################################          |
| Nov   | %%%%%%%%%%%%%%%%%%%%  | ##########################                    |
| Dec   | %%%%%%%%%%%%%%%%%%%%% | #################                             |
+-------+-----------------------+-----------------------------------------------+

To generate this type of output, you can execute the following commands to generate the sample data and to execute the SELECT that generates the bar chart itself…

CREATE TABLE TrendicsWebsiteCheckResults (
	Month VARCHAR(3) NOT NULL,
	Uptime FLOAT NOT NULL,
	Latency FLOAT NOT NULL
);
INSERT INTO TrendicsWebsiteCheckResults VALUES ('Jan', 100.0, 97.5),
('Feb', 92.3, 145.7),('Mar', 95.6, 201.1), ('Apr', 100.0, 101.3),
('May', 100.0, 72.0), ('Jun', 87.5, 221.0), ('Jul', 98.6, 152.7),
('Aug', 100.0, 144.8), ('Sep', 100.0, 68.8), ('Oct', 87.5, 177.2),
('Nov', 95.6, 123.4), ('Dec', 100.0, 77.9);

SELECT Month,
REPEAT("%", (Uptime-0.0)*0.2+1) Uptime,
REPEAT("#", (Latency-0.0)*0.2+1) Latency
FROM TrendicsWebsiteCheckResults;

Within the REPEAT() function, use whatever character you like for your bar chart and adjust the constants to scale each bar chart appropriately. This is a super simple way to quickly chart out data from a MySQL prompt.

Comments 2 Comments »