I am attempting to test and connect to a SQLite database using OpenRefine's latest version (originally I was using version 3.8.1) on a windows OS. I received an "error:Unauthorized" message when trying to test or connect to the database file. I have since tried the same process with version 3.6.1 and am able to do it. I was wondering if there are any fixes for the newest version coming down the line or if there is a way to solve this issue that I am unaware of. I am relatively new to OpenRefine and appreciate your help.
Thanks!
This is likely due to the CSRF token requirement for the new version. Extensions need to add CSRF wrappers around any methods that request data results that may have expression evaluation. I had this "authentication" issue with the RDF Transform extension for data exports. The SQLite DB code likely needs to be updated similarly.
@AtesComp would it be too much of an ask for you to help us with that in a PR?
@thadguidry I can. It might take a while for me to get familiar with the related code. Is this issue in the OpenRefine code or an extension?
@sdw50-maker can you post a log containing the problem?
The database extension is one of the built-in extensions in OpenRefine's code.
Changes would need to be made under
/extensions/database
@sdw50-maker A related log file will help greatly.
Reviewing the code, it looks like the CSRF token is NOT the problem as that error looks like it would produce a different error (400 BAD REQUEST).
...However, I continued down the rabbit hole...
NOTE: The following is base on my short acquaintance with the Database Extension, so I can be wrong.
This error is a 401 UNAUTHORIZED and is likely caught from a thrown error:
in ConnectCommand.java at line 77
DatabaseInfo databaseInfo = DatabaseService.get(databaseConfiguration.getDatabaseType()).connect(databaseConfiguration);
or in TestConnectCommand.java at line 77
boolean connectionTestResult = DatabaseService.get(databaseConfiguration.getDatabaseType()).testConnection(databaseConfiguration);
This implies it is reporting unauthorized access to the database?!? Username / password issue?
Why that is so is a mystery that a log file could possible narrow down in short order.
(heh, I look suspiciously sideways the same line numbers above in two different file)
I believe the actual error is caught in SQLiteConnectionManager.java in method getConnection
and the error is thrown in JavaSQL library by the DriverManager.class in method getConnection at the end of the class.
I also noticed that there are two (2) sqlite-jdbc libraries in the database extension:
sqlite-jdbc-3.47.0.0
sqlite-jdbc-3.47.1.0
Are they both needed? Are they in conflict? Is one overriding the other? Am I asking too many questions?
sorry for the wait, I was trying to figure out how to get a log file. I think this is what you are asking for?
11:16:03.391 [ refine_server] Starting Server bound to '127.0.0.1:3333' (0ms)
11:16:03.437 [ refine_server] Initializing context: '/' from 'C:\Program Files (x86)\OpenRefine\webapp' (46ms)
11:16:04.990 [ refine] Starting OpenRefine 3.8.7 [TRUNK]... (1552ms)
11:16:04.990 [ refine] initializing FileProjectManager with dir (0ms)
11:16:04.990 [ refine] C:\Users\saijdw\AppData\Roaming\OpenRefine (0ms)
11:16:06.651 [ refine] POST /command/core/load-language (1660ms)
11:16:06.675 [ refine] GET /command/core/get-preference (23ms)
11:16:06.691 [ refine] POST /command/core/load-language (15ms)
11:16:06.691 [ refine] POST /command/core/load-language (0ms)
11:16:06.707 [ refine] POST /command/core/load-language (16ms)
11:16:06.771 [ refine] GET /command/core/get-importing-configuration (64ms)
11:16:06.782 [ refine] GET /command/core/get-all-project-tags (10ms)
11:16:06.790 [ refine] GET /command/core/get-all-project-metadata (8ms)
11:16:06.845 [ refine] GET /command/core/get-csrf-token (54ms)
11:16:06.854 [ refine] GET /command/core/get-languages (8ms)
11:16:06.913 [ refine] GET /command/core/get-version (58ms)
11:16:07.011 [ refine] GET /command/database/saved-connection (97ms)
11:16:07.034 [ refine] GET /command/core/get-preference (22ms)
11:16:24.278 [ refine] GET /command/core/get-csrf-token (17244ms)
11:16:24.289 [ refine] POST /command/database/test-connect (11ms)
11:16:24.298 [ DatabaseService] Registered mysql Database (9ms)
11:16:24.298 [ DatabaseService] Registered postgresql Database (0ms)
11:16:24.299 [ DatabaseService] Registered mariadb Database (0ms)
11:16:24.299 [ DatabaseService] Registered sqlite Database (0ms)
11:16:24.312 [ SQLiteConnectionManager] SQLException::Couldn't get a Connection! (13ms)
java.sql.SQLException: opening db: 'C:%5CUsers%5Csaijdw%5CDocuments%5Ccdm_migrations%5Cnumismatics%5Ccdm_index.db': Access is denied
at org.sqlite.SQLiteConnection.open(SQLiteConnection.java:271)
at org.sqlite.SQLiteConnection.(SQLiteConnection.java:67)
at org.sqlite.jdbc3.JDBC3Connection.(JDBC3Connection.java:28)
at org.sqlite.jdbc4.JDBC4Connection.(JDBC4Connection.java:19)
at org.sqlite.JDBC.createConnection(JDBC.java:106)
at org.sqlite.JDBC.connect(JDBC.java:79)
at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
at com.google.refine.extension.database.sqlite.SQLiteConnectionManager.getConnection(SQLiteConnectionManager.java:139)
at com.google.refine.extension.database.sqlite.SQLiteConnectionManager.testConnection(SQLiteConnectionManager.java:113)
at com.google.refine.extension.database.sqlite.SQLiteDatabaseService.testConnection(SQLiteDatabaseService.java:82)
at com.google.refine.extension.database.cmd.TestConnectCommand.doPost(TestConnectCommand.java:78)
at com.google.refine.RefineServlet.service(RefineServlet.java:188)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)
at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:529)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:578)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1570)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:790)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1543)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at com.google.refine.ValidateHostHandler.handle(ValidateHostHandler.java:93)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.Server.handle(Server.java:563)
at org.eclipse.jetty.server.HttpChannel$RequestDispatchable.dispatch(HttpChannel.java:1598)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:753)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:501)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
11:16:24.326 [ TestConnectCommand] TestConnectCommand::Post::DatabaseServiceException::{} (13ms)
com.google.refine.extension.database.DatabaseServiceException: opening db: 'C:%5CUsers%5Csaijdw%5CDocuments%5Ccdm_migrations%5Cnumismatics%5Ccdm_index.db': Access is denied
at com.google.refine.extension.database.sqlite.SQLiteConnectionManager.getConnection(SQLiteConnectionManager.java:149)
at com.google.refine.extension.database.sqlite.SQLiteConnectionManager.testConnection(SQLiteConnectionManager.java:113)
at com.google.refine.extension.database.sqlite.SQLiteDatabaseService.testConnection(SQLiteDatabaseService.java:82)
at com.google.refine.extension.database.cmd.TestConnectCommand.doPost(TestConnectCommand.java:78)
at com.google.refine.RefineServlet.service(RefineServlet.java:188)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)
at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:529)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:578)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1570)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:790)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1543)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at com.google.refine.ValidateHostHandler.handle(ValidateHostHandler.java:93)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.Server.handle(Server.java:563)
at org.eclipse.jetty.server.HttpChannel$RequestDispatchable.dispatch(HttpChannel.java:1598)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:753)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:501)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
11:16:29.361 [ refine] GET /command/core/get-csrf-token (5034ms)
11:16:29.361 [ refine] POST /command/database/connect (0ms)
11:16:29.361 [ SQLiteConnectionManager] SQLException::Couldn't get a Connection! (0ms)
java.sql.SQLException: opening db: 'C:%5CUsers%5Csaijdw%5CDocuments%5Ccdm_migrations%5Cnumismatics%5Ccdm_index.db': Access is denied
at org.sqlite.SQLiteConnection.open(SQLiteConnection.java:271)
at org.sqlite.SQLiteConnection.(SQLiteConnection.java:67)
at org.sqlite.jdbc3.JDBC3Connection.(JDBC3Connection.java:28)
at org.sqlite.jdbc4.JDBC4Connection.(JDBC4Connection.java:19)
at org.sqlite.JDBC.createConnection(JDBC.java:106)
at org.sqlite.JDBC.connect(JDBC.java:79)
at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
at com.google.refine.extension.database.sqlite.SQLiteConnectionManager.getConnection(SQLiteConnectionManager.java:139)
at com.google.refine.extension.database.sqlite.SQLiteDatabaseService.getMetadata(SQLiteDatabaseService.java:97)
at com.google.refine.extension.database.sqlite.SQLiteDatabaseService.connect(SQLiteDatabaseService.java:87)
at com.google.refine.extension.database.cmd.ConnectCommand.doPost(ConnectCommand.java:78)
at com.google.refine.RefineServlet.service(RefineServlet.java:188)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)
at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:529)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:578)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1570)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:790)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1543)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at com.google.refine.ValidateHostHandler.handle(ValidateHostHandler.java:93)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.Server.handle(Server.java:563)
at org.eclipse.jetty.server.HttpChannel$RequestDispatchable.dispatch(HttpChannel.java:1598)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:753)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:501)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
11:16:29.377 [ ConnectCommand] ConnectCommand::Post::DatabaseServiceException::{} (15ms)
com.google.refine.extension.database.DatabaseServiceException: opening db: 'C:%5CUsers%5Csaijdw%5CDocuments%5Ccdm_migrations%5Cnumismatics%5Ccdm_index.db': Access is denied
at com.google.refine.extension.database.sqlite.SQLiteConnectionManager.getConnection(SQLiteConnectionManager.java:149)
at com.google.refine.extension.database.sqlite.SQLiteDatabaseService.getMetadata(SQLiteDatabaseService.java:97)
at com.google.refine.extension.database.sqlite.SQLiteDatabaseService.connect(SQLiteDatabaseService.java:87)
at com.google.refine.extension.database.cmd.ConnectCommand.doPost(ConnectCommand.java:78)
at com.google.refine.RefineServlet.service(RefineServlet.java:188)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)
at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:529)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:578)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1570)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:790)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1543)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at com.google.refine.ValidateHostHandler.handle(ValidateHostHandler.java:93)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.Server.handle(Server.java:563)
at org.eclipse.jetty.server.HttpChannel$RequestDispatchable.dispatch(HttpChannel.java:1598)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:753)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:501)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
11:16:29.377 [ ConnectCommand] ConnectCommand::Post::DatabaseServiceException::{} (15ms)
com.google.refine.extension.database.DatabaseServiceException: opening db: 'C:%5CUsers%5Csaijdw%5CDocuments%5Ccdm_migrations%5Cnumismatics%5Ccdm_index.db': Access is denied
That Access is denied might be a Windows file permission issue you are having? You can try copying the file to another location, perhaps just your desktop. You might also check the file permissions for your user on that file (Google search how to do this - Right click the file - Properties - Security - etc.)
I tested OpenRefine 3.8.7 just now with this SQLite test.db file test.zip (517 Bytes) stored in my C:\Users\thadg\Downloads folder in Windows 11 and was able to Test connection as well as Connect and issue the query:
SELECT * FROM table1
and preview the 3 rows it contains.