search

ChuckBell / MySQL_Connector_Arduino

Database connector library for using MySQL with your Arduino projects.
332 stars 133 forks source link

STM32, W5500 Ethernet: ERROR: Class requires connected server #146

Closed aaroncake closed 2 years ago

aaroncake commented 4 years ago

Hello!

Thank you for this excellent library. I have had it in use several months in an energy monitor project, posting readings to a database every 2 seconds, which is then read by Home Assistant. I decided to upgrade the energy monitor with more channels and an LCD screen so switched from an Uno to an STM32 (Blue Pill, STM32F103C8T6). Adjusted the code as necessary (pin numbers, etc.) and compiled. Except, it would not connect to the database.

Went back to the basics with the basic insert example: ` Created by: Dr. Charles A. Bell */

include

include

include

byte mac_addr[] = { 0xDE, 0xAD, 0x66, 0xEF, 0xFE, 0xED };

IPAddress server_addr(192,168,107,11); // IP of the MySQL server here char user[] = "energymon"; // MySQL user login username char password[] = "energymon"; // MySQL user login password

// Sample query char INSERT_SQL[] = "INSERT INTO test_arduino.hello_arduino (message) VALUES ('Hello, Arduino!')";

EthernetClient client; MySQL_Connection conn((Client *)&client);

void setup() { Serial.begin(115200); Serial.println("Start"); Ethernet.init(PA4); Ethernet.begin(mac_addr);

Serial.print(F("ETHERNET: Success. IP Addresss: ")); Serial.println(Ethernet.localIP());

Serial.println("Connecting..."); if (conn.connect(server_addr, 3306, user, password)) { delay(1000); } else Serial.println("Connection failed."); }

void loop() { delay(2000);

Serial.println("Recording data.");

// Initiate the query class instance MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); // Execute the query cur_mem->execute(INSERT_SQL); // Note: since there are no results, we do not need to read any data // Deleting the cursor also frees up memory used delete cur_mem; }`

Unfortunately, it hangs on connection to the database. I receive:

ETHERNET: Success. IP Addresss: 192.168.137.30 Connecting... ...trying... ...got: 0 retrying... ...trying...

I added some Serial.print debug statements in the MySQL_Connection::connect right before connected = client->connect(server, port); as a bit of a sanity check, and saw the real error:

192.168.107.113306...got: 0 retrying... ...trying... 192.168.107.113306...got: 0 retrying... Connection failed. Recording data. ERROR: Class requires connected server. Recording data. ERROR: Class requires connected server. Recording data. ERROR: Class requires connected server. Recording data. ERROR: Class requires connected server.

So somehow the client class isn't connecting.

As another sanity check, I used the Arduino example "Telnet Client" sketch to connect to my MySQL server. It made the connection and spat back the handshake from MySQL. So we know fundamentally that the ethernet.h and client classes work on the STM32 (as expected, Ethernet use under STM32 in the Arduino environment is fairly standard, I use PubSubClient and others).

I tried adding #include in MySQL_packet.h as a hunch, same difference. And also including ethernet.h outside of the preprocessor #ifdef without success.

Any suggestions for further troubleshooting? Any help would be much appreciated. Incidentally, this is my first ever GitHub post.

ReinhardDaemon commented 4 years ago

Hi aaroncake,

What I see at a first glance is:

192.168.107.113306...got: 0 retrying...
...trying...

should read (in my opinion):

192.168.107.11.3306...got: 0 retrying...
...trying...

there must be a "." ("point") between the last part of the IP number and the port number (which is usually 3306).

aaroncake commented 4 years ago

ReinhardDaemon,

Thank you for the reply. However I'm unsure why you would say there should be a dot between the last part of the IP number and the port.

That debug information is generated by two Serial.print statements I added to MySQL_Connection::connect in MySQL_Connection.cpp as below:

Serial.println("...trying..."); connected = client->connect(server, port); Serial.print(server); Serial.print(port); if (connected != SUCCESS) { Serial.print("...got: "); Serial.print(connected); Serial.println(" retrying...");

There is no dot within the print statements, thus no dot will be printed.

What seems to be failing is the line connected = client->connect(server, port); for reasons unknown/non-obvious to me. Looking through the code, I can't see a reason why the client object isn't passed to the library.

ChuckBell commented 4 years ago

Please check the troubleshooting section in the wiki. You need to verify the user and password is permitted to connect to the server from the IP address. Do this without using your Arduino sketch - another pc perhaps.

On Fri, Jun 26, 2020 at 09:59 aaroncake notifications@github.com wrote:

ReinhardDaemon,

Thank you for the reply. However I'm unsure why you would say there should be a dot between the last part of the IP number and the port.

That debug information is generated by two Serial.print statements I added to MySQL_Connection::connect in MySQL_Connection.cpp as below:

{ Serial.println("...trying..."); connected = client->connect(server, port); Serial.print(server); Serial.print(port); if (connected != SUCCESS) { Serial.print("...got: "); Serial.print(connected); Serial.println(" retrying...");

There is no dot within the print statements, thus no dot will be printed.

What seems to be failing is the line connected = client->connect(server, port); for reasons unknown/non-obvious to me. Looking through the code, I can't see a reason why the client object isn't passed to the library.

— 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/146#issuecomment-650195227, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYFASPNR4FK36ZN6KLLRYSSUNANCNFSM4OI5JY2A .

aaroncake commented 4 years ago

Dr. Charles,

Thank you for the reply. I should have mentioned in my initial post that I have already:

ChuckBell commented 4 years ago

Perfect! That eliminates a host of possible issues. One more question. Ok, two... What version of MySQL are you using and which version of the connector?

On Fri, Jun 26, 2020 at 12:08 aaroncake notifications@github.com wrote:

Dr. Charles,

Thank you for the reply. I should have mentioned in my initial post that I have already:

-

read the entire Wiki

confirmed connectivity from any PC/IP via that username and password via MySQL Workbench

confirmed connectivity via Telnet to 192.168.107.11 port 3306 via both PC Telnet client and the Arduino Telnet sketch example

Confirmed user energymon has access from % hosts, and full schema privileges to test_arduino

Confirmed that my code running on the original Uno is able to connect to the 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/146#issuecomment-650261546, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYEOIP5R7BV5LGLJYI3RYTBWRANCNFSM4OI5JY2A .

ChuckBell commented 4 years ago

Along with that, consider backing out parts of your sketch until it starts to work. It is possible you have a conflict among the libraries you added. I’ve seen that and adding more code can create an OOM issue.

On Fri, Jun 26, 2020 at 13:13 Charles Bell drcharlesbell@gmail.com wrote:

Perfect! That eliminates a host of possible issues. One more question. Ok, two... What version of MySQL are you using and which version of the connector?

On Fri, Jun 26, 2020 at 12:08 aaroncake notifications@github.com wrote:

Dr. Charles,

Thank you for the reply. I should have mentioned in my initial post that I have already:

-

read the entire Wiki

confirmed connectivity from any PC/IP via that username and password via MySQL Workbench

confirmed connectivity via Telnet to 192.168.107.11 port 3306 via both PC Telnet client and the Arduino Telnet sketch example

Confirmed user energymon has access from % hosts, and full schema privileges to test_arduino

Confirmed that my code running on the original Uno is able to connect to the 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/146#issuecomment-650261546, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYEOIP5R7BV5LGLJYI3RYTBWRANCNFSM4OI5JY2A .

aaroncake commented 4 years ago

Dr. Charles,

I'm using the latest version of the connector, 1.2 I believe.

MySQL version 5.7.30-0ubuntu0.18.04.1 according to the "basic insert" example code run on an Arduino Uno.

When I found my code didn't work, I switched to the "basic insert" example code (only modification being adding Ethernet.init for the CS pin on my STM32, and a serial print of the IP address) as the first troubleshooting step. I can flash that code to a Uno with W5500 shield and it works as expected:

ETHERNET: Success. IP Addresss: 192.168.137.28 Connecting... ...trying... Connected to server version 5.7.30-0ubuntu0.18.04.1 Recording data. Recording data. Recording data. Recording data. Recording data. Recording data. Recording data. Recording data.

However flashing that same code to the STM32 w/W5500 just results in the IP address being printed as expected, then a hang at "Connecting......trying...".

Probably not a memory issue as the STM32 has 20K of RAM (ignore the 32K program space, I had the wrong settings when flashing, device is actually 128K):

Sketch uses 25280 bytes (77%) of program storage space. Maximum is 32768 bytes. Global variables use 1640 bytes (16%) of dynamic memory, leaving 8600 bytes for local variables. Maximum is 10240 bytes.

Other Ethernet example work fine such as the Telnet Client, DHCP Address Printer, WebClient, etc. Have also used this combination of hardware with PubSubClient.

Thank you for your continued assistance.

ChuckBell commented 4 years ago

Sounds like an issue with the Ethernet class for the STM32. Do you have another shield or WiFi module you could try? One that uses the 5100 chip maybe as a test?

On Jun 27, 2020, at 11:34 AM, aaroncake notifications@github.com wrote:

Dr. Charles,

I'm using the latest version of the connector, 1.2 I believe.

MySQL version 5.7.30-0ubuntu0.18.04.1 according to the "basic insert" example code run on an Arduino Uno.

When I found my code didn't work, I switched to the "basic insert" example code (only modification being adding Ethernet.init for the CS pin on my STM32, and a serial print of the IP address) as the first troubleshooting step. I can flash that code to a Uno with W5500 shield and it works as expected:

ETHERNET: Success. IP Addresss: 192.168.137.28 Connecting... ...trying... Connected to server version 5.7.30-0ubuntu0.18.04.1 Recording data. Recording data. Recording data. Recording data. Recording data. Recording data. Recording data. Recording data.

However flashing that same code to the STM32 w/W5500 just results in the IP address being printed as expected, then a hang at "Connecting......trying...".

Probably not a memory issue as the STM32 has 20K of RAM (ignore the 32K program space, I had the wrong settings when flashing, device is actually 128K):

Sketch uses 25280 bytes (77%) of program storage space. Maximum is 32768 bytes. Global variables use 1640 bytes (16%) of dynamic memory, leaving 8600 bytes for local variables. Maximum is 10240 bytes.

Other Ethernet example work fine such as the Telnet Client, DHCP Address Printer, WebClient, etc. Have also used this combination of hardware with PubSubClient.

Thank you for your continued assistance.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/146#issuecomment-650575796, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYBJRDKCT63JLQORQC3RYYGPFANCNFSM4OI5JY2A.

aaroncake commented 4 years ago

Hello,

The only W5100s I have are on Arduino shields so I connected one up to an STM32 however was unable to obtain an IP address with instead the library complaining the W5100 couldn't be found (even though it reset, and established a link). After some time spent, troubleshooting, swapping out jumper wires, connecting up the reset pin so it doesn't float, etc. I just sort of gave up. The overall project has just been dragging on far too long (for reasons unrelated to your library but instead due to defective hardware, scope creep and other factors) that I'm tired of putting hours into it so I'm switching to MQTT for telemetry. And likely ditching the STM32 (which was only chosen because I had a bunch of them and they have enough memory) for an Atmel (1284 likely) because Arduino library support for the SMT32 seems spotty at best.

Thank you for all your help!

ChuckBell commented 2 years ago

Closed due to inactivity. Please open a new ticket if this is still relevant.