View source for Database/Drivers/MySQL Native/Known Problems

{{DISPLAYTITLE: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 [[Database/Drivers/MySQL_Native/Known_Problems/Fixed|available]].

== required for 1.0 release ==

=== On windows, the extension crashs on some systems ===

<span style="background-color:#F0EEC0; padding:3px">reported by FS on 2009-04-09</span>

On some Windows systems, if you install and use the extension, it will crash OOo.

Severity: <span style="background-color:#FF8080; padding:3px">required</span>

Reason: The extension ships an own version of <code>libmysql.dll</code>. When Windows loads the driver library (<code>mysqlc.uno.dll</code>), it also implicitly loads the <code>libmysql.dll</code>, which <code>mysqlc.uno.dll</code> is linked against. Unfortunately, Windows first searches in the folders specified by your <code>PATH</code> environment variable. If it finds a <code>libmysql.dll</code> in one of those folders, this one is used - no matter whether it is really compatible.

Suggested solutions:
* rename <code>libmysql.dll</code>, and link against this renamed version. Argh, not really.
* link <code>libmysql.dll</code> statically
* load libmysql.dll explicitly (using <code>LoadLibrary</code>). 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 <code>PATH</code> which do not proper version.

<span style="background-color:#FFC0C0">TODO (Andrey):</span> fix

=== Get the Mac version running ===

There's currently no working Mac version (though versions for Windows, Solaris Sparc, Solaris x86, Linux 32bit). Since Mac is one of the standard platforms of OOo, this needs to be fixed ...

Severity: <span style="background-color:#FF8080; padding:3px">required</span>

FS: Building the lib is no problem, and I also managed to employ the install_name_tool to fix the lib paths in the C/OOo and C/C++ libs, but somehow ... this things doesn't register so far ...

<span style="background-color:#FFC0C0">TODO (Frank):</span> Fix it

== Unclassified/TODO ==

=== Template for new entries ===

<span style="background-color:#F0EEC0; padding:3px">reported by NAME on 2009-mm-dd - needs review</span>

Description goes here.

Severity: <span style="background-color:#E0E0E0; padding:3px">undecided</span>

Reason: unknown

=== Copying tables may not work properly ===

<span style="background-color:#F0EEC0; padding:3px">reported by NAME on xx/01/2009 - needs review</span>

'''Needs further and systematic testing!'''

Original table:

  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

Copy:

  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)

Severity: <span style="background-color:#E0E0E0; padding:3px">undecided</span>

Reason: unknown

<span style="background-color:#FFC0C0">TODO (Ulf):</span> check what problems we actually have here, and how severe they are (compared with a connection vis ODBC/JDBC).

=== fieldtype is changed after saving ===

<span style="background-color:#F0EEC0; padding:3px">reported by [http://wiki.services.openoffice.org/wiki/User:Clu CLU] on 12/01/2009</span>

in table edit view: create a field from f.e. type bool & save → tinyint

Severity: <span style="background-color:#f0ee00; padding:3px">desired</span>

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

<span style="background-color:#FFC0C0">TODO (Ulf):</span> analyze reason, and suggest possible solution: "emulate" BOOL like the JDBC driver does (might yield other problems), or drop BOOL from the list of supported types

===Currency Value===

<span style="background-color:#F0EEC0; padding:3px">reported by Mechtilde - needs review</span>

This note relate to 4.2. I can't input currency values. I get there 0.00.

<span style="background-color:#FFC0C0">TODO (Chris):</span> check and confirm or deny

=== getImportedKeys()/getExportedKeys() ===

<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 2009-01-27 - needs review</span>

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?

Severity: <span style="background-color:#E0E0E0; padding:3px">undecided</span>

Reason: unknown

<span style="background-color:#FFC0C0">TODO (Ulf):</span> do we still/really have a problem here? Severity?

=== 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.

<span style="background-color:#FFC0C0">TODO (Frank):</span> confirm which ones are needed, actually

=== DatabseMetaData::getColumns() COLUMN_SIZE ===

DatabseMetaData::getColumns() takes COLUMN_SIZE from getDisplaySize().

Affected: DECIMAL/FLOAT/VARCHAR.

Severity: <span style="background-color:#FF8080; padding:3px">required</span>

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 [http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String) Java API], and Base uses it in the very same semantics.
As a consequence, COLUMN_SIZE is what needs to be tweaked.

<span style="background-color:#FFC0C0">TODO (Ulf):</span> check if still applicable

=== DatabseMetaData::getColumns() DECIMAL_DIGITS ===

DatabseMetaData::getColumns() does not set DECIMAL_DIGITS. Therefore, Base does not show it properly.

<span style="background-color:#FFC0C0">TODO (Ulf):</span> check if still applicable

== Post-1.0 ==

=== Renaming a view "makes" it a table ===

<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 09/01/2009</span>

Tested with the JDBC driver. Create a view and rename it in Base. Base changes the icon in the table list and shows the renamed object as a table :-)

clu: works also wrong with native mysql driver -> works fine with odbc driver

Severity: <span style="background-color:#C0C0FF; padding:3px">low</span>

Reason: unknown

=== Removing records from views fails ===

<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 08/01/2009</span>

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"

Severity: <span style="background-color:#C0C0FF; padding:3px">low</span>

Reason: Removing records from views is in general not possible in Base. The fact that "Edit/Delete record" is enabled for a view (as in any table which the user does not have the DELETE privilege for) is a bug, which I fixed in the CWS.

=== 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).

Severity: <span style="background-color:#c0ffc0; padding:3px">1.1</span>

=== 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.

Severity: <span style="background-color:#C0C0FF; padding:3px">low</span>

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.

Severity: <span style="background-color:#C0C0FF; padding:3px">later</span>

That's a known issue with all database types. The column description as displayed in Base is purely client-side, and stored within the .odb file only. There's also an issue for this, but I'm too lazy too search for it right now ...

=== Default values not properly processed ===

The Base table editor neither properly sets default values nor does it re-engineer default values properly.

Severity: <span style="background-color:#c0ffc0; padding:3px">1.1</span>

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 ===

Large BIGINT values are displayed in Base using scientific notation: 1e+15. If one changes the display format to number #.### the first 14 values of a large number (9223372036854775807) are displayed properly but then some rounding takes place, for example: 9223372036854800000. Connector/C++ can handle long long (L64) values properly and Base does use getLong() nevertheless the displayed value seems wrong.

Severity: <span style="background-color:#C0C0FF; padding:3px">later</span>

I bet that's because of the number formatter ... processing numbers for display is done using a office-wide number formatter component. Unfortunately, it works with double-precision values only, which imposes a precision loss for certain values. This applies to "too-large" values, as well as fractional values with "too many" digits. This is a general problem in Base, and not limited to the MySQL Native Driver.

A possible solution would be to '''not'' employ the number formatter for certain column types. (Effectively, this means ''not'' using a <code>FormattedField</code> 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.

=== getProcedureColumns is a stub ===

<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 2009-01-27</span>

http://pastebin.com/m75809

Severity: <span style="background-color:#C0C0FF; padding:3px">later</span>

Reason: unknown

=== getWarnings() (C/OOo) ===

<span style="background-color:#F0EEC0; padding:3px">reported by FS on 2009-01-29</span>

C/OOo's getWarnings is implemented as dummy only. It should at least retrieve the warnings from the underlying C/C++ connection, and concert them to css.sdbc.SQLWarnings as appropriate.

Severity: <span style="background-color:#C0C0FF; padding:3px">later</span>

== Potential Issues ==

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 ===

[http://dev.mysql.com/doc/refman/5.1/en/time.html|MySQL's TIME] data type may have a significantly larger value range than the Base counterpart.

Severity: <span style="background-color:#E0E0E0; padding:3px">undecided</span>

=== TINYINT / BOOLEAN ===

Educate Base users on [http://dev.mysql.com/doc/refman/5.1/en/other-vendor-data-types.html MySQL Server type mappings], for example BOOL[EAN] -> TINYINT.