mysql 插入报错 100213 20:22:16 [ERROR] /usr/libexec/mysqld: Sort aborted

0 0

mysql 插入报错 100213 20:22:16 [ERROR] /usr/libexec/mysqld: Sort aborted15

我用的一台server进行插入报错 24G内存 4核CPU
程序抛出的异常为:java.sql.SQLException: Unexpected eof found when reading file '/home/tmp/MYvFQFtT' (Errcode: 0)
我查看mysql的日志，错误信息为:100221 10:03:53 [ERROR] /usr/libexec/mysqld: Sort aborted

对应的my.cnf配置如下:
character-set-server = utf8

key_buffer = 3072M
max_allowed_packet = 512M
table_cache = 1024
query_cache_size = 1024M
sort_buffer_size = 3072M
read_buffer_size = 1024M
read_rnd_buffer_size = 1024M
myisam_sort_buffer_size = 1024M
net_buffer_length = 128M
open_files_limit = 10240
tmp_table_size=1024M
max_tmp_tables=100
thread_stack =512M
join_buffer_size=1024M

max_connections = 250
max_user_connections = 200
wait_timeout = 259200
interactive_timeout= 259200
net_read_timeout = 500
net_write_timeout = 600

是不是和我的配置有关以前这种操作是不会报错的。最近修改了几个参数后就开始报错，但是我改回去依旧是同样的情况

问题补充：

hunkguo 写道

得看看程序中sql是怎么写的，单独执行试试，感觉跟mysql配置关系不大。

手动运行也是报错.SQL比较复杂:
insert into tmp_P_S_R
select MONTH_ID ,PRODUCT_CATEGORY_ID ,PRODUCT_MENTION_ID ,DRIVER_CATEGORY_ID ,DRIVER_MENTION_ID ,FORUM_ID ,avg(P_S_R) as P_S_R from
(
select B.MONTH_ID,T.PRODUCT_CATEGORY_ID,T.PRODUCT_MENTION_ID,T.DRIVER_CATEGORY_ID,T.DRIVER_MENTION_ID,B.FORUM_ID,sum(T.POSMENTIONS)/(sum(T.POSMENTIONS)+sum(T.NEGMENTIONS)) as P_S_R , B.POST_ID
from tmp_Daily_B_POST B inner join T_POST2TM_Product_Driver T on T.POST_ID=B.POST_ID group by B.MONTH_ID,T.PRODUCT_CATEGORY_ID,T.PRODUCT_MENTION_ID,T.DRIVER_CATEGORY_ID,T.DRIVER_MENTION_ID,B.FORUM_ID, B.POST_ID
) A
group by MONTH_ID ,PRODUCT_CATEGORY_ID ,PRODUCT_MENTION_ID ,DRIVER_CATEGORY_ID ,DRIVER_MENTION_ID ,FORUM_ID ;

问题补充：

hunkguo 写道

手动运行报啥错？看上去可没啥问题。
这样测试
先执行
select B.MONTH_ID,T.PRODUCT_CATEGORY_ID,T.PRODUCT_MENTION_ID,T.DRIVER_CATEGORY_ID,T.DRIVER_MENTION_ID,B.FORUM_ID,sum(T.POSMENTIONS)/(sum(T.POSMENTIONS)+sum(T.NEGMENTIONS)) as P_S_R , B.POST_ID
from tmp_Daily_B_POST B inner join T_POST2TM_Product_Driver T on T.POST_ID=B.POST_ID group by B.MONTH_ID,T.PRODUCT_CATEGORY_ID,T.PRODUCT_MENTION_ID,T.DRIVER_CATEGORY_ID,T.DRIVER_MENTION_ID,B.FORUM_ID, B.POST_ID

再执行扑面的select，看看哪部分的数据有问题。

Unexpected eof found when reading file '/home/tmp/MYvFQFtT' (Errcode: 0)
我单独执行就报这个错误
查看/var/log/mysqld.log 里面就是
Sort Aborted!

问题补充：

hunkguo 写道

我怀疑和sort_buffer_size有关应该是计算所用的内存超过实际内存所有Mysql才会关闭查询
我现在在做2个测试:
1.把中间结果写入临时表,然后尝试
2.修改mysql 配置文件将sort_buffer_size修改小