If you started OSSEC before adding the new columns to alert and data then you
most probably have an entry in alert that does not exist in data; as I have the
same in MySQL. I resolved it by deleting the very last entry from alert and
ensuring the ID was not present in the data table.
--
Thanks, Phil
----- Original Message -----
> This gets postgresql almost working:
>
> alter table alert add column alertid varchar(255) default null;
> alter table data add column timestamp timestamp
>
> Trying to add an index of time gets the following:
> ossecdb=> create index time on data (timestamp);
> ERROR: relation "time" already exists
> STATEMENT: create index time on data (timestamp);
> ERROR: relation "time" already exists
> ossecdb=> ERROR: duplicate key value violates unique constraint
> "data_pkey"
>
>
> And I'm getting a bunch of these:
> 2011/07/26 21:36:53 ossec-dbd(5203): ERROR: Error executing query
> 'INSERT INTO data(id, server_id, "user", full_log) VALUES ('6', '1',
> '_nrpe', 'Jul 26 21:37:19 junction sudo: _nrpe : TTY=unknown ;
> PWD=/ ; USER=root ; COMMAND=/usr/local/scripts/ossecctl/ossecctl
> status ossec') '. Error: 'ERROR: duplicate key value violates unique
> constraint "data_pkey"
> DETAIL: Key (id, server_id)=(6, 1) already exists.
> '.
>
> select * from data where id='6';
> id | server_id | user |
> full_log
> | timestamp
> ----+-----------+-------+---------------------------------------------------------------------------------------------------------------------------------+-----------
> 6 | 1 | _nrpe | Jul 26 21:18:55 ix sudo: _nrpe :
> TTY=unknown ; PWD=/ ; USER=root ;
> COMMAND=/usr/local/scripts/ossecctl/ossecctl status ossec |
> (1 row)
>
>
> I'm not sure where it's trying to get the 'id' from off yet.
>
>
> On Tue, Jul 26, 2011 at 4:29 PM, dan (ddp) <[email protected]> wrote:
> > On Tue, Jul 26, 2011 at 4:10 PM, --[ UxBoD ]-- <[email protected]>
> > wrote:
> >> Cheers Dan, disappointed not mentioned anywhere. To fix but still
> >> checking other tables I ran:
> >>
> >
> > I think these types of changes should be publicized a lot more than
> > they have been. It's something we'll work on in the future.
> >
> > Thanks for the instructions. Hopefully they can help anyone else
> > having issues.
> >
> >> mysql
> >> use ossec
> >> alter table alert add (alertid tinytext default null);
> >> alter table data add (timestamp timestamp);
> >> create index time on data (timestamp);
> >> --
> >> Thanks, Phil
> >>
> >> ----- Original Message -----
> >>> It (the mysql schema) appears to have changed:
> >>> https://bitbucket.org/dcid/ossec-hids/diff/src/os_dbd/mysql.schema?diff2=6e752b612937&diff1=071c2fa74389
> >>>
> >>> I don't think the postgresql one has changed, but I don't know if
> >>> it
> >>> works either.
> >>>
> >>> On Tue, Jul 26, 2011 at 12:13 PM, --[ UxBoD ]--
> >>> <[email protected]>
> >>> wrote:
> >>> > Has the database schema for 2.6 change from 2.5 as I am now
> >>> > seeing:
> >>> >
> >>> > 2011/07/26 17:10:16 ossec-dbd(5203): ERROR: Error executing
> >>> > query
> >>> > 'INSERT INTO
> >>> > alert(id,server_id,rule_id,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,alertid)
> >>> > VALUES ('95043', '1', '502','1311696616', '1', '0', '6080',
> >>> > '0',
> >>> > '32758', '1311696614.2290190')'. Error: 'Unknown column
> >>> > 'alertid'
> >>> > in 'field list''.
> >>> >
> >>> > when I start OSSEC! :(
> >>> > --
> >>> > Thanks, Phil
> >>> >
> >>> > ----- Original Message -----
> >>> >> Hi lists,
> >>> >>
> >>> >> We are very happy to announce the availability of OSSEC
> >>> >> version
> >>> >> 2.6.
> >>> >>
> >>> >> This has been a long release cycle, but it is here now with
> >>> >> some
> >>> >> good
> >>> >> new features and very stable (thanks to our beta users).
> >>> >> Our manual for the new version is also live at
> >>> >> http://www.ossec.net/doc/ .
> >>> >>
> >>> >> Release notes + new features + contributor list:
> >>> >> http://www.ossec.net/main/ossec-v2-6-released
> >>> >>
> >>> >> You can download the new version from:
> >>> >> http://www.ossec.net/main/downloads/
> >>> >>
> >>> >>
> >>> >> *The GPG key was changed as well. So make sure to download the
> >>> >> new
> >>> >> one
> >>> >> before verifying the package.
> >>> >>
> >>> >>
> >>> >> Thanks!
> >>> >> Daniel B. Cid (in name of the OSSEC + Trend team)
> >>> >> [email protected]
> >>> >>
> >>> >
> >>>
> >>
> >
>
