Bug #18110 utf8 VARCHAR fields mis-linked as MEMO fields
Submitted: 9 Mar 2006 15:22 Modified: 28 Mar 2006 21:18
Reporter: Ged Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.12.00 OS:Windows (XP)
Assigned to: CPU Architecture:Any

[9 Mar 2006 15:22] Ged
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;
[9 Mar 2006 15:23] Ged
Changing Severity to S2
[9 Mar 2006 16:55] Ged
I'll try to reformat the table of behaviour I gave before:

                          shortstring    longstring    verylongstring
test_latin1                 text          text              text
test_utf8_general        text          memo           memo
test_utf8_swedish       text          text              memo
test_utf8_unicode       text          memo            memo
[23 Mar 2006 14:35] Tonci Grgin
I've been unable to repeat the error with 4.1.18-nt-max-log and 3.51.12 MyODBC driver. I'am  also attaching Access2003 screenshot. If I understood you wrongly, provide me with more info, please.
[23 Mar 2006 14:36] Tonci Grgin
Access screenshot

Attachment: 18110.jpg (image/jpeg, text), 86.19 KiB.

[28 Mar 2006 21:18] Tonci Grgin
Since no additional info was provided I'm changing status to "Can't repeat".