Description:
With the tables, data, and procedure in the how to repeat section I recieve the following error:
mysql> call wasp_get_messages_to_send ( 6, '2004-12-12');
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 041214 12:07:52 mysqld restarted
The procedure seems to only produce an error when I run it immediatly after updating the
weather_feeds table or the alerts table, to cause the join conditions to include or exclude the
record. If I run the select stmt outside of a proc with the variables hard coded it does not seem
to produce an error.
Idealy, I would like it to work after updating the tables and not crash the service, but I am fairly
confidant I can work around the issue. Perhaps I over designed the schema a bit. I will continue
to test and will try to follow up if I find anything interesting. My best guess is that it is something
to do with the bitwise comparison in the select stmt, but that is just a guess.
I am new to mysql, so please let me know if I am simply doing something wrong or if I have not
provided enough information. ( my email is gabeATdbaguyDOTcom).
I am still a bit shaky with the getting script execution working, but copying and pasting the
sections in the how to repeat should reproduce the problem.
Thanks!
How to repeat:
########## Create Tables ########
CREATE TABLE `alerts` (
`alert_id` int(11) NOT NULL auto_increment,
`member_id` int(11) NOT NULL,
`alert_address` varchar(100) NOT NULL,
`location_id` int(11) NOT NULL,
`run_at_hour` tinyint(4) NOT NULL,
`min_temp` tinyint(4) NOT NULL,
`max_temp` tinyint(4) NOT NULL,
`weather_code_sum` bigint(20) NOT NULL,
`max_precipitation_amt` smallint(6) NOT NULL,
`active` tinyint(1) NOT NULL default '1',
`alert_start` date NOT NULL,
`alert_end` date NOT NULL,
`auto_renew` tinyint(4) NOT NULL default '1',
`alert_name` varchar(255) NOT NULL default 'My Alert',
PRIMARY KEY (`alert_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
CREATE TABLE `alert_messages` (
`alert_id` int(11) NOT NULL,
`message_text` varchar(255) NOT NULL,
PRIMARY KEY (`alert_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `weather_feeds` (
`location_id` int(11) NOT NULL,
`pull_date` date NOT NULL,
`pull_hour` tinyint(4) NOT NULL,
`min_temp` smallint(6) NOT NULL,
`max_temp` smallint(6) NOT NULL,
`weather_code` int(11) NOT NULL,
`precipitation_pct` tinyint(4) NOT NULL,
PRIMARY KEY (`location_id`,`pull_date`,`pull_hour`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `locations` (
`location_id` int(11) NOT NULL auto_increment,
`zip_code` char(5) NOT NULL,
`long_value` decimal(8,4) NOT NULL,
`lat_value` decimal(8,4) NOT NULL,
PRIMARY KEY (`location_id`),
KEY `zip_code` (`zip_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `weather_types` (
`weather_desc` varchar(100) NOT NULL,
`weather_code` bigint(20) NOT NULL,
`id` int(50) NOT NULL,
PRIMARY KEY (`weather_desc`),
KEY `weather_code` (`weather_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
############# add data ##################
INSERT alerts
( member_id,
alert_address,
location_id,
run_at_hour,
min_temp,
max_temp,
weather_code_sum,
max_precipitation_amt,
active,
alert_start,
alert_end,
auto_renew,
alert_name )
VALUES
( 1,
'test@iai.net',
1,
6,
55,
100,
6,
20,
1,
'2004-12-01',
'2005-12-01',
1,
'my alert' );
INSERT alert_messages
( alert_id,
message_text )
VALUES
( 1,
'This is a test' );
INSERT locations
( location_id,
zip_code,
long_value,
lat_value )
VALUES
( 1,
'12345',
12.12,
-12.12 );
INSERT weather_feeds
( location_id,
pull_date,
pull_hour,
min_temp,
max_temp,
weather_code,
precipitation_pct )
VALUES
( 1,
'2004-12-12',
6,
60,
90,
4,
5 );
INSERT weather_types
( weather_desc,
weather_code,
id )
VALUES
('Clear', 2, 1);
INSERT weather_types
( weather_desc,
weather_code,
id )
VALUES
('Cloudy', 4, 2);
INSERT weather_types
( weather_desc,
weather_code,
id )
VALUES
('Drizzle', 8, 2);
############# create proc #################
DELIMITER $
DROP PROCEDURE IF EXISTS wasp_get_messages_to_send
$
CREATE PROCEDURE wasp_get_messages_to_send
(
IN i_intRunAtHour TINYINT,
# This param is just for testing
IN i_dateRunDate DATE # App should pass null
)
BEGIN
DECLARE m_dateRunDate DATE;
SET m_dateRunDate = ifnull( i_dateRunDate, curdate() );
SELECT a.alert_address,
a.alert_name,
l.zip_code,
wf.min_temp,
wf.max_temp,
wf.precipitation_pct,
wt.weather_desc
FROM alerts a
INNER JOIN weather_feeds wf
on a.location_id = wf.location_id
and wf.pull_date = m_dateRunDate
and wf.pull_hour = i_intRunAtHour
# this bitwise operation restricts by alert parameters
and ( a.weather_code_sum & wf.weather_code ) > 0
# these handle alert temp and precipitation restrictions
and wf.max_temp <= a.max_temp
and wf.min_temp >= a.min_temp
and wf.precipitation_pct <= a.max_precipitation_amt
INNER JOIN locations l
on a.location_id = l.location_id
INNER JOIN alert_messages am
on a.alert_id = am.alert_id
INNER JOIN weather_types wt
on wf.weather_code = wt.weather_code;
END
$
DELIMITER ;
############# To Generate Error ############
# this should work
call wasp_get_messages_to_send ( 6, '2004-12-12');
# this should work
update weather_feeds set weather_code = 8
# this should produce error
call wasp_get_messages_to_send ( 6, '2004-12-12');
Description: With the tables, data, and procedure in the how to repeat section I recieve the following error: mysql> call wasp_get_messages_to_send ( 6, '2004-12-12'); ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> 041214 12:07:52 mysqld restarted The procedure seems to only produce an error when I run it immediatly after updating the weather_feeds table or the alerts table, to cause the join conditions to include or exclude the record. If I run the select stmt outside of a proc with the variables hard coded it does not seem to produce an error. Idealy, I would like it to work after updating the tables and not crash the service, but I am fairly confidant I can work around the issue. Perhaps I over designed the schema a bit. I will continue to test and will try to follow up if I find anything interesting. My best guess is that it is something to do with the bitwise comparison in the select stmt, but that is just a guess. I am new to mysql, so please let me know if I am simply doing something wrong or if I have not provided enough information. ( my email is gabeATdbaguyDOTcom). I am still a bit shaky with the getting script execution working, but copying and pasting the sections in the how to repeat should reproduce the problem. Thanks! How to repeat: ########## Create Tables ######## CREATE TABLE `alerts` ( `alert_id` int(11) NOT NULL auto_increment, `member_id` int(11) NOT NULL, `alert_address` varchar(100) NOT NULL, `location_id` int(11) NOT NULL, `run_at_hour` tinyint(4) NOT NULL, `min_temp` tinyint(4) NOT NULL, `max_temp` tinyint(4) NOT NULL, `weather_code_sum` bigint(20) NOT NULL, `max_precipitation_amt` smallint(6) NOT NULL, `active` tinyint(1) NOT NULL default '1', `alert_start` date NOT NULL, `alert_end` date NOT NULL, `auto_renew` tinyint(4) NOT NULL default '1', `alert_name` varchar(255) NOT NULL default 'My Alert', PRIMARY KEY (`alert_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; CREATE TABLE `alert_messages` ( `alert_id` int(11) NOT NULL, `message_text` varchar(255) NOT NULL, PRIMARY KEY (`alert_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `weather_feeds` ( `location_id` int(11) NOT NULL, `pull_date` date NOT NULL, `pull_hour` tinyint(4) NOT NULL, `min_temp` smallint(6) NOT NULL, `max_temp` smallint(6) NOT NULL, `weather_code` int(11) NOT NULL, `precipitation_pct` tinyint(4) NOT NULL, PRIMARY KEY (`location_id`,`pull_date`,`pull_hour`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `locations` ( `location_id` int(11) NOT NULL auto_increment, `zip_code` char(5) NOT NULL, `long_value` decimal(8,4) NOT NULL, `lat_value` decimal(8,4) NOT NULL, PRIMARY KEY (`location_id`), KEY `zip_code` (`zip_code`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `weather_types` ( `weather_desc` varchar(100) NOT NULL, `weather_code` bigint(20) NOT NULL, `id` int(50) NOT NULL, PRIMARY KEY (`weather_desc`), KEY `weather_code` (`weather_code`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ############# add data ################## INSERT alerts ( member_id, alert_address, location_id, run_at_hour, min_temp, max_temp, weather_code_sum, max_precipitation_amt, active, alert_start, alert_end, auto_renew, alert_name ) VALUES ( 1, 'test@iai.net', 1, 6, 55, 100, 6, 20, 1, '2004-12-01', '2005-12-01', 1, 'my alert' ); INSERT alert_messages ( alert_id, message_text ) VALUES ( 1, 'This is a test' ); INSERT locations ( location_id, zip_code, long_value, lat_value ) VALUES ( 1, '12345', 12.12, -12.12 ); INSERT weather_feeds ( location_id, pull_date, pull_hour, min_temp, max_temp, weather_code, precipitation_pct ) VALUES ( 1, '2004-12-12', 6, 60, 90, 4, 5 ); INSERT weather_types ( weather_desc, weather_code, id ) VALUES ('Clear', 2, 1); INSERT weather_types ( weather_desc, weather_code, id ) VALUES ('Cloudy', 4, 2); INSERT weather_types ( weather_desc, weather_code, id ) VALUES ('Drizzle', 8, 2); ############# create proc ################# DELIMITER $ DROP PROCEDURE IF EXISTS wasp_get_messages_to_send $ CREATE PROCEDURE wasp_get_messages_to_send ( IN i_intRunAtHour TINYINT, # This param is just for testing IN i_dateRunDate DATE # App should pass null ) BEGIN DECLARE m_dateRunDate DATE; SET m_dateRunDate = ifnull( i_dateRunDate, curdate() ); SELECT a.alert_address, a.alert_name, l.zip_code, wf.min_temp, wf.max_temp, wf.precipitation_pct, wt.weather_desc FROM alerts a INNER JOIN weather_feeds wf on a.location_id = wf.location_id and wf.pull_date = m_dateRunDate and wf.pull_hour = i_intRunAtHour # this bitwise operation restricts by alert parameters and ( a.weather_code_sum & wf.weather_code ) > 0 # these handle alert temp and precipitation restrictions and wf.max_temp <= a.max_temp and wf.min_temp >= a.min_temp and wf.precipitation_pct <= a.max_precipitation_amt INNER JOIN locations l on a.location_id = l.location_id INNER JOIN alert_messages am on a.alert_id = am.alert_id INNER JOIN weather_types wt on wf.weather_code = wt.weather_code; END $ DELIMITER ; ############# To Generate Error ############ # this should work call wasp_get_messages_to_send ( 6, '2004-12-12'); # this should work update weather_feeds set weather_code = 8 # this should produce error call wasp_get_messages_to_send ( 6, '2004-12-12');