Migrating Zabbix 2.4.6 on sqlite to Zabbix 3.0 on mysql

Earlier, we set up Zabbix 2.4.6 with sqlite to just play around with it. But soon we started using it for monitoring various servers and applications. We were obviously aware that sqlite will not scale. We occasionally noticed database lock errors on UI. Finally, we decided to set up one with mysql for production grade scalability. Now, we loved the data we already had accumulated over couple of months. So, we migrated from sqlite to mysql and at same time also upgraded the zabbix version to 3.0. Here is how we did it. 

Steps:

1. Export Sqlite DB: That was fairly simple using sqlite .dump command. It produced sql scripts with ddl and dml statements. 
2. Massage SQL Script: Script created above was still a sqlite script and will not work for mysql. We needed to do following: 
• Used this sed to convert sqlite to mysql. This worked for everything except changing AUTOINCREMENT to AUTO_INCREMENT as sqlite script didn't have CREATE TABLE and AUTOINCREMNET in same line. 
• Used sed to change 'bigint' to 'bigint unsigned' as that is used by zabbix.
  sed -e "s/bigint/bigint unsigned/g"  zabbix.mysql.sql > zabbix_final.sql
• Divided sql script into four parts as it was huge script. First one with all ‘INSERT INTO `history`’ statements. Second with all ‘INSERT INTO `history_uint`’. Third with all Indices and finally fourth with rest of statements. I used simple grep command.
3. Execute SQL Scripts: We executed all scripts with autocommit turned off. And committed after every script.
• First we executed fourth script as that had all ddl scripts for tables.
• Then simultaneously scripts with inserts to history and history_uint table were executed. 
• And finally all indices were created. Few indices failed for me with error - "specified key was too long; max key length is 767 bytes"
4. Install and Upgrade: Install zabbix 3.0 as documented here, except part regarding executing mysql script. Start zabbix server. This will upgrade db too. Check for errors in zabbix server logs. If there is any sql related error, fix it manually and then restart zabbix server.
5. Upgrade / Configure agents: Upgrade agents. This is not mandatory. But good to do. If zabbix server IP is changed, corresponding changes need to be done in agent configuration. Agents need to be restarted.
6. Finally a Duhhhhhhhh: Blob in images table were not usable after migration. So, all rows from this table were deleted. All insert statements into images table were grepped from zabbix mysql 3.0 script and executed on this database. Not a big deal unless one has added any custom images. :)

Simple JsonPath in Python

Following python method is simplified jsonpath implementation that takes json object and jsonPath as input and returns json object at that json path as output.

For json array, we should use ".[element_num]". For example, from
    {"emp" : ["Indra", "Narada", "Yama"] }

to get "Narada", json path should be "emp.[1]". Remember, count starts with zero.

For objects with names that may vary, you may use "{object_number}". For example, from    

   {"machines": { "1a2d": { "os" : "Linux", "ram" : "16GB", "uptime" : "20045"}}}

if we need to get uptime,  json path can be "machines.{0}.uptime". But please ensure sequence of element in json is always fixed.

Python 3- Send Data to Zabbix Server

Following is method to send data to zabbix (2.4.6)  server written in python 3.

Corresponding Item key should be configured as zabbix trapper on zabbix server to successfully process this request.

Zabbix data is a json in following format.

{
  "request":"sender data",
  "data":[
     {
       "host":"hostName",
       "key":"key-1",
       "value":"200",
       "clock":1458748060
     },
     {
       "host":"hostName",
       "key":"key-4",
       "value":"test",
       "clock":1458748060}
     ]}
}

SQL Query- Where Value Between Two Columns

Imagine a simplified sample table that contains id, low_range, high_range. Low_range and High_range can be decimal ip address, zip codes or even timestamps.

id	low_range	high_range
1	400002	400120
5	407049	407340
5003	637190	637805
702984	849380	849875

We need to find row where a number is greater than low_range and lesser than high_range. Assumption is that maximum of one record and minimum of zero will be returned. 

SELECT id FROM table WHERE num > low_range AND num < high_range. 

The sql above might work well when we have probably few hundreds rows. But it is extremely slow with just few thousands records. Adding index on min_range (or / and max_range) will be helpful only for last few (or first few) records in index. For rest of rows, engine will consider a full table scan to be faster compared to using index. 

Solution 1:

SELECT id
FROM tab tab,
( SELECT max(low_range) as max_low_range
           FROM tab WHERE low_range <= num ) low_range_table
WHERE table.low_range = low_range_table.max_low_range
AND full_table.high_range >= num;

While above query might look complex to human, it makes life easier for database engine.   It first finds max low range available lesser than number. We have index on low_range; so this sub query will be fast. Next it joins with full table using this low_range. (Ideally, we should join with primary key). This will give exactly one row. Finally it validates if high range is still higher than number / string.

Explain plan:

Solution 2: 

          SELECT id
          FROM (
                    SELECT * FROM tab tab
                    WHERE low_range <= num
                     ORDER BY low_range DESC LIMIT 1) as max_low_range
          WHERE max_low_range.high_range >= num

Explain Plan