Bug 81430 - libreoffice-mysql-connector cannot create/edit tables without primary key
Summary: libreoffice-mysql-connector cannot create/edit tables without primary key
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.3.0.2 rc
Hardware: x86-64 (AMD64) Linux (All)
: low enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Connectivity
  Show dependency treegraph
 
Reported: 2014-07-16 14:29 UTC by Y
Modified: 2017-11-05 22:32 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Y 2014-07-16 14:29:33 UTC
Testing with Xubuntu 14.04 and with LibreOffice version 1:4.3.0~rc2-1ubuntu1~trusty0 from the pre-releases PPA.

Server Side:
------------
> CREATE DATABASE test;
> GRANT ALL PRIVILEGES ON test.* to 'dbuser'@'localhost' IDENTIFIED BY 'password';
> FLUSH PRIVILEGES;

dbuser performs all operations on databse test as expected.

Client Side:
------------
SSH tunnel established with:
$ ssh -L5000:server.example.com:3306 user@server.example.com

Manual connection test works well:
$ mysql -h 127.0.0.1 --port=5000 -u dbuser - p

dbuser performs all operations on databse test as expected.

Install and restart libreoffice:
$ sudo apt-get install libreoffice-mysql-connector

In LibreOffice, when I try to create a table, it reports the following error:
  SQL Status: 42000
  Error code: 1142
  CREATE command denied to user 'dbuser'@'localhost' for table 'Capacity'

A quick search for the error codes indicates that I am logged into mysql with a default null user with just about zero privileges.

I can read table content, but I cannot edit them in any meaningful way.

Workaround (JDBC):
------------------

Install and restart libreoffice:
$ sudo apt-get install libmysql-java

Configure:
* Tools -> Options -> LibreOffice -> Advanced
* tick Use a jre
** Select Oracle 1.7.0_55
* Click Class Path
** Add Archive /usr/share/java/mysql-connector-java.jar
* Restart LO (or maybe reboot, at first it did not work)

Now LibreOffice Base can edit a MySQL database over an SSH tunnel.

Bug submitted also with the packager as I am not sure where the problem lies:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1342755
Comment 1 Y 2014-07-16 15:08:55 UTC
More research into this:
* I can create tables from LibreOffice to MySQL, but they must have primary keys
* Once tables have primary keys, they can be edited
* Table created MySQL-side, that works:
> CREATE TABLE idxtest (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10));
* Table that did not work, making it work:
> ALTER TABLE test ADD d INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (d);

Is there a reason for LibreOffice Base not to allow writing into non-indexed tables?
Comment 2 Alex Thurgood 2014-07-18 09:59:10 UTC
(In reply to comment #1)

> 
> Is there a reason for LibreOffice Base not to allow writing into non-indexed
> tables?

Other than that it has always been this way with the C++ native connector, not that I know of. There is probably some discussion about this in a similarly, closed, previously filed bug report, but I don't remember where.
Comment 3 Alex Thurgood 2014-07-18 10:05:54 UTC
Note also that the connector itslef is a non-bundled extension, i.e. a kind of external project, despite having hooks in the code tree. It is not built by default with the application code, but rather it is the distribs that build the connector as a separate add-on and then provide it in their packaging systems, so in order for this to change, it would require someone to write handling code that would allow non-indexed fields to be writable via the connector. I don't think that this is as trivial to achieve as it might first seem (although I'm not a programmer, so can not estimate the effort required).

At best, this is a RFE, but whether there will ever be any takers to actually do the work is another matter.
Comment 4 Alex Thurgood 2015-01-03 17:38:46 UTC Comment hidden (no-value)