[Snort-devel] DB schema v104

roman at ...49... roman at ...49...
Tue Sep 25 09:57:08 EDT 2001


There is a bug in the MySQL, Oracle MSSQL table schema.  The reg_tag field
in the reference table is too small to accomodate long URLs. This issue is
potentially causing problems with logging certain rules into the database,
and causing alert archiving under ACID to fail.

[CVS writters]: Please commit the patches to the four files below.

[MySQL DB users]: Note the upgrade instructions below.

CVS log:

+ enlarged the reference.reg_tag field to 100 characters. (Roman Danyliw
<roman at ...49...>)

cheers,
Roman

-----------------------------------------------------------------------
Upgrade instructions from DB v103 to v104:

- Log-in to the alert database and issue the following SQL commands
- If you use ACID remember to also run these commands on the archive
database

[MySQL v103 -> v104]

mysql> DELETE FROM schema WHERE vseq = 103;
mysql> INSERT INTO schema (vseq, ctime) VALUES (104, now());
mysql> ALTER TABLE reference MODIFY ref_tag VARCHAR(100) NOT NULL;

[PostgreSQL v103 -> v104]

psql> DELETE FROM schema WHERE vseq = 103;
psql> INSERT INTO schema (vseq, ctime) VALUES (104, now());


========================================================================
--- create_mysql.old    Tue Sep 25 12:51:17 2001
+++ create_mysql        Tue Sep 25 12:51:56 2001
@@ -21,7 +21,7 @@
 CREATE TABLE schema ( vseq        INT      UNSIGNED NOT NULL,
                       ctime       DATETIME NOT NULL,
                       PRIMARY KEY (vseq));
-INSERT INTO schema  (vseq, ctime) VALUES ('103', now());
+INSERT INTO schema  (vseq, ctime) VALUES ('104', now());

 CREATE TABLE event  ( sid        INT      UNSIGNED NOT NULL,
                       cid        INT      UNSIGNED NOT NULL,
@@ -48,7 +48,7 @@

 CREATE TABLE reference (  ref_id        INT         UNSIGNED NOT NULL
AUTO_INCREMENT,
                           ref_system_id INT         UNSIGNED NOT NULL,
-                          ref_tag       VARCHAR(20) NOT NULL,
+                          ref_tag       VARCHAR(100) NOT NULL,
                           PRIMARY KEY (ref_id));

 CREATE TABLE reference_system ( ref_system_id   INT         UNSIGNED NOT
NULL AUTO_INCREMENT,

=========================================================================
--- create_postgresql.old       Tue Sep 25 12:51:26 2001 
+++ create_postgresql   Tue Sep 25 12:52:07 2001
@@ -21,7 +21,7 @@
 CREATE TABLE schema ( vseq        INT4     NOT NULL,
                       ctime       DATETIME NOT NULL,
                       PRIMARY KEY (vseq));
-INSERT INTO schema  (vseq, ctime) VALUES ('103', now());
+INSERT INTO schema  (vseq, ctime) VALUES ('104', now());

 CREATE TABLE signature ( sig_id       SERIAL NOT NULL,
                          sig_name     TEXT NOT NULL,

=========================================================================


--- create_oracle.old   Tue Sep 25 12:51:34 2001
+++ create_oracle       Tue Sep 25 12:55:50 2001
@@ -34,7 +34,7 @@
 CREATE TABLE schema ( vseq        INT          NOT NULL,
                       ctime       VARCHAR2(24) NOT NULL,
                       PRIMARY KEY (vseq));
-INSERT INTO schema  (vseq, ctime) VALUES ('103', now());
+INSERT INTO schema  (vseq, ctime) VALUES ('104', now());
 
 CREATE TABLE event  ( sid        INT          NOT NULL,
                       cid        INT          NOT NULL,
@@ -70,7 +70,7 @@

 CREATE TABLE reference (  ref_id        INT          NOT NULL,
                           ref_system_id INT          NOT NULL,
-                          ref_tag       VARCHAR2(20) NOT NULL,
+                          ref_tag       VARCHAR2(100) NOT NULL,
                           PRIMARY KEY (ref_id));
 --
 --  auto-increment the reference.ref_id

==========================================================================

 
--- create_mssql.old    Tue Sep 25 13:07:12 2001
+++ create_mssql        Tue Sep 25 13:07:39 2001
@@ -24,7 +24,7 @@
 CREATE TABLE [schema] ( vseq        NUMERIC(10,0) NOT NULL,
                         ctime       DATETIME      NOT NULL,
                         PRIMARY KEY (vseq))
-INSERT INTO [schema]  (vseq, ctime) VALUES ('103', GETDATE())
+INSERT INTO [schema]  (vseq, ctime) VALUES ('104', GETDATE())

 CREATE TABLE event  ( sid         NUMERIC(10,0)   NOT NULL ,  
                       cid         NUMERIC(10,0)   NOT NULL ,  
@@ -51,7 +51,7 @@

 CREATE TABLE reference (  ref_id        NUMERIC(10,0) IDENTITY(1,1) NOT
NULL,
                           ref_system_id NUMERIC(10,0)               NOT
NULL,
-                          ref_tag       VARCHAR(20)                 NOT  
NULL,
+                          ref_tag       VARCHAR(100)                 NOT
NULL,
                           PRIMARY KEY (ref_id))
 
 CREATE TABLE reference_system ( ref_system_id   NUMERIC(10,0)
IDENTITY(1,1) NOT NULL,




---------------------------------------------
This message was sent using Voicenet WebMail.
      http://www.voicenet.com/webmail/






More information about the Snort-devel mailing list