Bug 149434 - MySQL/MariaDB: Database name with dot will be split at first dot, rest will added to table names
Summary: MySQL/MariaDB: Database name with dot will be split at first dot, rest will a...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
7.3.3.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-06-02 16:07 UTC by Shlomo belleli
Modified: 2024-08-05 08:43 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
images inside the zip (62.74 KB, application/octet-stream)
2022-06-02 16:14 UTC, Shlomo belleli
Details
schema and table name apart (20.66 KB, image/png)
2022-06-03 07:51 UTC, Shlomo belleli
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Shlomo belleli 2022-06-02 16:07:01 UTC
Description:
I am using the database tool and I connected to MySql database
I got a list of schemas and tables inside any schema not as in the real world

Actual Results:
in correct table list

Expected Results:
a correct table list


Reproducible: Always


User Profile Reset: No



Additional Info:
look images
Comment 1 Shlomo belleli 2022-06-02 16:14:27 UTC
Created attachment 180541 [details]
images inside the zip

the schemas list is not the same as in the MySql tool
the table list is incorrect there is an addition to any table name
the 0.0 at the start of any table name, from where it comes?
Comment 2 Julien Nabet 2022-06-02 20:44:41 UTC
What connection do you use? Direct connection? ODBC? JDBC?
What's the precise server Mysql/MariaDB version?
Just for the record, we use MariaDB (the Mysql fork) connector.

Is it a new behavior (after some LO upgrade)?
Do you reproduce this with any Mysql database or just some specific ones?

Robert/Alex: thought you might be interested in this one. I never saw this kind of "prefix behavior".
Comment 3 Shlomo belleli 2022-06-02 22:40:25 UTC
Connection - Direct connection
Server - 5.7.26 MySql community server
New behavior - No, After installation, I tried to use the product, and on the first time and all later tries I got the same result (with errors).

Any Mysql database - all my databases
Comment 4 QA Administrators 2022-06-03 03:36:15 UTC Comment hidden (obsolete)
Comment 5 Robert Großkopf 2022-06-03 05:45:31 UTC
Which version of LO do you use?

Won't get such a "prefix" with

Version: 7.3.4.1 / LibreOffice Community
Build ID: 13668373362b52f6e3ebcaaecb031bd59a3ac66b
CPU threads: 6; OS: Linux 5.3; UI render: default; VCL: kf5 (cairo+xcb)
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Calc: threaded

connecting to MariaDB on OpenSUSE 15.3, 10.5.15-MariaDB
Comment 6 Shlomo belleli 2022-06-03 06:52:26 UTC
Version: 7.3.3.2 (x64) / LibreOffice Community
Build ID: d1d0ea68f081ee2800a922cac8f79445e4603348
CPU threads: 4; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win
Locale: he-IL (he_IL); UI: en-US
Calc: threaded
Comment 7 Alex Thurgood 2022-06-03 07:10:20 UTC
Are the schema located on a remote, web hosting server, or a VPS ?
The numbers in front that you point out seem to indicate some kind of versioning?

I don't see the issue using a direct connection (native mysql/mariadb connector) on a locally hosted mysql instance (8.0.27) with

Version: 7.3.1.3 / LibreOffice Community
Build ID: a69ca51ded25f3eefd52d7bf9a5fad8c90b87951
CPU threads: 8; OS: Mac OS X 12.2.1; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded
Comment 8 Shlomo belleli 2022-06-03 07:37:55 UTC
My server and schema are on my local computer
I just made another test with a connection through JDBC driver and the result is the same

but I think I will give you a clue after I looked at the error message

Table 'webstore-2.0.0.adv_board_base' doesn't exist

and I think the problem is the size of the table name
'webstore-2.0.0.adv_board_base'   is the full table name
and this is the partially  error table name 0.0.adv_board_base
the missing part is "webstore-2." from the whole name
Comment 9 Shlomo belleli 2022-06-03 07:50:08 UTC
Furthermore
please look at the image I attached

it looks like there is a new schema named webstore-2
but it is not true my schema name is webstore-2.0.0
and LibreOffice  part the name of the table between un existing schema name
and the wrong table name

the real name of the table is webstore-2.0.0.The_Table_name
and in the error 
the schema name is  webstore-2 (no such schema in my database)
the table name is 0.0.The_Table_name (no such table in my database)
Comment 10 Shlomo belleli 2022-06-03 07:51:34 UTC
Created attachment 180546 [details]
schema and table name apart
Comment 11 Robert Großkopf 2022-06-03 08:06:48 UTC
Have tested with a database name, which contains a dot:
MyTest.1.0
It will be shown as database
MyTest
and the tables inside this database will appear with
1.0Table1
1.0Table2

This will happen with direct connection and JDBC-connection. Will test it also with ODBC, but haven't installed it at this moment.

Could connect to the database in the right way with phpMyAdmin.
Seems LO sees the first dot as separator between database name and table name.
Comment 12 Alex Thurgood 2022-06-03 08:42:19 UTC
Gotta love our schema string parser code :-/
Comment 13 Shlomo belleli 2022-06-03 08:49:59 UTC
It is still less expensive and less dangerous from the bugs in the  list  here
https://www.geeksforgeeks.org/10-famous-bugs-in-the-computer-science-world/
Comment 14 Alex Thurgood 2022-06-03 09:20:10 UTC
Sounds like something not quite right in 

connectivity/source/drivers/mysqlc/mysqlc_databasemetadata.cxx ?

Lines 834-843:
    OUString query = buffer.makeStringAndClear();
    // TODO use prepared stmt instead
    // TODO escape schema, table name ?
    query = query.replaceFirst("?", schemaPattern);
    query = query.replaceFirst("?", tableNamePattern);
    Reference<XStatement> statement = m_rConnection.createStatement();
    Reference<XResultSet> rs = statement->executeQuery(query);
    return rs;
Comment 15 Julien Nabet 2022-06-03 21:19:25 UTC
Here's a bt showing that at the beginning, schema name is ok:
#0  dbtools::impl_doComposeTableName(com::sun::star::uno::Reference<com::sun::star::sdbc::XDatabaseMetaData> const&, rtl::OUString const&, rtl::OUString const&, rtl::OUString const&, bool, dbtools::EComposeRule) (_rxMetaData=uno::Reference to (connectivity::mysqlc::ODatabaseMetaData *) 0x4c47498, _rCatalog="def", _rSchema="test.00", _rName="test", _bQuote=false, _eComposeRule=dbtools::EComposeRule::InDataManipulation)
    at connectivity/source/commontools/dbtools.cxx:833
#1  0x00007f8aa5795b19 in dbtools::composeTableName(com::sun::star::uno::Reference<com::sun::star::sdbc::XDatabaseMetaData> const&, rtl::OUString const&, rtl::OUString const&, rtl::OUString const&, bool, dbtools::EComposeRule)
    (_rxMetaData=uno::Reference to (connectivity::mysqlc::ODatabaseMetaData *) 0x4c47498, _rCatalog="def", _rSchema="test.00", _rName="test", _bQuote=false, _eComposeRule=dbtools::EComposeRule::InDataManipulation) at connectivity/source/commontools/dbtools.cxx:1292
#2  0x00007f8aa585f752 in connectivity::sdbcx::OCatalog::buildName(com::sun::star::uno::Reference<com::sun::star::sdbc::XRow> const&)
    (this=0x4c36e70, _xRow=uno::Reference to (connectivity::mysqlc::OResultSet *) 0x4c7c878) at connectivity/source/sdbcx/VCatalog.cxx:181
#3  0x00007f8aa585f87b in connectivity::sdbcx::OCatalog::fillNames(com::sun::star::uno::Reference<com::sun::star::sdbc::XResultSet>&, std::__debug::vector<rtl::OUString, std::allocator<rtl::OUString> >&)
    (this=0x4c36e70, _xResult=uno::Reference to (connectivity::mysqlc::OResultSet *) 0x4c7c870, _rNames=std::__debug::vector of length 28, capacity 40 = {...}) at connectivity/source/sdbcx/VCatalog.cxx:193
#4  0x00007f8a96bfa225 in connectivity::mysqlc::Catalog::refreshTables() (this=0x4c36e70) at connectivity/source/drivers/mysqlc/mysqlc_catalog.cxx:32

Now the pb arises at this point connectivity/source/commontools/dbtools.cxx
    832     if ( !_rSchema.isEmpty() && aNameComps.bSchemas )
    833     {
    834         aComposedName.append( _bQuote ? quoteName( sQuoteString, _rSchema ) : _rSchema );
    835         aComposedName.append( "." );
    836     }

_bQuote is false so a dot is added in aComposedName and for a schema call "test.00" we get here:
"test.00."
obviously any caller which parses this can't know that the beginning of the table name is after the second dot and not after first dot.

It just reminds me this patch https://gerrit.libreoffice.org/c/core/+/128579 with Lionel's comments.
I struggled some time to make maximum things on mysqlc to work but gave up and try to provide some minimal so with schemas or tables which don't include a dot since it's a kind of special character.

Now I'm far from being an expert or perhaps I missed something obvious, anyway if someone knows how to fix this, you're welcome of course! :-)

Can't help here=>uncc myself.
Comment 16 Robert Großkopf 2022-06-04 06:56:13 UTC
Have tried a little bit more.

"MyTest.1.0" is the database name
"Table" is the table name

"MyTest" as database will appear with "1.0.Table"
and couldn't show the content. Seems GUI will give "MyTest.1.0.Table" as table name.

"MyTest.1.0" will appear also. But GUI won't show any table there.

Query with
SELECT * FROM "MyTest.1.0"."Table"
will show the content of the table. But the table will appear write protected. So you could contact to the tables trough queries, but you couldn't add any data without using tools → SQL or using macro code.
Comment 17 Robert Großkopf 2022-06-04 07:11:06 UTC
Only to complete this:
ODBC connection will give the same buggy behavior in LO.
Comment 18 Robert Großkopf 2022-06-04 07:30:29 UTC
(In reply to Julien Nabet from comment #15)
> 
> It just reminds me this patch https://gerrit.libreoffice.org/c/core/+/128579
> with Lionel's comments.

The bug with the dot appears also in LO 6.4.0.3. This patch had been submitted later, so it would have nothing to do with the buggy behavior.
Comment 19 Alex Thurgood 2022-06-04 13:48:52 UTC
Think you're on the right track here. That append probably shouldn't happen here, but how to avoid that it does for this use case?
Comment 20 Robert Großkopf 2022-06-04 15:59:30 UTC
Think this isn't a specific MySQL driver problem, because it will appear in ODBC, JDBC and with direct connection.

It doesn't appear, for example, in Firebird, because Firebird will only connect to one database and won't show other databases in pane "Tables".

It doesn't appear in PostgreSQL, because there is a schema name like "public", which will separate database name and table name.
Comment 21 QA Administrators 2024-06-04 03:14:33 UTC Comment hidden (obsolete)
Comment 22 Robert Großkopf 2024-06-04 06:24:49 UTC
Bug is still the same with LO 24.2.3.2 on OpenSUSE 15.6 64bit rpm Linux