MySQL Bugs: #18727: Primary key too long

Bug #18727	Primary key too long
Submitted:	3 Apr 2006 6:54	Modified:	29 May 2006 10:06
Reporter:	Grace Coronado	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.12	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
If the primary key used is too long, the table will also display ‘#Deleted’ record.

Our current settings:
    Windows XP 5.1
    MS Access 2002
    MySQL Server 5.0.18
    MyODBC 3.51.12 
    MS Jet Engine 4.0

How to repeat:
We have a table in MS Access which we migrated to MySQL which is called REFRENS (list of references).  

I.  Just create the table with the following columns (the actual table has almost 100 columns):

CREATE TABLE `refrens` (
  `Author` varchar(250) NOT NULL,
  `Year` smallint(6) NOT NULL,
  `Title` varchar(255) NOT NULL,
  `SourceUnique` varchar(255) NOT NULL,
  `FirstAuthor` varchar(65) default NULL,
  `TS` timestamp NULL default NULL,
  PRIMARY KEY  (`Author`,`Year`,`Title`,`SourceUnique`),
  KEY `Author` (`Author`),
  KEY `FirstAuthor` (`FirstAuthor`),
  KEY `Year` (`Year`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

II.  Link the table to MS Access and insert/append the following records:

RECORD 1:

Yu, Y.S., A.W. Sin and Y.W. Chan.

1973

Results of induced spawning of the bighead, <i>Aristichthys nobilis</i> (Richardson), grass carp, <i>Ctenopharyngodon idella</i> (Valenciennes) and silver carp, <i>Hypophthalmichthys molitrix</i> (Valenciennes) in Hong Kong, 1969-71.

Hong Kong Fish. Bull. (3): 55-76.

Yu

RECORD 2:

Zarske, A.

1997

Geryiichthys sterbai gen. et spec. nov. und Microcharacidium geryi spec. nov.: Beschreibung einer neuen Gattung un zweier neuer Arten von Bodensalmlern aus dem Eizugsgebiet des rio Ucayali in Peru (Teleostei: Ostariophysi: Characiformes: Characidiidae).

Zool. Abh. Mus. Tierkd. Dresden 49(9):157-172  [English abstract].

Zarske

RECORD 3:

Zimmermann, M., R.C. Harrison and A.F. Jones

2001

Differential parasitism by <i>Naobranchia occidentalis</i> (Copepoda: Naobranchiidae) and <i>Nectobrachia indivisa</i> (Copepoda: Lernaeopodidae) on northern rock sole (<i>Lepidopsetta polyxystra</i> Orr and Matarese, 2000) and southern rock sole

(<i>L. bilineata</i> Ayres, 1588) in Alaskan waters. Fish. Bull. 99(2):371-380.

Zimmermann

RECORD 4:

Zupancic, P. and N.G. Bogutskaya

2002

Description of two new species, <i>Phoxinellus krbavensis</i> & <i>P. jadovensis</i>, re-description of <i>P. fontinalis</i> Karaman, 1972, & discussion of the distribution of <i>Phoxinellus</i>sp. (Teleostei: Cyprinidae) in Croatia, Bosnia & Herzegovina.

Nat. Croat. 11(4):411-437.

Zupancic

Thank you for a problem report. Please, send the 

SELECT * from `refrens`\G

statement results fro the mysql command line client.

Your primary key is too long, really (with UTF8 character set!). You have to change it.

I tried executing:

select * from refrens \G;

as you have suggested.  But it causes SQL syntax error.

Have you triedthat from mysql command line?

Anyway, try to execute this

SELECT * from `refrens`;

from mysql command line client. And send any output you get here.

These are the output that I got (see below).  There's no problem in viewing the data from MySQL.  Only when it's linked within MS Access.

(Btw, I have tried changing the default char to different characters but the results are still the same.

We have developed our database in MS Access and it's been working for years.  We just decided to migrate to MySQL (the backend only) to address the needs for web applications.)

Thanks for the assistance that you give us. - Grace C.

================================

OUTPUT 1:  SELECT WITH OPTION \G

mysql> select * from refrens \G;
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\G' at line 1
mysql> 

OUTPUT 2: SELECT WITHOUT \G OPTION

mysql> select * from refrens;
+-----------------------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+-------------+----+
| Author                            | Year | Title                                                                                                                                                                                                                                                           | SourceUnique                                                                        | FirstAuthor | TS |
+-----------------------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+-------------+----+
| Zupancic, P. and N.G. Bogutskaya  | 2002 | Description of two new species, <i>Phoxinellus krbavensis</i> & <i>P. jadovensis</i>, re-description of <i>P. fontinalis</i> Karaman, 1972, & discussion of the distribution of <i>Phoxinellus</i>sp. (Teleostei: Cyprinidae) in Croatia, Bosnia & Herzegovina. | Nat. Croat. 11(4):411-437.                                                          | Zupancic    | NULL|
| Yu, Y.S., A.W. Sin and Y.W. Chan. | 1973 | Results of induced spawning of the bighead, <i>Aristichthys nobilis</i> (Richardson), grass carp, <i>Ctenopharyngodon idella</i> (Valenciennes) and silver carp, <i>Hypophthalmichthys molitrix</i> (Valenciennes) in Hong Kong, 1969-71.                       | Hong Kong Fish. Bull. (3): 55-76.                                                   | Yu          | NULL|
| Zhao, Y., C. Ma and W. Song       | 2000 | Description of two new species of <i>Parvicapsula</i> Shulman, 1953 (Myxosporea: Parvicapsulidae) parasitic in the urinary bladder of marine fishes, <i>Paralichthys olivaceus</i> and <i>Kareius bicoloratus</i>, from the coast of the yellow sea, China.     | p.188. In: The Third World Fisheries Congress abstracts books. Beijing, P.R. China. | Zhao        | NULL|
| Zarske, A.                        | 1997 | Geryiichthys sterbai gen. et spec. nov. und Microcharacidium geryi spec. nov.: Beschreibung einer neuen Gattung un zweier neuer Arten von Bodensalmlern aus dem Eizugsgebiet des rio Ucayali in Peru (Teleostei: Ostariophysi: Characiformes: Characidiidae).   | Zool. Abh. Mus. Tierkd. Dresden 49(9):157-172  [English abstract].                  | Zarske      | NULL|
+-----------------------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+-------------+----+
4 rows in set
 
mysql>

Actually it is "select * from refrens\G" (without "). Did you shorten your primary key? If so, what were the results?

Here's the result of the SELECT statement.
=========================================

mysql> select * from refrens \G;
*************************** 1. row ***************************
      Author: Zupancic, P. and N.G. Bogutskaya
        Year: 2002
       Title: Description of two new species, <i>Phoxinellus krbavensis</i> & <i>P. jadovensis</i>, re-description of <i>P. fontinalis</i> Karaman, 1972, & discussion of the distribution of <i>Phoxinellus</i>sp. (Teleostei: Cyprinidae) in Croatia, Bosnia & Herzegovina.
SourceUnique: Nat. Croat. 11(4):411-437.
 FirstAuthor: Zupancic
          TS: NULL
*************************** 2. row ***************************
      Author: Yu, Y.S., A.W. Sin and Y.W. Chan.
        Year: 1973
       Title: Results of induced spawning of the bighead, <i>Aristichthys nobilis</i> (Richardson), grass carp, <i>Ctenopharyngodon idella</i> (Valenciennes) and silver carp, <i>Hypophthalmichthys molitrix</i> (Valenciennes) in Hong Kong, 1969-71.
SourceUnique: Hong Kong Fish. Bull. (3): 55-76.
 FirstAuthor: Yu
          TS: NULL
*************************** 3. row ***************************
      Author: Zhao, Y., C. Ma and W. Song
        Year: 2000
       Title: Description of two new species of <i>Parvicapsula</i> Shulman, 1953 (Myxosporea: Parvicapsulidae) parasitic in the urinary bladder of marine fishes, <i>Paralichthys olivaceus</i> and <i>Kareius bicoloratus</i>, from the coast of the yellow sea, China.
SourceUnique: p.188. In: The Third World Fisheries Congress abstracts books. Beijing, P.R. China.
 FirstAuthor: Zhao
          TS: NULL
*************************** 4. row ***************************
      Author: Zarske, A.
        Year: 1997
       Title: Geryiichthys sterbai gen. et spec. nov. und Microcharacidium geryi spec. nov.: Beschreibung einer neuen Gattung un zweier neuer Arten von Bodensalmlern aus dem Eizugsgebiet des rio Ucayali in Peru (Teleostei: Ostariophysi: Characiformes: Characidiidae).
SourceUnique: Zool. Abh. Mus. Tierkd. Dresden 49(9):157-172  [English abstract].
 FirstAuthor: Zarske
          TS: NULL
4 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> notee

Grace, did you shorten your primary key and what were the results?

Actually, we don't intend to shorten it.  We will handle it from the code (in MS Access) instead, to get a unique combination of all the fields.  It will be helpful to us, if future release of MySQL will be able to handle longer primary keys.

Hi Grace. Much better approach :)

I would like to suggest not to use varchar(255), it will slow down your search. Using varchar(200) (or even something much smaller) should suffice to store the actual data values you need in those fields. The alternative is to only index a portion of your varchar(255) fields by putting the length of the field after the field name enclosed in parentheses (http://dev.mysql.com/doc/mysql/en/create-index.html):
UNIQUE (VCFld1-255 (200), VCFld2-255 (200)...)