Bug 146432 - LibreOffice Base crashes when a mysql query with REGEXP_REPLACE() is executed in query editor with direct connection to MariaDB
Summary: LibreOffice Base crashes when a mysql query with REGEXP_REPLACE() is executed...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
7.1.4.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Julien Nabet
URL:
Whiteboard: target:7.4.0 target:7.3.0.2 target:7.2.6
Keywords: haveBacktrace
Depends on:
Blocks:
 
Reported: 2021-12-27 10:46 UTC by Stephan S.
Modified: 2022-01-10 15:58 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
LLDB backtrace on EXC_BAD_ACCESS (13.06 KB, text/plain)
2022-01-03 15:14 UTC, Alex Thurgood
Details
bt with debug symbols (11.20 KB, text/plain)
2022-01-03 19:52 UTC, Julien Nabet
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Stephan S. 2021-12-27 10:46:19 UTC
Description:
I created a query in a LibreOffice Base document that connects to a mysql database and runs the following SQL:
SELECT REGEXP_REPLACE( rpath, '/[^/]*$', '' ) FROM urls;
Base crashes every time this query is executed. No output on stdout, no output on stderr, no segfault in dmesg, no window with explanation.
The query works fine in the mysql command line interface. 
Mysql version: mariadb 10.5.13

Steps to Reproduce:
1. Create a mysql database with a table "urls" and a column "rpath"
2. Create a Libreoffice Base document that connects to this database
3. Create a new query and enter "SELECT REGEXP_REPLACE( rpath, '/[^/]*$', '' ) FROM urls;" in SQL edit mode
4. Run this query

Actual Results:
LibreOffice Base crashes

Expected Results:
Show the results as a table with one column of strings


Reproducible: Always


User Profile Reset: No


OpenGL enabled: Yes

Additional Info:
Version: 7.1.4.2 / LibreOffice Community
Build ID: 10(Build:2)
CPU threads: 12; OS: Linux 5.3; UI render: default; VCL: gtk3
Locale: de-DE (de_DE.utf8); UI: de-DE
Calc: threaded
Comment 1 Alex Thurgood 2021-12-27 11:27:55 UTC
@Stephan : isn't that query considered an update query ?

If so, then I don't believe it would work from the Query UI, as the UI doesn't allow UPDATE statements to execute,

An exception (I haven't tested) might be that if you execute the query in "Direct SQL mode" from the Query UI, i.e. avoiding the built-in SQL parser.

It shouldn't crash though, rather it should display an error message.

I'm not even sure that the native connector code supports REGEXP_REPLACE in any case (but I haven't checked).

What happens if you run that query from Tools > SQL rather than from the Query UI?
Comment 2 Alex Thurgood 2021-12-27 11:29:55 UTC
See this discussion here, which only related to the use of REGEXP() and not REGEXP_REPLACE() :

https://forum.openoffice.org/fr/forum/viewtopic.php?f=9&t=51477
Comment 3 Stephan S. 2021-12-28 20:59:38 UTC
Hello @Alex, thanks for responding so quickly. The query is SELECT rather than UPDATE. It does not change anything in the database. Instead, it only manipulates the string after retrieving from the database and before printing it. Suppose there is an entry rpath='/usr/local/bin/whatever' then this particular query should return '/usr/local/bin'. I get exactly this result from Tools->SQL with status "1: Command successfully executed."

I'm not sure what to make of the forum discussion that you mentioned. I could not figure out what the actual problem was; it sounds to me like a syntax parser issue rather than a crash. I had to use an online translator, though.
Comment 4 Stephan S. 2021-12-28 21:01:43 UTC Comment hidden (obsolete)
Comment 5 Alex Thurgood 2021-12-29 11:52:20 UTC
@Stephan: does your table contain a primary key field as well ?

As far as I recall, the native driver via the SQL parser requires a primary field in order to be able to fetch a resultset and assign a relevant row cursor in order to be able to traverse the resultset.

If it doesn't have this, it is conceivable that the result of trying to execute the query might lead to an unmanaged exception and then a crash.

I don't suppose you have any debug output by any chance when the crash occurs ?

I'll try and see if I can reproduce this on my mac, but it may not generate any useful debug/trace output.
Comment 6 Alex Thurgood 2021-12-29 12:12:23 UTC
Testing with 

Version: 7.4.0.0.alpha0+ / LibreOffice Community
Build ID: 1d21a51d814b39711cb1cc7f925b0c620b42eaa7
CPU threads: 8; OS: Mac OS X 10.16; UI render: Skia/Metal; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: en-US
Calc: threaded

on a mysql table that only contains a single field "rpath" in a "urls" table, with no primary key defined, I get the following error message when I enter the following query into the Query UI using "Create Query in SQL View" :

The data content could not be loaded.

SQL Status: 42000
Error code: 1305

FUNCTION test.REGEXP_REPLACE does not exist

The SQL command leading to this error is:

SELECT REGEXP_REPLACE( rpath, '/[^/]*$', '' ) FROM urls

I guess my version of mysql doesn't support the REGEXP_REPLACE function, sorry.

Just checked : MySQL 5.7.15 indeed doesn't appear to have this function.
Comment 7 Robert Großkopf 2021-12-30 07:58:18 UTC
Have tested this with 10.5.13-MariaDB on OpenSUSE 15.3 64bit rpm Linux.

Created the table with the one field. Set this field to primary key.
Then added content to the first row and tested with phpMyAdmin.
Works as expected.
Opened MariaDB-Connection with Base.
Started in Tools → SQL the same query.
Selected "Show output …".
Output will be shown, no problems appear.

Testing with direct connection:

When doing the same in Query editor LO will crash immediately. Could also mask the content by double quotes like
SELECT REGEXP_REPLACE( "rpath", '/[^/]*$', '' ) FROM "test"."urls" AS "urls"
Crash will be the same.

Testing with JDBC connection:

No crash, query will be executed without problems.

All tested with LO 7.3.0.1.
Comment 8 Stephan S. 2022-01-03 10:39:16 UTC
To answer earlier questions: I have been using the built-in mysql connector and there is indeed another column ("id") as primary key.
Comment 9 Alex Thurgood 2022-01-03 15:10:50 UTC
I have now managed to test this against a MySQL server running 8.0.27 and

Version: 7.4.0.0.alpha0+ / LibreOffice Community
Build ID: 1d21a51d814b39711cb1cc7f925b0c620b42eaa7
CPU threads: 8; OS: Mac OS X 10.16; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: en-US
Calc: threaded

Confirming crash.

Loading the test ODB file when running a LLDB debug session displays the following on the terminal :

(lldb) target create "/Applications/LibreOfficeDev.app"
Current executable set to '/Applications/LibreOfficeDev.app' (x86_64).
(lldb) run
Process 15615 launched: '/Applications/LibreOfficeDev.app/Contents/MacOS/soffice' (x86_64)
2022-01-03 15:55:58.866763+0100 soffice[15615:523273] SecTaskLoadEntitlements failed error=22 cs_flags=20, pid=15615
2022-01-03 15:55:58.867134+0100 soffice[15615:523273] SecTaskCopyDebugDescription: soffice[15615]/0#-1 LF=0
warn:legacy.osl:15615:523273:dbaccess/source/core/dataaccess/ModelImpl.cxx:764: ODatabaseModelImpl::getOrCreateRootStorage: no source to create the storage from!
warn:legacy.osl:15615:523273:dbaccess/source/core/dataaccess/ModelImpl.cxx:764: ODatabaseModelImpl::getOrCreateRootStorage: no source to create the storage from!
warn:legacy.osl:15615:523273:dbaccess/source/core/dataaccess/ModelImpl.cxx:764: ODatabaseModelImpl::getOrCreateRootStorage: no source to create the storage from!
warn:dbaccess:15615:523273:dbaccess/source/filter/xml/xmlTable.cxx:78: unknown attribute urn:oasis:names:tc:opendocument:xmlns:database:1.0 db:command value=SELECT REGEXP_REPLACE( rpath, '/[^/]*$', '' ) FROM urls
warn:dbaccess:15615:523273:dbaccess/source/filter/xml/xmlTable.cxx:78: unknown attribute urn:oasis:names:tc:opendocument:xmlns:database:1.0 db:escape-processing value=false
warn:dbaccess:15615:523273:dbaccess/source/filter/xml/xmlQuery.cxx:56: unknown attribute urn:oasis:names:tc:opendocument:xmlns:database:1.0 db:name value=REGXPURL
2022-01-03 15:57:59.494024+0100 soffice[15615:523273] SecTaskLoadEntitlements failed error=22 cs_flags=20, pid=15615
2022-01-03 15:57:59.494164+0100 soffice[15615:523273] SecTaskCopyDebugDescription: soffice[15615]/0#-1 LF=0



Ignoring the macOS security permissions error messages...

From the above, we can see that the XML filter code used for parsing the ODB file doesn't know how to parse the query reference (which I'd previously stored in the ODB file when testing against a mysql 5.7 server), as shown by the following warning :

warn:dbaccess:15615:523273:dbaccess/source/filter/xml/xmlTable.cxx:78: unknown attribute urn:oasis:names:tc:opendocument:xmlns:database:1.0 db:command value=SELECT REGEXP_REPLACE( rpath, '/[^/]*$', '' ) FROM urls
warn:dbaccess:15615:523273:dbaccess/source/filter/xml/xmlTable.cxx:78: unknown attribute urn:oasis:names:tc:opendocument:xmlns:database:1.0 db:escape-processing value=false
warn:dbaccess:15615:523273:dbaccess/source/filter/xml/xmlQuery.cxx:56: unknown attribute urn:oasis:names:tc:opendocument:xmlns:database:1.0 db:name value=REGXPURL

When I then try to execute the saved query, e.g. by double-clicking on it in the UI, I get the following output :

warn:legacy.osl:15615:523273:connectivity/source/drivers/mysqlc/mysqlc_general.cxx:188: mysqlToOOOType: unhandled case, falling back to VARCHAR
Process 15615 stopped
* thread #1, queue = 'com.apple.main-thread', stop reason = EXC_BAD_ACCESS (code=1, address=0x0)
    frame #0: 0x00007ff8090258e2 libsystem_platform.dylib`_platform_memmove$VARIANT$Rosetta + 482
libsystem_platform.dylib`_platform_memmove$VARIANT$Rosetta:
->  0x7ff8090258e2 <+482>: movq   %r8, (%rcx)
    0x7ff8090258e5 <+485>: addq   $0x8, %rcx
    0x7ff8090258e9 <+489>: subq   $0x8, %rdx
    0x7ff8090258ed <+493>: jae    0x7ff8090258db            ; <+475>
Target 0: (soffice) stopped.


This shows that mysqlc_general.cxx doesn't know how to handle the data type being provided by the function and therefore falls back to VARCHAR, which causes LO to die.

Attaching backtrace of lldb session at point where EXC_BAD_ACESS occurs.
Comment 10 Alex Thurgood 2022-01-03 15:14:51 UTC
Created attachment 177288 [details]
LLDB backtrace on EXC_BAD_ACCESS
Comment 11 Alex Thurgood 2022-01-03 15:16:33 UTC
@Julien : putting you on cc in case you're interested in having a look at this one.
Comment 12 Julien Nabet 2022-01-03 19:52:30 UTC
Created attachment 177292 [details]
bt with debug symbols

On pc Debian x86-64 with master sources updated today, I could reproduce this.

I also noticed this on console:
warn:legacy.osl:7697:7697:connectivity/source/drivers/mysqlc/mysqlc_general.cxx:188: mysqlToOOOType: unhandled case, falling back to VARCHAR

After some debugging it seems it's because we get MYSQL_TYPE_LONG_BLOB which isn't listed in sal_Int32 mysqlToOOOType(int eType, int charsetnr)
Comment 13 Julien Nabet 2022-01-03 20:24:24 UTC
I gave a try with https://gerrit.libreoffice.org/c/core/+/127919
Comment 14 Alex Thurgood 2022-01-04 08:12:13 UTC
Still crashes for me with

2022-Jan-04 03:43
Version: 7.4.0.0.alpha0+ / LibreOffice Community
Build ID: 9cc3672a4d7e9c9a647d388dba493bbb28d96da2
CPU threads: 8; OS: Mac OS X 10.16; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: en-US
Calc: threaded

Guess it didn't make it into that build ?
Comment 15 Julien Nabet 2022-01-04 17:08:14 UTC
(In reply to Alex Thurgood from comment #14)
> Still crashes for me with
> ...
> Guess it didn't make it into that build ?

Sorry, the patch hasn't been merged yet, it was waiting for review.
I had a feedback from Lionel that I must take into account.
Comment 16 Commit Notification 2022-01-04 18:08:47 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/e43573aae0fa07d170fb042b7184156c851c1f77

tdf#146432: fix crash with REGEXP_REPLACE() executed in query editor to MariaDB

It will be available in 7.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 17 Julien Nabet 2022-01-04 18:10:06 UTC
And now the patch has been pushed on master sources :-)

Patch for 7.3 waiting for review here:
https://gerrit.libreoffice.org/c/core/+/127952

Not sure it worths it to backport on 7.2 branch.
Comment 18 Commit Notification 2022-01-04 19:27:12 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-7-3":

https://git.libreoffice.org/core/commit/6f9278886eec76fdba19e2763c194fc129a2a42e

tdf#146432: fix crash with REGEXP_REPLACE() executed in query editor to MariaDB

It will be available in 7.3.0.2.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 19 Stephan S. 2022-01-05 12:47:16 UTC
Hello, just to confirm that my problem is fixed with this daily build on Linux (opensuse 15.3 x86_64):

Version: 7.4.0.0.alpha0+ / LibreOffice Community
Build ID: 22f52db0be81ceec2aa7a61f7092b54f36e2d00c
CPU threads: 2; OS: Linux 5.3; UI render: default; VCL: gtk3
Locale: de-DE (de_DE.utf8); UI: en-US
Calc: threaded
Comment 20 Julien Nabet 2022-01-05 12:59:37 UTC
(In reply to Stephan S. from comment #19)
> Hello, just to confirm that my problem is fixed with this daily build on
> Linux (opensuse 15.3 x86_64):
> ...
Thank you for your feedback, let's put this one to VERIFIED then.
Comment 21 Alex Thurgood 2022-01-06 13:55:04 UTC
(In reply to Julien Nabet from comment #20)


> Thank you for your feedback, let's put this one to VERIFIED then.

Many thanks to you Julien, for implementing this so quickly !
Comment 22 Stephan S. 2022-01-06 15:29:48 UTC
Yes, many thanks from me, too!
Comment 23 Julien Nabet 2022-01-06 17:18:09 UTC
You're welcome! :-)
I had some luck, this one wasn't too difficult, I just had to apply an existing mechanism (for plain BLOB) for just other types of BLOBs.
Comment 24 Commit Notification 2022-01-10 15:58:13 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-7-2":

https://git.libreoffice.org/core/commit/0dec041cb8156afb4d022d94c4c6a1d9fa222f91

tdf#146432: fix crash with REGEXP_REPLACE() executed in query editor to MariaDB

It will be available in 7.2.6.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.