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:Microsoft Windows (Windows 2003 sp1)
Assigned to: CPU Architecture:Any

[13 Jul 2006 3:52] xlprox xlprox
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:
--
[13 Jul 2006 4:11] xlprox xlprox
It because of TEXT.

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

Please fix it
[13 Jul 2006 5:12] xlprox xlprox
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"
[13 Jul 2006 19:52] Valeriy Kravchuk
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.
[14 Jul 2006 1:33] xlprox xlprox
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.
[15 Jul 2006 17:07] xlprox xlprox
Original table memo and news (as examples) are working fine.
[16 Jul 2006 10:02] xlprox xlprox
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 ;
[16 Jul 2006 10:10] xlprox xlprox
--
[24 Jul 2006 16:35] Valeriy Kravchuk
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.
[25 Jul 2006 16:18] Valeriy Kravchuk
5.0.24 should be released really soon. This month, I hope.