Difference between revisions of "Database/Drivers/MySQL Native/Known Problems"

From Apache OpenOffice Wiki
Jump to: navigation, search
(Developer Observations)
(INSERT does not work)
Line 115: Line 115:
 
     KEY `idx_col1` (`col1`)
 
     KEY `idx_col1` (`col1`)
 
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 +
 +
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
 +
 +
=== information_schema (I_S) not shown in schema list ===
 +
 +
SHOW DATABASES shows the I_S. When quering the I_S for schema, the I_S will not expose itself. Different versions of Connector/C++ use different ways to fetch schema lists. Until and including Alpha SHOW DATABSES is used.
 +
 +
When using the JDBC driver and specifying no database in the connection URL no schemata are shown at all.
 +
 +
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.
 +
 +
=== DatabseMetaData::getColumns() COLUMN_SIZE ===
 +
 +
DatabseMetaData::getColumns() takes COLUMN_SIZE from getDisplaySize().
 +
 +
Affected: DECIMAL/FLOAT.
 +
 +
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.
 +
 +
=== DatabseMetaData::getColumns() DECIMAL_DIGITS ===
 +
 +
DatabseMetaData::getColumns() does not set DECIMAL_DIGITS. Therefore, Base does not show it properly.
  
 
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
 
Severity: <span style="background-color:#FF8080; padding:3px">required</span>

Revision as of 08:39, 8 January 2009

This page lists the currently known problems with the MySQL Native Driver, aka MySQL Connector/OOo.

End User Observations

This section collects issues as observed by end users

Empty schemata not shown

Create an empty schemata/database. Create a new Base database which connects to an empty MySQL database. The MySQL database is not shown.

Severity: desired

Reason: OOo calls getTables() - no tables - schema not displayed. Setting might even be cached - verify that database becomes visible after creating table (from mysql prompt) in schema.

DECIMAL shown as TEXT

Base shows numerical DECIMAL columns as TEXT (in the table editor?).

Severity: required

I'm 95% sure C/C++ and C/OOo report a proper column type.

SMALLINT shown as REAL

When loading a table with a SMALLINT column it is shown as a FLOAT/REAL column in Base (in the table editor?)

Severity: required

OOo shows unrelated databases/schemata

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

Severity: desired

No default schema on table creation

Base does not preselect a default schema in the table editor dialog. Its does not even if you connect to a certain schema by specifying it in the connection settings.

Severity: desired

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: 1.1

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: undecided

Is this really an issue? There's View/Refresh Tables, if needed.

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: later

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

Violation of FK constraint give two errors instead of one

Create two tables. Have one table "derived" reference entries in the other table "source". Try to remove a referenced record from the table "source". MySQL will report an error stating that you are violating a FK constrain. When using the MySQL JDBC driver the Base shows one requester with stating something like "Error - (error message from MySQL)". When using the native driver you get an requester stating "error" and you can proceed to an error details dialog. In the details dialog you will find two errors. The first has no message, the second shows the error message provided by MySQL.

Severity: undecided

The empty error message is ugly, but not the only one within Base, so that's nothing which will block an 1.0 release ...

Default values not properly processed

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

Severity: 1.1

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: undecided

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

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 of those (potential) problems.

TIME value range

TIME data type may have a significantly larger value range than the Base counterpart.

Severity: undecided

Developer Observations

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

INSERT does not work

Inserting data into a simple table does not work. Works fine when using the JDBC driver.

 CREATE TABLE `source` (
   `id` int(11) NOT NULL,
   `col1` int(11) NOT NULL,
   PRIMARY KEY  (`id`),
   KEY `idx_col1` (`col1`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Severity: required

information_schema (I_S) not shown in schema list

SHOW DATABASES shows the I_S. When quering the I_S for schema, the I_S will not expose itself. Different versions of Connector/C++ use different ways to fetch schema lists. Until and including Alpha SHOW DATABSES is used.

When using the JDBC driver and specifying no database in the connection URL no schemata are shown at all.

Severity: undecided

TINYINT / BOOLEAN

Educate Base users on MySQL Server type mappings, for example BOOL[EAN] -> TINYINT.

DatabseMetaData::getColumns() COLUMN_SIZE

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

Affected: DECIMAL/FLOAT.

Severity: required

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() DECIMAL_DIGITS

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

Severity: required

Personal tools