[SOLVED] Barnyard2 setup

3rensho · 10-26-2017, 10:30 AM

Running Slack64-current (plus AlienBob's Plasma 5). On another machine some years ago I had snort set up feeding to barnyard2 and to mysql. Worked fine. On a different machine now I have snort and pulled pork installed and seem to be running fine. I installed barnyard2 and got to the point of setting up the snort database in Mariadb-10.2.9. Running the supplied schema threw some errors so I created the db and populated it with tables manually using phpMyAdmin. It appears that the barnyard schema was written a while back and I got errors trying to create two of the tables (everything else went without a hitch) -

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));

gave error -

Error

SQL query:

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))

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ref_system_id INT UNSIGNED NOT NULL,
ref_tag ' at line 2

and

CREATE TABLE reference_system ( ref_system_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ref_system_name VARCHAR(20),
PRIMARY KEY (ref_system_id));

gave -

Error

SQL query:

CREATE TABLE reference_system ( ref_system_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ref_system_name VARCHAR(20),
PRIMARY KEY (ref_system_id))

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ref_system_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
' at line 1

Can anyone that is familiar with updated versions of MariaDB please have a look and heopfully let me know the proper syntax to create these two tables??? Thanks in advance

kjhambrick · 10-27-2017, 05:37 AM

3rensho --

I didn't see any issues with your SQL Code.

So I cut-n-pasted your Scripts directly from your LQ Post into a newly created DB and it worked fine.

Session below.

Maybe it is an issue with phpMyAdmin and mariadb ?

What happens if you paste your code directly into the mysql client ?

EDIT: I am running Slackware64 14.2 + Multilib with mariadb 10.0.32:

Code:

# ver mariadb
-rw-r--r-- 1 root root 20640 Sep 11 06:39 /var/log/packages/mariadb-10.0.32-x86_64-1_slack14.2
-rw-r--r-- 1 root root  3492 Sep 11 06:41 /var/log/packages/mariadb-compat32-10.0.32-x86_64-1_slack14.2compat32

HTH.

-- kjh

Code:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.0.32-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database kjh ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use kjh ;
Database changed
MariaDB [kjh]> 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));
Query OK, 0 rows affected (0.08 sec)

MariaDB [kjh]> CREATE TABLE reference_system ( ref_system_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> ref_system_name VARCHAR(20),
    -> PRIMARY KEY (ref_system_id));
Query OK, 0 rows affected (0.08 sec)

MariaDB [kjh]> drop table reference_system ;
Query OK, 0 rows affected (0.03 sec)

MariaDB [kjh]> drop table reference ;
Query OK, 0 rows affected (0.03 sec)

MariaDB [kjh]> drop database kjh ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit
Bye

3rensho · 10-27-2017, 08:55 AM

Hello, thank you for giving it a try and reporting the results. I gave it a go just now directly in mysql but alas it resulted in the same error.

MariaDB [snort]> 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));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ref_system_id INT UNSIGNED NOT NULL,
ref_tag ' at line 2
MariaDB [snort]>

Must be some change in 10.2.9

3rensho · 10-29-2017, 02:25 AM

Git it sorted. I found the same schema at https://github.com/firnsy/barnyard2/...s/create_mysql and even though it looks identical to the one supplied with Barnyard2 the one from github works fine in mysql. Must be some subtle bit that I can't see in the schema supplied with Barnyard2. Anyway, alles paletti.

kjhambrick · 10-29-2017, 04:46 AM

Good deal 3rensho !

One very subtle change for the two tables over in the SCHEMA that you linked is that `ref_system_id` is quoted with back-ticks:

Code:

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`));

Odd ... ref_system_id doesn't appear to be a reserved word, but SQL Engines can be mysterious at times

Thanks for the feedback !

-- kjh

3rensho · 10-30-2017, 03:26 AM

Good catch. I have Barnyard2 dying after starting now due to back ticks -

[SystemPullDataStore()]: Failed exeuting query [SELECT ref_system_id, ref_system_name FROM reference_system;] , will retry
[CacheSynchronize()]:, SystemCacheSyncronize() call failed.

but with backticks -

MariaDB [snort]> SELECT `ref_system_id`, `ref_system_name` FROM `reference_system`;
Empty set (0.00 sec)

Barnyard2 needs updating for use with updated MariaDB.