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

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/[email protected]