0

Context:

I have a simple program that gets sensor data once per second and immediately uploads it to AWS RDS MySQL instance. However, I have very spotty network connection that constantly goes in and out. Half the time the data uploads in under 1 second no problem, but when there is no internet connection, pymysql (mysql? aws?) takes 5 seconds to throw an error. Ideally it would only take 1 second to throw an error and then I could properly catch it and continue collecting sensor data without interruption.

Example Code:

import pymysql
import gpsd


def uploaddata(): #I want this to timeout in 1 second. 
    try:
        db = pymysql.connect('host=xxx.region.rds.amazonaws.com', portxxxx,user='admin',password='xxxx')
        with db:
            with db.cursor() as cursor:
                cursor.executemany(insertstatement,datatoupload)
                db.commit()
        datatoupload.clear()
    except pymysql.err.OperationalError:
        logging.info('rds temp dns failure aka no internet, saving datatoupload for later')


datatoupload= []
lasttime = []
insertstatement = (
    "INSERT INTO db.tablename(timelogged,datatoupload)"
    "VALUES (%s)"
)

startpacket = gpsd.get_current()

while True:
    packet = gpsd.get_current() #get lastest value from gpsd
    newtime = packet.time[0:19]
    speed = packet.hspeed
    if newtime!= lastime: #if data is in the next second, save speed value
        data = [newtime,speed]
        datatoupload.append(data)
        uploaddata()
        lasttime = newtime

Solutions I've attempted with no luck:

  • spinning up a nonbinding thread to wait the 5 seconds while the error is thrown, but maybe I implemented incorrectly because the program waited until the thread was complete before capturing more sensor data (even though I did not use join)
  • implementing a timeout class, function, and context manager with the help of this tutorial: https://www.youtube.com/watch?v=vGWSdp9dyhI which works for all other functions except this one.
  • implementing Panagiotis Simakis' solution which didn't work for pymysql query: How to set a timeout for a pymysql query
  • modifying the db instance parameter group to change any global timeout parameters that looked relevant to 1 (maybe throwing spaghetti at the wall which I'm not above)
J Oliver
  • 1
  • Can you run multiple threads/programs, so that one 'collects' the data in a local buffer, and another thread is responsible for 'sending' the data? That way, the 'collector' can keep operating grabbing sensor readings without being interrupted due to network issues. Also, the overhead of communicating to MySQL might be too high -- you could consider sending the data to an IoT (Internet of Things) service, or an Amazon SQS queue instead, which would require less traffic on the network. – John Rotenstein Feb 25 '23 at 03:15

0 Answers0