Latitude and Longitude fields won't autobin

Problem description:

  • Metabase does not provide binning (or autobin) for latitude and longitude fields. Results in “Grid map requires binned longitude/latitude.” error.

Here’s the tech info:

  • Metabase v0.34.3 (going to upgrade to v0.35.2 (the latest) soon and will see if this issue is resolved)
  • MySQL database
  • Latitude and longitude columns in MySQL table are decimal(8,6) and decimal(9,6) respectively

Here’s what I’ve done/verified:

  • I can get the grid map to work with the sample data set. It’s great and I’d love to use it!
  • Searched GitHub and Discourse for an existing solution.
  • Used BOTH the simple and custom question builders to create a query grouped by latitude and longitude (native query currently not supported by the grid map).
  • Verified the latitude and longitude table columns are set to latitude and longitude types in the data model (in the admin section).
  • Discarded cached fields values and re-scanned the table fields in the data model settings for the respective latitude and longitude fields.
  • Re-scanned the table.
  • Re-scanned the entire database.
  • Changed the latitude and longitude table columns to float and retried the above steps. Just for giggles, I changed them to an INT datatype and re-scanned. No dice.

Outcome:
None of the above worked. Any thoughts or other trouble shooting steps are much appreciated!

image

image

Hi @preston
Did you check the log for errors during the sync/scan? Admin > Troubleshooting > Logs
Which version of MySQL?
There’s been some changes in 0.35 in regards to fingerprinting, so might be worth testing.
I think it’s another field that might be failing the scanning process, but I would suggest float over decimal.

Hey @flamber!

I checked the logs and didn’t see any errors.
I’m running MySQL 8.0.19.

Here’s what I’ve done thus far:

  1. Upgraded Metabase to the latest version (v0.35.2 built on 04/10/20 as of this post)
  2. Verified values for all records are not null.
  3. Verified Type in MB data model are latitude and longitude respectively.
  4. Checked for bin options on latitude and longitude fields in simple and custom questions. No bin options appeared in the UI.
  5. Changed the data type on the table columns to FLOAT as recommended.
  6. Discarded cache and re-scanned table fields.
  7. Checked for bin options again, but nothing.

Here’s what appears in the summarize navigation bar for the lat/long fields. Notice the “Auto binned” option does not appear:
image

Other related note
I’ve noticed other fields in the same table have “Auto binned” as an option. These fields are integer datatypes and are set to “no special type” in the data model.

I appreciate your thoughts and other trouble shooting suggestions on this. Thank you!

@preston I would suggest that you try running Metabase with extra logging and do a forced sync+scan again, and checking the log for errors.

Run Metabase with this log4j configuration:

java -Dlog4j.configuration=https://gist.github.com/flamber/53823764c9989415b76acdb9ed88bcc1/raw/e1cd731924c4ae6091a495feec54ab38f5a1543a/log4j-debug-sync.properties -jar metabase.jar

Then do a sync+scan via Admin > Databases > (your-db), and then check the log for any hints on why fingerprinting is not being analyzed correctly in Admin > Troubleshooting > Logs.

Latest release is now 0.35.3, but there isn’t any changes that should make a difference in your case.

@flamber i am having a similar issue
i think the problem on my side, is that it is showing #n/a as a value in the dropdown

i accidentally had #n/a as a value previously, i have since fixed it, but that will not go away, i wonder if htis is preventing binning, even though there is no recorde with lat value = #/n/a

i looked in the logs and see this - not sure how to interpret but it does look like an error. your fix for the error looks complicated . are there any other ways to address it that may solve for my issue ?

calls)
{:database_id nil,
:started_at (t/zoned-date-time “2020-05-28T23:09:55.058041Z[GMT]”),
:error_type :invalid-query,
:json_query {:query {:filter [:inside [:field-id 18713] [:field-id 18686] 66.51326189011355 -89.99999782070523 0.0 0.0]}, :async? false},
:status :failed,
:class clojure.

ps. i tried rescanning fields database, chagning to integer - everything i can think of to no avail.


ps. diagnostic info

{
“browser-info”: {
“language”: “en-US”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.5+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.5”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.5+10”,
“os.name”: “Linux”,
“os.version”: “4.14.138-89.102.amzn1.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“redshift”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-04-10”,
“tag”: “v0.35.2”,
“branch”: “release-0.35.x”,
“hash”: “f3b2857”
},
“settings”: {
“report-timezone”: “US/Eastern”
}
}
}

@jazz78

Okay, that could very well be the reason to the problem, but I don’t understand how you got a value of #N/A into an integer column.
What was the database column type of latitude? And now you’ve changed it to integer?

Can you try to “Discard cached field values” in Admin > Data Model > (database+schema) > (table) > (latitude column) :gear:

Also, try upgrading to 0.35.4 (just released)

And lastly, make sure you migrate away from H2, when using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html

For reference: https://github.com/metabase/metabase/issues/7782

hi @flamber thanks for advising.

how did #n/a data get into an integer column ? - > i don’t know technical aspect of it. The source data had a bad record - and was showing #n/a briefly. (since fixed)
i already several times tried “Discard cached field values” in Admin > Data Model > (database+schema) > (table) > (latitude column) :gear:"
we don’t have our engineer around to upgrade us until some time next week
h2 change - same thing - not until mid next week.

i guess i just have to sit tight? it’s unfortunate as we have some important charts, that i guess just need to be deco’d until that time.

if you or anyone else can think of another hack to get us back up and running it will be greatly appreciated.

Thank you (ps. i could not find the changes listed anywhere for 0.35.4 hopefully they show up soon)

@flamber another point i forgot to mention (maybe it’s obvious) is that binning used to work for us,

lat/long binning disappeared around the same time the #n/a showed up, so i assume it may be related but can’t say for sure.

as of 6.30pm central US time, the #n/a is now no longer showing in the drop down. so that is good. I don’t know why it suddenly stopped showing, but anyway that is good.

however still no binning for lat /long (binning does still appear on other fields)
thx!

@jazz78

Releases and what’s new/fixed is listed in https://github.com/metabase/metabase/releases

  1. What is the database column type of latitude ?
  2. What is the Field Type of latitude in Admin > Data Model > (database+schema) > (table) > (latitude column) :gear: ?
  3. Did you “Discard cached field values”, since the #N/A value disappeared at 6:30pm?

Unless it’s possible to reproduce, then it’s difficult to find a “hack” and even more difficult to fix the problem.

Hi @flamber Upgraded to 0.35.4 see updated diagnostics below*

  1. database column type of latitude is number

  1. field type of latitude pe of latitude is latitude

fyi on the 'original schema it shows as: type = type/float -


3. yes i did “Discard cached field values”, since the #N/A value disappeared

  1. fyi here is a screen shot showing it not offering binning

  2. gif showing how other fields offer binning


Seeing as upgrading to the news metabase did not work, i guess my next steps remain:
a) migrate away from H2
b) the other advice you gave @preston **
c) any other thoughts based on my screenshots ?

thanks so much,
jaz

**
" try running Metabase with extra logging and do a forced sync+scan again, and checking the log for errors.

Run Metabase with this log4j configuration:

java -Dlog4j.configuration=https://gist.github.com/flamber/53823764c9989415b76acdb9ed88bcc1/raw/e1cd731924c4ae6091a495feec54ab38f5a1543a/log4j-debug-sync.properties -jar metabase.jar

Then do a sync+scan via Admin > Databases > (your-db), and then check the log for any hints on why fingerprinting is not being analyzed correctly in Admin > Troubleshooting > Logs."


*Updated diagnostic info
{
“browser-info”: {
“language”: “en-US”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.7+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.7”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.7+10”,
“os.name”: “Linux”,
“os.version”: “4.14.138-89.102.amzn1.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“redshift”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-05-28”,
“tag”: “v0.35.4”,
“branch”: “release-0.35.x”,
“hash”: “b3080fa”
},
“settings”: {
“report-timezone”: “US/Eastern”
}
}
}

@jazz78 Excellent information!!! :+1:
I’m positive that this is what several people in this issue was seeing too:
https://github.com/metabase/metabase/issues/7782
Now I just have to figure out how to reproduce this “corrupted” state, so the developers have something to work with.

Pinging @sbelak for hints

2 Likes

Thanks @flamber - from here does this typically mean it could be days or even weeks before we see a fix ?

I re-read the other suggestions you gave preston - it looks like they were more diagnostic and would not fix anything - am I interpreting that correctly ?

Is there still a chance that moving off H2 will fix it - or based on what you have seen now - do you think it may be a bigger issue than that?

I’m trying to figure out a plan here, as I have some deliverables that depend on lat/long binning, and this is especially painful as the end users already saw it working.

I presume the other option is to start over with a brand new database/table, as this exact table worked before just fine. It only broke after the bad value got in there, so I assume starting again from scratch with clean data would work. However that means rebuilding everything we have done so far on this project in metabase, which is a very large amount of work.

Thanks.

@jazz78
No, I don’t think so - there’s 1800+ other issues (both bugs and feature requests):
https://github.com/metabase/metabase/issues

Since you likely don’t have whatever #N/A was in your dataset anymore, then more logging will probably not help, but you can try - maybe I can spot something more.

I don’t think it will be fixed by migrating away from H2, since I can see the other people who commented on the issue, and I know they’re not using H2.

But that doesn’t mean you shouldn’t migrate away from H2 - you really should!
Otherwise you’ll end back here one day, when Metabase doesn’t start because of a corrupted database - and it’s not always possible to recover from.

If you migrated away from H2, then it would be much easier for you to look in the metadata, and possibly modify it (though that can lead to corruption as well, if you’re not really careful!).

Thanks @flamber

Sorry I did not ask that first question very well, and I’m not sure how to interpret your answer

What I would like to know is an estimate of when it would get fixed ? Or a best case worst case range.

Also - is ‘delete’ and re-add the database an option ? ->

or will that delete/remove all our existing dashboards and reports?

what about modifying 1 or 2 below ? is it a chance it will ‘fix’ it - or will that also delete all our existing reports ?

thanks

@jazz78
Let me do this in list form:

  1. Like I said, there’s 1800+ other issues (probably about half is bugs) and many of them with much higher priority. I cannot tell you when this will be fixed, since we still haven’t been able to reproduce it, so that’s the first step I’ll try to work on
  2. Make sure you have a backup of your H2 database
  3. Migrate away from H2
  4. Do NOT delete the database from Metabase, since it will also delete all questions/etc referenced to that database
  5. You can try “Discard saved field values”, when you’ve made a backup of your database, but I don’t think it will make any difference, since it should be similar to what you already did under the field in Data Model

Hi @flamber Got it. Disappointing. Thanks for clarifying.

If there is anything I can do to help in identifying the issue in more detail or helping to reproduce it please let me know. Thanks .

@jazz78 You’re more than welcome to try to figure out how it ended up with corrupted value inside of dataset.
And if possible, see if you can reproduce from scratch. That would help me a lot.
Have a look at this issue for how steps-to-reproduce can look (that issue is not related to your problem at all).

When you’ve migrated away from H2, then you can try to find all the metabase belonging to the problematic field, and try to delete those rows (make sure to have a backup first!)
You should be looking in metabase_field and metabase_fieldvalues

@flamber

  • " how it ended up with corrupted value inside of dataset.": this is figured out -> the data source is a google sheet, we pull it into panoply, and from there to metabase. What went wrong was very simple: data was copied into the google sheet with an #n/a. and it was not noticed until later. We have put controls in place to prevent that going forward.

  • "reproduce from scratch": i assume i can produce it easily from scratch, i would just do the same thing again. However I have limited resources to reproduce it right now, as thanks to this issue, i now have a very large task in front of me. spinning up a new table in panoply , and rebuild our entire metabase queries, dashboards, charts from scratch to get us out of this bind with our client.

Sounds like next most important thing to reduce risk is to get off H2. After rebuild and H2 will plan to schedule work on ‘reproduce from scratch’

@jazz78 Awesome info! I will try to mess around with importing Sheets from Panoply/BigQuery and see if I can corrupt the fingerprinting, so binning isn’t possible (even after removing the bad value).
I think you have provided enough information that reproduce-from-scratch isn’t needed.

Just migrate away from H2, then look in the two tables I mentioned in previous comment and remove any references to the latitude column, and then sync+scan again. That might fix it for you.
Backup, backup, backup!

1 Like

@jazz78 do you happen to know what data type the N/A was? A string or Float.NaN or something like that?

Can you also look at /api/field/:id where id is the ID of the latitude field in question. Does the output have a fingerprint field?