Search for a command to run...
Technical leading expert concerning datacenters, cybersecurity, administration and project management, including : Managed Services Provider - PaaS, SaaS - Clouds & Datacenters - CyberSecurity & Network Architecture - B2B, B2C, B2E tailor-made or custom Services and Softwares - Backups - GIS - Driving external Support Teams
No comments yet. Be the first to comment.
A few days ago, we have recently noticed some strange access to one of our customer portals, some kind of direct external access to a specific account, nothing dangerous for customers' data, but just annoying, we never like direct access especially w...
This article is intended to help you dynamically selecting objects on the Geoconcept GIS web interface. Defined the objects you need as vector objects To be able to interact with specific objects on your map, first you have to declare them as a vecto...
The other day, I wanted to help a friend to start his Ethereum development Environment on MacOS and I didn’t remember how much little stuff you need to install to make it work. So here is my small Cheat Sheet to start his Ethereum development Environ...
The Rock64 has a Quad-Core ARM Cortex a53 64-bit that you could easily be used to catch few more $DGB Digibyte wants to bring back CPU mining implementing RandomX lowering more the barrier to mine Digibyte and get started without any kind of “commitm...
On this page
During one of my project working with Ignition SCADA by Inductive Automation, and using it with MySql to store its data, I had to find a way to create views for external reporting that would dynamically integrate the new monthly automatically created tables of data by Ignition. Not very clear at the first read, let me explain: the design of Ignition makes it store its data in multiple specific tables and each month it creates new tables from these specific tables for the current month. It’s a kind of self made partitioning, I’d have preferred to work with partitioning instead but I would had the pleasure to write this post.
Duckducking go the Internet, I found an interesting article from Paul Campbell on Stack Overflow explaining how to dynamically create a procedure to dynamically create SQL with UNION of tables found in the system tables. My first idea was to find a way to know if a new table was created in the database. What a better place to look into that by searching in the system tables, specifically the information_schema.tables. One would have thought that we’d have just to add a trigger calling this procedure on this table and act accordingly… but if you read this article, this means that you already know that MySql prohibit to create triggers on a system tables. It’s mainly because system tables are not really tables, but are more acting like views. I was looking for a solution that will reside in the database, nothing called from the external: a SCADA system have to be resilient and depending on other external computers is not always a good idea. Finally MySql has everything we need: he owns a scheduler and a scheduled event is able to call a stored procedure. From this point it is pretty easy, just create the stored procedure that will create or replace the reporting views every month and call it with the an specific event in MySql.
First, here is a screen shot of the code as Medium will break most of it.
The code:
Now you have a stored procedure called dyn_view_maker. Let’s create the event that will trigger it. At first, you have to check if your MySql has Event Scheduler already enabled. If not, first add a line in your my.cnf (or my.ini on Windows) under [mysqld] to start automatically the event scheduler at launch time.
[mysqld]
event_scheduler=ENABLED
If you cannot restart your MySql to start the Event Scheduler, just run this sql
SET GLOBAL event_scheduler = ON;
SET @@GLOBAL.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@GLOBAL.event_scheduler = 1;
The event creation is pretty straightforward
CREATE EVENT `ev_create_v_temparature`
ON SCHEDULE EVERY 1 MONTH
STARTS ‘2018–01–01 01:00:00’
ON COMPLETION PRESERVE ENABLE
COMMENT ‘Your comment’
DO
call dyn_view_maker()
In my case the tables was created every month, but it’s pretty easy to adapt this sql to your needs. This is what I thought to implement to get to this result. If you have a better idea, feel free to leave me a comment.