Bug 93094 - Editing - sqlite3 tables read only even when defined with primary key using Xerial jdbc driver
Summary: Editing - sqlite3 tables read only even when defined with primary key using X...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.4.3.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: haveBacktrace
Depends on:
Blocks: Database-Connectivity
  Show dependency treegraph
 
Reported: 2015-08-03 08:45 UTC by Alex Thurgood
Modified: 2020-05-21 17:38 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
bt on master (46.23 KB, text/plain)
2015-08-04 15:15 UTC, Alex Thurgood
Details
SQLite3 Db schema - salespeople database (523 bytes, text/plain)
2015-08-04 15:26 UTC, Alex Thurgood
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Thurgood 2015-08-03 08:45:19 UTC
How to reproduce :

1) Create a sqlite database with an appropriate command line tool, using, e.g. the example given here :

http://alvinalexander.com/android/sqlite-foreign-keys-example

2) Set up an ODB file that connect to this sqlite database using a JDBC driver, e.g. Xerial : sqlite-jdbc-3.8.11.jar :

https://bitbucket.org/xerial/sqlite-jdbc/downloads/sqlite-jdbc-3.8.11.jar

3) Set the Advanced Parameters to Respect Driver Privileges (last tick box on Advanced Settings - Special Parameters). 

Also tick the Primary Key support tickbox (actually, setting/unsetting this option makes no difference to the result).

The Driver Privilege option is required to avoid SCROLL_CURSOR error message.


4) Try opening a table by double-clicking. The table opens, but no data is editable.
Comment 1 Alex Thurgood 2015-08-03 08:47:13 UTC
My testing indicates that irrespective of whether or not a primary key is defined, the table remains read only.
Comment 2 Alex Thurgood 2015-08-03 08:49:04 UTC
I created the tables from the command line with the OSX provided sqlite3 client version 3.8.5
Comment 3 Lionel Elie Mamane 2015-08-03 12:41:26 UTC
Is the Primary Key showed in "edit table"? If not, the primary key is likely not shown as such by the JDBC driver, i.e. (in this case) it is a JDBC driver issue.
Comment 4 Rolf Leggewie 2015-08-03 12:50:46 UTC
As of now, I'm still struggling to get the Xerial driver to do anything.  FWIW, the ODBC driver shipped in Ubuntu seems to be able to open sqlite3 databases for reading and writing.

Is the situation any better if you create the sqlite database from within LO instead of preparing it outside with the sqlite3 CLI?
Comment 5 Alex Thurgood 2015-08-03 13:01:51 UTC
(In reply to Lionel Elie Mamane from comment #3)
> Is the Primary Key showed in "edit table"? If not, the primary key is likely
> not shown as such by the JDBC driver, i.e. (in this case) it is a JDBC
> driver issue.

No, unfortunately, the key does not show up in the leftmost column of the table when in table property editing mode in LO
Comment 6 Rolf Leggewie 2015-08-03 13:07:30 UTC
(In reply to Alex Thurgood from comment #5)
> (In reply to Lionel Elie Mamane from comment #3)
> > Is the Primary Key showed in "edit table"?

> No, unfortunately, the key does not show up in the leftmost column of the
> table when in table property editing mode in LO

that indeed does sound a lot like the Xerial driver has some issue with primary keys in sqlite databases.  I remember you were complaining that some of my test databases did not contain a primary key whereas as I was able to reconfirm today, the databases do show with primary keys here when connecting to them via ODBC (which is the method I used to create them, too).
Comment 7 Alex Thurgood 2015-08-03 13:55:27 UTC
(In reply to Rolf Leggewie from comment #6)


> that indeed does sound a lot like the Xerial driver has some issue with
> primary keys in sqlite databases.  I remember you were complaining that some
> of my test databases did not contain a primary key whereas as I was able to
> reconfirm today, the databases do show with primary keys here when
> connecting to them via ODBC (which is the method I used to create them, too).

OK so changed title to mention that the problem occurs with the Xerial driver. Will try and find another driver. This has side effects when the database is copied to another OSX machine (same driver / LO versions), as LO then reports that the tables are in an inconsistent state when one tries to open them, yet the OSX CLI sqlite3 opens the tables on the other machine just fine - sigh.
Comment 8 Alex Thurgood 2015-08-03 15:51:49 UTC
DBVisualiser relies on the Xerial JDBC driver and enables editing of SQLite databases from within DbVisualiser, so the problem would appear to lie with the way LibreOffice talks to the driver rather than the Xerial driver itself.

The same is true of Netbeans, which also uses the Xerial driver.
Comment 9 Alex Thurgood 2015-08-03 16:05:47 UTC
If I create a fresh database from within LO using the Xerial jdbc driver, I can create a new table, eg :

Table rainfall
id INTEGER NOT NULL
qty REAL

Set id as Primary Key via GUI

I can save that table, and then open it for data entry and enter data.

On saving and re-opening, I get an error message :

SQLite JDBC: inconsistent internal state.

The data has however been saved, because I can read and write to the same database from the command line using sqlite3. 

For some reason however, LO can no longer read the data from the table.

This indicates that the JDBC driver from Xerial is not necessarily the limiting factor, rather possibly the way LO interacts with the driver
Comment 10 Alex Thurgood 2015-08-03 16:06:43 UTC
(In reply to Alex Thurgood from comment #8)
> DBVisualiser relies on the Xerial JDBC driver and enables editing of SQLite
> databases from within DbVisualiser, so the problem would appear to lie with
> the way LibreOffice talks to the driver rather than the Xerial driver itself.
> 
> The same is true of Netbeans, which also uses the Xerial driver.

I should add that DbVisualiser can see the primary keys and constraints defined in the tables.
Comment 11 Alex Thurgood 2015-08-04 15:10:01 UTC
Enclosing bt.

The first SIGSEV occurs after loading the ODB file and clicking on the Tables icon. I pulled a bt from that before continuing. The bt in thread 39 seems to indicate that there is a problem with JNI instantiation ?

Double-click on the table customers, another SIGSEV occurs in thread 39, frame 0 again :

* thread #39: tid = 0x1a5ea9, 0x000000012e95c033, stop reason = signal SIGSEGV
  * frame #0: 0x000000012e95c033
    frame #1: 0x000000012e7694e7
    frame #2: 0x000000012dae01e2 libjvm.dylib`JavaCalls::call_helper(JavaValue*, methodHandle*, JavaCallArguments*, Thread*) + 1710
    frame #3: 0x000000012db16c9e libjvm.dylib`jni_invoke_nonstatic(JNIEnv_*, JavaValue*, _jobject*, JNICallType, _jmethodID*, JNI_ArgumentPusher*, Thread*) + 773
    frame #4: 0x000000012db06e86 libjvm.dylib`jni_CallObjectMethodV + 248
    frame #5: 0x0000000146335339 libjdbclo.dylib`JNIEnv_::CallObjectMethod(this=0x000000010a3b21f8, obj=0x000000012b481cc0, methodID=0x0000000147212558) + 393 at jni.h:901
    frame #6: 0x00000001463441ec libjdbclo.dylib`connectivity::java_sql_DatabaseMetaData::impl_callResultSetMethodWithStrings(this=0x0000000116a175a0, _pMethodName=0x00000001463b9dd4, _inout_MethodID=0x00000001463ee920, _rCatalog=0x00000001200ee6e0, _rSchemaPattern=0x0000000120d28770, _rLeastPattern=0x0000000120d28758, _pOptionalAdditionalString=0x0000000000000000) + 1884 at DatabaseMetaData.cxx:649
    frame #7: 0x0000000146346337 libjdbclo.dylib`connectivity::java_sql_DatabaseMetaData::getPrimaryKeys(this=0x0000000116a175a0, catalog=0x00000001200ee6e0, schema=0x0000000120d28770, table=0x0000000120d28758) + 87 at DatabaseMetaData.cxx:331
    frame #8: 0x00000001463463b6 libjdbclo.dylib`non-virtual thunk to connectivity::java_sql_DatabaseMetaData::getPrimaryKeys(this=0x0000000116a175c8, catalog=0x00000001200ee6e0, schema=0x0000000120d28770, table=0x0000000120d28758) + 70 at DatabaseMetaData.cxx:332
    frame #9: 0x000000010fd7c30d libgcc3_uno.dylib`gcc3::callVirtualMethod(void*, unsigned int, void*, _typelib_TypeDescriptionReference*, bool, unsigned long*, unsigned int, unsigned long*, unsigned int, double*, unsigned int) + 477
    frame #10: 0x000000010fd7ae04 libgcc3_uno.dylib`cpp_call(pThis=0x0000000129f14d00, aVtableSlot=(offset = 0, index = 132), pReturnTypeRef=0x000000011467c950, nParams=3, pParams=0x0000000116b5bf70, pUnoReturn=0x00007fff5fbf4e70, pUnoArgs=0x00007fff5fbf4e80, ppUnoExc=0x00007fff5fbf4fb8) + 1892 at uno2cpp.cxx:241
    frame #11: 0x000000010fd7a54b libgcc3_uno.dylib`bridges::cpp_uno::shared::unoInterfaceProxyDispatch(pUnoI=0x0000000129f14d00, pMemberDescr=0x00000001146a6920, pReturn=0x00007fff5fbf4e70, pArgs=0x00007fff5fbf4e80, ppException=0x00007fff5fbf4fb8) + 1211 at uno2cpp.cxx:430
    frame #12: 0x0000000116910ab3 libuno_purpenvhelpergcc3.dylib.3`s_dispatcher_v(pParam=0x00007fff5fbf4e10) [1]) + 563 at helper_purpenv_Proxy.cxx:383
    frame #13: 0x00000001010f3080 libuno_cppu.dylib.3`s_environment_invoke_v(pCurrEnv=0x0000000143010060, pTargetEnv=0x0000000143010060, pCallee=0x0000000116910880, pParam=0x00007fff5fbf4e10)(__va_list_tag (*) [1]), __va_list_tag (*) [1]) + 208 at EnvStack.cxx:293
    frame #14: 0x00000001010f4b9c libuno_cppu.dylib.3`s_environment_invoke_vv(pParam=0x00007fff5fbf4bc0) [1]) + 428 at EnvStack.cxx:276
    frame #15: 0x00000001010f4e85 libuno_cppu.dylib.3`s_pull(pParam=0x00007fff5fbf49e0) [1]) + 213 at EnvStack.cxx:226
    frame #16: 0x000000011642df24 libaffine_uno_uno.dylib`AffineBridge::innerDispatch(this=0x00000001428bf590) + 260 at AffineBridge.cxx:235
    frame #17: 0x000000011642de0d libaffine_uno_uno.dylib`InnerThread::run(this=0x0000000120baaad0) + 61 at AffineBridge.cxx:105
    frame #18: 0x000000011642f8ce libaffine_uno_uno.dylib`threadFunc(param=0x0000000120baaad0) + 30 at thread.hxx:184
    frame #19: 0x000000010009850f libuno_sal.dylib.3`osl_thread_start_Impl(pData=0x00000001428c1df0) + 335 at thread.cxx:240
    frame #20: 0x00007fff8eb10268 libsystem_pthread.dylib`_pthread_body + 131
    frame #21: 0x00007fff8eb101e5 libsystem_pthread.dylib`_pthread_start + 176
    frame #22: 0x00007fff8eb0e41d libsystem_pthread.dylib`thread_start + 13



* thread #39: tid = 0x1a5ea9, 0x000000012e95c033, stop reason = signal SIGSEGV
    frame #0: 0x000000012e95c033
->  0x12e95c033: movl   0xc(%rdx), %r9d
    0x12e95c037: movl   0xc(%r12,%r9,8), %r13d
    0x12e95c03c: movl   0x10(%rsi), %r11d
    0x12e95c040: movl   0xc(%r12,%r11,8), %ecx


(lldb) c
Process 96874 resuming
warn:connectivity.commontools:96874:1:connectivity/source/commontools/TTableHelper.cxx:340: NULL Primary Key name
warn:connectivity.commontools:96874:1:connectivity/source/commontools/TTableHelper.cxx:341: empty Primary Key name
warn:connectivity.commontools:96874:1:connectivity/source/commontools/TTableHelper.cxx:348: empty Primary Key name
java.lang.IllegalStateException: SQLite JDBC: inconsistent internal state
	at org.sqlite.core.CoreResultSet.checkCol(CoreResultSet.java:78)
	at org.sqlite.jdbc3.JDBC3ResultSet.getColumnName(JDBC3ResultSet.java:726)
warn:legacy.osl:96874:1:dbaccess/source/core/dataaccess/databasedocument.cxx:633: lcl_hasAnyModifiedSubComponent_throw: anything left to do here?
warn:legacy.osl:96874:1:dbaccess/source/core/dataaccess/databasedocument.cxx:633: lcl_hasAnyModifiedSubComponent_throw: anything left to do here?
warn:legacy.osl:96874:1:include/cppuhelper/interfacecontainer.h:479: object is disposed
warn:legacy.osl:96874:1:include/cppuhelper/interfacecontainer.h:479: object is disposed
warn:legacy.osl:96874:1:include/cppuhelper/interfacecontainer.h:479: object is disposed
warn:legacy.osl:96874:1:include/cppuhelper/interfacecontainer.h:479: object is disposed
warn:legacy.osl:96874:1:include/cppuhelper/interfacecontainer.h:479: object is disposed
warn:legacy.osl:96874:1:include/cppuhelper/interfacecontainer.h:479: object is disposed
warn:legacy.osl:96874:1:include/cppuhelper/interfacecontainer.h:479: object is disposed
warn:legacy.osl:96874:1:include/cppuhelper/interfacecontainer.h:479: object is disposed
Comment 12 Alex Thurgood 2015-08-04 15:15:46 UTC
Created attachment 117648 [details]
bt on master
Comment 13 Alex Thurgood 2015-08-04 15:26:21 UTC
Created attachment 117649 [details]
SQLite3 Db schema - salespeople database
Comment 14 Julien Nabet 2015-08-09 09:33:17 UTC
I missed have missed something but if the problem is Xerial driver, why shouldn't we put this one "NOTOURBUG"?
Comment 15 Alex Thurgood 2015-08-09 09:41:00 UTC
(In reply to Julien Nabet from comment #14)
> I missed have missed something but if the problem is Xerial driver, why
> shouldn't we put this one "NOTOURBUG"?

Because the Xerial driver works properly in other db connection software that uses it, e.g. see my comment 8 and comment 10
Comment 16 Julien Nabet 2015-08-09 09:43:26 UTC
(In reply to Alex Thurgood from comment #15)
> (In reply to Julien Nabet from comment #14)
> > I missed have missed something but if the problem is Xerial driver, why
> > shouldn't we put this one "NOTOURBUG"?
> 
> Because the Xerial driver works properly in other db connection software
> that uses it, e.g. see my comment 8 and comment 10
Ok thank you for your quick feedback, I indeed had missed something :-)
Comment 17 Alex Thurgood 2015-08-09 10:04:32 UTC
It would appear from this :

https://groups.google.com/d/msg/ward-tools-dev/3hHnFuzFfIw/jOkXy4m0qJMJ

that a similar problem exists with the Zentus JDBC driver, i.e. a connection can be obtained, but no data retrieved. The first error message about the scroll cursor :

"The zentus SQLite driver supports only TYPE_FORWARD_ONLY cursors so OOo can't get
any results" is the same that I see when using the Xerial driver without using the default Advanced Parameters. This message only goes away when the bottom-most option is ticked, but that then leads to the INCONSISTENT STATE error message.
Comment 18 Alex Thurgood 2015-08-09 10:07:59 UTC
So perhaps one of the questions should be : why doesn't LO support TYPE_FORWARD_ONLY cursors ? Or, does it, and it just can't understand the Zentus and Xerial drivers.


Note that there does not appear to be any Christian Werner JDBC driver available for OSX 64bit.
Comment 19 Alex Thurgood 2015-09-27 08:34:38 UTC
Confirming on Linux Mint 17.2 with 

Version: 4.4.3.2
Build ID: 40m0(Build:2)
Locale : fr_FR
Comment 20 Alex Thurgood 2015-09-27 08:44:09 UTC
(In reply to Lionel Elie Mamane from comment #3)
> Is the Primary Key showed in "edit table"? If not, the primary key is likely
> not shown as such by the JDBC driver, i.e. (in this case) it is a JDBC
> driver issue.

The Primary Key symbols are displayed on Linux Mint 17.2 with LO

Version: 4.4.3.2
Build ID: 40m0(Build:2)
Locale : fr_FR
Comment 21 Alex Thurgood 2015-09-27 09:07:20 UTC
On Linux Mint 17.2 with LO

Version: 4.4.3.2
Build ID: 40m0(Build:2)
Locale : fr_FR

the pre-created SQLite3 tables remain read-only. Not only can one not display the data in any table, but the table definitions can not be edited.
Comment 22 QA Administrators 2016-11-08 10:35:41 UTC Comment hidden (obsolete)
Comment 23 Alex Thurgood 2017-06-27 09:25:53 UTC
Still reproducible with

Version: 5.3.4.2
Build ID: f82d347ccc0be322489bf7da61d7e4ad13fe2ff3
Threads CPU : 4; Version de l'OS :Mac OS X 10.12.5; UI Render : par défaut; Moteur de mise en page : nouveau; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group

Xerial JDBC driver : 3.14.2.1
Comment 24 Tyler 2018-01-11 21:53:55 UTC
Still reproducible with-

LibreOffice
Version: 5.4.3.2 (x64)
Build ID: 92a7159f7e4af62137622921e809f8546db437e5
CPU threads: 4; OS: Windows 6.19; UI render: default; 
Locale: en-US (en_US); Calc: group

Xerial JDBC driver : 3.21.0

SQLite version 3.21.0 2017-10-24
Comment 25 Tyler 2018-01-24 00:17:23 UTC
Just wanted to add a bit of information-

When creating a new query in Base, running the query results in the same error, and setting it to "Run SQL Directly" completes, but returns no results.

The interesting part... Using the Tools>SQL tool, results are returned and records can be added without issue. I can't be sure of the difference in how these two operate, but I have a hunch it's still related to cursors.

Reproduced using-
Version: 6.0.0.2 (x64)
Build ID: 06b618bb6f431d27fd2def25aa19c833e29b61cd
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: en-US (en_US); Calc: group
Comment 26 Julien Nabet 2018-01-31 12:36:39 UTC
On pc Debian x86-64 with master sources updated yesterday + sqlite-jdbc-3.21.0.jar (2017-11-14), I don't reproduce this because I got the message:
SQLite JDBC: inconsistent internal state.

On console, I got this:
java.lang.IllegalStateException: SQLite JDBC: inconsistent internal state
	at org.sqlite.core.CoreResultSet.checkCol(CoreResultSet.java:81)
	at org.sqlite.jdbc3.JDBC3ResultSet.getColumnName(JDBC3ResultSet.java:721)

BTW, I created an sqlite 3 db with just salespeople table.
Notice: I did first an sqlite < 3 by using "sqlite" instead of "sqlite3", I got a crash.
0x00007fffed875b7c in std::type_info::name (this=0x0) at /usr/include/c++/7/typeinfo:100
100	    { return __name[0] == '*' ? __name + 1 : __name; }
(gdb) bt
#0  0x00007fffed875b7c in std::type_info::name() const (this=0x0) at /usr/include/c++/7/typeinfo:100
#1  0x00007fffed8757d9 in DbgUnhandledException(com::sun::star::uno::Any const&, char const*, char const*, char const*) (caught=
  uno::Any("com.sun.star.sdbc.SQLException": {<com::sun::star::uno::Exception> = {Message = "[SQLITE_NOTADB]  File opened that is not a database file (file is not a database)", Context = uno::Reference to (com::sun::star::uno::XInterface *) 0x5555583969b8}, SQLState = "", ErrorCode = 26, NextException = uno::Any(void)}), currentFunction=0x7fffca16d760 <dbaccess::OFilteredContainer::construct(com::sun::star::uno::Sequence<rtl::OUString> const&, com::sun::star::uno::Sequence<rtl::OUString> const&)::__PRETTY_FUNCTION__> "void dbaccess::OFilteredContainer::construct(const com::sun::star::uno::Sequence<rtl::OUString>&, const com::sun::star::uno::Sequence<rtl::OUString>&)", fileAndLineNo=0x7fffca16b528 "/home/julien/lo/libreoffice/dbaccess/source/core/api/FilteredContainer.cxx:380: ", explanatory=0x0) at /home/julien/lo/libreoffice/tools/source/debug/debug.cxx:101
#2  0x00007fffc9f398b3 in dbaccess::OFilteredContainer::construct(com::sun::star::uno::Sequence<rtl::OUString> const&, com::sun::star::uno::Sequence<rtl::OUString> const&) (this=0x555558403210, _rTableFilter=uno::Sequence of length 1 = {...}, _rTableTypeFilter=empty uno::Sequence)
    at /home/julien/lo/libreoffice/dbaccess/source/core/api/FilteredContainer.cxx:380

but that's another story.
Comment 27 Alex Thurgood 2018-06-28 07:44:08 UTC
Still reproducible with 

Xerial JDBC driver : 3.14.2.1

and 

Version: 6.2.0.0.alpha0+
Build ID: fa7ebc82bbcee15cd75b42d1e7b8ef66ed719225
CPU threads: 4; OS: Mac OS X 10.13.5; UI render: default; 
Locale: fr-FR (fr_FR.UTF-8); Calc: group threaded

Data is displayed but is uneditable on first opening of a table.
Thereafter, subsequent attempts to display data in a table lead to the Inconsistent State error message reported by Julien.
Comment 28 QA Administrators 2019-06-29 02:57:53 UTC Comment hidden (obsolete)
Comment 29 Alex Thurgood 2019-09-04 10:54:58 UTC
Problem still reproducible with 

Version: 6.4.0.0.alpha0+
Build ID: e04b6f3c0cdacf2a3cdcd3f34bad54c8764ff1ed
CPU threads: 4; OS: Mac OS X 10.14.6; UI render: default; VCL: osx; 
Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
Calc: threade
Comment 30 Alex Thurgood 2019-09-04 10:55:50 UTC
(In reply to Alex Thurgood from comment #29)
> Problem still reproducible with 
> 
> Version: 6.4.0.0.alpha0+
> Build ID: e04b6f3c0cdacf2a3cdcd3f34bad54c8764ff1ed
> CPU threads: 4; OS: Mac OS X 10.14.6; UI render: default; VCL: osx; 
> Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
> Calc: threade

and latest sqlite JDBC driver from Xerial 3.27.2.1
Comment 31 Tyler 2020-05-21 17:38:24 UTC
Still reproducible with-

Version: 6.4.3.2 (x64)
Build ID: 747b5d0ebf89f41c860ec2a39efd7cb15b54f2d8
CPU threads: 4; OS: Windows 10.0 Build 18362; UI render: default; VCL: win; 
Locale: en-US (en_US); UI-Language: en-US
Calc: threaded

and latest sqlite JDBC driver from Xerial 3.31.1