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
@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?
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
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.
@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.
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.
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.
To answer earlier questions: I have been using the built-in mysql connector and there is indeed another column ("id") as primary key.
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.
Created attachment 177288 [details] LLDB backtrace on EXC_BAD_ACCESS
@Julien : putting you on cc in case you're interested in having a look at this one.
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)
I gave a try with https://gerrit.libreoffice.org/c/core/+/127919
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 ?
(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.
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.
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.
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.
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
(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.
(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 !
Yes, many thanks from me, too!
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.
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.