Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8331

Server crashes by complex UPDATE with one view. Same update on a MySQL 5.1 server worked.

    Details

      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.

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              sokrates Jens Bollmann
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: