MySQL Bugs: #25828: Falcon compound primary key problem

Bug #25828	Falcon compound primary key problem
Submitted:	24 Jan 2007 14:01	Modified:	24 Jan 2007 16:13
Reporter:	Mikko metsola	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	mysql-5.2.0-falcon-alpha-win32	OS:	Windows (Win XP)
Assigned to:	Hakan Küçükyılmaz	CPU Architecture:	Any

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>