MySQL Bugs: #13191: Getting "#HY000 Can't find record in..." on and INSERT

Bug #13191	Getting "#HY000 Can't find record in..." on and INSERT
Submitted:	14 Sep 2005 17:41	Modified:	4 May 2007 18:53
Reporter:	Mark Modrall	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.12, 4.1.14-nt,5.0,5.1	OS:	Linux (Linux, Windows)
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
I have a program that has 3 tables.  I'm trying to use Innodb to get the foreign key/on delete cascade functionality, but I'm getting a Can't find record in... error trying to do an *insert* into the parent table.  That doesn't seem to make much sense.

Curious points:
1) I'm using Mysql connector.net 1.0.4.  Since I'm doing many repeated insertions, I Prepared() the statement and added all the named parameters.  In the for loop, I manipulate the values of those parameters with Cmd.Parameters[name].Value = val; (don't know if that would do this).

2) I have an ON DUPLICATE KEY UPDATE clause, but at the time of error, I find the key value doesn't yet exist in the db.

3) When I try the insert query from the MySql Query Browser after the error, it succeeded.

4) I've run the program twice, and it bombs on the same value.  It's a long value (232 characters out of the 250 defined in the index definition), but at the time of error there are already 84 entries in the db longer than that.

I saw that there were some bugs fixed in more recent releases around this error but all the bug reports seemed to be about SELECTs and clustering implications.  I'm not clustering and it seemed odd to get this error on an INSERT.

How to repeat:
Given the large amount of data involved, it's not practical to send up the whole ball of wax.  I may have to try and whittle it down to a smaller test case, but here are the schemas and queries:

CREATE TABLE IF NOT EXISTS l50_qry (
	qid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	query TEXT NOT NULL, UNIQUE (query(250)),
	alias BOOL NOT NULL DEFAULT false,
	normalized BOOL NOT NULL, 
	nature BOOL NOT NULL
) ENGINE=INNODB CHARACTER SET UTF8 COLLATE utf8_bin;

CREATE TABLE IF NOT EXISTS l50_stat (
	qid INT UNSIGNED, FOREIGN KEY (qid) REFERENCES l50_qry(qid) ON DELETE CASCADE,
	cip INT NOT NULL,
	date SMALLINT NOT NULL,
	count INT NOT NULL
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS l50_roll (
	qid INT UNSIGNED, FOREIGN KEY (qid) REFERENCES l50_qry(qid) ON DELETE CASCADE,
	pid INT UNSIGNED, FOREIGN KEY (pid) REFERENCES l50_qry(qid) ON DELETE CASCADE,
	UNIQUE (qid,pid)
) ENGINE=INNODB;

The INSERT that's failing is:
INSERT INTO l50_qry (query, alias, normalized, nature) 
	VALUES (?query, ?al, ?norm, ?nat) ON DUPLICATE KEY UPDATE 
	qid=LAST_INSERT_ID(qid), alias=?al || alias, normalized=?norm || normalized, nature = ?nat || nature;

(basically, insert new value; if there's a key collision, set LAST_INSERT_ID to the existing qid and OR in all of the input booleans to the existing record  When I did the query in the MySql Query Browser after the error, obviously I didn't have the parameterization working in my favor there, so the boolean ORing wasn't there).

Suggested fix:
don't have one

I tried to repeat these using mysql command line on latest 4.1.15-BK build:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE IF NOT EXISTS l50_qry (
    ->  qid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> query TEXT NOT NULL, UNIQUE (query(250)),
    -> alias BOOL NOT NULL DEFAULT false,
    -> normalized BOOL NOT NULL,
    -> nature BOOL NOT NULL
    -> ) ENGINE=INNODB CHARACTER SET UTF8 COLLATE utf8_bin;
Query OK, 0 rows affected (0,05 sec)

mysql> CREATE TABLE IF NOT EXISTS l50_stat (
    -> qid INT UNSIGNED, FOREIGN KEY (qid) REFERENCES l50_qry(qid) ON DELETE CASCADE,
    -> cip INT NOT NULL,
    -> date SMALLINT NOT NULL,
    -> count INT NOT NULL
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0,05 sec)

mysql> CREATE TABLE IF NOT EXISTS l50_roll (
    ->  qid INT UNSIGNED, FOREIGN KEY (qid) REFERENCES l50_qry(qid) ON DELETE CASCADE,
    -> pid INT UNSIGNED, FOREIGN KEY (pid) REFERENCES l50_qry(qid) ON DELETE CASCADE,
    -> UNIQUE (qid,pid)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0,08 sec)

mysql> INSERT INTO l50_qry (query, alias, normalized, nature)
    -> VALUES ('test', 1, 1, 1) ON DUPLICATE KEY UPDATE
    -> qid=LAST_INSERT_ID(qid), alias='test' || alias, normalized= 1 || normalized,
    -> nature = 1 || nature;
Query OK, 1 row affected (0,03 sec)

mysql> INSERT INTO l50_qry (query, alias, normalized, nature)  VALUES ('test', 1
, 1, 1) ON DUPLICATE KEY UPDATE  qid=LAST_INSERT_ID(qid), alias='test' || alias,
 normalized= 1 || normalized, nature = 1 || nature;
Query OK, 2 rows affected (0,00 sec)

So, I just performed 2 same insert statements. This gave me the following result:

mysql> select * from l50_qry;
+-----+-------+-------+------------+--------+
| qid | query | alias | normalized | nature |
+-----+-------+-------+------------+--------+
|   1 | test  |     1 |          1 |      1 |
+-----+-------+-------+------------+--------+
1 row in set (0,00 sec)

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

No "Can't find record" error messages... So, please, point out where is the problem from your point of view and what should I change in my test case to repeat the problem you described.

Sorry it took me so long to respond.  I didn't have time to create a reproducible case until now.  It seems to be an inappropriate error message responding to strings that get truncated by the UNIQUE() index.  When you try to insert 2 strings that have the same first 250 characters but differ afterwards, it blows up with this incorrect message.

In the repro case, I still create the dependent tables, but I do 2 inserts into the parent and boom.

	MySqlConnection conn = new MySqlConnection ("<cstring>" );
	conn.Open();
	MySqlCommand createCmd = conn.CreateCommand(),
		innodbBug = conn.CreateCommand();
			
string c1 = "CREATE TABLE IF NOT EXISTS l50_qry (" +
	" qid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, " +
	" query TEXT NOT NULL, UNIQUE (query(250)), " +
	" alias BOOL NOT NULL DEFAULT false, " +
	" normalized BOOL NOT NULL, " +
	" nature BOOL NOT NULL " +
") ENGINE=INNODB CHARACTER SET UTF8 COLLATE utf8_bin";

string c2 = "CREATE TABLE IF NOT EXISTS l50_stat (" +
	" qid INT UNSIGNED, FOREIGN KEY (qid) REFERENCES l50_qry(qid) ON DELETE CASCADE, " +
	" cip INT NOT NULL, " +
	" date SMALLINT NOT NULL, " +
	" count INT NOT NULL " +
") ENGINE=INNODB";

string c3 = "CREATE TABLE IF NOT EXISTS l50_roll (" +
	" qid INT UNSIGNED, FOREIGN KEY (qid) REFERENCES l50_qry(qid) ON DELETE CASCADE, " +
	" pid INT UNSIGNED, FOREIGN KEY (pid) REFERENCES l50_qry(qid) ON DELETE CASCADE, " +
	" UNIQUE (qid,pid) " +
") ENGINE=INNODB" ;

	createCmd.CommandText = c1;
	createCmd.ExecuteNonQuery ();

	createCmd.CommandText = c2;
	createCmd.ExecuteNonQuery ();

	createCmd.CommandText = c3;
	createCmd.ExecuteNonQuery ();
		
string ins = "INSERT INTO l50_qry (query, alias, normalized, nature) " +
	" VALUES (?query, ?al, ?norm, ?nat) ON DUPLICATE KEY UPDATE " +
	" qid=LAST_INSERT_ID(qid), alias=?al || alias, normalized=?norm || " +
	" normalized, nature = ?nat || nature" ;

	innodbBug.CommandText = ins;
	innodbBug.Parameters.Add (new MySqlParameter("?query", ""));
	innodbBug.Parameters.Add (new MySqlParameter("?al", false));
	innodbBug.Parameters.Add (new MySqlParameter("?norm", false));
	innodbBug.Parameters.Add (new MySqlParameter("?nat", false));
		
	StreamReader input = File.OpenText ("D:\\input.txt");
	for (int i = 0; ; i++)
	{
		string qin = input.ReadLine();
		if (qin == null) break;
		innodbBug.Parameters["query"].Value = qin;
		innodbBug.Parameters["norm"].Value = true;
		innodbBug.Parameters["nat"].Value = true;
		innodbBug.ExecuteNonQuery();
	}

/////////////////////////////////////////////////////////////////
input.txt contains
/////////////////////////////////////////////////////////////////
http://www.metadirect.net/keyword/errredirect.cfm?v=1.3,http://www.metadirect.net/keyword/...
http://www.metadirect.net/keyword/errredirect.cfm?v=1.3,http://www.metadirect.net/keyword/...

Looks like I was able to repeat the behaviour you described on latest released 4.1.14-nt (without any Java, using simple mysql client, prepared statements and your data):

mysql> CREATE TABLE IF NOT EXISTS l50_qry (
    ->  qid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ->  query TEXT NOT NULL, UNIQUE (query(250)),
    ->  alias BOOL NOT NULL DEFAULT false,
    ->  normalized BOOL NOT NULL,
    ->  nature BOOL NOT NULL
    -> ) ENGINE=INNODB CHARACTER SET UTF8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.79 sec)

mysql> CREATE TABLE IF NOT EXISTS l50_stat (
    ->  qid INT UNSIGNED, FOREIGN KEY (qid) REFERENCES l50_qry(qid) ON DELETE CASCADE,
    ->  cip INT NOT NULL,
    ->  date SMALLINT NOT NULL,
    ->  count INT NOT NULL
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.38 sec)

mysql> CREATE TABLE IF NOT EXISTS l50_roll (
    ->  qid INT UNSIGNED, FOREIGN KEY (qid) REFERENCES l50_qry(qid) ON DELETE CASCADE,
    ->  pid INT UNSIGNED, FOREIGN KEY (pid) REFERENCES l50_qry(qid) ON DELETE CASCADE,
    ->  UNIQUE (qid,pid)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.14 sec)

mysql> prepare stmt from 'INSERT INTO l50_qry (query, alias, normalized, nature) VALUES (?, ?, ?, ?)
    '> ON DUPLICATE KEY UPDATE qid=LAST_INSERT_ID(qid), alias=? || alias,
    '> normalized=? || normalized, nature = ? || nature';
Query OK, 0 rows affected (0.07 sec)
Statement prepared

mysql> set @al=false;
Query OK, 0 rows affected (0.02 sec)

mysql> set @norm=true;
Query OK, 0 rows affected (0.01 sec)

mysql> set @nat=true;
Query OK, 0 rows affected (0.00 sec)

mysql> set @query='http://www.metadirect.net/keyword/errredirect.cfm?v=1.3,http://www.metadirect.ne
    '> t/keyword/errredirect.cfm?v=1.3,http://www.metadirect.net/keyword/errredirect.cf
    '> m?v=1.3,http://www.metadirect.net/keyword/errredirect.cfm?v=1.3,http://www.metad
    '> irect.net/keyword/errredirect.cfm?v=1.3,http://www.metadirect.net/keyword/errred
    '> irect.cfm?v=1.3,http://www.metadirect.net/keyword/errredirect.cfm?v=1.3,http://w
    '> ww.metadirect.net/keyword/errredirect.cfm?v=1.3,http://www.metadirect.net/keywor
    '> d/errredirect.cfm?v=';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @query, @al, @norm, @nat, @al, @norm, @nat;
Query OK, 1 row affected (0.08 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.03 sec)

mysql> set @query='http://www.metadirect.net/keyword/errredirect.cfm?v=1.3,http://www.metadirect.ne
    '> t/keyword/errredirect.cfm?v=1.3,http://www.metadirect.net/keyword/errredirect.cf
    '> m?v=1.3,http://www.metadirect.net/keyword/errredirect.cfm?v=1.3,http://www.metad
    '> irect.net/keyword/errredirect.cfm?v=1.3,http://www.metadirect.net/keyword/errred
    '> irect.cfm?v=1.3,res://d:winntsystem32shdoclc.dll/navcancl.htm';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @query, @al, @norm, @nat, @al, @norm, @nat;
ERROR 1032 (HY000): Can't find record in 'l50_qry'

So, if second value being inserted has the same indexed 250 bytes and is different in the rest, we have such a bug. The reason look obvious - we can not check constraint using index, because it does not support values of such a length, and it will not be correct to truncate them. Possibly, it is a documentation request only...

This bug affects InnoDB tables of UTF-8 charset with unique TEXT keys of fixed key length N, when we are trying to insert keys of length longer than N with common prefixes of length N.
4.1, 5.0 and 5.1 version are affected by this bug.
 
Minimized test case is: 
 
CREATE TABLE IF NOT EXISTS t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1) ENGINE=INNODB CHARACTER SET UTF8;
INSERT INTO t1 (c1) VALUES ('1a');
INSERT INTO t1 (c1) VALUES ('1a') ON DUPLICATE KEY UPDATE cnt=cnt+1;
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
 
Test case log for 5.0.38:
 
mysql> SELECT VERSION();
+--------------+ 
| VERSION()    | 
+--------------+ 
| 5.0.38-debug |  
+--------------+ 
1 row in set (0.00 sec) 
 
mysql> CREATE TABLE IF NOT EXISTS t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
 ENGINE=INNODB CHARACTER SET UTF8;
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO t1 (c1) VALUES ('1a');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 (c1) VALUES ('1a') ON DUPLICATE KEY UPDATE cnt=cnt+1;
ERROR 1032 (HY000): Can't find record in 't1'
mysql> INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
ERROR 1032 (HY000): Can't find record in 't1'

Non-UTF-8 tables and MyISAM tables are not affected.

Unique keys with underlying CHARACTER fields are affected too, VARCHAR fields are not.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25564

ChangeSet@1.2637, 2007-04-27 02:24:11+05:00, gshchepa@gshchepa.loc +5 -0
  Fixed bug #13191.
  INSERT...ON DUPLICATE KEY UPDATE may cause error 1032: 
  "Can't find record in ..." if we are inserting into
  InnoDB table where unique index of limited key length has
  underlying UTF-8 string field of larger length.
  
  This occurs because INSERT...ON DUPLICATE  uses trivial copying algorithm
  of key parts data for index search ignoring difference between the number
  of key bytes and the number of key characters.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25672

ChangeSet@1.2637, 2007-04-28 18:49:14+05:00, gshchepa@gshchepa.loc +5 -0
  Fixed bug #13191.
  INSERT...ON DUPLICATE KEY UPDATE may cause error 1032: 
  "Can't find record in ..." if we are inserting into
  InnoDB table unique index of partial key with
  underlying UTF-8 string field.
  
  This error occurs because INSERT...ON DUPLICATE uses insufficient
  algorithm to copy string fields of variable char size for index search.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25684

ChangeSet@1.2637, 2007-04-29 03:12:05+05:00, gshchepa@gshchepa.loc +5 -0
  Fixed bug #13191.
  INSERT...ON DUPLICATE KEY UPDATE may cause error 1032: 
  "Can't find record in ..." if we are inserting into
  InnoDB table unique index of partial key with
  underlying UTF-8 string field.
  
  This error occurs because INSERT...ON DUPLICATE uses a wrong
  procedure to copy string fields of multi-byte character sets
  for index search.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25686

ChangeSet@1.2636, 2007-04-29 04:16:17+05:00, gshchepa@gshchepa.loc +5 -0
  Fixed bug #13191.
  INSERT...ON DUPLICATE KEY UPDATE may cause error 1032: 
  "Can't find record in ..." if we are inserting into
  InnoDB table unique index of partial key with
  underlying UTF-8 string field.
  
  This error occurs because INSERT...ON DUPLICATE uses a wrong
  procedure to copy string fields of multi-byte character sets
  for index search.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25695

ChangeSet@1.2640, 2007-04-29 11:56:23+05:00, gshchepa@gshchepa.loc +1 -0
  Patch to eliminate compilation errors under VC after bug #13191 fix.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25697

ChangeSet@1.2449, 2007-04-29 12:56:46+05:00, gshchepa@gshchepa.loc +1 -0
  Patch to eliminate compilation errors under VC after bug #13191 fix.

Pushed into 4.1.23

Pushed into 5.0.42

Pushed into 5.1.18-beta

Noted in 4.1.23, 5.0.42, 5.1.18 changelogs.

INSERT...ON DUPLICATE KEY UPDATE could cause Error 1032: Can't find
record in ... for inserts into an InnoDB table unique index using key
column prefixes with an underlying utf8 string column.