Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

Links

XODOX
Impressum

#1: Simple query returns inconsistent results when using "=" operator

Posted on 2008-11-28 22:13:51 by 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)

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

Report this message

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

Posted on 2008-11-28 22:28:18 by Valeriy Kravchuk

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

Report this message

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

Posted on 2008-11-28 23:56:36 by Thomas Klettke

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

Report this message

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

Posted on 2008-11-29 09:02:36 by Valeriy Kravchuk

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

Report this message

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

Posted on 2008-11-29 10:57:41 by Carol Stone

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 <thomas@klettke.us>:

> 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

Report this message

#6: Re: Simple query returns inconsistent results when using

Posted on 2008-11-29 14:02:44 by Thomas Klettke

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

Report this message

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

Posted on 2008-11-29 14:10:58 by Thomas Klettke

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 <thomas@klettke.us>:
>
> > 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

Report this message

#8: Re: Simple query returns inconsistent results when using

Posted on 2008-11-29 14:51:58 by Thomas Klettke

> 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

Report this message