I have an .odb file which uses ODBC to connect to a postgresql database located on another computer. Inside the .odb file there are several forms defined. Since LO 4.0 whenever I change data in any of these forms and press the "save record" button, I get: Error updating the current record the target rows is out of the rowset SQL-Status: HY107 Error code: 20 And the next time I press the "save record" button, LO crashes. This bug makes LO 4.0 completely unusable for me since I can do no work on my database. I am stuck with using LO 3.6.x which works fine.
The error message seems to originate from the odbc-postgresql package. Since this is identical for my installation of LO 3.6 and LO 4.0 it seems that LO 4.0 sends different information to the ODBC driver than it used to do in version 3.6
You have reported this behavior for Linux. Which version do you use? Have a look here for special problem ODBC / MariaDB in openSUSE: https://bugs.freedesktop.org/show_bug.cgi?id=65830
I use Ubuntu 12.04. The bug you mention is similar but different. For example, I can view any amount of data in the forms but as soon as I change some I get an error message, and at the second try a crash. Also, my database is PostgreSQL not mySQL
I have now setup ODBC for postgresql on OpenSUSE 12.3 64bit. I could open the folder public, could see the tables, but when I try to open the tables LO 4.* crashes immediately. When I try the same with any 3.* version (have tested 3.3.4, 3.6.6.2 and 3.6.7.2) LO hangs and the whole KDE-windowmanager is very slow. The only way to get LO open a table of postgresql with ODBC is to use the packages, which are packed by OpenSUSE itself. With this packages (LO 4.1.0.4) I could see the content of the tables, could try a input, but it fails with an error "7", query ... «)». So ODBC isn't usable with postgresql on my system at all. The direct connection between postgresql and the OpenSUSE-packages of LO is running without problems. Where do you get the packages from, when LO is working together with ODBC and Postgresql?
Server (on a different machine than the LO client): - Ubuntu 12.04 32bit - postgresql-9.1 version 9.1.9-0ubuntu12.04 Client: - Ubuntu 12.04 32bit - odbc-postgresql version 1:09.00.0310-2 LO 3.6.6 installed manually from the libreoffice web page -> this combination works fine. The only reason I am installing manually from the webpage is because of the report-builder package which is not included in the Ubuntu12.04 version of LO. But before I noticed this I installed the standard Ubuntu12.04 LO version (3.5 I believe) which also worked fine. All LO 3.x versions I have tried work equally well. LO 4.0 (4.1 has the same problems as described in this bug report): - deb http://ppa.launchpad.net/libreoffice/libreoffice-4-0/ubuntu precise main What I have also noticed (in LO 3.x) is that accessing postgresql directly from LO without going through ODBC doesn't work properly.
Could you try to retrieve and attach some bts? (see https://wiki.documentfoundation.org/QA/BugReport/Debug_Information#GNU.2FLinux:_How_to_get_a_backtrace) Lionel: thought you might be interested in this one.
Created attachment 84500 [details] backtrace Version: 4.1.0.4 Build ID: 410m0(Build:4)
Jan: thank you for your feedback. I update the status to NEW since a bt has been provided.
Version field of bug report is *oldest* version where the bug has been reproduced. Resetting to 4.0.4.2 since Jan says he gets this bug with 4.0.
Cannot reproduce on Debian GNU/Linux with LibreOffice Version: 4.1.2.0.0+ (my development tree) and odbc-postgresql 1:09.02.0100-2 and unixodbc 2.2.14p2-5 and server 9.0.6. Please: 1) attach the .odb file you are using 2) attach a SQL dump of the database with which you have the problem; you can fill it with dummy data if the data is personal / confidential / ... 3) Give an exact description of what to do to reproduce the bug (open which form, change what data to what, ...)
Also give the relevant entries in odbcinst.ini and odbc.ini
Created attachment 84679 [details] The .odb file, reduced to the minimum to reproduce the bug
Created attachment 84680 [details] The .odb file, reduced to the minimum to reproduce the bug
Created attachment 84681 [details] The database SQL dump Use pgadmin3 to import into an empty database called "Villages" (capital V) with owner postgres
To reproduce the bug: - Create an empty database "Villages" (capital V!) with owner postgres - Import the SQL dump into this database using pgadmin3 - Create an odbc connection in /etc/odbc.ini: [Villages-local] Description = Connection to postgresql Villages database (local copy) Driver = PostgreSQL Unicode Trace = No TraceFile = /tmp/psqlodbc.log Database = Villages Servername = localhost UserName = Password = Port = ReadOnly = No RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings = - Open the .odb file - Open the only form (it might ask for the password of the postgres user) - Edit a field, e.g. set "houses" to 10 - Click on the "save" icon at the bottom of the window. An error message pops up - Click again. Crashes
Created attachment 84682 [details] /etc/odbcinst.ini
server 9.1+129ubuntu1 odbc-postgresql 1:09.00.0310-2 unixodbc 2.2.14p2-5ubuntu3
OK, reproduced. Here's why I did not get the problem: in edit / database / advanced settings / special settings, I *unset* "Ignore the privileges from the database driver" and I *set* "Respect the result set type from the database driver". Which (if/when they work), are the preferred settings anyway, since they are work-arounds for bugs / incompleteness in the ODBC driver.
Changing the settings solved this problem for me. Though I don't think I ever edited them. But the database file .odb is several years old... Maybe you could have an automatic warning pop up if an old file is loaded with these settings?
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8f9388c0234f59ef4460512cd3a3e9b4727e6363 fdo#68315 odbc update *reads* a bookmark, not *writes* a bookmark The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I've fixed the crash, and the reason for the "the target rows is out of the rowset" error message; backport to 4.0.x and 4.1.x pending review. I still cannot get PostgreSQL/ODBC to work with the option "Respect the result set type from the database driver" unset. The update fails with 'syntax error in query near "where"'. However, you say that LibreOffice 3.6.x works for you, it does not for me; it fails with the same error. So I'm closing this bug with the assumption that the situation is back to what it was for LibreOffice 3.6.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b17f6475d732f4123f4334cc02af18308c1d1256&h=libreoffice-4-1 fdo#68315 odbc update *reads* a bookmark, not *writes* a bookmark It will be available in LibreOffice 4.1.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Thanks for this patch. "However, you say that LibreOffice 3.6.x works for you, it does not for me; it fails with the same error." The identical files attached here work fine with LO Version 3.6.6.2 (Build ID: f969faf)
BTW when I create a NEW database with ODBC connector in LO 4.1, by default the options are SET: Ignore privilges from db driver UNSET: Respect result set from db driver which is exactly the opposite of what you said in a previous comment should be set!
(In reply to comment #23) > "However, you say that LibreOffice 3.6.x works for you, it does not for me; > it fails with the same error." > The identical files attached here work fine with LO Version 3.6.6.2 (Build > ID: f969faf) They work for me with 3.6 only if I additionally set UpdatableCursors = Yes in the DSN (in ~/.odbc.ini). With this additional setting, it works also with LibreOffice 4.0 and 4.1 AFTER applying my patch. (In reply to comment #24) > BTW when I create a NEW database with ODBC connector in LO 4.1, by default > the options are > SET: Ignore privilges from db driver > UNSET: Respect result set from db driver > which is exactly the opposite of what you said in a previous comment should > be set! Yes, it is a conundrum. These values are good to work with buggy drivers (that lie about their capacities and the user's permissions). The values I have given are good for working with non-buggy drivers (that give correct information about their capacities and the user's permissions). Which ones should be the default? There is no way to win...
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=eb0aef67c76e23e77be12deda0acfb2475368fa2&h=libreoffice-4-0 fdo#68315 odbc update *reads* a bookmark, not *writes* a bookmark It will be available in LibreOffice 4.0.6. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.