Upgrade to 1.1.6 from 0.8.8h on CentOS 6.9

[hrv231]

I'm seeing a lot of people having upgrade issues with Cacti from version 0.8.8h to 1.1.6 using the repository.
I will be explaining what I had to do to make this work again, because after updating cacti everything stopped working for me.
I'm using:
CentOS 6.9
kernel 2.6.32-696.1.1.el6.x86_64
Cacti 0.8.8h
MySQL 5.1.73
spine 0.8.8g

username for cacti database is cacti, a lot of people use cactiuser online

I'm writing this guy after a lot of tries going back and forth with snapshots and after a lot of research online.

1) Before updating cacti from the repository you should first update the MySQL database to at least the minimum that version 1.1.6 recommends which is 5.6

//Add MySQL repo
#vim /etc/yum.repos.d/mysql-community.repo

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-com ... $basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-com ... $basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

save & exit

//Add RPM-GPG-KEY-mysql
#vim /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
//just copy and paste the GPG-KEY from this website:

https://repo.mysql.com/RPM-GPG-KEY-mysql

save & exit

//Do a yum update and it will install mysql 5.6
#yum -y update mysql*

//After that I started seeing a lot of database issues, the database failed to start and find out that it's because of the innodb, by adding the below line in /etc/my.cnf will fix it:
#vim /etc/my.cnf

innodb_data_file_path = ibdata1:10M:autoextend

//useful commands to check that the database is running:
#service mysqld status
#service mysqld restart

//Then I ran:
#mysql_upgrade -u root -p

//And this one:
#mysqlcheck --auto-repair --databases cacti -u root -p


2) Cacti installation will complain about the TimeZone database on mysql database not being able to connect, so for that I did this:
#mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
//you will get 3 warnings but that's fine, and then run this inside the database:
#mysql -u root -p

mysql> use mysql;
mysql> GRANT SELECT ON mysql.time_zone_name TO cacti@localhost;
mysql> flush privileges;
mysql> quit;

3) After checking that the database was running I did a repair of cacti database:
#cd /var/lib/cacti/cli
#php repair_database.php --force

4) Update cacti to version 1.1.6
#yum update

//Cacti is installed and when you open the webpage you should see the License Agreement, accept that, clic next and you will see a lot of recommended options to change in /etc/my.cnf file.
This is my complete my.cnf file:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_heap_table_size = 268435456
max_allowed_packet = 67108864
tmp_table_size = 268435456
join_buffer_size = 134217728
innodb_file_per_table = ON
innodb_buffer_pool_size = 1088337920
innodb_doublewrite = OFF
innodb_additional_mem_pool_size = 83886080
innodb_flush_log_at_timeout = 3
innodb_read_io_threads = 32
innodb_write_io_threads = 16
innodb_data_file_path = ibdata1:10M:autoextend

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

//The only thing that I did not fix is the complain with regard to the "collation_server" and "character_set_client"
//Restart MySQL service
#service mysqld restart

//Now click Next and then the Upgrade button to upgrade Cacti.
//After it finished I restarted the httpd service and then did:
#cd /var/lib/cacti/cli
#php upgrade_database.php

//I started seeing holes in my graphs and some of the graphs were not displaying at all, so the next step was upgrading spine to the same version as Cacti.
#yum info help2man

#cd /tmp
#wget http://www.cacti.net/downloads/spine/ca ... 1.6.tar.gz
#tar -xzvf cacti-spine-1.1.6.tar.gz

//compile spine
#cd /tmp/cacti-spine-1.1.6
./bootstrap
./configure
make
make install

//Check that the new version of spine is installed
#/usr/local/spine/bin/spine --version

There were some other problems that I encounter along the way with permissions but I can't remember, searching on google you will find out the answer.

Even if you upgraded cacti first and still has the old version of MySQL, I think with some test and tweaks you can make it work.

Thanks

[rony]

Appreciate the guide! Thanks!