MySQL problem I guess

[gleve]

Hi,

Is the project still alive ? (No commit for the last 6 months)

I installed a new server under Debian 8.7.1 64 bits, then I tried to install Elsa but that didn't work. The web interface works properly but I can't see any logs on it.

After some researches, I understood that syslog-ng gets the logs from the network, then redirect them to /usr/local/elsa/node/elsa.pl which store them in /data/elsa/tmp/buffers/. But after, the perl script /usr/local/elsa/web/cron.pl launched every minutes by cron shows errors like this : DBD::mysql::st execute failed: Duplicate entry 'syslog_data.syslogs_index_1' for key 'table_name' at /usr/local/elsa/web/../node/Indexer.pm line 1636. DBD::mysql::st execute failed: Table 'syslog_data.syslogs_index_1' doesn't exist at /usr/local/elsa/web/../node/Indexer.pm line 1342. And logs are just accumulating into the buffer.

Any help would be greatly appreciated :-) Thanks in advance !

[PVi1]

Hi Gleve,

I had the same problem recently, just enable mysql query log a look for the create table syslog_data.syslogs_index_1 query. CREATE TABLE syslog_data.syslogs_index_1 ( idbigint(20) unsigned NOT NULL AUTO_INCREMENT, timestampint(10) unsigned NOT NULL DEFAULT '0', host_idint(10) unsigned NOT NULL DEFAULT '1', program_idint(10) unsigned NOT NULL DEFAULT '1', class_idsmallint(5) unsigned NOT NULL DEFAULT '1', msgtext, i0int(10) unsigned DEFAULT NULL, i1int(10) unsigned DEFAULT NULL, i2int(10) unsigned DEFAULT NULL, i3int(10) unsigned DEFAULT NULL, i4int(10) unsigned DEFAULT NULL, i5int(10) unsigned DEFAULT NULL, s0varchar(255) DEFAULT NULL, s1varchar(255) DEFAULT NULL, s2varchar(255) DEFAULT NULL, s3varchar(255) DEFAULT NULL, s4varchar(255) DEFAULT NULL, s5varchar(255) DEFAULT NULL, PRIMARY KEY (id) ) DEFAULT CHARSET=latin1 DATA DIRECTORY='/data/elsa/mysql' INDEX DIRECTORY='/data/elsa/mysql' AUTO_INCREMENT=1 ENGINE=MyISAM Then try to run that query manually as an elsa user.

In my case I had to leave mysql data dir in /var/lib/mysql because in mysql 5.5 you cannot have DATA DIRECTORY in create table statement pointing to the same directory as datadir defined in my.cnf.

Double check that you have apparmor disabled (not sure if it is installed by default on debian, too)

[gleve]

Hi PVi1,

Thank you for your reply. I did this commands as root : mysql use syslog_data CREATE TABLE syslogs_index_1 ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, timestamp int(10) unsigned NOT NULL DEFAULT '0', host_id int(10) unsigned NOT NULL DEFAULT '1', program_id int(10) unsigned NOT NULL DEFAULT '1', class_id smallint(5) unsigned NOT NULL DEFAULT '1', msg text, i0 int(10) unsigned DEFAULT NULL, i1 int(10) unsigned DEFAULT NULL, i2 int(10) unsigned DEFAULT NULL, i3 int(10) unsigned DEFAULT NULL, i4 int(10) unsigned DEFAULT NULL, i5 int(10) unsigned DEFAULT NULL, s0 varchar(255) DEFAULT NULL, s1 varchar(255) DEFAULT NULL, s2 varchar(255) DEFAULT NULL, s3 varchar(255) DEFAULT NULL, s4 varchar(255) DEFAULT NULL, s5 varchar(255) DEFAULT NULL, PRIMARY KEY (id) ) DEFAULT CHARSET=latin1 DATA DIRECTORY='/data/elsa/mysql' INDEX DIRECTORY='/data/elsa/mysql' AUTO_INCREMENT=1 ENGINE=MyISAM; CREATE TABLE syslogs_archive_1 ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, timestamp int(10) unsigned NOT NULL DEFAULT '0', host_id int(10) unsigned NOT NULL DEFAULT '1', program_id int(10) unsigned NOT NULL DEFAULT '1', class_id smallint(5) unsigned NOT NULL DEFAULT '1', msg text, i0 int(10) unsigned DEFAULT NULL, i1 int(10) unsigned DEFAULT NULL, i2 int(10) unsigned DEFAULT NULL, i3 int(10) unsigned DEFAULT NULL, i4 int(10) unsigned DEFAULT NULL, i5 int(10) unsigned DEFAULT NULL, s0 varchar(255) DEFAULT NULL, s1 varchar(255) DEFAULT NULL, s2 varchar(255) DEFAULT NULL, s3 varchar(255) DEFAULT NULL, s4 varchar(255) DEFAULT NULL, s5 varchar(255) DEFAULT NULL, PRIMARY KEY (id) ) DEFAULT CHARSET=latin1 DATA DIRECTORY='/data/elsa/mysql' INDEX DIRECTORY='/data/elsa/mysql' AUTO_INCREMENT=1 ENGINE=MyISAM;

It solved the mysql problem and the buffer is now ok. Now, when I go on the web interface, I can see in the up right corner that I have many logs. But when I submit a query, it gives me no results. And I'm sure it should give me results. So something might be broken somewhere but I have no idea why.

I think I'm done with this project, I'll try another one.

[olemissrebel]

I was seeing the same issue with the exact same error messages. After creating syslogs_index_1 (with the above create statement by 'gleve') syslogs_archive_1 was missing. After creating syslogs_archive_1 and restarting all services, old logs could be searched, but new logs were not available to the web interface. Apparmor wasn't enabled. I looked at SHOW FULL COLUMNS of those two new tables and two of the older ones and noted that the old ones were utf8 instead of latin. After changing the charset of the two new tables to utf8 and restarting services, I am able to see new logs. Hope this helps.