Difference between revisions of "How to add logger tables"

(Created page with "=How to add logger tables= {{KB|{{ZC}}|{{ZCS 8.8}}|{{ZCS 8.7}}||}} {{WIP}} ==Problem== Zimbra includes the logger service, that collects data from different servers for the...")
 
(How to add logger tables)
 
(11 intermediate revisions by the same user not shown)
Line 6: Line 6:
 
==Problem==
 
==Problem==
  
Zimbra includes the logger service, that collects data from different servers for the state of the different services. If multiserver installation is in place, there must be only one logger server, and the rest of the server are viewed as clients. Each client and the logger server keep a database for the known servers in the setup, placed in SQLite3 database.  
+
The Logger service collects data from each server, for the state of the different services. If multiserver installation is in place, there must be only one logger server, and the rest of the servers are viewed as clients. Each client and the logger server keep a database for the known servers in the setup, placed in SQLite3 database. Sometimes, due to power outage or different reasons, one or more tables might be missing. In this wiki we will see how to recreate tables from the logger DB.
  
 
==Resolution==
 
==Resolution==
  
1. Enter mysql command line:
+
1. Enter the SQLite3 database:
  $ mysql
+
  $ sqlite3 /opt/zimbra/logger/db/data/logger.sqlitedb
  
2. Once inside confirm the current setting:
 
show variables like "max_connections";
 
  
3. Change the current setting with higher value:
+
2. Once inside confirm which tables exist:
  set global max_connections = 200;
+
  sqlite> .tables
 +
'''config          hosts            rrd_column_type  rrds'''
  
4. Restart:
 
$ mysql.server stop
 
$ mysql.server start
 
  
However after server restart these settings will be gone.  
+
3. If tables are missing, recreate them with the following lines. For each table, copy and paste the text bundle to the sqlite command line:
  
==== To make the change permanent: ====
+
CREATE TABLE '''hosts''' (
 +
        id          INTEGER PRIMARY KEY,
 +
        dns_hostname VARCHAR(255) NOT NULL UNIQUE,
 +
        zm_hostname  VARCHAR(255)
 +
    );
  
1. Edit '''my.cnf''' file and under '''['''mysqld''']''' section change the ''max_connections'' value.
 
  
2. Restart:
+
CREATE TABLE '''config''' (
$ mysql.server stop
+
            version INTEGER NOT NULL UNIQUE
$ mysql.server start
+
        );
  
 +
 +
CREATE TABLE '''rrds''' (
 +
        id          INTEGER PRIMARY KEY,
 +
        host_id    INTEGER NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
 +
        col_name    VARCHAR(255) NOT NULL,
 +
        col_name_19 VARCHAR(20) NOT NULL, -- due to rrd 19 char limit
 +
        col_num    INTEGER NOT NULL,
 +
        csv_file    VARCHAR(255) NOT NULL,
 +
        rrd_file    INTEGER NOT NULL DEFAULT 0
 +
    );
 +
 +
 +
CREATE TABLE '''rrd_column_type''' (
 +
            csv_file    varchar(255) not null,
 +
            col_name    varchar(255) not null,
 +
            -- G-AUGE, C-OUNTER, A-BSOLUTE or D-ERIVED
 +
            col_type    char(1) not null,
 +
            col_interval integer, col_unit varchar(64),
 +
            CONSTRAINT unqcoltype UNIQUE (csv_file, col_name)
 +
        );
 +
 +
 +
Exit the db with:
 +
.quit
 +
 +
 +
4. If the [hosts] table was missing, you can re-populate it with the /'''opt/zimbra/bin/zmloggerhostmap''' command line tool.
 +
 +
To add mapping use the -a switch at the end as follows:
 +
/opt/zimbra/bin/zmloggerhostmap -a server1. domain.com  server1. domain.com
 +
 +
To delete use -d switch:
 +
/opt/zimbra/bin/zmloggerhostmap -d server1. domain.com  server1. domain.com
 +
 +
To view current settings:
 +
/opt/zimbra/bin/zmloggerhostmap
 +
 +
<br>
 +
The hostmap rely on correct values set in the hosts file, DNS and the zimbra_server_hostname attribute. You need to make sure all is correct for proper functioning.
  
  
'''IMPORTANT''': Increasing the connections will also increase the memory usage on the server. Make sure you have enough resources before applying these changes.
 
  
  

Latest revision as of 13:14, 1 October 2019

How to add logger tables

   KB 23882        Last updated on 2019-10-1  




0.00
(0 votes)


Problem

The Logger service collects data from each server, for the state of the different services. If multiserver installation is in place, there must be only one logger server, and the rest of the servers are viewed as clients. Each client and the logger server keep a database for the known servers in the setup, placed in SQLite3 database. Sometimes, due to power outage or different reasons, one or more tables might be missing. In this wiki we will see how to recreate tables from the logger DB.

Resolution

1. Enter the SQLite3 database:

$ sqlite3 /opt/zimbra/logger/db/data/logger.sqlitedb


2. Once inside confirm which tables exist:

sqlite> .tables
config           hosts            rrd_column_type  rrds


3. If tables are missing, recreate them with the following lines. For each table, copy and paste the text bundle to the sqlite command line:

CREATE TABLE hosts (
       id           INTEGER PRIMARY KEY,
       dns_hostname VARCHAR(255) NOT NULL UNIQUE,
       zm_hostname  VARCHAR(255)
   );


CREATE TABLE config (
           version INTEGER NOT NULL UNIQUE
       );


CREATE TABLE rrds (
       id          INTEGER PRIMARY KEY,
       host_id     INTEGER NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
       col_name    VARCHAR(255) NOT NULL,
       col_name_19 VARCHAR(20) NOT NULL, -- due to rrd 19 char limit
       col_num     INTEGER NOT NULL,
       csv_file    VARCHAR(255) NOT NULL,
       rrd_file    INTEGER NOT NULL DEFAULT 0
   );


CREATE TABLE rrd_column_type (
           csv_file     varchar(255) not null,
           col_name     varchar(255) not null,
           -- G-AUGE, C-OUNTER, A-BSOLUTE or D-ERIVED
           col_type     char(1) not null,
           col_interval integer, col_unit varchar(64),
           CONSTRAINT unqcoltype UNIQUE (csv_file, col_name)
       );


Exit the db with:

.quit


4. If the [hosts] table was missing, you can re-populate it with the /opt/zimbra/bin/zmloggerhostmap command line tool.

To add mapping use the -a switch at the end as follows:

/opt/zimbra/bin/zmloggerhostmap -a server1. domain.com  server1. domain.com

To delete use -d switch:

/opt/zimbra/bin/zmloggerhostmap -d server1. domain.com  server1. domain.com

To view current settings:

/opt/zimbra/bin/zmloggerhostmap


The hostmap rely on correct values set in the hosts file, DNS and the zimbra_server_hostname attribute. You need to make sure all is correct for proper functioning.



Verified Against: Zimbra Collaboration 8.7, 8.8.9, 8.8.12 Date Created: 7/10/2019
Article ID: https://wiki.zimbra.com/index.php?title=How_to_add_logger_tables Date Modified: 2019-10-01



Try Zimbra

Try Zimbra Collaboration with a 60-day free trial.
Get it now »

Want to get involved?

You can contribute in the Community, Wiki, Code, or development of Zimlets.
Find out more. »

Looking for a Video?

Visit our YouTube channel to get the latest webinars, technology news, product overviews, and so much more.
Go to the YouTube channel »


Wiki/KB reviewed by SME1 SME2 COPY EDITOR Last edit by Teodor Vizirov
Jump to: navigation, search