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

[DavidM2]

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

[Ratslinger]

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.

[mikekaganski]

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

[Ratslinger]

@mikekaganski 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.

[DavidM2]

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.

[Ratslinger]

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.

[mikekaganski]

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.

[Ratslinger]

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?

[mikekaganski]

@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 3208fcb3a36d75d6290d9c548430682f153b09db - core - Gitiles. 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…

[EasyTrieve]

1) For me I found that the search/replace strings in the unzipped content.xml file were NOT as listed above, but rather like these:

<db:driver-settings db:system-driver-settings="" db:base-dn="" db:parameter-name-substitution="false"/>
<db:driver-settings db:system-driver-settings="" db:base-dn="" db:parameter-name-substitution="true"/>

(I don’t know why, but these are what I found I had to change).

---

2) And as I had a lot of databases to update in linux, I wrote this bash script to process them all at once. Be sure to backup before you use it! (Note: I left a couple of show artifacts in it in case they are needed. I found that some of my tree needed to be cleaned up before this would fully work.)

Usage: cd to the directory tree where you have your odb files. Then create a file called FixLO5.sh and paste the script below in it. Give it execute permissions: chmod u+x FixLO5.sh. Run this script there: ./FixLO5.sh:

#!/bin/bash

# THIS FIXES OLDER LIBREOFFICE DATABASES 


# === Constants ==========================================================================
version=1

filename='content.xml';

seeking='<db:driver-settings db:system-driver-settings="" db:base-dn="" db:parameter-name-substitution="false"/>';
pasting='<db:driver-settings db:system-driver-settings="" db:base-dn="" db:parameter-name-substitution="true"/>';


# === Version & Usage =====================================================================
if [ "$1" = "--version" 			]; then echo "Version $version"		; exit; fi
if [ "$1" = '--help' -o "$1" = -h 	]; then echo "Usage:  FixLO5.sh"	; exit; fi


# === Functions ==========================================================================
#unzip, edit and re-zip
unFind() { 
	unzip -q "$1" 							"$filename";
	sed -i -e "s|${seeking}|${pasting}|g"	"$filename";
	  zip "$1" 								"$filename";
	  rm 									"$filename";
}

#Only grep for what we are seeking
Show() {
	unzip -q "$1" 							"$filename";
	grep -e "$seeking"						"$filename"; 
	  rm 									"$filename";
}

#Only grep for what we have done
Show2() {
	unzip -q "$1" 							"$filename";
	grep -e "$pasting"						"$filename"; 
	  rm 									"$filename";
}


############################################################################################
### MAIN LINE ##############################################################################
############################################################################################

#get tree of files and call sub-function on each one
while  IFS='' read -r  -a line; do
	echo -n "$line --- ";
	cd   "$(dirname "$line")";
#		  Show  "$(basename "$line")";
		unFind  "$(basename "$line")";
#		  Show2 "$(basename "$line")";
	cd - > /dev/null;
#	echo "done";
#	echo
done <<< "$(find ./ -name '*.odb' -type f;)"

[dino.moser]

Thank you! This script is fantastic!