Bug 68315 - EDITING: Crashes when changing data in a form to postgresql database through ODBC
Summary: EDITING: Crashes when changing data in a form to postgresql database through ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.0.4.2 release
Hardware: x86 (IA32) Linux (All)
: highest critical
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:4.2.0 target:4.1.2 target:4.0.6
Keywords: regression
Depends on:
Blocks:
 
Reported: 2013-08-20 08:36 UTC by Jan Rheinländer
Modified: 2013-09-03 12:09 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
backtrace (20.03 KB, text/x-log)
2013-08-23 07:32 UTC, Jan Rheinländer
Details
The .odb file, reduced to the minimum to reproduce the bug (23.79 KB, application/vnd.oasis.opendocument.database)
2013-08-27 05:20 UTC, Jan Rheinländer
Details
The .odb file, reduced to the minimum to reproduce the bug (23.70 KB, application/vnd.oasis.opendocument.database)
2013-08-27 05:31 UTC, Jan Rheinländer
Details
The database SQL dump (16.00 KB, application/x-tar)
2013-08-27 05:34 UTC, Jan Rheinländer
Details
/etc/odbcinst.ini (708 bytes, text/plain)
2013-08-27 05:40 UTC, Jan Rheinländer
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jan Rheinländer 2013-08-20 08:36:16 UTC
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.
Comment 1 Jan Rheinländer 2013-08-20 10:04:00 UTC
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
Comment 2 Robert Großkopf 2013-08-20 10:24:57 UTC
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
Comment 3 Jan Rheinländer 2013-08-20 11:13:48 UTC
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
Comment 4 Robert Großkopf 2013-08-20 19:19:52 UTC
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?
Comment 5 Jan Rheinländer 2013-08-21 05:30:15 UTC
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.
Comment 6 Julien Nabet 2013-08-23 04:54:37 UTC
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.
Comment 7 Jan Rheinländer 2013-08-23 07:32:14 UTC
Created attachment 84500 [details]
backtrace

Version: 4.1.0.4
Build ID: 410m0(Build:4)
Comment 8 Julien Nabet 2013-08-25 09:57:21 UTC
Jan: thank you for your feedback. I update the status to NEW since a bt has been provided.
Comment 9 Lionel Elie Mamane 2013-08-26 14:41:50 UTC
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.
Comment 10 Lionel Elie Mamane 2013-08-27 01:52:48 UTC
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, ...)
Comment 11 Lionel Elie Mamane 2013-08-27 01:54:42 UTC
Also give the relevant entries in odbcinst.ini and odbc.ini
Comment 12 Jan Rheinländer 2013-08-27 05:20:07 UTC
Created attachment 84679 [details]
The .odb file, reduced to the minimum to reproduce the bug
Comment 13 Jan Rheinländer 2013-08-27 05:31:35 UTC
Created attachment 84680 [details]
The .odb file, reduced to the minimum to reproduce the bug
Comment 14 Jan Rheinländer 2013-08-27 05:34:20 UTC
Created attachment 84681 [details]
The database SQL dump

Use pgadmin3 to import into an empty database called "Villages" (capital V) with owner postgres
Comment 15 Jan Rheinländer 2013-08-27 05:39:59 UTC
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
Comment 16 Jan Rheinländer 2013-08-27 05:40:22 UTC
Created attachment 84682 [details]
/etc/odbcinst.ini
Comment 17 Jan Rheinländer 2013-08-27 05:55:09 UTC
server 9.1+129ubuntu1
odbc-postgresql 1:09.00.0310-2
unixodbc 2.2.14p2-5ubuntu3
Comment 18 Lionel Elie Mamane 2013-08-27 07:59:27 UTC
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.
Comment 19 Jan Rheinländer 2013-08-27 08:50:43 UTC
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?
Comment 20 Commit Notification 2013-08-27 16:59:34 UTC
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.
Comment 21 Lionel Elie Mamane 2013-08-28 04:39:03 UTC
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.
Comment 22 Commit Notification 2013-08-28 08:43:09 UTC
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.
Comment 23 Jan Rheinländer 2013-08-28 08:53:04 UTC
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)
Comment 24 Jan Rheinländer 2013-08-28 09:42:19 UTC
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!
Comment 25 Lionel Elie Mamane 2013-08-28 13:50:25 UTC
(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...
Comment 26 Commit Notification 2013-09-03 12:09:02 UTC
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.