Title: Arduino unable to connect to database · Issue #237 · ChuckBell/MySQL_Connector_Arduino · GitHub
Open Graph Title: Arduino unable to connect to database · Issue #237 · ChuckBell/MySQL_Connector_Arduino
X Title: Arduino unable to connect to database · Issue #237 · ChuckBell/MySQL_Connector_Arduino
Description: As recommended in the wiki documentation, I tested that I can connect to the database from a different system: paul@paul-desktop:~$ sudo mysql -h192.168.1.201 -ugranary_sensors -ppassword --port=3306 Welcome to the MariaDB monitor. Comma...
Open Graph Description: As recommended in the wiki documentation, I tested that I can connect to the database from a different system: paul@paul-desktop:~$ sudo mysql -h192.168.1.201 -ugranary_sensors -ppassword --port=33...
X Description: As recommended in the wiki documentation, I tested that I can connect to the database from a different system: paul@paul-desktop:~$ sudo mysql -h192.168.1.201 -ugranary_sensors -ppassword --port=33...
Opengraph URL: https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/237
X: @github
Domain: github.com
{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Arduino unable to connect to database","articleBody":"As recommended in the wiki documentation, I tested that I can connect to the database from a different system:\r\n```\r\npaul@paul-desktop:~$ sudo mysql -h192.168.1.201 -ugranary_sensors -ppassword --port=3306\r\nWelcome to the MariaDB monitor. Commands end with ; or \\g.\r\nYour MariaDB connection id is 122\r\nServer version: 10.11.5-MariaDB-log Alpine Linux\r\n\r\nCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nMariaDB [(none)]> select * from granary_sensors.granary_sensors\r\n -> ;\r\nERROR 1142 (42000): SELECT command denied to user 'granary_sensors'@'paul-desktop.lan' for table `granary_sensors`.`granary_sensors`\r\nMariaDB [(none)]> insert into granary_sensors.granary_sensors (sensor_name, sensor_value) values ('tt', -999);\r\nQuery OK, 1 row affected (0.001 sec)\r\n\r\nMariaDB [(none)]> exit\r\nBye\r\n```\r\nThe above is as expected. I have granted permission to the user granary_sensors to insert records into only that one table, and nothing else. That is why the select command fails but the insert command succeeds. So far, great.\r\n\r\nNext, I took the example sketch from this library, amended it with my desired details and uploaded it to a Wemos D1 Mini Pro (an ESP8266 board):\r\n\r\n```\r\n/*\r\n MySQL Connector/Arduino Example : connect by wifi\r\n\r\n This example demonstrates how to connect to a MySQL server from an\r\n Arduino using an Arduino-compatible Wifi shield. Note that \"compatible\"\r\n means it must conform to the Ethernet class library or be a derivative\r\n with the same classes and methods.\r\n \r\n For more information and documentation, visit the wiki:\r\n https://github.com/ChuckBell/MySQL_Connector_Arduino/wiki.\r\n\r\n INSTRUCTIONS FOR USE\r\n\r\n 1) Change the address of the server to the IP address of the MySQL server\r\n 2) Change the user and password to a valid MySQL user and password\r\n 3) Change the SSID and pass to match your WiFi network\r\n 4) Connect a USB cable to your Arduino\r\n 5) Select the correct board and port\r\n 6) Compile and upload the sketch to your Arduino\r\n 7) Once uploaded, open Serial Monitor (use 115200 speed) and observe\r\n\r\n If you do not see messages indicating you have a connection, refer to the\r\n manual for troubleshooting tips. The most common issues are the server is\r\n not accessible from the network or the user name and password is incorrect.\r\n\r\n Created by: Dr. Charles A. Bell\r\n*/\r\n#include \u003cESP8266WiFi.h> // Use this for WiFi instead of Ethernet.h\r\n#include \u003cMySQL_Connection.h>\r\n#include \u003cMySQL_Cursor.h>\r\n\r\nIPAddress server_addr(192,168,1,201); // IP of the MySQL *server* here\r\nchar user[] = \"granary_sensors\"; // MySQL user login username\r\nchar password[] = \"password\"; // MySQL user login password\r\n\r\n// Sample query\r\nchar INSERT_SQL[] = \"INSERT INTO granary_sensors.granary_sensors (sensor_name, sensor_value) VALUES ('TT', -999)\";\r\n\r\n// WiFi card example\r\nchar ssid[] = \"granary\"; // your SSID\r\nchar pass[] = \"password\"; // your SSID Password\r\n\r\nWiFiClient client; // Use this for WiFi instead of EthernetClient\r\nMySQL_Connection conn(&client);\r\nMySQL_Cursor* cursor;\r\n\r\nvoid setup()\r\n{\r\n Serial.begin(115200);\r\n while (!Serial); // wait for serial port to connect. Needed for Leonardo only\r\n\r\n // Begin WiFi section\r\n Serial.printf(\"\\nConnecting to %s\", ssid);\r\n WiFi.begin(ssid, pass);\r\n while (WiFi.status() != WL_CONNECTED) {\r\n delay(500);\r\n Serial.print(\".\");\r\n }\r\n\r\n // print out info about the connection:\r\n Serial.println(\"\\nConnected to network\");\r\n Serial.print(\"My IP address is: \");\r\n Serial.println(WiFi.localIP());\r\n\r\n Serial.print(\"Connecting to SQL... \");\r\n if (conn.connect(server_addr, 3306, user, password))\r\n Serial.println(\"OK.\");\r\n else\r\n Serial.println(\"FAILED.\");\r\n \r\n // create MySQL cursor object\r\n cursor = new MySQL_Cursor(&conn);\r\n}\r\n\r\nvoid loop()\r\n{\r\n if (conn.connected())\r\n cursor->execute(INSERT_SQL);\r\n\r\n delay(5000);\r\n}\r\n```\r\n\r\nI get the following error in Serial Monitor:\r\n```\r\n14:34:55.908 -> ..............\r\n14:35:03.128 -> Connected to network\r\n14:35:03.128 -> My IP address is: 192.168.1.76\r\n14:35:03.128 -> Connecting to SQL... ...trying...\r\n14:35:03.460 -> Error: 88 = Access denied for user 'granary_sensors'@'ESP-D091A2.lan' (using password: YES).\r\n14:35:03.460 -> FAILED.\r\n```\r\n\r\nCan anyone please help me spot my error, or suggest next steps to diagnose?\r\n\r\nArduino IDE: 1.8.19\r\nMySQL_Connector_Arduino library version installed: 1.2.0\r\nESP8266 board core version: 3.1.2\r\n\r\nPermissions for the granary_sensors user:\r\n\r\n```\r\npaul@paul-desktop:~$ mysql -uroot -ppassword -h192.168.1.201 --port=3306\r\nWelcome to the MariaDB monitor. Commands end with ; or \\g.\r\nYour MariaDB connection id is 150\r\nServer version: 10.11.5-MariaDB-log Alpine Linux\r\n\r\nCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nMariaDB [(none)]> SHOW GRANTS FOR granary_sensors;\r\n+----------------------------------------------------------------------------------------------------------------+\r\n| Grants for granary_sensors@% |\r\n+----------------------------------------------------------------------------------------------------------------+\r\n| GRANT USAGE ON *.* TO `granary_sensors`@`%` IDENTIFIED BY PASSWORD '*733B51F92EB06BE45A3761D5B53B881709A82C03' |\r\n| GRANT INSERT ON `granary_sensors`.`granary_sensors` TO `granary_sensors`@`%` WITH GRANT OPTION |\r\n+----------------------------------------------------------------------------------------------------------------+\r\n2 rows in set (0.001 sec)\r\n\r\n```","author":{"url":"https://github.com/PaulRB","@type":"Person","name":"PaulRB"},"datePublished":"2024-09-01T15:45:33Z","url":"https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/237"}
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@b8488ee2c322 |
| route-pattern | /_view_fragments/issues/show/:user_id/:repository/:id/issue_layout(.:format) |
| route-controller | voltron_issues_fragments |
| route-action | issue_layout |
| fetch-nonce | v2:c0b3b14c-d8da-04f3-7256-968e242fac51 |
| current-catalog-service-hash | 81bb79d38c15960b92d99bca9288a9108c7a47b18f2423d0f6438c5b7bcd2114 |
| request-id | A31E:3AC034:5B9A5A:7B777A:6B003411 |
| html-safe-nonce | c291965d855a3e0043fe837aa9142a3a93ea844135aac63aec1c8cecdeb60c69 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJBMzFFOjNBQzAzNDo1QjlBNUE6N0I3NzdBOjZCMDAzNDExIiwidmlzaXRvcl9pZCI6IjU0MDYzNDY2NjY1MTEwNDI3MyIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9 |
| visitor-hmac | 9445d853dff4f2a48deb13b95af3c4068fdea0cae051d2e40541e967d87221e1 |
| hovercard-subject-tag | issue:2499548427 |
| github-keyboard-shortcuts | repository,issues,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/_view_fragments/issues/show/ChuckBell/MySQL_Connector_Arduino/237/issue_layout |
| twitter:image | https://opengraph.githubassets.com/ca918ba1a4debdfd06de658c011a090ca6e7c38e2597f649b39f95943647907c/ChuckBell/MySQL_Connector_Arduino/issues/237 |
| twitter:card | summary_large_image |
| og:image | https://opengraph.githubassets.com/ca918ba1a4debdfd06de658c011a090ca6e7c38e2597f649b39f95943647907c/ChuckBell/MySQL_Connector_Arduino/issues/237 |
| og:image:alt | As recommended in the wiki documentation, I tested that I can connect to the database from a different system: paul@paul-desktop:~$ sudo mysql -h192.168.1.201 -ugranary_sensors -ppassword --port=33... |
| og:image:width | 1200 |
| og:image:height | 600 |
| og:site_name | GitHub |
| og:type | object |
| og:author:username | PaulRB |
| hostname | github.com |
| expected-hostname | github.com |
| None | 7d0829bbde9e48b2486cb5dd6d147896eb01d32134627091f818958a935ef3f0 |
| turbo-cache-control | no-preview |
| go-import | github.com/ChuckBell/MySQL_Connector_Arduino git https://github.com/ChuckBell/MySQL_Connector_Arduino.git |
| octolytics-dimension-user_id | 8201184 |
| octolytics-dimension-user_login | ChuckBell |
| octolytics-dimension-repository_id | 49725805 |
| octolytics-dimension-repository_nwo | ChuckBell/MySQL_Connector_Arduino |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 49725805 |
| octolytics-dimension-repository_network_root_nwo | ChuckBell/MySQL_Connector_Arduino |
| turbo-body-classes | logged-out env-production page-responsive |
| disable-turbo | false |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | f3f3a43ca095cdea3abbb394112e971f9fdb94fa |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
| react-app-name | issues |
Links:
Viewport: width=device-width