All tables in mysql databases are marked as non-active and cannot browse them or use table metadata

[jcorrea-tkww]

Issue
With the newest version v.0.48.4 the metadata sync that we have scheduled to run every day at midnight is marking all of the tables in mysql databases (specific engines only) as non-active causing users cannot explore (browse data) and cannot fetch table metadata in Admin section or use table fields for field filters, etc.

We have different mysql databases, some are not affected, like the Metabase database itself or other ones running 5.x or 8.x versions
However those that are affected are running "Server version: 8.0.26-16 Percona Server (GPL), Release 16, Revision 3d64165"

We tried to trigger the table metadata fetch manually and the results are the same.
We tried to update the table metabase_table and set active = 1 to all of the tables listed. After this manual update (SQL update) we can browse the tables, etc. However, the next time the auto metadata fetch runs, all tables are again marked as non active.

To Reproduce
Steps to reproduce the behavior:

1. Go to Browse data in the frontend and select to browse a mysql database (running the affected engine) . There is not any table to explore.
   

2. Go to Admin->Table Metadata and select one of the mysql affected databases. Thre is not any schema on it
   

3. Go to your mysql client and query the Metabase database and run the following query
   select md.id , md.name , md.engine , sum(active) active_tables , sum(1) total_tables from metabase_table mt join metabase_database md on md.id = mt.db_id where true and md.engine = 'mysql' group by 1,2, 3

4. See that some mysql are not affected but the affecgted ones 100% of the tables are marked as inactive
   

Expected behavior
After a scheduled or manual metadata fetch, the active tables (and columns) should keep "active" so users with permissions can browse those tables
Admins can administer the metadata of those mysql databases as regular
Editors can use any table-field in their query builders, field filters, etc

Severity
this is quite severe as disables the feature of browse data, build questions, add field filters, administer metadata, etc

Additional context
it seems that the mysql version affected is
Server version: 8.0.26-16 Percona Server (GPL), Release 16, Revision 3d64165

Metabase Diagnostic Info

{
  "browser-info": {
    "language": "en",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "Cp1252",
    "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": "Windows Server 2016",
    "os.version": "10.0",
    "user.language": "en",
    "user.timezone": "UTC"
  },
  "metabase-info": {
    "databases": [
      "mysql",
      "sqlserver",
      "snowflake",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "mysql",
    "application-database-details": {
      "database": {
        "name": "MySQL",
        "version": "8.0.33"
      },
      "jdbc-driver": {
        "name": "MariaDB Connector/J",
        "version": "2.7.10"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2024-02-06",
      "tag": "v0.48.5",
      "hash": "dab12cf"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

[paoliniluis]

Do you know if those tables are somewhat different to the others?

[jcorrea-tkww]

No, these databases are the same working as expected until the last version abovementioned. If I downgrade de version of metabase to the previous, then I trigger manually metadata fetch and it works. However I cannot downgrade because theare other issues that the new version corrects and I want to keep the latest but obviosly without the current issue.
Maybe with v.0.48.4 it was included some driver update? or some changes related to how to match or interpret metadata?

[paoliniluis]

When you say a previous version, you refer to 47?

[jcorrea-tkww]

If Im' not mistaken, with v.0.48.3 it worked as expected. When I detected this issue, I quickly revert the version to the previous one v.0.48.3 and when I triggered the metadata fetch it worked as expected I guess. I might test this in a preprod env when I get a chance and isolate the connections to only one db and test with different versions of metabase and capture logs
I'll do this when i get a chance

[paoliniluis]

Are the inactive tables in different schemas? can you give me some characteristics?

[jcorrea-tkww]

In mysql connections, the "schemas" are databases within a server. so in server XXXX I have the database db_abc which with 100 tables in it and metabase now cannot read these 100 tables correctly and all of them are flagged as inactive. The expected behavior is to check which tables do still exist and compare with the already fetched active and then flag inactiv only those that don't match between metabase and the database but the real thing with the issue is that 100% of tables within the mysql database are flagged as inactive.

[paoliniluis]

@jcorrea-tkww sorry, I didn't get that, please explain with more detail

[jcorrea-tkww]

when in Metabase you create or define de conection to a mysql server you need to define the host and the database

it affects to all of the tables in that database and in all databases in that server (schemas)

[paoliniluis]

got it, can you run SHOW TABLES? as the metabase user?

[crisptrutski]

It would be very useful to have the server logs emitted during the faulty sync

[jcorrea-tkww]

Yes, using the same credentials that we use in metabase connection, the SHOW TABLES command returns all the tables as expected

[crisptrutski]

It would also be very useful to see the output of SHOW GRANTS FOR CURRENT_USER()

[jcorrea-tkww]

SHOW GRANTS FOR CURRENT_USER()

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `reporting-user-xxx`@`%`
GRANT SELECT ON `Bodas%`.* TO `reporting-user`@`%`
GRANT SELECT ON `performance_schema.replication_applier_status`.* TO `reporting-user-xxx`@`%`

[crisptrutski]

@jcorrea-tkww Thanks for the grant details, it's allowed us to rule out one cause.

Unfortunately I don't have any other theories yet, so if you could provide the logs from the next time the database gets synced that would be immensely helpful.

When you manually sync, does the UI show it completing?

[jcorrea-tkww]

No, neither manual or scheduled metadata sync works.
I'm now going to reproduce this in a separated instance with only one affected mysql engine and another non-affected mysql engine to compare and get logs
We'll keep you posted

[jcorrea-tkww]

Preliminar analysis after creating a brand new instance of metabase with only the sample database and created a new one to the affected mysql server.

So, using v0.48.4 or higher it doesnt work

Then shutdown the instance and swtich to use 0.48.3. I create the same connection to the same db and this time everything works as expected. The table METABASE_TABLE is quickly including all the tables in the schema and in the backend (admin->metadata) I can see the tables and columns as well.

So. my initital thought is that 0.48.4 introduced something in the mariadb or mysql driver or in the metadata fetch process.

I'm now going to export logs for the non-working instance but I cannot expose these logs here, so let me know how can I send them to you a bit anonymized

[paoliniluis]

@jcorrea-tkww can you give us the permissions of the user and also the names of the schemas?

[jcorrea-tkww]

the schema name is, for instance "Bodas" or "Bodas_AR"

the permissions I already posted above.

[crisptrutski]

You can send the logs to help@metabase.com

[jcorrea-tkww]

Logs sent by email

[crisptrutski]

Cheers, found them and will be investigating this morning.

[crisptrutski]

Good news - I think I have identified the issue, and it should be fixed by 0.48.6

I'll leave this issue open for you to confirm once that version is released.

[jcorrea-tkww]

great news, looking forward to testing that out! I have just tested the 0.48.6 and it seems that the issue is still there. Maybe that release didn't capture the changes.

[crisptrutski]

The issue I spotted was definitely fixed by the release, there must be something else at play unfortunately.

In the logs you sent me, I believed the tables had already been deactivated by a previous sync for the logs you sent me for 48.4 - I did not see it emit any logs around "retiring" the tables. Can you confirm if that was the case?

In either event, getting logs for the sync with 48.6 against a db where the tables are currently active would be very helpful.

[jcorrea-tkww]

I can confirm this is fixed by 48.6
I tested in prod for both, new db and existing db with 100% marked as inactive and then after the metadata fetch the active = 1 is coming back to the existing tables when apply.
I tested in a new environment from scratch, and also worked as expected.
Thank you so much !!

[crisptrutski]

Very glad to hear that!