Simple query returns inconsistent results when using "=" operator

Scenario: Moved from MySQL 4.1.9-0 (Fedora Core 2) to MySQL 5.0.45-7.el5
(CentOS 5.2)

Noticed that basic queries yield inconsistent results:

Query 1:

mysql> select contact_id,system_message from contact_comments where
contact_id=168676;
+------------+----------------+
| contact_id | system_message |
+------------+----------------+
| 168676 | no |
| 168676 | yes |
| 168676 | no |
| 168676 | yes |
| 168676 | no |
| 168676 | yes |
+------------+----------------+
6 rows in set (0.00 sec)

Query 2:
mysql> select contact_id,system_message from contact_comments where
contact_id=168676 and system_message='no';
Empty set (0.00 sec)

I would expect that this should have resulted in 3 rows.

Query 3:
mysql> select contact_id,system_message from contact_comments where
contact_id=168676 and system_message='yes';
Empty set (0.00 sec)

Should have been 3 rows as well.

Query 4:
mysql> select contact_id,system_message from contact_comments where
contact_id=168676 and system_message<>'no';
+------------+----------------+
| contact_id | system_message |
+------------+----------------+
| 168676 | yes |
| 168676 | yes |
| 168676 | yes |
+------------+----------------+
3 rows in set (0.00 sec)

Now it gets interesting.

Query 5:
mysql> select contact_id,system_message from contact_comments where
contact_id=168676 and system_message<>'yes';
+------------+----------------+
| contact_id | system_message |
+------------+----------------+
| 168676 | no |
| 168676 | no |
| 168676 | no |
+------------+----------------+
3 rows in set (0.00 sec)

Query 6:
mysql> select contact_id,system_message from contact_comments where
contact_id like 168676 and system_message='no';
+------------+----------------+
| contact_id | system_message |
+------------+----------------+
| 168676 | no |
| 168676 | no |
| 168676 | no |
+------------+----------------+
3 rows in set (0.00 sec)

Replaced "=" with "like": That worked - but it found all those records
with the "=" operator in query 1 above.

I'm lost - what am I missing here?

For your reference - this is what the table looks like:

mysql> describe contact_comments;
+-------------------+------------------+------+-----+------- --------------+-------+
| Field | Type | Null | Key | Default
| Extra |
+-------------------+------------------+------+-----+------- --------------+-------+
| contact_id | int(11) | NO | PRI | 0
| |
| username | varchar(25) | NO | PRI | 0
| |
| comment | text | NO | PRI |
| |
| system_message | enum('yes','no') | NO | MUL | no
| |
| time | datetime | NO | PRI | 0000-00-00
00:00:00 | |
| talked_to_contact | enum('yes','no') | NO | | no
| |
| status | int(3) | YES | | NULL
| |
| hidden | enum('yes','no') | NO | | no
| |
+-------------------+------------------+------+-----+------- --------------+-------+
8 rows in set (0.00 sec)


Thanks for checking.
Thomas



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Simple query returns inconsistent results when using "=" operator

Hi Thomas,

> Scenario: Moved from MySQL 4.1.9-0 (Fedora Core 2) to MySQL 5.0.45-7.el5
> (CentOS 5.2)
>
> Noticed that basic queries yield inconsistent results:

Had you dumped your data in 4.1.9 and re-loaded in 5.0.x? If not, had
you tried to run mysql_upgrade script?

> Query 1:
>
> mysql> select contact_id,system_message from contact_comments where
> contact_id=168676;
> +------------+----------------+
> | contact_id | system_message |
> +------------+----------------+
> | 168676 | no |
> | 168676 | yes |
> | 168676 | no |
> | 168676 | yes |
> | 168676 | no |
> | 168676 | yes |
> +------------+----------------+
> 6 rows in set (0.00 sec)
>
> Query 2:
> mysql> select contact_id,system_message from contact_comments where
> contact_id=168676 and system_message='no';
> Empty set (0.00 sec)
>
> I would expect that this should have resulted in 3 rows.
>
> Query 3:
> mysql> select contact_id,system_message from contact_comments where
> contact_id=168676 and system_message='yes';
> Empty set (0.00 sec)
>
> Should have been 3 rows as well.
>
> Query 4:
> mysql> select contact_id,system_message from contact_comments where
> contact_id=168676 and system_message<>'no';
> +------------+----------------+
> | contact_id | system_message |
> +------------+----------------+
> | 168676 | yes |
> | 168676 | yes |
> | 168676 | yes |
> +------------+----------------+
> 3 rows in set (0.00 sec)
>
> Now it gets interesting.
>
> Query 5:
> mysql> select contact_id,system_message from contact_comments where
> contact_id=168676 and system_message<>'yes';
> +------------+----------------+
> | contact_id | system_message |
> +------------+----------------+
> | 168676 | no |
> | 168676 | no |
> | 168676 | no |
> +------------+----------------+
> 3 rows in set (0.00 sec)
>
> Query 6:
> mysql> select contact_id,system_message from contact_comments where
> contact_id like 168676 and system_message='no';
> +------------+----------------+
> | contact_id | system_message |
> +------------+----------------+
> | 168676 | no |
> | 168676 | no |
> | 168676 | no |
> +------------+----------------+
> 3 rows in set (0.00 sec)
>
> Replaced "=" with "like": That worked - but it found all those records
> with the "=" operator in query 1 above.
>
> I'm lost - what am I missing here?
>
> For your reference - this is what the table looks like:
>
> mysql> describe contact_comments;
> +-------------------+------------------+------+-----+------- --------------+-------+
> | Field | Type | Null | Key | Default
> | Extra |
> +-------------------+------------------+------+-----+------- --------------+-------+
> | contact_id | int(11) | NO | PRI | 0
> | |
> | username | varchar(25) | NO | PRI | 0
> | |
> | comment | text | NO | PRI |
> | |
> | system_message | enum('yes','no') | NO | MUL | no
> | |
> | time | datetime | NO | PRI | 0000-00-00
> 00:00:00 | |
> | talked_to_contact | enum('yes','no') | NO | | no
> | |
> | status | int(3) | YES | | NULL
> | |
> | hidden | enum('yes','no') | NO | | no
> | |
> +-------------------+------------------+------+-----+------- --------------+-------+
> 8 rows in set (0.00 sec)

Please, send the results of

SHOW CREATE TABLE contact_comments\G
SHOW TABLE STATUS LIKE 'contact_comments';
CHECK TABLE contact_comments FOR UPGRADE;

Best regards,
--
Valeriy Kravchuk, MySQL Principal Support Engineer
Sun Microsystems, Inc.
Kiev, Ukraine, www.sun.com/mysql

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Simple query returns inconsistent results when using "="

On Fri, 2008-11-28 at 23:28 +0200, Valeriy Kravchuk wrote:
> Hi Thomas,
>
> > Scenario: Moved from MySQL 4.1.9-0 (Fedora Core 2) to MySQL 5.0.45-7.el5
> > (CentOS 5.2)
> >
> > Noticed that basic queries yield inconsistent results:
>
> Had you dumped your data in 4.1.9 and re-loaded in 5.0.x? If not, had
> you tried to run mysql_upgrade script?
>
Hi Valeriy,
I've dumped the data on the old server (mysqldump) and then loaded them
on the new. It is a different machine. Haven't tried the mysql_upgrade
script yet.

> Please, send the results of
>
See below:

> SHOW CREATE TABLE contact_comments\G
mysql> SHOW CREATE TABLE contact_comments\G;
*************************** 1. row ***************************
Table: contact_comments
Create Table: CREATE TABLE `contact_comments` (
`contact_id` int(11) NOT NULL default '0',
`username` varchar(25) NOT NULL default '0',
`comment` text NOT NULL,
`system_message` enum('yes','no') NOT NULL default 'no',
`time` datetime NOT NULL default '0000-00-00 00:00:00',
`talked_to_contact` enum('yes','no') NOT NULL default 'no',
`status` int(3) default NULL,
`hidden` enum('yes','no') NOT NULL default 'no',
PRIMARY KEY (`username`,`comment`(30),`time`,`contact_id`),
KEY `contact_id`
(`contact_id`,`username`,`time`,`talked_to_contact`,`comment `(30)),
KEY `username` (`username`,`comment`(30),`talked_to_contact`,`time`),
KEY `contact_id_2` (`contact_id`),
KEY `system_message` (`system_message`),
KEY `time` (`time`),
CONSTRAINT `contact_comments_1` FOREIGN KEY (`contact_id`) REFERENCES
`contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specified


> SHOW TABLE STATUS LIKE 'contact_comments';
mysql> SHOW TABLE STATUS LIKE 'contact_comments';
+------------------+--------+---------+------------+------+- ---------------+-------------+-----------------+------------ --+-----------+----------------+---------------------+------ -------+------------+-------------------+----------+-------- --------+--------------------------------------------------- -------------------------------+
| Name | Engine | Version | Row_format | Rows |
Avg_row_length | Data_length | Max_data_length | Index_length |
Data_free | Auto_increment | Create_time | Update_time |
Check_time | Collation | Checksum | Create_options | Comment
|
+------------------+--------+---------+------------+------+- ---------------+-------------+-----------------+------------ --+-----------+----------------+---------------------+------ -------+------------+-------------------+----------+-------- --------+--------------------------------------------------- -------------------------------+
| contact_comments | InnoDB | 10 | Compact | 201 |
244 | 49152 | 0 | 81920 | 0 |
NULL | 2008-11-28 14:55:40 | NULL | NULL |
latin1_swedish_ci | NULL | | InnoDB free: 667648 kB;
(`contact_id`) REFER `asis_crm/contacts`(`id`) ON DELETE |
+------------------+--------+---------+------------+------+- ---------------+-------------+-----------------+------------ --+-----------+----------------+---------------------+------ -------+------------+-------------------+----------+-------- --------+--------------------------------------------------- -------------------------------+
1 row in set (0.00 sec)


> CHECK TABLE contact_comments FOR UPGRADE;
>
mysql> CHECK TABLE contact_comments FOR UPGRADE;
+---------------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------------+-------+----------+----------+
| asis_crm.contact_comments | check | status | OK |
+---------------------------+-------+----------+----------+
1 row in set (0.00 sec)


Thanks for your help!
Thomas

> Best regards,
> --
> Valeriy Kravchuk, MySQL Principal Support Engineer
> Sun Microsystems, Inc.
> Kiev, Ukraine, www.sun.com/mysql
>


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Simple query returns inconsistent results when using "=" operator

Hi Thomas,

Your table is small, properly upgraded. The system_message column is not
indexed. I see no obvious reasons for the problem described, and I was
not able to find similar known bug fast.

So, please, check with a newer version, 5.0.67, and if you will get the
same results, report a bug at http://bugs.mysql.com. Upload dump of the
table to the report (as private file, if you want).

Best regards,
--
Valeriy Kravchuk, MySQL Principal Support Engineer
Sun Microsystems, Inc.
Kiev, Ukraine, www.sun.com/mysql

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Simple query returns inconsistent results when using "="

Thomas,

It looks to me like it's behaving as though some portion of your =20
upgrade has introduced trailing spaces or non-printable characters. It =20
might be useful to whoever ends up looking at this, if you were to =20
report the behavior of your query using left and trim (or rtrim) =20
respectively.

If one or both of those brings back your expected result set, again it =20
might be helpful to check the output data file to see if it was =20
introduced when outputting the data to the disk or on the latter input.

OTOH, if you just send them your files, the programmer will surely do =20
the same check. This might be most useful if you're in a terrible =20
hurry and are looking for a work-around.

-carol stone



Quoting Thomas Klettke :

> Scenario: Moved from MySQL 4.1.9-0 (Fedora Core 2) to MySQL 5.0.45-7.el5
> (CentOS 5.2)
>
> Noticed that basic queries yield inconsistent results:
>
> Query 1:
>
> mysql> select contact_id,system_message from contact_comments where
> contact_id=3D168676;
> +------------+----------------+
> | contact_id | system_message |
> +------------+----------------+
> | 168676 | no |
> | 168676 | yes |
> | 168676 | no |
> | 168676 | yes |
> | 168676 | no |
> | 168676 | yes |
> +------------+----------------+
> 6 rows in set (0.00 sec)
>
> Query 2:
> mysql> select contact_id,system_message from contact_comments where
> contact_id=3D168676 and system_message=3D'no';
> Empty set (0.00 sec)
>
> I would expect that this should have resulted in 3 rows.
>
> Query 3:
> mysql> select contact_id,system_message from contact_comments where
> contact_id=3D168676 and system_message=3D'yes';
> Empty set (0.00 sec)


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

Re: Simple query returns inconsistent results when using

On Sat, 2008-11-29 at 10:02 +0200, Valeriy Kravchuk wrote:
> Hi Thomas,
>
> Your table is small, properly upgraded. The system_message column is not
> indexed. I see no obvious reasons for the problem described, and I was
> not able to find similar known bug fast.
>
> So, please, check with a newer version, 5.0.67, and if you will get the
> same results, report a bug at http://bugs.mysql.com. Upload dump of the
> table to the report (as private file, if you want).

Hi Valeriy,
I've downloaded and installed 5.1.30-community - still get the same bug.
Before reporting a bug I will try to dump/import the same DB on another
CentOS 5 machine - don't want to waste anyone's time unless I can
reproduce it here.
If you're interested in taking a look yourself I could give you ssh
access to the server - please contact me off-list.

Cheers,
Thomas


>
> Best regards,
> --
> Valeriy Kravchuk, MySQL Principal Support Engineer
> Sun Microsystems, Inc.
> Kiev, Ukraine, www.sun.com/mysql
>


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Simple query returns inconsistent results when using "="

On Sat, 2008-11-29 at 04:57 -0500, carol@carolstone.com wrote:
> Thomas,
>
> It looks to me like it's behaving as though some portion of your
> upgrade has introduced trailing spaces or non-printable characters. It
> might be useful to whoever ends up looking at this, if you were to
> report the behavior of your query using left and trim (or rtrim)
> respectively.

Hi Carol,
I've imported the original database structure from the old server, but
then - at least for this particular table - deleted all entries and
started with new records. The contact_id field is populated via
auto_increment - thus IMO ruling out any kind of extra characters.
Strangely enough I get correct matches on "contact_id" when I only use
this field for the query (see Query 1 from my original post).
>
> If one or both of those brings back your expected result set, again it
> might be helpful to check the output data file to see if it was
> introduced when outputting the data to the disk or on the latter input.
>
I've followed Valeriy's advice and upgraded to the latest version -
5.1.30-community MySQL Community Server (GPL) - apparently the bug is
not related to the version I had.

I'll dump/load the DB on another server - to see if I can reproduce the
result.

> OTOH, if you just send them your files, the programmer will surely do
> the same check. This might be most useful if you're in a terrible
> hurry and are looking for a work-around.
>
I could go further, and give the developer ssh access - this is not a
production server.

Thanks for your insights, I'll post any updates here.

Cheers,
Thomas

> -carol stone
>
>
>
> Quoting Thomas Klettke :
>
> > Scenario: Moved from MySQL 4.1.9-0 (Fedora Core 2) to MySQL 5.0.45-7.el5
> > (CentOS 5.2)
> >
> > Noticed that basic queries yield inconsistent results:
> >
> > Query 1:
> >
> > mysql> select contact_id,system_message from contact_comments where
> > contact_id=168676;
> > +------------+----------------+
> > | contact_id | system_message |
> > +------------+----------------+
> > | 168676 | no |
> > | 168676 | yes |
> > | 168676 | no |
> > | 168676 | yes |
> > | 168676 | no |
> > | 168676 | yes |
> > +------------+----------------+
> > 6 rows in set (0.00 sec)
> >
> > Query 2:
> > mysql> select contact_id,system_message from contact_comments where
> > contact_id=168676 and system_message='no';
> > Empty set (0.00 sec)
> >
> > I would expect that this should have resulted in 3 rows.
> >
> > Query 3:
> > mysql> select contact_id,system_message from contact_comments where
> > contact_id=168676 and system_message='yes';
> > Empty set (0.00 sec)
>
>


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Simple query returns inconsistent results when using

> Hi Valeriy,
> I've downloaded and installed 5.1.30-community - still get the same bug.
> Before reporting a bug I will try to dump/import the same DB on another
> CentOS 5 machine - don't want to waste anyone's time unless I can
> reproduce it here.
> If you're interested in taking a look yourself I could give you ssh
> access to the server - please contact me off-list.
>
Update:

Dumped/Loaded the DB to another server (CentOS 5.2, 5.0.45) - same
problem.

Another interesting observation:

Typed the query by, got results. Hit "arrow up" to repeat the same query
- no results:

mysql> select contact_id,system_message from contact_comments where
contact_id=168676 and system_message='no';
+------------+----------------+
| contact_id | system_message |
+------------+----------------+
| 168676 | no |
| 168676 | no |
+------------+----------------+
2 rows in set (0.00 sec)

mysql> select contact_id,system_message from contact_comments where
contact_id=168676 and system_message='no';
Empty set (0.00 sec)

mysql> select contact_id,system_message from contact_comments where
contact_id=168676 and system_message='no';
Empty set (0.00 sec)

Is there anything that caches queries that might be broken?

Cheers,
Thomas




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org