I have a set of Float numbers I have in a transaction group storing to my database every 10 seconds. These values are typically brought in, after scaling, with four digits past the decimal point. In one of my pages, I have a query pulling a chunk of those numbers in to a dataset for manipulation and display. When I look at the dataset that has been pulled in, all of the values are rounded off to the decimal point. Do I need to do anything explicit in my query to make sure the full value is pulled in? Has anyone seen this before? Thanks.
–Bump–
FWIW, I’m running 7.1.4 on this machine right now, with MySQL Server (not sure which version). Thanks.
I’m not sure it’s really necessary to bump when your post is still in the top 3 topics…
Anyhow, when you say you’re “looking at the dataset”, and only seeing 2 digits after the decimal, how are you looking at it? In scripting? Or in a table? The main thing to figure out is whether the data is really getting rounded before it gets to the dataset, or whether the rounding is simply in the presentation.
For example, the default table column config only shows 2 decimals. If you’re looking at it in a table, go to the table customizer (right click or ctrl-u on the table), and change the “Number Format” for the column to something like “#,##0.####”. If you now see more digits, the data is fine, and you just need to configure the format settings for the columns.
In scripting, you could loop through the data and use “print” to print to the console, and see what the full value is.
Regards,
It’s not that I’m only seeing two digits past the decimal, it’s that I’m not seeing any digits past.
Looking at the data in the MySQL table, a typical value might be 12345.6789
Looking at the dataset viewer in Ignition, the value is 12345
This is with only the database query operating on the data. Thanks.
In the dataset viewer, if you click on the cell, what does it say down below for “Column Type”? Also, is the query you’re using to select the value performing any calculations, or is it simply selecting the column?
Regards,
Column type is Double. I’m not performing any calculations, but I am selecting based on the time of day the value was stored. I don’t know if that would impact the data or not. Thanks.
Can you try running the exact same query through the MySQL query browser and see what the results look like? We need to start narrowing down who/what is rounding/truncating the fractional part of the numbers. First step: MySQL or Ignition?
OK, I had some time to hook up to our customer’s system and gather some more data. Here is what I found:
- The tag is set up as a Float4, and is showing a value with two digits past the decimal point in an on-page display
- In the transaction group, it shows the tag as a Float4 Data Type, and the value displays with two digits past the decimal point
- When I run a query in Ignition pulling the data into a table, it comes in as an Integer
- When I run a query using a query tool (FlySpeed in this case), it comes in as an Ingeger
So, it appears that something between Ignition and MySQL is causing the value to be stored as an Integer instead of a Float4. Now, my first though would be to blow out the MySQL table, let Ignition re-create the table, and see what it does, but I can’t lose the existing data. Is there anything I should check in the transaction group settings? If I do a database backup and blow out the table to try that, how much difficulty am I going to have merging the existing table with the new one?
Thanks.
Hi-
Did you happen to look at what the column was defined as in the database? It’s possible that it was created by Ignition when the item was defined as Int, and then the item was changed, causing the column to be defined as INT. “Blowing out” the table would do it, but it’s also not necessary- you can change column types without much difficulty.
You can use the MySQL administrator to check that and change the column if necessary, or you can do it through SQL, if you only have access to the designer.
To see how a table is defined:
describe tablename(with your real table name, of course)
If you find the column is the wrong type, you can change it with:
ALTER TABLE tablename MODIFY columnname doubleIf the column in the database already is a floating point type, it’s back to looking at Ignition.
Regards,
I didn’t think to post that the column was already defined as a FLOAT in MySQL. Just for giggles, I changed them to DOUBLE and monitored logging for a while, but the values are still rounding. Thanks.
Man, this is just strange. What version of MySQL are you using? The columns show up as float/double, but when you do a simple “select colname from table” in Ignition OR a different query tool they show up as Integers?
And when you test, you’re just selecting the column, and not some expression involving it, right?
Finally, have you tried querying from the mysql command line tool?
Regards,
Colby,
We are running MySQL 5.1.44-community. Every method I’ve used to run a query pulls out integers. I am running a plain SELECT statement, no expressions involved.
I did have a thought over the weekend. I wondered if somehow the transaction group got hung up from an older version, and was storing everything as integers. Before startup this morning, I deleted everything in my transaction group and re-created it. I’ll let it run today and see if it makes a difference.
I suppose it could be something like that, but the thing is, you’ve already checked that mysql has the columns as double (or at least float). Even if the group was storing integers, all of your various query mechanisms would still return floats. The numbers wouldn’t have any precision after the decimal, but they would still be returned as float data types.
Also, I just had a thought- you just recreated the items, so maybe you can’t check, but if you have an old copy of them, what is the deadband set to?
Regards,
OK, as I kind of expected, re-creating the transaction group didn’t make any difference. I checked the deadband, and it is set at 1E-4 for all of my floats. What I don’t really understand right now is, some of the floats in the transaction group are storing properly, and some are rounding. I don’t see any difference between the ones that round and the ones that don’t. Is there anything else you can think of that I should check? Thanks.
Considering that this has gone on for over a month, sounds like its time to call in so we can take a look. If it is a remote system, then take a backup, mock it up locally and reproduce it in an environment we can look at.
I was afraid you were going to say that. 
I’ll arrange to get a backup or an Internet connection on the machine, and call in. Thanks.
Ok, so the problem is coming from MySQL. If you have a column with a float datatype and you insert a value of 48403.9987 the value returned from the SELECT query is 48404. Here the problem is that the number is so large the precision gets cut off making MySQL round it up. To fix this you just change the dataytpe to either double or decimal(10,4) in the db and the value will be correct.
Thanks for the help Travis! Do you know if this is a known MySQL bug? Would upgrading to a newer version take care of this, or is the float precision cutoff common?
It is not a MySQL bug. It is a known problem. If you set a float property of a numeric text field in Ignition you would get the same result.
OK. I’ll have to tuck this one away for future reference. Thanks again!