While trying to test robustness of system, we took down MySQL for a moment then brought it back up. This is with a Kepware -> FSQL -> FPMI implementation. We wanted to see what would happen to the system.
After return of the database, we could no longer write to any tags - reads seemed OK, but write errors. We restarted the FPMI gateway, but that didn’t help. We disabled, then re-enabled, SQLTags in FSQL and that seemed to solve it. I know there is caching in FSQL if the Database goes away, but could you describe how SQLTags should behave with a database shutdown/restart? I don’t know the designed behaviour, so I can’t put what we are seeing in context. I also couldn’t find info in any of the logs related to the DB down event. Any ideas on where I should look?
That is NOT desired behavior. Please take a look in the FactorySQL Frontend under: the connection->system status, then the SQLTags tab. Also, Help->Log Viewer.
Database write caching only makes sense for data logging. You don’t want a write to occur long after the request failed - that’s like having an ATM dispense your money hours late!
Here’s what should happen in the event of a DB failure:
- Operator performs action in FPMI that does a write
- Component overlay status indicates write failure and value doesn’t change
That’s it. DB comes back up, operator repeats #1, then write success.
[quote=“Carl.Gould”]1) What were the version of FactoryPMI and FactorySQL?
2) What were the specific “Write Errors” that you observed?[/quote]
Version: FactoryPMI v.3.3.1
Let me execute the test protocol again and I can get you the specific write error info and the FSQL version.
FSQL Version: 4.2.9
Log indicating that we toggled database off then back on. The last two entries show that it was down for about 4 minutes.
The write error we get in FPMI when system goes down then comes back up (Note the DB went down then is back when this error message occurs which you can tell by the timestamps:)
This is the log showing no errors in SQLTags after the DB went down and came back up (but before recovering by toggling SQLTags support in FSQL):
I am limited to three attachments, so I will post another followup to continue.
To continue from the last post because of the three attachment limit:
To fix problem, we uncheck the ‘Enable SQLTags support’ in FSQL and save. Open the settings back up and check it again and save. All seems well after that (i.e. we can then write). We tried previously resetting the gateway, but that had no effect. The log entry shows:
Some thoughts:
- The error is repeatable, so it wasn’t just some errant electrons floating around
- We observed the caching system for our historical data while MySQL was down and it worked. This is a nice feature.
Ok, you’re not crazy, I just replicated it. It actually appears to actually be an issue with how MySQL’s auto increment works, in particular with InnoDB. here’s what happens:
-Each write request has an ID, which is an auto-incrementing column in MySQL.
-FactorySQL tracks the last id it processed, and then uses it to look for new write entries.
-Upon restart of MySQL, InnoDB sets its counter to max(id) from the table- which should have no rows- and thus 1. So, the new ids are behind the last value that FactorySQL saw.
-FactorySQL doesn’t see the new writes until the counter is greater than the last value, which could be very high if the system has been running for a long time.
Solutions:
I suppose we shouldn’t keep track of the ids in memory- or at least reset the internal value when the data connection goes bad. In the mean time, however, the simple solution for you would be to switch the engine of the write queue table to be MyISAM, which doesn’t have this behavior.
To do so:
Open MySQL administrator or query browser, find your SQLTags database, and then “edit” the sqlt_wq table. On Table Options change the engine to MyISAM, hit apply, and you should be good to go.
Hope this helps,
Thanks for the detailed response. Why does MyISAM have a different behaviour? Does it not reset the auto-increment counter?
Well, I guess the exact definition of what the “auto increment” column should do is not defined, so it’s up to the engines make up their own definition.
From what I could find, MyISAM stores the last key value in the table’s data file. InnoDB, on the other hand, does a “SELECT max()” query on startup and puts that value in memory.
In basically all other databases systems (that I can think of), auto incrementing columns are done by way of a sequence & trigger, even when there’s a shortcut keyword (ie postgres). In those cases, the sequence is a first class object whose whole purpose is its value- I guess that’s why one might presume that the autoincrement column in MySQL would be similar.
Maybe we should consider switching over to the sequence/trigger method with MySQL as well…
Regards,
