MySQL Bugs: #25828: Falcon compound primary key problem

Bug #25828	Falcon compound primary key problem
Submitted:	24 Jan 2007 15:01	Modified:	24 Jan 2007 17:13
Reporter:	M Me
Status:	Closed
Category:	Server: Falcon	Severity:	S2 (Serious)
Version:	mysql-5.2.0-falcon-alpha-win32	OS:	Microsoft Windows (Win XP)
Assigned to:	Bugs System	Target Version:

Description:
It seems that compound primary keys don't work anymore in Falcon. I have e.g. a
BIGINT-VARCHAR primary key, and during insert I get a duplicate primary key errors where
as the same inserts work on MYISAM. The BIGINT part is the same but VARCHAR part varies
slightly. Seems like the whole VARCHAR is not used for comparison.

Is there some new directive to enforce similar behaviour in Falcon as was in MYISAM?

mysql> create table test(a bigint not null, b varchar(64) not null, primary key(a,b));
Query OK, 0 rows affected (0.44 sec)

mysql> insert into test values(1,'test');
Query OK, 1 row affected (0.05 sec)

mysql> insert into test values(1,'test2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(1,'test3');
ERROR 1062 (23000): Duplicate entry '1-test3' for key 'PRIMARY'
mysql> select * from test;
+---+-------+
| a | b     |
+---+-------+
| 1 | test  |
| 1 | test2 |
+---+-------+

How to repeat:
create tables and insert data as described in description section.

Can't repeat with mysql-5.2.1-falcon-alpha-debug.

[15:15] root@test> create table test(a bigint not null, b varchar(64) not null, primary
    -> key(a,b));
Query OK, 0 rows affected (0.48 sec)

[15:16] root@test>show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                   
                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `a` bigint(20) NOT NULL,
  `b` varchar(64) NOT NULL,
  PRIMARY KEY (`a`,`b`)
) ENGINE=Falcon DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[15:16] root@test>insert into test values(1,'test');
Query OK, 1 row affected (0.11 sec)

[15:16] root@test>insert into test values(1,'test2');
Query OK, 1 row affected (0.10 sec)

[15:16] root@test>insert into test values(1,'test3');
Query OK, 1 row affected (0.13 sec)

Best regards,

Hakan

Can you please provide the output of
  SHOW CREATE TABLE test;

Thanks, Hakan

CREATE TABLE `test` (
  `a` bigint(20) NOT NULL,
  `b` varchar(64) NOT NULL,
  PRIMARY KEY (`a`,`b`)
) ENGINE=Falcon DEFAULT CHARSET=utf8

Can I already download 5.2.1 for Win?

I can reproduce this failure with the Falcon alpha release, but it works correctly in the
current codebase.  There were some character string changes checked in Monday night that
probably fixed this.

OK, another similar example for currently downloadable mysql-5.2.0-falcon-alpha-win32
would be:

CREATE TABLE `test2` (
  `a` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`a`),
  UNIQUE KEY `a` (`a`)
) ENGINE=Falcon DEFAULT CHARSET=utf8

--

insert into test2 values('test_user');
insert into test2 values('Test user');

-> Duplicate entry 'Test user' for key 'PRIMARY'

This is fixed in latest version. A new public alpha version will be released soon.

Thanks for your bug report,

Hakan

5.2.1-falcon-alpha-debug
[17:11] root@test>CREATE TABLE `test2` (
    ->   `a` varchar(64) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`a`),
    ->   UNIQUE KEY `a` (`a`)
    -> ) ENGINE=Falcon DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.15 sec)

[17:11] root@test>insert into test2 values('test_user');
Query OK, 1 row affected (0.12 sec)

[17:11] root@test>insert into test2 values('Test user');
Query OK, 1 row affected (0.11 sec)

Indeed already fixed:

C:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.2.1-falcon-alpha-nt Source distribution

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

mysql> create database db7;
Query OK, 1 row affected (0.02 sec)

mysql> use db7
Database changed
mysql> CREATE TABLE `test2` (
    ->   `a` varchar(64) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`a`),
    ->   UNIQUE KEY `a` (`a`)
    -> ) ENGINE=Falcon DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.42 sec)

mysql> show create table test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `a` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`a`),
  UNIQUE KEY `a` (`a`)
) ENGINE=Falcon DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into test2 values('test_user');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test2 values('Test user');
Query OK, 1 row affected (0.00 sec)

mysql>