[mysqldumpslow 報錯] Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.

mysqldumpslow報錯：Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.

總結：是因為top數目太多了，mysqldumpslow遍曆不過來的緣故。

/usr/local/mysql/bin/mysqldumpslow -s -t 15 /root/db01-102-slow.log 

1，把r去掉試試，還是報錯，參數不識別。

[root@slave ]# /usr/local/mysql/bin/mysqldumpslow -s -t 15 /root/db01-102-slow.log Reading mysql slow query log from 15 /root/db01-102-slow.logCan't open 15: 沒有那個檔案或目錄 at /usr/local/mysql/bin/mysqldumpslow line 91.Count: 22  Time=9.61s (211s)  Lock=0.00s (0s)  Rows=1.0 (22), [dubbo]@2hosts  select count(*)  from coupon_lot  left join coupon   on coupon.coupon_lot_id = coupon_lot.coupon_lot_id  LEFT JOIN brand  ON brand.brand_id=coupon_lot.brand_id  LEFT JOIN product_category  on product_category.category_id=coupon_lot.category_idCount: 177  Time=1.40s (247s)  Lock=0.00s (0s)  Rows=1.0 (177), [dubbo]@2hosts  select count(*) from user_info  LEFT JOIN system_region a  ON province_id =a.region_id  LEFT JOIN system_region b  ON city_id = b.region_id  LEFT JOIN system_region c  ON district_id = c.region_idCount: 22  Time=13.20s (290s)  Lock=0.00s (0s)  Rows=10.0 (220), [dubbo]@2hosts  select   coupon_id, coupon_lot.coupon_lot_id, coupon_lot.remark,  coupon.code,  coupon_lot.coupon_type, coupon.state,  coupon_lot.ckey, coupon_lot.cvalue, coupon_lot.discount, coupon_lot.type, coupon_lot.channel,   coupon_lot.is_reusable, coupon_lot.start_datetime,  coupon_lot.end_datetime, coupon_lot.create_datetime, coupon.update_datetime ,coupon_lot.department,  coupon_lot.state as couponLotState, coupon_lot.create_operator,coupon_lot.coupon_category,  coupon_lot.pdt_code,brand.brand_name,product_category.category_name  from coupon_lot  left join coupon   on coupon.coupon_lot_id = coupon_lot.coupon_lot_id  LEFT JOIN brand  ON brand.brand_id=coupon_lot.brand_id  LEFT JOIN product_category  on product_category.category_id=coupon_lot.category_id  group by coupon_lot.coupon_lot_id  order by coupon_lot.create_datetime DESC  limit N,NCount: 8  Time=1.09s (8s)  Lock=0.00s (0s)  Rows=10.0 (80), [dubbo]@2hosts  select   return_sn,  relating_order_sn, return_from,  oi.order_out_sn ,  add_time , return_type,   return_pay, ui.usename as userName, return_consignee, return_goods_count ,  return_total_fee, total_paid,   return_desc, return_order_status,  return_pay_status ,  return_shipping_status, order_return.lock_operator, order_return.return_invoice_no  from order_return  LEFT JOIN   order_info oi  on order_return.relating_order_sn = oi.order_sn   left join user_info ui on oi.user_id = ui.user_id  left join order_payment on order_payment.order_sn = oi.order_sn  order by order_return.add_time DESC  limit N,NCount: 1  Time=1.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost  select sleep(N)Count: 1  Time=1.13s (1s)  Lock=0.00s (0s)  Rows=10.0 (10), [dubbo]@[xx]  select   return_sn,  relating_order_sn, return_from,  oi.order_out_sn ,  add_time , return_type,   return_pay, ui.usename as userName, return_consignee, return_goods_count ,  return_total_fee, total_paid,   return_desc, return_order_status,  return_pay_status ,  return_shipping_status, order_return.lock_operator, order_return.return_invoice_no  from order_return  LEFT JOIN   order_info oi  on order_return.relating_order_sn = oi.order_sn   left join user_info ui on oi.user_id = ui.user_id  left join order_payment on order_payment.order_sn = oi.order_sn  order by order_return.add_time DESC  , oi.order_sn   limit N,NCount: 5  Time=2.47s (12s)  Lock=0.00s (0s)  Rows=0.0 (0), [dubbo]@2hosts  select  user_id, usename, nickname, password, user_group_id, realname, gender, email, mobilephone,   birthday, province_id, city_id, district_id, marry, income, remarks, create_datetime,   active_state, verityEmail, verityPhone, credit, last_login, last_ip, is_locked, channel,   tag, union_partner_uuid, union_user_safekey, is_black, qq, weibo_id, verity_email_key,   verity_phone_key, verity_email_datetime, verity_phone_datetime, verity_question,   verity_answer, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channel  from user_info where mobilephone = 'S' and (channel is null or trim(channel) ='S')

2，查看下mysqldumpslow 使用參數，-s後面需要帶參數的，看了西夏at比較合適

[root@db-master-2 ~]# /usr/local/mysql/bin/mysqldumpslow  --helpUsage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are  --verbose    verbose  --debug      debug  --help       write this text to standard output  -v           verbose  -d           debug  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default                al: average lock time                ar: average rows sent                at: average query time                 c: count                 l: lock time                 r: rows sent                 t: query time    -r           reverse the sort order (largest last instead of first)  -t NUM       just show the top n queries  -a           don't abstract all numbers to N and strings to 'S'  -n NUM       abstract numbers with at least n digits within names  -g PATTERN   grep: only consider stmts that include this string  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),               default is '*', i.e. match all  -i NAME      name of server instance (if using mysql.server startup script)  -l           don't subtract lock time from total time

3，在mysqldumpslow的-s後面加入 at參數試試：  [root@slave ]# /usr/local/mysql/bin/mysqldumpslow -s at -t 15 /root/db01-102-slow.log 

[root@db-master-2 ~]# /usr/local/mysql/bin/mysqldumpslow -s at -t 15 /root/db01-102-slow.log Reading mysql slow query log from /root/db01-102-slow.logCount: 22  Time=13.20s (290s)  Lock=0.00s (0s)  Rows=10.0 (220), [dubbo]@2hosts  select   coupon_id, coupon_lot.coupon_lot_id, coupon_lot.remark,  coupon.code,  coupon_lot.coupon_type, coupon.state,  coupon_lot.ckey, coupon_lot.cvalue, coupon_lot.discount, coupon_lot.type, coupon_lot.channel,   coupon_lot.is_reusable, coupon_lot.start_datetime,  coupon_lot.end_datetime, coupon_lot.create_datetime, coupon.update_datetime ,coupon_lot.department,  coupon_lot.state as couponLotState, coupon_lot.create_operator,coupon_lot.coupon_category,  coupon_lot.pdt_code,brand.brand_name,product_category.category_name  from coupon_lot  left join coupon   on coupon.coupon_lot_id = coupon_lot.coupon_lot_id  LEFT JOIN brand  ON brand.brand_id=coupon_lot.brand_id  LEFT JOIN product_category  on product_category.category_id=coupon_lot.category_id  group by coupon_lot.coupon_lot_id  order by coupon_lot.create_datetime DESC  limit N,NCount: 22  Time=9.61s (211s)  Lock=0.00s (0s)  Rows=1.0 (22), [dubbo]@2hosts  select count(*)  from coupon_lot  left join coupon   on coupon.coupon_lot_id = coupon_lot.coupon_lot_id  LEFT JOIN brand  ON brand.brand_id=coupon_lot.brand_id  LEFT JOIN product_category  on product_category.category_id=coupon_lot.category_idCount: 5  Time=2.47s (12s)  Lock=0.00s (0s)  Rows=0.0 (0), [dubbo]@2hosts  select  user_id, usename, nickname, password, user_group_id, realname, gender, email, mobilephone,   birthday, province_id, city_id, district_id, marry, income, remarks, create_datetime,   active_state, verityEmail, verityPhone, credit, last_login, last_ip, is_locked, channel,   tag, union_partner_uuid, union_user_safekey, is_black, qq, weibo_id, verity_email_key,   verity_phone_key, verity_email_datetime, verity_phone_datetime, verity_question,   verity_answer, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channel  from user_info where mobilephone = 'S' and (channel is null or trim(channel) ='S')Count: 177  Time=1.40s (247s)  Lock=0.00s (0s)  Rows=1.0 (177), [dubbo]@2hosts  select count(*) from user_info  LEFT JOIN system_region a  ON province_id =a.region_id  LEFT JOIN system_region b  ON city_id = b.region_id  LEFT JOIN system_region c  ON district_id = c.region_idCount: 1  Time=1.13s (1s)  Lock=0.00s (0s)  Rows=10.0 (10), [dubbo]@[10.10.3.8]  select   return_sn,  relating_order_sn, return_from,  oi.order_out_sn ,  add_time , return_type,   return_pay, ui.usename as userName, return_consignee, return_goods_count ,  return_total_fee, total_paid,   return_desc, return_order_status,  return_pay_status ,  return_shipping_status, order_return.lock_operator, order_return.return_invoice_no  from order_return  LEFT JOIN   order_info oi  on order_return.relating_order_sn = oi.order_sn   left join user_info ui on oi.user_id = ui.user_id  left join order_payment on order_payment.order_sn = oi.order_sn  order by order_return.add_time DESC  , oi.order_sn   limit N,NCount: 8  Time=1.09s (8s)  Lock=0.00s (0s)  Rows=10.0 (80), [dubbo]@2hosts  select   return_sn,  relating_order_sn, return_from,  oi.order_out_sn ,  add_time , return_type,   return_pay, ui.usename as userName, return_consignee, return_goods_count ,  return_total_fee, total_paid,   return_desc, return_order_status,  return_pay_status ,  return_shipping_status, order_return.lock_operator, order_return.return_invoice_no  from order_return  LEFT JOIN   order_info oi  on order_return.relating_order_sn = oi.order_sn   left join user_info ui on oi.user_id = ui.user_id  left join order_payment on order_payment.order_sn = oi.order_sn  order by order_return.add_time DESC  limit N,NCount: 1  Time=1.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost  select sleep(N)Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.

看到後面還是報錯：Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.

4，去看看/usr/local/mysql/bin/mysqldumpslow的第161行左右，到底是在操作什麼指令碼。

156 my @sorted = sort { $stmt{$b}->{$opt{s}} <=> $stmt{$a}->{$opt{s}} } keys %stmt;157 @sorted = @sorted[0 .. $opt{t}-1] if $opt{t};158 @sorted = reverse @sorted         if $opt{r};159 160 foreach (@sorted) {161     my $v = $stmt{$_} || die;162     my ($c, $t,$at, $l,$al, $r,$ar) = @{ $v }{qw(c t at l al r ar)};163     my @users = keys %{$v->{users}};164     my $user  = (@users==1) ? $users[0] : sprintf "%dusers",scalar @users;165     my @hosts = keys %{$v->{hosts}};166     my $host  = (@hosts==1) ? $hosts[0] : sprintf "%dhosts",scalar @hosts;167     printf "Count: %d  Time=%.2fs (%ds)  Lock=%.2fs (%ds)  Rows=%.1f (%d), $user\@$host\n%s\n\n",168             $c, $at,$t, $al,$l, $ar,$r, $_;169 }170 171 sub usage {172     my $str= shift;

看到主要是foreach (@sorted)遍曆函數，也就是遍曆到chunk 236的時候就died結束了，沒有辦法往下繼續foreach了。因為以前寫過代碼，知道報錯所在的這一行

 my $v = $stmt{$_} || die;

是遍曆第一句，那麼肯定是指派陳述式，報錯Died，chunk 236肯定是已經遍曆到末尾沒有資料了，看這個函數沒有判斷傳入的參數是否為null的邏輯，所以當檔案資料已經遍曆完，但是命令還沒有結束，所以繼續遍曆，遍曆null的時候報錯，所以進程中止了，解決辦法就是做個限制不要遍曆超過未經處理資料的字串長度。那麼我們再來看執行的命令:

 /usr/local/mysql/bin/mysqldumpslow -s at -t 15 /root/db01-102-slow.log 有 -t 15這一句，那麼就是要顯示出前15個記錄，那麼如果我的slow.log統計出來只有12個不到15個，而這裡有繼續遍曆到15的話，那進程肯定會Died。

所以這裡有2個解決方案：

1 修改/usr/local/mysql/bin/mysqldumpslow裡面的原始碼，加null判斷。

這個過程有些複雜，暫時略過吧，期待後續空了再研究。

2 修改命令  /usr/local/mysql/bin/mysqldumpslow -s at -t 15 /root/db01-102-slow.log ，把-t 15修改成-t 10或者-t 5試試。

試了好幾次，發現臨界點在-t 7上面，如下：

[root@slave~]# /usr/local/mysql/bin/mysqldumpslow -s at -t 8 /root/db01-102-slow.log Reading mysql slow query log from /root/db01-102-slow.logCount: 22  Time=13.20s (290s)  Lock=0.00s (0s)  Rows=10.0 (220), [dubbo]@2hosts  select   coupon_id, coupon_lot.coupon_lot_id, coupon_lot.remark,  coupon.code,  coupon_lot.coupon_type, coupon.state,  coupon_lot.ckey, coupon_lot.cvalue, coupon_lot.discount, coupon_lot.type, coupon_lot.channel,   coupon_lot.is_reusable, coupon_lot.start_datetime,  coupon_lot.end_datetime, coupon_lot.create_datetime, coupon.update_datetime ,coupon_lot.department,  coupon_lot.state as couponLotState, coupon_lot.create_operator,coupon_lot.coupon_category,  coupon_lot.pdt_code,brand.brand_name,product_category.category_name  from coupon_lot  left join coupon   on coupon.coupon_lot_id = coupon_lot.coupon_lot_id  LEFT JOIN brand  ON brand.brand_id=coupon_lot.brand_id  LEFT JOIN product_category  on product_category.category_id=coupon_lot.category_id  group by coupon_lot.coupon_lot_id  order by coupon_lot.create_datetime DESC  limit N,NCount: 22  Time=9.61s (211s)  Lock=0.00s (0s)  Rows=1.0 (22), [dubbo]@2hosts  select count(*)  from coupon_lot  left join coupon   on coupon.coupon_lot_id = coupon_lot.coupon_lot_id  LEFT JOIN brand  ON brand.brand_id=coupon_lot.brand_id  LEFT JOIN product_category  on product_category.category_id=coupon_lot.category_idCount: 5  Time=2.47s (12s)  Lock=0.00s (0s)  Rows=0.0 (0), [dubbo]@2hosts  select  user_id, usename, nickname, password, user_group_id, realname, gender, email, mobilephone,   birthday, province_id, city_id, district_id, marry, income, remarks, create_datetime,   active_state, verityEmail, verityPhone, credit, last_login, last_ip, is_locked, channel,   tag, union_partner_uuid, union_user_safekey, is_black, qq, weibo_id, verity_email_key,   verity_phone_key, verity_email_datetime, verity_phone_datetime, verity_question,   verity_answer, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channel  from user_info where mobilephone = 'S' and (channel is null or trim(channel) ='S')Count: 177  Time=1.40s (247s)  Lock=0.00s (0s)  Rows=1.0 (177), [xx]@2hosts  select count(*) from user_info  LEFT JOIN system_region a  ON province_id =a.region_id  LEFT JOIN system_region b  ON city_id = b.region_id  LEFT JOIN system_region c  ON district_id = c.region_idCount: 1  Time=1.13s (1s)  Lock=0.00s (0s)  Rows=10.0 (10), [xx]@[xxx]  select   return_sn,  relating_order_sn, return_from,  oi.order_out_sn ,  add_time , return_type,   return_pay, ui.usename as userName, return_consignee, return_goods_count ,  return_total_fee, total_paid,   return_desc, return_order_status,  return_pay_status ,  return_shipping_status, order_return.lock_operator, order_return.return_invoice_no  from order_return  LEFT JOIN   order_info oi  on order_return.relating_order_sn = oi.order_sn   left join user_info ui on oi.user_id = ui.user_id  left join order_payment on order_payment.order_sn = oi.order_sn  order by order_return.add_time DESC  , oi.order_sn   limit N,NCount: 8  Time=1.09s (8s)  Lock=0.00s (0s)  Rows=10.0 (80), [xx]@2hosts  select   return_sn,  relating_order_sn, return_from,  oi.order_out_sn ,  add_time , return_type,   return_pay, ui.usename as userName, return_consignee, return_goods_count ,  return_total_fee, total_paid,   return_desc, return_order_status,  return_pay_status ,  return_shipping_status, order_return.lock_operator, order_return.return_invoice_no  from order_return  LEFT JOIN   order_info oi  on order_return.relating_order_sn = oi.order_sn   left join user_info ui on oi.user_id = ui.user_id  left join order_payment on order_payment.order_sn = oi.order_sn  order by order_return.add_time DESC  limit N,NCount: 1  Time=1.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost  select sleep(N)Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.-- 臨界點 -t 7[root@db-master-2 ~]# /usr/local/mysql/bin/mysqldumpslow -s at -t 7 /root/db01-102-slow.log Reading mysql slow query log from /root/db01-102-slow.logCount: 22  Time=13.20s (290s)  Lock=0.00s (0s)  Rows=10.0 (220), [xx]@2hosts  select   coupon_id, coupon_lot.coupon_lot_id, coupon_lot.remark,  coupon.code,  coupon_lot.coupon_type, coupon.state,  coupon_lot.ckey, coupon_lot.cvalue, coupon_lot.discount, coupon_lot.type, coupon_lot.channel,   coupon_lot.is_reusable, coupon_lot.start_datetime,  coupon_lot.end_datetime, coupon_lot.create_datetime, coupon.update_datetime ,coupon_lot.department,  coupon_lot.state as couponLotState, coupon_lot.create_operator,coupon_lot.coupon_category,  coupon_lot.pdt_code,brand.brand_name,product_category.category_name  from coupon_lot  left join coupon   on coupon.coupon_lot_id = coupon_lot.coupon_lot_id  LEFT JOIN brand  ON brand.brand_id=coupon_lot.brand_id  LEFT JOIN product_category  on product_category.category_id=coupon_lot.category_id  group by coupon_lot.coupon_lot_id  order by coupon_lot.create_datetime DESC  limit N,NCount: 22  Time=9.61s (211s)  Lock=0.00s (0s)  Rows=1.0 (22), [dubbo]@2hosts  select count(*)  from coupon_lot  left join coupon   on coupon.coupon_lot_id = coupon_lot.coupon_lot_id  LEFT JOIN brand  ON brand.brand_id=coupon_lot.brand_id  LEFT JOIN product_category  on product_category.category_id=coupon_lot.category_idCount: 5  Time=2.47s (12s)  Lock=0.00s (0s)  Rows=0.0 (0), [xx]@2hosts  select  user_id, usename, nickname, password, user_group_id, realname, gender, email, mobilephone,   birthday, province_id, city_id, district_id, marry, income, remarks, create_datetime,   active_state, verityEmail, verityPhone, credit, last_login, last_ip, is_locked, channel,   tag, union_partner_uuid, union_user_safekey, is_black, qq, weibo_id, verity_email_key,   verity_phone_key, verity_email_datetime, verity_phone_datetime, verity_question,   verity_answer, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channel  from user_info where mobilephone = 'S' and (channel is null or trim(channel) ='S')Count: 177  Time=1.40s (247s)  Lock=0.00s (0s)  Rows=1.0 (177), [xx]@2hosts  select count(*) from user_info  LEFT JOIN system_region a  ON province_id =a.region_id  LEFT JOIN system_region b  ON city_id = b.region_id  LEFT JOIN system_region c  ON district_id = c.region_idCount: 1  Time=1.13s (1s)  Lock=0.00s (0s)  Rows=10.0 (10), [dubbo]@[xxx]  select   return_sn,  relating_order_sn, return_from,  oi.order_out_sn ,  add_time , return_type,   return_pay, ui.usename as userName, return_consignee, return_goods_count ,  return_total_fee, total_paid,   return_desc, return_order_status,  return_pay_status ,  return_shipping_status, order_return.lock_operator, order_return.return_invoice_no  from order_return  LEFT JOIN   order_info oi  on order_return.relating_order_sn = oi.order_sn   left join user_info ui on oi.user_id = ui.user_id  left join order_payment on order_payment.order_sn = oi.order_sn  order by order_return.add_time DESC  , oi.order_sn   limit N,NCount: 8  Time=1.09s (8s)  Lock=0.00s (0s)  Rows=10.0 (80), [dubbo]@2hosts  select   return_sn,  relating_order_sn, return_from,  oi.order_out_sn ,  add_time , return_type,   return_pay, ui.usename as userName, return_consignee, return_goods_count ,  return_total_fee, total_paid,   return_desc, return_order_status,  return_pay_status ,  return_shipping_status, order_return.lock_operator, order_return.return_invoice_no  from order_return  LEFT JOIN   order_info oi  on order_return.relating_order_sn = oi.order_sn   left join user_info ui on oi.user_id = ui.user_id  left join order_payment on order_payment.order_sn = oi.order_sn  order by order_return.add_time DESC  limit N,NCount: 1  Time=1.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost  select sleep(N)[root@db-master-2 ~]# 

最終總結：
mysqldumpslow報錯：Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236. 是因為top數目太多了，mysqldumpslow遍曆不過來的緣故。