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.