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

INSERT-SELECT with many when/case/if conditions running forever

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 10.6.4, 10.3.38, 10.6.12
    • 10.5, 10.6
    • None

    Description

      One of our customers migrated from MySQL to MariaDB 10.6.4 and their INSERT-SELECT query having many case/when/if conditions running forever.

      Locally on the empty tables, can reproduce this situation, queries are getting stuck with "Init" and "Closing tables" state.

      MariaDB [(none)]> show processlist;
      		 
      +------+-----------------+-----------+------+---------+-------+------------------------+------------------------------------------------------------------------------------------------------+----------+
      		 
      | Id   | User            | Host      | db   | Command | Time  | State                  | Info                                                                                                 | Progress |
      		 
      +------+-----------------+-----------+------+---------+-------+------------------------+------------------------------------------------------------------------------------------------------+----------+
      		 
      .....
      		 
      |  255 | root            | localhost | mcc  | Query   |  9511 | closing tables         | insert into mcc.testoutplanreport_p1
      		 
      (
      		 
      select period,plant_code,rawline,finishedgood,status,w1status |    0.000 |
      		 
      |  288 | root            | localhost | NULL | Query   |  8881 | init                   | select period,plant_code,rawline,finishedgood,status,w1status,DELQ,DEL_DSI,DELO
      		 
      ,WW1P1,WW1P1_DSI,WW1 |    0.000 |
      		 
      .....

      Attachments

        1. mdev31130.jpg
          362 kB
          Sergei Petrunia
        2. oracle_script.sql
          9 kB
          Oleg Smirnov

        Activity

          Ascending order - Click to sort in descending order
          View 10 older comments
          sanja Oleksandr Byelkin added a comment -

          Interesting what a difference if all references would be different Items, it still will work through them?

          sanja Oleksandr Byelkin added a comment - Interesting what a difference if all references would be different Items, it still will work through them?
          sanja Oleksandr Byelkin added a comment -

          Idea with automatic walk_id do not work, because then each walk looks like a separate walk.

          There is 2 ideas:

          1. Walk protection make switchable (for example 0 walk id mean do not use it) and switch it on and of from higher functions

          2. Also remember processor and start new id only if processor changed (other way to make it automatic)

          sanja Oleksandr Byelkin added a comment - Idea with automatic walk_id do not work, because then each walk looks like a separate walk. There is 2 ideas: 1. Walk protection make switchable (for example 0 walk id mean do not use it) and switch it on and of from higher functions 2. Also remember processor and start new id only if processor changed (other way to make it automatic)
          sanja Oleksandr Byelkin added a comment -

          Prototype with second way did not work, because probably exclude important walking.

          sanja Oleksandr Byelkin added a comment - Prototype with second way did not work, because probably exclude important walking.
          sanja Oleksandr Byelkin added a comment -

          Try to cut checked branches of Item tree by check goal... i.e. similar task (so far only one) will have task (or goal ID) and mark items checked already...

          sanja Oleksandr Byelkin added a comment - Try to cut checked branches of Item tree by check goal... i.e. similar task (so far only one) will have task (or goal ID) and mark items checked already...
          sanja Oleksandr Byelkin added a comment -

          Better: idea get access to the translation table and mark the table slots if the bit mark operation is done...
          (the main problem is get fast access to the table)

          sanja Oleksandr Byelkin added a comment - Better: idea get access to the translation table and mark the table slots if the bit mark operation is done... (the main problem is get fast access to the table)

          People

            sanja Oleksandr Byelkin
            suresh.ramagiri@mariadb.com suresh ramagiri
            Votes:
            1 Vote for this issue
            Watchers:
            9 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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