I cannot get mysql settings correct - [SOLVED}

[themetman]

I am installing cacti 1.2.3 on Ubuntu 18.04. I have done this many times, so am not new to this.
I am using 1.2.3 because my graphing disappered on 1.2.4 so I have decided to start again on a differnet server with the install manually rather than using apt to install. Then I am in control.
My /etc/mysql/conf.d/cacti.cnf file is

Code: Select all

[mysq]
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4
character_set_client=utf8mb4
max_heap_table_size=512M
max_allowed_packet=16777216
tmp_table_size=128M
join_buffer_size=128M
innodb_file_per_table=ON
innodb_buffer_pool_size=1024M
innodb_doublewrite=OFF
innodb_flush_log_at_timeout=3
innodb_read_io_threads=32
innodb_write_io_threads=16
innodb_buffer_pool_instances=11
innodb_io_capacity=5000
innodb_io_capacity_max=10000

I have no errors in journalctl -fxe
BUT
the Cacti install shows these remaining problems

Code: Select all

collation_server 	latin1_swedish_ci = utf8mb4_unicode_ci 
character_set_client 	latin1 		  = utf8mb4 
innodb_flush_log_at_timeout 		1 >= 3 
innodb_read_io_threads 	    		4 >= 32 
innodb_write_io_threads     		4 >= 16

So you can see that mysql is not picking up the settings from the cacti.cnf file
I have searched high and lopw across the internet for a solution to this, but cannot find anything else I can do.
I even tried the hyphen rather than the underscore in the char settings, but to no avail.
There must be a solution to this.

[tertius]

The settings must be in the [mysqld] section. You put them into some "[mysq]" section, which is unknown.

[themetman]

Well I should have spotted that myself!
However even that is not working. I am using mysql version 5.7.26
I have changed the name of the cnf file to /etc/mysql/conf.d/99_cacti.cnf so it is loaded last
I have put the settings exactly as cacti wants them, so my file is now

Code: Select all

[mysqd]
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4
character_set_client=utf8mb4
max_heap_table_size=31M
max_allowed_packet=16777216
tmp_table_size=62M
join_buffer_size=128M
innodb_file_per_table=ON
innodb_buffer_pool_size=487M
innodb_doublewrite=OFF
innodb_flush_log_at_timeout=3
innodb_read_io_threads=32
innodb_write_io_threads=16
innodb_buffer_pool_instances=2
innodb_io_capacity=5000
innodb_io_capacity_max=10000

I have restarted mysql using

Code: Select all

service mysql restart

There are no errors whatsoever in journalctl -xe
Refresh the cacti install page in my Firefox browser, but have even more errors showing up now:

Code: Select all

collation_server    latin1_swedish_ci = utf8mb4_unicode_ci
character_set_client    latin1         = utf8mb4
max_heap_table_size 		16M    >= 31M
tmp_table_size 			16M 	>= 31M
join_buffer_size 		0M 	>= 62M
innodb_buffer_pool_size 	128M 	>= 487M
innodb_doublewrite 		ON 	= OFF
innodb_flush_log_at_timeout       1 	>= 3
innodb_read_io_threads            4 	>= 32
innodb_write_io_threads           4 	>= 16
innodb_buffer_pool_instances 	  1 	>= 2
innodb_io_capacity 		200 	>= 5000
innodb_io_capacity_max 		2000 	>= 10000

This is really bizare!
I think I might change to Mariadb and see what happens.
---------------------------------------------Update ------------------------------------------
I removed mysql and installed Mariadb
Everything except the collation_server setting is being read correctly.
I have checked here https://mariadb.com/kb/en/library/setti ... ollations/ and put the suggested settings in the mysql.cnf file but the collation_server settings are still not quite correct. As I am using english this does not matter really. Here is my /etc/mysql/conf.d/mysql.cnf file now:

Code: Select all

[mysqld]
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
max_heap_table_size=32M
max_allowed_packet=16777216
tmp_table_size=62M
join_buffer_size=128M
innodb_file_per_table=ON
innodb_file_format=Barracuda
innodb_large_prefix=1
innodb_buffer_pool_size=487M
innodb_doublewrite=OFF
innodb_flush_log_at_timeout=3
innodb_read_io_threads=32
innodb_write_io_threads=16
innodb_buffer_pool_instances=4
innodb_io_capacity=5000
innodb_io_capacity_max=10000

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

So seems to be working for Mariadb not mysql

[tertius]

You should practice more careful proof-reading. You first updated the section from [mysq] to [mysqd], but it should read [mysqld]. Only when you inserted the entries in a pre-existing correctly named section you succeeded.

[themetman]

Thanks a million, @tertius