[Mysqldumpslow error] Diedat/usr/local/mysql/bin/mysqldumps_MySQL

[Mysqldumpslow error] Diedatusrlocalmysqlbinmysqldumpslowline161, amp; lt; amp; gt; chunk236. mysqldump

Mysqldumpslow:Died at/usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.

Conclusion: The number of top jobs is too large, and mysqldumpslow cannot be traversed.

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

1. try removing r and report an error. the parameter is not recognized.

[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: there is no such file or directory at/usr/local/mysql/bin/mysqldumpslow line 91. count: 22 Time = 9.61 s (211 s) Lock = 0.00 s (0 s) Rows = 1.0 (22), [dubbo] @ 2 hostsselect count (*) from coupon_lotleft join coupon on coupon. coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand. brand_id = coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id = coupon_lot.category_idCount: 177 Time = 1.40 s (247 s) Lock = 0.00 s (0 s) Rows = 1.0 (177 ), [dubbo] @ 2 hostsselect count (*) from user_infoLEFT JOIN system_region aON province_id =. region_idLEFT JOIN system_region bON city_id = B. region_idLEFT JOIN system_region cON district_id = c. region_idCount: 22 Time = 13.20 s (290 s) Lock = 0.00 s (0 s) Rows = 10.0 (220), [dubbo] @ 2 hostsselect coupon_id, cursor, 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, role, 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_namefrom coupon_lotleft join coupon on coupon. coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand. brand_id = coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id = partition by coupon_lot.create_datetime DESClimit N, NCount: 8 Time = 1.09 s (8 s) Lock = 0.00 s (0 s) rows = 10.0 (80), [dubbo] @ 2 hostsselect return_sn, relating_order_sn, return_from, oi. order_out_sn, add_time, return_type, return_pay, ui. usename as userName, identifier, return_goods_count, identifier, total_paid, return_desc, return_order_status, return_pay_status, identifier, identifier, role order_returnLEFT JOIN order_info oion identifier = oi. order_sn left join user_info ui on oi. user_id = ui. user_idleft join order_payment on order_payment.order_sn = oi. order_snorder by order_return.add_time DESClimit N, NCount: 1 Time = 1.00 s (1 s) Lock = 0.00 s (0 s) Rows = 1.0 (1 ), root [root] @ localhostselect sleep (N) Count: 1 Time = 1.13 s (1 s) Lock = 0.00 s (0 s) 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, identifier, return_goods_count, identifier, total_paid, return_desc, return_order_status, return_pay_status, identifier, identifier, role order_returnLEFT JOIN order_info oion identifier = oi. order_sn left join user_info ui on oi. user_id = ui. user_idleft join order_payment on order_payment.order_sn = oi. order_snorder by order_return.add_time DESC, oi. order_sn limit N, NCount: 5 Time = 2.47 s (12 s) Lock = 0.00 s (0 s) Rows = 0.0 (0), [dubbo] @ 2hostsselectuser_id, usename, nickname, password, user_group_id, realname, gender, email, mobilephone, birthday, province_id, city_id, region, marry, income, remarks, create_datetime, active_state, verityEmail, verityPhone, credit, expiration, last_ip, is_locked, channel, tag, token, token, is_black, qq, weibo_id, token, verity_question, verity_answer, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channelfrom user_info where mobilephone ='s and (channel is null or trim (channel) ='s ')

2. check the mysqldumpslow usage parameters.-s must be followed by parameters. it is more suitable for Xixia.

[root@db-master-2 ~]# /usr/local/mysql/bin/mysqldumpslow--helpUsage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are--verboseverbose--debugdebug--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 defaultal: average lock timear: average rows sentat: 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 HOSTNAMEhostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all-i NAMEname of server instance (if using mysql.server startup script)-l don't subtract lock time from total time

3. add the at parameter after-s of mysqldumpslow:[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: 22Time=13.20s (290s)Lock=0.00s (0s)Rows=10.0 (220), [dubbo]@2hostsselect 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_namefrom coupon_lotleft join coupon on coupon.coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand.brand_id=coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id=coupon_lot.category_idgroup by coupon_lot.coupon_lot_idorder by coupon_lot.create_datetime DESClimit N,NCount: 22Time=9.61s (211s)Lock=0.00s (0s)Rows=1.0 (22), [dubbo]@2hostsselect count(*)from coupon_lotleft join coupon on coupon.coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand.brand_id=coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id=coupon_lot.category_idCount: 5Time=2.47s (12s)Lock=0.00s (0s)Rows=0.0 (0), [dubbo]@2hostsselectuser_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_channelfrom user_info where mobilephone = 'S' and (channel is null or trim(channel) ='S')Count: 177Time=1.40s (247s)Lock=0.00s (0s)Rows=1.0 (177), [dubbo]@2hostsselect count(*) from user_infoLEFT JOIN system_region aON province_id =a.region_idLEFT JOIN system_region bON city_id = b.region_idLEFT JOIN system_region cON district_id = c.region_idCount: 1Time=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_nofrom order_returnLEFT JOIN order_info oion order_return.relating_order_sn = oi.order_sn left join user_info ui on oi.user_id = ui.user_idleft join order_payment on order_payment.order_sn = oi.order_snorder by order_return.add_time DESC, oi.order_sn limit N,NCount: 8Time=1.09s (8s)Lock=0.00s (0s)Rows=10.0 (80), [dubbo]@2hostsselect 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_nofrom order_returnLEFT JOIN order_info oion order_return.relating_order_sn = oi.order_sn left join user_info ui on oi.user_id = ui.user_idleft join order_payment on order_payment.order_sn = oi.order_snorder by order_return.add_time DESClimit N,NCount: 1Time=1.00s (1s)Lock=0.00s (0s)Rows=1.0 (1), root[root]@localhostselect sleep(N)Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.

The following error is returned:Died at/usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.

4. let's see about 161st rows of/usr/local/mysql/bin/mysqldumpslow. what script is being operated.

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: %dTime=%.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;

The main result is the foreach (@ sorted) Traversal function, that is, when chunk 236 is traversed, died is over. there is no way to continue foreach. Because I have written code before and know the line in which the error is reported

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

It is the first sentence of traversal, so it must be a value assignment statement. The error Died is reported. chunk 236 is certainly no data has been traversed to the end. check whether the input parameter is null by using this function, so when the file data has been traversed, but the command is not over, so continue to traverse, when traversing null, an error is reported, so the process is aborted, the solution is to limit not to traverse strings that exceed the length of the original data. Then let's look at the executed command:

/Usr/local/mysql/bin/mysqldumpslow-s at-t 15/root/db01-102-slow.log has-t 15, so it is to display the first 15 records, so if my slow. log statistics show that there are only 12 to 15, and if we continue to traverse to 15, the process will certainly be Died.

Therefore, there are two solutions:

1. modify the source code in/usr/local/mysql/bin/mysqldumpslow and add null for determination.

This process is a bit complicated. let's skip it for the time being. we hope we will try again later.

2 modify command/usr/local/mysql/bin/mysqldumpslow-s at-t 15/root/db01-102-slow.log, change-t 15 to-t 10 or-t 5 try.

I tried it several times and found that the critical point was above-t 7, as shown below:

[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.20 s (290 s) Lock = 0.00 s (0 s) Rows = 10.0 (220), [dubbo] @ 2 hostsselect 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.cha Nnel, 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_namefrom coupon_lotleft join coupon on coupon. coupon_lot_id = coupon_lot.coupon_lot_idLEFT J OIN brandON brand. brand_id = coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id = partition by coupon_lot.create_datetime DESClimit N, NCount: 22 Time = 9.61 s (211 s) Lock = 0.00 s (0 s) rows = 1.0 (22), [dubbo] @ 2 hostsselect count (*) from coupon_lotleft join coupon. coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand. B Rand_id = coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id = hour: 5 Time = 2.47 s (12 s) Lock = 0.00 s (0 s) Rows = 0.0 (0), [dubbo] @ 2hostsselectuser_id, usename, nickname, password, user_group_id, realname, gender, email, mobilephone, birthday, province_id, city_id, identifier, marry, income, remarks, create_datetime, active_state, verityEmail, verityPhone, crere Dit, last_login, last_ip, is_locked, channel, tag, tags, tags, is_black, qq, weibo_id, tags, verity_question, metrics, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channelfrom user_info where mobilephone ='s and (channel is null or trim (channel) ='s) Count: 177 Time = 1.40 S (247 S) Lock = 0.00 s (0 s) Rows = 1.0 (177), [xx] @ 2 hostsselect count (*) from user_infoLEFT JOIN system_region aON province_id =. region_idLEFT JOIN system_region bON city_id = B. region_idLEFT JOIN system_region cON district_id = c. region_idCount: 1 Time = 1.13 s (1 s) Lock = 0.00 s (0 s) 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. usena Me as userName, response, return_goods_count, return_total_fee, total_paid, return_desc, return_order_status, return_pay_status, condition, condition, order_returnLEFT JOIN order_info oion limit = oi. order_sn left join user_info ui on oi. user_id = ui. user_idleft join order_payment on order_payment.orde R_sn = oi. order_snorder by order_return.add_time DESC, oi. order_sn limit N, NCount: 8 Time = 1.09 s (8 s) Lock = 0.00 s (0 s) Rows = 10.0 (80), [xx] @ 2 hostsselect 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_timeout, total_paid, return_desc, return_order_status, return_pay_status, return_shippin G_status, order_return.lock_operator, order_return.return_invoice_nofrom order_returnLEFT JOIN order_info oion order_return.relating_order_sn = oi. order_sn left join user_info ui on oi. user_id = ui. user_idleft join order_payment on order_payment.order_sn = oi. order_snorder by order_return.add_time DESClimit N, NCount: 1 Time = 1.00 s (1 s) Lock = 0.00 s (0 s) Rows = 1.0 (1 ), root [root] @ localhostselect sleep (N) D Ied at/usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236. -- critical point-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.20 s (290 s) Lock = 0.00 s (0 s) Rows = 10.0 (220), [xx] @ 2 hostsselect 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.channe L, 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_namefrom coupon_lotleft join coupon on coupon. coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN BrandON brand. brand_id = coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id = partition by coupon_lot.create_datetime DESClimit N, NCount: 22 Time = 9.61 s (211 s) Lock = 0.00 s (0 s) rows = 1.0 (22), [dubbo] @ 2 hostsselect count (*) from coupon_lotleft join coupon. coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand. bran D_id = coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id = frequency: 5 Time = 2.47 s (12 s) Lock = 0.00 s (0 s) Rows = 0.0 (0), [xx] @ 2hostsselectuser_id, usename, nickname, password, user_group_id, realname, gender, email, mobilephone, birthday, province_id, city_id, identifier, marry, income, remarks, create_datetime, active_state, verityEmail, verityPhone, credit, l Ast_login, last_ip, is_locked, channel, tag, tags, tags, is_black, qq, weibo_id, tags, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channelfrom user_info where mobilephone ='s and (channel is null or trim (channel) ='s) Count: 177 Time = 1.40 S (247 S) Lock = 0.00 s (0 s) Rows = 1.0 (177), [xx] @ 2 hostsselect count (*) from user_infoLEFT JOIN system_region aON province_id =. region_idLEFT JOIN system_region bON city_id = B. region_idLEFT JOIN system_region cON district_id = c. region_idCount: 1 Time = 1.13 s (1 s) Lock = 0.00 s (0 s) 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, region, return_goods_count, return_total_fee, total_paid, return_desc, return_order_status, return_pay_status, region order_returnLEFT JOIN order_info oion quota = oi. order_sn left join user_info ui on oi. user_id = ui. user_idleft join order_payment on order_payment.order_s N = oi. order_snorder by order_return.add_time DESC, oi. order_sn limit N, NCount: 8 Time = 1.09 s (8 s) Lock = 0.00 s (0 s) Rows = 10.0 (80), [dubbo] @ 2 hostsselect 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_timeout, total_paid, return_desc, return_order_status, return_pay_status, return_shippin G_status, order_return.lock_operator, order_return.return_invoice_nofrom order_returnLEFT JOIN order_info oion order_return.relating_order_sn = oi. order_sn left join user_info ui on oi. user_id = ui. user_idleft join order_payment on order_payment.order_sn = oi. order_snorder by order_return.add_time DESClimit N, NCount: 1 Time = 1.00 s (1 s) Lock = 0.00 s (0 s) Rows = 1.0 (1 ), root [root] @ localhostselect sleep (N )[ Root @ db-master-2 ~] #

Conclusion:
Mysqldumpslow:Died at/usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236. The reason is that the number of top jobs is too large and mysqldumpslow cannot be traversed.