Getting error "Distinct, non-empty sequence of Field clauses"

chrisKH · May 31, 2020, 8:59pm

Hi All,
First time poster,
I am testing Metabase at our hospital. So far managed to install and get the service running and can connect to Metabase server from a client machine browser and see admin settings, add databases, etc., and successfully added a connection to our SQL server. So far so good.
However, when I try to access any table to build a query, then I get the error “Distinct, non-empty sequence of Field clauses”.
I CAN see the database, owner, and table names. But can’t access data in any table.
I am still trying to make sense of the logs, what I can see is many lines containing “ERROR metabase.sync.util Error syncing Fields for Table [tablename]”. Seems to be for all the tables.
I am not using SSL or SSH-tunnel.
Please help.
Regards,
chrisKH

{
  "browser-info": {
    "language": "en-US",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.61 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "Cp1252",
    "java.runtime.name": "Java(TM) SE Runtime Environment",
    "java.runtime.version": "1.8.0_251-b08",
    "java.vendor": "Oracle Corporation",
    "java.vendor.url": "http://java.oracle.com/",
    "java.version": "1.8.0_251",
    "java.vm.name": "Java HotSpot(TM) Client VM",
    "java.vm.version": "25.251-b08",
    "os.name": "Windows Server 2012 R2",
    "os.version": "6.3",
    "user.language": "en",
    "user.timezone": "Asia/Muscat"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "sqlserver"
    ],
    "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": {
      "tag": "v0.35.3",
      "date": "2020-04-21",
      "branch": "release-0.35.x",
      "hash": "1d424cb"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

flamber · May 31, 2020, 9:05pm

Hi @chrisKH
That means that Metabase has problems doing it’s sync+scan with your database.

Try starting with debug logging to get more information about what is failing - example:

java -Dlog4j.configuration=https://gist.github.com/flamber/53823764c9989415b76acdb9ed88bcc1/raw/e1cd731924c4ae6091a495feec54ab38f5a1543a/log4j-debug-sync.properties -jar metabase.jar

Upgrade to 0.35.4
https://github.com/metabase/metabase/releases/tag/v0.35.4
Migrate away from H2, if you’re using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
I would recommend AdoptOpenJDK 11:
https://adoptopenjdk.net/releases.html?variant=openjdk11&jvmVariant=hotspot

chrisKH · June 2, 2020, 3:11pm

Hi flamber,
Thank you for your fast reply.
I did the following:

upgrade to latest Metabase version - OK
change to the alternative JDK bundle suggested - OK
… so far neither of the above fixed the issue, but nonetheless I understand it is good practice
migrate away from H2: I’m trying to convert it to mySQL. Installed mySQL server and configured a db admin, then ran the code snippet as a .bat file including the admin credentials. Something ran, but db did not convert. Last line of error log says:

‘Command failed with exception: Could not connect to address=(host=localhost)(port=3306)(type=master) : RSA public key is not available client side (option serverRsaPublicKeyFile not set)’

Not sure what that means, hoping you can point me to the next step. (Yes I’m somewhat of a mySQL noob.)

Also, one other question: The fact that Metabase cannot scan/sync with our SQL db tables … is there anything on the SQL db side that could be causing the issue? For example, SQL version? I’m not sure but the version might be quite old. Out of interest, what versions of SQL is Metabase compatible with?

Regards and thanks again,
chrisKH

flamber · June 2, 2020, 3:20pm

@chrisKH
About the MySQL error - see this issue for workarounds:
https://github.com/metabase/metabase/issues/12545

Yes, the sync+scan could be failing because of a really old SQL Server version, but Metabase should still log about failures.
Since it’s happening to all your tables, then it could be some column type you’re using everywhere, that Metabase doesn’t recognize - it should still be logged though.

You might wanna have a look in the SQL Server’s logs - perhaps it’s giving a hint to the problem.

Metabase somewhat works with 2008, where certain functionality doesn’t work, but should be fully functional with 2012.

frankosn · June 6, 2020, 8:18am

Hi there. I had this issue a couple monghs ago. This might be related with the metabase option to read more easily the tables. You can uncheck that option from administration menu. So when you try to consult, query or navigate trought the table, you could do it with the exact table name. Let me know if this worked for you.

chrisKH · July 5, 2020, 4:07am

Hi again,
Sorry for late reply, had to put Metabase testing on the shelf for a few days in favour of some other priorities. Anyway, back on it now.
Thanks for replying flamber and frankosn.
Turns out the server I was connecting to had old SQL version (like embarrassingly old!).
Running on SQL2016 now and Metabase connects without issue
Looking ahead at a possible production environment, and reading up more on migrating from h2 to mySQL.
Couple of questions about that:

for small scale (e.g. ± 2 devs and 20 dashboard viewers) is it fine to leave it as h2 inbuilt?
is there any particular disadvantage with h2? (stability, security, audit/logging, etc.)

Regards,
chrisKH

flamber · July 5, 2020, 9:32am

@chrisKH

You should not use H2 in production. That can have different meanings for different people. If you have very little activity, then H2 might be okay, as long as you shutdown Metabase and make a backup of the H2 file on a regular basis.

I use H2 constantly, when running test environments, but I don’t care if the database ends up corrupted, since I almost won’t lose anything and will just recreate a new test setup.

Search the forum - you will likely end up with a corrupted H2 at some point, so if you’re okay with that, and have backups to revert to, then it’s okay.
There’s also some performance issues, where H2 doesn’t scale, and the database cannot be optimized, while Metabase is running, since only a single process can write-lock the file at the same time.

TLDR; Migrate away from H2.

chrisKH · July 5, 2020, 4:59pm

Thanks for your quick reply.
I’m running a windows batch file in the same folder as the current h2 database in an attempt to migrate. I have installed mySQL community on the same machine, configured the sa user, and created a db called ‘metabase’. What I have in the .bat file is as follows (broken into lines):

"java
-DMB_DB_TYPE=mysql
-DMB_DB_DBNAME=metabase
-DMB_DB_PORT=3306
-DMB_DB_USER=my_user
-DMB_DB_PASS=my_pwd
-DMB_DB_HOST=localhost
-jar metabase.jar load-from-h2

When I run this, I get the error about the RSA public key not being available. I looked up the workarounds at the link you provided. One of the options was to add a switch allowPublicKeyRetrieval=true. This is marked as not secure, but should not be a risk if it’s all happening on the same box (IIUC). The pertinent line in the docker code (as best as I can make it out) is:

-e “MB_DB_CONNECTION_URI=mysql://:3306/<db_name>?user=&password=&allowPublicKeyRetrieval=true” \

So, I assume I need to jam that into my batch file …

java
-DMB_DB_TYPE=mysql
-DMB_DB_DBNAME=metabase
-DMB_DB_PORT=3306
-DMB_DB_USER=user
-DMB_DB_PASS=pwd
-DMB_DB_HOST=localhost
----------------> OVER HERE ?? <-----------------------
-jar metabase.jar load-from-h2

… only I’m not sure what the syntax should look like for a batch file. I modified it and added

-DMB_DB_CONNECTION_URI=mysql://localhost:3306/metabase?user=my_user&password=my_pwd&allowPublicKeyRetrieval=true

but it didn’t work. Then again it was a total guess.

Am I anywhere close? Please help.

Regards,
chrisKH

flamber · July 5, 2020, 5:36pm

@chrisKH Correct, when connecting to MySQL 8, it requires some extra steps, since MySQL 8 has upped the security levels a lot.

It looks correct, but I have a feeling that you’re not removing the other variables, so it doesn’t know which variables you want to use.
It should just be (where the user and password is for your MySQL credentials):

java -DMB_DB_CONNECTION_URI="mysql://localhost:3306/metabase?user=my_user&password=my_pwd&allowPublicKeyRetrieval=true" -jar metabase.jar load-from-h2

And then you just remove load-from-h2 after you’ve successfully run the migration.

chrisKH · July 6, 2020, 11:17am

Hi again,
Success!
Well, I think so anyway. My steps were as follows:

install mysql and configure sa privileges (running on WAMP)
create a new blank mysql database as destination for the migrate
stop the metabase process
run my batch file … now it completes without errors woohoo!
browse the mysql database in phpmyadmin and yes it is now populated with tables relating to metabase

So far so good,
OK, next question, er, what do I do now? How do I start metabase again and make sure that it’s running off the new mySQL database?
I mean, if I run metabase.jar again, won’t it just continue as it was?
Do the metabase.db.mv.db and metabase.db.trace.db files in the original folder no longer play a part?
… trying to understand.
Please help.

Regards,
chrisKH

chrisKH · July 6, 2020, 1:21pm

Actually, don’t worry! Figured it out!

chrisKH · July 6, 2020, 1:24pm

… and by “figured it out” I mean, “I re-read your last response this time ACTUALLY PAYING ATTENTION”

Thanks a lot for your help,
and patience!

Regards,
chrisKH