Bug #6041 Select statements with 'not empty' sit at sorting result
Submitted: 12 Oct 2004 10:22 Modified: 12 Nov 2004 14:50
Reporter: Michael Furdyk Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:4.1.5-gamma-standard-log OS:Linux (Redhat 7.3)
Assigned to: CPU Architecture:Any

[12 Oct 2004 10:22] Michael Furdyk
Description:
After upgrading to 4.1.1, a number of queries with != '' operators in them, such as:

SELECT * FROM EVENTS WHERE EventName != '' ORDER BY EventName

Sit at 'Sorting Result' in process list, and hang there... but they slowly build up and overwhelm/crash the server.

- all tables are UTF8

Also, when this happens, mysql.server is not able to restart or stop the server -- I have to manually kill off the processes.

Pretty serious?

How to repeat:
Here's a sample field for one of the tables affected:

Query it with Where Name != '' ORDER BY Name

CREATE TABLE `Organizations` (
  `OrgID` int(5) NOT NULL auto_increment,
  `ParentID` int(11) NOT NULL default '0',
  `ChildType` int(11) NOT NULL default '0',
  `Name` varchar(85) NOT NULL default '',
  `URL` varchar(85) NOT NULL default '',
  `Address` varchar(85) NOT NULL default '',
  `Address2` varchar(85) default NULL,
  `City` varchar(12) default NULL,
  `StateProv` varchar(12) default NULL,
  `Zipcode` varchar(12) default NULL,
  `CountryID` int(3) NOT NULL default '0',
  `GlobalReach` int(1) NOT NULL default '0',
  `YouthLed` int(1) NOT NULL default '0',
  `flNYC` int(1) NOT NULL default '0',
  `flUN` int(1) NOT NULL default '0',
  `PartnerFlag` int(2) default NULL,
  `GYSDLevel` int(1) NOT NULL default '0',
  `Email` varchar(85) default NULL,
  `Phone` varchar(8) default NULL,
  `Fax` varchar(8) default NULL,
  `ImageName` varchar(8) default NULL,
  `Vision` text,
  `WhatDoTheyDo` text,
  `OppsForYouth` text,
  `Networks` text NOT NULL,
  `OrgHead` varchar(85) NOT NULL default '',
  `OrgStatusID` int(2) NOT NULL default '0',
  `Approved` int(1) NOT NULL default '0',
  `flGYANMember` tinyint(4) NOT NULL default '0',
  `Comments` text NOT NULL,
  PRIMARY KEY  (`OrgID`),
  UNIQUE KEY `OrgID` (`OrgID`),
  KEY `CountryID` (`CountryID`),
  KEY `Name` (`Name`),
  FULLTEXT KEY `Name_2` (`Name`,`WhatDoTheyDo`,`Vision`,`OppsForYouth`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 AUTO_INCREMENT=7894 ;

-- 
-- Dumping data for table `Organizations`
-- 

INSERT INTO `Organizations` VALUES (1, 2519, 2, 'Greenpeace Canada', 'http://www.greenpeacecanada.org/', '250 Dundas Street West, Suite 605', '', 'Toronto', 'Ontario', 'M5T 2Z5', 1, 0, 0, 0, 0, 0, 0, 'greenpeace.toronto@dialb.greenpeace.org', '', '', '1.gif', '', 'Greenpeace is an independent not-for-profit campaigning organization that uses nonviolent, creative confrontation to expose global environmental problems. We research solutions and alternatives that are essential to a green and peaceful future. Greenpeace''s goal is to ensure the ability of the Earth to nurture life in all its diversity. ', '', '', '', 1, 1, 1, '');
[12 Oct 2004 10:30] Michael Furdyk
I noticed a bunch of these types of files are being created in data...

    12 -rw-rw----    1 mysql    mysql       10058 Oct 12 04:56 #sql-181_6f.frm
  5348 -rw-rw----    1 mysql    mysql     5463048 Oct 12 04:56 #sql-181_6f.MYD
   116 -rw-rw----    1 mysql    mysql      112640 Oct 12 06:07 #sql-181_6f.MYI
  5348 -rw-r--r--    1 root     root      5463048 Oct 12 06:07 #sql-181_6f.TMD
    12 -rw-rw----    1 mysql    mysql       10058 Oct 12 05:02 #sql-272_28.frm
  5348 -rw-rw----    1 mysql    mysql     5463048 Oct 12 05:02 #sql-272_28.MYD
   116 -rw-rw----    1 mysql    mysql      112640 Oct 12 05:02 #sql-272_28.MYI
    12 -rw-rw----    1 mysql    mysql       10058 Oct 12 04:28 #sql-7da9_395.frm
  5348 -rw-rw----    1 mysql    mysql     5463048 Oct 12 04:28 #sql-7da9_395.MYD
   116 -rw-rw----    1 mysql    mysql      112640 Oct 12 04:28 #sql-7da9_395.MYI
    12 -rw-rw----    1 mysql    mysql       10058 Oct 12 04:37 #sql-7f35_51.frm
  5348 -rw-rw----    1 mysql    mysql     5463048 Oct 12 04:37 #sql-7f35_51.MYD
   116 -rw-rw----    1 mysql    mysql      112640 Oct 12 04:37 #sql-7f35_51.MYI
[12 Oct 2004 10:41] MySQL Verification Team
Hi,

Thank you for the report.
Do you get server hang with exactly the same test case?
Because it works fine for me.
If no, how big is table 'Organizations'?
[12 Oct 2004 11:12] MySQL Verification Team
These files are temporary tables that MySQL creates during execution some queries.
Check that you have enough free disk space.
[12 Oct 2004 11:23] Michael Furdyk
Yes, there's enough disk space.

With more investigation, mysql seems to be having this problem with a few tables it's incorrectly set as utf-8 - they are actually latin1. When the default charset was changed it seemed to ignore the latin1 tables latin1 origin and set them somehow as utf8?

Do you think this could be the problem? (not in logs) if so, how can I get mysql to properly re-identify the tables that are latin1?

ALTER TABLE `Events` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci ;

just seems to hang forever...
[12 Oct 2004 14:50] MySQL Verification Team
If you want to change just default character set, the above ALTER TABLE statement does it.
If you want to convert all data from utf8 to latin1, you should use
ALTER TABLE ... CONVERT TO CHARACTER SET ..

If you didn't specify explicitly character set for the table, table has the same character set as current database has.

You didn't answer me about size of the table "Organizations".
Tell me also, how many rows in the table that are satisfied Name!='' condition?
What is the output of EXPLAIN SELECT?
[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".