mysql 8 caching_sha2_password incompatibility

utdrmac commented 6 years ago

I searched through issues and did not find anything on this. As caching_sha2_password is the new default authentication method in mysql8, sysbench needs to be updated to utilize this.

sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

FATAL: unable to connect to MySQL server on socket '/var/lib/mysql/mysql.sock', aborting...
FATAL: error 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
FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:83: connection creation failed
FATAL: unable to connect to MySQL server on socket '/var/lib/mysql/mysql.sock', aborting...
FATAL: error 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
FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:83: connection creation failed

Must run the following to allow connections:

mysql> ALTER USER sbuser IDENTIFIED WITH mysql_native_password;
Query OK, 0 rows affected (0.12 sec)

grooverdan commented 6 years ago

It looks likes its just missing the mysql8.0 package includes the missing shared library, unfortunately I haven't found which one it is.

utdrmac commented 6 years ago

I installed all of the community packages for 8.0 and none of them contained what was necessary. I believe this is because caching_sha2_password is not a standalone client-side library.

The client-side plugin is built into the libmysqlclient client library and is available to any program linked against libmysqlclient. caching-sha2-pluggable-authentication.html

The manual says that if a client is not connecting over a secure channel (ie: ssl), then the client must request RSA keys from the server to encrypt the SHA2 hash before authentication can happen.

For programs that use the C API, call mysql_options() to specify the RSA public key file by passing the MYSQL_SERVER_PUBLIC_KEY option and the name of the file, or request the public key from the server by passing the MYSQL_OPT_GET_SERVER_PUBLIC_KEY option

akopytov commented 6 years ago

I'm not sure what needs to be changed on the sysbench side to make it work with caching_sha2_password auth if it's not supported by the client library. Which is the case with the client library

However, it looks like we are going to have client-side support for caching_sha2_password! :tada:

utdrmac commented 6 years ago

So, why is this closed if the issue is not resolved?

akopytov commented 6 years ago

Because it's not a sysbench issue, there's nothing I can change in the sysbench code to resolve it. The problem applies to every single client utility using the mysql client library 5.7 or older which is trying to authenticate against MySQL 8.0 using the new default caching_sha2_password authentication. https://www.google.com/search?q=Authentication+plugin+%27caching_sha2_password%27+cannot+be+loaded

utdrmac commented 6 years ago

Even with the libmysqlclient library from MySQL 8, sysbench still refuses to connect. According to the MySQL 8 manual, you must add certain options to the mysql_connect call to request RSA keys in order to facilitate connection (see my comment above). Based on what the manual says, this is an issue that all client applications will have to fix.

akopytov commented 6 years ago

@utdrmac I think you misunderstood the manual. I have no problems using sysbench with MySQL 8.0 when sysbench is linked with the 8.0 client library. I know that I'm not the only person using sysbench successfully with MySQL 8.0.

Can you describe the problems you see in more detail?

akopytov commented 6 years ago

@utdrmac For a clean experiment, I did the following in a Ubuntu Bionic docker container:

install MySQL 8.0.11 Community packages (all packages from Ubuntu Linux 18.04 (x86, 64-bit), DEB Bundle)
build sysbench from source following the instructions from here

After that I could start the oltp_read_write benchmark without any issues.

If the above doesn't work for you and there's something to change on the sysbench side, feel free to reopen this issue with more details.

utdrmac commented 6 years ago

I did not build from scratch. I used yum install sysbench

akopytov commented 6 years ago

@utdrmac prebuilt packages are of course built with whatever is provided by the corresponding distribution, which, at the moment is either MySQL <= 5.7 or MariaDB <= 10.2.

utdrmac commented 6 years ago

But I installed all MySQL 8 community packages, as you did.

akopytov commented 6 years ago

What's the output of ldd $(which sysbench) | grep client?

utdrmac commented 6 years ago

Just created a test case with fresh VM. Full output: https://pastebin.com/raw/3uzNhvGs

[root@mysql8 ~]# ldd $(which sysbench) | grep client
    libmysqlclient.so.18 => /usr/lib64/mysql/libmysqlclient.so.18 (0x00007f078cf6a000)

[root@mysql8 ~]# rpm -q -f /usr/lib64/mysql/libmysqlclient.so.18
mysql-community-libs-compat-8.0.11-1.el7.x86_64

akopytov commented 6 years ago

There's a number things to note:

mysql-community-libs-compat-8.0.11 is built from MySQL 5.6.37, and therefore doesn't have support for caching_sha2_password authentication
the reason is that sysbench on CentOS 7 is built against MariaDB 10.2, not MySQL 8.0
even if I will change that and start building packages against MySQL 8.0, I will break sysbench packages for MariaDB users on CentOS 7
whatever I will come up with, that will not solve your issue, because you are installing packages created by Percona, not by me. In which case it would be natural to report the problem to your sysbench vendor of choice :)

utdrmac commented 6 years ago

OK. Understood. But why is sysbench not using the mysql8 library? Shouldn't it use the latest installed client library (/usr/lib64/mysql/libmysqlclient.so.21)? Or is .so.18 "required" by the pacakge?

akopytov commented 6 years ago

sysbench expects a certain ABI from the client library, so it requires a certain SO version (libmysqlclient.so.18). That dependency is satisfied by the compat 8.0 lib. The normal one has SO version 21.

utdrmac commented 4 years ago

@akopytov I removed the percona-sysbench package and I installed from your repo:

# yum --disablerepo=tools-release-x86_64 install sysbench
...
Installing:
 sysbench         x86_64        1.0.20-1.el7         akopytov_sysbench       430 k

# ldd /usr/bin/sysbench | grep mysql
    libmysqlclient.so.18 => /usr/lib64/mysql/libmysqlclient.so.18 (0x00007fa7e9978000)

I installed your package and I still cannot get caching_sha2 support. Is the only solution to compile from scratch?

vjunior1981 commented 4 years ago

@akopytov I removed the percona-sysbench package and I installed from your repo:
# yum --disablerepo=tools-release-x86_64 install sysbench
...
Installing:
 sysbench         x86_64        1.0.20-1.el7         akopytov_sysbench       430 k
# ldd /usr/bin/sysbench | grep mysql
  libmysqlclient.so.18 => /usr/lib64/mysql/libmysqlclient.so.18 (0x00007fa7e9978000)
I installed your package and I still cannot get caching_sha2 support. Is the only solution to compile from scratch?

Use this package: https://lefred.be/wp-content/uploads/2018/09/sysbench-1.0.15-1.el7_.centos.x86_64.rpm

shapled commented 6 months ago

mysql 8.4.0, adding these items to the configure file makes sysbench work.

mysql_native_password           = on
authentication_policy               = mysql_native_password

utdrmac commented 6 months ago

@shapled

As of MySQL 8.0.34, the mysql_native_password authentication plugin is deprecated and subject to removal in a future version of MySQL.

Your workaround, while valid, is not viable long-term. We need proper support for passwords in sysbench.

akopytov / sysbench

mysql 8 caching_sha2_password incompatibility #244