Error creating Tables

[semar]

Hello,

 

I have installed mysql 4.0.15-nt and have tried to use a script to automatically configure the required tables. The error reported tells me the syntax is incorrect error 1064 at the 4 line \'INSERT INTO.....\' . I am new to mysql but i believe the script was written for a previous version of mysql.

 

I would appreciate any help on why the script is falling over.

 

semar

 

CREATE TABLE schema ( vseq INT UNSIGNED NOT NULL,

ctime DATETIME NOT NULL,

PRIMARY KEY (vseq));

INSERT INTO schema (vseq, ctime) VALUES (\'106\', now());

 

CREATE TABLE event ( sid INT UNSIGNED NOT NULL,

cid INT UNSIGNED NOT NULL,

signature INT UNSIGNED NOT NULL,

timestamp DATETIME NOT NULL,

PRIMARY KEY (sid,cid),

INDEX sig (signature),

INDEX time (timestamp));

 

CREATE TABLE signature ( sig_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

sig_name VARCHAR(255) NOT NULL,

sig_class_id INT UNSIGNED NOT NULL,

sig_priority INT UNSIGNED,

sig_rev INT UNSIGNED,

sig_sid INT UNSIGNED,

PRIMARY KEY (sig_id),

INDEX sign_idx (sig_name(20)),

INDEX sig_class_id_idx (sig_class_id));

 

CREATE TABLE sig_reference (sig_id INT UNSIGNED NOT NULL,

ref_seq INT UNSIGNED NOT NULL,

ref_id INT UNSIGNED NOT NULL,

PRIMARY KEY(sig_id, ref_seq));

 

CREATE TABLE reference ( ref_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

ref_system_id INT UNSIGNED NOT NULL,

ref_tag TEXT NOT NULL,

PRIMARY KEY (ref_id));

 

CREATE TABLE reference_system ( ref_system_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

ref_system_name VARCHAR(20),

PRIMARY KEY (ref_system_id));

 

CREATE TABLE sig_class ( sig_class_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

sig_class_name VARCHAR(60) NOT NULL,

PRIMARY KEY (sig_class_id),

INDEX (sig_class_id),

INDEX (sig_class_name));

 

# store info about the sensor supplying data

CREATE TABLE sensor ( sid INT UNSIGNED NOT NULL AUTO_INCREMENT,

hostname TEXT,

interface TEXT,

filter TEXT,

detail TINYINT,

encoding TINYINT,

last_cid INT UNSIGNED NOT NULL,

PRIMARY KEY (sid));

 

# All of the fields of an ip header

CREATE TABLE iphdr ( sid INT UNSIGNED NOT NULL,

cid INT UNSIGNED NOT NULL,

ip_src INT UNSIGNED NOT NULL,

ip_dst INT UNSIGNED NOT NULL,

ip_ver TINYINT UNSIGNED,

ip_hlen TINYINT UNSIGNED,

ip_tos TINYINT UNSIGNED,

ip_len SMALLINT UNSIGNED,

ip_id SMALLINT UNSIGNED,

ip_flags TINYINT UNSIGNED,

ip_off SMALLINT UNSIGNED,

ip_ttl TINYINT UNSIGNED,

ip_proto TINYINT UNSIGNED NOT NULL,

ip_csum SMALLINT UNSIGNED,

PRIMARY KEY (sid,cid),

INDEX ip_src (ip_src),

INDEX ip_dst (ip_dst));

 

# All of the fields of a tcp header

CREATE TABLE tcphdr( sid INT UNSIGNED NOT NULL,

cid INT UNSIGNED NOT NULL,

tcp_sport SMALLINT UNSIGNED NOT NULL,

tcp_dport SMALLINT UNSIGNED NOT NULL,

tcp_seq INT UNSIGNED,

tcp_ack INT UNSIGNED,

tcp_off TINYINT UNSIGNED,

tcp_res TINYINT UNSIGNED,

tcp_flags TINYINT UNSIGNED NOT NULL,

tcp_win SMALLINT UNSIGNED,

tcp_csum SMALLINT UNSIGNED,

tcp_urp SMALLINT UNSIGNED,

PRIMARY KEY (sid,cid),

INDEX tcp_sport (tcp_sport),

INDEX tcp_dport (tcp_dport),

INDEX tcp_flags (tcp_flags));

 

# All of the fields of a udp header

CREATE TABLE udphdr( sid INT UNSIGNED NOT NULL,

cid INT UNSIGNED NOT NULL,

udp_sport SMALLINT UNSIGNED NOT NULL,

udp_dport SMALLINT UNSIGNED NOT NULL,

udp_len SMALLINT UNSIGNED,

udp_csum SMALLINT UNSIGNED,

PRIMARY KEY (sid,cid),

INDEX udp_sport (udp_sport),

INDEX udp_dport (udp_dport));

 

# All of the fields of an icmp header

CREATE TABLE icmphdr( sid INT UNSIGNED NOT NULL,

cid INT UNSIGNED NOT NULL,

icmp_type TINYINT UNSIGNED NOT NULL,

icmp_code TINYINT UNSIGNED NOT NULL,

icmp_csum SMALLINT UNSIGNED,

icmp_id SMALLINT UNSIGNED,

icmp_seq SMALLINT UNSIGNED,

PRIMARY KEY (sid,cid),

INDEX icmp_type (icmp_type));

 

# Protocol options

CREATE TABLE opt ( sid INT UNSIGNED NOT NULL,

cid INT UNSIGNED NOT NULL,

optid INT UNSIGNED NOT NULL,

opt_proto TINYINT UNSIGNED NOT NULL,

opt_code TINYINT UNSIGNED NOT NULL,

opt_len SMALLINT,

opt_data TEXT,

PRIMARY KEY (sid,cid,optid));

 

# Packet payload

CREATE TABLE data ( sid INT UNSIGNED NOT NULL,

cid INT UNSIGNED NOT NULL,

data_payload TEXT,

PRIMARY KEY (sid,cid));

 

# encoding is a lookup table for storing encoding types

CREATE TABLE encoding(encoding_type TINYINT UNSIGNED NOT NULL,

encoding_text TEXT NOT NULL,

PRIMARY KEY (encoding_type));

INSERT INTO encoding (encoding_type, encoding_text) VALUES (0, \'hex\');

INSERT INTO encoding (encoding_type, encoding_text) VALUES (1, \'base64\');

INSERT INTO encoding (encoding_type, encoding_text) VALUES (2, \'ascii\');

 

# detail is a lookup table for storing different detail levels

CREATE TABLE detail (detail_type TINYINT UNSIGNED NOT NULL,

detail_text TEXT NOT NULL,

PRIMARY KEY (detail_type));

INSERT INTO detail (detail_type, detail_text) VALUES (0, \'fast\');

INSERT INTO detail (detail_type, detail_text) VALUES (1, \'full\');

 

# be sure to also use the snortdb-extra tables if you want

# mappings for tcp flags, protocols, and ports

?>[/code]

[Barand]

Can\'t imediately see error on line 4 but I do notice some reserved words used as column and index names viz. TIMESTAMP, TIME. You can do it but you need backticks round the names (`time` etc.)

 

hth

[semar]

thanks for the reply.

 

If i just run line....

 

CREATE TABLE schema ( vseq INT UNSIGNED NOT NULL,

ctime DATETIME NOT NULL,

PRIMARY KEY (vseq));

 

table is created, but adding the next line causes the error \'you have an error in your SQL syntax\'

 

INSERT INTO schema (vseq, ctime) VALUES (\'106\', now());

 

regards

 

Semar

[Barand]

What happens if you try

 

INSERT INTO schema (`vseq`, `ctime`) VALUES (\'106\', now());

[semar]

tried that, still get the same error.

 

semar

[PHPcadet]

I ran the script (CREATE and INSERT) in phpMyAdmin and did not get any errors. Could you post your script file that they are in. Check your mySQL manual to see what a 1064 error is.

[semar]

I have been using DBManager 2.2.0 to run script, the complete script is inserted in my initial posting.

 

semar

[semar]

I have just downloaded PhpAdmin and ran the complete sql query script successfully.

 

DBManager 2.2.0 uninstalled from my computer!

 

 

thankyou everyone for you help.

 

Semar

[PHPcadet]

GREAT! Glad to be of help.