History Tables problem

Hi,

Today We’ve noticed that the history table has some problems like the same data 3 times and that kind of stuff. At least that is what the history query function shows (system.tag.queryTagHistory).

To solve the problem we dropped all the history tables (SQLT*) but without shutting down ignition. Now even if I restart the server, the tables are not created again.

By the way, I’m using ORACLE 10 and Ignition 7.1.8. Any clue of what happens here?.

Thanks in advance.

You really should have verified that the data was like that in the actual table. Based on the parameters passed to queryTagHistory, it would be possible to get a result set that appeared to have the same value multiple times.

As for recreating the table, there’s a few things that could be coming into play. First, the tables are only verified when the first values get stored. If no data is being generated to store, they won’t get checked. However, I suspect what is happening is that while you dropped the table, you didn’t delete it’s entry in the partitions table- and I think the system only creates a table when it creates a partition.

So, look at “sqlth_partitions” and delete the entry that corresponds to the table you dropped. You will probably then need to restart the gateway, as I believe these entries get cached.

After you get it back up and running, if the values aren’t like you’d expect, post back or give us a call.

Regards,

Colby,

I don’t know if this is a known issue but if I look into my Oracle SQL developer, there is no SQLTH* tables into my DB. But if I do the same thing with the Database query browser (the Ignition tool) I’m able to browse the SQLTH* table. In fact, you can see in the attached figure that I have this tables two times (I don’t know why but I have 3 copies of my other tables).

Do you know why this happens?

Thanks in advance.




With the same ORACLE, I’m running 3 different DB (users) with the same SID. The 3 DB are basicaly the same, so I belive that’s why Ignition shows the same table 3 times. But I’m not sure if this could be a problem for ignition.

Ok,

I’ll sum up the problem because I noticed several problems with the DB connection.

I’m using Oracle 10g and only one SID. In that SID, today I have 2 users (“planta1” and “planta2”). For the Oracle SQL developer, Toad or any tool based in TNSnames conection, this is like 2 different databases. The problem is that for Ignition and his JDBC conection this is like only one DB. In fact, in ignition i have 2 conection to the diferent users (same SID, different username and password) but if I do a simple query SELECT * and shows it in a table, shows data of both users.

So, do you know something about it?.

If I use two SIDs instead of only one, will solve my problem?
In MySQL, if I have 2 different Schemas, happens the same problem?

Thanks in advance for the time and the pacience.

This does come up from time to time. I believe most users end up fully qualifying the table names with the schema id. In MySQL, “schemas” are much different, they’re separate databases and not “namespaces”, and you specify a single one in the connection string. It may be possible to specify a single schema to limit to in the Oracle connection string, but I’ve haven’t come across it.

There is one thing in Ignition that might help you: edit the database connection and select “show advanced”. Under SQL Compatibility, select “include schema in table name”. This should at least help you distinguish what’s what when browsing the database.

Regards,