Sun MySQL Connector/OOo: Known Problems

From Apache OpenOffice Wiki
Jump to: navigation, search


This page lists the currently known problems with the MySQL Native Driver, aka MySQL Connector/OOo. A list of fixed problems is also available.

required for 1.0 release

On windows, the extension crashs on some systems

reported by FS on 2009-04-09

detailed here

Severity: required

Suggested solutions:

  • rename libmysql.dll, and link against this renamed version. Argh, not really.
  • link libmysql.dll statically
  • 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 PATH which do not proper version.

TODO (Andrey): fix

Linux version does not install on systems without libstdc++.so.5

detailed here

Severity: required

Unclassified/TODO

Template for new entries

reported by NAME on 2009-mm-dd - needs review

Description goes here.

Severity: undecided

Reason: unknown

Copying tables does 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: undecided

Reason: unknown

TODO (Ulf): check what problems we actually have here, and how severe they are (compared with a connection vis ODBC/JDBC).

(Ulf) Create the following table on the MySQL prompt and clone it using Base. Compare the table that Base creates with the original one.

CREATE TABLE `all_types` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `col1` bit(1) DEFAULT NULL,
 `col2` bit(1) NOT NULL,
 `col3` bit(5) NOT NULL,
 `col4` bit(8) DEFAULT NULL,
 `col5` tinyint(4) DEFAULT NULL,
 `col6` tinyint(4) NOT NULL,
 `col7` tinyint(4) NOT NULL DEFAULT '-1',
 `col8` tinyint(1) DEFAULT NULL,
 `col9` tinyint(3) unsigned DEFAULT NULL,
 `col10` tinyint(3) unsigned zerofill DEFAULT NULL,
 `col11` tinyint(1) DEFAULT NULL,
 `col12` tinyint(1) NOT NULL,
 `col13` tinyint(1) DEFAULT '0',
 `col14` smallint(6) DEFAULT NULL,
 `col15` smallint(6) NOT NULL,
 `col16` smallint(6) NOT NULL DEFAULT '-1',
 `col17` smallint(3) DEFAULT NULL,
 `col18` smallint(5) unsigned DEFAULT NULL,
 `col19` smallint(5) unsigned zerofill DEFAULT NULL,
 `col20` smallint(5) unsigned zerofill DEFAULT '00101',
 `col21` mediumint(9) DEFAULT NULL,
 `col22` mediumint(9) NOT NULL,
 `col23` mediumint(1) DEFAULT NULL,
 `col24` mediumint(2) DEFAULT '12',
 `col25` mediumint(8) unsigned DEFAULT NULL,
 `col26` mediumint(8) unsigned zerofill DEFAULT NULL,
 `col27` int(11) DEFAULT NULL,
 `col28` int(11) NOT NULL,
 `col29` int(1) DEFAULT NULL,
 `col30` int(10) unsigned DEFAULT NULL,
 `col31` int(4) unsigned zerofill DEFAULT NULL,
 `col32` int(4) DEFAULT '-123',
 `col33` bigint(20) DEFAULT NULL,
 `col34` bigint(20) NOT NULL,
 `col35` bigint(20) unsigned DEFAULT NULL,
 `col36` bigint(20) unsigned DEFAULT NULL,
 `col37` bigint(4) unsigned zerofill DEFAULT '10101',
 `col38` float DEFAULT NULL,
 `col39` float NOT NULL,
 `col40` float unsigned DEFAULT NULL,
 `col41` float(5,3) unsigned zerofill DEFAULT NULL,
 `col42` float DEFAULT NULL,
 `col43` float DEFAULT '1.01',
 `col44` double DEFAULT NULL,
 `col45` double NOT NULL,
 `col46` double unsigned DEFAULT NULL,
 `col47` double unsigned DEFAULT '1.123',
 `col48` double(5,3) unsigned zerofill DEFAULT NULL,
 `col49` decimal(10,0) DEFAULT NULL,
 `col50` decimal(10,0) NOT NULL,
 `col51` decimal(10,0) unsigned DEFAULT NULL,
 `col52` decimal(5,3) unsigned zerofill DEFAULT NULL,
 `col53` decimal(6,3) unsigned zerofill DEFAULT '034.560',
 `col54` date DEFAULT NULL,
 `col55` date NOT NULL,
 `col56` date NOT NULL DEFAULT '2009-02-16',
 `col57` datetime DEFAULT NULL,
 `col58` datetime NOT NULL,
 `col59` datetime NOT NULL DEFAULT '2009-02-12 21:36:54',
 `col60` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `col61` time DEFAULT NULL,
 `col62` time NOT NULL,
 `col63` time DEFAULT '12:39:41',
 `col64` year(4) DEFAULT NULL,
 `col65` year(4) NOT NULL,
 `col66` year(4) DEFAULT NULL,
 `col67` year(2) DEFAULT NULL,
 `col68` year(4) DEFAULT '2009',
 `col69` char(1) DEFAULT NULL,
 `col70` char(1) NOT NULL,
 `col71` char(255) DEFAULT NULL,
 `col72` binary(255) DEFAULT NULL,
 `col73` char(254) NOT NULL,
 `col74` char(254) NOT NULL DEFAULT 'abc',
 `col75` char(255) CHARACTER SET utf8 DEFAULT NULL,
 `col76` char(215) CHARACTER SET utf8 NOT NULL,
 `col77` char(215) CHARACTER SET utf8 NOT NULL DEFAULT 'Ulf',
 `col78` char(255) CHARACTER SET utf8 DEFAULT NULL,
 `col79` char(55) CHARACTER SET utf8 NOT NULL,
 `col80` char(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `col81` char(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 'Wendel',
 `col82` char(43) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `col83` char(123) CHARACTER SET ucs2 DEFAULT NULL,
 `col84` binary(255) DEFAULT NULL,
 `col85` binary(12) NOT NULL,
 `col86` char(14) DEFAULT 'Andrey',
 `col87` binary(25) DEFAULT NULL,
 `col88` varchar(10) DEFAULT NULL,
 `col89` varbinary(10) DEFAULT NULL,
 `col90` varchar(7) NOT NULL,
 `col91` varchar(255) DEFAULT 'Good night twitter. BTW, go MySQL!',
 `col92` varchar(11) CHARACTER SET utf8 DEFAULT NULL,
 `col93` varchar(11) CHARACTER SET ascii DEFAULT 'Hristov',
 `col94` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `col95` varbinary(13) DEFAULT NULL,
 `col96` varbinary(14) NOT NULL,
 `col97` binary(1) DEFAULT NULL,
 `col98` varbinary(1) DEFAULT NULL,
 `col99` varbinary(2) NOT NULL,
 `col100` varbinary(20) NOT NULL DEFAULT 'Lawrin',
 `col101` tinyblob,
 `col102` tinytext,
 `col103` tinytext NOT NULL,
 `col104` tinytext,
 `col105` tinyblob,
 `col106` tinytext CHARACTER SET utf8,
 `col107` tinytext CHARACTER SET utf8 COLLATE utf8_bin,
 `col108` mediumblob,
 `col109` mediumblob NOT NULL,
 `col110` mediumtext,
 `col111` mediumblob,
 `col112` mediumtext NOT NULL,
 `col113` mediumtext CHARACTER SET utf8,
 `col114` mediumtext CHARACTER SET utf8 COLLATE utf8_bin,
 `col115` longblob,
 `col116` longblob NOT NULL,
 `col117` longtext,
 `col118` longblob,
 `col119` longtext NOT NULL,
 `col120` longtext CHARACTER SET utf8,
 `col121` longtext CHARACTER SET utf8 COLLATE utf8_bin,
 `col122` enum('yes','no') DEFAULT NULL,
 `col123` enum('yes','no') CHARACTER SET binary DEFAULT NULL,
 `col124` enum('yes','no') NOT NULL,
 `col125` enum('yes','no','not sure') NOT NULL,
 `col126` enum('yes','no','buy') NOT NULL DEFAULT 'buy',
 `col127` set('yes','no') DEFAULT NULL,
 `col128` set('yes','no') CHARACTER SET binary DEFAULT NULL,
 `col129` set('yes','no') CHARACTER SET ascii DEFAULT NULL,
 `col130` set('yes','no') CHARACTER SET ascii DEFAULT 'yes',
 `col131` set('yes','no','ascii') CHARACTER SET ascii NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
INSERT INTO all_types (id  , col1 , col2 , col3 , col4 , col5 , col6 , col7 , col8 , col9 , col10 , col11 , col12 , col13 , col14 , col15 ,  col16 , col17 , col18 , col19 , col20 , col21 , col22 , col23 , col24 , col25 , col26 , col27 , col28 , col29 , col30 , col31 , col32 , col33 , col34 , col35 , col36 , col37 , col38 , col39 , col40 , col41 , col42 , col43 , col44 , col45 , col46 , col47 , col48 , col49 , col50 , col51 , col52 , col53 , col54 , col55 , col56 , col57 , col58 , col59 , col60 , col61 , col62 , col63 , col64 , col65 , col66 , col67 , col68 , col69 , col70 , col71 , col72 , col73 , col74 , col75 , col76 , col77 , col78 , col79 , col80 , col81 , col82 , col83 , col84 , col85 , col86 , col87 , col88 , col89 , col90 , col91 , col92 , col93 , col94 , col95 , col96 , col97 , col98 , col99 , col100 , col101 , col102 , col103 , col104 , col105 , col106 , col107 , col108 , col109 , col110 , col111 , col112 , col113 , col114 , col115 , col116 , col117 , col118 , col119 , col120 , col121 , col122 , col123 , col124 , col125 , col126 , col127 , col128 , col129 , col130 , col131) VALUES ('1' , '0' , '1' , '0' , '0' , '127' , '127' , '12' , '3' , '255' , '1' , '1' , '2' , '3' , '-32768' , '32767' , '-32768' , '-32768' , '65535' , '123' , '123' , '-8388608' , '-8388608' , '2' , '2' , '16777215' , '1677721' , '2147483647' , '2147483647' , '3' , '4294967295' , '1' , '1' , '-9223372036854775808' , '-9223372036854775808' , '18446744073709551615' , '18446744073709551615' , '2' , '-1.01' , '-1.01' , '1.01' , '1.01' , '1.01' , '1.01' , '-1.01' , '-1.01' , '1.01' , '1.01' , '1.01' , '-1.01' , '-1.01' , '1.01' , '1.01' , '1.01' , '2009-02-09' , '2009-02-12' , '2009-02-12' , '2009-02-09 20:05:43' , '2009-02-12 17:49:21' , '2009-02-12 17:49:21' , '2038-01-09 03:14:07' , '-838:59:59' , '838:59:59' , '-838:59:59' , '1901' , '1902' , '2009' , '1' , '1' , 'a' , 'a' , 'abc' , 'abc' , 'abc' , 'abc' , 'abc' , 'abc' , 'abc' , 'abc' , 'abc' , 'abc' , 'abc' , 'abc' , 'abc' , 'abc' , 'abc' , 'abc' , 'abc' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'a' , 'yes' , 'yes' , 'yes' , 'yes' , 'yes' , 'yes' , 'yes' , 'yes' , 'yes' , 'yes')


(Ulf) Issue summary:

  • Precision changes can usually be ignored, because most of the time it is a "display width" for MySQL, not a column value range limitation, see http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html . The "precision" seems correct whenever it has to be.
    • JDBC: same
  • BIT [original] to VARCHAR [copy]: wrong data
    • JDBC: no issue
  • MEDIUMINT to VARCHAR conversion: wrong data, likely poor performance for users
    • JDBC: no issue
  • TIMESTAMP to DATETIME conversion: safe with regards to data, but TIMESTAMP columns have a special semantics in MySQL - "By default, the first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation [...]"
    • JDBC: same issue
  • YEAR to VARCHAR conversion: safe with regards to the data, but value range is wider and YEAR(2) DEFAULT 70 means default 1970... glory details
    • JDBC: YEAR to DATE
  • TINYTEXT to VARCHAR(255): should be safe
    • JDBC: same conversion
  • TINYBLOB to VARBINARY(255): should be safe
    • JDBC: TINYBLOB to BINARY(255) - identical
  • BITINT(20) to BIGINT(19): should be safe
    • JDBC: same conversion
  • ENUM(x) to ENUM - wrong SQL, enumeration list missing, very likely the same with SET
    • JDBC: not tested
  • FLOAT/DOUBLE: wrong data, fraction lost
    • JDBC: worse - no data copied for any column!
  • All types: DEFAULT values lost, if the default value is not NULL.
    • JDBC: same issue
  • All string types: CHARACTER SET lost
    • JDBC: same issue (because not supported by Base)
  • All string types: COLLATION lost
    • JDBC: same issue (because not supported by Base)
  • Most/All numeric types: UNSIGNED not copied - usually combined with wrong data for min/max, kind of overflow?
    • JDBC: wrong SQL, columns cannot be copied at all
  • Most/All numeric types: ZEROFILL not copied - should be ignored, MySQL specific
    • JDBC: same


(Ulf) Detailed results:

o OK
x some kind of trouble 
o  `id` int(10) NOT NULL AUTO_INCREMENT,
--> type changes BIT -> VARCHAR()
--> col1 - col3: wrong data - <something> -> 0
x  `col1` varchar(1) DEFAULT NULL,
x  `col2` varchar(1) NOT NULL,
x  `col3` varchar(5) NOT NULL,
x  `col4` varchar(8) DEFAULT NULL,
--> Precision wrong TINYINT(n) -> TINYINT(3), always
--> DEFAULT values lost
--> UNSIGNED lost
--> ZEROFILL lost
x  `col5` tinyint(3) DEFAULT NULL,
x  `col6` tinyint(3) NOT NULL,
x  `col7` tinyint(3) NOT NULL,
x  `col8` tinyint(3) DEFAULT NULL,
--> col9 - wrong data: 255 -> -1 ([UN]SIGNED overflow?)
x  `col9` tinyint(3) DEFAULT NULL,
x  `col10` tinyint(3) DEFAULT NULL,
x  `col11` tinyint(3) DEFAULT NULL,
x  `col12` tinyint(3) NOT NULL,
x  `col13` tinyint(3) DEFAULT NULL,
--> Precision wrong SMALLINT(n) -> SMALLINT(5), always
--> DEFAULT values lost
--> UNSIGNED lost
--> ZEROFILL lost
x  `col14` smallint(5) DEFAULT NULL,
x  `col15` smallint(5) NOT NULL,
x  `col16` smallint(5) NOT NULL,
x  `col17` smallint(5) DEFAULT NULL,
--> col18 - wrong data: 65535 -> -1 ([UN]SIGNED overflow?)
x  `col18` smallint(5) DEFAULT NULL,
x  `col19` smallint(5) DEFAULT NULL,
x  `col20` smallint(5) DEFAULT NULL,
--> Type wrong: MEDIUMINT -> VARCHAR, always
--> Precision wrong: MEDIUMINT(n) -> VARCHAR(7), always
--> DEFAULT values lost
--> UNSIGNED lost
--> ZEROFILL lost
x  `col21` varchar(7) DEFAULT NULL,
x  `col22` varchar(7) NOT NULL,
x `col23` varchar(7) DEFAULT NULL,
x  `col24` varchar(7) DEFAULT NULL,
--> col25 - wrong data -> 16777215 -> 1677721
x  `col25` varchar(7) DEFAULT NULL,
x  `col26` varchar(7) DEFAULT NULL,
--> Precision wrong: INT(n) -> INT(10), always
--> DEFAULT values lost
--> UNSIGNED lost
--> ZEROFILL lost
x  `col27` int(10) DEFAULT NULL,
x  `col28` int(10) NOT NULL,
x  `col29` int(10) DEFAULT NULL,
--> col30 - wrong data - 4294967295 -> -1 ([UN]SIGNED overflow?)
x  `col30` int(10) DEFAULT NULL,
x  `col31` int(10) DEFAULT NULL,
x  `col32` int(10) DEFAULT NULL,
--> Precision wrong: BIGINT(20) -> BIGINT(19), always
--> Possible data loss due to 20->19 (!)
--> DEFAULT values lost
--> UNSIGNED lost
--> ZEROFILL lost
x  `col33` bigint(19) DEFAULT NULL,
x  `col34` bigint(19) NOT NULL,
--> col35, col36: wrong data:  1844...bignumber...615 -> -1 ([UN]SIGNED overflow)
x  `col35` bigint(19) DEFAULT NULL,
x  `col36` bigint(19) DEFAULT NULL,
x  `col37` bigint(19) DEFAULT NULL,
--> Precision wrong: FLOAT -> FLOAT(10), always
--> DEFAULT values lost
--> UNSIGNED lost
--> ZEROFILL lost
--> col38, col39, col40: wrong data, fraction part is lost, e.g. -1.01 -> 1
x  `col38` float(10,0) DEFAULT NULL,
x  `col39` float(10,0) NOT NULL,
x  `col40` float(10,0) DEFAULT NULL,
x  `col41` float(5,3) DEFAULT NULL,
--> col42, col43: wrong data, fraction part is lost, e.g. -1.01 -> 1
x  `col42` float(10,0) DEFAULT NULL,
x  `col43` float(10,0) DEFAULT NULL,
--> Precision wrong: DOUBLE -> FLOAT(10), always
--> DEFAULT values lost
--> UNSIGNED lost
--> ZEROFILL lost
--> col44 - col47: wrong data, fraction part is lost, e.g. -1.01 -> 1
x  `col44` double(17,0) DEFAULT NULL,
x  `col45` double(17,0) NOT NULL,
x  `col46` double(17,0) DEFAULT NULL,
x  `col47` double(17,0) DEFAULT NULL,
x  `col48` double(5,3) DEFAULT NULL,
o  `col49` decimal(10,0) DEFAULT NULL,
o  `col50` decimal(10,0) NOT NULL,
--> UNSIGNED lost
--> ZEROFILL lost
x  `col51` decimal(10,0) DEFAULT NULL,
x  `col52` decimal(5,3) DEFAULT NULL,
x  `col53` decimal(6,3) DEFAULT NULL,
o  `col54` date DEFAULT NULL,
o  `col55` date NOT NULL,
--> DEFAULT values lost
x  `col56` date NOT NULL,
o  `col57` datetime DEFAULT NULL,
o  `col58` datetime NOT NULL,
x  `col59` datetime NOT NULL,
--> TIMESTAMP -> DATETIME (safe value range but wrong semantics)
x  `col60` datetime NOT NULL,
o  `col61` time DEFAULT NULL,
o  `col62` time NOT NULL,
--> DEFAULT values lost
x  `col63` time DEFAULT NULL,
--> YEAR->VARCHAR (safe value range but wrong semantics)
--> DEFAULT values lost
x  `col64` varchar(4) DEFAULT NULL,
x  `col65` varchar(4) NOT NULL,
x  `col66` varchar(4) DEFAULT NULL,
x  `col67` varchar(2) DEFAULT NULL,
x  `col68` varchar(4) DEFAULT NULL,
o  `col69` char(1) DEFAULT NULL,
o  `col70` char(1) NOT NULL,
o  `col71` char(255) DEFAULT NULL,
o  `col72` binary(255) DEFAULT NULL,
o  `col73` char(254) NOT NULL,
--> DEFAULT values lost
x  `col74` char(254) NOT NULL,
--> CHARACTER SET lost
--> COLLATION lost
--> DEFAULT values lost
x  `col75` char(255) DEFAULT NULL,
x  `col76` char(215) NOT NULL,
x  `col77` char(215) NOT NULL,
x  `col78` char(255) DEFAULT NULL,
x  `col79` char(55) NOT NULL,
x  `col80` char(250) DEFAULT NULL,
x  `col81` char(250) DEFAULT NULL,
x  `col82` char(43) NOT NULL,
x  `col83` char(123) DEFAULT NULL,
o  `col84` binary(255) DEFAULT NULL,
o  `col85` binary(12) NOT NULL,
--> DEFAULT values lost
x  `col86` char(14) DEFAULT NULL,
o  `col87` binary(25) DEFAULT NULL,
o  `col88` varchar(10) DEFAULT NULL,
o  `col89` varbinary(10) DEFAULT NULL,
o  `col90` varchar(7) NOT NULL,
--> DEFAULT values lost
x  `col91` varchar(255) DEFAULT NULL,
x  `col92` varchar(11) DEFAULT NULL,
x  `col93` varchar(11) DEFAULT NULL,
x  `col94` varchar(12) DEFAULT NULL,
o  `col95` varbinary(13) DEFAULT NULL,
o  `col96` varbinary(14) NOT NULL,
o  `col97` binary(1) DEFAULT NULL,
o  `col98` varbinary(1) DEFAULT NULL,
o  `col99` varbinary(2) NOT NULL,
--> DEFAULT values lost
x  `col100` varbinary(20) NOT NULL,
--> TINYBLOB -> VARBINARY(255)
x  `col101` varbinary(255) DEFAULT NULL,
--> TINYTEXT -> VARCHAR(255)
x  `col102` varchar(255) DEFAULT NULL,
x  `col103` varchar(255) NOT NULL,
x  `col104` varchar(255) DEFAULT NULL,
--> TINYBLOB -> VARBINARY(255)
x  `col105` varbinary(255) DEFAULT NULL,
--> TINYTEXT -> VARCHAR(255)
--> CHARACTER SET lost
--> COLLATION lost
x  `col106` varchar(255) DEFAULT NULL,
x  `col107` varchar(255) DEFAULT NULL,
o  `col108` mediumblob,
o  `col109` mediumblob NOT NULL,
o  `col110` mediumtext,
o  `col111` mediumblob,
o  `col112` mediumtext NOT NULL,
--> CHARACTER SET lost
--> COLLATION lost
x  `col113` mediumtext,
x  `col114` mediumtext,
o  `col115` longblob,
o  `col116` longblob NOT NULL,
o  `col117` longtext,
o  `col118` longblob,
o  `col119` longtext NOT NULL,
--> CHARACTER SET lost
--> COLLATION lost
x  `col120` longtext,
x  `col121` longtext,
o  PRIMARY KEY (`id`)
o) ENGINE=InnoDB DEFAULT CHARSET=latin1

On windows, some database structures make Base crashes

reported by Nicolas Froidure on 2009-08-22 - needs review

If you have a UTF8 database with some ascii-bin collation fields, Open Office crashes when trying to view their content.

Severity: undecided

Reason: unknown

Post-1.0

Renaming a view "makes" it a table

reported by Ulf on 09/01/2009

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

Reason: unknown

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"

Severity: low

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

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

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

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.

getProcedureColumns is a stub

reported by Ulf on 2009-01-27

http://pastebin.com/m75809

Severity: later

Reason: unknown

getWarnings() (C/OOo)

reported by FS on 2009-01-29

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

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 is not used.

getExportedKeys seems to be used in the form wizard, to properly set up sub forms. Given that this currently doesn't work for MySQL/JDBC, either, this does qualify as severity 1.x.

The other ones are not used.

Severity: 1.1

BOOLEAN -> TINYINT conversion

As outlined in the original item with the same title, MySQL does not really know the type BOOLEAN, but implicitly converts columns created as BOOLEAN to TINYINT(1). C/J implicitly converts TINYINT(1) to BOOLEAN, C/OOo doesn't. As a consequence, to the user it looks as if a column created as BOOLEAN silently changes its type after creation.

We should fix this, by either disallowing BOOLEAN as type in OOo, or by also silently interpreting TINYINT(1) as BOOLEAN.

Severity: 1.1

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.

Personal tools