| Bug #5129 | Query execution fails. | ||
|---|---|---|---|
| Submitted: | 20 Aug 2004 15:48 | Modified: | 22 Oct 2004 17:22 |
| Reporter: | Igor Zinkovsky | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1.3 | OS: | Linux (Linux Mandrake 10.0 Community) |
| Assigned to: | Michael Widenius | CPU Architecture: | Any |
[20 Aug 2004 18:00]
Hartmut Holzgraefe
Can you please add some sample data or at least the CREATE TABLE statements for your database so that we can actually execute the query for testing? A trace file would also be helpfull, seehttp://dev.mysql.com/doc/mysql/en/Making_trace_files.html
[20 Aug 2004 22:50]
Igor Zinkovsky
Here are CREATE TABLE statements:
CREATE TABLE `stat_traf` (
`id` int(10) unsigned NOT NULL auto_increment,
`date` date default NULL,
`domain_id` int(10) unsigned NOT NULL default '0',
`hit` int(10) unsigned NOT NULL default '0',
`googlebot` int(10) unsigned NOT NULL default '0',
`yahoo` int(10) unsigned NOT NULL default '0',
`msn` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `date` (`date`),
KEY `domain_id` (`domain_id`),
CONSTRAINT `se/stat_traf_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `domain` (`id`)
) TYPE=InnoDB DEFAULT CHARSET=cp1251
CREATE TABLE `domain` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(150) NOT NULL default '',
`server` varchar(150) NOT NULL default '',
`registrar` varchar(150) NOT NULL default '',
`login` varchar(150) NOT NULL default '',
`password` varchar(50) NOT NULL default '',
`reg_date` date default NULL,
`reg_end_date` date default NULL,
`type` int(10) unsigned NOT NULL default '0',
`note` text,
`ready` enum('0','1') default '0',
`ip` varchar(15) default NULL,
`pr_type` enum('dmoz','expired') default NULL,
`engine` enum('0','1') default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name_2` (`name`),
KEY `type` (`type`),
KEY `reg_date` (`reg_date`),
KEY `reg_end_date` (`reg_end_date`),
KEY `name` (`name`,`server`,`registrar`),
KEY `engine` (`engine`),
CONSTRAINT `0_742` FOREIGN KEY (`type`) REFERENCES `domain_type` (`id`)
) TYPE=InnoDB DEFAULT CHARSET=cp1251
CREATE TABLE `domain_type` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=InnoDB DEFAULT CHARSET=cp1251
[20 Aug 2004 23:24]
Matthew Lord
Dear User,
Is this the full query?
SELECT
@did:=domain_id,
(SELECT name FROM domain WHERE id = @did) AS name,
(SELECT pr_type FROM domain WHERE id = @did) AS prt,
sum(hit) AS hit,
sum(googlebot) AS googlebot,
sum(yahoo) AS yahoo,
sum(hit) AS hit,
sum(googlebot) AS googlebot,
sum(yahoo) AS yahoo,
sum(msn) AS msnbot,
(
(SELECT SUM(hit) FROM stat_traf WHERE domain_id=@did AND date =
SUBDATE(
CURDATE(), INTERVAL 1 DAY)) / (SELECT SUM(hit) FROM stat_traf WHERE
domain_id=@did AND date = SUBDATE(CURDATE(), INTERVAL 2 DAY))
) AS k1,
(
(SELECT SUM(hit) FROM stat_traf WHERE domain_id=@did AND date =
SUBDATE(
CURDATE(), INTERVAL 1 DAY)) / (SELECT SUM(hit)/7 from stat_traf WHERE
domain_id=
@did AND date <= SUBDATE(CURDATE(), INTERVAL 1 DAY) AND date >
SUBDATE(CURDATE()
, INTERVAL 8 DAY))
) AS k2
FROM stat_traf
WHERE date LIKE "2004-07-%" AND domain_id IN
('NULL','285','305','309','333','33
5','336','337','338','341','342','343','345','346','347','348','349','350','353'
,'371','373','377','378','379','381','394','718','719','720','721','723','724','
725','728','729','730','731','732','733','734','736','737','738','739','740')
This query does not cause a crash for me but rather:
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP
columns is illegal if there is no GROUP BY clause
Best Regards
[21 Aug 2004 23:02]
Igor Zinkovsky
Sorry, here is a complete query:
SELECT
@did:=domain_id,
(SELECT name FROM domain WHERE id = @did) AS name,
(SELECT pr_type FROM domain WHERE id = @did) AS prt,
sum(hit) AS hit,
sum(googlebot) AS googlebot,
sum(yahoo) AS yahoo,
sum(msn) AS msnbot,
(
(SELECT SUM(hit) FROM stat_traf WHERE domain_id=@did AND date =
SUBDATE(CURDATE(), INTERVAL 1 DAY)) / (SELECT SUM(hit) FROM stat_traf WHERE
domain_id=@did AND date = SUBDATE(CURDATE(), INTERVAL 2 DAY))
) AS k1,
(
(SELECT SUM(hit) FROM stat_traf WHERE domain_id=@did AND date =
SUBDATE(CURDATE(), INTERVAL 1 DAY)) / (SELECT SUM(hit)/7 from stat_traf
WHERE domain_id=@did AND date <= SUBDATE(CURDATE(), INTERVAL 1 DAY) AND date
> SUBDATE(CURDATE(), INTERVAL 8 DAY))
) AS k2
FROM stat_traf
WHERE date = SUBDATE(CURDATE(), INTERVAL 1 DAY) AND domain_id IN
('NULL','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','1
6','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31
','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46'
,'47','48','49','50','51','52','53','54','55','56','57','58','59','60','61',
'62','63','64','65','66','67','68','70','71','72','73','74','75','76','77','
78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','9
3','94','95','96','97','98','99','100','101','102','103','104','105','106','
107','108','109','110','111','112','113','114','115','116','117','118','119'
,'120','121','122','123','124','125','126','127','128','129','130','131','13
2','133','134','135','136','137','138','139','140','141','142','143','144','
145','146','147','148','149','150','151','152','153','154','155','156','157'
,'159','160','161','162','163','164','165','166','167','168','169','170','17
1','172','173','174','175','176','177','178','179','180','181','182','183','
184','185','186','187','188','189','190','191','192','193','194','195','196'
,'197','198','199','200','201','202','203','204','205','206','207','208','20
9','210','211','212','213','214','215','216','217','218','219','220','221','
222','223','224','225','226','227','228','229','230','232','233','234','235'
,'236','237','238','239','240','241','242','243','244','245','246','247','24
8','249','250','251','252','253','254','255','256','257','258','259','260','
261','262','263','264','265','266','267','268','270','271','272','273','274'
,'275','276','277','278','279','280','281','282','283','284','285','286','28
7','288','289','290','291','292','293','295','296','297','298','299','300','
301','302','303','304','305','306','307','308','309','310','311','312','313'
,'314','315','316','317','318','319','320','321','322','323','324','325','32
6','327','328','329','330','331','332','333','334','335','336','337','338','
339','340','351','353','354','355','356','357','358','359','360','361','362'
,'363','364','365','366','367','368','369','370','371','372','373','374','37
5','376','377','378','379','380','381','382','383','384','385','386','387','
388','389','390','391','392','393','394','395','396','397','398','399','401'
,'402','403','404','429','430','431','432','433','434','435','437','438','44
0','442','443','444','445','446','447','448','449','450','451','452','453','
454','455','456','457','458','459','460','461','510','511','512','513','514'
,'515','516','517','518','519','520','521','522','523','524','525','526','52
7','528','529','530','531','532','533','534','535','536','537','538','539','
540','541','542','543','544','545','546','547','548','549','550','551','552'
,'553','554','555','556','557','558','559','560','561','562','563','564','56
5','566','567','568','569','570','571','572','573','574','575','576','577','
579','601','602','603','604','605','606','607','608','609','610','674','675'
,'676','677','678','679','680','681','682','683','778','341','342','343','34
4','345','346','347','348','349','350','352','400','416','417','418','419','
420','421','422','423','424','425','436','439','462','463','464','465','466'
,'467','468','469','470','472','473','474','475','476','477','478','479','48
0','481','482','483','484','485','486','487','488','489','490','491','492','
493','494','495','496','497','498','499','500','501','502','503','504','505'
,'506','507','508','578','580','581','582','583','584','585','586','587','58
8','590','591','592','593','594','595','596','597','598','599','600','638','
639','640','641','642','643','644','645','646','647','648','649','650','651'
,'652','653','654','655','656','657','658','659','660','661','662','663','66
4','665','666','667','668','684','685','686','687','688','689','690','691','
692','693','694','695','696','697','698','699','700','701','702','703','704'
,'705','706','707','708','709','710','711','712','713','714','715','716','71
7','718','719','720','721','722','723','724','725','726','727','728','729','
730','731','732','733','734','735','736','737','738','739','740','741','742'
,'743','744','745','746','747','748','749','750','751','757','758','759','76
0','761','762','763','764','765','766','767','768','769','770','771','772','
773','774','775','776','777','779','780','781','782','783','784','785','786'
,'787','788')
GROUP BY domain_id
ORDER BY prt DESC
[23 Aug 2004 20:55]
Matthew Lord
Dear User, I was unable to repeat the crash using 4.1.3-beta on x86 linux (booty 2.4.21 #12 SMP) nor on 4.1.3b-beta on windows 2000. I also have no data in these tables. Can you try your query on an empty copy of the table and see if it still crashes? Can you also resolve the stack trace that you got using the directions on the URL provided at the end of the stack trace? That may point us in the right direction as well. If it doesn't crash for you either with an empty table, would it be possible for you to provide an example record that I can use to build good test data? Best Regards
[30 Aug 2004 11:22]
Igor Zinkovsky
I rolled back first server to ver 4.1.0. On this version query executes without crashes.
I installed 4.1.3 on another (second) server (Linux Mandrake 10.0 Community) and created new database (using CREATE statments, I've posted) with data. No crashes while executing query.
After that I repaired all of my databases on first server using ANALYZE, CHECK, REPAIR statements and upgrade it (using rpm -U) to Ver 4.1.3-beta-standard. While reparing one error in another database was found. There were no crashes for nearly 5 days, but now it crashes again.
Now on first server I'm using Ver 4.1.3-beta-standard for pc-linux on i686 (Official MySQL RPM)
Now I've repaired just `se` database on first server. It didn't resolve problem.
Version: '4.1.3-beta-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=4194304
read_buffer_size=131072
max_used_connections=27
max_connections=100
threads_connected=18
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 836095 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x6f61fa08
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfc5e768, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x808bd47
0x82c79d8
0x8081892
0x80f8248
0x80f818d
0x80f22a2
0x80f3d3d
0x80c7cd9
0x80c6fa8
0x80c6ce4
0x80bd870
0x8075685
0x80726af
0x8072ff3
0x8056230
0x804e531
0x83445e1
0x80cc1f5
0x80c884d
0x80c7029
0x80c6ce4
0x80bcbb8
0x80bdd38
0x80badd9
0x809b2da
0x809fa11
0x8099f42
0x80998b4
0x8098fb7
0x82c518c
0x82f672a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x878a7c8 = SELECT
@did:=domain_id,
(SELECT name FROM domain WHERE id = @did) AS name,
(SELECT pr_type FROM domain WHERE id = @did) AS prt,
sum(hit) AS hit,
sum(googlebot) AS googlebot,
sum(yahoo) AS yahoo,
sum(msn) AS msnbot,
(
(SELECT SUM(hit) FROM stat_traf WHERE domain_id=@did AND date = SUBDATE(CURDATE(), INTERVAL 1 DAY)) / (SELECT SUM(hit) FROM stat_traf WHERE domain_id=@did AND date = SUBDATE(CURDATE(), INTERVAL 2 DAY))
) AS k1,
(
(SELECT SUM(hit) FROM stat_traf WHERE domain_id=@did AND date = SUBDATE(CURDATE(), INTERVAL 1 DAY)) / (SELECT SUM(hit)/7 from stat_traf WHERE domain_id=@did AND date <= SUBDATE(CURDATE(), INTERVAL 1 DAY) AND date > SUBDATE(CURDATE(), INTERVAL 8 DAY))
) AS k2
FROM stat_traf
WHERE date = SUBDATE(CURDATE(), INTERVAL 1 DAY) AND domain_id IN ('NULL','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','5
thd->thread_id=7579
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
[22 Oct 2004 17:22]
Michael Widenius
Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php If you can provide more information, feel free to add it to this bug and change the status back to 'Open'. Thank you for your interest in MySQL.
Description: It worked with 4.1.1 and 4.1.2. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=4194304 read_buffer_size=131072 max_used_connections=4 max_connections=100 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 836095 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x6f61cdf8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfe3e768, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x808bd47 0x82c79d8 0x8081892 0x80f8248 0x80f818d 0x80f22a2 0x80f3d3d 0x80f8248 0x80f818d 0x80f22a2 0x80f3d3d 0x80c7cd9 0x80c6fa8 0x80c6ce4 0x80bd870 0x8075685 0x80726af 0x8072ff3 0x8056230 0x804e531 0x83445e1 0x80cc1f5 0x80c8bbd 0x80c7029 0x80c6ce4 0x80bcbb8 0x80bdd38 0x80badd9 0x809b2da 0x809fa11 0x8099f42 0x80998b4 0x8098fb7 0x82c518c 0x82f672a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instru ctions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x8677f40 = SELECT @did:=domain_id, (SELECT name FROM domain WHERE id = @did) AS name, (SELECT pr_type FROM domain WHERE id = @did) AS prt, sum(hit) AS hit, sum(googlebot) AS googlebot, sum(yahoo) AS yahoo, sum(hit) AS hit, sum(googlebot) AS googlebot, sum(yahoo) AS yahoo, sum(msn) AS msnbot, ( (SELECT SUM(hit) FROM stat_traf WHERE domain_id=@did AND date = SUBDATE( CURDATE(), INTERVAL 1 DAY)) / (SELECT SUM(hit) FROM stat_traf WHERE domain_id=@d id AND date = SUBDATE(CURDATE(), INTERVAL 2 DAY)) ) AS k1, ( (SELECT SUM(hit) FROM stat_traf WHERE domain_id=@did AND date = SUBDATE( CURDATE(), INTERVAL 1 DAY)) / (SELECT SUM(hit)/7 from stat_traf WHERE domain_id= @did AND date <= SUBDATE(CURDATE(), INTERVAL 1 DAY) AND date > SUBDATE(CURDATE() , INTERVAL 8 DAY)) ) AS k2 FROM stat_traf WHERE date LIKE "2004-07-%" AND domain_id IN ('NULL','285','305','309','333','33 5','336','337','338','341','342','343','345','346','347','348','349','350','353' ,'371','373','377','378','379','381','394','718','719','720','721','723','724',' 725','728','729','730','731','732','733','734','736','737','738','739','740' thd->thread_id=91 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instru ctions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x8677f40 = SELECT @did:=domain_id, (SELECT name FROM domain WHERE id = @did) AS name, (SELECT pr_type FROM domain WHERE id = @did) AS prt, sum(hit) AS hit, sum(googlebot) AS googlebot, sum(yahoo) AS yahoo, sum(msn) AS msnbot, ( (SELECT SUM(hit) FROM stat_traf WHERE domain_id=@did AND date = SUBDATE( CURDATE(), INTERVAL 1 DAY)) / (SELECT SUM(hit) FROM stat_traf WHERE domain_id=@d id AND date = SUBDATE(CURDATE(), INTERVAL 2 DAY)) ) AS k1, ( (SELECT SUM(hit) FROM stat_traf WHERE domain_id=@did AND date = SUBDATE( CURDATE(), INTERVAL 1 DAY)) / (SELECT SUM(hit)/7 from stat_traf WHERE domain_id= @did AND date <= SUBDATE(CURDATE(), INTERVAL 1 DAY) AND date > SUBDATE(CURDATE() , INTERVAL 8 DAY)) ) AS k2 FROM stat_traf WHERE date LIKE "2004-07-%" AND domain_id IN ('NULL','285','305','309','333','33 5','336','337','338','341','342','343','345','346','347','348','349','350','353' ,'371','373','377','378','379','381','394','718','719','720','721','723','724',' 725','728','729','730','731','732','733','734','736','737','738','739','740' thd->thread_id=91 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. How to repeat: Execute query like this: SELECT @did:=domain_id, (SELECT name FROM domain WHERE id = @did) AS name, (SELECT pr_type FROM domain WHERE id = @did) AS prt, sum(hit) AS hit, sum(googlebot) AS googlebot, sum(yahoo) AS yahoo, sum(msn) AS msnbot, ( (SELECT SUM(hit) FROM stat_traf WHERE domain_id=@did AND date = SUBDATE( CURDATE(), INTERVAL 1 DAY)) / (SELECT SUM(hit) FROM stat_traf WHERE domain_id=@d id AND date = SUBDATE(CURDATE(), INTERVAL 2 DAY)) ) AS k1, ( (SELECT SUM(hit) FROM stat_traf WHERE domain_id=@did AND date = SUBDATE( CURDATE(), INTERVAL 1 DAY)) / (SELECT SUM(hit)/7 from stat_traf WHERE domain_id= @did AND date <= SUBDATE(CURDATE(), INTERVAL 1 DAY) AND date > SUBDATE(CURDATE() , INTERVAL 8 DAY)) ) AS k2 FROM stat_traf WHERE date LIKE "2004-07-%" AND domain_id IN ('NULL','285','305','309','333','33 5','336','337','338','341','342','343','345','346','347','348','349','350','353' ,'371','373','377','378','379','381','394','718','719','720','721','723','724',' 725','728','729','730','731','732','733','734','736','737','738','739','740')