Ivan, ----- Original Message ----- From: ""Ivan Latysh"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, June 17, 2004 7:44 PM Subject: Dying query ....
> Hello! > > I am running: > Server version 4.1.2-alpha-max-log > On linux RedHat. > > When I execute a simple select, it's dying, with no error message > reported or so, the query is simply dying together with the server. > After query is died entire server is hang on. I can't even shut it down. > Query shows as *** DEAD *** in mysql processlist. > > Here is a query: > select contest_id, question_id, answer_id, answer, > count(distinct(answer_id)) as count from user_answer > group by question_id, answer_id, answer > order by question_id, answer_id > > The table is: > CREATE TABLE `user_answer` ( > `user_id` int(11) NOT NULL default '0', > `contest_id` int(11) NOT NULL default '0', > `question_id` int(11) NOT NULL default '0', > `answer_id` int(11) NOT NULL default '0', > `answer` char(255) NOT NULL default '', > `date` datetime NOT NULL default '0000-00-00 00:00:00', > KEY `user_id` (`user_id`), > KEY `contest_id` (`contest_id`), > KEY `question_id` (`question_id`), > KEY `answer_id` (`answer_id`), > KEY `date` (`date`), > CONSTRAINT `0_3488` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`), > CONSTRAINT `0_3489` FOREIGN KEY (`contest_id`) REFERENCES `contest` > (`id`), > CONSTRAINT `0_3490` FOREIGN KEY (`question_id`) REFERENCES `question` > (`id`), > CONSTRAINT `0_3491` FOREIGN KEY (`answer_id`) REFERENCES `answer` (`id`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > A real table has 52000 records. > > I tested it on a 4.0.1 version - the result is same. > > Any idead what is wrong with it ? please run CHECK TABLE on that table. Does it report corruption or does mysqld print anything to the .err log? I tested your query with a small data set on 4.1.3, and it ran fine. > -- > With best regards, > Ivan Latysh. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ [EMAIL PROTECTED]:~/mysql-4.1/client> ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.3-beta-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table user(id int not null primary key) type=innodb; Query OK, 0 rows affected, 1 warning (0.16 sec) mysql> create table contest(id int not null primary key) type=innodb; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> create table question(id int not null primary key) type=innodb; Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> create table answer(id int not null primary key) type=innodb; Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> insert into user values (10); Query OK, 1 row affected (0.02 sec) mysql> insert into contest values (10); Query OK, 1 row affected (0.00 sec) mysql> insert into question values (10); Query OK, 1 row affected (0.00 sec) mysql> insert into answer values (10); Query OK, 1 row affected (0.00 sec) mysql> mysql> mysql> mysql> mysql> CREATE TABLE `user_answer` ( -> `user_id` int(11) NOT NULL default '0', -> `contest_id` int(11) NOT NULL default '0', -> `question_id` int(11) NOT NULL default '0', -> `answer_id` int(11) NOT NULL default '0', -> `answer` char(255) NOT NULL default '', -> `date` datetime NOT NULL default '0000-00-00 00:00:00', -> KEY `user_id` (`user_id`), -> KEY `contest_id` (`contest_id`), -> KEY `question_id` (`question_id`), -> KEY `answer_id` (`answer_id`), -> KEY `date` (`date`), -> CONSTRAINT `0_3488` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`), -> CONSTRAINT `0_3489` FOREIGN KEY (`contest_id`) REFERENCES `contest` -> (`id`), -> CONSTRAINT `0_3490` FOREIGN KEY (`question_id`) REFERENCES `question` -> (`id`), -> CONSTRAINT `0_3491` FOREIGN KEY (`answer_id`) REFERENCES `answer` (`id` ) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.11 sec) mysql> insert into user_answer values (1, 1, 1, 1, 'kukkuuu', '2004-06-18'); ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint f ails mysql> insert into user_answer values (10, 10, 10, 10, 'kukkuuu', '2004-06-18'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (10, 10, 10, 10, 'kukkuuu', '2004-06-18'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (10, 10, 10, 10, 'kukkuuu', '2004-06-18'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (10, 10, 10, 10, 'kukkuuu', '2004-06-18'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (10, 10, 10, 10, 'kukkuuu', '2004-06-18'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (10, 10, 10, 10, 'kukkuuu', '2004-06-18'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (10, 10, 10, 10, 'kukkuuu', '2004-06-18'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (10, 10, 10, 10, 'kukkuuu', '2004-06-18'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (10, 10, 10, 10, 'kukkuuu', '2004-06-18'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (10, 10, 10, 10, 'kukkuuu', '2004-06-18'); Query OK, 1 row affected (0.00 sec) mysql> select contest_id, question_id, answer_id, answer, -> count(distinct(answer_id)) as count from user_answer -> group by question_id, answer_id, answer -> order by question_id, answer_id -> ; +------------+-------------+-----------+---------+-------+ | contest_id | question_id | answer_id | answer | count | +------------+-------------+-----------+---------+-------+ | 10 | 10 | 10 | kukkuuu | 1 | +------------+-------------+-----------+---------+-------+ 1 row in set (0.05 sec) mysql> insert into user values (20); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (20, 10, 10, 10, 'kukkuuuluuruu', '2004-06 -18'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (20, 10, 10, 10, 'kukkuuuluuruu', '2004-06 -18'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (20, 10, 10, 10, 'kukkuuuluuruu', '2004-06 -18'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (20, 10, 10, 10, 'kukkuuuluuruu', '2004-06 -18'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_answer values (20, 10, 10, 10, 'kukkuuuluuruu', '2004-06 -18'); Query OK, 1 row affected (0.00 sec) mysql> mysql> mysql> mysql> select contest_id, question_id, answer_id, answer, -> count(distinct(answer_id)) as count from user_answer -> group by question_id, answer_id, answer -> order by question_id, answer_id -> ; +------------+-------------+-----------+---------------+-------+ | contest_id | question_id | answer_id | answer | count | +------------+-------------+-----------+---------------+-------+ | 10 | 10 | 10 | kukkuuu | 1 | | 10 | 10 | 10 | kukkuuuluuruu | 1 | +------------+-------------+-----------+---------------+-------+ 2 rows in set (0.00 sec) mysql> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
