Possible synch error between MySQL and Base

[marus_b]

Hello all,

To begin with:

LO:

Version: 7.5.0.3 (X86_64) / LibreOffice Community
Build ID: c21113d003cd3efa8c53188764377a8272d9d6de
CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win
Locale: en-CA (en_CA); UI: en-US
Calc: threaded

I am creating a database in MySQL, and I have it connected to Base via MySQL/Maria Connector. It’s a fresh install, thus, I’d imagine it’s most likely the very latest version of the community edition.

Nonetheless, tables and relationships are populating. I am able to pull the data out, without any problems. However, I wanted to write a macro to check for repetitive entries. And when I got to listing of the tables it is showing me old ones, which I did and subsequently deleted plus a few of the currently existing ones. I’m wondering what am I missing here.

[Wanderer]

As drop table autocommits I don’t know why they would be in the lists. However you are not the first to notice:

MySQL deleted tables stuck in db

Did you delete the other tables in the same session, so we could guess for not updated cached tables?

[RobertG]

Got the same here. But don’t think it is a sync error. I will see 32 tables in database test, but there will be only available 10 tables when looking at the database browser in Base and also database browser in phpMyAdmin. Also SHOW TABLES executed in Tools → SQL will only show this 10 tables.

The different 22 tables are very old tables. Tablenames must be saved somewhere in the database, because MariaDB-server is installed here on this system and will shutdown every day together with the system.

I have deleted 2 tables, which could be seen in table browser of Base and phpMyAdmin. Both won’t appear any more when running the macro.

[marus_b]

The problem is that I could have deleted them using SQL command in MySQL Workbench or I could have deleted them via GUI in Base. Most likely a mix of both. I wasn’t paying attention, as I did not expect this to have happened. Subsequently I tried to recreate table of the same name using both, SQL and GUI in Workbench, only to delete it right after. But the record still exists.

[RobertG]

Don’t know where it come from, but you will get the same tables in Base GUI and with SHOW TABLES in Tools → SQL. You will only get this old table by reading table names by macro.

Have opened such a *.odb-file and had a look at the content.xml. The table names will be read out of this content.xml. So oDataource.getTables won’t show the right table names at all. I have created a new connection to my database and the command you posted as screenshots won’t show any table here…

[marus_b]

Good stuff… thank you for all your help.

[marus_b]

I just connected from LO Base via ODBC Unicode driver, and it shows all tables correctly. However, I set up two tables, both were set up in Base GUI. Then I deleted one with Base GUI and the other was deleted from Workbench GUI (not sql - just right click with a mouse and ‘drop’). The latter still shows in my macro output. But at least I’m showing all the existing tables (at least for now).

I tried to use “show tables” from tools/sql in LO GUI and run the command directly, but it keeps crashing LO, so I cannot check that. What’s also interesting is that this method of connectivity does not let me set up relationship in LO GUI. Thus, I’d need to resort doing everything with SQL in Workbench. Not the end of the world, but it seems that both connectors, in their own way, have some form of a malfunctioning mechanism within.

[Ratslinger]

@marus_b
Looked at this last night for some time and not until your last post did I have a hint as to where to look.
.
Per @RobertG the item is listed in content.xml. Items are only getting into that file when the table is generated using the Base GUI (did not check through SQL). When that happens you can produce the list you want (I used Mri). If you delete the table in Base, the entry is deleted and all is OK. If you create or delete a table outside of Base, like in MySQL Workbench, the listing is not correct. It never gets added or deleted.
.
If the table is created in Workbench and not seen in Base, then by editing the table in Base through the GUI makes it visible. Similar with delete; create min table with same name then delete.
.
This is a bug and the Base info should be updated from the database every time it is opened and/or when modified (not clear how that would work). Have also seen this now in PostgreSQL. Probably other databases as well.
.
Have not tried this with embedded databases using SQL to create/delete a table.

[RobertG]

Have written a bug description for this: bug 154037

[marus_b]

How does this translate into an everyday utilization of LO Base as a front end to MySQL or Postgres? Does it become more problematic down the road?

[RobertG]

You will only see this problem when asking for oDataSource.getTables().
GUI works with MySQL/MariaDB (some special bugs as it is also for every database connected with Base - see bugtracker). If you need all the tables start SQL with SHOW TABLES instead.

[marus_b]

I just wanted to add another solution. It just occurred to me that one does not necessarily have to rely on LO to do this job. Why not just go directly to the source to get the info:

Sub readTableNames_sql
dim oBase as object: oBase = thisDataBaseDocument
dim oConnection as object: oConnection = oBase.CurrentController.ActiveConnection
dim oQuery as object: oQuery = oConnection.createStatement()
dim oResult as object: oResult = oQuery.executeQuery(“show tables”)

oResult.next

do
msgbox oResult.getString(1)
oResult.next
loop while not oResult.isAfterLast()
End Sub

[marus_b]

I’m going back to this post, as I believe I may have found a solution. AndrewBase 2.2.4 speaks precisely about this issue. There is a workaround listed there, which I have slightly modified, and it seems to be working just fine:

sub refreshTables()
dim dbURL as string: dbURL = ConvertToURL(“C:\filename.odb”)
dim oBaseContext as object: oBaseContext = CreateUnoService(“com.sun.star.sdb.DatabaseContext”)
dim oDB as object: oDB = oBaseContext.getByName(dbURL)
dim oDoc as object: oDoc = oDB.DatabaseDocument
dim oDisp as object: oDisp = createUnoService(“com.sun.star.frame.DispatchHelper”)
dim oFrame as object: oFrame = oDoc.getCurrentController().getFrame()

oDisp.executeDispatch(oFrame, “.uno:DBRefreshTables”, “”, 0, Array())
end sub

[flywire]

@marus_b

AndrewBase 2.2.4

Download from OpenOffice.org Database Explained - 2.2.4 Refresh the tables

[marus_b]

Thank you. My last post refers precisely to that. I just came across it a couple of days ago.

[RobertG]

Have just tested the macro. It is the same what I get when pressing View → Refresh Tables. It has nothing to do with the reported problem. The entries in the database file in content.xml will almost be there after executing the macro.