MySQL_Connector_Arduino
MySQL_Connector_Arduino copied to clipboard
ChuckBell
Issue connecting Arduino MKR1000 through WiFi to cloud Azure MySQL
Hi there, I am trying to run the example in the Arduino MySQL connector library (sketch 'connect_wifi_101') with a MKR1000 using the WiFi101 library and MySQL connector library v 1.2 trying to connect to an Azure cloud mySQL server. Everything seems to run well up until the authentication stage, where I apparently get a mysql_native_password error. This is the printout of the serial monitor from arduino:
I have run out of ideas of how to get around this issue, especially I cannot figure out how to check if mysql_native_password is actually selected on the azure mysql portal. Any help would me really appreciated. Thank you.
Hi.
Yes, unfortunately the connector only supports the native authentication. You can read about it more starting here: https://dev.mysql.com/doc/refman/8.0/en/native-pluggable-authentication.html https://dev.mysql.com/doc/refman/8.0/en/native-pluggable-authentication.html and https://dev.mysql.com/doc/refman/8.0/en/pluggable-authentication.html https://dev.mysql.com/doc/refman/8.0/en/pluggable-authentication.html.
One way to solve the problem is to create a new user specifying the mysql_native_password option per the docs.
For example…
mysql> select user, host, plugin from mysql.user; +------------------+-----------+-----------------------+ | user | host | plugin | +------------------+-----------+-----------------------+ | root | % | mysql_native_password | | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | mysql_native_password | +------------------+-----------+-----------------------+
Notice the first row. I created a special user named root with the native password authentication like this:
mysql> CREATE USER dummy@'%' IDENTIFIED WITH mysql_native_password BY 'secret'; Query OK, 0 rows affected (0.01 sec)
mysql> select user, host, plugin from mysql.user; +------------------+-----------+-----------------------+ | user | host | plugin | +------------------+-----------+-----------------------+ | dummy | % | mysql_native_password | | root | % | mysql_native_password | | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | mysql_native_password | +------------------+-----------+-----------------------+ 6 rows in set (0.00 sec)
On Oct 15, 2021, at 11:52 AM, mprof9 @.***> wrote:
Hi there, I am trying to run the example in the Arduino MySQL connector library (sketch 'connect_wifi_101') with a MKR1000 using the WiFi101 library and MySQL connector library v 1.2 trying to connect to an Azure cloud mySQL server. Everything seems to run well up until the authentication stage, where I apparently get a mysql_native_password error. This is the printout of the serial monitor from arduino:
https://user-images.githubusercontent.com/92594035/137516212-eb19cdd0-5b39-4925-8fca-e97f3e2650d7.png I have run out of ideas of how to get around this issue, especially I cannot figure out how to check if mysql_native_password is actually selected on the azure mysql portal. Any help would me really appreciated. Thank you.
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/178, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYEQABHGN4PFBIH3JO3UHBE45ANCNFSM5GCIOV6A.
Thank you. Actually, from the mysql.user table, the user I am utilizing for connecting has already the mysql_native_password plugin (it is not the sha2_password). I also tried to create a new user as you stated above, but to no avail. I tested different hardware (Ethernet and WiFi) with the same error. I then called a PHP file on a web server with a GET request to make an INSERT into mysql Azure and it worked (by-passing the mySQL connector); so it seems to be an authentication issue with the mySQL connector on an external network residing database.
Ok, what does the SQL command SELECT @@version return?
On Fri, Oct 22, 2021 at 03:18 mprof9 @.***> wrote:
Thank you. Actually, from the mysql.user table, the user I am utilizing for connecting has already the mysql_native_password plugin (it is not the sha2_password). I also tried to create a new user as you stated above, but to no avail. I tested different hardware (Ethernet and WiFi) with the same error. I then called a PHP file on a web server with a GET request to make an INSERT into mysql Azure and it worked (by-passing the mySQL connector); so it seems to be an authentication issue with the mySQL connector on an external network residing database.
— You are receiving this because you commented.
Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/178#issuecomment-949356107, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYF2SCQIZC3G6IIRLPLUIEF35ANCNFSM5GCIOV6A .
Well, it's suspicious because you shouldn't get that error. I suspect there is a PHP or some other script in the middle. I would suggest using the mysql client tool and try to connect from the same subnet. Let me know if you get an error.
Well, it's suspicious because you shouldn't get that error. I suspect there is a PHP or some other script in the middle. I would suggest using the mysql client tool and try to connect from the same subnet using the same credentials. Let me know if you get an error.
On Fri, Oct 22, 2021 at 10:04 mprof9 @.***> wrote:
5.7.32-log
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/178#issuecomment-949663002, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYHCZMYQSZQ3YPTRIVLUIFVQRANCNFSM5GCIOV6A .
Sorry, I don't get exactly what you mean by mysql client tool: I used mySQL WorkBench with any issue whatsoever if this is what you intend.
I also managed to connect with an android app for mySQL using the same credentials.
The MySQL client is named MySQL. It is a command line tool. Workbench will indeed work, but you must create a connection using the same credentials in your Arduino sketch and make sure the PC is connected to the same WiFi subnet.
Regardless, I’d also like to see the output of the following, please. :)
show variables like ‘%version%’
On Fri, Oct 22, 2021 at 10:21 mprof9 @.***> wrote:
Sorry, I don't get exactly what you mean by mysql client tool: I used mySQL WorkBench with any issue whatsoever if this is what you intend.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/178#issuecomment-949677467, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYC37MITSWUNMBDB4ZDUIFXOVANCNFSM5GCIOV6A .
This is what I get from the command line:
I do not have any errors from the mysql command line
Ok, thanks. When I get some time, I’ll setup an Azure MySQL instance and try it myself.
On Fri, Oct 22, 2021 at 10:36 mprof9 @.***> wrote:
This is what I get from the command line: [image: immagine] https://user-images.githubusercontent.com/92594035/138472996-586aa40b-6809-4bf1-b779-87b0a2ae3177.png
I do not have any errors from the mysql command line
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/178#issuecomment-949691093, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYCJQA47G7PZRCI4A5LUIFZHXANCNFSM5GCIOV6A .