Using a Historical Tag Provider that is looking at a valid mysql database. Enabled history on tags and no historian tables seem to be created on the database. Don’t see any errors related to create database permission issue as seen in another post. The only issue I see is logs are store and forward issues related to sqlth_drv table not found. Using trial version of igntiton 7.9. Historical tag provider status is Running without issues
Does the user you're connecting to the database with have access to create tables? Sqlth_drv is one of the tables that is automatically created and maintained by the historian, so if it's not found it would get automatically created - if it were possible to do so.
I use root user for mysql and see no problems with database connections. I was able to even manually execute SQL scripts like insert from the designer sql db query browser tool.
I just installed Postgres just to be sure and set my project to use it instead of mysql and all tag historian tables are now created automatically.
This leads that something is not right with mysql connections specially when it deals with schemas i.e. multiple databases. The reason we created multiple mysql dbs is to use a different one for each project.
Could you open a support ticket (email or phone) and go into some more detail on your setup? If there is an issue with multiple schemas with MySQL, we're not aware of it, but support should be able to copy down the details and reproduce whatever's going on.
Hello,
With an Ignition version 7.9.9, I encounter the same issues.
I am using MariaDB.
When creating a new connection, I have to specify the Extra Connection Property "serverTimezone=UTC;". If not specified, the connection would not go Valid with the following error message:
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (The server time zone value 'Paris, Madrid' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.)
[......]
7.9.9 (b2018081621)
Oracle Corporation 1.8.0_171
I did not have to had this extra parameter on the previous versions.
Concerning the history part, once the connection is valid, the history status becomes valid as well. However The history tables are not created. The credentials for the database are correct and I can CRUD tables and data from HeidiSQL with them.
Table 'xxx.sqlth_drv' doesn't exist
Restarting the services (Ignition and MariaDB), restarting the server won't change anything. The history tables aren't created automatically anymore.
Has something changed with the sql connector ? Or somewhere else ?
Regards.
Ah, yeah - there's some special case code added for the MySQL translator in a version coming up (looks like 7.9.11) that will automatically use the gateway's timezone when requesting a connection with the MySQL server. For now, manually specifying it is definitely the way to go. Unfortunately, this is just a change required by the new JDBC driver - technically, it's a server-side configuration "problem" that the old driver was less strict about.
I would definitely get in touch with support about this. I don't know if we specifically test MariaDB/InnoDB/all different MySQL flavors, but if there is an issue with table creating logic we should definitely get it fixed.
Hi, I got the same problem.
Im using Ignition 7.9.9.
The SQL server is running on Ubuntu linux with all access granted, also tried several users and databases/projects, but there is no difference.
SQL version is 5.7.23 build 18.04.1.
I tried updating the JDBC driver from the supplied 7.9.9. version to 8.0.12.on MySQL, but it didnt help.
I will try reinstalling the gateway from scratch, but finding the root cause to the problem should be priority.
This is from a fresh install of a gateway, so I dont think reinstalling should fix it, but its worth a shot.
Did anyone get this issue resolved? I am seeing it also after a fresh install of 7.9.9. There really aren’t any useful errors in the Ignition Gateway logs.
I did some research today, and identified a fix that will go into 7.9.10. If you’re not using MySQL server version 8, then rolling back the JDBC driver should be a valid workaround.
1 Like
Thanks for the update Paul.
Quick question, would this affect gateways that are updating, say from 7.9.7 to 7.9.9, and already have pre existing, multiple, MySQL db connections?
We already have the older MySQL JDBC driver since we are using an older version of their DB. Just want to make sure before we start updating some of our servers.
Any existing install won’t be affected during upgrade - the new driver is only used on fresh installs. Note that there can be complications there - since the default ‘Connection String’ parameters changed for MySQL 8 compatibility, restoring an old .gwbk on a fresh install can cause problems (because it will take the connection parameters from the old).
Ok, thank you for the feedback. I ran into the issue you are eluding too on my testing setup. Thanks again@
Hi,
there is any temporary solution for who is using MySQL server version 8 and has the same issues?
This “no tags in historian tables” happened to me but with MS SQL. There were a few things different in my implementation. First, all my tags were imported into a new project (or I think I transferred the project first from a backup) and then I created the database connection. I noticed my graphs where not showing up and I received a lot of ResultWriter errors in the logs indicating “invalid object name ‘sqlth_drv’” within the details. I did 2 things at once so not sure what caused it to start working again. First, I edited the “Extra Connection Properties” and ensured the databaseName=mydatabase casing matched exactly to the database casing in MS SQL…in my case, the database was in ALL capital letters. Then I created a brand new tag from scratch and enabled the historian setting on the tag. Viola! That single tag was added to the table. I few seconds later…ALL my previous pre-created tags were created in the database automagically.
Hi,
I have the same problem, Ignition Version 7.9.9 and MySQL 5.7 and Ignition can’t create automatically the table for historian saving, I want to know ho you have solved?
I still have this problem, no idea why.
Gonna try update to 7.9.10
Ok, so I updated to the newest Version 7.9.10.
Everything is working now, I only needed to update. Nothing else, all the tags in Store and Forward is now unloading onto the DB itself.
Great!
1 Like
Someone online had my exact same problem and solved it for me! What are the odds?
Can confirm an upgrade to 7.9.10 resolved issue immediately.
Thanks lads
I had this issue with nothing being written to the database but still being allowed to create tables inside the database query tool. The connections to the database were stated as valid but nothing would be written. I ended up uninstalling 7.9.10 and downgrading to 7.9.9. This ended up resolving my issue.
We have a similar case, except that we have one database connection where history is being written properly, but another where no tables are being created. Both connections point to the same database and use the same user. Connection is valid, tables can be created from within Ignition DB browser.
Using 7.9.9