MySQL Native Driver: Known Problems
This page lists the currently known problems with the MySQL Native Driver, aka MySQL Connector/OOo. A list of fixed problems is also available.
- 1 End User Observations
- 1.1 Template for new entries
- 1.2 On windows, the extension crashs on some systems
- 1.3 Copying tables may not work properly
- 1.4 Wrong column width reported for text/binary columns
- 1.5 Renaming a view "makes" it a table
- 1.6 Removing records from views fails
- 1.7 OOo shows unrelated databases/schemata
- 1.8 No way to set MySQL specific table attributes
- 1.9 Base does not recognize schema changes
- 1.10 Table column comments not synced between MySQL and Base
- 1.11 Default values not properly processed
- 1.12 BIGINT values crippled
- 1.13 fieldtype is changed after saving
- 1.14 Currency Value
- 2 Developer Observations
- 2.1 Template for new entries
- 2.2 getProcedureColumns is a stub
- 2.3 getImportedKeys()/getExportedKeys()
- 2.4 Definition of catalog and schema
- 2.5 XCloseable::close throws wrong exception
- 2.6 getWarnings() (C/C++)
- 2.7 getWarnings() (C/OOo)
- 2.8 Do we need any of those?
- 2.9 DatabseMetaData::getColumns() COLUMN_SIZE
- 2.10 DatabseMetaData::getColumns() DECIMAL_DIGITS
- 3 Potential Issues
End User Observations
This section collects issues as observed by end users
Template for new entries
reported by NAME on 2009-mm-dd - needs review
Description goes here.
On windows, the extension crashs on some systems
reported by FS on 2009-04-09
On some Windows systems, if you install and use the extension, it will crash OOo.
Reason: The extension ships an own version of
libmysql.dll. When Windows loads the driver library (
mysqlc.uno.dll), it also implicitly loads the
mysqlc.uno.dll is linked against. Unfortunately, Windows first searches in the folders specified by your
PATH environment variable. If it finds a
libmysql.dll in one of those folders, this one is used - no matter whether it is really compatible.
libmysql.dll, and link against this renamed version. Argh, not really.
- load libmysql.dll explicitly (using
LoadLibrary). In this case, we would have control over where the lib is taken from
- make libmysql a managed assembly, having a identifer, and a version, which changes with every incompatible API/ABI change (and differs between the debug and the release version). In this case, Windows would ignore all libmysql.dll versions found in the
PATHwhich do not proper version.
TODO (Andrey): fix
Copying tables may not work properly
reported by NAME on xx/01/2009 - needs review
Needs further and systematic testing!
CREATE TABLE `source` ( `id` int(11) NOT NULL, `col1` int(11) NOT NULL, `col2` char(1) default NULL, `col3` varchar(50) default NULL, PRIMARY KEY (`id`), KEY `idx_col1` (`col1`), KEY `idx_col2` (`col2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `source2` ( `id` int(10) NOT NULL, `col1` int(10) default NULL, `col2` char(3) default NULL, `col3` varchar(150) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
- Wrong column definitions (known issue)
- NOT NULL not properly handled
- Index information not copyied (same with JDBC, might be a Base issue)
TODO (Ulf): check what problems we actually have here, and how severe they are (compared with a connection vis ODBC/JDBC).
Wrong column width reported for text/binary columns
reported by Ulf on 09/01/2009 - needs review
We discussed this before and it might be fixed in the current source, but I work on an older snapshot. We return number of bytes, OO.org wants number of characters. A typical follow-up error is that copying tables may fail and/or give wrong results.
From an earlier mail exchange:
Hintergrund: für eine VARBINARY(10)-Spalte in MySQL liefern wir aktuell 40, für eine INTEGER-Spalte in MySQL liefern wir aktuell 11. Wir liefern momentan 40 für VARBINARY(10), weil das Feld maximal 10 Zeichen aufnehmen kann, die maximal 4 Bytes für das Encoding benötigen. Für ein VARCHAR(255) wird sogar VARCHAR(255) der Wert von 3060 geliefert, weil hier der MySQL Server nochmal in einem seiner Datenfelder einen Mul tiplikator von 3 einbaut (255 * 3 * 4 = 3060).
Später am Abend fiel es mir wieder ein... wir ziehen getColumnDisplaySize() intern im Connector/C++ ran für ConnectionMetaData::getColumns() -> COLUMN_SIZE. rs_data.push_back(my_i_to_a(buf, sizeof(buf)-1, (long) rs3_meta->getColumnDisplaySize(i))); // Column size
Pointer: MySQL ODBC file utitlity.c get_column_size
Renaming a view "makes" it a table
reported by Ulf on 09/01/2009
clu: works also wrong with native mysql driver -> works fine with odbc driver
Removing records from views fails
reported by Ulf on 08/01/2009
Removing records from SQL views neither works with the JDBC driver nor with the native driver. Base sends a misformed query like: "DELETE FROM `test`.`v` W"
Create a new Base database that connects to a MySQL schemata "test". Have "test" in the connection settings! OOo will ignore it and query MySQL for all tables in all schematas, and also display all those tables.
As a consequence the user will see all schemata he has access to not only the schemata/database "test" as requested in the connection settings
No way to set MySQL specific table attributes
The Base table editor does not give access to table attributes. Not even basic ones such as the Engine (MyISAM: non-transactional, InnoDB: transactional).
Base does not recognize schema changes
After connecting to a database and opening a table once, Base will not recognize changes applied to the DB schema meanwhile when opening the table in the table editor again.
that's not nice, but consistent with other DB/Drivers. For this purpose, there's View/Refresh Tables ...
Table column comments not synced between MySQL and Base
Base table column comments are not synchronized with the MySQL DB and its schema. Existing comments are not displayed in Base, and entering comments in the table editor is not propagated to MySQL.
Default values not properly processed
The Base table editor neither properly sets default values nor does it re-engineer default values properly.
Again, this is a known issue. The default value displayed in the UI is a so-called "control default", which is applied to controls used to enter data into the given field. The DB-side default for a column is a different property, API-wise, and currently not evaluated at all. Probably not even properly fetched by most existing drivers.
Changing this is possible, but probably requires UI changes. First, we would need to define how the control default and the DB default should interact in the UI. A possible scenario would be to drop the UI support for the control default, and always use the DB default (even in controls), as long as the driver supports providing/accepting DB defaults.
BIGINT values crippled
A possible solution would be to 'not employ the number formatter for certain column types. (Effectively, this means not using a
FormattedField for the respective table column in the data view, but a NumericField, which internally works with long integer values of arbitrary (?) length.) This would solve the BIGINT issue, though not the too-many-digits issue for fractional values.
fieldtype is changed after saving
reported by CLU on 12/01/2009
in table edit view: create a field from f.e. type bool & save → tinyint
Reason: maybe the same problem like 1.10 & 1.11
(Ulf) Duplicate and not an error in C/C++. There is no boolean type in MySQL. However, its handled in the JDBC driver case, the best we can do is emulate it in the same way.
(FS): Really, if there is no BOOL type, I tend to think the driver should not offer it. This way, users are not tempted to use it. Finally, this is a dedicated MySQL driver, it must not necessarily emulate features it does not really have.
(CLU): agree with FS .. every proposed type has to work, any unsupported type must not be shown
reported by Mechtilde - needs review
This note relate to 4.2. I can't input currency values. I get there 0.00.
This section collects observations interesting for developers only. It's likely the describe the root cause for another problem listed in the End User Observations section. In this sense, they might be duplicates, but we currently just use this as reminder list whenever we notice a problem ...
Template for new entries
reported by NAME on 2009-mm-dd - needs review
Description goes here.
getProcedureColumns is a stub
reported by Ulf on 2009-01-27
reported by Ulf on 2009-01-27 - needs review
Most likely Connector/C++ does not distinguish between "" and NULL parameter values for catalog and schema as requested by the JDBC specs. Do we need the differentiation?
Definition of catalog and schema
reported by Ulf on 2009-01-27
Connector/J and Connector/C++ use differen meanings for schema and catalog. See getCatalogTerm(). This may impact assorted methods with *catalog*() and *schema*() in their names and also their result sets. In the result sets the meaning of columns like TABLE_CAT and TABLE_SCHEM may differ with Connector/J and Connector/C++.
All C/OO.org methods need to be checked against this and implement whatever logic Base wants.
FS: Base doesn't really want a particular logic. If a database supports only schemas, that's fine, if it supports only catalogs, that's fine, if it supports both, that's fine, too. The only requirement is that the responses of the driver should be consistent. The fact that the JDBC driver exposes MySQL databases as catalogs is, finally, an implementation detail of the JDBC driver. As far as I know, the decision to mirror MySQL databases as schema instead of catalogs is younger than the current C/J.
I'd say we stay with C/OOo mapping databases to schemas, I seem to remember from talks with Georg that this is the preferred way nowadays.
XCloseable::close throws wrong exception
reported by Ulf on 2009-01-26 - needs review
The following sequence does not issue a SQLEception but a DisposedException as it seems:
[...] statement.close() statement.executeQuery()
It should throw SQLException, accoding to http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/XCloseable.html
FS: The DisposedException is the usual exception when you try to work with a, well, disposed object. The XCloseable documentation linked above only mentions that an SQLException is thrown in case of a "database access error", anyway. Also, I bet that in the sequence above, the exception is really thrown during executeQuery - which would be correct. In short: I don't see an issue here.
reported by Ulf on 2009-01-26
getWarnings() always returns a warning object, regardless if there is one or not. Should it return NULL? JDBC compliance test expects NULL.
reported by FS on 2009-01-29
Do we need any of those?
ERROR: SQL Exception ODatabaseMetaData::getCrossReference: feature not implemented. ERROR: SQL Exception ODatabaseMetaData::getProcedures: feature not implemented. ERROR: SQL Exception ODatabaseMetaData::getUDTs: feature not implemented. ERROR: SQL exception ODatabaseMetaData::supportsIntegrityEnhancementFacility: feature not implemented. ERROR: SQL Exception ODatabaseMetaData::supportsResultSetConcurrency: feature not implemented. ERROR: SQL Exception: OResultSet::getObject: feature not implemented. ERROR: SQL Exception ODatabaseMetaData::getExportedKeys: feature not implemented.
FS: supportsIntegrityEnhancementFacility is used to determine relationship support, but there's meanwhile an exception in the code which enables relationships for MySQL, regardless. (using this method is incorrect, speaking strictly, anyway.) supportsResultSetConcurrency is not used by default, but its usage can be enabled by the user on a per-database basis. So, it might be better to implement it.
getObject - not sure.
The other three are not used, I think.
DatabseMetaData::getColumns() takes COLUMN_SIZE from getDisplaySize().
Note: DisplaySize is not the same as ColumnSize. The former is a hint for UI processors which are to display the data in a tabular way. Currently, Base does not make use of this attribute. The ColumnSize's semantics is described in the Java API, and Base uses it in the very same semantics. As a consequence, COLUMN_SIZE is what needs to be tweaked.
DatabseMetaData::getColumns() does not set DECIMAL_DIGITS. Therefore, Base does not show it properly.
This section serves as collection of issues which might become a problem to end users, though we did not yet (bother to) create scenarios where users are hit by those (potential) problems.
TIME value range
TIME data type may have a significantly larger value range than the Base counterpart.
TINYINT / BOOLEAN
Educate Base users on MySQL Server type mappings, for example BOOL[EAN] -> TINYINT.