Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.19-galera
-
None
-
CentOS 6
Description
The following UPDATE and EXPLAIN UPDATE causes the crash:
update
|
aggintel
|
join
|
wb_transaction_work
|
on wb_transaction_work.id = aggintel.SourceID |
and aggintel.SourceCode = 'WBD' |
join
|
wb_transaction
|
on wb_transaction.id = wb_transaction_work.id |
join
|
wb_vessel
|
on wb_vessel.id = wb_transaction_work.vessel_id |
join
|
trade_ships
|
on trade_ships.LR_IMO_Ship_No = wb_vessel.imo_number |
left join |
wb_supply_plant
|
on wb_supply_plant.id = wb_transaction_work.supply_plant_id |
left join |
TTN_ports.portsdata originportsdata
|
on wb_supply_plant.MINT_PortID = originportsdata.PortID |
left join |
wb_destination_port
|
on wb_destination_port.id = wb_transaction_work.destination_port_id |
left join |
TTN_ports.portsdata destinationportsdata
|
on wb_destination_port.MINT_PortID = destinationportsdata.PortID |
left join |
wb_seller
|
on wb_seller.id = wb_transaction_work.seller_id |
left join |
wb_destination_country
|
on wb_destination_country.id = wb_transaction_work.destination_country_id |
left join |
wb_source_country
|
on wb_source_country.id = wb_transaction_work.source_country_id |
left join |
LNGVoyageTimes
|
on LNGVoyageTimes.OriginCountry = case |
when originportsdata.Country is null then wb_source_country.MINT_Country |
else originportsdata.Country end |
and LNGVoyageTimes.DestinationCountry = case |
when destinationportsdata.Country is null then wb_destination_country.MINT_Country |
else destinationportsdata.Country end |
set
|
aggintel.OriginPortAISDepartureDate =
|
case |
when aggintel.LR_IMO_Ship_No != trade_ships.LR_IMO_Ship_No |
or aggintel.OriginPortID != originportsdata.PortID |
or originportsdata.PortID is null |
or (aggintel.DepartureDate != date_add(wb_transaction_work.date_arrived, interval (LNGVoyageTimes.MinDays * -1) DAY) |
and LNGVoyageTimes.MinDays is not null) |
then null |
else aggintel.OriginPortAISDepartureDate end, |
aggintel.OriginAISVerfied =
|
case |
when aggintel.LR_IMO_Ship_No != trade_ships.LR_IMO_Ship_No |
or aggintel.OriginPortID != originportsdata.PortID |
or originportsdata.PortID is null |
or (aggintel.DepartureDate != date_add(wb_transaction_work.date_arrived, interval (LNGVoyageTimes.MinDays * -1) DAY) |
and LNGVoyageTimes.MinDays is not null) |
then null |
else aggintel.OriginAISVerfied end, |
aggintel.DepartureDate =
|
case |
when (aggintel.LR_IMO_Ship_No != trade_ships.LR_IMO_Ship_No |
or aggintel.OriginPortID != originportsdata.PortID |
or originportsdata.PortID is null |
or aggintel.DepartureDate != date_add(wb_transaction_work.date_arrived, interval -7 DAY)) |
and LNGVoyageTimes.MinDays is null |
then date_add(wb_transaction_work.date_arrived, interval -7 DAY) #setting departure date to default 7 days prior to arrival |
when (aggintel.LR_IMO_Ship_No != trade_ships.LR_IMO_Ship_No |
or aggintel.OriginPortID != originportsdata.PortID |
or originportsdata.PortID is null |
or aggintel.DepartureDate != date_add(wb_transaction_work.date_arrived, interval (LNGVoyageTimes.MinDays * -1) DAY)) |
and LNGVoyageTimes.MinDays is not null |
then date_add(wb_transaction_work.date_arrived, interval (LNGVoyageTimes.MinDays * -1) DAY) |
else aggintel.DepartureDate end, |
aggintel.DestinationPortAISArrivalDate =
|
case |
when aggintel.LR_IMO_Ship_No != trade_ships.LR_IMO_Ship_No |
or aggintel.DestinationPortID != destinationportsdata.PortID |
or destinationportsdata.PortID is null |
then null |
else aggintel.DestinationPortAISArrivalDate end, |
aggintel.DestinationAISVerified =
|
case |
when aggintel.LR_IMO_Ship_No != trade_ships.LR_IMO_Ship_No |
or aggintel.DestinationPortID != destinationportsdata.PortID |
or destinationportsdata.PortID is null |
then null |
else aggintel.DestinationAISVerified end, |
aggintel.LR_IMO_Ship_No = trade_ships.LR_IMO_Ship_No,
|
aggintel.Ship_Name = trade_ships.Ship_Name,
|
aggintel.grade_id = 35, #LNG
|
aggintel.DateReported = wb_transaction_work.date_modified,
|
aggintel.OriginPort = originportsdata.portname,
|
aggintel.OriginPortID = originportsdata.PortID,
|
aggintel.OriginCountry =
|
case |
when originportsdata.Country is null then wb_source_country.MINT_Country |
else originportsdata.Country end, |
aggintel.DestinationPort = destinationportsdata.portname,
|
aggintel.DestinationPortID = destinationportsdata.PortID,
|
aggintel.DestinationCountry =
|
case |
when destinationportsdata.Country is null then wb_destination_country.MINT_Country |
else destinationportsdata.Country end, |
aggintel.ArrivalDate = wb_transaction_work.date_arrived,
|
aggintel.CargoVolM3 = round((wb_transaction_work.bcf / v_Factor), 0),
|
aggintel.CargoWeightMT = wb_transaction_work.mt,
|
aggintel.SpecificGradeName = 'LNG', |
aggintel.Charterer = wb_seller.name, |
aggintel.Seller = wb_seller.name, |
aggintel.Price = wb_transaction_work.spot_price_reported
|
where
|
# wb_transaction_work.action = 'Update' |
#and |
wb_transaction_work.date_modified >= wb_transaction.date_modified
|
and
|
wb_transaction_work.date_modified in (select max(wtw2.date_modified) from wb_transaction_work wtw2 |
where wb_transaction_work.id = wtw2.id); |
Error log output:
150611 10:58:32 [ERROR] 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.
|
|
To report this bug, see http://kb.askmonty.org/en/reporting-bugs
|
|
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.
|
|
Server version: 10.0.19-MariaDB-wsrep-log
|
key_buffer_size=4294967296
|
read_buffer_size=16777216
|
max_used_connections=1
|
max_threads=502
|
thread_count=1
|
It is possible that mysqld could use up to key_buffer_size +
|
(read_buffer_size + sort_buffer_size)*max_threads = 20653953 K
|
bytes of memory Hope that's ok; if not, decrease some variables in
|
the equation.
|
|
Thread pointer: 0x0x67261eff2008
|
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...
|
stack_bottom = 0x6728555a5d00 thread_stack 0x48000
|
/usr/sbin/mysqld(my_print_stacktrace+0x2b)[0xba1e4b]
|
/usr/sbin/mysqld(handle_fatal_signal+0x398)[0x748948]
|
/lib64/libpthread.so.0(+0xf710)[0x672855296710]
|
/usr/sbin/mysqld[0x60d004]
|
/usr/sbin/mysqld[0x52e2e1]
|
/usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x6de)[0x62d2de]
|
/usr/sbin/mysqld(_ZN4JOIN8optimizeEv+0x28)[0x62f798]
|
/usr/sbin/mysqld(_ZN13st_select_lex31optimize_unflattened_subqueriesEb+0x88)[0x5d40b8]
|
|
/usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x1e59)[0x62ea59]
|
/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xd8)[0x62f9b8]
|
|
/usr/sbin/mysqld(_Z18mysql_multi_updateP3THDP10TABLE_LISTP4ListI4ItemES6_PS4_y15enum_duplicatesbP18st_select_lex_unitP13st_select_lexPP12multi_update+0x1ba)[0x6760ba]
|
|
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6563)[0x5e3723]
|
/usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x1e)[0x83baee]
|
/usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x8b)[0x8410cb]
|
|
/usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x155)[0x8415b5] /usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0x6c9)[0x83ed49]
|
/usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x606)[0x83ffe6]
|
|
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5f7c)[0x5e313c]
|
/usr/sbin/mysqld[0x5e54f7]
|
/usr/sbin/mysqld[0x5e5ebb]
|
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x19fb)[0x5e7dfb]
|
|
/usr/sbin/mysqld(_Z10do_commandP3THD+0x1e2)[0x5e86c2]
|
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x55b)[0x6a674b] /usr/sbin/mysqld(handle_one_connection+0x42)[0x6a6842]
|
/lib64/libpthread.so.0(+0x79d1)[0x67285528e9d1]
|
/lib64/libc.so.6(clone+0x6d)[0x6728539adb6d]
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x672615b8e020): is an invalid pointer Connection ID
|
(thread ID): 8
|
Status: NOT_KILLED
|
|
Optimizer switch:
|
index_merge=on,index_merge_union=on,index_merge_sort_union=on,inde
|
x_merge_intersection=on,index_merge_sort_intersection=off,engine_c
|
ondition_pushdown=off,index_condition_pushdown=on,derived_merge=on
|
,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=o
|
n,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial
|
_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,
|
mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,
|
join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,o
|
ptimize_join_buffer_size=off,table_elimination=on,extended_keys=on
|
,exists_to_in=on
|
|
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html
|
contains
|
information that should help you find out what is causing the crash.
|
Thanks for the report.
It is likely to be the same problem as
MDEV-8114/MDEV-7892, but given the rough path with those reports, it would be nice to check whether the bugfix helps with this query as well.sokrates,
There are a few options. You can
Please choose whatever suits you best.
If you decide on the 2nd option, please let me know what kind of package you prefer (rpm or bintar, and whether it's i386 or x86_64), so I could give you the link.