Tables doesn't recognize data as numbers

Hello, everyone!

One of my tables aren’t recognizing values as numbers, even if I changed the type of data on data model admin, therefore I can’t filter correctly. On other tables there are commas separating thousands of hundreds, but in this particularly doesn’t.

Hi @beey
If the database column type is string, then changing Field Type will not change anything, since it’s mainly used for formatting purpose.

Hello @flamber
Ok, understood.
The column type on database it is decimal, on others tables with the same types I didn’t have any problem.

@beey
Post “Diagnostic Info” from Admin > Troubleshooting
And which database is it?
I highly doubt it’s decimal - there must be something different with this column compared to your other tables.

@flamber

It’s mysql.

{
“browser-info”: {
“language”: “pt-BR”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.102 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.8+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.8”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.8+10”,
“os.name”: “Linux”,
“os.version”: “4.14.193-149.317.amzn2.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“mysql”
],
“hosting-env”: “elastic-beanstalk”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “12.3”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.8”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-09-11”,
“tag”: “v0.36.5.1”,
“branch”: “release-0.36.x”,
“hash”: “a3459e8”
},
“settings”: {
“report-timezone”: “America/Sao_Paulo”
}
}
}

@beey Use this query on two different tables - one where a column work, and another where it does not:

DESCRIBE table_name;

@flamber
oh thanks, now I see the problem. In the table where it doesn’t work the column type it’s varchar(45).
But I don’t know why, on my model it’s decimal(8,2)…
What can I do to fix this? Just update my column?

@beey I don’t know your database or what program is writing to that, so you’ll have to figure that out yourself, but it has nothing to do with Metabase.

@flamber
Thanks, all solved. I changed directly on my db.
I still have one question, the data type is now decimal but when I tried to create a graph doesn’t have the option auto group, why?
I managed to do that with SQL, but like I said there are similar columns in other tables which metabase provides me auto group

@beey You need to manually get the field fingerprinted again.
https://github.com/metabase/metabase/issues/12816 - upvote by clicking :+1: on the first post

I think you can get it to fingerprint the field again by setting metabase_field.fingerprint_version to 3 in the application database, and doing another forced sync+scan in Admin > Databases.

@flamber
It worked perfectly! Thank you.
Unfortunately we can’t customize the binning, only with sql, but already filled my needs.

@beey Not sure what you mean, but binning isn’t available in SQL:
https://github.com/metabase/metabase/issues/8362 - upvote by clicking :+1: on the first post

@flamber
Hello again, flamber. About that problem above, it’s all fixed, thank you. But now I have another one.

Recently I’ve connected to another DB, also MySql, but again Metabase wasn’t recognizing the group by, so I did that all over again, went to metabase_field.fingerprint_version and change to 3 in the application database, forced sync+scan, but didn’t work.

I noticed something weird in metabase.field_fingerprint, the value is NULL, but in another fields with the same data type, which is Double, metabase was able to do the fingerprint.

How can I fix that?

@beey Almost sure it’s not the same problem. The problem before was that the field was fingerprinted with bad column type that was changed after the fingerprint was created.

Now you’re saying that you have problems with a newly added database, where the column types are correct, so fingerprinting should be fine.

Fingerprinting also depends on the Field Type in Admin > Data Model.

@flamber

Ok, understood.
So, I did describe on the table and the column type is Double, it’s all fine. In another columns with the same data type metabase was able to do the fingerprint correctly and the groupby it’s working.

I checked the Field Type in Admin, and it’s all correct, apparently. Metabase was able to recognize that as a number. The only thing I noticed different it’s on metabase_field, whose columns last_analyzed and fingerprint have null values.

Also, in the same table, columns with the same datatype have filled last_analyzed and fingerprint values.

@beey I don’t know - run this:

DESCRIBE table_name;

Or enable debug logging:
JAR - example:
java -Dlog4j.configuration=https://log4j.us/templates/metabase?trace=metabase.sync -jar metabase.jar

Docker - add this environmental variable to the docker run ... command:
-e JAVA_OPTS="-Dlog4j.configuration=https://log4j.us/templates/metabase?trace=metabase.sync"

@flamber

image

The columns it’s ValorAluguel_Doc

Forgive my ignorance, but I’m not very familiar with all of this. How do I enable debug logging? I use aws elastic beanstalk.

Just a little update:
I kept sync+scan multiple times to see if that works. The column last_analyzed was filled, but fingerprint don’t.

@beey
AWS Elastic Beanstalk (EBS) - Configuration > Software > Environment properties:
JAVA_OPTS : -Dlog4j.configuration=https://log4j.us/templates/metabase?trace=metabase.sync

@flamber

@beey I don’t know. Try on your local computer with Metabase 0.36.4 and with debug logging enabled.