Description:
I am using v3.51.12.00 of the MySQL Connector to link from Access to a v4.1.7-max-log MySQL database using a System DSN. When linking to a table with VARCHAR fields of collation type utf8_*, these fields are linked as MEMO type depending on the exact collation type and the length of the VARCHAR field (see "How to repeat" below). Linking to a table with VARCHAR fields of collation type latin1_swedish_ci is linked correctly as text fields.
This has been set at severity S2, since UTF8 VARCHAR fields are unusable if interpreted as Memo fields by Access. You cannot join on a Memo field...
How to repeat:
An SQL Dump from phpmyadmin can be found below. Load that into a MySQL database. Link to the 4 tables using Access via an ODBC Connection using a System DSN and v3.51.12.00 of the MySQL Connector. Access will see the fields as follows:
test_latin1 test_utf8_general test_utf8_swedish test_utf8_unicode
shortstring text text text text
longstring text memo text memo
verylongstring text memo memo memo
I would expect that the behaviour of all utf8 collations would be like latin1. That is, all varchar's up to length 255 would be recognised as text fields by Access.
-- phpMyAdmin SQL Dump
-- version 2.6.0-pl2
-- http://www.phpmyadmin.net
--
-- Host: 158.232.76.11:3307
-- Generation Time: Mar 09, 2006 at 04:08 PM
-- Server version: 4.1.7
-- PHP Version: 4.2.1
--
-- Database: `hinari_reg`
--
-- --------------------------------------------------------
--
-- Table structure for table `test_latin1`
--
CREATE TABLE `test_latin1` (
`shortstring` varchar(10) NOT NULL default '',
`longstring` varchar(100) NOT NULL default '',
`reallylongstring` varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `test_utf8_general`
--
CREATE TABLE `test_utf8_general` (
`shortstring` varchar(10) NOT NULL default '',
`longstring` varchar(100) NOT NULL default '',
`reallylongstring` varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `test_utf8_swedish`
--
CREATE TABLE `test_utf8_swedish` (
`shortstring` varchar(10) collate utf8_swedish_ci NOT NULL default '',
`longstring` varchar(100) collate utf8_swedish_ci NOT NULL default '',
`reallylongstring` varchar(255) collate utf8_swedish_ci NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
-- --------------------------------------------------------
--
-- Table structure for table `test_utf8_unicode`
--
CREATE TABLE `test_utf8_unicode` (
`shortstring` varchar(10) collate utf8_unicode_ci NOT NULL default '',
`longstring` varchar(100) collate utf8_unicode_ci NOT NULL default '',
`reallylongstring` varchar(255) collate utf8_unicode_ci NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Description: I am using v3.51.12.00 of the MySQL Connector to link from Access to a v4.1.7-max-log MySQL database using a System DSN. When linking to a table with VARCHAR fields of collation type utf8_*, these fields are linked as MEMO type depending on the exact collation type and the length of the VARCHAR field (see "How to repeat" below). Linking to a table with VARCHAR fields of collation type latin1_swedish_ci is linked correctly as text fields. This has been set at severity S2, since UTF8 VARCHAR fields are unusable if interpreted as Memo fields by Access. You cannot join on a Memo field... How to repeat: An SQL Dump from phpmyadmin can be found below. Load that into a MySQL database. Link to the 4 tables using Access via an ODBC Connection using a System DSN and v3.51.12.00 of the MySQL Connector. Access will see the fields as follows: test_latin1 test_utf8_general test_utf8_swedish test_utf8_unicode shortstring text text text text longstring text memo text memo verylongstring text memo memo memo I would expect that the behaviour of all utf8 collations would be like latin1. That is, all varchar's up to length 255 would be recognised as text fields by Access. -- phpMyAdmin SQL Dump -- version 2.6.0-pl2 -- http://www.phpmyadmin.net -- -- Host: 158.232.76.11:3307 -- Generation Time: Mar 09, 2006 at 04:08 PM -- Server version: 4.1.7 -- PHP Version: 4.2.1 -- -- Database: `hinari_reg` -- -- -------------------------------------------------------- -- -- Table structure for table `test_latin1` -- CREATE TABLE `test_latin1` ( `shortstring` varchar(10) NOT NULL default '', `longstring` varchar(100) NOT NULL default '', `reallylongstring` varchar(255) NOT NULL default '' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `test_utf8_general` -- CREATE TABLE `test_utf8_general` ( `shortstring` varchar(10) NOT NULL default '', `longstring` varchar(100) NOT NULL default '', `reallylongstring` varchar(255) NOT NULL default '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `test_utf8_swedish` -- CREATE TABLE `test_utf8_swedish` ( `shortstring` varchar(10) collate utf8_swedish_ci NOT NULL default '', `longstring` varchar(100) collate utf8_swedish_ci NOT NULL default '', `reallylongstring` varchar(255) collate utf8_swedish_ci NOT NULL default '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci; -- -------------------------------------------------------- -- -- Table structure for table `test_utf8_unicode` -- CREATE TABLE `test_utf8_unicode` ( `shortstring` varchar(10) collate utf8_unicode_ci NOT NULL default '', `longstring` varchar(100) collate utf8_unicode_ci NOT NULL default '', `reallylongstring` varchar(255) collate utf8_unicode_ci NOT NULL default '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;