| Bug #26132 | Query failed | ||
|---|---|---|---|
| Submitted: | 7 Feb 2007 4:28 | Modified: | 15 Apr 2007 7:15 |
| Reporter: | Lim tienaik | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.1.14-beta | OS: | Linux (linux) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | GROUP BY, huge table, insert, SELECT | ||
[7 Feb 2007 7:01]
Lim tienaik
added operating system = linux
[7 Feb 2007 11:34]
Valeriy Kravchuk
Thank you for a problem report. Please, describe your hardware, send your my.cnf file content and the results of: df -k uname -a ulimit -a free Linux commands. Send the results of SHOW CREATE TABLE and SHOW TABLE STATUS for both tables used. I suspect this is just an out-of-memory issue.
[8 Feb 2007 2:04]
Lim tienaik
df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda8 10317828 3290004 6503708 34% /
/dev/sda1 1035660 43824 939228 5% /boot
/dev/sda14 1035660 117908 865144 12% /home
/dev/sda10 4127076 2520772 1396660 65% /opt/ora/u01
/dev/sda7 51605436 25164456 23819576 52% /opt/ora/u02
/dev/sda6 51605436 41543268 7440764 85% /opt/ora/u03
/dev/sda2 103210972 43874656 54093504 45% /opt/ora/u04
/dev/sda5 51605436 22047672 26936360 46% /opt/ora/u05
/dev/sda3 51605464 8604308 40379752 18% /opt/ora/u06
none 1547364 0 1547364 0% /dev/shm
/dev/sda13 1035660 33224 949828 4% /tmp
/dev/sda11 4127076 1683272 2234160 43% /usr
/dev/sda12 3099260 906856 2034972 31% /var
uname -a
Linux root 2.4.21-4.ELsmp #1 SMP Fri Oct 3 17:52:56 EDT 2003 i686 i686 i386 GNU/Linux
ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) 4
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 7168
virtual memory (kbytes, -v) unlimited
free
total used free shared buffers cached
Mem: 3094732 3076748 17984 0 22392 2292524
-/+ buffers/cache: 761832 2332900
Swap: 6289408 0 6289408
show table status
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
sales MyISAM 10 Dynamic 50433252 91 4636459556 0 54272 0 2007-02-05 09:27:59 2007-02-06 10:46:39 latin1_swedish_ci partitioned
sales_bycode3 MyISAM 10 Dynamic 10369547 55 579547764 2.81475E+14 209084416 0 2007-02-02 16:28:27 2007-02-07 19:22:33 2007-02-07 15:05:41 latin1_swedish_ci
CREATE TABLE `sales` (
`YR_NO` mediumint(9) NOT NULL DEFAULT '0',
`WEEK_NO` smallint(6) NOT NULL DEFAULT '0',
`WEEK_ID` int(11) NOT NULL DEFAULT '0',
`code4` varchar(6) NOT NULL DEFAULT '0',
`code8` varchar(4) NOT NULL DEFAULT '0',
`code1` char(2) DEFAULT '0',
`code2` char(2) DEFAULT '0',
`code3` char(2) DEFAULT '0',
`code5` char(2) NOT NULL DEFAULT '0',
`code6` char(2) NOT NULL DEFAULT '0',
`code7` char(2) NOT NULL DEFAULT '0',
`QTY` double(8,0) NOT NULL DEFAULT '0',
`AMT` double(10,2) NOT NULL DEFAULT '0.00',
`CUST` double(8,0) NOT NULL DEFAULT '0',
`base` double(10,2) NOT NULL DEFAULT '0.00',
`cnt` char(3) DEFAULT '0',
`FLA` char(3) DEFAULT NULL,
`DELV_code6` char(2) DEFAULT NULL,
`ZD` char(2) DEFAULT NULL,
`DMCD` char(2) DEFAULT NULL,
`MCD` varchar(5) DEFAULT NULL,
`PCD` char(3) DEFAULT NULL,
`BCD` varchar(5) DEFAULT NULL,
`CCD` char(3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH ( WEEK_ID) PARTITIONS 53 */;
CREATE TABLE `sales_bycode3` (
`WEEK_ID` int(11) NOT NULL DEFAULT '0',
`code4` varchar(6) NOT NULL DEFAULT '0',
`code1` char(2) DEFAULT '0',
`code2` char(2) DEFAULT '0',
`code3` char(2) DEFAULT '0',
`code5` char(2) NOT NULL DEFAULT '0',
`code6` char(2) NOT NULL DEFAULT '0',
`code7` char(2) NOT NULL DEFAULT '0',
`QTY` int(11) NOT NULL DEFAULT '0',
`AMT` double(10,2) NOT NULL DEFAULT '0.00',
`CUST` int(11) NOT NULL DEFAULT '0',
`base` double(10,2) NOT NULL DEFAULT '0.00',
`QG` double(10,2) NOT NULL DEFAULT '0.00',
`AG` double(10,2) NOT NULL DEFAULT '0.00',
`CG` double(10,2) NOT NULL DEFAULT '0.00',
`COSG` double(10,2) NOT NULL DEFAULT '0.00',
`PG` double(10,2) NOT NULL DEFAULT '0.00',
KEY `Search_key` (`WEEK_ID`,`code4`,`code7`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
[4 Mar 2007 18:21]
Valeriy Kravchuk
Please, try to repeat with a newerr version, 5.1.16, and, in case of the same problem, send the results of SHOW TABLE STATUS for both tables involved.
[4 Apr 2007 23:00]
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".
[12 Apr 2007 16:11]
Valeriy Kravchuk
Feedback is still needed.
[15 Apr 2007 7:15]
Valeriy Kravchuk
This is, likely, not a bug. You just have not enough free space in /tmp filesystem for GROUP BY: /dev/sda13 1035660 33224 949828 4% /tmp for the amound of data you have. Try to set TMPDIR to a directory into another filesystem, with much more free space.
Description: I get this message from the client [Server] ERROR 3: Error writing file '/tmp/MY6DgoOb' (Errcode: 28) This from the server 070207 12:06:14 [ERROR] /usr/sbin/mysqld: Sort aborted How to repeat: I performed the following query and it failed after 2 hours of processing. My dataset is about 37mil lines. INSERT INTO sales_bycode3(WEEK_ID, code4, code1, code2, code3,code7, code6, code5, SALES_QTY, SALES_AMT, SALES_CUST, COST_OF_SALES) select WEEK_ID, code4, code1, code2, code3,code7, code6, code5, sum(SALES_QTY) as SALES_QTY, sum(SALES_AMT) as SALES_AMT, sum(SALES_CUST) as SALES_QTY, sum(COST_OF_SALES) from sales where WEEK_ID >= 159 and WEEK_ID <= 215 group by WEEK_ID, code4, code1, code2, code3, code7, code6, code5;