[Maria-discuss] What is the right way to update based on a DB change?
Hey Everybody, I have the next scenario which I am not sure about the right way for a solution. I have a radius server that authenticate clients from multiple WIFI Access Points. I have couple systems and they need to know the current client login identifier (MAC or IP Address). It is stored in the DB and the DB is being updated on every login. I can try to give a static IP for every user and it will resolve this issue but there is an overhead for this. So what I was thinking instead is to either run a query that will fetch the latest changes or use a trigger to update another table which in turn will be read by an external software every 1-5 seconds. I believe that the right solution is to use a trigger that will update another table with the details and on the other table I will have a Processed column that will be updated when the software finished processing the row. Then in turn every 24 hours I will run a DB cleanup cron. I will try later on to share the schema. Any advice is more than welcome. Thanks, Eliezer ---- Eliezer Croitoru NgTech, Tech Support Mobile: +972-5-28704261 Email: <mailto:ngtech1ltd@gmail.com> ngtech1ltd@gmail.com Web: <https://ngtech.co.il/> https://ngtech.co.il/ My-Tube: <https://tube.ngtech.co.il/> https://tube.ngtech.co.il/
Hi Eliezer, Why not simply having a timestamp on your client list table with the automatic timestamp update of mysql/mariadb (https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html) And having your external client filter on this automatically maintained update date ? I don't know the use-case but a stronger auth than MAC/IP is also often a good solution ^ Regards, Benoit On 30/09/2022 09:02, ngtech1ltd@gmail.com wrote:
Hey Everybody,
I have the next scenario which I am not sure about the right way for a solution.
I have a radius server that authenticate clients from multiple WIFI Access Points.
I have couple systems and they need to know the current client login identifier (MAC or IP Address).
It is stored in the DB and the DB is being updated on every login.
I can try to give a static IP for every user and it will resolve this issue but there is an overhead for this.
Sowhat I was thinking instead is to either run a query that will fetch the latest changes or use a trigger to update another table which in turn will be read by an external software every 1-5 seconds.
I believe that the right solution is to use a trigger that will update another table with the details and on the other table I will have a
Processed column that will be updated when the software finished processing the row.
Then in turn every 24 hours I will run a DB cleanup cron.
I will try later on to share the schema.
Any advice is more than welcome.
Thanks,
Eliezer
----
Eliezer Croitoru
NgTech, Tech Support
Mobile: +972-5-28704261
Email: ngtech1ltd@gmail.com <mailto:ngtech1ltd@gmail.com>
Web: https://ngtech.co.il/ <https://ngtech.co.il/>
My-Tube: https://tube.ngtech.co.il/ <https://tube.ngtech.co.il/>
_______________________________________________ Mailing list:https://launchpad.net/~maria-discuss Post to :maria-discuss@lists.launchpad.net Unsubscribe :https://launchpad.net/~maria-discuss More help :https://help.launchpad.net/ListHelp
Hi Eliezer, Why not simply having a timestamp on your client list table with the automatic timestamp update of mysql/mariadb (https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html) And having your external client filter on this automatically maintained update date ? I don't know the use-case but a stronger auth than MAC/IP is also often a good solution ^ Regards, Benoit On 30/09/2022 09:02, ngtech1ltd@gmail.com wrote:
Hey Everybody,
I have the next scenario which I am not sure about the right way for a solution.
I have a radius server that authenticate clients from multiple WIFI Access Points.
I have couple systems and they need to know the current client login identifier (MAC or IP Address).
It is stored in the DB and the DB is being updated on every login.
I can try to give a static IP for every user and it will resolve this issue but there is an overhead for this.
Sowhat I was thinking instead is to either run a query that will fetch the latest changes or use a trigger to update another table which in turn will be read by an external software every 1-5 seconds.
I believe that the right solution is to use a trigger that will update another table with the details and on the other table I will have a
Processed column that will be updated when the software finished processing the row.
Then in turn every 24 hours I will run a DB cleanup cron.
I will try later on to share the schema.
Any advice is more than welcome.
Thanks,
Eliezer
----
Eliezer Croitoru
NgTech, Tech Support
Mobile: +972-5-28704261
Email: ngtech1ltd@gmail.com <mailto:ngtech1ltd@gmail.com>
Web: https://ngtech.co.il/ <https://ngtech.co.il/>
My-Tube: https://tube.ngtech.co.il/ <https://tube.ngtech.co.il/>
_______________________________________________ Mailing list:https://launchpad.net/~maria-discuss Post to :maria-discuss@lists.launchpad.net Unsubscribe :https://launchpad.net/~maria-discuss More help :https://help.launchpad.net/ListHelp
Hey Benoit, First thanks for the response. For an ISP the authentication is done in the radius level and then applied to a MAC and IP. I will try to make more sense into it. The client connects to the WIFI AP with a USERNAME an PASSWORD. These are verified on an encryption tunnel against the Radius server. The radius server then decides which IP to distribute to the client from a POOL. I can try to change the radius server schema but it’s not in my hands. However, another table is in my hands and in there I can add a timestamp. The reason I was thinking about a trigger and another table is since I can mark in the table with an “updated” field when the script is done processing the data. Eliezer ---- Eliezer Croitoru NgTech, Tech Support Mobile: +972-5-28704261 Email: <mailto:ngtech1ltd@gmail.com> ngtech1ltd@gmail.com Web: <https://ngtech.co.il/> https://ngtech.co.il/ My-Tube: <https://tube.ngtech.co.il/> https://tube.ngtech.co.il/ From: Benoit Plessis <benoit@plessis.info> Sent: Friday, 30 September 2022 10:25 To: ngtech1ltd@gmail.com; maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] What is the right way to update based on a DB change? Hi Eliezer, Why not simply having a timestamp on your client list table with the automatic timestamp update of mysql/mariadb (https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html) And having your external client filter on this automatically maintained update date ? I don't know the use-case but a stronger auth than MAC/IP is also often a good solution ^ Regards, Benoit On 30/09/2022 09:02, ngtech1ltd@gmail.com <mailto:ngtech1ltd@gmail.com> wrote: Hey Everybody, I have the next scenario which I am not sure about the right way for a solution. I have a radius server that authenticate clients from multiple WIFI Access Points. I have couple systems and they need to know the current client login identifier (MAC or IP Address). It is stored in the DB and the DB is being updated on every login. I can try to give a static IP for every user and it will resolve this issue but there is an overhead for this. So what I was thinking instead is to either run a query that will fetch the latest changes or use a trigger to update another table which in turn will be read by an external software every 1-5 seconds. I believe that the right solution is to use a trigger that will update another table with the details and on the other table I will have a Processed column that will be updated when the software finished processing the row. Then in turn every 24 hours I will run a DB cleanup cron. I will try later on to share the schema. Any advice is more than welcome. Thanks, Eliezer ---- Eliezer Croitoru NgTech, Tech Support Mobile: +972-5-28704261 Email: <mailto:ngtech1ltd@gmail.com> ngtech1ltd@gmail.com Web: <https://ngtech.co.il/> https://ngtech.co.il/ My-Tube: <https://tube.ngtech.co.il/> https://tube.ngtech.co.il/ _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net <mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Benoit Plessis
-
Benoit Plessis
-
ngtech1ltd@gmail.com