MySQL Bugs: #21024: Federated Engine - mysql service die after do query with "order by"

Bug #21024	Federated Engine - mysql service die after do query with "order by"
Submitted:	13 Jul 2006 3:52	Modified:	25 Jul 2006 8:33
Reporter:	xlprox xlprox	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S1 (Critical)
Version:	5.0.22-max - innodb	OS:	Windows (Windows 2003 sp1)
Assigned to:		CPU Architecture:	Any

Description:
I am using mysql-max in windows 2003 service pack 1 (for federated engine)

If I executed query on federated engine table:
select * from `confignews` order by `date` desc
or
select * from `confignews` order by `date`

mysql service will DIE

the structure of table confignews:


CREATE TABLE `confignews` (
  `id` tinyint(2) NOT NULL auto_increment,
  `news` text NOT NULL,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://sss_admin:818283@192.168.0.4:3306/sss2_db/confignews';

-- 
-- Dumping data for table `confignews`
-- 

INSERT INTO `confignews` (`id`, `news`, `date`) VALUES (1, 'asd', '2006-05-24 10:58:44'),
(2, 'xxxxdfsf', '2006-05-24 10:59:27'),
(3, 'dddddddddddd', '2006-05-24 10:59:20'),
(4, 'eeeeeeee', '2006-05-24 10:59:22'),
(5, 'a', '2006-04-04 00:00:00'),
(6, 'FIFA  Wold cup 2006 OUTRIGHT  Bets Available Now, clik OUTRIGHT to place your bets,  Good Luck !!!...............Please see back OUTRIGHT Rules for your reference...................... Thank you', '2006-05-24 14:26:56'),
(7, 'b', '2006-04-02 00:00:00'),
(8, 'c', '2006-03-04 00:00:00');

How to repeat:
--

Suggested fix:
--

It because of TEXT.

After I change to varchar(255), It works.

Please fix it

New case found again.

CREATE TABLE `memo` (
  `id` int(4) NOT NULL auto_increment,
  `from` varchar(20) character set utf8 NOT NULL,
  `to` varchar(20) character set utf8 NOT NULL,
  `subject` varchar(50) character set utf8 NOT NULL,
  `body` varchar(255) character set utf8 NOT NULL,
  `read` tinyint(1) NOT NULL default '0',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  KEY `id` (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://sss_admin:818283@192.168.0.4:3306/sss2_db/memo';

mysql die if do query with "order by"

Thank you for a problem report. What version of MySQL server your original tables, pointed by FEDERATED, are working on? Please, send the SHOW CREATE TABLE results for them also.

Table structure from original is same (I do copy overall and only modify address to point to remote).

I simulate using 2 different server with same version 5.0.22 (mysql-max)

-------------------------

Then I tried again in same computer. One DB remote to different one.
Problem still same, frequently appear fatal error mysql-max. And at last mysql service will die.

Original table memo and news (as examples) are working fine.

Missing info:  I use innodb.

--------------------------------------

CREATE TABLE `memo` (
  `id` int(4) NOT NULL auto_increment,
  `from` varchar(20) character set utf8 NOT NULL,
  `to` varchar(20) character set utf8 NOT NULL,
  `subject` varchar(50) character set utf8 NOT NULL,
  `body` varchar(255) character set utf8 NOT NULL,
  `read` tinyint(1) NOT NULL default '0',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=56 ;

--

Thank you for a bug report. I was not able to repeat the behaviour described with 5.0.25-BK on Linux:

mysql> CREATE TABLE `confignews` (
    ->   `id` tinyint(2) NOT NULL auto_increment,
    ->   `news` text NOT NULL,
    ->   `date` datetime NOT NULL default '0000-00-00 00:00:00',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=FEDERATED DEFAULT CHARSET=latin1
    -> CONNECTION='mysql://root@127.0.0.1/test/confignews'
    -> ;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO `confignews` (`id`, `news`, `date`) VALUES (1, 'asd', '2006-
05-24 10:58:44'),
    -> (2, 'xxxxdfsf', '2006-05-24 10:59:27'),
    -> (3, 'dddddddddddd', '2006-05-24 10:59:20'),
    -> (4, 'eeeeeeee', '2006-05-24 10:59:22'),
    -> (5, 'a', '2006-04-04 00:00:00'),
    -> (6, 'FIFA  Wold cup 2006 OUTRIGHT  Bets Available Now, clik OUTRIGHT to
place your bets,  Good Luck !!!...............Please see back OUTRIGHT Rules fo
r your reference...................... Thank you', '2006-05-24 14:26:56'),
    -> (7, 'b', '2006-04-02 00:00:00'),
    -> (8, 'c', '2006-03-04 00:00:00');
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

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

So, please, wait for 5.0.24 to be released officially and check again.

5.0.24 should be released really soon. This month, I hope.