Stop working GPRS

[tarcisocallegario]

Dear Dr ChukBell, I have been using the MySQL_Connector_Arduino library during the month of June/2023 in an IOT product that uses the ESP32 board combined with the SIM800L module. After many attempts I managed to insert data into BD HOSTINGER.com.br. The device sent a record every 5 minutes to the DB. For connection I used the TinyGsmClient.h library. After working for 30 days, the device stopped sending records to the DB. The SIM800L module can no longer connect to the BD. Checking the MySQL_Connector_Arduino documentation I was able to verify, to my surprise, that this library should not work with the GPRS GSM module. Would you recommend any modifications so that the MySQL_Connector_Arduino could establish a connection with the DB using the GSM module?

[ChuckBell]

Hello, The statement about the connector not working with GSM is there because most GSM libraries are not designed to be used with the Server and Client libraries common to the Arduino. That said, if you got it working, then it is likely the GSM library you found is compatible. As to why it stopped working, I would check external issues such as firewalls and access/permissions before attempting modifications to the library. You may also want to make sure whomever hosts your MySQL database hasn’t upgraded recently and is using an Oracle distribution because non-Oracle distributions have been known to fail, especially if they have been upgraded. Hint: check the native password setting. You can send me your sketch to look over if you’d like but it is more likely there is an external influence/issue at work here. Dr. Bell

…

On Oct 9, 2023, at 8:45 PM, tarcisocallegario ***@***.***> wrote: Dear Dr ChukBell, I have been using the MySQL_Connector_Arduino library during the month of June/2023 in an IOT product that uses the ESP32 board combined with the SIM800L module. After many attempts I managed to insert data into BD HOSTINGER.com.br. The device sent a record every 5 minutes to the DB. For connection I used the TinyGsmClient.h library. After working for 30 days, the device stopped sending records to the DB. The SIM800L module can no longer connect to the BD. Checking the MySQL_Connector_Arduino documentation I was able to verify, to my surprise, that this library should not work with the GPRS GSM module. Would you recommend any modifications so that the MySQL_Connector_Arduino could establish a connection with the DB using the GSM module? — Reply to this email directly, view it on GitHub <#215>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB6SHYDIJYEKRPUXCC3ZXVTX6SLEDAVCNFSM6AAAAAA5ZQZ7GCVHI2DSMVQWIX3LMV43ASLTON2WKOZRHEZTIMBWGY3DEMQ>. You are receiving this because you are subscribed to this thread.

[tarcisocallegario]

Dear Dr. Charles Bell, I am sending the message recorded in the Error Log file in the Public folder
from Hostinger at https://srv369-files.hstgr.io/e4c740aebdfa4af5/files/public_html/error_log:
...
464 [15-Oct-2023 22:29:57 UTC] PHP Warning: mysqli_real_connect(): (HY000/1045): Access denied for user 'u262515252_Aerogerador'@'141.136.41.220' (using password: YES) in /home/ u262515252/domains/claudia28.hostingerapp.com/public_html/wp-includes/class-wpdb.php on line 2019

465 [15-Oct-2023 22:29:57 UTC] PHP Warning: mysqli_real_connect(): (HY000/1045): Access denied for user 'u262515252_Aerogerador'@'141.136.41.220' (using password: YES) in /home/ u262515252/domains/claudia28.hostingerapp.com/public_html/wp-includes/class-wpdb.php on line 2019
...

[tarcisocallegario]

Dear Dr. Charles Bell, below is the code from the MySQL_Connection.cpp library where I inserted the "lprint" commands to check where the program stopped working:
MySQL_Connection.cpp - Library for communicating with a MySQL Server over Ethernet. (formerly mysql.cpp)

Change History:

Version 1.0.0a Created by Dr. Charles A. Bell, April 2012.
Version 1.0.0b Updated by Dr. Charles A. Bell, October 2013.
Version 1.0.1b Updated by Dr. Charles A. Bell, February 2014.
Version 1.0.2b Updated by Dr. Charles A. Bell, April 2014.
Version 1.0.3rc Updated by Dr. Charles A. Bell, March 2015.
Version 1.0.4ga Updated by Dr. Charles A. Bell, July 2015.
Version 1.1.0a Created by Dr. Charles A. Bell, January 2016.
Version 1.1.1a Created by Dr. Charles A. Bell, January 2016.
Version 1.1.2b Created by Dr. Charles A. Bell, November 2016.
Version 1.2.0 Created by Dr. Charles A. Bell, March 2020.
*/
#include <Arduino.h>
#include <MySQL_Connection.h>
#include <MySQL_Encrypt_Sha1.h>

#define MAX_CONNECT_ATTEMPTS 3
#define CONNECT_DELAY_MS 500
#define SUCCESS 1

const char CONNECTED[] PROGMEM = "Connected to server version ";
const char DISCONNECTED[] PROGMEM = "Disconnected.";

/*
connect - Connect to a MySQL server.

This method is used to connect to a MySQL server. It will attempt to
connect to the server as a client retrying up to MAX_CONNECT_ATTEMPTS.
This allows the possibility of longer than normal network lag times
for wireless networks. You can adjust MAX_CONNECT_ATTEMPTS to suit
your environment.

server[in] IP address of the server as IPAddress type
port[in] port number of the server
user[in] user name
password[in] (optional) user password
db[in] (optional) default database

Returns boolean - True = connection succeeded
*/
boolean MySQL_Connection::connect(IPAddress server, int port, char *user,
char *password, char *db)
{
int connected = 0;
int retries = MAX_CONNECT_ATTEMPTS;

// Retry up to MAX_CONNECT_ATTEMPTS times.
while (retries--)
{
Serial.println("...trying...");

Serial.println(server);
Serial.println(port);
Serial.println(user);
Serial.println(password);

connected = client->connect(server, port);       >>>>>>>>>>>>>>>>>>>>> the pprogram seems to stop working here...
if (connected != SUCCESS) {
  Serial.print("...got: ");
  Serial.print(connected);
  Serial.println(" retrying...");
  delay(CONNECT_DELAY_MS);
} else {
  break;
}

}

....

[tarcisocallegario]

Below is the result in the serial output during the execution of the script of my program:

range2===>>>11
millis===>>>88007
range2===>>>1
Allcom.brallcomallcom
vento=0
vento_copia=0.00
rpm=0
rpm_copia=0.00
range2=1
version=34
pointerdirection=7
last_action=0
rpm_previous=0.00
If 1
If 2
141.136.34.3
u262515252_testeuser
Tarciso21

ps: from here is the output generated by the MySQL_Connection.cpp library resulting from the "lprint" commands

...trying...
141.136.34.3
3306
u262515252_testeuser
Tarciso21

[tarcisocallegario]

Here's part of the program I'm developing:

//
// Created on: 03/07/2019 By Tarciso Callegario - tarciso.callegario@gmail.com - updated on 08/10/2023 - Version 3.4
//
#include <ESP32httpUpdate.h>
#include <Arduino.h>
#include <Update.h>
#define TINY_GSM_MODEM_SIM800 // definition of the modem used (SIM800L)
#define TINY_GSM_RX_BUFFER 1030
#include <TinyGsmClient.h> // library with GSM commands
// SIM800L serial communication object
HardwareSerial SerialGSM(1);
// library object with GSM functions
TinyGsm modemGSM(SerialGSM);
#include <CRC32.h>
#include "FS.h"
#include "SPIFFS.h"
//
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

TinyGsmClient client(modemGSM);

const char server[] = "141.136.41.220";
const int port = 80;

int program_version = 34;

IPAddress server_addr(141 , 136 , 34 , 3) ​​; // IP of the MySQL server here
char userdb[] = "u262515252_testeuser" ; // MySQL user login username
char passworddb[] = "Tarciso21" ; // MySQL user login password

char INSERT_SQL[] = "INSERT INTO u262515252_Aerogerador.Parametros_Aerogerador ( v_vento , eixo_rpm , versao_firmiware, ponteirodirecao, ultima_acao, intervalo2 ) VALUES ('%f', '%f', '%d', '%d', '%d', '%d')";
//( v_vento , eixo_rpm , versao_firmiware , data_corrente, ponteirodirecao ,ultima_acao , rpm_anterior , intervalo2) VALUES ('%d', '%d','%d', '%d','%d', '%d','%d','%d')";
char query[256];

// INSTANCIANDO OBJETOS
MySQL_Connection conn((Client *)&client);

....
...

if (modemGSM.waitForNetwork())
  {
  Serial.println(" If 1");  
  if (modemGSM.gprsConnect(APN, USER, PASSWORD))
    {
    Serial.println(" If 2");
     
    Serial.println(server_addr);
    Serial.println(userdb);
    Serial.println(passworddb);


    if (!conn.connect(server_addr, 3306, userdb, passworddb))    >>>>>>>>>> >>>>>>>>>>>>>>>>>>>>> the pprogram seems to stop working here...
      {
      Serial.println(" If 3");  
      Serial.println("Conexão SQL falhou.");
      conn.close();
      delay(1000);
      }
    else
      {
      Serial.println(" If 4"); 
      sprintf(query, INSERT_SQL, vento_copia, rpm_copia, versao_programa , ponteirodirecao , ultima_acao, intervalo2 );
      // Initiate the query class instance
      Serial.println(" If 5"); 
      MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
      // Execute the query
      cur_mem->execute(query);
      Serial.println(" If 6"); 
      // 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;
      Serial.println("Informações Enviadas");
      conn.close();
      }
    }

[tarcisocallegario]

Thank you for your helpful help and availability. Att. Tarciso Callegario

[tarcisocallegario]

MariaDB Server 10.5.19

[ChuckBell]

Hi, The error message you are getting tells me a few things: 1) The connection is working 2) Either the password was changed or the GRANT statement restricts the host (more later on this) 3) You are using a non-Oracle MySQL installation, which is not guaranteed to work (but appears to be working) That said, you should check with the MySQL Administrator to double-check the password for that user account and ensure that the mysql_native_password is enabled for that user. Recall that a user account in MySQL is actually two parts: the user id and the host id. GRANT statements can be issued to restrict access to specific hosts/networks, so you need to ask the MySQL administrator to check the ***@***.*** is: 1) still valid, 2) not restricted, 3) has the correct password, and 4) mysql_native_password is enabled for that user+host. For example, the error log shows Access denied for user 'u262515252_Aerogerador'@‘141.136.41.220’. Thus, the user u262515252_Aerogerador is actually stored in MySQL as: User_id = u262515252_Aerogerador host_id = 141.136.41.220 One way to check permissions for users is with this command: SHOW GRANTS FOR 'u262515252_Aerogerador'@‘141.136.41.220’; You should see various grants for the databases and that the mysql_native_password is enabled. If you don’t see any rows, you should consider using wildcards in the host_id (but that is not recommended). See https://dev.mysql.com/doc/refman/8.1/en/account-names.html and “Account Names” at https://dev.mysql.com/doc/refman/8.1/en/grant.html for more details. Hope this helps, Dr. Bell

…

On Oct 15, 2023, at 10:58 PM, tarcisocallegario ***@***.***> wrote: MariaDB Server 10.5.19 — Reply to this email directly, view it on GitHub <#215 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB6SHYDOAIQS7CF2DZ6J6RLX7SPDVANCNFSM6AAAAAA5ZQZ7GA>. You are receiving this because you commented.