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

Sudden crash on query

    XMLWordPrintable

Details

    Description

      MariaDB suddenly crashes with [ERROR] mysqld got signal 11 ; during the request:

      *************************** 8. row ***************************
            Id: 121063
          User: comua120_usr
          Host: localhost
            db: comua120_back_09-03-2017
       Command: Query
          Time: 5
         State: statistics
          Info: SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value,
                          COUNT(DISTINCT p.id_product) nbr,
                          lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max
       
                          FROM ps_feature_product fp
                          INNER JOIN ps_product p ON (p.id_product = fp.id_product)
                          LEFT JOIN ps_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 2)
                          INNER JOIN ps_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0))
                          LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 2)
                          LEFT JOIN ps_layered_indexable_feature_lang_value lifl
                          ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 2)
                          LEFT JOIN ps_layered_indexable_feature_value_lang_value lifvl
                          ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 2)  INNER JOIN ps_product_shop product_shop
              ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
       
                  INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 1
                  AND psi.price_min <= 16000 AND psi.price_max >= 0) 
      WHERE product_shop.`active` = 1 AND fp.id_feature = 9
                          AND p.id_product IN (
                          SELECT id_product
                          FROM ps_category_product cp
                          INNER JOIN ps_category c ON (c.id_category = cp.id_category AND
                          c.nleft >= 25
                          AND c.nright <= 50
                          AND c.active = 1))  AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 92)  AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 37)  AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 31)  AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 59) 
      GROUP BY fv.id_feature_value
      Progress: 0.000
      

      As a workaround we had to dump and restore database into the new db. This process (dump/restore) should be made every 10-14 days, as in the other case we are facing this bug again.

      my.cnf:

      [mysqld]
      max_connections              = 300
      max_user_connections         = 30
       
      innodb_file_per_table        = 1
      innodb_corrupt_table_action  = assert
      local-infile                 = 0
       
      key_buffer_size              = 512M
      query_cache_size             = 96M
      query_cache_limit            = 1M
      query_cache_min_res_unit     = 2048
       
      table_open_cache             = 7000   
      innodb_open_files            = 7000
      open_files_limit=50000
       
      tmp_table_size               = 16M
      max_heap_table_size          = 16M
       
      join_buffer_size             = 32M
      max_join_size                = 100000000
       
      thread_cache_size            = 20
       
      innodb_buffer_pool_size      = 8000M
      innodb_buffer_pool_instances = 8
      innodb_flush_log_at_trx_commit = 2
      innodb_log_buffer_size       = 16M
      innodb_additional_mem_pool_size = 24M
      innodb_log_file_size         = 512M
      innodb_io_capacity           = 200
       
      max_allowed_packet           = 268435456
       
      tmpdir=/var/lib/mysql/tmp_table
      

      Attachments

        1. log-error.txt
          634 kB
        2. schema.sql
          185 kB

        Activity

          People

            Unassigned Unassigned
            noonedeadpunk Dmitriy Rabotyagov
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.