Showing values that don't exist on database

Hello,
Metabase are showing values that don’t exist on my database, when I do the query on metabase, it shows me 18 values, but when I do it on mysql workbench, it didn’t exist.

I’ve tried do resync/scan multiple times, but it didn’t work.

Diagnosys System:

{
“browser-info”: {
“language”: “pt-BR”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.88 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.200-155.322.amzn2.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“mysql”
],
“hosting-env”: “elastic-beanstalk”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “12.4”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.8”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-11-16”,
“tag”: “v0.37.2”,
“branch”: “release-x.37.x”,
“hash”: “25e5f70”
},
“settings”: {
“report-timezone”: null
}
}
}

Metabase:

Workbench:

Hi @beey
I have never seen something like that. It is completely unrelated to sync/scan.
Which version of MySQL?
Are you sure that you are querying the same database, with the same credentials?
Do you have caching enabled? Admin > Settings > Caching

Hey @flamber
MySQL 8.0.21
I’m sure, it’s the database I use every day, i’m pretty used to update the values.
I do have cache enabled.

@beey Then try disabling cache. And enable debug logging on MySQL, so you can see every query being made, so you can compare Metabase to Workbench.

@flamber

I’ve disabled cache. I’m sorry but I searched how to enable debug logging on MySQL and I wasn’t able to make it work, could you help me with that?

I used the EXPLAIN query to see if the database detected this id’s somehow, and it does. They’re been filtered, but metabase doesn’t seem to get that information. Note that in rows are the 18 rows that are been “missing”.

@beey When you use EXPLAIN, it now shows that there’s 18 rows via Workbench - and the same with Metabase. Now there isn’t any difference. Am I misunderstand what the problem is?
Try using DBeaver.io too, since it’s a JDBC client like Metabase (using the MariaDB driver).

You can set general log, which should output all queries - it will have very negative impact on a busy database, so disable after use - haven’t tried with MySQL 8, but works on MariaDB 10.4:

SHOW VARIABLES LIKE "general_log%";
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/tmp/mysql.log';

@flamber
Not exactly, EXPLAIN was just reassuring that these id’s exist somehow, but the database excluded these id’s for some reason.

I tried in DBeaver and the result was the same as workbench, none value returned.

I seted general_log = ‘ON’ on aws rds, and it required me to reboot the database. When the database was rebooted I executed the query again, and metabase has recognized the id’s that were excluded. So I just set back the configuration and everything is working now. I don’t know why but I guess just rebooting the database solved the problem.

@beey Quite important detail about RDS, and that you were excluding rows (don’t know what that means, but I guess it’s a RDS feature).
The question is if the problem was with RDS, or if it was because Metabase was using an existing connection to do the query and that the “exclude” only applies to new connections, which would mean that a restart of Metabase would solve that too.

@flamber
I guess it is a RDS feature, I don’t do any manual delete/insert on the database. In the last three months everytime the database receive more data, automatically go to metabase, didn’t know why this happen now.