My basic problem is that I have master-master replication set up with mysql and I get duplicate records on both servers prior to replication ocurring, which causes replication to fail.
The typical mysql solution to this is to have an autoincrement primary key and configure one server to only use odd numbers and one to only use even numbers. This works great except the ignition trend does not have an autoincrement primary key in the trend. I want to stick with the built-in trending because of all the neat functions for restoring trends into charts etc.
Is there a way to add a primary key auto increment to the trend file within ignition? It is about a 20% penalty on data storage size, but with hard drive prices these days that is pretty much free.
If not, will the Ignition trending system get upset with an additional column in the trend tables? I would have a script create the trend files a week before the end of the month and have an auto increment column to the right of all of the other columns.
Are there other solutions people have used to have high availability (both reads and writes) on the database side with unasissted failover and resynchronization?
Or maybe the root cause of the problem is really the fact that Ignition is writing the same data to the primary and backup database… This is on a redundant Ignition system and the backup Ignition is not allowed to write to the backup database-- this was to avoid a split-brain situation, so I know it is the primary Ignition that is writing to both databases. This is stopped at the database level by blocking comms from the Ignition server’s IP address.
How is the master Ignition possibly writing to both databases? You would need the tag history splitter, or at least potentially to have some tags pointed to the backup connection. Is there any possibility that the other node is looping back? (unlikely, I know, but I suppose in some cases it’s possible, especially with “log-slave-updates” turned on)
Figuring out why the same data is being stored to both is probably the first issue.
As for the other questions… fundamentally, the system won’t care if you add additional columns. Now, the current key is carefully constructed in order to make it the first unique index on the table, which causes data to be physically stored according to it, which helps query performance a lot. However, you can give it a try with an auto incrementing column and see what the result is. You might even be able to include the auto increment column in the clustered index, such as (t_stamp, tagid, id). As long as it’s the last column in the index, I don’t think it will affect performance when not used in the query.
[del]It may also be possible to add a column for “server_id”, and then create on on insert trigger on the data table that sets it to “@@global.server_id”. Of course, in order to accomplish what you want, it also needs to be part of the primary key, so I’m not sure it’s any better than the auto-increment route.[/del] Edit: apparently triggers are discouraged with replication.
Looks like this works. It has been going a while now and queries to the trend data seem to be working properly and equally fast.
In mysql, the auto-increment field has to be the primary key and also the first one in the hash so it kills the tag_id indexing that makes trend queries nice and fast.
Not a problem as you can just manually add a tagid ascending/timestamp ascending index to match the default index that the table would get with the standard primary keys and columns.
Final step is to setup some daily code in Ignition to periodically check for existance of next month’s table and create it with the extra index and column if it doesn’t exist. Then we just have to wait forever and see if it fails… It’s taken months to fail in the past, so it is hard to know when it is truly solved.