mySQL query execution problem when using timer interrupts

[jyaquinas]

Hi,

I've been saving some sensor data read from the MKR1000 to the mySQL server using your library. Pseudocode goes something like this:

main(){
  disable interrupts
  if(trigger is true){
    saveDataToDB()
    set trigger to false
  }
  enable interrupts
}

ISR1(){
  read analog values and save to buffer array
}

ISR2(){
  set trigger to true
}

saveDataToDB(){
  parse data to char
  create mysql cursor
  execute cursor (and save data to server)
  delete cursor
}

But I've realized that the connection to the server (and saving the data to it) takes some time (longer if more data is to be saved), and since I have the interrupts disabled during the query, no sensor data is being saved during the query. I want to capture the sensor data in the buffer array while the connection to the server is happening, so I've tried to leave the interrupts enabled during the query, but the main loop will stop. So the only thing I have changed in the sketch is the enabling of the interrupts during the query and it won't work anymore. I'm not sure where the ISRs are interfering with the "cur_mem->execute(query);" Do you have any idea about where the problem is occurring?

Thanks. Thomas

[ChuckBell]

Hello,

I've not worked with interrupts much. Can you send me your sketch or a minimal sketch that demonstrates the problem? I have a MKR1000 which I can use to investigate.

Dr. Bell

On 3/6/18 9:39 PM, jythomaskim wrote:

Hi,

I've been saving some sensor data read from the MKR1000 to the mySQL server using your library. Pseudocode goes something like this:

|main(){ disable interrupts if(trigger is true){ saveDataToDB() set trigger to false enable interrupts } ISR1(){ read analog values and save to buffer array } ISR2(){ set trigger to true } saveDataToDB(){ parse data to char create mysql cursor execute cursor (and save data to server) delete cursor } |

But I've realized that the connection to the server (and saving the data to it) takes some time (longer if more data is to be saved), and since I have the interrupts disabled during the query, no sensor data is being saved during the query. I want to capture the sensor data in the buffer array while the connection to the server is happening, so I've tried to leave the interrupts enabled during the query, but the main loop will stop. So the only thing I have changed in the sketch is the enabling of the interrupts during the query and it won't work anymore. I'm not sure where the ISRs are interfering with the "cur_mem->execute(query);" Do you have any idea about where the problem is occurring?

Thanks. Thomas

— 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/34, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4OpHwFDRmZAu8TDEZgS0Px_Cvunwks5tb0hlgaJpZM4SfxV7.

[jyaquinas]

Hi Dr. Bell,

I’m sending you my sketch. It might be quite confusing but the key point here is that within the loop(), i run a function called save2SQL() inside a if(trigger==true) loop. There, if I enable TC4 interrupt before the function, it seems like the interrupts are causing some errors with the mysql connector library (the query execution function). However, it will work normally if I leave it disabled while that function is running, and only enable after it’s finished. I do want the interrupts to keep running while the query execution is done though, allowing me to save all the data during the querying process (and not miss any). If you have any questions please let me know. Thanks for your time.

Thomas JY Kim

On 8 Mar 2018, at 5:37 AM, Dr. Charles Bell notifications@github.com wrote:

Hello,

I've not worked with interrupts much. Can you send me your sketch or a minimal sketch that demonstrates the problem? I have a MKR1000 which I can use to investigate.

Dr. Bell

On 3/6/18 9:39 PM, jythomaskim wrote:

Hi,

I've been saving some sensor data read from the MKR1000 to the mySQL server using your library. Pseudocode goes something like this:

|main(){ disable interrupts if(trigger is true){ saveDataToDB() set trigger to false enable interrupts } ISR1(){ read analog values and save to buffer array } ISR2(){ set trigger to true } saveDataToDB(){ parse data to char create mysql cursor execute cursor (and save data to server) delete cursor } |

But I've realized that the connection to the server (and saving the data to it) takes some time (longer if more data is to be saved), and since I have the interrupts disabled during the query, no sensor data is being saved during the query. I want to capture the sensor data in the buffer array while the connection to the server is happening, so I've tried to leave the interrupts enabled during the query, but the main loop will stop. So the only thing I have changed in the sketch is the enabling of the interrupts during the query and it won't work anymore. I'm not sure where the ISRs are interfering with the "cur_mem->execute(query);" Do you have any idea about where the problem is occurring?

Thanks. Thomas

— 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/34, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4OpHwFDRmZAu8TDEZgS0Px_Cvunwks5tb0hlgaJpZM4SfxV7.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/34#issuecomment-371276130, or mute the thread https://github.com/notifications/unsubscribe-auth/AYUq_bRNvJlMOd4ynFl5wr6dzez4rvykks5tcETwgaJpZM4SfxV7.

[jyaquinas]

UPDATE: Seems like the problem was in that the buffer array was running out of memory, so increasing the buffer array size resolved this issue. But there is another issue now...

While the query is executed, the interrupt function, which saves values into a buffer array, is executed (currently at 5Hz or so). So the values are continuously saved into this buffer array until the next query (the values in the buffer array are saved to the server, and the buffer array is cleared). Then the now cleared buffer is free, and the interrupt function will save new functions into this array. But the issue appears when the query takes too long, and the values saved in the buffer array start to increase (and accumulate). The more values I insert, the longer the query takes, which then causes more values to be saved into the array, leading to an even longer query execution time, and so on (until buffer array runs out of memory again and the program stops).

For instance: Only 4 values are initially stored in the buffer array, causing the initial query to be:

INSERT INTO ##.## (val1, val2, val3, val4) values (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4);

But this query will take about, say 500 ms. While this querying is occurring, the interrupt saves values into the buffer, but within a period of 500 ms, about 16 values are now saved. Then the next query will have 16 new entries:

INSERT INTO ##.## (val1, val2, val3, val4) values (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4) (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4);

This query, which now contains more entries, takes about 3000 ms. This longer querying time causes even more values to be saved into the buffer array (about 33 can be saved within this period). The query will then take longer, more values saved, etc etc...

When I directly execute these queries to the server, using MySQLWorkbench, it only takes a fraction of a second. Therefore, I assume the delay is being caused when the query is being parsed/sent to the server from the arduino, but I can't exactly pinpoint where this delay is occurring.

Is there a way to increase the query execution speed?

[copterino]

@jythomaskim could you please post the code of saveDataToDB() function? I suppose the problem in the way you prepare a query string.

As for reading sensor values using interrupts, maybe you can replace it with manual polling of the sensor using time interval variable and Arduino's millis()?

[jyaquinas]

@copterino The reason I'm reading the sensor values using interrupts is that I wanted to make sure I was continuously measuring the data while the query was occurring. I had previously read the sensor values in the main loop and while the query was occurring, no data was being read, so it produced a gap in the data.

Currently, the raw current sensor values is being read and placed in a fixed window size, similar to like a moving window. This is being done using an interrupt that executes at a high frequency. Then using another interrupt, it takes the values in the window and calculates the rms current value, and then places it in a buffer array. These rms values collected in the buffer are then uploaded all at once using the function below.

void save2SQL(int deviceID, int elements, double rms1[], double rms2[], double rms3[], double rms4[], char time[][15]) {
  char INSERT_SQL[3000];
  char valchar[100];
  sprintf(INSERT_SQL, "INSERT INTO %s.multi%d (rmsI1, rmsI2, rmsI3, rmsI4, time) VALUES ", dbName, deviceID);

  for (int i = 0; i < elements; i++)   
  {
    sprintf(valchar, "(%.2f, %.2f, %.2f, %.2f, '", rms1[i], rms2[i], rms3[i], rms4[i]);
    strcat(valchar, time[i]);
    strcat(valchar, "'), ");
    strcat(INSERT_SQL, valchar);
    memset(valchar, 0, 100);   //reset valchar array (all to NULL)
  }

  //Remove last comma and space
  INSERT_SQL[strlen(INSERT_SQL)-2] = 0;

  // Serial.println(INSERT_SQL);
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);    
  Serial.println("Uploading");
  unsigned long startt = millis();

  cur_mem->execute(INSERT_SQL);

  unsigned long endt = millis();
  Serial.print("Uploaded in ");
  Serial.println(endt-startt);
  delete cur_mem;
  memset(INSERT_SQL, 0, 3000);  //reset INSERT_SQL array (all to NULL)
}

The issue, like I mentioned, is that the query is taking quite long, and during this time period, rms values get accumulated in the buffer array, and thus produce an even longer query time. Seems like the time is being taken up somewhere in the process of sending the query to the server from the arduino, because when I run the same query in MySQLWorkbench, it only takes a fraction of a second.

[jyaquinas]

I'm adding a picture of the resulting serial monitor.

[ChuckBell]

Hi,

Sorry for the delay. The only way I've been able to improve query speed is by using an Ethernet shield rather than WiFi. That probably won't help you, sadly.

I hope you do get this worked out and in the interim please consider closing the item since we seem to have solved the original issue.

Thanks,

Dr. Bell

On 5/3/18 2:41 AM, jythomaskim wrote:

UPDATE: Seems like the problem was in that the buffer array was running out of memory, so increasing the buffer array size resolved this issue. But there is another issue now...

While the query is executed, the interrupt function, which saves values into a buffer array, is executed (currently at 5Hz or so). So the values are continuously saved into this buffer array until the next query (the values in the buffer array are saved to the server, and the buffer array is cleared). Then the now cleared buffer is free, and the interrupt function will save new functions into this array. But the issue appears when the query takes too long, and the values saved in the buffer array start to increase (and accumulate). The more values I insert, the longer the query takes, which then causes more values to be saved into the array, leading to an even longer query execution time, and so on (until buffer array runs out of memory again and the program stops).

For instance: Only 4 values are initially stored in the buffer array, causing the initial query to be: INSERT INTO ##.## (val1, val2, val3, val4) values (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4);

But this query will take about, say 500 ms. While this querying is occurring, the interrupt saves values into the buffer, but within a period of 500 ms, about 16 values are now saved. Then the next query will have 16 new entries: INSERT INTO ##.## (val1, val2, val3, val4) values (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4) (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4), (1,2,3,4); This query, which now contains more entries, now takes about 3000 ms. This longer querying time causes even more values to be saved into the buffer array (about 33 can be saved within this period). The query will then take longer, more values saved, etc etc...

It seems like when I directly execute these queries to the server, using MySQLWorkbench, it only takes a fraction of a second. Therefore, I assume the delay is being caused when the query is being parsed/sent to the server. I can't exactly pinpoint where this delay is occurring, but I'd like to increase the querying speed.

Is there a way to increase the query execution speed?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/34#issuecomment-386203927, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4MwE2KBOgsAIvVq3DJvB5i-mfN79ks5tuqa0gaJpZM4SfxV7.