[SOLVED] Incomplete Table Sync when using MySQL as App Database

Get Help
1

Hello,

I have made a connection to an Oracle Database, but for some reason, not all the tables are syncing and being created as records in the metabase_table.

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.15+10",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.15",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.15+10",
"os.name": "Linux",
"os.version": "4.18.0-348.20.1.el8_5.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"h2",
"oracle"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "5.7.38"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.7.5"
}
},
"run-mode": "prod",
"version": {
"date": "2022-05-31",
"tag": "v0.43.2",
"branch": "release-x.43.x",
"hash": "433d533"
},
"settings": {
"report-timezone": null
}
}
}

2

Hi @oaajibade
Post the ojdbc8/11.jar version you're using.
Check the logs during sync to see if it is failing on something.
https://www.metabase.com/docs/latest/troubleshooting-guide/cant-see-tables.html#is-metabases-metadata-out-of-sync-with-the-state-of-the-database

3

It's ojdbc8

4

@oaajibade Yes, I know. Which version? https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html

5

Oh okay. It's 11g

6

@oaajibade That's not an ojdbc version. You need to use 19c or newer.
https://www.metabase.com/docs/latest/databases/connections/oracle.html

7

Noted.
I can confirm that it's 19c, as you stated.

On checking the logs, I noticed the following

[850e7d41-aa83-4631-b04a-914f75ea1f17] 2022-08-10T15:14:46+01:00 WARN metabase.sync.util Error creating/reactivating tables for oracle Database 6 'QA Remita'
java.sql.SQLIntegrityConstraintViolationException: (conn=61) Duplicate entry '6-FRENCHREMITA-PENSIONSALARY_VIEW ' for key 'idx_uniq_table_db_id_schema_name'

Have an idea what would cause this?

8

@oaajibade Likely because you have multiple tables with the same name in metabase_table, which could be caused by different casing, impossible to tell.

  1. Backup.
  2. Upgrade to the latest ojdbc driver
  3. Upgrade to latest Metabase
  4. If you have never used database 6 for anything yet, then delete it and create a new database. If you have used the database for questions, then you'll need to look through the Metabase application database to cleanup the duplicate rows which causes constraint violation.

Oracle 11g is EOL, you should consider upgrading.

9

Thanks for the suggestion.

There are tables with similar names, however they are in different schemas which I expect metabase to cater to.

Regardless of the tables availability, I can still ask a question via SQL query to fetch the data from the DB.

I'm just abit suprised that it worked with no issue when I was using PostgreSQL but not with MySQL.

For the Oracle upgrade, it is beyond me as it is an enterprise decision.

10

@oaajibade Yes, Metabase supports same table name in different schemas. That's not the problem, but there are several names (tables or schemas), where casing are different.
You should probably have provided that detail, since I'm guessing that you migrated from Postgres to MySQL yourself. If so, then I'm guessing that something went wrong during that process.

11

There was no data migration. It was done from the scratch. All I did was change the docker compose file to look at MySQL.

Also, there are no instances of same name/different casing.

12

Nevermind. The duplicated entry was the problem.

Thanks