• Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Search
  • Register
  • Login
Netgate Discussion Forum
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Search
  • Register
  • Login

Barnyard2 can't connect to remote mysql

Scheduled Pinned Locked Moved IDS/IPS
5 Posts 2 Posters 824 Views
Loading More Posts
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • R
    rickyzhang
    last edited by rickyzhang Aug 4, 2019, 12:21 PM Aug 4, 2019, 3:17 AM

    I used pfsense 2.4.4-RELEASE-p3 with snort package. I tried to send alerts to MySQL server in my Dell T20 home server.

    But Barnyard2 log show the following repeatedly.

    Aug  3 22:58:47 pfsense.localdomain barnyard2[38593]: [Select()]: Failed to execute  query [SELECT vseq FROM `schema`] , will retry
    

    I can used the credential to login from pfsense ssh shell. But it show no tables in the data set:

    mysql> show tables;
    Empty set (0.00 sec)
    

    It seemed that it can't see the schema thus it can't proceed to create tables. Should the user be root in mysql?

    Here is my MySQL server version:

    Server version: 5.5.5-10.3.9-MariaDB-log MariaDB Server

    1 Reply Last reply Reply Quote 0
    • R
      rickyzhang
      last edited by Aug 4, 2019, 11:09 AM

      I initialized mysql database manually from pfsense router.

      I downloaded create_mysql script from barnyard2 and create database and tables:

      mysql --user=root --password=mypassword -P 3306 --host=192.168.2.30 snort_db < create_mysql
      

      I grant permission to db user snort:

      grant INSERT,SELECT on snort_db.* to snort;
      grant INSERT,SELECT,UPDATE on snort_db.sensor to snort;
      

      However, barnyard2 still failed:

      103 Aug  4 06:51:33 pfsense.localdomain barnyard2[66013]: ===============================================================================
      104 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: [CacheSynchronize()]:, SystemCacheSyncronize() call failed.
      105 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: [SystemPullDataStore()]: Failed exeuting query [SELECT ref_system_id, ref_system_name FROM reference_system;] , will retry
      106 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: FATAL ERROR: database [DatabaseInitFinalize()]: CacheSynchronize() call failed ...
      107 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: Barnyard2 exiting
      108 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: database: Closing connection to database "snort_db"
      109 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: Record Totals:
      110 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]:    Unknown:           0 (0.000%)
      111 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]:    Suppressed:           0 (0.000%)
      112 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: ===============================================================================
      

      select statement failed because the syntax issue.

      mysql> SELECT ref_system_id, ref_system_name FROM reference_system;
      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, ref_system_name FROM reference_system' at line 1
      
      mysql> SELECT `ref_system_id`, ref_system_name FROM reference_system;
      Empty set (0.00 sec)
      

      The barnyard2 patched this 2 years ago in their Github repo. It seems that pfsense use old barnyard2 code.

      1 Reply Last reply Reply Quote 0
      • R
        rickyzhang
        last edited by Aug 4, 2019, 11:51 AM

        I also tried to replace maria db with mysql.

        254 Aug  4 07:42:07 pfsense.localdomain barnyard2[85723]: ---------------------------- +[ Signature Suppress list ]+
        255 Aug  4 07:42:09 pfsense.localdomain barnyard2[85723]: Barnyard2 spooler: Event cache size set to [8192]
        256 Aug  4 07:42:09 pfsense.localdomain barnyard2[85723]: Log directory = /var/log/snort/snort_mvneta132940
        257 Aug  4 07:42:09 pfsense.localdomain barnyard2[85723]: INFO database: Defaulting Reconnect/Transaction Error limit to 10
        258 Aug  4 07:42:09 pfsense.localdomain barnyard2[85723]: INFO database: Defaulting Reconnect sleep time to 5 second
        259 Aug  4 07:42:09 pfsense.localdomain barnyard2[85723]: Initializing daemon mode
        260 Aug  4 07:42:09 pfsense.localdomain barnyard2[85777]: Daemon initialized, signaled parent pid: 85723
        261 Aug  4 07:42:09 pfsense.localdomain barnyard2[85723]: Daemon parent exiting
        262 Aug  4 07:42:09 pfsense.localdomain barnyard2[85777]: PID path stat checked out ok, PID path set to /var/run
        263 Aug  4 07:42:09 pfsense.localdomain barnyard2[85777]: Writing PID "85777" to file "/var/run/barnyard2_mvneta132940.pid"
        264 Aug  4 07:42:09 pfsense.localdomain barnyard2[85777]: database mysql_error: Authentication plugin 'caching_sha2_password' cannot be loaded: Cannot open "/usr/local/lib/mysql/plugin/cachin    g_sha2_password.so"
        265 Aug  4 07:42:09 pfsense.localdomain barnyard2[85777]: Barnyard2 exiting
        

        The whole barnyard2 is not tested. It should not release.

        1 Reply Last reply Reply Quote 0
        • N
          NogBadTheBad
          last edited by NogBadTheBad Aug 4, 2019, 12:16 PM Aug 4, 2019, 12:11 PM

          The barnyard2 code is old all @bmeeks did is port it to pfSense.

          With the next major Snort release I highly doubt will include barnyard2.

          https://forum.netgate.com/topic/143538/barnyard2-and-mariadb

          Andy

          1 x Netgate SG-4860 - 3 x Linksys LGS308P - 1 x Aruba InstantOn AP22

          1 Reply Last reply Reply Quote 0
          • R
            rickyzhang
            last edited by rickyzhang Aug 4, 2019, 12:42 PM Aug 4, 2019, 12:21 PM

            I see. I will stop using Barnyard2.

            1 Reply Last reply Reply Quote 0
            5 out of 5
            • First post
              5/5
              Last post
            Copyright 2025 Rubicon Communications LLC (Netgate). All rights reserved.
              This community forum collects and processes your personal information.
              consent.not_received