MySQL 8.0.11 auth issue

tmsvr commented 5 years ago

Environment

VerneMQ Version: 1.8.0 (official docker image)
OS: MacOS 10.14.5
Cluster size/standalone: single node, no clustering used

Run command

docker run -p 8884:8884 --link mysql:mysql -e "DOCKER_VERNEMQ_PLUGINS__VMQ_PASSWD=off" -e "DOCKER_VERNEMQ_PLUGINS__VMQ_ACL=off" -e "DOCKER_VERNEMQ_PLUGINS__VMQ_DIVERSITY=on" -e "DOCKER_VERNEMQ_VMQ_DIVERSITY__MYSQL__HOST=projectx" -e "DOCKER_VERNEMQ_VMQ_DIVERSITY__MYSQL__PORT=3306" -e "DOCKER_VERNEMQ_VMQ_DIVERSITY__MYSQL__USER=alma" -e "DOCKER_VERNEMQ_VMQ_DIVERSITY__MYSQL__PASSWORD=alma" -e "DOCKER_VERNEMQ_VMQ_DIVERSITY__MYSQL__DATABASE=projectx" -e "DOCKER_VERNEMQ_VMQ_DIVERSITY__MYSQLACL__FILE=/etc/scripts/mysqlacl.lua" -e "DOCKER_VERNEMQ_LISTENER__SSL__CAFILE=/etc/ssl/ca.crt" -e "DOCKER_VERNEMQ_LISTENER__SSL__CERTFILE=/etc/ssl/server.crt" -e "DOCKER_VERNEMQ_LISTENER__SSL__KEYFILE=/etc/ssl/server.key" -e "DOCKER_VERNEMQ_LISTENER__SSL__DEFAULT=0.0.0.0:8884" -e "DOCKER_VERNEMQ_LISTENER__SSL__DEFAULT__USE_IDENTITY_AS_USERNAME=off" -e "DOCKER_VERNEMQ_LISTENER__SSL__DEFAULT__REQUIRE_CERTIFICATE=off" -v /Users/alma/Desktop/zpmqtt/certs:/etc/ssl -v /Users/alma/Desktop/zpmqtt/scripts:/etc/scripts --name vernemq -d erlio/docker-vernemq:1.8.0

Expected behavior

VMQ Diversity can connect to a MySQL 8.0+ database and authenticate.

Actual behaviour

Authentication fails with the following:

15:55:14.447 [info] enable script for "/etc/scripts/mysqlacl.lua"
15:55:14.493 [error] can't load script "/etc/scripts/mysqlacl.lua" due to {throw,{auth_fail,{error_packet,2,1251,<<"08004">>,"Client does not support authentication protocol requested by server; consider upgrading MySQL client"}}}
15:55:14.495 [error] could not load script "/etc/scripts/mysqlacl.lua" due to {{'EXIT',{{badmatch,{error,{normal,{child,undefined,{vmq_diversity_script_state,1},{vmq_diversity_script_state,start_link,[1,"/etc/scripts/mysqlacl.lua"]},permanent,5000,worker,[vmq_diversity_script_state]}}}},[{vmq_diversity_script_sup_sup,setup_lua_states,2,[{file,"/vernemq-build/apps/vmq_diversity/src/vmq_diversity_script_sup_sup.erl"},{line,97}]},{vmq_diversity_script_sup_sup,start_link,1,[{file,"/vernemq-build/apps/vmq_diversity/src/vmq_diversity_script_sup_sup.erl"},{line,45}]},{supervisor,do_start_child_i,3,[{file,"supervisor.erl"},{line,379}]},{supervisor,do_start_child,2,[{file,"supervisor.erl"},{line,365}]},{supervisor,handle_start_child,2,[{file,"supervisor.erl"},{line,671}]},{supervisor,handle_call,3,[{file,"supervisor.erl"},{line,420}]},{gen_server,try_handle_call,4,[{file,"gen_server.erl"},{line,661}]},{gen_server,handle_msg,6,[{file,"gen_server.erl"},{line,690}]}]}},{child,undefined,{vmq_diversity_script_sup_sup,"/etc/scripts/mysqlacl.lua"},{vmq_diversity_script_sup_sup,start_link,["/etc/scripts/mysqlacl.lua"]},permanent,5000,supervisor,[vmq_diversity_script_sup_sup]}}

Altering the mysql user with the following fixes the problem.

ALTER USER 'alma'@'%' IDENTIFIED WITH mysql_native_password BY 'password'

We have tried to use the feature described here https://github.com/vernemq/vernemq/pull/1023 to no success.

larshesel commented 5 years ago

Hi, thanks for reporting. As the error says then the MySQL client we are using is old and we should upgrade it. We'll have to look into that and which options we have.

tmsvr commented 5 years ago

Thank you very much! Do you have anything in mind regarding the timing?

larshesel commented 5 years ago

I'm afraid I can't say when we'll get to this. We'd likely have to switch to another MySQL client library to make this work and this might be easier said than done. Of course we have to support new MySQL versions out of the box, so we'll get to it at some point.

larshesel commented 5 years ago

Perhaps we can use the https://github.com/mysql-otp/mysql-otp driver instead. Will have to investigate.

joehallaschameleon commented 1 year ago

Just wanted to add that AWS are dropping support for MySQL 5.7 starting October next year so this may become a little more urgent for customers soon.

ioolkos commented 1 year ago

@joehallaschameleon Thanks, it looks like this should be fixed by https://github.com/vernemq/vernemq/commit/93838e09778c8467768b4e5f33520d57740a03c8

Do you see an actual error with MySQL 8.0+?

👉 Thank you for supporting VerneMQ: https://github.com/sponsors/vernemq 👉 Using the binary VerneMQ packages commercially (.deb/.rpm/Docker) requires a paid subscription.

vernemq / vernemq