Maybe a bug? Zabbix 4.0rc3 running on top of MySQL 8.0.12

[steeladept]

I was setting up Zabbix 4.0rc3 today after I saw the release notification on a brand new CENTOS 7 install. I used the instructions for MySQL 8.0.12 and found a few minor issues with the install instructions, that I was able to work around. (You now have to create the user before you grant the rights was the main issue). However, after I have everything up and running, I go into the front end for the initial setup and I am getting this error for the MySQL database connection:

• Error connecting to database: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

I realize I can go back to MySQL 5.7 and there are many workaround options, but since this is supposed to be supported, when 4.0 comes out, I thought I would ask here, and if it is a bug, let you know.

[vso]

I think that frontend is not related to Zabbix, it’s php you can find more information here http://php.net/manual/en/ref.pdo-mysql.php hopefully it helps

[steeladept]

Thanks, because I am running into a similar issue with 4.0 GA.

Error from logs:

3357:20181003:204712.356 [Z3001] connection to database 'zabbix' failed: [2059] Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

I suspect with the current documentation that you will see more on this.

[steeladept]

Following the recommendations listed I upgraded PHP to version 7.2.10 and it still didn't resolve the issue. I then added the entry in my.cnf to "default_authentication_plugin=mysql_native_passwo rd" which also failed to address the issue. After each change I restarted mysqld and attempted the zabbix-server service with the same results. Am I missing another service or something else I might need to change?

[vso]

Is your server compiled with mysql 8 library ? I haven’t experienced such issues but I am running latest Fedora. Can you please show yum deplist zabbix_server and ldd zabbix_server

[steeladept]

Absolutely, thank you. I did use the 4.0 MySQL from Zabbix Pakages repository, so I believe it is. I am running the latest CENTOS 7 so it should be comparable to a recent Fedora build. Hope this helps...Here is the deplist output:

yum deplist zabbix_server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.mit.edu
* epel: mirror.vcu.edu
* extras: repo1.ash.innoscale.net
* remi-php72: repo1.ash.innoscale.net
* remi-safe: repo1.ash.innoscale.net
* updates: centos.mirror.constant.com


And here is the ldd:

ldd /usr/sbin/zabbix_server
linux-vdso.so.1 => (0x00007ffdfcde4000)
libmysqlclient.so.18 => /usr/lib64/mysql/libmysqlclient.so.18 (0x00007f8d8c9e1000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f8d8c7c5000)
libz.so.1 => /lib64/libz.so.1 (0x00007f8d8c5af000)
libm.so.6 => /lib64/libm.so.6 (0x00007f8d8c2ad000)
libssl.so.10 => /lib64/libssl.so.10 (0x00007f8d8c03b000)
libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007f8d8bbda000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f8d8b9d6000)
libiksemel.so.3 => /lib64/libiksemel.so.3 (0x00007f8d8b7c8000)
libxml2.so.2 => /lib64/libxml2.so.2 (0x00007f8d8b45e000)
libodbc.so.2 => /lib64/libodbc.so.2 (0x00007f8d8b1f6000)
libnetsnmp.so.31 => /lib64/libnetsnmp.so.31 (0x00007f8d8aef4000)
libssh2.so.1 => /lib64/libssh2.so.1 (0x00007f8d8acca000)
libOpenIPMI.so.0 => /lib64/libOpenIPMI.so.0 (0x00007f8d8a9bd000)
libOpenIPMIposix.so.0 => /lib64/libOpenIPMIposix.so.0 (0x00007f8d8a7b5000)
libevent-2.0.so.5 => /lib64/libevent-2.0.so.5 (0x00007f8d8a56d000)
libldap-2.4.so.2 => /lib64/libldap-2.4.so.2 (0x00007f8d8a318000)
liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x00007f8d8a109000)
libcurl.so.4 => /lib64/libcurl.so.4 (0x00007f8d89ea0000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f8d89c87000)
libpcre.so.1 => /lib64/libpcre.so.1 (0x00007f8d89a25000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f8d8980f000)
libc.so.6 => /lib64/libc.so.6 (0x00007f8d89442000)
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f8d8913b000)
/lib64/ld-linux-x86-64.so.2 (0x00007f8d8d46a000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f8d88eee000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f8d88c06000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f8d88a02000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f8d887cf000)
libgnutls.so.28 => /lib64/libgnutls.so.28 (0x00007f8d88495000)
libgcrypt.so.11 => /lib64/libgcrypt.so.11 (0x00007f8d88214000)
libgpg-error.so.0 => /lib64/libgpg-error.so.0 (0x00007f8d8800f000)
liblzma.so.5 => /lib64/liblzma.so.5 (0x00007f8d87de9000)
libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f8d87bdf000)
libOpenIPMIutils.so.0 => /lib64/libOpenIPMIutils.so.0 (0x00007f8d879d5000)
libgdbm.so.4 => /lib64/libgdbm.so.4 (0x00007f8d877cc000)
libsasl2.so.3 => /lib64/libsasl2.so.3 (0x00007f8d875af000)
libssl3.so => /lib64/libssl3.so (0x00007f8d8735d000)
libsmime3.so => /lib64/libsmime3.so (0x00007f8d87136000)
libnss3.so => /lib64/libnss3.so (0x00007f8d86e09000)
libnssutil3.so => /lib64/libnssutil3.so (0x00007f8d86bda000)
libplds4.so => /lib64/libplds4.so (0x00007f8d869d6000)
libplc4.so => /lib64/libplc4.so (0x00007f8d867d1000)
libnspr4.so => /lib64/libnspr4.so (0x00007f8d86593000)
libidn.so.11 => /lib64/libidn.so.11 (0x00007f8d86360000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f8d86152000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f8d85f4e000)
libp11-kit.so.0 => /lib64/libp11-kit.so.0 (0x00007f8d85c1f000)
libtasn1.so.6 => /lib64/libtasn1.so.6 (0x00007f8d85a0c000)
libnettle.so.4 => /lib64/libnettle.so.4 (0x00007f8d857db000)
libhogweed.so.2 => /lib64/libhogweed.so.2 (0x00007f8d855b4000)
libgmp.so.10 => /lib64/libgmp.so.10 (0x00007f8d8533c000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f8d85105000)
librt.so.1 => /lib64/librt.so.1 (0x00007f8d84efd000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f8d84cd6000)
libffi.so.6 => /lib64/libffi.so.6 (0x00007f8d84ace000)
libfreebl3.so => /lib64/libfreebl3.so (0x00007f8d848cb000)

[Atsushi]

If you want to use MySQL server for authentication with id and password as before, try using mysql_native_password as an authentication plug-in how about?

Create user sample:

mysql> create user zabbix@localhost identified with mysql_native_password by '<Your password>';
mysql> grant all privileges on zabbix.* to zabbix@localhost;

I have been running Zabbix 4.0.0 on CentOS 7.5.1804 using mysql-community-server-8.0.12-1.el7.x86_64.

[steeladept]

I tried this by setting it in the my.cnf, but didn't realize I needed to create the user that way as well (Not a DBA as you can tell ) Thank you for this, I will definitely give this a try.

[steeladept]

Thanks Atsushi, that was it! I knew to change it in the my.cnf due to the recommendations on the site vso pointed me to, but I didn't make it match in the user account inside mysql until you mentioned that.

Thank you both for all your help. May be worth noting in the Zabbix documentation regarding setup of MySQL 8+.

[jgreene]

Thank you Atsushi & steeladept.
New install with mysql-community 8.0.16-2, zabbix-server-mysql 4.2-1 and centos 7.6.1810

in my.cnf uncomment
# default-authentication-plugin=mysql_native_password

# systemctl restart mysqld