MySQL Bugs: #2660: Select fails on a indexed varchar field

Bug #2660	Select fails on a indexed varchar field
Submitted:	6 Feb 2004 2:56	Modified:	6 Feb 2004 13:21
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	3.23.58	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Excuse me for my poor english.

I have a table defined as follows:
CREATE TABLE `user_mail` (
  `email` varchar(80) NOT NULL default '',
  `nome` varchar(30) NOT NULL default '',
  `cognome` varchar(30) NOT NULL default '',
  `password` varchar(30) NOT NULL default '',
  `indirizzo` varchar(255) NOT NULL default '',
  `citta` varchar(255) NOT NULL default '',
  `provincia` varchar(255) NOT NULL default '',
  `nazione` varchar(255) NOT NULL default '',
  `settore_id` int(4) NOT NULL default '1',
  `ricevere_newsle` set('S','N') NOT NULL default 'N',
  `bacheca` set('S','N') NOT NULL default 'N',
  `data` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`email`)
) TYPE=MyISAM;

If I run a query like this:
SELECT * FROM `user_mail` WHERE `email`='a.bcdefg@wert.com'
it fails (no result).

Then if I try this query:
SELECT * FROM `user_mail` WHERE `email` like '%a.bcdefg@wert.com%'
It runs ok.

So I'm sure that the value is present in the table and I checked that there is no "white spaces" neither at the beginning nor at the end of the field.

Thanks

How to repeat:
If you try the same operations you can get the same bug.

I was unable to repeat this.  Can you create a full test case which displays this problem?  I did this:

mysql> show create table user_mail\G
*************************** 1. row ***************************
       Table: user_mail
Create Table: CREATE TABLE `user_mail` (   
  `email` varchar(80) NOT NULL default '', 
  `nome` varchar(30) NOT NULL default '',  
  `cognome` varchar(30) NOT NULL default '',
  `password` varchar(30) NOT NULL default '',
  `indirizzo` varchar(255) NOT NULL default '',
  `citta` varchar(255) NOT NULL default '',
  `provincia` varchar(255) NOT NULL default '',
  `nazione` varchar(255) NOT NULL default '',
  `settore_id` int(4) NOT NULL default '1',
  `ricevere_newsle` set('S','N') NOT NULL default 'N',
  `bacheca` set('S','N') NOT NULL default 'N',
  `data` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`email`)
) TYPE=MyISAM
1 row in set (0.00 sec)

mysql> load data local infile 'emails.txt' into table user_mail (email);
Query OK, 0 rows affected (0.00 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from user_mail where email like '%pies@bran.ramp%'\G
*************************** 1. row ***************************
          email: pies@bran.ramp
           nome: 
        cognome: 
       password: 
      indirizzo: 
          citta: 
      provincia: 
        nazione: 
     settore_id: 1
ricevere_newsle: N
        bacheca: N
           data: 0000-00-00
1 row in set (2.55 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 3.23.58   |
+-----------+
1 row in set (0.00 sec)

Tim

Oops.  I posted the wrong query.  I did this also:

mysql> select * from user_mail where email = 'pies@bran.ramp'\G
*************************** 1. row ***************************
          email: pies@bran.ramp
           nome: 
        cognome: 
       password: 
      indirizzo: 
          citta: 
      provincia: 
        nazione: 
     settore_id: 1
ricevere_newsle: N
        bacheca: N
           data: 0000-00-00
1 row in set (0.00 sec)

Tim

One more thing,

Can you please check your tables with myisamchk, to verify that there is no corruption?  That could cause problems like the one you describe, if the index isn't intact.

Thanks!

Tim

I checked my indexes. They're all ok.
Could you try with data like  'abd.bsg@jshs.com' ?
I've seen that the problem rises when there are two "points" in e-mail address.

Thanks in any case.

Guido

I updated one of the rows, and did this:

mysql> select * from user_mail where email='for.each@example.com'\G;
*************************** 1. row ***************************
          email: for.each@example.com
           nome:
        cognome:
       password:
      indirizzo:
          citta:
      provincia:
        nazione:
     settore_id: 1
ricevere_newsle: N
        bacheca: N
           data: 0000-00-00
1 row in set (0.01 sec)

I'm sorry, but I don't understand how this problem could occur
without breaking many things.  It is such a common type of
issue.

Is there any way you can trim down your table to a manageable
size and get a repeatable bug with the table that you could send
to us?

Also, can you test this with a MySQL 4.0 server?

I came across the same problem on MySQL 4.1.11-Debian_2woody1. Didn't discover the solution.

Hi.  Thanks for letting us know you've seen the problem.  What we really need is a way to repeat it.

If this happens regularly for you, and you are able to send us your actual table files, that would be really great.  So far we don't have enough information to repeat the problem, so I can't verify that it is actually a bug, and can't fix it.

You can upload large data files (zipped first) to:

-- Use .zip, .tar.gz, etc. as appropriate....
ftp://ftp.mysql.com/pub/mysql/upload/BUG2660-tables.zip

If you upload such an example, please post a comment here letting us know the name of the file, and the exact query you use to demonstrate this bug.

Thanks,

Timothy

Hi,

Simply backup your table.Then see the backup file, you can find each record ended with a \r character ie., carriage return, remove them and try again.

for more reference:

<a href="http://www.freshyellow.com">Classic</a>

http://weblinks.atshya.com
http://www.freshprlinks.com
http://www.freshyellow.com

today i encountered this bug using 5.1.39-8-log (MySQL Community Server (GPL), XtraDB 8, Revision 46) on a InnoDB table. Changing the colomn from varchar->char fixed it.

I tried to reproduce it with my backups, didn't work, sorry.

Thank you for your additional report.  Hopefully if this bug does still exist, someone will be able to reproduce it reliably so that it can be fixed.  Odd that it shows up so infrequently.