Capturing the Server Console Log (2)

The previous article introduced the sa_server_messages() procedure and how it can be used to query the server console log messages produced by the SQL Anywhere database server.

This article talks about how to capture those messages in a permanent database table, on the fly, while the server is running.

Here's the table:

CREATE TABLE server_message ( 
   message_number   UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY CLUSTERED,
   msg_id           UNSIGNED BIGINT NOT NULL,
   msg_text         LONG VARCHAR,
   msg_time         TIMESTAMP,
   msg_severity     VARCHAR ( 255 ),
   msg_category     VARCHAR ( 255 ),
   msg_database     VARCHAR ( 255 ) );

Here is a SQL Anywhere event that automatically fires every second to do the work of copying all the new rows returned by sa_server_messages() into the server_message table:

CREATE EVENT capture_server_messages_every_second SCHEDULE 
   START TIME '00:00:00' 
   EVERY 1 SECONDS
HANDLER BEGIN

   DECLARE @next_msg_id          UNSIGNED BIGINT;
   DECLARE @sqlcode              INTEGER;
   DECLARE @sqlstate             VARCHAR ( 5 );
   DECLARE @errormsg             VARCHAR ( 32767 );

   ------------------------------------------------------------
   -- Get the starting point msg_id, which may be zero.

   SELECT next_msg_id.next_msg_id
     INTO @next_msg_id
     FROM next_msg_id;

   ------------------------------------------------------------
   -- Get all the new messages, if any.

   INSERT server_message (
          msg_id,
          msg_text,
          msg_time,
          msg_severity,
          msg_category,
          msg_database )
   SELECT sa_server_messages.msg_id,
          sa_server_messages.msg_text,
          sa_server_messages.msg_time,
          sa_server_messages.msg_severity,
          sa_server_messages.msg_category,
          sa_server_messages.msg_database
     FROM sa_server_messages ( first_msg = @next_msg_id )
    ORDER BY sa_server_messages.msg_id;

   ------------------------------------------------------------
   -- Update the starting point for next time.

   -- Note: The previous INSERT may not have inserted anything, 
   --       so @@IDENTITY cannot be used.

   SELECT TOP 1 
          server_message.msg_id + 1
     INTO @next_msg_id
     FROM server_message
    ORDER BY server_message.message_number DESC;

   UPDATE next_msg_id
      SET next_msg_id.next_msg_id = @next_msg_id
    WHERE next_msg_id.one_row_only = 1;

   COMMIT;

END;

Here's a query that displays the table:

SELECT message_number,
       msg_id,
       msg_time,
       msg_severity,
       msg_category,
       msg_database,
       msg_text
  FROM server_message
 ORDER BY message_number ASC;

Here's what the output looks like for messages captured just before and after the server was stopped and started:



Note that the server_message.message_number column starts at 1 and increases by 1 for all rows, whereas the msg_id value returned by sa_server_messages() starts over at 0 each time the server is started.

Astute readers will notice something's missing... here is the table that stores the starting point specified in "FROM sa_server_messages ( first_msg = @next_msg_id )" clause:

CREATE TABLE next_msg_id ( 
   one_row_only  INTEGER NOT NULL DEFAULT 1 CHECK ( one_row_only = 1 ) PRIMARY KEY,
   next_msg_id   UNSIGNED BIGINT NOT NULL DEFAULT 0 );

INSERT next_msg_id VALUES ( DEFAULT, DEFAULT );
COMMIT;

CREATE TRIGGER next_msg_id 
BEFORE DELETE ON next_msg_id
FOR EACH ROW
BEGIN
   ROLLBACK TRIGGER WITH RAISERROR 99999 'Do not attempt to delete from next_msg_id';
END;

CREATE EVENT database_start TYPE DatabaseStart 
HANDLER BEGIN

   TRUNCATE TABLE next_msg_id; -- does not fire BEFORE DELETE trigger
   INSERT next_msg_id VALUES ( DEFAULT, DEFAULT );
   COMMIT;

END;

The CHECK condition makes sure there is never more than one row in next_msg_id, the INSERT puts one row in that table, the trigger makes sure that row is never deleted, and the database_start event resets the next_msg_id table every time the server is restarted.