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

Recursive query leads to endless execution or server crash

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Duplicate
    • 10.2.12
    • 10.2.15
    • N/A
    • None
    • Java 8, Windows 10, 16GB RAM, SSD disk, JDBC driver mariadb-java-client-2.1.0

    Description

      Execution of the recursive query below leads to one of the possible outcomes:
      a) query execution seems to be endless (I waited no more than 20 minutes) while CPU, memory and disk usage is high (see attached screenshot)
      ...and sometimes the exectiuon leads to the crash of the server!!! The mysqld service was stopped and I had to start it up.
      b) the following exception occurs almost immediatelly :
      java.sql.SQLException: (conn:1608) (conn:1608) Out of memory (Needed 2327021592 bytes)
      c) the following exception occurs after several seconds:
      error code 1815, java.sql.SQLException: (conn:1626) (conn:1626) Internal error: Using too big key for internal temp tables

      The query is:
      with recursive query(ID, PARENT_ID, `TEXT`, DESCRIPTION) as (
      select CD0T0.ID, CD0T0.PARENT_ID, CD0T0.TEXT, CD0T0.DESCRIPTION
      FROM TEST CD0T0 where CD0T0.TEXT = 'vehicle' and CD0T0.PARENT_ID IS NULL
      union all
      select CD0T0.ID, CD0T0.PARENT_ID, CD0T0.TEXT, CD0T0.DESCRIPTION
      from TEST CD0T0, query
      where CD0T0.PARENT_ID = query.ID)
      select *
      from query

      The column TEXT is varchar(100), the column DESCRIPTION is longtext.
      The complete table defintion and data is an an attached file.

      The problem does not occur when the DESCRIPTION column is ommittted from the query - it returns the 8 rows almost instantly.

      The problem depends on the data somehow. Without the mass data (I had 22000+ records), the problem doesn't occur.

      I thought it could be the same problem as MDEV-12556, but that one should be solved in 10.2.

      Attachments

        1. task manager.png
          56 kB
          Radek Wikturna
        2. TEST.sql
          3.17 MB
          Radek Wikturna

        Issue Links

          Activity

            People

              alice Alice Sherepa
              Roderick Radek Wikturna
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.