Ask Your Question
0

Form got broken with Libreoffice Base version 6.1.5.2 and later [MySQL(ODBC)]

asked 2019-03-12 03:24:40 +0200

David M gravatar image

updated 2019-03-12 03:35:40 +0200

Hello:

I have a test database made in MySQL (using MySQL Workbench). This is a simple data base consisting on a form and a subform. I was thinking to migrate an HSQLDB to a MySQL Server, so I was learning about connections between Libreoffice and MySQL using ODBC drivers. I connected LO Base to Mysql as follows: Connect to existing database-->MySQL ; and then Connect with ODBC. Here I choose the DSN made with MySQL Connector ODBC 8.0.15.

Tables are related as shown below (the >> is suposed to show the relation).

tblMain  ----------------------------|   tblSubform 
ID INT(10) (PK, NN, AI, UN)---------->> ID_Mainform  INT(10) (FK, UN)
blah   (VARCHAR)---------------------|  IDSubform INT(10) (PK,NN,AI)
-------------------------------------|  blah VARCHAR

Then I used the LO Base wizard to make a Form with a Subform. This form worked in Libreoffice v6.0.7. I could add data, the autonumbering worked OK, data was stored and retrieved correctly. But with Libreoffice 6.1.5.2 and 6.2.1 the form is broken. When I open the form it gives me this error:

The data could not be loaded: invalid description index.

What could be happening?

I've attached the ODB file, and made a dump fie of the Mysql database. The last one could not be loaded in this forum, so I converted to text and pasted to a Writer Document.

Other info:

  • MySQL Server 5.7.25-log (MySQL Community Server (GPL))

  • Windows 10: Win64 (x84_64)

  • Connector ODBC 8.0.15

  • jre-9.0.1 (is active in Tools->Options->Advanced)

Test01_mysql_ODBC.odb

test01-BK-20190311.odt

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2019-03-12 05:06:01 +0200

Ratslinger gravatar image

Hello,

In the future, when posting a file type which is not accepted, just change the extension to an acceptable one and note what it needs to be changed back to after downloading. Saves others some hassle.

This was just a problem with Firebird but now seems to be popping up with MySQL:

MariaDB/MySQL - tdf#118112

Firebird - tdf#117053

Each requires either a deletion or change of a parameter in a file within the .odb:

1) Unzip the .odb
2) edit content.xml
3) replace
   <db:driver-settings db:parameter-name-substitution="false" />
   by
   <db:driver-settings db:parameter-name-substitution="true" />
4) repack (rezip) the odb with the new content.xml

The Firebird tdf states to change it from false to true & the MySQL tdf states to delete it but changing it to true appears to work as well.

If you are not comfortable with this, my answer in this post -> How to unzip ODB file and edit the content.xml file to fix Firebird bug? contains the macro in a LO extension. The macro can be run to correct your problem. Instructions are in the answer.

Have built the DB with your files, applied the macro to fix the parameter & all works without error.

And to save you ANY effort, here is the fixed .odb -> MySQLodbc.odb

The above information may help others.

edit flag offensive delete link more

Comments

But why edit XML, and not use normal Advanced Settings dialog to set the parameter?

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-12 06:47:24 +0200 )edit

@Mike Kaganski Until your comment had not thought to look at Advanced Settings. From tdf#118112 mentioned, I had not seen this mentioned, or even thought of this, but rather, as stated, that content.xml needed to be modified.

Changing Advanced Settings is certainly the easiest way to go. However, with Firebird one still needs to edit the XML since there is no way to modify Advanced Settings there.

Note to all reading, for MySQL, the setting in Advanced Settings (from main .odb screen menu -> Edit->Database->Advanced Settings...) Ignore the privileges from the database driver must be selected.

Ratslinger gravatar imageRatslinger ( 2019-03-12 17:36:48 +0200 )edit

Thanks to you both! I tried @Ratslinger 's answer and it worked. Both solutions were correct: editing the content.xml and running the macro. I made two copies of the database, so I had one bad database and two corrected databases. All databases had the Ignore the privileges from the database driver checked. I though you should know.

David M gravatar imageDavid M ( 2019-03-12 20:18:30 +0200 )edit

As I am unable to edit my comment above, this will have to do.

The Advanced Setting which needs to be set is actually ParameterNameSubstitution. This may have been on the settings in previous versions, but it is not present on Linux LO v6.2.1.2 and because of that, you must either run the mentioned macro or edit the content.xml file.

Ratslinger gravatar imageRatslinger ( 2019-03-15 02:50:21 +0200 )edit

Can you greate a dummy connection ODB file and put it here? Recently I've fixed a number of advanced settings dialog issues - but I only can do that for a connection type I can create an ODB for.

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-15 05:35:01 +0200 )edit

Mike there are two already in the post. The original in the question with the problem and the same one fixed in my answer. Is this what you need or am I off course?

Ratslinger gravatar imageRatslinger ( 2019-03-15 05:41:07 +0200 )edit

@Ratslinger: you are right! sorry. Checked it. That's quite enough; and yes, the said option is not there. And it wasn't there previously, too - up to v.3.3.0.4...

The default handling has changed in commit https://git.libreoffice.org/core/+/32.... It was backported to 6.1.3. So if that default setting value doesn't allow a connection type to work, and furthermore the connection type is even unable to define the setting, then it should be filed as a bug (separate one). Is tdf#118112 the one?

In my tdf#124022, I proposed to always write all the settings into the ODB, which isn't done now when a setting is equal to a default which is effective for this LO version...

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-15 06:26:18 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-03-12 03:24:40 +0200

Seen: 73 times

Last updated: Mar 12