MySQL Bugs: #22161: Duplicate key error with non-unique index after alter

Bug #22161	Duplicate key error with non-unique index after alter
Submitted:	9 Sep 2006 9:16	Modified:	4 Nov 2006 17:24
Reporter:	Georg Richter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	5.2	OS:	Linux (Linux 32-bit)
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
CREATE TABLE with a text column, INSERT long string, ALTER column type a few times, then try to CREATE INDEX. The error will be
ERROR 1022 (23000): Can't write; duplicate key in table '#sql-304c_1'
But there's only one row, and the index isn't unique.

How to repeat:
mysql> create table tj1 (s1 text) engine=jstar;
Query OK, 0 rows affected (0.14 sec)

mysql> insert into tj1 values (repeat('a',16384));
Query OK, 1 row affected (0.00 sec)

mysql> alter table tj1 modify column s1 varchar(30000);
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> alter table tj1 modify column s1 text;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> create index itj1 on tj1 (s1);
ERROR 1073 (42000): BLOB column 's1' can't be used in key specification with the used table type
mysql> alter table tj1 modify column s1 varchar(30000);
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> create index itj1 on tj1 (s1);
ERROR 1022 (23000): Can't write; duplicate key in table '#sql-304c_1'

Test case is falcon_bug_185.test

Falcon is returning an error internally called TRUNCATION_ERROR (-9) in Value::getString().  The buffer to put the string in is 2500 which is the maximum key length that Falcon allows.  This was recently increased from 2000 in order to bring the internal Falcon index limit in phase with the external index key limit.
See changeset 1.2273.55.60 in file IndexKey.h.

The test case tries to create an index on a varchar(30,000) field using a record that is 16384 bytes long.  I think the internal error is correct, and that this ALTER statement should fail.  

So the question is what error should it get.  There is a catch() in StorageTable::insert in which the DuplicateKey error code is assumed.  It should translate the internal error to an external error here.  But what should it be???

Bug #22161 is a problem of error translation.  The test tries to create an index on a text field.   Instead of getting an appropriate error message, a Duplicate key error was returned.  This set of changes allows the appropriate error message to get returned.  
  
The internal falcon SQLError is TRUNCATION_ERROR=-9 (SQLException.h). I added a new StorageError called StorageErrorTruncation=-11 (StorageTableShare.h).  This is now translated in ha_falcon.cpp NfsStorageTable::error() to the generic handler error HA_ERR_TO_BIG_ROW=139 (my_base.h).  This error code in turn would normally get translated to a MYSQLD error in handler.cc, handler::print_error(), but this particular error is not handled specifically.  Instead, it defaults to ER_GET_ERRMSG=1296 and prints out the text of the error message that was generated way back where the original SQLError happened.  
  
This seems to be informative enough for our purposes, so I changed the test case result file to reflect this error and the test case itself to expect it.

Also, the current Falcon engine is returning error 1073 instead of 1170 when you try to create an index on a blob or text field.  So I also changed falcon_bug_185.result to expect this actual error.  If this is deemed an inappropriate error, we can reopen the bug and take another look.

Works now as expected. Tested on Linux 32-bit change set 1.2324, 2006-10-17.

I think that the error message is a bit misleading.

[13:27] root@test>CREATE INDEX itj1 ON tj1 (s1);
ERROR 1296 (HY000): Got error 139 'string truncation into 2500 bytes from "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' from Falcon

There was no string truncation:
[14:14] root@test>select character_length(s1) from tj1;
+----------------------+
| character_length(s1) |
+----------------------+
|                16384 |
+----------------------+
1 row in set (0.00 sec)

Regards, Hakan

Works on Linux but fails on Pushbuild.

Regards, Hakan

Sorry!

falcon_bug_185.test works on Linux and on Pushbuild. 

Closed the bug report.

Regards, Hakan