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

From Apache OpenOffice Wiki
Jump to: navigation, search
(Tools -> User Administration does not work)
 
(131 intermediate revisions by 5 users not shown)
Line 1: Line 1:
{{DISPLAYTITLE:MySQL Native Driver: Known Problems}}
+
{{DISPLAYTITLE:Sun MySQL Connector/OOo: Known Problems}}
  
This page lists the currently known problems with the MySQL Native Driver, aka MySQL Connector/OOo.
+
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]].
  
== End User Observations ==
+
== required for 1.0 release ==
  
This section collects issues as observed by end users
+
=== On windows, the extension crashs on some systems ===
  
=== Template for new entries ===
+
<span style="background-color:#F0EEC0; padding:3px">reported by FS on 2009-04-09</span>
  
<span style="background-color:#F0EEC0; padding:3px">reported by NAME on 2009-mm-dd - needs review</span>
+
[[Database/Drivers/MySQL_Native/Beta/Known_Issues#On_windows.2C_the_extension_crashs_on_some_systems|detailed here]]
  
Description goes here.
+
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
  
Severity: <span style="background-color:#E0E0E0; padding:3px">undecided</span>
+
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.
  
Reason: unknown
+
<span style="background-color:#FFC0C0">TODO (Andrey):</span> fix
  
=== severe character set problems ===
+
=== Linux version does not install on systems without libstdc++.so.5 ===
  
Tables columns containing non-ASCII characters in their name are improperly named in OOo.
+
[[Database/Drivers/MySQL_Native/Beta/Known_Issues#Linux_version_does_not_install_on_systems_without_libstdc.2B.2B.so.5|detailed here]]
  
 
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
 
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
  
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).
+
== Unclassified/TODO ==
  
I'd say we have a serious problem here.
+
=== Template for new entries ===
  
=== Binary columns are reported as text ===
+
<span style="background-color:#F0EEC0; padding:3px">reported by NAME on 2009-mm-dd - needs review</span>
  
Columns of type "LONG (VAR)BINARY" are reported as "LONG (VAR)CHAR" in the table designer
+
Description goes here.
  
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
+
Severity: <span style="background-color:#E0E0E0; padding:3px">undecided</span>
  
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.
+
Reason: unknown
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, [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) DATA_TYPE is defined] to contain a value from the java.sql.Types class, which already is a normalization we need ...
+
  
=== Copying tables may not work properly ===
+
=== Copying tables does not work properly ===
  
 
<span style="background-color:#F0EEC0; padding:3px">reported by NAME on xx/01/2009 - needs review</span>
 
<span style="background-color:#F0EEC0; padding:3px">reported by NAME on xx/01/2009 - needs review</span>
Line 76: Line 75:
 
Reason: unknown
 
Reason: unknown
  
=== Wrong column width reported for text/binary columns ===
+
<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).
  
<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 09/01/2009 - needs review</span>
+
(Ulf) Create the following table on the MySQL prompt and clone it using Base. Compare the table that Base creates with the original one.
  
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.
+
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
  
From an earlier mail exchange:
+
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')
  
  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:
+
(Ulf) Issue summary:
  
  Später am Abend fiel es mir wieder ein... wir ziehen getColumnDisplaySize() intern im Connector/C++ ran für ConnectionMetaData::getColumns() -> COLUMN_SIZE.
+
* 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.
  rs_data.push_back(my_i_to_a(buf, sizeof(buf)-1, (long) rs3_meta->getColumnDisplaySize(i))); // Column size
+
** 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
  
Pointer: MySQL ODBC file utitlity.c get_column_size
 
  
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
+
(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 ===
 +
 
 +
<span style="background-color:#F0EEC0; padding:3px">reported by Nicolas Froidure on 2009-08-22 - needs review</span>
 +
 
 +
If you have a UTF8 database with some ascii-bin collation fields, Open Office crashes when trying to view their content.
 +
 
 +
Severity: <span style="background-color:#E0E0E0; padding:3px">undecided</span>
  
 
Reason: unknown
 
Reason: unknown
  
=== (JDBC) Renaming a view "makes" it a table ===
+
== 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>
 
<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 :-)
 
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>
 
Severity: <span style="background-color:#C0C0FF; padding:3px">low</span>
Line 118: Line 479:
  
 
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.
 
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.
 
=== 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: <span style="background-color:#f0ee00; padding:3px">desired</span>
 
  
 
=== No way to set MySQL specific table attributes ===
 
=== No way to set MySQL specific table attributes ===
Line 148: Line 501:
  
 
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 ...
 
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: <span style="background-color:#E0E0E0; padding:3px">undecided</span>
 
 
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 ===
 
=== Default values not properly processed ===
Line 173: Line 516:
 
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.
 
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:#E0E0E0; padding:3px">undecided</span>
+
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.
 
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.
 
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.
 
=== 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:#FF8080; padding:3px">required</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.
 
 
=== query wizard shows 'query' as 'table' ===
 
 
<span style="background-color:#F0EEC0; padding:3px">reported by [http://wiki.services.openoffice.org/wiki/User:Clu CLU] on 12/01/2009 - needs review</span>
 
 
create any query, open query wizard → tables listbox propose query (also with the other wizards)
 
 
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
 
 
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 ...
 
 
=== 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
 
 
=== OPreparedStatement::getMetaData is not implemented ===
 
 
<span style="background-color:#F0EEC0; padding:3px">reported by FS on 2009-01-29</span>
 
 
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: <span style="background-color:#f0ee00; padding:3px">desired</span>
 
  
 
=== getProcedureColumns is a stub ===
 
=== getProcedureColumns is a stub ===
  
<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 2009-01-27 - needs review</span>
+
<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 2009-01-27</span>
  
 
http://pastebin.com/m75809
 
http://pastebin.com/m75809
  
Severity: <span style="background-color:#E0E0E0; padding:3px">undecided</span>
+
Severity: <span style="background-color:#C0C0FF; padding:3px">later</span>
  
 
Reason: unknown
 
Reason: unknown
 
=== Exception not converted ===
 
 
<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 2009-01-27</span>
 
 
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: <span style="background-color:#FF8080; padding:3px">required</span>
 
 
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.
 
 
=== 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
 
 
=== Definition of catalog and schema ===
 
 
<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 2009-01-27</span>
 
 
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: <span style="background-color:#E0E0E0; padding:3px">undecided</span>
 
 
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.
 
 
=== XCloseable::close throws wrong exception ===
 
 
<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 2009-01-26 - needs review</span>
 
 
The following sequence does not issue a SQLEception but a DisposedException as it seems:
 
 
[...]
 
statement.close()
 
statement.executeQuery()
 
 
It should throw SQLException, accoding to http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/XCloseable.html
 
 
Severity: <span style="background-color:#E0E0E0; padding:3px">undecided</span>
 
 
FS: The DisposedException is the usual exception when you try to work with a, well, disposed object. The XCloseable documentation linked above only mentions that an SQLException is thrown in case of a "database access error", anyway. Also, I bet that in the sequence above, the exception is really thrown during executeQuery - which would be correct. In short: I don't see an issue here.
 
 
=== getWarnings() (C/C++) ===
 
 
<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 2009-01-26</span>
 
 
getWarnings() always returns a warning object, regardless if there is one or not. Should it return NULL? JDBC compliance test expects NULL.
 
 
Severity: <span style="background-color:#E0E0E0; padding:3px">undecided</span>
 
 
Reason: Connector/OO.org
 
  
 
=== getWarnings() (C/OOo) ===
 
=== getWarnings() (C/OOo) ===
Line 310: Line 537:
  
 
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.
 
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>
  
 
=== Do we need any of those? ===
 
=== Do we need any of those? ===
Line 322: Line 551:
  
 
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.)
 
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.
 
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.
+
getObject is not used.
  
The other three are not used, I think.
+
getExportedKeys seems to be used in the form wizard, to properly set up sub forms. Given that this currently [http://www.openoffice.org/issues/show_bug.cgi?id=101463 doesn't work for MySQL/JDBC], either, this does qualify as severity 1.x.
  
 +
The other ones are not used.
  
 +
Severity: <span style="background-color:#c0ffc0; padding:3px">1.1</span>
  
=== DatabseMetaData::getColumns() COLUMN_SIZE ===
+
=== BOOLEAN -> TINYINT conversion ===
  
DatabseMetaData::getColumns() takes COLUMN_SIZE from getDisplaySize().
+
As outlined in [[Database/Drivers/MySQL_Native/Known_Problems/Fixed#BOOLEAN_-.3E_TINYINT_conversion|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.
  
Affected: DECIMAL/FLOAT/VARCHAR.
+
We should fix this, by either disallowing BOOLEAN as type in OOo, or by also silently interpreting TINYINT(1) as BOOLEAN.
  
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
+
Severity: <span style="background-color:#c0ffc0; padding:3px">1.1</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.
+
 
+
=== Perfoemance problem when accessing big tables ===
+
 
+
Open a table with more then 1000 rows. Jump to last one. => 2 min.
+
 
+
 
+
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
+
  
 
== Potential Issues ==
 
== Potential Issues ==
Line 367: Line 584:
 
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.
 
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.
  
== fixed ==
+
[[Category:MySQL]]
 
+
=== 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: <span style="background-color:#FF8080; padding:3px">required</span>
+
 
+
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 ===
+
 
+
<span style="background-color:#F0EEC0; padding:3px">reported by [http://wiki.services.openoffice.org/wiki/User:Clu CLU] on 12/01/2009</span>
+
 
+
create any table with pk → not possible to insert data into table
+
 
+
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
+
 
+
Investigation: The driver currently returns an empty result set when being asked for the table privileges: <code>getTablePrivileges( null, "schema", "table" )</code>.  Debugging deeper seems to indicate that parsing the result of a "SHOW GRANTS" query is flawed.
+
 
+
=== Tools->SQL does not show error message ===
+
 
+
<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 08/01/2009</span>
+
 
+
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: <span style="background-color:#FF8080; padding:3px">required</span>
+
 
+
=== New tables shown after "refresh tables" but (delete) rights/grants wrong? ===
+
 
+
<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 08/01/2009</span>
+
 
+
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, <code>DatabaseMetaData::getTablePrivileges</code> 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: <span style="background-color:#FF8080; padding:3px">required</span>
+
 
+
Investigation: column is reported as <code>MYSQL_TYPE_NEWDECIMAL</code>, which wasn't covered in C/OOo (which only had a case for <code>MYSQL_TYPE_DECIMAL</code>)
+
=== 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: <span style="background-color:#FF8080; padding:3px">required</span>
+
 
+
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 <code>EscapeProcessing</code> is <code>FALSE</code>, 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: <span style="background-color:#FF8080; padding:3px">required</span>
+
 
+
=== change pk fails ===
+
 
+
<span style="background-color:#F0EEC0; padding:3px">reported by [http://wiki.services.openoffice.org/wiki/User:Clu CLU] on 12/01/2009</span>
+
 
+
open table with pk and change pk to any other field -> error: duplicate entry for key
+
 
+
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
+
 
+
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.
+
 
+
=== copy table & paste special fails ===
+
 
+
<span style="background-color:#F0EEC0; padding:3px">reported by [http://wiki.services.openoffice.org/wiki/User:Clu CLU] on 12/01/2009</span>
+
 
+
copy table & paste special (change tablename) with pk ? invalid error message without text appears
+
 
+
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
+
 
+
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.
+
 
+
=== 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>
+
 
+
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 ===
+
 
+
<span style="background-color:#F0EEC0; padding:3px">reported by [http://wiki.services.openoffice.org/wiki/User:Clu CLU] on 12/01/2009</span>
+
 
+
run table wizard: take default & all field & push finish ? java assertion list appears
+
 
+
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
+
 
+
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 [http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/XConnection.html#getCatalog 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 ===
+
 
+
<span style="background-color:#F0EEC0; padding:3px">reported by Ulf on 09/01/2009</span>
+
 
+
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: <span style="background-color:#f0ee00; padding:3px">desired</span>
+
 
+
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: <span style="background-color:#f0ee00; padding:3px">desired</span>
+
 
+
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: <span style="background-color:#f0ee00; padding:3px">desired</span>
+
 
+
=== change table fieldtype in edit view not possible ===
+
 
+
<span style="background-color:#F0EEC0; padding:3px">reported by [http://wiki.services.openoffice.org/wiki/User:Clu CLU] on 12/01/2009</span>
+
 
+
open table in edit mode -> fieldtypes greyed out (not possible to change)
+
 
+
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
+
 
+
Reason: The driver does not support <code>XAlterTable</code> (and more SDBCX-level interfaces)
+
 
+
=== Tools -> User Administration does not work ===
+
 
+
When you choose <code>Tools/User administration...</code>, you're presented with an error.
+
 
+
Severity: <span style="background-color:#FF8080; padding:3px">required</span>
+
 
+
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: <span style="background-color:#FF8080; padding:3px">required</span>
+
 
+
FS: fixed meanwhile. Was most probably a duplicate of the other "can't insert data" issue
+

Latest revision as of 21:40, 15 December 2009


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