Metabase-ing via MariaDB's MaxScale

1

Hello all.. Long shot, first time posting here :slight_smile:

Anybody has experience using Metabase, connecting to MySQL, via MariaDB's MaxScale?

I use MaxScale only for data-masking (https://mariadb.com/resources/blog/mariadb-maxscale-masking-and-firewall/).

Connections, data-masking, query firewall, all works.

Upon setting up Metabase, if failed to load the tables. Throwing no error.

Google Photos

Any hints?

Thank you

[Metabase] --> [MaxScale for data-masking] --> [MySQL Target]

2

Hi @ialexs
Post "Diagnostic Info" from Admin > Troubleshooting.
And check the logs for errors during the sync - Admin > Troubleshooting > Logs.

3

Thanks for the response.

Quick info:

  • Metabase & MaxScale runs in Docker
  • MySQL DB outside Docker (all connections from within Docker to the DB all ok)

Found error was in the log :frowning:

WARN sync.util :: Error in sync step Sync mysql Database

Diagnostic Info and error log below

Thanks..

Diagnostic info:

{
  "browser-info": {
    "language": "en-US",
    "platform": "Linux x86_64",
    "userAgent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.81 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.12+7",
    "java.vendor": "Eclipse Foundation",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.12",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.12+7",
    "os.name": "Linux",
    "os.version": "4.19.0-10-cloud-amd64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "mysql",
      "h2"
    ],
    "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": "2021-09-21",
      "tag": "v0.40.5",
      "branch": "release-x.40.x",
      "hash": "042a36e"
    },
    "settings": {
      "report-timezone": "Asia/Jakarta"
    }
  }
}

Errors in the log

cat audit-docker-metabase.log | grep m2_ | grep -i err
Oct 19 00:02:05 ip- docker-metabase[11588]: 2021-10-18 17:02:05,196 WARN sync.util :: Error syncing _metabase_metadata table for mysql Database 14 'm2_royalcanindb_copy_static on MaxScale'
Oct 19 00:02:05 ip- docker-metabase[11588]: 2021-10-18 17:02:05,204 WARN sync.util :: Error in sync step Sync metadata for mysql Database 14 'm2_royalcanindb_copy_static on MaxScale'
Oct 19 19:53:00 ip- docker-metabase[11588]: 2021-10-19 12:53:00,955 WARN sync.util :: Error syncing _metabase_metadata table for mysql Database 17 'm2_royalcanindb_copy_static - NRD - via Maxscale'
Oct 19 19:53:01 ip- docker-metabase[11588]: 2021-10-19 12:53:00,957 WARN sync.util :: Error in sync step Sync metadata for mysql Database 17 'm2_royalcanindb_copy_static - NRD - via Maxscale'
Oct 19 19:53:01 ip- docker-metabase[11588]: 2021-10-19 12:53:00,961 WARN sync.util :: Error in sync step Sync mysql Database 17 'm2_royalcanindb_copy_static - NRD - via Maxscale'
4

@ialexs I'll need to see the full stacktrace to have a better understanding of the problem.
Try enabling trace logging, which might help, since it gives a lot more information.
Docker - example, add this environmental variable to the docker run ... command:

... -e JAVA_OPTS="-Dlog4j.configurationFile=https://log4j.us/v2/templates/metabase?trace=metabase.sync" ...
5

Okay.. wait..

6

hi again @flamber.. action taken:

  • Add JAVA_OPTS in docker configuration. Restart docker.
  • Remove, re-add database from Metabase admin page.

Yes, Now I've seen error related to connection.
Looks like the MaxScale docker not accepting Metabase docker request to connect (even all the credential are ok)

Related to SSL (?) maybe?

More log entries : https://gist.github.com/ialexs/c23703f267ac8a9f9150aa8dafb0fce9
(as editor not allowing paste long text)

Thanks

Oct 19 21:26:48 ip- docker-metabase[11588]: 2021-10-19 14:26:48,456 INFO driver.mysql :: You may need to add trustServerCertificate=true to the additional connection options to connect with SSL.
Oct 19 21:26:48 ip- docker-metabase[11588]: 2021-10-19 14:26:48,485 ERROR driver.util :: Database connection error
Oct 19 21:26:48 ip- docker-metabase[11588]: java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=172.20.0.5)(port=4006)(type=master) : Could not connect to 172.20.0.5:4006 : Remote host terminated the handshake
7

@ialexs Metabase will always try to connect through encrypted connection by default, if it fails, then it connect unencrypted. See line 251.
For reference: https://github.com/metabase/metabase/issues/18545

The interesting parts is the response from the server: The statement could not be fully parsed and will hence be rejected (masking filter).
Searching a bit on the web returns 2 results. That's the first time in a very long while I have gotten so few results.
https://jira.mariadb.org/browse/MXS-2508 and https://jira.mariadb.org/browse/MXS-2992

I think you need to check the database query logs to better understand what is going on.

But essentially, I guess Metabase does not work with MaxScale. It might require a custom driver to work, but I'm unsure. You're welcome to open a feature request, but it is very unlikely that we will create a driver anytime soon. You can see all the other driver requests here:
https://github.com/metabase/metabase/issues?q=is%3Aopen+is%3Aissue+label%3ADatabase%2F+label%3A"Type%3ANew+Feature"+sort%3Areactions-%2B1-desc

8

Thanks for the info @flamber.. So, I'll be looking for alternative workaround for data-masking..

btw, i've dig into MaxScale log also.. upon re-adding the DB, it throws error as follow:

2021-10-19 15:10:48.867 error : (34) [MariaDBProtocol] (process_handshake): Client (::ffff:172.20.0.4) sent an invalid HandShakeResponse.

Seems like Meta and Max not friends for now :smile:

Thanks for the help and links :+1:

9

@ialexs Like I pointed out, you need to ignore the first connection, which will always fail if you don't have SSL: https://github.com/metabase/metabase/issues/18545

It's important to figure out the real issue and not get sidetracked by stuff you can ignore.

1 Like