ESP-NOW in combination with WiFi.mode(WIFI_AP_STA) and MySQL

[coenblijker]

Hi All,

I have a project that uses the ESP-NOW protocol, acts as a (ESPNOW) server and connects to my WIFI network also.

The network setup code is shown below:

void setupESPNOW()
{
    //  setup as access point and station at the same time.
    WiFi.mode(WIFI_AP_STA);
    WiFi.onEvent(WiFiEvent);
    WiFi.begin(ssid, password);

    // Wait for connection
    while (WiFi.status() != WL_CONNECTED) {
        delay(500);
    }
    // Init ESP-NOW
    if (esp_now_init() != ESP_OK)  {
        Serial.println("Error initializing ESP-NOW");
        return;
    }
    // Register the receive callback
    if (!esp_now_register_recv_cb(OnDataRecv) == ESP_OK) {
        Serial.println("Register receive CB failed.");
       return;
    }
    Serial.printf("IP: %s, MAC: %s, channel: %d.\n", WiFi.localIP().toString().c_str(),   WiFi.macAddress().c_str(), WiFi.channel());
}

These are the OnDataRecv() callback functions:

void OnDataRecv(const uint8_t *mac, const uint8_t *incomingData, int len)
{
    if (mac[0] == 0x40 && mac[1] == 0x91 && mac[2] == 0x51 && mac[3] == 0x9B && mac[4] == 0xD7 && mac[5] == 0xB0)
    {
        handleSolarBoilerMessages(mac, incomingData, len);
    }
}

void handleSolarBoilerMessages(const uint8_t *mac, const uint8_t *incomingData, int len)
{
    TempHumidity_t tempHumStr;

    if (len == sizeof(tempHumStr)) {
        memcpy(&tempHumStr, incomingData, sizeof(tempHumStr));
        // Send values to MQTT..
        sendFloatToMQTT(tempHumStr.temperature1, "esp32/Basement/SolarBoiler/Temp1", 2);
        sendFloatToMQTT(tempHumStr.temperature2, "esp32/Basement/SolarBoiler/Temp2", 2);
        sendIntegerToMQTT(tempHumStr.humidityValue, "esp32/Basement/SolarBoiler/HumidityValue");

        // Store this data in MySQL..
        storeTempAndHumidityValues(tempHumStr.temperature1, tempHumStr.temperature2, tempHumStr.humidityValue);
    }
}

And this is the MySQL code:

static WiFiClient wifiClient;
MySQL_Connection conn(&wifiClient);

bool connectToMySQL(){
    if (!conn.connected()) {
        if (!conn.connect(server_addr, 3306, mySQLuser, mySQLpassword)) {
            Serial.printf("MySQL connect failed..\n");
            return false;
        }
        Serial.printf("MySQL connect succeeded..\n");
    }
    return true;
}

void execQuery(const char *query)
{
    if (!connectToMySQL()){
        return;
    }
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    // Execute the query
    if (!cur_mem->execute(query, false)){
        Serial.printf("*** Exec query: '%s' failed..\n", query);
    }
    delete cur_mem;
    conn.close();
}

void storeTempAndHumidityValues(float hotWaterTemp, float solarBoilerTemp, int humVal){
    char query[400];

     // clang-format off
     sprintf(query, "insert into Environment.BasementMeasurements "
                    "(HotWater_temperature, SolarBoiler_temperature, Humidity_value, measured_at_UTC)"
                    "values(%.1f, %.1f, %d, UTC_TIMESTAMP)", hotWaterTemp,  solarBoilerTemp, humVal);
      // clang-format on
      execQuery(query);
    }

The weird thing is that the 'storeTempAndHumidityValues()' function works fine when called from within the main loop() function but fails when being called from within the context of the OnDataRecv() function, as is in this code example.
After enabling the log functionality in the WifiClient.cpp module (and ading some extra log lines) I see the following:

The lines below are created during the initial setup were I just test whether a MySQL connection can be setup, which is indeed the case.

[ 1044][D][WiFiClient.cpp:635] connected(): WifiClient not connected.
MySQL connecting server: 192.168.178.29 @port: 3306
[ 1045][D][WiFiClient.cpp:253] connect(): Calling lwip_connect with socket: 49
[ 1053][D][WiFiClient.cpp:260] connect(): Returned res: -1, errno: 119 from lwip_connect
[ 1061][I][WiFiClient.cpp:302] connect(): socket fd in use: 49
Connected to server version 5.5.5-10.1.47-MariaDB-0ubuntu0.18.04.1
[ 1873][D][WiFiClient.cpp:599] connected(): WifiClient is connected.
Disconnected.

The lines below are created during the attempt to connect to the MySQL server when an ESP-NOW message was received:

Storing values in MySQL
[ 2174][D][WiFiClient.cpp:635] connected(): WifiClient not connected.
MySQL connecting server: 192.168.178.29 @port: 3306
[ 2186][D][WiFiClient.cpp:253] connect(): Calling lwip_connect with socket: 50
[ 2188][D][WiFiClient.cpp:260] connect(): Returned res: -1, errno: 119 from lwip_connect
[ 5191][I][WiFiClient.cpp:278] connect(): select returned due to timeout 3000 ms for fd 50
...got: 0

In this case the 'select()' function is timing out. And I can't figure out why that is. I find it especially intriguing that the MQTT send functions, which use the same WiFiClient code, is working without any issues. The difference between the MQTT and the MySQL code is that the latter is making a new connection at each call to 'execQuery()' and the MQTT code work with a persistent connection (Created at startup). It tried to use a persistent connection for the MySQL connection also but that doesn't work either.

Any help on how to solve this issue, or suggestions where to look, are highly appreciated!!

Coen