Difference between revisions of "Database/Drivers/MySQL Native/Known Problems"
Line 283: | Line 283: | ||
Reason: maybe the same problem like 1.10 & 1.11 | Reason: maybe the same problem like 1.10 & 1.11 | ||
+ | |||
+ | (Ulf) Duplicate. 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. | ||
=== copy table & paste special fails === | === copy table & paste special fails === |
Revision as of 17:39, 12 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
Template for new entries
reported by NAME on xx/01/2009 - needs review
Description goes here.
Severity: unclassified
Reason: unknown
Copying tables may not work properly
reported by NAME on xx/01/2009 - needs review
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: unclassified
Reason: unknown
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).
And:
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
Severity: unclassified
Reason: unknown
Renaming a view not possible
reported by Ulf on 09/01/2009 - needs review
Tested with the native driver: Its not possible to rename a view, the UI does not offer the context menu item when selecting the view in the table list.
Severity: unclassified
Reason: unknown
Renaming a view "makes" it a table
reported by Ulf on 09/01/2009 - needs review
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 :-)
Severity: low
Reason: unknown
Removing records from views fails
reported by Ulf on 08/01/2009 - needs review
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: unclassified
Reason: unknown
Tools->SQL does not show error message
reported by Ulf on 08/01/2009 - needs review
Open the SQL execution wizard by invoking Tools->SQL. Run an invalid SQL query, for example, "create table foo". Base prints a new line "n:" in the status part of the window but it shows no error message.
Severity: unclassified
Reason: unknown
New tables shown after "refresh tables" but (delete) rights/grants wrong?
reported by Ulf on 08/01/2009 - needs review
Create some tables in MySQL. Start Base and browse the table list. Create a new table in MySQL (not in Base!), make sure it has a PK, add some records. Select View->Refresh tables. Base will show the new table. Try to remove a row from the newly created table. Base rejects the delete operation stating that the user as insufficient rights.
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
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 gives 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.
This is a common pattern. Cause any SQL error and you can see up to three errors when you quit the initial requester using the button "more". Quite annoying, for example, when writing SQL statements for a Base "Query" object.
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 ... (Ulf) But why does it work fine with the JDBC driver?
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.
Subforms do not work
Well, some of them do ... more precisely, SQL statements with named parameters (":param_name") do not work in general. The ODBC/JDBC driver wrapper used to replace them with unnamed parameters ("?"), since MySQL does not understand them, but the native driver does not do this, yet. Since subforms usually make heavy use of such named parameters, they currently fail.
Severity: required
Fixing this should be easy, at least so it is as usable as with other DBs/drivers, in particular with the existing MySQL-(O/J)DBC bridge. Before sending statements to the server, the driver must replace (unless the statement's EscapeProcessing
is FALSE
, of course) named parameters with anonymous ones. The disadvantage is that this only works when Base itself can parse the statement, and that it potentially changes the statement. However, both statements are true for all other DBs which do not support named parameters, too.
Tools -> User Administration does not work
When you choose Tools/User administration...
, you're presented with an error.
Severity: required (remove the menu item)
Severity: required (implement proper user administration)
Tools -> Relations... not working
Error message: Database not support relations.
We should hint MySQL users in the driver documentation that Base takes no(?) measures to clean up no longer needed indexes. MySQL's InnoDB needs some special treatment.
Severity: required
With 3.x, Base is able to administrate MySQL's relationships (via the ODBC/JDBC bridge), so the native driver should be able to do this, too. At least with approximately the same set of bugs as the "old" driver :)
insert data in table not possible
reported by CLU on 12/01/2009 - needs review
create any table with pk → not possible to insert data into table
Severity: required
Reason: pk not detected?
create table with wizard fails
reported by CLU on 12/01/2009 - needs review
run table wizard: take default & all field & push finish → java assertion list appears
Severity: required
Reason: unknown
change pk fails
reported by CLU on 12/01/2009 - needs review
open table with pk and change pk to any other field -> error: duplicate entry for key
Severity: required
Reason: pk not detected?
change table fildtype in edit view not possible
reported by CLU on 12/01/2009 - needs review
open table in edit mode -> fildtypes greyed out (not possible to change)
Severity: required
Reason: unknown
fildtype is changed after saving
reported by CLU on 12/01/2009 - needs review
in table edit view: create a field from f.e. type bool & save → tinyint
Severity: required
Reason: maybe the same problem like 1.10 & 1.11
(Ulf) Duplicate. 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.
copy table & paste special fails
reported by CLU on 12/01/2009 - needs review
copy table & paste special (change tablename) with pk → invalid error message without text appears
Severity: required
Reason: unknown
query wizard shows 'query' as 'table'
reported by CLU on 12/01/2009 - needs review
create any query, open query wizard → tables listbox propose query (also with the other wizards)
Severity: required
Reason: unknown
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
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
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
TIME data type may have a significantly larger value range than the Base counterpart.
Severity: undecided
TINYINT / BOOLEAN
Educate Base users on MySQL Server type mappings, for example BOOL[EAN] -> TINYINT.