Base connection to MySQL reveals ALL databases

[notorious.dds]

Hi All,

I appologize if this has already been asked an answered. (If so, it's buried in the mess of threads about issues just trying to successfully connect OO Base to MySQL generally.)

At any rate, I've successfully connected Base to my MySQL server. My issue is that that ALL of the MySQL databases get connected even though I'm specifying just one database (by name) when configuring my JDBC connection using the OO Database Wizard.

Is this normal? If so, is there any way to prohibit this other that creating a new MySQL user that only has access to the one database to which I'm desiring to connect?

Thanks!

[Villeroy]

It is not a known issue or anything I heard of. Are you confusing tables with databases? What makes you think that the Base document is connected with multiple databases? Can you post a screenshot from the tables section? What is your connection URL? What do you get when you connect another frontend to the same URL?

[notorious.dds]

Thank you for your reply Villeroy.

What's funny is that when looking at my MySQL server via phpMyAdmin, it has my multiple databases listed (as expected) as "databases". However, when I connect to the server via OpenOffice (specifying one database), it gives me access to all of my multiple databases and considers them to be "Tables".

I suspect the behavior is probably "normal" just unexpected by me simply because I'm new to this process.

Regardless, I created a new MySQL user and limited access to just the desired database. Now, when I connect via OpenOffice using this new user, I only see the database I'm interested in.

[Ratslinger]

I can say this is not normal. Only have MySQL now for testing (and use it with LO) but have seen this issue since using the JDBC J8 connector. Just re-tested with MySQL 8.x & 5.x servers and both produced the same result. With the 5.x server the problem went away when I changed to the JDBC 5.1.45 connector. 8.x server didn't like this connector but did test with ODBC connection and it was OK.
Testing was minimal but produced some results.
My preference is PostgreSQL mostly because of actual databases and multiple schema for each (but do have some other reasons). In MySQL, as the documentation states, there is no difference when creating a database or schema.

[GowerMick]

I have a similar problem with LibreOffice. My MySQL database contains a few schema, and when I open my LibreOffice Database to access a specific schema, all schema are listed!.

i have several forms in my LO databse, which correctly access the required table in the correct schema, but if I wish to examine a table, I have to firstly select the correct schema then the requisite table.

Is there a way to specify to my Libreoffice database which schema should be connected when I open it?

[notorious.dds]

Is there a way to specify to my Libreoffice database which schema should be connected when I open it?

The only way I found to make this work is to create another user on the MySQL server and limit it's permissions to the table(s) desired... unfortunately.

For what it's worth, I tried specifing the MySQL database via URL (with desired DB in the URL) as a JBDC connection, but didn't make much difference.

What I noticed after creating the new MySQL user was that upon connecting as database type "MySQL", OO still listed the "information_schema" database along with the one(s) to which I permitted access. If I changed the connection type to "JBDC" and specified the database via URL, I no longer had to see the "information_schema" database.

Hope that helps.

[Ratslinger]

@GowerMick
Have noted that using an ODBC connector has worked for me in Ubuntu 20.x

With keeping JDBC, you can eliminate the display of unwanted schema from the main screen menu.

From Tools select Table Filter.. and uncheck those not wanted. Afterward refresh tables - while in Tables section, from menu View->Refresh Tables.

[notorious.dds]

With keeping JDBC, you can eliminate the display of unwanted schema from the main screen menu.

From Tools select Table Filter.. and uncheck those not wanted. Afterward refresh tables - while in Tables section, from menu View->Refresh Tables.

Yeah, I tried that. But, it didn't seem to stick once the .ODB file was closed and reopened. Is there any way to maintain those settings between sessions?

[Ratslinger]

@notorious.dds
with:
Version: 7.3.2.2 / LibreOffice Community
Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

There is nothing else to do. The settings remain. Have not used OpenOffice in some time now.

[GowerMick]

Firstly may I point out that in essence, the terms database and schema are synonymous within Mysql, so where LO refers to database, you may take it that it when using Mysql, it actually refers to Mysql schema!

I have had partial succes by assigning different users for each of my odb databases. Then rerstricting these users to the specific schema they need access to. e.g. I have an LO database called 'bookings.odb", which only needs access to the bookings schema in Mysql.

Using Mysql Workbench, I created a user called bookings. Then in 'Users and Privileges', I assigned schema priviledge to the bookings schema only.
Then when I open 'bookings.odb', I need to sign in with user 'bookings' for the schema restrictions to be applied

Then when I view the tables in bookings.odb, I only see the bookings schema and the information_schema. It would seem I cannot stop the
information_schema from appearing, but as schema are listed alphabetically, bookings takes preferenc at top of the table list, so I can live with it.

I no longer see all my other schema that I have in mysql, so a partial success.

WARNING When playing with Users and priviledges, be careful you don't lock yourself out. Do a Mysql backup data dump first, so you don't lose data if things go wrong. I ended up re-installing Mysql and potentially lost all my data since the last and infrequent backup - lesson learned.

[Bidouille]

Yeah, I tried that. But, it didn't seem to stick once the .ODB file was closed and reopened. Is there any way to maintain those settings between sessions?

I have no problem to connect OpenOffice with MySQL.
I use two stuffs:

• the native connector found here
• the mini server portable for Windows