MySQL Bugs: #21881: Inconsistent error message on failure creating unique index on ndb backed table

Bug #21881	Inconsistent error message on failure creating unique index on ndb backed table
Submitted:	28 Aug 2006 16:31	Modified:	21 Nov 2006 3:53
Reporter:	Roland Bouman	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	5.1.12-BK, 5.1.11-beta	OS:	Linux (Linux)
Assigned to:	justin he	CPU Architecture:	Any
Tags:	error, ERROR 1062, ERROR 1169, INDEX, ndb, unique

Description:
Creating a unique index using hash on a table that uses the NDB storage engine gives error 1169. The identical situation for a table that uses the MyISAM or InnoDB storage engine gives error 1062.

The error messages are different too:

ERROR 1062 (23000): Duplicate entry <entry> for key <key>

ERROR 1169 (23000): Can't write, because of unique constraint, to table <table>

It seems to me that 1169 is the odd duck here:
- it mentions a unique constraint - there is none, just an index constraint
- it mentions writing to the table -  why would it mention that?
 
Furthermore, the sql state values are the same: 23000. 

It becomes stranger still when the USING HASH clause is ommitted. For InnoDB and MyISAM, the error remains 1062 as expected. For NDB, you suddenly get:

ERROR 1296 (HY000): Got error 156 'Unknown error code' from NDBCLUSTER

How to repeat:
create table t1 (id int not null) engine = ndb;

insert into tt values (1),(1);

create unique index utt on tt (id) USING HASH;

Gives:
ERROR 1169 (23000): Can't write, because of unique constraint, to table 'City'

create unique index utt on tt (id);

ERROR 1296 (HY000): Got error 156 'Unknown error code' from NDBCLUSTER

Suggested fix:
If a similar situation is intended as for InnoDB and MyISAM, use the same error: 1062 (and the corresponding message). 

If there really is something special about the NDB case, make sure that message is returned regardless of the USING HASH clause in the CREATE statement. (Unless of course there is something different to repory)

If there really is something special about the NDB case, make sure the message reflects that. 
Don't mention unique constraints in the error message - it's not about constraints, it's about unique indexes. The other way around - getting an error about a unique index when violating a constraint does make sense, because unique constraints 'happen' to be implemented using unique indexes. 
Don't mention writing to a table: the underlying implementation might implement the syntax by writing to a table, but certainly not the table for which the index is being created.

updated to Cluster

sorry - I said "index constraint".  I meant: unique index.

Thank you for a bug report. With 5.1.12-BK (ChangeSet@1.2286, 2006-08-29 16:39:09+03:00) I've got even server crash:

-bash-3.00$ ./mysql-test-run.pl --with-ndbcluster bug21881
Logging: ./mysql-test-run.pl --with-ndbcluster bug21881
Using binlog format 'stmt'
Using ndbcluster when necessary, mysqld supports it
Setting mysqld to support SSL connections
Binaries are debug compiled
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Using NDBCLUSTER_PORT       = 9310
Using NDBCLUSTER_PORT_SLAVE = 9311
Using IM_PORT               = 9312
Using IM_MYSQLD1_PORT       = 9313
Using IM_MYSQLD2_PORT       = 9314
Killing Possible Leftover Processes
Removing Stale Files
Installing Master Database
Installing Master Database
Installing Master Cluster
Stopping All Servers
=======================================================
Starting Tests in the 'main' suite

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

bug21881                       [ fail ]

Errors are (from /users/vkravchuk/mysql-5.1/mysql-test/var/log/mysqltest-time) :

mysqltest: At line 12: query 'create unique index utt on t1 (id) USING HASH' fai
led: 2013: Lost connection to MySQL server during query
(the last lines may be the most important ones)
Result from queries before failure can be found in r/bug21881.log

Aborting: bug21881 failed in default mode. To continue, re-run with '--force'.
Stopping All Servers
-bash-3.00$ cat var/log/master.err
CURRENT_TEST: bug21881
...
060829 21:10:39 [Note] NDB Binlog: logging ./test/t1
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=1048576
read_buffer_size=131072
max_used_connections=4
max_connections=100
threads_connected=4
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 39420
K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Writing a core file
-bash-3.00$ cat t/bug21881.test
bug21881.test
-bash-3.00$ cat t/bug21881.test
-- source include/have_ndb.inc
-- source include/have_multi_ndb.inc
-- source include/not_embedded.inc

connect (con1,localhost,root,,test);
connection con1;

create table t1 (id int not null) engine = ndb;

insert into t1 values (1),(1);

create unique index utt on t1 (id) USING HASH;

This test was performed on our:

-bash-3.00$ uname -a
Linux ndbsup-1.mysql.com 2.6.9-22.0.1.ELsmp #1 SMP Tue Oct 18 18:39:02 EDT 2005
x86_64 x86_64 x86_64 GNU/Linux

Valeriy, 

it might be that the crash you are experiencing is the same I encountered when I submitted this:

http://bugs.mysql.com/bug.php?id=21873