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

LP:925985 - Innodb crash on JOIN query when "join_buffer_size" > "join_buffer_space_limit"

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      I don't how to describe the problem but the query below crashes innodb (xtradb). It does not happening on mysql 5.1 & 5.5
      I'm using 5.3.3-MariaDB-rc-mariadb108~squeeze-log from mariadb repository on debian 6 64bit

      Query:

      SELECT DISTINCT
      B.TYPE_SID,
      B.ID BANNER_ID,
      B.WEIGHT BANNER_WEIGHT,
      B.SHOWS_FOR_VISITOR,
      B.FIX_CLICK,
      B.FIX_SHOW,
      B.KEYWORDS BANNER_KEYWORDS,
      DATE_FORMAT(B.DATE_SHOW_FIRST, '%d.%m.%Y %H:%i:%s') DATE_SHOW_FIRST,
      DATE_FORMAT(B.DATE_SHOW_FROM, '%d.%m.%Y %H:%i:%s') DATE_SHOW_FROM,
      DATE_FORMAT(B.DATE_SHOW_TO, '%d.%m.%Y %H:%i:%s') DATE_SHOW_TO,
      B.FLYUNIFORM FLYUNIFORM,
      B.MAX_SHOW_COUNT MAX_SHOW_COUNT,
      B.SHOW_COUNT SHOW_COUNT,
      C.ID CONTRACT_ID,
      C.WEIGHT CONTRACT_WEIGHT,
      C.KEYWORDS CONTRACT_KEYWORDS
      FROM
      b_adv_type T

      INNER JOIN b_adv_banner B ON (
      B.ACTIVE='Y'
      and B.TYPE_SID = T.SID
      and B.STATUS_SID = 'PUBLISHED'
      and (B.FOR_NEW_GUEST is null or B.FOR_NEW_GUEST='N')
      and (ifnull(B.MAX_SHOW_COUNT,0)>ifnull(B.SHOW_COUNT,0) or ifnull(B.MAX_SHOW_COUNT,0)=0)
      and (ifnull(B.MAX_CLICK_COUNT,0)>ifnull(B.CLICK_COUNT,0) or ifnull(B.MAX_CLICK_COUNT,0)=0)
      and (ifnull(B.MAX_VISITOR_COUNT,0)>ifnull(B.VISITOR_COUNT,0) or ifnull(B.MAX_VISITOR_COUNT,0)=0)
      and (B.DATE_SHOW_FROM<=now() or B.DATE_SHOW_FROM is null or length(B.DATE_SHOW_FROM)<=0)
      and (B.DATE_SHOW_TO>=now() or B.DATE_SHOW_TO is null or length(B.DATE_SHOW_TO)<=0))

      INNER JOIN b_adv_banner_2_site BS ON (
      BS.BANNER_ID = B.ID
      and BS.SITE_ID = 'en')

      INNER JOIN b_adv_contract C ON (
      C.ID = B.CONTRACT_ID
      and C.ACTIVE='Y'
      and (ifnull(C.MAX_SHOW_COUNT,0)>ifnull(C.SHOW_COUNT,0) or ifnull(C.MAX_SHOW_COUNT,0)=0)
      and (ifnull(C.MAX_CLICK_COUNT,0)>ifnull(C.CLICK_COUNT,0) or ifnull(C.MAX_CLICK_COUNT,0)=0)
      and (ifnull(C.MAX_VISITOR_COUNT,0)>ifnull(C.VISITOR_COUNT,0) or ifnull(C.MAX_VISITOR_COUNT,0)=0)
      and (C.DATE_SHOW_FROM<=now() or C.DATE_SHOW_FROM is null or length(C.DATE_SHOW_FROM)<=0)
      and (C.DATE_SHOW_TO>=now() or C.DATE_SHOW_TO is null or length(C.DATE_SHOW_TO)<=0))

      INNER JOIN b_adv_contract_2_site CS ON (
      CS.CONTRACT_ID = B.CONTRACT_ID
      and CS.SITE_ID = 'en')

      INNER JOIN b_adv_contract_2_type CT ON (
      CT.CONTRACT_ID = C.ID
      and (CT.TYPE_SID = 'ALL' or CT.TYPE_SID = T.SID))

      INNER JOIN b_adv_banner_2_weekday BW ON (
      BW.BANNER_ID = B.ID
      and BW.C_WEEKDAY='FRIDAY'
      and BW.C_HOUR = '11')

      INNER JOIN b_adv_contract_2_weekday CW ON (
      CW.CONTRACT_ID = C.ID
      and CW.C_WEEKDAY='FRIDAY'
      and CW.C_HOUR = '11')

      LEFT JOIN b_adv_banner_2_group UG1 ON (
      (UG1.BANNER_ID = B.ID
       and UG1.GROUP_ID in (1,2) and UG1.GROUP_ID<>2)
      )

      LEFT JOIN b_adv_banner_2_page BP1 ON (
      BP1.BANNER_ID = B.ID
      and BP1.SHOW_ON_PAGE='Y')

      LEFT JOIN b_adv_banner_2_page BP2 ON (
      BP2.BANNER_ID = B.ID
      and BP2.SHOW_ON_PAGE='N'
      and '/club/index.php' like concat(BP2.PAGE, '%'))

      LEFT JOIN b_adv_contract_2_page CP1 ON (
      CP1.CONTRACT_ID = C.ID
      and CP1.SHOW_ON_PAGE='Y')

      LEFT JOIN b_adv_contract_2_page CP2 ON (
      CP2.CONTRACT_ID = C.ID
      and CP2.SHOW_ON_PAGE='N'
      and '/club/index.php' like concat(CP2.PAGE, '%'))

      LEFT JOIN b_adv_banner_2_stat_adv BA ON BA.BANNER_ID = B.ID
      LEFT JOIN b_adv_banner_2_country BC ON BC.BANNER_ID = B.ID AND (
      (
      (B.STAT_TYPE is null OR length(B.STAT_TYPE)=0 OR B.STAT_TYPE='COUNTRY')
      AND BC.COUNTRY_ID='UA'
      ) OR (
      B.STAT_TYPE='REGION'
      AND BC.COUNTRY_ID='UA'
      AND BC.REGION=''
      ) OR (
      B.STAT_TYPE='CITY'
      AND BC.CITY_ID='2'
      )
      )

      WHERE
      T.ACTIVE = 'Y'

      and (
      B.STAT_COUNT is null
      or B.STAT_COUNT = 0
      or BC.BANNER_ID is not null
      )
      and BP2.ID is null
      and CP2.ID is null
      and (BP1.ID is null or '/club/index.php' like concat(BP1.PAGE, '%'))
      and (CP1.ID is null or '/club/index.php' like concat(CP1.PAGE, '%'))
      and (BA.STAT_ADV_ID is null or BA.STAT_ADV_ID='0')
      and (BC.COUNTRY_ID is null or BC.COUNTRY_ID='UA')
       and
       (
      (B.SHOW_USER_GROUP = 'Y' and UG1.GROUP_ID is not null)
      or
      (B.SHOW_USER_GROUP <> 'Y' and UG1.GROUP_ID is null)
       )
      ORDER BY B.TYPE_SID desc, C.ID desc

      This query is used in most popular russian CMS (bitrix) upon user login. I would like to provide more info if needed

      Attachments

        Activity

          People

            igor Igor Babaev (Inactive)
            troexnevelin Troex Nevelin (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.