MySQL DB update issue

[maxdemaio]

Currently the bot will go down in the situation where the app fails to do an UPDATE on the MySQL database. At the very end of retweeting tweets, the bot will attempt to store the last seen ID into the DB. If it fails, this causes the last seen tweet to be stale, and if the bot re-queries Twitter, most subsequent tweets from the stale ID will have already been retweeted.

Error seen from logs:

2022-04-28T13:22:41.643519+00:00 app[scheduler.3653]:     Utils.storeLastSeenId(mydb, mycursor, currLastSeenId)
2022-04-28T13:22:41.643519+00:00 app[scheduler.3653]:   File "/app/utils.py", line 41, in storeLastSeenId
2022-04-28T13:22:41.643580+00:00 app[scheduler.3653]:     mycursor.execute("UPDATE tweet SET tweetId = '%s' WHERE id = 1", (exampleId,))
2022-04-28T13:22:41.643582+00:00 app[scheduler.3653]:   File "/app/.heroku/python/lib/python3.9/site-packages/mysql/connector/cursor_cext.py", line 269, in execute
2022-04-28T13:22:41.643692+00:00 app[scheduler.3653]:     result = self._cnx.cmd_query(stmt, raw=self._raw,
2022-04-28T13:22:41.643693+00:00 app[scheduler.3653]:   File "/app/.heroku/python/lib/python3.9/site-packages/mysql/connector/connection_cext.py", line 510, in cmd_query
2022-04-28T13:22:41.643837+00:00 app[scheduler.3653]:     raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
2022-04-28T13:22:41.643856+00:00 app[scheduler.3653]: mysql.connector.errors.OperationalError: 2013 (HY000): Lost connection to MySQL server during query

Possible solution: attempt to store the last ID in the iterable first, and if this fails then we should not continue with retweets.

[maxdemaio]

Another solution would be querying since a specific date time string if the Twitter API allows for this. This will allow the bot to function independent of individual tweets for anchor points (could be deleted).

Also, instead of storing the last tweeted date in a database, we could just Tweet since now.currentTime() - 10mins

[maxdemaio]

We would want to find a way to not store the last seen ID anymore. Some way to query the twitter API and retweet every 10mins as usual. We can look at other Twitter bots for inspiration.

[maxdemaio]

closing to migrate issue to #31