mySQL db

[KillahB33]

I saw you mention in another issue that moving to an external database such as mysql would fix some issues with access a queue from multiple machines. I currently have a Docker setup with two containers in one pod and I can post to the queue but my other container doesn't appear to be updating it's count for the queue, I switched to ack queue and it's still doesn't appear to be working. So I am hoping if I switch to an external db such as mySQL/MariaDB that this issue would be fixed, just need to know the syntax for using that instead of SQLlite

[KillahB33]

Sorry to bug you @peter-wangxu but I am at the stage where I need to make this work or move on but don't want to spend time rewriting to use an mqtt if this can be made to work with a small change.

[peter-wangxu]

Sorry to bug you @peter-wangxu but I am at the stage where I need to make this work or move on but don't want to spend time rewriting to use an mqtt if this can be made to work with a small change.

not very big change, i could try to give a version this weekend.

[KillahB33]

That would be absolutely amazing!! Thank you so much!! I don't see an option to buy you a coffee/beer, I definitely owe you one after this!

[KillahB33]

Hey @peter-wangxu! I finally got a chance to set it up, the only thing blocking me from testing is that it's saying MySQLQueue object has no attribute total, this is when I try to do qsize. Is that still available, from what I can read it looks like it is but maybe I am doing something wrong. I see it now, self.total +=1 was part of the put before and now it's not (would need it for the get as well). Did you want me to do a pull request to fix this or did you want to? I have made the fix in my testing env, and testing now. Will updated UPDATE: I must still be missing something cause it's still complaining. So I figured out what is missing, but I'll have to dig in tomorrow to figure out how to fix it.

    self.total = self._count()

def _count(self):
    sql = 'SELECT COUNT({}) FROM {} WHERE status < ?'.format(
        self._key_column, self._table_name
    )
    row = self._getter.execute(sql, (AckStatus.unack,)).fetchone()
    return row[0] if row else 0

[KillahB33]

Hey @peter-wangxu sorry to bother but i tried a few things and can't seem to get it working.

[KillahB33]

Thanks so much for the updates, something still appears to be of though cause the first process is putting records on the queue but my second process isn't recognizing the records are there after running a qsize

[KillahB33]

Here is the code snippet, for some reason it's not updating once the loop starts and I use my other process to put items into the queue.

import os, requests, json, sys, persistqueue, time

job_queue = persistqueue.MySQLQueue(os.getenv("DOCKERENV_MARIAHOST"), "pyqueue", "Password", "pyQueue", int(os.getenv("DOCKERENV_MARIAPORT")))
job_queue_p1 = persistqueue.MySQLQueue(os.getenv("DOCKERENV_MARIAHOST"), "pyqueue", "Password", "pyQueue_p1", int(os.getenv("DOCKERENV_MARIAPORT")))

total_queue = job_queue.qsize() + job_queue_p1.qsize()

if __name__ == '__main__': 
    while True:
            total_queue = job_queue.qsize() + job_queue_p1.qsize()
            print(total_queue)
            time.sleep(10)

[KillahB33]

Found it. Here is someone else with the issue. https://stackoverflow.com/questions/60276355/why-python-script-loop-is-not-fetching-updated-data-from-db

I updated my code to count the records from the db instead of queue size, and without conn.commit() the count wasn't updating either but as soon as I do conn.commit() after execute it started working properly.

[peter-wangxu]

Found it. Here is someone else with the issue. https://stackoverflow.com/questions/60276355/why-python-script-loop-is-not-fetching-updated-data-from-db

I updated my code to count the records from the db instead of queue size, and without conn.commit() the count wasn't updating either but as soon as I do conn.commit() after execute it started working properly.

Thanks for the investigation. i was busy with other stuff, will continue work on this later this weekend

[KillahB33]

Thanks for the investigation. i was busy with other stuff, will continue work on this later this weekend

No stress at all, I had time as was needing to solve it so I figured I would dig into it. I really appreciate all your help on this!