My testing indicates that irrespective of whether or not a primary key is defined, the table remains read only.

I created the tables from the command line with the OSX provided sqlite3 client version 3.8.5

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.

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?

(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

(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).

(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.

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.

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

(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.

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

Created attachment 117648 [details]
bt on master

Created attachment 117649 [details]
SQLite3 Db schema - salespeople database

I missed have missed something but if the problem is Xerial driver, why shouldn't we put this one "NOTOURBUG"?

(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

(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 :-)

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.

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.

Confirming on Linux Mint 17.2 with 

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

(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

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.

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

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

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

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.

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.

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

(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

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

Bug still present in 

Version: 7.2.5.2 / LibreOffice Community
Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5
CPU threads: 8; OS: Mac OS X 12.2.1; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded

using latest Xerial JDBC driver from Maven (3.36.0.3)

Tried this one with LO 7.4.3.2 and sqlite-jdbc-3.40.0.0.jar

Could create a table in SQLite, could input new rows, could change content of existing rows. No buggy behavior appears.

System here: OpenSuSE 15.3 64bit rpm Linux.

Might be the bug has gone with sqlite-jdbc-3.40.0.0.jar?

Have tried also the attachment https://bugs.documentfoundation.org/attachment.cgi?id=117649 (Db schema). Tables will be created, could input data, but relations won't work as expected. But this might be a special behavior of SQLite - foreign key could differ to primary key of the connected table, so it seems the relation has been ignored.

Still not working for me with

Version: 7.5.0.0.beta1 (AARCH64) / LibreOffice Community
Build ID: 3aca23eec42e9d6fbe57071d7633ae1fc4bc5fcc
CPU threads: 8; OS: Mac OS X 13.0.1; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded

and Xerial JDBC driver 3.40.0.0


When I try to open a table (double-click):

Error message:
SQLite only supports TYPE_FORWARD_ONLY cursors

(In reply to Alex Thurgood from comment #35)
Hi Alex,
> 
> Error message:
> SQLite only supports TYPE_FORWARD_ONLY cursors

Sometimes I also have to look what I have written in Base Handbuch. You have written in the post for describing the bug:

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

(In reply to Robert Großkopf from comment #36)
> (In reply to Alex Thurgood from comment #35)

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


Hi Robert, yes, thanks for reminding me, you are absolutely correct !

With that setting enabled, I see the data and can edit and save changes.

Excellent news !

Hopefully, this might now make it possible to use the Thunderbird address book as well, using this latest JDBC driver.

As we don't know where the fix to this problem came from, setting as WFM.