Bug #12981 index on a 'char() binary' field case-insensitive

• Submitted: 5 Sep 2005 7:41
• Modified: 5 Sep 2005 12:51
• Reporter: sean finney
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.13a
• OS: Linux (Debian GNU/Linux)
• CPU Architecture: Any

[5 Sep 2005 7:41] sean finney

Description:
originally reported in the debian BTS at http://bugs.debian.org/326698.

from the OP:

The following SQL fails on mysql-server 4.1.13a-3 with
                                                      
    ERROR 1062 (23000) at line 28: Duplicate entry 'J/JO/JONO/cpan.html' for key 1

Needless to say that this worked in 3.x and 4.0 as the column is
declared binary which means that case makes a difference. Here is the
SQL:

-- MySQL dump 9.11
--
-- Host: localhost    Database: mod
-- ------------------------------------------------------
-- Server version       4.0.24_Debian-10-log

--
-- Table structure for table `applymod`
--

--
-- Table structure for table `distmtimes`
--

DROP TABLE IF EXISTS distmtimes;
CREATE TABLE distmtimes (
  dist char(128) binary NOT NULL default '',
  distmtime char(10) default NULL,
  distmdatetime datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (dist)
) TYPE=ISAM PACK_KEYS=1;

--
-- Dumping data for table `distmtimes`
--

INSERT INTO distmtimes VALUES ('J/JO/JONO/CPAN.html','966270168','2000-08-14 18:22:48');
INSERT INTO distmtimes VALUES ('J/JO/JONO/cpan.html','1004130109','2001-10-26 23:01:49');

How to repeat:
the included SQL statements should repeat it.

[5 Sep 2005 12:51] Valeriy Kravchuk

I tried to repeat this behaviour on newer 4.1.14 (Windows) and 4.1.15-BK (Linux), with MyISAM table type, but was unable to do it:

mysql> DROP TABLE IF EXISTS distmtimes;
Query OK, 0 rows affected, 1 warning (0,03 sec)

mysql> CREATE TABLE distmtimes (
    ->   dist char(128) binary NOT NULL default '',
    ->   distmtime char(10) default NULL,
    ->   distmdatetime datetime NOT NULL default '0000-00-00 00:00:00',
    ->   PRIMARY KEY  (dist)
    -> ) Engine=MyISAM PACK_KEYS=1;

Query OK, 0 rows affected (0,00 sec)

mysql> INSERT INTO distmtimes VALUES ('J/JO/JONO/CPAN.html','966270168','2000-08-14 18:22:48');
Query OK, 1 row affected (0,00 sec)

mysql> IINSERT INTO distmtimes VALUES ('J/JO/JONO/cpan.html','1004130109','2001-10-26 23:01:49');
Query OK, 1 row affected (0,00 sec)

mysql> select * from distmtimes;
+---------------------+------------+---------------------+
| dist                | distmtime  | distmdatetime       |
+---------------------+------------+---------------------+
| J/JO/JONO/CPAN.html | 966270168  | 2000-08-14 18:22:48 |
| J/JO/JONO/cpan.html | 1004130109 | 2001-10-26 23:01:49 |
+---------------------+------------+---------------------+
2 rows in set (0,00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.15    |
+-----------+
1 row in set (0,01 sec)

According to the manual (http://dev.mysql.com/doc/mysql/en/isam-storage-engine.html), ISAM is a depricated engine. 

Try to repeat this test case on latest 4.1.x versions of MySQL with MyISAM and reopen the bug report if the problem still exists.