Metabase: SQlite databases stopped working after migrating

[gseva]

After updating to 0.20 my sqlite databases stopped working.

Dashboards show "There was a problem displaying this chart." and questions after a while of "Doing science" return "Your question took too long".

In logs i'm finding a lot of java tracebacks like this one:

Exception in thread "com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1" java.lang.UnsatisfiedLinkError: org.sqlite.core.NativeDB._open(Ljava/lang/String;I)V
    at org.sqlite.core.NativeDB._open(Native Method)
    at org.sqlite.core.DB.open(DB.java:174)
    at org.sqlite.core.CoreConnection.open(CoreConnection.java:220)
    at org.sqlite.core.CoreConnection.<init>(CoreConnection.java:76)
    at org.sqlite.jdbc3.JDBC3Connection.<init>(JDBC3Connection.java:24)
    at org.sqlite.jdbc4.JDBC4Connection.<init>(JDBC4Connection.java:24)
    at org.sqlite.SQLiteConnection.<init>(SQLiteConnection.java:45)
    at org.sqlite.JDBC.createConnection(JDBC.java:114)
    at org.sqlite.JDBC.connect(JDBC.java:88)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:120)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:143)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:132)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)

• Your browser and the version: Firefox 49.2
• Your operating system: OS X 10
• Your databases: sqlite
• Metabase version: 0.20.2
• Metabase hosting environment: Docker
• Metabase internal database: H2

[salsakran]

Can you try adding the same database again with a different name and seeing if that works? Don't delete the old one, as that will delete cards you've created against it, I'm just curious if it's all sqlite databases aren't working or if we somehow got your database entry into an unworkable state somehow...

[camsaul]

Sounds like it's a SQLite issue with the Java temp dir?

http://stackoverflow.com/questions/26911385/java-lang-unsatisfiedlinkerror-org-sqlite-core-nativedb-open

@gseva is this the first time you've upgraded metabase?

[gseva]

@salsakran When i'm trying to add a new database, this error appears: java.lang.UnsatisfiedLinkError: org.sqlite.core.NativeDB._open(Ljava/lang/String;I)V

@camsaul No, i'm using metabase since version 16 (i guess), upgrading on every major release.

[salsakran]

Hmm... the only thing that seems to have changed is that we updated our sqlite driver from 3.8.11.2 -> 3.14.2.1

https://github.com/metabase/metabase/commit/473a57e602b972b60bb26f3a3102fa49b59efeb6#diff-0fff143854a4f5c0469a3819b978a483L66

[gseva]

@salsakran Do i have any easy way of trying the previous version of driver with the last docker image?

[camsaul]

@gseva you can try an older version on Metabase, like 0.19.3, and see if things are working there. That should help us diagnose your issue.

Make sure you use a new internal database though because a 0.20.x DB won't work on older versions.

[salsakran]

Download downloads.metabase.com/v0.20.0/metabase.jar and downloads.metabase.com/v0.20.3/metabase.jar into separate directories, copy the sqlite file into each, and then set up MB with each version. If the former works, but not the latter, we've isolated it to MB's jar itself. If you really want to get your hands dirty, try building master, try it, and then revert that line and try again.

As an aside, can you share any info on how you're running docker? Are you using our dockerfile directly? What's the underlying host? Were there any changes other than upgrading that occurred?

[gseva]

We're using a docker-compose entrance for metabase that looks like this:

metabase_instance:
    image: metabase/metabase
    ports:
        - 3000
    container_name: metabase_instance
    volumes:
        - ./meta_instance:/tmp
    environment:
        - MB_DB_FILE=/tmp/metabase.db

To upgrade, i'm pulling the new image, stopping and lifting up the docker-compose. There we no changes aside from that.

[salsakran]

Ok, so I'm not able to replicate this. I didn't have a sqlite db handy so I pulled down http://www.sqlitetutorial.net/sqlite-sample-database/

I was able to connect to it and run queries without any kind of error on both v0.20.0 and v0.20.3.

As per @camsaul's SO link, are you able to see if ./metabase_instance/tmp has NOEXEC on it?

[gseva]

Seems like it does not:

bash-4.3# mount | grep /tmp
/dev/disk/by-uuid/0a76513a-37fc-43df-9833-34f8f9598ada on /tmp type ext4 (rw,relatime,discard,data=ordered)

I reviewed the logs, and this is the first error to appear (if it helps):

Failed to load native library:sqlite-3.14.2.1-9633473d-9408-44ba-8ed6-185f25105717-libsqlitejdbc.so. osinfo: Linux/x86_64
java.lang.UnsatisfiedLinkError: /tmp/sqlite-3.14.2.1-9633473d-9408-44ba-8ed6-185f25105717-libsqlitejdbc.so: Error relocating /tmp/sqlite-3.14.2.1-9633473d-9408-44ba-8ed6-185f25105717-libsqlitejdbc.so: __isnan: symbol not found

[gseva]

Also, this seems to be the same problem: http://discourse.metabase.com/t/error-adding-sqlite-database/807

[camsaul]

Weird. That guy is also running Metabase via Docker.

Sounds like this is a Docker + SQLite issue. @salsakran did you try running via Docker or did you just run the JAR directly?

[salsakran]

ran the mac app in my case.

[ghost]

Hi there

I have since migrated everything to Postgres, but here are the details of the sqlite version:

docker-compose:

version: '2'
services:
  metabase:
    build: ./mb/
    entrypoint: /ep/mb-entrypoint.sh
    ports:
      - "3000"
    environment:
      - JAVA_TIMEZONE=US/Eastern
      - MB_DB_TYPE=h2
      - MB_DB_FILE=/dbdata/metabase.db.h2.db
    volumes:
      - ./data/dbdata:/dbdata

I had a separate container which scraped some data and put it into sqlite databases in the dbdata folder.

mb/Dockerfile

FROM metabase/metabase:latest
COPY ./mb-entrypoint.sh /ep/
RUN chmod +x /ep/mb-entrypoint.sh

mb-entrypoint.sh

#!/usr/bin/env bash
java -jar /app/metabase.jar migrate release-locks;
echo "db locks released";
./app/run_metabase.sh

(p.s. not sure if that db lock release makes sense, but it did the trick for me)

I was running on a Ubuntu 16.04 box, with docker version 1.12.2, and docker-compose version 1.8.1 (though it might have been v1.9.0-rc1)

You should be able to put that docker-compose.yml, create a folder called mb with those two files, and then put a sqlite database into data/dbdata and try to add it...

Hope this helps.

--p

edit: should also note that the latest docker image as 0.20.0 at the time - I may have also tried 0.20.1 as well...

[gseva]

So i tried using the 0.19.3 with a fresh internal database and could use my sqlite database without a problem.

I also tried a fresh 0.20.3 install, and received the same error.

I moved my volume from /tmp to /dbdata, but it changed nothing. The error i mentioned remains the same (the problem is still within the /tmp directory):

Failed to load native library:sqlite-3.14.2.1-c69861a9-2b83-4973-b65f-826f04adb456-libsqlitejdbc.so. osinfo: Linux/x86_64
java.lang.UnsatisfiedLinkError: /tmp/sqlite-3.14.2.1-c69861a9-2b83-4973-b65f-826f04adb456-libsqlitejdbc.so: Error relocating /tmp/sqlite-3.14.2.1-c69861a9-2b83-4973-b65f-826f04adb456-libsqlitejdbc.so: __isnan: symbol not found

I'm also receiving the error independently from what i chose as sqlite database.

What i want to know is: do i have any possible solution without losing my questions and dashboards? Either reverting the Metabase version or migrating my data to another base?

[salsakran]

That's strange. I'll try to replicate this again, but on a docker image.

In theory, you could migrate your data to another db, and just manually change the database type.
So long as the column + fields remain the same name, it _should_ JustWork, but I'll be honest and say that I've never tried to do it.

[ghost]

That is more or less what I did... the script that I had scraping data and putting it into a sqlite db now puts it into a postgresdb.

[camsaul]

Looks like this is an error with the SQLite library we're using:

https://github.com/xerial/sqlite-jdbc/issues/97

[camsaul]

@salsakran did you manage to repro this on Docker? There's a new version of the SQLite library we use available, 3.15.1.

I think we should check if it's fixed in the new version. If not, we should downgrade back to the old version that was known to work.

[salsakran]

repro'd this on the v0.20.3 published image.

[camsaul]

@salsakran I'm going to switch this to you since you actually did the research to fix this, not me

[ghost]

Thank you

[gseva]

Nice to hear it's solved.

Meanwhile we are moving our data to postgresql. It's not that straightforward, particularly i'm having problems with datetime fields (timestamp without time zone). They are not mapped automatically to date types from Metabase. Do you know why it's happening?

[gseva]

I'm not sure if this issue is related, but after migrating to postgresql (changing the database to PostgreSQL) i'm seeing this:

That's how my datetime field is recognized (in Data Model/show original schema):

But Metabase does not map this correctly to a datetime type, and i cannot do it manually, there are no option:

And when i'm trying to make a question, a receive this error:

[camsaul]

@gseva SQLite doesn't have proper datetime types, but uses strings instead, so if you kept them as strings when you migrated to Postgres that would explain this behavior. You'll want to use something like DATE or TIMESTAMP In Postgres for the dates instead of keeping them as strings.

[Krever]

@camsaul
This is a problem again. I've just started metabase in docker and get

java.lang.UnsatisfiedLinkError: org.sqlite.core.NativeDB._open_utf8([BI)V

There is not much in logs as well:

Jun 15 15:42:40 ERROR metabase.driver :: Failed to connect to database: java.lang.UnsatisfiedLinkError: org.sqlite.core.NativeDB._open_utf8([BI)V
Jun 15 15:42:40 DEBUG metabase.middleware :: POST /api/database 400 (2 ms) (0 DB calls)
{:valid false, :dbname "java.lang.UnsatisfiedLinkError: org.sqlite.core.NativeDB._open_utf8([BI)V", :message "java.lang.UnsatisfiedLinkError: org.sqlite.core.NativeDB._open_utf8([BI)V"}

I'm running:

You're on version v0.24.2
Built on 2017-06-01

Edit: I tried to recreate metabase docker(openjdk:8-jre + metabase.jar) and it is working just fine

[jjmata]

I also noticed/reverted to the old driver to keep going, can this be addressed again @salsakran? Thanks!

[camsaul]

@jjmata are you using Java 7 as well?

It does indeed look like we updated the SQLite driver for version 24 in #4704

[camsaul]

At any rate the upstream bug was allegedly fixed in March 2016 :thinking:

[camsaul]

It looks like they're still having issues with this:

• https://github.com/xerial/sqlite-jdbc/issues/225
• https://github.com/xerial/sqlite-jdbc/issues/197

[camsaul]

I'll downgrade us back to the last know working version and add it to our upcoming 0.25.2 patch release

[camsaul]

Re-fixed by #5720

[jjmata]

Thanks! Working fine now on 0.25.2 👏

[starhel]

@camsaul It should be fixed with sqlite-jdbc 3.20.0 (fixed by xerial/sqlite-jdbc/pull/233). There's also fix for #4826.

[camsaul]

@starhel xerial/sqlite-jdbc#225 and xerial/sqlite-jdbc#197 are still open. I'm extremely wary of updating to a new version of the SQLite JDBC driver because doing so has broken SQLite support entirely for people the last couple of times I tried it. Last time the bug was supposedly fixed too

[starhel]

@camsaul There's probable a need of dev version for tests. Currently I'm using Metabase 0.25.2 with sqlite-jdbc 3.20.0 without any problem, so if you need some tests just tell me what to do :)

[mknippen]

I'm still having this issue with docker, and then think I found an easy way to reproduce.

1. Start with a clean docker image.
2. Grab a sample SQLite DB file
3. Copy that file into your docker image with
   docker container db.sqlite3 CONTAINER_ID:/home/db.sqlite3
4. Try to connect the data with SQLite and path /home/db.sqlite3.

What happens:
java.lang.UnsatisfiedLinkError: org.sqlite.core.NativeDB._open_utf8([BI)V

Reproduced consistently on AWS on local machine. Works fine on Metabase for Mac, only a problem through Docker. Happening on 0.26.2

[johackim]

I have this error too when I add sqlite database :
java.lang.UnsatisfiedLinkError: org.sqlite.core.NativeDB._open_utf8([BI)V

metabase version: v0.26.2

[fuzaro]

+1
Docker with dedicated volume /metabase-data.
java.lang.UnsatisfiedLinkError: org.sqlite.core.NativeDB._open_utf8([BI)V

[Simbul]

I'm getting the same error on a docker-installed Metabase version v0.27.1 (055e448 release-0.27.0).

Whenever I try to add a SQLite database, the following error is logged:

Failed to load native library:sqlite-3.16.1-f83e60cf-01fa-47a9-9d35-10898cc6780d-libsqlitejdbc.so. osinfo: Linux/x86_64
java.lang.UnsatisfiedLinkError: /tmp/sqlite-3.16.1-f83e60cf-01fa-47a9-9d35-10898cc6780d-libsqlitejdbc.so: Error relocating /tmp/sqlite-3.16.1-f83e60cf-01fa-47a9-9d35-10898cc6780d-libsqlitejdbc.so: __isnan: symbol not found
12-09 17:49:27 ERROR metabase.driver :: Failed to connect to database: java.lang.UnsatisfiedLinkError: org.sqlite.core.NativeDB._open_utf8([BI)V

Note that the last line occurs every time I try to add my database, whereas the first and second only occur once. To me, this suggests something is wrong with loading the library in the first place.

@starhel you mentioned you're using Metabase 0.25.2 with sqlite-jdbc 3.20.0. How come I'm on Metabase 0.27.1 with sqlite-3.16.1? Has the driver used by Metabase been downgraded?

[starhel]

@Simbul I've compiled Metabase on my own with newer sqlite driver.

[bitsofinfo]

+1 same error w/ docker and alpine linux (3.20.1)

[camsaul]

@starhel did the newer SQLite driver fix the issue?

[starhel]

I've never had problem like this. My database wasn't supported by default driver, so I decided to update. You should probably follow discussion in this thread: https://github.com/xerial/sqlite-jdbc/issues/197