MySQL Native Driver: Fixed Problems

From Apache OpenOffice Wiki
Jump to: navigation, search


DatabseMetaData::getColumns() DECIMAL_DIGITS

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

Ulf: can not reproduce with 1.0 alpha

DatabseMetaData::getColumns() COLUMN_SIZE

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

Affected: DECIMAL/FLOAT/VARCHAR.

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.

Ulf: works with 1.0 alpha

Tools -> Relations... not working

FIXED: Error message: Database not support relations. ERROR: Still not possible to create new 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

create any table with pk → not possible to insert data into table

Severity: fixed in current version

Investigation: The driver currently returns an empty result set when being asked for the table privileges: getTablePrivileges( null, "schema", "table" ). Debugging deeper seems to indicate that parsing the result of a "SHOW GRANTS" query is flawed.

(CLU): please fix this issue preferred, because this is a potential 'showstopper' for even an alpha release and also hinder further testing.

(Mechtilde) After testing the alpha version I found this problem only with currency values. It stays as 0.00. In my test all other values work

Tools->SQL does not show error message

reported by Ulf on 08/01/2009

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

New tables shown after "refresh tables" but (delete) rights/grants wrong?

reported by Ulf on 08/01/2009

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.

Investigation: This is a general problem currently, DatabaseMetaData::getTablePrivileges returns wrong data (all the time? sometimes? andrey claimed to have fixed this, but the fix does not work for me). If I fake the getTablePrivileges implementation to return the full set of privileges regardless the table being asked for, then the problem does not happen.

DECIMAL shown as TEXT

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

Severity: fixed in current version

Investigation: column is reported as MYSQL_TYPE_NEWDECIMAL, which wasn't covered in C/OOo (which only had a case for MYSQL_TYPE_DECIMAL)

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.

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

change pk fails

reported by CLU on 12/01/2009

open table with pk and change pk to any other field -> error: duplicate entry for key

Severity: fixed in current version

INVALID :) When you add a new column to a table, then you need to fill it with unique values, before declaring it as primary key. When you do so, changing the PK to this column works perfectly.

(CLU): no new column is added, just the pk is changed in table edit view (in this case the field is empty) .. sure the content must be unique, else i would await an error message ;)

clu: .. and error message comes ;)

copy table & paste special fails

reported by CLU on 12/01/2009

copy table & paste special (change tablename) with pk ? invalid error message without text appears

Severity: fixed in current version

Cannot reproduce in latest builds. What happens, though, is that e.g. VARCHAR columns get a wrong length (tripled). This is the well-known problem of wrong column lengths being reported by the driver.

(CLU): cannot check at the moment .. but i do not understand, what an invalid error message has to do with wrong lenght of varchar columns? do you think it could have caused this message?

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

FS: works as expected now. As Ulf and /me agreed, C/OOo will not expose the I_S at all.

create table with wizard fails

reported by CLU on 12/01/2009

run table wizard: take default & all field & push finish ? java assertion list appears

Severity: fixed in current version

Reason: The wizard tries to create a table in the INFORMATION_SCHEMA schema. Bad idea. However, how would a better idea look like? There's no such thing as a "default schema" which could be used, so the wizard picks the first schema which it encounters, which happens to be INFORMATION_SCHEMA.

Note that the problem does not happen when connecting via the JDBC driver, since here a) schemas are in fact reported as catalogs, and there is such a thing as a default/current catalog (namely Connection.getCatalog), which is used and respected by the wizard.

FS: Fixed. Since the Connector/OOo does not expose the INFORMATION_SCHEMA anymore, the wizard cannot pick it ...

Renaming a view not possible

reported by Ulf on 09/01/2009

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: fixed in current version

Reason: missing SDBCX support of the driver. In general, tables can only be renamed when there is a css.sdbcx.Table object representing a single table, which supports the XRename interface.

FS: fixed with the usage of the SDBCX wrapper, which is also employed for MySQL via ODBC/JDBC.

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.

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

change table fieldtype in edit view not possible

reported by CLU on 12/01/2009

open table in edit mode -> fieldtypes greyed out (not possible to change)

Severity: fixed in current version


Reason: The driver does not support XAlterTable (and more SDBCX-level interfaces)

(CLU): as the driver should work as good as jdbc/odbc driver or better, we have to think about a solution in future versions (how about deleting and creating new?)

Tools -> User Administration does not work

When you choose Tools/User administration..., you're presented with an error.

Severity: fixed in current version

FS: fixed with the usage of the SDBCX wrapper driver. Note that for undprivileged users (which cannot select from the users table), there's still the error message that user administration is not supported. This is actually wrong, but the error of the missing privileges is not propagated to the user here.

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

FS: fixed meanwhile. Was most probably a duplicate of the other "can't insert data" issue

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

FS: 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?

FS: fixed meanwhile. Probably was a duplicate of another issue, which was caused by wrong translations from C/C++'s sql::SQLException to C/OOo's css.sdbc.SQLException.

severe character set problems

Tables columns containing non-ASCII characters in their name are improperly named in OOo.

Severity: required

Reason: C/OOo is flooded with OUString::createFromAscii usage, not only for column names. It seems that C/Cpp has a 8-bit api, and conversion from/to those API often happens assuming an ASCII encoding, sometimes using an encoding set at the connection (where I currently do not know where it originates from).

I'd say we have a serious problem here.

FS: fixed. Replaced all occurrences of OUString::createFromAscii, which was applied to a C/Cpp-provided string, with proper decoding assuming UTF-8.

Binary columns are reported as text

Columns of type "LONG (VAR)BINARY" are reported as "LONG (VAR)CHAR" in the table designer

Severity: required

Reason: MySQL-internally, binary columns re-use existing types, e.g. MYSQL_TYPE_VAR_STRING for a VARBINARY column. The fact that the column is not text, but binary, is recorded in an additional per-column flag, where the BINARY_FLAG bit is set. Unfortunately, this information is not transported via C/Cpp. Instead, the DATA_TYPE column of DatabaseMetaData.getColumns returns the untouched MYSQL_TYPE_, which alone is not sufficient to determine the proper column type.

Suggested solution: Either, DatabaseMetaData.getColumns must return additional information, e.g. an additional result set column, taking the column flags. Alternatively, the content of the DATA_TYPE column returned in getColumns must be "normalized", so that VARBINARY and VARCHAR in fact have different data types.

I'd somehow prefer the latter, as this is more future-proof. Also, since C/Cpp claims to have a JDBC-compliant API, it would be the better solution: In JDBC, DATA_TYPE is defined to contain a value from the java.sql.Types class, which already is a normalization we need ...

FS: fixed in a recent build, where Andrey's normalization of data types landed

Perfoemance problem when accessing big tables

Open a table with more then 1000 rows. Jump to last one. => 2 min.

Severity: fixed in current version

FS: Fixed in a recent build, where Andrey's performance fixes to C/C++'s result sets landed. Now the performance is on the same level as with the JDBC driver, further performance improvements will happen on the Base side, independent from the MySQL native driver.

Creating view not possible

create any view in design mode & save -> error message: 'CREATE VIEW command denied to user ..'

Severity: desired

FS: cannot reproduce in current build (as of 2009-04-30)

OPreparedStatement::getMetaData is not implemented

OPreparedStatement::getMetaData is not implemented currently. Per API spec, it is expected to retrieve the meta data of the result set which the prepared statement would, well, result in, when executed. There are places in the API (SingleSelectQueryComposer, for instance), which make use of this functionality, and might fail.

Severity: desired

Exception not converted

reported by Ulf on 2009-01-27

sql.SQLException not handled in C/OO.org and not converted into SQLException.

.MSG: Calling DatabaseMetaData.getImportedKeys
ERROR: Unexpected Exception exception type not found: sql.SQLException

Same for getIndex().

Severity: required

Reason: C/OO.org bug

FS: Raised the severity to "required", since on Unix platforms, throwing an exception which is not part of the method's exception specification will abort the process.

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

FS: classified as feature by now ...

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.

Severity: undecided

Reason: unknown

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.

FS: as agreed with Ulf, the current situation, where MySQL DBs are mapped to schemas, is perfectly okay.

getWarnings() (C/C++)

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.

Severity: undecided

Reason: Connector/OO.org

FS: fixed in current version (C/C++ GA), according to Ulf

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

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

Editing values in DECIMAL columns fails

reported by Mechtilde

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

(Chris): i can confirm similar behavior:

1. create a table with a decimal field with two decimal places (& pk)
2. open the table and insert some values (f.e. currency)
-> after leaving the row the input is changed to '0'
(the same with every text or decimal field) => no senseful inserting is possible

(Ulf): this is about DECIMAL not Currency. Works with DOUBLE.

Severity: required

BOOLEAN -> TINYINT conversion

reported by CLU on 12/01/2009

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

Severity: desired

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

(Ulf): Not an error in C/C++. There is no boolean type in MySQL, see http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html . MySQL accepts the keywords BOOLEAN, TRUE and FALSE. That is all. MySQL converts BOOLEAN into TINYINT(1). TRUE and FALSE are aliases for 1 and 0, respectively:

mysql> create table bool_test(col boolean default true);
Query OK, 0 rows affected (1.41 sec)
mysql> show warnings;
Empty set (0.03 sec)
mysql> show errors;
Empty set (0.00 sec)
mysql> show create table bool_test;
+-----------+--------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                     |
+-----------+--------------------------------------------------------------------------------------------------+
| bool_test | CREATE TABLE `bool_test` (
  `col` tinyint(1) DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+--------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)


(Ulf): Connector/C++ returns what you get when you create a BOOLEAN column. You get a TINYINT(1) in your database. Connector/C++ returns what you see when you check the schema definition of a table with a TINYINT(1) column: type tinyint. Connector/J seems to define TINYINT(1) == BOOLEAN. That means, Connector/J cannot distinguish between a numeric column TINYINT(1) and a TINYINT(1) column that is intended to be a BOOLEAN. If a user creates a TINYINT(1) column with Connector/J, Connector/J will report BOOLEAN. There is no solution for handling the MySQL Server data type alias BOOLEAN (= converted implicitly into TINYINT(1)). The only thing you can do it is either to disallow the usage of BOOLEAN or TINYINT(1) in Base (= in Connector/OO.org) to solve the lack of clarity.

Frank: Okay, I'll move that item to the "fixed" section, to preserve the explanations, and create a new (shorter) item requesting (for post-1.0) to solve this.

getImportedKeys()/getExportedKeys()

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?

Reason: unknown

FS: Ah ... I always thought this refers to the concrete implementation of C/C++'s getImported/ExportedKeys method. Instead, it in fact is a shortcoming of the DatabaseMetaData interface definition, which simply does not allow to distinguish between "null" and "empty string", since the arguments passed to getImported/ExportedKeys are actually std::string instances.

So, for OOo, this is not a problem: MySQL does not know tables which do not belong a schema (aka database), so the MySQL_ConnectionMetaData implementation is of course free to interpret the empty string as "do not narrow the search" (something which in JDBC is expressed by "null", not by the "empty string").

Given that the abstract API implemented by C/C++ should, on the long run, also be used for other databases, this might become a problem in the future.

Personal tools