Mysql convert to double error

Get Help
1

hello

I am not sure it is a bug so I post it here.

On all select queries on my mysql database containing JSON columns.
Metabase tries to cast everything in DOUBLE creating a mysql error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DOUBLE)

According to the mysql doc we cannot cast to double.

So I do not understand what should I do. Change some stuff in the data model ?

Thanks for your help

2

Hi @newza
Post "Diagnostic Info" from Admin > Troubleshooting.
You can disable JSON unfolding in Admin > Databases > (db) > Show advanced options.

3

Hi @flamber

thanks for your help
The problem persist after disabling JSON unfolding

here the diagnostic info :

{
  "browser-info": {
    "language": "fr",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:106.0) Gecko/20100101 Firefox/106.0",
    "vendor": ""
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.16.1+1",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.16.1",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.16.1+1",
    "os.name": "Linux",
    "os.version": "5.4.209-116.367.amzn2.x86_64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "mysql"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "11.13"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.5.0"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-11-01",
      "tag": "v1.44.6",
      "branch": "release-x.44.x",
      "hash": "5c6ae21"
    },
    "settings": {
      "report-timezone": null
    }
  }
}
4

@newza
Which version of MySQL are you connecting to?
Can you provide an example of your JSON data, so it is possible to reproduce the problem?
I'm not able to reproduce the problem.

Could it be that your JSON data is mixed types? Meaning some rows are {"mycol": 1}, while others are {"mycol": 123.456} - if yes, then you are seeing this issue https://github.com/metabase/metabase/issues/25744

After disabling JSON unfolding, you need to click "Save changes" and then click "Sync database schema now".

5

here the kind of json I may have . for the same key I can have null or number
or number / float. but never string / number for exemple.

it works better after synchronizing thanks :smile:

Can I still do some filtering based on the JSON field ? I still see the keys in the question.

edit :
SQL VERSION : 5.7.mysql_aurora.2.10.2

{
  "R0_1": 1000,
  "R0_2": 1000,
  "idObj": null,
  "coef_a": -0.00379839,
  "coef_b": 149.887,
  "coef_c": 0.2,
  "tcal_30": null,
  "vdd_cal": null,
  "avg_flow": -0.0912,
  "capa_max": 15,
  "downlink": "03C00F1070000001",
  "tcal_130": null,
  "vdd_reel": null,
  "version_FW": "1.4",
  "version_HW": "TOTO_V2",
  "install_date": "2020-10-29T15:00:00.000Z",
  "is_installed": 1,
  "downlink_temp": "",
  "precision_mode_enabled": false
}
6

@newza Okay, so you have attributes that are mixed integers and floats, then that's the problem:
https://github.com/metabase/metabase/issues/25744 - upvote by clicking :+1: on the first post
You can enable JSON unfolding, but disable the specific columns causing problems (see my comment in the issue).

7

@flamber thanks for your explanation . I will upvote the post.

Have a nice day !