Closed chakthy closed 4 years ago
What version of MySQL are you using?
On Fri, Jan 11, 2019 at 11:16 chakthy notifications@github.com wrote:
I have been trying to use some of the examples to establish a connection with MySQL server. So far the WiFi connection is established, but once it goes to the "conn.connect(server_addr, 3306, user, password)" it seems to hang there. Memory doesn't seem to be an issue: Sketch uses 16612 bytes (33%) of program storage space. Maximum is 49152 bytes. Global variables use 683 bytes (11%) of dynamic memory, leaving 5461 bytes for local variables. Maximum is 6144 bytes.
MySQL server is running on a virtual machine inside my Mac. I have tested the mysql connection between the Mac and the virtual machine (both using different IP addresses). I added code on the Arduino to ping the virtual machine and it gets detected. I have tried another code to connect directly into the server that has the databases and it works, but of course this doesn't connect to MySQL so I tried to use the conn.connect() afterwards but it didn't work either. The connection doesn't fail, and the Arduino remains connected to the network. But the code doesn't go to the next line after the conn.connect(). Not sure if this is related to slight differences on the Arduino WiFi rev.2 Any ideas on how to debug the problem?
This is the code I'm using
include
include
include
include
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
char ssid[] = “my_network"; // your network SSID (name) char pass[] = “my_pass"; // your network password (use for WPA, or use as key for WEP) int status = WL_IDLE_STATUS; // the Wifi radio's status
IPAddress server_addr(X,X,X,X); // IP of the MySQL server here char user[] = “my_user"; char password[] = “my_pass2";
// Sample query char INSERT_SQL[] = "INSERT INTO RFID.log (card_num) VALUES ('2019')";
WiFiClient client; // Use this for WiFi instead of EthernetClient MySQL_Connection conn((Client )&client); MySQL_Cursor cursor;
void setup() { //Initialize serial and wait for port to open: Serial.begin(115200); while (!Serial) { ; // wait for serial port to connect. Needed for native USB port only }
// check for the WiFi module: if (WiFi.status() == WL_NO_MODULE) { Serial.println("Communication with WiFi module failed!"); // don't continue while (true); }
String fv = WiFi.firmwareVersion(); if (fv < "1.0.0") { Serial.println("Please upgrade the firmware"); }
// attempt to connect to Wifi network: while (status != WL_CONNECTED) { Serial.print("Attempting to connect to WPA SSID: "); Serial.println(ssid); // Connect to WPA/WPA2 network: status = WiFi.begin(ssid, pass);
// wait 10 seconds for connection:
delay(10000);
}
// you're connected now, so print out the data: Serial.print("You're connected to the network"); printCurrentNet(); printWifiData(); delay(5000);
Serial.println("Connecting to SQL..."); if (conn.connect(server_addr, 3306, user, password)) { delay(1000); Serial.println("Connected"); } else Serial.println("Connection failed."); conn.close();
}
void loop() { // check the network connection once every 10 seconds: delay(10000); printCurrentNet();
Serial.println("Recording data."); if (conn.connected()) cursor->execute(INSERT_SQL);
delay(5000);
// 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;
}
void printWifiData() { // print your board's IP address: IPAddress ip = WiFi.localIP(); Serial.print("IP Address: "); Serial.println(ip);
// print your MAC address: byte mac[6]; WiFi.macAddress(mac); Serial.print("MAC address: "); printMacAddress(mac); }
void printCurrentNet() { // print the SSID of the network you're attached to: Serial.print("SSID: "); Serial.println(WiFi.SSID());
// print the MAC address of the router you're attached to: byte bssid[6]; WiFi.BSSID(bssid); Serial.print("BSSID: "); printMacAddress(bssid);
// print the received signal strength: long rssi = WiFi.RSSI(); Serial.print("signal strength (RSSI):"); Serial.println(rssi);
// print the encryption type: byte encryption = WiFi.encryptionType(); Serial.print("Encryption Type:"); Serial.println(encryption, HEX); Serial.println(); }
void printMacAddress(byte mac[]) { for (int i = 5; i >= 0; i--) { if (mac[i] < 16) { Serial.print("0"); } Serial.print(mac[i], HEX); if (i > 0) { Serial.print(":"); } } Serial.println(); }
— 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/80, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4MAFvZnYKyn_I4ynwxiUQxICQSflks5vCLjMgaJpZM4Z7wSh .
Check firewalls too to ensure the port is open and double check the IP address.
Also, try using the MySQL client from another machine. That’ll tell you if you have networking issues.
On Fri, Jan 11, 2019 at 18:33 Charles Bell drcharlesbell@gmail.com wrote:
What version of MySQL are you using?
On Fri, Jan 11, 2019 at 11:16 chakthy notifications@github.com wrote:
I have been trying to use some of the examples to establish a connection with MySQL server. So far the WiFi connection is established, but once it goes to the "conn.connect(server_addr, 3306, user, password)" it seems to hang there. Memory doesn't seem to be an issue: Sketch uses 16612 bytes (33%) of program storage space. Maximum is 49152 bytes. Global variables use 683 bytes (11%) of dynamic memory, leaving 5461 bytes for local variables. Maximum is 6144 bytes.
MySQL server is running on a virtual machine inside my Mac. I have tested the mysql connection between the Mac and the virtual machine (both using different IP addresses). I added code on the Arduino to ping the virtual machine and it gets detected. I have tried another code to connect directly into the server that has the databases and it works, but of course this doesn't connect to MySQL so I tried to use the conn.connect() afterwards but it didn't work either. The connection doesn't fail, and the Arduino remains connected to the network. But the code doesn't go to the next line after the conn.connect(). Not sure if this is related to slight differences on the Arduino WiFi rev.2 Any ideas on how to debug the problem?
This is the code I'm using
include
include
include
include
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
char ssid[] = “my_network"; // your network SSID (name) char pass[] = “my_pass"; // your network password (use for WPA, or use as key for WEP) int status = WL_IDLE_STATUS; // the Wifi radio's status
IPAddress server_addr(X,X,X,X); // IP of the MySQL server here char user[] = “my_user"; char password[] = “my_pass2";
// Sample query char INSERT_SQL[] = "INSERT INTO RFID.log (card_num) VALUES ('2019')";
WiFiClient client; // Use this for WiFi instead of EthernetClient MySQL_Connection conn((Client )&client); MySQL_Cursor cursor;
void setup() { //Initialize serial and wait for port to open: Serial.begin(115200); while (!Serial) { ; // wait for serial port to connect. Needed for native USB port only }
// check for the WiFi module: if (WiFi.status() == WL_NO_MODULE) { Serial.println("Communication with WiFi module failed!"); // don't continue while (true); }
String fv = WiFi.firmwareVersion(); if (fv < "1.0.0") { Serial.println("Please upgrade the firmware"); }
// attempt to connect to Wifi network: while (status != WL_CONNECTED) { Serial.print("Attempting to connect to WPA SSID: "); Serial.println(ssid); // Connect to WPA/WPA2 network: status = WiFi.begin(ssid, pass);
// wait 10 seconds for connection:
delay(10000);
}
// you're connected now, so print out the data: Serial.print("You're connected to the network"); printCurrentNet(); printWifiData(); delay(5000);
Serial.println("Connecting to SQL..."); if (conn.connect(server_addr, 3306, user, password)) { delay(1000); Serial.println("Connected"); } else Serial.println("Connection failed."); conn.close();
}
void loop() { // check the network connection once every 10 seconds: delay(10000); printCurrentNet();
Serial.println("Recording data."); if (conn.connected()) cursor->execute(INSERT_SQL);
delay(5000);
// 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;
}
void printWifiData() { // print your board's IP address: IPAddress ip = WiFi.localIP(); Serial.print("IP Address: "); Serial.println(ip);
// print your MAC address: byte mac[6]; WiFi.macAddress(mac); Serial.print("MAC address: "); printMacAddress(mac); }
void printCurrentNet() { // print the SSID of the network you're attached to: Serial.print("SSID: "); Serial.println(WiFi.SSID());
// print the MAC address of the router you're attached to: byte bssid[6]; WiFi.BSSID(bssid); Serial.print("BSSID: "); printMacAddress(bssid);
// print the received signal strength: long rssi = WiFi.RSSI(); Serial.print("signal strength (RSSI):"); Serial.println(rssi);
// print the encryption type: byte encryption = WiFi.encryptionType(); Serial.print("Encryption Type:"); Serial.println(encryption, HEX); Serial.println(); }
void printMacAddress(byte mac[]) { for (int i = 5; i >= 0; i--) { if (mac[i] < 16) { Serial.print("0"); } Serial.print(mac[i], HEX); if (i > 0) { Serial.print(":"); } } Serial.println(); }
— 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/80, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4MAFvZnYKyn_I4ynwxiUQxICQSflks5vCLjMgaJpZM4Z7wSh .
Ver 14.14 Dist 5.7.24 for linux The connection between the physical OS machine and the virtual machine using the terminal works fine to establish a connection with mysql using "mysql -h IP -uuser -p". I also connected with an app (mysql) from my iPhone to the mysql server and there weren't any error messages and I can do queries. The only errors on the mysql.log on the linux server are these two: [Warning] IP address '10.0.0.19' could not be resolved: Name or service not known [Note] Got timeout reading communication packets I have an older Mac computer but I'm struggling to set it up to be able to use the mysql command and connect through the terminal.
You appear to be using a non-Oracle distribution. The connector is not guaranteed to work with non-Oracle distributions. In fact, it doesn’t work with certain forks. Please install a genuine version of MySQL from Oracle and try again.
On Sat, Jan 12, 2019 at 10:20 chakthy notifications@github.com wrote:
Ver 14.14 Dist 5.7.24 for linux The connection between the physical OS machine and the virtual machine using the terminal works fine to establish a connection with mysql using "mysql -h IP -uuser -p". I also connected with an app (mysql) from my iPhone to the mysql server and there weren't any error messages and I can do queries. The only errors on the mysql.log on the linux server are these two: [Warning] IP address '10.0.0.19' could not be resolved: Name or service not known [Note] Got timeout reading communication packets I have an older Mac computer but I'm struggling to set it up to be able to use the mysql command and connect through the terminal.
— You are receiving this because you commented.
Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-453755668, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4O7lVUWk9oQ2cTKZ7aW87zDmxR2yks5vCf1LgaJpZM4Z7wSh .
The mysql that I installed is from Oracle, it is the community community edition. It should not be that different, right?
Ok. If it was downloaded from Oracle then it should work. :)
Now, you should also check permissions on the user account. Be sure the user you are using is permitted to connect from your Arduino.
On Sat, Jan 12, 2019 at 13:26 chakthy notifications@github.com wrote:
The mysql that I installed is from Oracle, it is the community community edition. It should not be that different, right?
— You are receiving this because you commented.
Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-453770294, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4FWKXlERnnjlS0ouSynTX25gtUWtks5vCijtgaJpZM4Z7wSh .
A few more suggestions.
Be sure to use the IP address and port when using the MySQL client. Or, simply try connecting from a different machine (not sure if you’ve done that yet).
Be sure your WiFi card is working by using the sample WiFi sketches to ensure it can connect to the internet.
Finally, you can try turning on debug in the connector and post the output.
If you want, you can contact me directly and send me your sketch with the values for your system as well as the result of using the MySQL client and any screen captures.
d r c h a r l e s b e l l at g m a i l dot c o m
On Jan 12, 2019, at 1:26 PM, chakthy notifications@github.com wrote:
The mysql that I installed is from Oracle, it is the community community edition. It should not be that different, right?
— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.
I used the WiFi sketch example and it connects to the network. I'm able to ping from the Arduino to the mysql server. I also connected from my physical Mac computer to the virtual machine, so at least there are no remote access restrictions due to the binding address on the local machine. I installed the mysql-server using the apt, so far from the documentation it is for the ubuntu and Debian distributions. How do I enable the debug on the connector?
Well, the easiest, old-school way is to insert print statements in the likely place where the hang occurs. In this case, I'm confident it's in the connect().
So this:
boolean MySQL_Connection::connect(IPAddress server, int port, char user, char password) { int connected = 0; int i = -1;
// Retry up to MAX_CONNECT_ATTEMPTS times 1 second apart. do { delay(1000); connected = client->connect(server, port); Serial.println("attempting connect") i++; } while (i < MAX_CONNECT_ATTEMPTS && !connected);
if (connected) { Serial.Println("Read packet 1") read_packet(); Serial.Println("Parse handshake") parse_handshake_packet(); Serial.Println("Send auth") send_authentication_packet(user, password); Serial.Println("Read packet 2") read_packet(); if (check_ok_packet() != 0) { parse_error_packet(); return false; } show_error(CONNECTED); Serial.println(server_version); free(server_version); // don't need it anymore return true; } return false; }
After adding that and recompiling and reuploading, you should see these print statements appear in the serial monitor. Locate the last one before the hang then after the next operation, add print_packet().
For example, if the code hangs on the first read_packet(), add the method there like this:
... Serial.Println("Read packet 1") read_packet(); print_packet(); ...
The print_packet() method prints out the data read from the server, which can help us determine if there is an issue in transit.
All of that said, I think you should consider trying a new sketch that has only your WiFi code and the connect in it. See the examples to base your attempt. It is remotely possible that one of the libraries you're using is interfering with the connector. This can occur if the library uses the same pins as the WiFi shield.
BTW, which Arduino are you using?
Dr. Bell
On 1/12/19 10:27 PM, chakthy wrote:
I used the WiFi sketch example and it connects to the network. I'm able to ping from the Arduino to the mysql server. I also connected from my physical Mac computer to the virtual machine, so at least there are no remote access restrictions due to the binding address on the local machine. I installed the mysql-server using the apt, so far from the documentation it is for the ubuntu and Debian distributions. How do I enable the debug on the connector?
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-453798884, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4LHIcKIevIZK8V3rbbwSbWLR6hYeks5vCqecgaJpZM4Z7wSh.
So I had the other laptop connecting to the virtual mysql server. It took me some time because it is older and the mysql command was not working. It looks like there are no restrictions from the server to connect. I also reinstalled mysql on a new server downloading the files from the mysql website, just to make sure that the mysql-community-server was not the issue as you mentioned.
I tested the WiFi connection without the code for mysql and it works without issue. I tried to connect again using the mysql code but no luck. The error I get on the error.log is just a warning for not being able to resolve the Arduino IP address. I'm still pending to add the debug code and retest. I'll send you the details. Thanks for the support
One more thing to check: make sure you’re using the native authentication method and not the newer sha2 method.
On Tue, Jan 15, 2019 at 17:10 chakthy notifications@github.com wrote:
So I had the other laptop connecting to the virtual mysql server. It took me some time because it is older and the mysql command was not working. It looks like there are no restrictions from the server to connect. I also reinstalled mysql on a new server downloading the files from the mysql website, just to make sure that the mysql-community-server was not the issue as you mentioned.
I tested the WiFi connection without the code for mysql and it works without issue. I tried to connect again using the mysql code but no luck. The error I get on the error.log is just a warning for not being able to resolve the Arduino IP address. I'm still pending to add the debug code and retest. I'll send you the details. Thanks for the support
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-454571133, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4JHfcfyL6dimg_VnpRs1BappeM4_ks5vDlHQgaJpZM4Z7wSh .
I'm using the Arduino wifi rev2
Ok, but which Arduino board?
On Tue, Jan 15, 2019 at 17:37 chakthy notifications@github.com wrote:
I'm using the Arduino wifi rev2
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-454578990, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4J2i5k4YvjX2ndu_NFfX6Ooe2JsTks5vDlgsgaJpZM4Z7wSh .
That is the new board https://store.arduino.cc/usa/arduino-uno-wifi-rev2
Ah. I was looking for the UNO part. ;)
On Tue, Jan 15, 2019 at 18:00 chakthy notifications@github.com wrote:
That is the new board https://store.arduino.cc/usa/arduino-uno-wifi-rev2
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-454584902, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4JzuKc32lxhvOmJEvJJPnDOCOf8Mks5vDl12gaJpZM4Z7wSh .
I have been trying to use the code, but I'm pretty confused on how to use it. I'm using the same base code from your example, but adding the new code sends compiling errors. I added the packet library and try to define some of the variables until the code did not show errors but it failed to compile.
You should have only replaced the one method - nothing more. Send me the code and let me take a look at it.
On Jan 15, 2019, at 7:47 PM, chakthy notifications@github.com wrote:
I have been trying to use the code, but I'm pretty confused on how to use it. I'm using the same base code from your example, but adding the new code sends compiling errors. I added the packet library and try to define some of the variables until the code did not show errors but it failed to compile.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.
the authentication is set to mysql_native_password.
This is the code I'm using. Now it seems to at least fail on the connect; it does take some time to go through the step.
//byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
char ssid[] = “my_network"; // your network SSID (name) char pass[] = “password"; // your network password (use for WPA, or use as key for WEP) int status = WL_IDLE_STATUS; // the Wifi radio's status IPAddress server_addr(x,x,x,x); // IP of the MySQL server here char user[] = “user"; char password[] = “passwd"; // Sample query char INSERT_SQL[] = "INSERT INTO RFID.log (card_num) VALUES ('2019')";
WiFiClient client; // Use this for WiFi instead of EthernetClient MySQL_Connection conn(&client); MySQL_Cursor* cursor;
void setup() { //Initialize serial and wait for port to open: Serial.begin(115200); while (!Serial) { ; // wait for serial port to connect. Needed for native USB port only }
// check for the WiFi module: if (WiFi.status() == WL_NO_MODULE) { Serial.println("Communication with WiFi module failed!"); // don't continue while (true); }
String fv = WiFi.firmwareVersion(); if (fv < "1.0.0") { Serial.println("Please upgrade the firmware"); }
// attempt to connect to Wifi network: while (status != WL_CONNECTED) { Serial.print("Attempting to connect to WPA SSID: "); Serial.println(ssid); // Connect to WPA/WPA2 network: status = WiFi.begin(ssid, pass);
// wait 10 seconds for connection:
delay(5000);
}
// you're connected now, so print out the data: Serial.print("You're connected to the network"); printCurrentNet(); printWifiData(); delay(5000);
Serial.println("\nStarting connection to server..."); // if you get a connection, report back via serial: if (client.connect(server_addr, 3306)) { Serial.println("connected to server");
}
Serial.println("Connecting to SQL..."); Serial.println(server_addr); if (conn.connect(server_addr, 3306, user, password)) { delay(1000); Serial.println("Connected.");
} else{ Serial.println("Connection failed"); conn.close(); } }
void loop() { // check the network connection once every 10 seconds: delay(10000); printCurrentNet();
Serial.println("Recording data if connected."); if (conn.connected()) { Serial.println("Inserting data"); cursor->execute(INSERT_SQL);
delay(5000);
}else{ Serial.println("Not recording, connection failed."); }
// 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;
}
void printWifiData() { // print your board's IP address: IPAddress ip = WiFi.localIP(); Serial.print("IP Address: "); Serial.println(ip);
// print your MAC address: byte mac[6]; WiFi.macAddress(mac); Serial.print("MAC address: "); printMacAddress(mac); }
void printCurrentNet() { // print the SSID of the network you're attached to: Serial.print("SSID: "); Serial.println(WiFi.SSID());
// print the MAC address of the router you're attached to: byte bssid[6]; WiFi.BSSID(bssid); Serial.print("BSSID: "); printMacAddress(bssid);
// print the received signal strength: long rssi = WiFi.RSSI(); Serial.print("signal strength (RSSI):"); Serial.println(rssi);
// print the encryption type: byte encryption = WiFi.encryptionType(); Serial.print("Encryption Type:"); Serial.println(encryption, HEX); Serial.println(); }
void printMacAddress(byte mac[]) { for (int i = 5; i >= 0; i--) { if (mac[i] < 16) { Serial.print("0"); } Serial.print(mac[i], HEX); if (i > 0) { Serial.print(":"); } } Serial.println(); }
Did you change the connector code to use the WiFi library instead of the Ethernet library?
On Jan 16, 2019, at 2:15 PM, chakthy notifications@github.com wrote:
This is the code I'm using. Now it seems to at least fail on the connect; it does take some time to go through the step.
include
include
include
include
include
include
//byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
char ssid[] = “my_network"; // your network SSID (name) char pass[] = “password"; // your network password (use for WPA, or use as key for WEP) int status = WL_IDLE_STATUS; // the Wifi radio's status IPAddress server_addr(x,x,x,x); // IP of the MySQL server here char user[] = “user"; char password[] = “passwd"; // Sample query char INSERT_SQL[] = "INSERT INTO RFID.log (card_num) VALUES ('2019')";
WiFiClient client; // Use this for WiFi instead of EthernetClient MySQL_Connection conn(&client); MySQL_Cursor* cursor;
void setup() { //Initialize serial and wait for port to open: Serial.begin(115200); while (!Serial) { ; // wait for serial port to connect. Needed for native USB port only }
// check for the WiFi module: if (WiFi.status() == WL_NO_MODULE) { Serial.println("Communication with WiFi module failed!"); // don't continue while (true); }
String fv = WiFi.firmwareVersion(); if (fv < "1.0.0") { Serial.println("Please upgrade the firmware"); }
// attempt to connect to Wifi network: while (status != WL_CONNECTED) { Serial.print("Attempting to connect to WPA SSID: "); Serial.println(ssid); // Connect to WPA/WPA2 network: status = WiFi.begin(ssid, pass);
// wait 10 seconds for connection: delay(5000); }
// you're connected now, so print out the data: Serial.print("You're connected to the network"); printCurrentNet(); printWifiData(); delay(5000);
Serial.println("\nStarting connection to server..."); // if you get a connection, report back via serial: if (client.connect(server_addr, 3306)) { Serial.println("connected to server");
}
Serial.println("Connecting to SQL..."); Serial.println(server_addr); if (conn.connect(server_addr, 3306, user, password)) { delay(1000); Serial.println("Connected.");
} else{ Serial.println("Connection failed"); conn.close(); } }
void loop() { // check the network connection once every 10 seconds: delay(10000); printCurrentNet();
Serial.println("Recording data if connected."); if (conn.connected()) { Serial.println("Inserting data"); cursor->execute(INSERT_SQL);
delay(5000); }else{ Serial.println("Not recording, connection failed."); }
// 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;
}
void printWifiData() { // print your board's IP address: IPAddress ip = WiFi.localIP(); Serial.print("IP Address: "); Serial.println(ip);
// print your MAC address: byte mac[6]; WiFi.macAddress(mac); Serial.print("MAC address: "); printMacAddress(mac); }
void printCurrentNet() { // print the SSID of the network you're attached to: Serial.print("SSID: "); Serial.println(WiFi.SSID());
// print the MAC address of the router you're attached to: byte bssid[6]; WiFi.BSSID(bssid); Serial.print("BSSID: "); printMacAddress(bssid);
// print the received signal strength: long rssi = WiFi.RSSI(); Serial.print("signal strength (RSSI):"); Serial.println(rssi);
// print the encryption type: byte encryption = WiFi.encryptionType(); Serial.print("Encryption Type:"); Serial.println(encryption, HEX); Serial.println(); }
void printMacAddress(byte mac[]) { for (int i = 5; i >= 0; i--) { if (mac[i] < 16) { Serial.print("0"); } Serial.print(mac[i], HEX); if (i > 0) { Serial.print(":"); } } Serial.println(); }
— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.
Nope, I did not change any code on the connector library. I changed this "MySQL_Connection conn((Client *)&client);" on the sketch to this "MySQL_Connection conn(&client);" as a test to see if it made a difference. This Arduino uno wifi rev.2 uses the WiFiNINA library. I used the example code to make the connection to my network and then added your code for the connector. Using the connect_wifi code from your code on GitHub I only changed the library to WiFiNINA.h and it connect to the network but no to the mysql server, it remains on the conn.connect and it doesn't fail, just hangs there.
Ok. I'm not sure what is wrong. I've ordered a Uno Wifi v2 to test it myseld. So, please have patience as I wait for it to be delivered. In the meantime, I may ask more silly questions. Like this one:
Which version of the connector library are you using?
On 1/16/19 11:23 PM, chakthy wrote:
Nope, I did not change any code on the connector library. I changed this "MySQL_Connection conn((Client *)&client);" on the sketch to this "MySQL_Connection conn(&client);" as a test to see if it made a difference. This Arduino uno wifi rev.2 uses the WiFiNINA library. I used the example code to make the connection to my network and then added your code for the connector. Using the connect_wifi code from your code on GitHub I only changed the library to WiFiNINA.h and it connect to the network but no to the mysql server, it remains on the conn.connect and it doesn't fail, just hangs there.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-455037670, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4NxAak1lYBazKpz50Mvq_OFJLLxyks5vD_qwgaJpZM4Z7wSh.
Version 1.1.1 Downloaded from the library manager
Ok. Strange. My new board will be here next week. Monday being a holiday.
On Thu, Jan 17, 2019 at 13:12 chakthy notifications@github.com wrote:
Version 1.1.1 Downloaded from the library manager
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-455272695, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4CGViw_YPyUF321fXbELdhv8x8Xiks5vEL0bgaJpZM4Z7wSh .
I know, it is confusing why it doesn't work. The fact that it is not even failing to make the connection. I checked the port, but I was able to connect from other programs and from a different computer. If it was restricted none of them would be able to connect.
I’ll be much more helpful once I get my own board. In the interim, what version of the IDE are you using?
On Thu, Jan 17, 2019 at 15:49 chakthy notifications@github.com wrote:
I know, it is confusing why it doesn't work. The fact that it is not even failing to make the connection. I checked the port, but I was able to connect from other programs and from a different computer. If it was restricted none of them would be able to connect.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-455326947, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4IRxsn9ngga6l-au2EVO7kn_sH53ks5vEOHngaJpZM4Z7wSh .
1.8.9 Hourly build
One more day to get your new device!!
As I receive your message also per e-mail, the above message creates expectations with me. Today I’ll stay home so I won’t miss it.
Try this:
Comment out lines 306-309 in MySQL_Packet.cpp. These lines:
// We must wait for slow arriving packets for Ethernet shields only. / avail_bytes = wait_for_client(); while (avail_bytes < packet_len) { avail_bytes = wait_for_client(); } /
That should get you connecting. There is still a problem with static cursors, but I'll work on that next. Just use dynamic cursors.
On 1/21/19 10:00 PM, chakthy wrote:
One more day to get your new device!!
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-456254919, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4BDXBRGemVVpT3P1a_bmFjsldpR0ks5vFn7UgaJpZM4Z7wSh.
Great, thanks. I will try that today.
it seems like it got connected now. But is not doing the insert into the table. No errors on the server side. It does go through the "Inserting data message" but no record on the table, and the following message is not printed.
if (conn.connected()) { Serial.println("Inserting data"); cursor->execute(INSERT_SQL); Serial.println("Data inserted");
So I removed the code from the previous post and I got some records on the table. It seems like it takes too long to get it inserted, or something else is delaying it. Is it better to do a while so if I get some data from a source then establish the connection to the mysql server and do the insert and then do a conn.close()? So that I don't get multiple connections if the board fails.
Good that it is working! Queries in general are much slower than on other hardware. To combat this, you may need to reorganize the way you gather data (as you mentioned) to accommodate the slower rate. You can use batch inserts or use a queue of values. Either way, it is also best to move the connect and disconnect inside the loop() method so that you're connecting before your queries then disconnect after. Also, try a wait for a bit (500 is usually enough) at the end of the loop.
If that doesn't help, I will try to reproduce your inserts and see if there is something in the WiFiNiNA library that is working unexpectedly. Which brings me to the other problems...
As for the problem I found regarding a static cursor, you can still use them but you must either comment out a bit more code in the cursor constructor or declare the cursor as a pointer and after the connection assign it like this:
MySQL_Cursor *cur_static = NULL; ...
Once again thank you for all the support.
So it looks the communication to the database on the virtual server was not that good. I ended up installing the database directly into the physical machine and that took care of the speed issues. What I'm trying to do now is use the adafruit PN532 with the same board, but it doesn't work. Do you know if the Arduino uno wifi ver 2 board is using some specific pins that are limited to the WIFI and prevents communication with the NFC board?
From the docs https://www.arduino.cc/en/Tutorial/WiFiNINAWiFiSSLClient:
Please note: these three boards use dedicated pins to communicate and select the WiFi module, therefore you have no restriction in the usage of the available digital pins connected to the header pins.
Doesn't speak of analog pins, but I think it may be safe to say it doesn't use them. I think. ;)
On Sun, Jan 27, 2019 at 4:47 PM chakthy notifications@github.com wrote:
So it looks the communication to the database on the virtual server was not that good. I ended up installing the database directly into the physical machine and that took care of the speed issues. What I'm trying to do now is use the adafruit PN532 with the same board, but it doesn't work. Do you know if the Arduino uno wifi ver 2 board is using some specific pins that are limited to the WIFI and prevents communication with the NFC board?
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-457957353, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4CU7AUAMAmwoOt6SvtyqGtdWg_uXks5vHh5rgaJpZM4Z7wSh .
So far I was able to get everything connecting better to the mysql server to make the inserts into the database. For every Insert there is this message on the error.log: Aborted connection X to db: 'unconnected' user: 'user' host: 'X.X.X.X' (Got an error reading communication packets) But even with this error there is a record on the table. After ~8 Inserts the "conn.connect(server_addr, 3306, user, password" starts failing and there is no connection to the mysql server. Then resetting the module gets everything running again. But the issue repeats after the ~8 INSERT statements. I'm closing the connection to the server after each mysql statement, and when the connection fails. I also do a conn.close() at the end of the code, just in case.
Ok. I think my last email was unnecessary. Sorry.
Interesting problem. Remind me again - are we using an Oracle-based MySQL or a variant? The variants are the ones that exhibit this problem more frequently.
I would try a ridiculously long delay at the end of the loop and test it. Say, 2 minutes. Then, if the problem doesn't manifest slowly lower the value until you discover the minimal delay that keeps the code running. Sounds low tech but if this is a latency issue, this poling strategy can help.
Dr. Bell
On Fri, Feb 15, 2019 at 3:34 PM chakthy notifications@github.com wrote:
So far I was able to get everything connecting better to the mysql server to make the inserts into the database. For every Insert there is this message on the error.log: Aborted connection X to db: 'unconnected' user: 'user' host: 'X.X.X.X' (Got an error reading communication packets) But even with this error there is a record on the table. After ~8 Inserts the "conn.connect(server_addr, 3306, user, password" starts failing and there is no connection to the mysql server. Then resetting the module gets everything running again. But the issue repeats after the ~8 INSERT statements. I'm closing the connection to the server after each mysql statement, and when the connection fails. I also do a conn.close() at the end of the code, just in case.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-464189937, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4LQmNxpVydFFuJ1xDXY1aR18MTt_ks5vNxntgaJpZM4Z7wSh .
The issue occurs on the Oracle based and variants. I have tested removing the Insert from the code and only going through the connection with a delay and then closing the connection. The connections still fail after 8 times
Ok. Remind me. Which version are you using? I’ll run some tests to see if there is something in the protocol.
On Mon, Feb 25, 2019 at 11:30 chakthy notifications@github.com wrote:
The issue occurs on the Oracle based and variants. I have tested removing the Insert from the code and only going through the connection with a delay and then closing the connection. The connections still fail after 8 times
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-467079104, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4Aio-aAmTjmw_40TxJKXy6UzAnyMks5vRA-YgaJpZM4Z7wSh .
I'm using 8.0.14 on a Mac and 8.0.13 on my virtual server (MySQL community server- GPL). I'm also using a mysql server downloaded with apt for an Ubuntu server. I get the same behavior on all of them.
Ok, let me check it out. I'll get back to you soon. :)
On Mon, Feb 25, 2019 at 11:47 AM chakthy notifications@github.com wrote:
I'm using 8.0.14 on a Mac and 8.0.13 on my virtual server (MySQL community server- GPL). I'm also using a mysql server downloaded with apt for an Ubuntu server. I get the same behavior on all of them.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/80#issuecomment-467085506, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4JG7zd5ZWucn0KfJVPOuxJIJ-gtfks5vRBOGgaJpZM4Z7wSh .
Hi ,had same problem, your conversation helped :) thanks
hello,
does this library supports mysql aws rds? i am facing problem with connection to aws rds.
If you're using a MySQL database, then yes, it can work provided:
The list is long and most of the troubleshooting is in the manual. However, I would recommend using the mysql client to try and connect to your database. All of the issues listed above except the mysql_native_password can be tested this way. Once you are able to connect (and the MySQL user account is set to use mysql_native_password), your connection should work.
Note: please don't hijack old threads/issues with new questions. Thank you.
I have been trying to use some of the examples to establish a connection with MySQL server. So far the WiFi connection is established, but once it goes to the "conn.connect(server_addr, 3306, user, password)" it seems to hang there. Memory doesn't seem to be an issue: Sketch uses 16612 bytes (33%) of program storage space. Maximum is 49152 bytes. Global variables use 683 bytes (11%) of dynamic memory, leaving 5461 bytes for local variables. Maximum is 6144 bytes.
MySQL server is running on a virtual machine inside my Mac. I have tested the mysql connection between the Mac and the virtual machine (both using different IP addresses). I added code on the Arduino to ping the virtual machine and it gets detected. I have tried another code to connect directly into the server that has the databases and it works, but of course this doesn't connect to MySQL so I tried to use the conn.connect() afterwards but it didn't work either. The connection doesn't fail, and the Arduino remains connected to the network. But the code doesn't go to the next line after the conn.connect(). Not sure if this is related to slight differences on the Arduino WiFi rev.2 Any ideas on how to debug the problem?
This is the code I'm using
include
include
include
include
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
char ssid[] = “my_network"; // your network SSID (name) char pass[] = “my_pass"; // your network password (use for WPA, or use as key for WEP) int status = WL_IDLE_STATUS; // the Wifi radio's status
IPAddress server_addr(X,X,X,X); // IP of the MySQL server here char user[] = “my_user"; char password[] = “my_pass2";
// Sample query char INSERT_SQL[] = "INSERT INTO RFID.log (card_num) VALUES ('2019')";
WiFiClient client; // Use this for WiFi instead of EthernetClient MySQL_Connection conn((Client )&client); MySQL_Cursor cursor;
void setup() { //Initialize serial and wait for port to open: Serial.begin(115200); while (!Serial) { ; // wait for serial port to connect. Needed for native USB port only }
// check for the WiFi module: if (WiFi.status() == WL_NO_MODULE) { Serial.println("Communication with WiFi module failed!"); // don't continue while (true); }
String fv = WiFi.firmwareVersion(); if (fv < "1.0.0") { Serial.println("Please upgrade the firmware"); }
// attempt to connect to Wifi network: while (status != WL_CONNECTED) { Serial.print("Attempting to connect to WPA SSID: "); Serial.println(ssid); // Connect to WPA/WPA2 network: status = WiFi.begin(ssid, pass);
}
// you're connected now, so print out the data: Serial.print("You're connected to the network"); printCurrentNet(); printWifiData(); delay(5000);
Serial.println("Connecting to SQL..."); if (conn.connect(server_addr, 3306, user, password)) { delay(1000);
Serial.println("Connected"); } else Serial.println("Connection failed."); conn.close();
}
void loop() { // check the network connection once every 10 seconds: delay(10000); printCurrentNet();
Serial.println("Recording data."); if (conn.connected()) cursor->execute(INSERT_SQL);
// 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;
}
void printWifiData() { // print your board's IP address: IPAddress ip = WiFi.localIP(); Serial.print("IP Address: "); Serial.println(ip);
// print your MAC address: byte mac[6]; WiFi.macAddress(mac); Serial.print("MAC address: "); printMacAddress(mac); }
void printCurrentNet() { // print the SSID of the network you're attached to: Serial.print("SSID: "); Serial.println(WiFi.SSID());
// print the MAC address of the router you're attached to: byte bssid[6]; WiFi.BSSID(bssid); Serial.print("BSSID: "); printMacAddress(bssid);
// print the received signal strength: long rssi = WiFi.RSSI(); Serial.print("signal strength (RSSI):"); Serial.println(rssi);
// print the encryption type: byte encryption = WiFi.encryptionType(); Serial.print("Encryption Type:"); Serial.println(encryption, HEX); Serial.println(); }
void printMacAddress(byte mac[]) { for (int i = 5; i >= 0; i--) { if (mac[i] < 16) { Serial.print("0"); } Serial.print(mac[i], HEX); if (i > 0) { Serial.print(":"); } } Serial.println(); }