newbie: Stored proc silently fails

[7 reeds]

Hi,

I am using MySQL 5.1.x on a RedHat enterprise Linux distro.

I have used MySQL for some time now but have just started messing with stored procs and triggers. I have 300k records that describe various user events like: login, logout, system start/stop. I want to load these into a table that will record these events. I have another table that should be a "session log" of the user events. I will include the exact table, trigger, stored proc info below.

Specifically, LOGIN of user X on machine M should create an event record with:

auto_increment row id, user, machine, event_datetime

I have a BEFORE INSERT trigger that does some house keeping on the to be inserted data [this is working]. I have an AFTER INSERT trigger that i hope will manage the session log.

A session log record looks like:

auto_increment row id, start_id, end_id

where start_id and end_id are foreign keys into the eventLog.rowID. start_id should almost always point to a LOGIN event. end_id could be NULL [currently active] or a pointer to *any* other event record. There is a special case in which the start and end records can be the same.

So here are my questions:

1) I have seen that stored procs may not include transactions. Does this mean that all trigger activities are atomic?

2) I just tried loading the eventLog table and the inserts worked but the stored procedure either didn't work or silently failed. How does one debug these things?

Following is the dump of the database structures.

DELIMITER $$
--
-- Procedures
--
DROP PROCEDURE IF EXISTS `manage_sessions`$$
CREATE PROCEDURE `manage_sessions`(IN `id` INT(11), IN `event` VARCHAR(8), IN `machine` VARCHAR(64), IN `line` VARCHAR(64))
BEGIN
    DECLARE updateCount int(11);
    SET @updateCount = 0;
    
    UPDATE
        sessions s,
        eventLog e
    SET
        s.end_id = @id
    WHERE
        e.id = s.start_id
	AND s.end_id is NULL
	AND UPPER(e.machine) = UPPER(@machine)
	AND UPPER(e.line) = UPPER(@line);
            
    SET @updateCount = (SELECT ROW_COUNT());
    
    IF UPPER(@event) = 'LOGIN' THEN
	    --
	    -- Start new Session
	    --
	    INSERT INTO sessions
		    (start_id, end_id)
	    VALUES(@id, NULL);
    ELSEIF UPPER(@event) = 'LOGOUT'
	       AND @updateCount = 0 THEN
	--
	-- Insert zero length session to mark that the
	-- logout-user was present
	--
	INSERT INTO sessions
	(start_id, end_id)
	VALUES(@id, @id);
    END IF;

END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `eventLog`
--

DROP TABLE IF EXISTS `eventLog`;
CREATE TABLE IF NOT EXISTS `eventLog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `machine` varchar(64) COLLATE utf8_bin NOT NULL,
  `date` datetime NOT NULL,
  `event` varchar(8) COLLATE utf8_bin NOT NULL,
  `netid` varchar(16) COLLATE utf8_bin NOT NULL,
  `domain` varchar(32) COLLATE utf8_bin NOT NULL,
  `line` varchar(64) COLLATE utf8_bin NOT NULL,
  `source` varchar(8) COLLATE utf8_bin NOT NULL,
  `dateInserted` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_row` (`netid`,`domain`,`machine`,`line`,`event`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- Triggers `eventLog`
--
DROP TRIGGER IF EXISTS `manage_sessions`;
DELIMITER //
CREATE TRIGGER `manage_sessions` AFTER INSERT ON `eventLog`
 FOR EACH ROW BEGIN
    CALL manage_sessions(NEW.id, NEW.event,
                         NEW.machine, NEW.line);
END
//
DELIMITER ;

DROP TRIGGER IF EXISTS `set_date`;
DELIMITER //
CREATE TRIGGER `set_date` BEFORE INSERT ON `eventLog`
 FOR EACH ROW BEGIN
    SET NEW.dateInserted = NOW(), 
        NEW.date = IF(YEAR(NEW.date) = 0, 
                      NOW(), 
                      NEW.date);
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `sessions`
--

DROP TABLE IF EXISTS `sessions`;
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `start_id` int(11) unsigned NOT NULL,
  `end_id` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `start_id_UNIQUE` (`start_id`),
  KEY `end_id` (`end_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `sessions`
--
ALTER TABLE `sessions`
  ADD CONSTRAINT `end_id` FOREIGN KEY (`end_id`) REFERENCES `eventLog` (`id`),
  ADD CONSTRAINT `start_id` FOREIGN KEY (`start_id`) REFERENCES `eventLog` (`id`);