Hi Nicholas,
For the user permission issue i would recommend trying one of the
following things: 1) adding a 'sql_host: 127.0.0.1' to your config or 2)
configuring your user as 'pmacct'@'%'. If i recall correctly, when
connecting locally you could opt for the loopback interface or the UNIX
socket; 'localhost' applies when using the loopback interface (and not
the UNIX socket). Shall none of this work, please rever to 1.6.2 to see
if that is (not) working for you.
For the SQL UPDATE statement, you are using a v8 schema and should set
pmacct to use that same version: a 'sql_table_version: 8' should do.
Paolo
On Thu, Nov 02, 2017 at 02:11:04PM -0500, Nicholas Geovanis wrote:
> I also don't know if this is relevant or important, but I note that the
> subsequent failing
> SQL UPDATE statements are referencing columns which do not exist in the
> acct_v8 table.
> Here's an example; note that the schema defines column "port_src", not the
> missing "src_port":
>
> UPDATE `acct_v8` SET packets=packets+21, bytes=bytes+9818 WHERE
> ip_src=INET6_ATON('192.168.50.133') AND
> ip_dst=INET6_ATON('239.255.255.250') AND src_port=0 AND dst_port=0 AND
> ip_proto='0' AND mac_src='0:0:0:0:0:0' AND mac_dst='0:0:0:0:0:0'
> ERROR ( default_mysql/mysql ): Unknown column 'src_port' in 'where clause'
>
> Here's the running DB:
> mysql> describe acct_v8;
> +----------------+---------------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +----------------+---------------------+------+-----+---------+-------+
> | agent_id | int(4) unsigned | NO | PRI | NULL | |
> | class_id | char(16) | NO | PRI | NULL | |
> | mac_src | char(17) | NO | PRI | NULL | |
> | mac_dst | char(17) | NO | PRI | NULL | |
> | vlan | int(2) unsigned | NO | PRI | NULL | |
> | as_src | int(4) unsigned | NO | PRI | NULL | |
> | as_dst | int(4) unsigned | NO | PRI | NULL | |
> | ip_src | char(15) | NO | PRI | NULL | |
> | ip_dst | char(15) | NO | PRI | NULL | |
> | port_src | int(2) unsigned | NO | PRI | NULL | |
> | port_dst | int(2) unsigned | NO | PRI | NULL | |
> | tcp_flags | int(4) unsigned | NO | | NULL | |
> | ip_proto | char(6) | NO | PRI | NULL | |
> | tos | int(4) unsigned | NO | PRI | NULL | |
> | packets | int(10) unsigned | NO | | NULL | |
> | bytes | bigint(20) unsigned | NO | | NULL | |
> | flows | int(10) unsigned | NO | | NULL | |
> | stamp_inserted | datetime | NO | PRI | NULL | |
> | stamp_updated | datetime | YES | | NULL | |
> +----------------+---------------------+------+-----+---------+-------+
> 19 rows in set (0.00 sec)
>
>
>
> On Thu, Nov 2, 2017 at 1:58 PM, Nicholas Geovanis <nickgeova...@gmail.com>
> wrote:
>
> > On Thu, Nov 2, 2017 at 11:59 AM, Yann Belin <y.belin...@gmail.com> wrote:
> >
> >> Hi Nicholas,
> >>
> >> How is your MySQL user defined, xxx@'localhost'?
> >>
> > Hi -
> > I've tried both pmacct and pmacct@localhost in the conf file, but the
> > result is the same. Also tried
> > with and without quotes.
> >
> > Perhaps not related, but I also noticed a syntax error message in the conf
> > file from the following line:
> > plugins: mysql
> > Leaving that line out of the conf file causes the error message to not
> > appear at startup, but doesn't attain
> > database connection either. Here are the log lines in that case (note the
> > sql_passwd is the shipped default):
> >
> > DEBUG: [/home/nick/src/pmacct.conf] plugins:mysql
> > WARN: [/home/nick/src/pmacct.conf:11] Unable to handle key: plugins.
> > Ignored.
> > INFO ( default/core ): Promiscuous Mode Accounting Daemon, pmacctd 1.7.0
> > (20170924-00+c1)
> >
> > Here is the full startup log without the "plugins: mysql" line in the conf
> > file. Note that the sql_passwd is set to
> > the shipped default:
> >
> > root@nick-OptiPlex-755:/home/nick/src/pmacct-1.7.0#
> > /usr/local/sbin/pmacctd -d -P mysql -f /home/nick/src/pmacct.conf
> > 192.168.50.211
> > DEBUG: [/home/nick/src/pmacct.conf] plugin name/type: 'default'/'core'.
> > DEBUG: [/home/nick/src/pmacct.conf] plugin name/type:
> > 'default_mysql'/'mysql'.
> > DEBUG: [/home/nick/src/pmacct.conf] daemonize:false
> > DEBUG: [/home/nick/src/pmacct.conf] aggregate:src_host, dst_host
> > DEBUG: [/home/nick/src/pmacct.conf] interface:enp0s25
> > DEBUG: [/home/nick/src/pmacct.conf] sql_db:pmacct
> > DEBUG: [/home/nick/src/pmacct.conf] sql_user:pmacct
> > DEBUG: [/home/nick/src/pmacct.conf] sql_passwd:arealsmartpwd
> > DEBUG: [/home/nick/src/pmacct.conf] sql_table:acct_v8
> > DEBUG: [/home/nick/src/pmacct.conf] sql_num_hosts:true
> > DEBUG: [/home/nick/src/pmacct.conf] debug:true
> > INFO ( default/core ): Promiscuous Mode Accounting Daemon, pmacctd 1.7.0
> > (20170924-00+c1)
> > INFO ( default/core ): '--enable-mysql' '--enable-l2' '--enable-ipv6'
> > '--enable-64bit' '--enable-threads' '--enable-traffic-bins'
> > '--enable-bgp-bins' '--enable-bmp-bins' '--enable-st-bins'
> > INFO ( default/core ): Reading configuration file
> > '/home/nick/src/pmacct.conf'.
> > INFO ( default_mysql/mysql ): plugin_pipe_size=4096000 bytes
> > plugin_buffer_size=288 bytes
> > INFO ( default_mysql/mysql ): ctrl channel: obtained=212992 bytes
> > target=113776 bytes
> > INFO ( default_mysql/mysql ): cache entries=32771 base cache
> > memory=11631392 bytes
> > INFO ( default/core ): link type is: 1
> >
> >> Yann
> >> On Thu, Nov 2, 2017, 17:54 Nicholas Geovanis <nickgeova...@gmail.com>
> >> wrote:
> >>
> >>> Hi -
> >>> I have pmacct 1.7.0 installed from source on an Ubuntu 17.10 system
> >>> running MySQL 5.7. Just experimenting so far. I have specified both
> >>> sql_user and sql_passwd (and sql_table and sql_db) in the conf file, run
> >>> the initialization sql successfully, and started pmacctd like this:
> >>>
> >>> /home/nick/src/pmacct-1.7.0# /usr/local/sbin/pmacctd -d -P mysql -f
> >>> /home/nick/src/pmacct.conf 192.168.50.211
> >>>
> >>> No matter what I do, pmacctd cannot connect to the database:
> >>>
> >>> ERROR ( default_mysql/mysql ): PRIMARY 'mysql' backend trouble.
> >>> ERROR ( default_mysql/mysql ): The SQL server says: Access denied for
> >>> user 'pmacct'@'localhost' (using password: YES)
> >>>
> >>> Despite this, I can successfully connect using the very same password
> >>> which is defined in the pmacct.conf file if I type it by hand:
> >>> "nick@nick-OptiPlex-755:~$ mysql -u pmacct -p
> >>> Enter password:
> >>> Welcome to the MySQL monitor. Commands end with ; or \g.
> >>> Your MySQL connection id is 163
> >>> Server version: 5.7.20-0ubuntu0.17.10.1 (Ubuntu)
> >>>
> >>> Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights
> >>> reserved.
> >>>
> >>> Oracle is a registered trademark of Oracle Corporation and/or its"
> >>> ...and so on, clipped.
> >>>
> >>> There is no difference if I enclose the conf file's sql_passwd argument
> >>> in quotes or not.
> >>> What is my issue here? Thanks.....Nick
> >>>
> >>>
> _______________________________________________
> pmacct-discussion mailing list
> http://www.pmacct.net/#mailinglists
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
