MySQL Bugs: #32203: Using MySQL ODBC, cannot access views from Access (and possibly other apps?)

Bug #32203	Using MySQL ODBC, cannot access views from Access (and possibly other apps?)
Submitted:	8 Nov 2007 22:34	Modified:	11 Nov 2007 19:37
Reporter:	Andrew Hanna	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51, 5.1	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
My apologies if this is a documented bug/unsupported feature, but I could not find any documentation on it.

If using a view as a linked table in Access, opening this table simply lists #Error in all the columns, and will report several "Scaling of decimal value resulted in data truncation" error messages.

How to repeat:
CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `test` text,
  `number` decimal(10,9) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `t2` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `t2text` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `t` VALUES (1, "Hello", 9.345456);
INSERT INTO `t2` VALUES (1, "Hi");

CREATE OR REPLACE VIEW `tv` AS SELECT * FROM `t` NATURAL LEFT JOIN `t2`;

Now, setup your ODBC connection and all that.  In Access, link the two tables and the view.  You can browse the individual tables fine.  But, browse the view and you get the errors.  Now do this:

ALTER TABLE `t` DROP `number`;

CREATE OR REPLACE VIEW `tv` AS SELECT * FROM `t` NATURAL LEFT JOIN `t2`;

Reload/refresh the table definitions in Access to see the new table and view design.  Then view again.  No errors.

Suggested fix:
I'm guessing it has something to do with how MySQL reports the DECIMAL field in the VIEW.  Not sure what Access does to get column definitions, but it is getting invalid information.  If you go into design view for the tables and the view, you will see that the tables properly report (10, 9) for the precision of the decimal.  But the view reports just the total length, without any decimals.

Table Design View:

Precision: 10
Scale: 9

View Design View:

Precision: 11
Scale: 0

Looks like it's getting the total string length of the field as the precision and no decimal places for scale.  #.######### => total length of 11.  I had a hypothesis that changing the decimal to signed would change the length to 12, but it did not appear to.

Access showing the view properly

Attachment: Bug32203.JPG (image/jpeg, text), 61.69 KiB.

Hi Andrew and thanks for your report. You got me thinking for a while over this...
Here's what I came up with:
 - Check attached image, see that my Access 2003 and MyODBC 3.51.21 show correct result.
 - I'm using MySQL server 5.0.50pb on WinXP Pro SP2 localhost
 - In DDL script I just changed t -> t32203-1, t2 -> t32203-2 and tv -> tv32203
If you start mysql cl client with -T option you should see Field 3: 'number' is defined as NEWDECIMAL which is a rather large type:
c:\mysql507\bin>mysql -uroot -p -T test
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.50-pb1046-log MySQL Pushbuild Edition, build 1046

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from tv32203;
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `tv32203`
Org_table:  `t32203-1`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY

Field   2:  `test`
Catalog:    `def`
Database:   `test`
Table:      `tv32203`
Org_table:  `t32203-1`
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     65535
Max_length: 5
Decimals:   0
Flags:      BLOB

Field   3:  `number`
Catalog:    `def`
Database:   `test`
Table:      `tv32203`
Org_table:  `t32203-1`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     11
Max_length: 11
Decimals:   9
Flags:      UNSIGNED

Field   4:  `t2text`
Catalog:    `def`
Database:   `test`
Table:      `tv32203`
Org_table:  `t32203-2`
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     65535
Max_length: 2
Decimals:   0
Flags:      BLOB

+----+-------+-------------+--------+
| id | test  | number      | t2text |
+----+-------+-------------+--------+
|  1 | Hello | 9.345456000 | Hi     |
+----+-------+-------------+--------+
1 row in set (0.00 sec)

If you were to put wider float type into smaller one I would have expected this error but that doesn't seem to be the case...

There is a long history of Microsoft fighting DECIMAL types, especially Excel and Access. In Access 97 the data would just disappear if you tried to programmatically assign Decimal type data to display in a form or report. Sorting of DECIMAL fields is not yet fixed not even in Access 2007 (http://allenbrowne.com/bug-08.html) etc.

Now, please add MySQL server version, host OS info, Access version info, MDAC info, MyODBC version info, MyODBC options used to this otherwise great report and maybe we can find out where the problem might be.

Well, I have Office XP, so that might be the problem.  It seems to not be a problem with the Float/Double data types, so I think I'll just think about switching to that column type instead...

Thanks for the help, though!

Andrew, it could be, especially with numeric structure ODBC uses but as I can't repeat it I'll close report for now.

Thanks for your interest in MySQL.

Andrew,

this really seems to be your Office version. I can't repeat this with Office 2007 too.

Susanne

here is my screenshot from Office 2007

Attachment: accees_view.JPG (image/jpeg, text), 115.35 KiB.