Description: On Ubuntu 20.04 LTS, I am using MySQL 8 and I have build many Databases already. Today I was accessing these databases, but there is a problem opening/viewing a table. The table causing an error via LibreOffice 7.2 was created with the following "Create Table" statement:CREATE TABLE Leverancieren (ID smallint(5) unsigned NOT NULL AUTO_INCREMENT,Tllr mediumint(8) unsigned NOT NULL,X bit(1) DEFAULT NULL,Naam varchar(28) NOT NULL,Straat_ID mediumint(9) NOT NULL,Huisnr varchar(7) NOT NULL,Busnr varchar(6) DEFAULT NULL,PostcodeGemeente_ID smallint(5) unsigned NOT NULL,Bedrijvigheid varchar(50) DEFAULT NULL,TelGSM varchar(12) DEFAULT NULL,website varchar(54) DEFAULT NULL,Overschrijvingnr varchar(14) DEFAULT NULL,PRIMARY KEY (ID),KEY Tllr (Tllr),KEY Straat_ID (Straat_ID),KEY PostcodeGemeente_ID (PostcodeGemeente_ID)); Is it "X bit(1) DEFAULT NULL" that leads to a problem? Is LibreOffice 7.2 not completely compatible with MySQL? Steps to Reproduce: 1.Create a database and table with given Create statement in MySQL 8 2.Access that database and table via a direct LibreOffice 7.2 Database connection. 3. Actual Results: Following error-screen appears: LibreOffice Base The data content could not be loaded. /build/libreoffice/- parts/libreoffice/build/connectivity/source/commontools/- dbtools.cxx:751 Unknown column type when fetching result ... Expected Results: Normal opening of the mentioned table. Reproducible: Always User Profile Reset: No Additional Info: [Information automatically included from LibreOffice] Locale: en-US Module: SpreadsheetDocument [Information guessed from browser] OS: Mac OS X (All) OS is 64bit: no
Created attachment 175817 [details] When I try to open a certain database table, this appears.
Created attachment 176284 [details] screenshot SQL error msg
Created attachment 176285 [details] screenshot wrong field type
I am able to reproduce the problem on my Win10 system. I get an SQL status:22000 Error code:1. In Base, column X shows as a date, not a binary (see screenshot). I have a similar problem, with other field type (mediumblob fields show as varchar text). I just reported bug 145714 for this. Maybe a Duplicate ?
@Alexander That error message is thrown when the connector is incapable of recognizing a field data type. Below are the currently defined/recognized field types : case MYSQL_TYPE_TINY returns getByte(nColumnIndex); case MYSQL_TYPE_SHORT returns getShort(nColumnIndex); case MYSQL_TYPE_LONG and case MYSQL_TYPE_INT24 returns getInt(nColumnIndex); case MYSQL_TYPE_LONGLONG returns getLong(nColumnIndex); case MYSQL_TYPE_FLOAT returns getFloat(nColumnIndex); case MYSQL_TYPE_DOUBLE returns getDouble(nColumnIndex); case MYSQL_TYPE_TIMESTAMP and case MYSQL_TYPE_DATETIME returns getTimestamp(nColumnIndex); case MYSQL_TYPE_DATE returns getDate(nColumnIndex); case MYSQL_TYPE_TIME returns getTime(nColumnIndex); case MYSQL_TYPE_STRING and case MYSQL_TYPE_VAR_STRING and case MYSQL_TYPE_DECIMAL and case MYSQL_TYPE_NEWDECIMAL all return getString(nColumnIndex); case MYSQL_TYPE_BLOB throws an SQLException("Column with type BLOB cannot be converted", *this, "22000", 1, Any()). I don't see a specific "BIT" data type in the list above, which would probably explain why the error is thrown. If confirmed, this could be made into a RequestForEnhancement for someone to add support for the BIT datatype. As a comparison, did you try the same thing over a JDBC connector (msyql JDBC connector or the mariadb JDBC connector) ?
I'm curious whether the BIT datatype was recognised by the "old" MySQL-C++-connector-based "native" SDBC driver, and thus whether this is a regression from our chucking it out and replacing it by our own layer directly to the C client library. Theoretically, the LibreOffice SDBC API supports the BIT datatype https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sdbc_1_1DataType.html#a35315c070602fedc47a2c2daf137895c so it could be straightforward to add. I'm not 100% sure the rest of LibreOffice (e.g. the UI elements / the controls) has decent support for BIT.
Just for the record, on pc Debian x86-64 with master sources updated today or with LO Debian package 7.2.2.1, I don't have an error but X field is indicated as a DATE. Remarks: - I got 10.5.12-MariaDB-1 server - Tllr is indicated as INTEGER[INTEGER] 8 (whereas it's MEDIUMINT 8) - Straat_ID is indicated as INTEGER[MEDIUMINT] 7 (whereas it's MEDIUMINT 9)
I gave a try here: https://gerrit.libreoffice.org/c/core/+/125429 As I put in the comment: " Beware, we just want to display the right info here, BIT management in Mysql/MariaDB needs some work There are 2 parts in this patch: 1) - if (sType.equalsIgnoreAsciiCase("bit") || sType.equalsIgnoreAsciiCase("bool") - || sType.equalsIgnoreAsciiCase("boolean")) + if (sType.equalsIgnoreAsciiCase("bit")) + return css::sdbc::DataType::BIT; + if (sType.equalsIgnoreAsciiCase("bool") || sType.equalsIgnoreAsciiCase("boolean")) allows to display BIT instead of DATE when editing the table 2) - return css::sdbc::DataType::VARCHAR; + return css::sdbc::DataType::BIT; allows to show a checkbox instead of a field where you can type anything when opening the table"
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/6bb6e59ca2b40958504ac176cab448de3fe0a2a9 tdf#145205: Mysql/MariaDB, display BIT instead of DATE when its a BIT field It will be available in 7.3.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.
Patch waiting for review here for 7.2 branch: https://gerrit.libreoffice.org/c/core/+/125447 Let's put this one to FIXED. Don't hesitate to reopen this tracker if you reproduce the pb with a build which includes the patch.
Hi Julien, With Version: 7.3.0.0.alpha1+ / LibreOffice Community Build ID: 4be0ae19065b1b50870bc0b2a28189ad39c96a8a 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 I still get the same error message when attempting to load the table. SQL Status: 22000 Error code: 1 Unknown column type when fetching result Did your commit not make into that build ?
(In reply to Alex Thurgood from comment #11) > > Did your commit not make into that build ? My bad, I see that your fix only went in this morning, sorry.
@Julien : hmm, still seeing the same error message with today's master build (19/11/2021) for macOS: Version: 7.3.0.0.alpha1+ / LibreOffice Community Build ID: 11800469cc7b3a40c42410be93a12e5107db0efb 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 Using mysql 5.7.15-log osx10.11 (x86_64)
It seems MacOs only pb. Indeed, I gave a try on Win10 with master sources updated today, no crash. Would it be possible someone on a MacOs attach a bt (see https://wiki.documentfoundation.org/QA/BugReport/Debug_Information#macOS:_How_to_get_debug_information)?
(In reply to Julien Nabet from comment #10) > Patch waiting for review here for 7.2 branch: > https://gerrit.libreoffice.org/c/core/+/125447 > > Let's put this one to FIXED. > > Don't hesitate to reopen this tracker if you reproduce the pb with a build > which includes the patch. Just for the record. Patch for 7.2 abandoned, no need to let it rot. So it will be available from 7.3.0 Anyway, since it still fails, it won't change much.
Could you please retest this with LO 7.3.0? I have just detected the same behavior in older versions, but with LO 7.3.0 the bug has been gone …
Today - since I have installed LibreOffice 7.3 - I tested the MySQL table with the 'bit' field again, and it works fine, thank you all!
Thank you Alexander for your feedback! Let's put this one to FIXED then. Alex: could you give a new try? Indeed if you still reproduce this, it means there's another pb, perhaps something specific to MacOS.
and since the author confirmed it worked, let's put this one to VERIFIED.