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

CHECK_CLAUSE field in INFORMATION_SCHEMA.CHECK_CONSTRAINTS truncate check constraints expressions

Details

    Description

      Hello.
      To get information from the database about the check constraints available in the tables, in DBeaver} we use this expression:
      SELECT cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc

      And it seems the number of characters in the CHECK_CLAUSE field is limited (64?).
      Therefore, long expressions (like start_date IS NULL OR end_date IS NULL OR start_date<end_date)) are truncated.

      This creates some inconvenience when working with data for our users.

      We use SHOW CREATE TABLE to get tables DDL, but pulling the constraint expression out of there is difficult.

      Are there any other alternative solutions for this problem?

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-14474 INFORMATION_SCHEMA.CHECK_CONSTRAINTS

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            anvolka Anastasiya Volkova created issue -
            anel Anel Husakovic made changes -
            Field Original Value New Value
            Assignee Anel Husakovic [ anel ]
            anel Anel Husakovic made changes -
            anvolka Anastasiya Volkova added a comment -

            oh, i see the problem is known

            anvolka Anastasiya Volkova added a comment - oh, i see the problem is known
            anel Anel Husakovic added a comment -

            Hi,
            based on MDEV-14474 comment and looking into PR's #1127 and #1150, patch for this problem is created c8b8b01b6 but patch pushed to server a134f1ebb129 is saying that we don't use patch for extension of the length.
            I don't see why we didn't pushed (probably waiting on new MDEV? ) cvicentiu ?
            Assigning to cvicentiu to determine further flow since if ^ is true, now we have a reason to apply the patch c8b8b01b6.

            anel Anel Husakovic added a comment - Hi, based on MDEV-14474 comment and looking into PR's #1127 and #1150, patch for this problem is created c8b8b01b6 but patch pushed to server a134f1ebb129 is saying that we don't use patch for extension of the length. I don't see why we didn't pushed (probably waiting on new MDEV? ) cvicentiu ? Assigning to cvicentiu to determine further flow since if ^ is true, now we have a reason to apply the patch c8b8b01b6.
            anel Anel Husakovic made changes -
            Assignee Anel Husakovic [ anel ] Vicențiu Ciorbaru [ cvicentiu ]
            anel Anel Husakovic made changes -
            Description Hello.
            To get information from the database about the check constraints available in the tables, in DBeaver we use this expression:
            "SELECT cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc"

            And it seems the number of characters in the CHECK_CLAUSE field is limited (64?).
            Therefore, long expressions (like "start_date IS NULL OR end_date IS NULL OR start_date<end_date)") are truncated.

            This creates some inconvenience when working with data for our users.

            We use SHOW CREATE TABLE to get tables DDL, but pulling the constraint expression out of there is difficult.

            Are there any other alternative solutions for this problem?
            		Hello.
            To get information from the database about the check constraints available in the tables, in {{DBeaver}}} we use this expression:
            {{SELECT cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc}}

            And it seems the number of characters in the {{CHECK_CLAUSE}} field is limited (64?).
            Therefore, long expressions (like {{start_date IS NULL OR end_date IS NULL OR start_date<end_date)}}) are truncated.

            This creates some inconvenience when working with data for our users.

            We use {{SHOW CREATE TABLE}} to get tables {{DDL}}, but pulling the constraint expression out of there is difficult.

            Are there any other alternative solutions for this problem?
            danblack Daniel Black added a comment - 

            include/mysql_com.h:#define NAME_CHAR_LEN       64U    /* Field/table name length */
            		 
            include/mysql_com.h:#define NAME_LEN                (NAME_CHAR_LEN*SYSTEM_CHARSET_MBMAXLEN)

            Table definitions are defined in terms of character length, so using NAME_CHAR_LEN makes sense, if it was a field/table name, which is isn't.

            So work out how big CHECK can be, does it actually have a character set? Maybe MYSQL_TYPE_BLOB is more approprate in the information_schema?

            danblack Daniel Black added a comment - include/mysql_com.h:#define NAME_CHAR_LEN 64U /* Field/table name length */ include/mysql_com.h:#define NAME_LEN (NAME_CHAR_LEN*SYSTEM_CHARSET_MBMAXLEN) Table definitions are defined in terms of character length, so using NAME_CHAR_LEN makes sense, if it was a field/table name, which is isn't. So work out how big CHECK can be, does it actually have a character set? Maybe MYSQL_TYPE_BLOB is more approprate in the information_schema?
            cvicentiu Vicențiu Ciorbaru made changes -
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            cvicentiu Vicențiu Ciorbaru made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            cvicentiu Vicențiu Ciorbaru made changes -
            Status In Progress [ 3 ] In Review [ 10002 ]
            danblack Daniel Black made changes -
            Assignee Vicențiu Ciorbaru [ cvicentiu ] Daniel Black [ danblack ]
            danblack Daniel Black made changes -
            Assignee Daniel Black [ danblack ] Anel Husakovic [ anel ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            anel Anel Husakovic added a comment -

            Example of MYSQL_TYPE_BLOB filed type has length PROCESS_LIST_INFO_WIDTH=65535, which is only used for IS.PROCESSLIST.
            But in this case it is the expression that has a variable length and more likely to string instead of blob.
            So length could be here a scalar with the type of MYSQL_TYPE_STRING.
            Comparing to the check_clause of sql server where CHECK_CLAUSE nvarchar(4000) and also comparing to other fields like PLUGIN_DESCRIPTION which has value of 65535 I would suggest to go with scalar length like 65535 of type MYSQL_TYPE_STRING as suggested in the patch

            anel Anel Husakovic added a comment - Example of MYSQL_TYPE_BLOB filed type has length PROCESS_LIST_INFO_WIDTH=65535 , which is only used for IS.PROCESSLIST . But in this case it is the expression that has a variable length and more likely to string instead of blob. So length could be here a scalar with the type of MYSQL_TYPE_STRING . Comparing to the check_clause of sql server where CHECK_CLAUSE nvarchar(4000) and also comparing to other fields like PLUGIN_DESCRIPTION which has value of 65535 I would suggest to go with scalar length like 65535 of type MYSQL_TYPE_STRING as suggested in the patch
            anel Anel Husakovic made changes -
            Assignee Anel Husakovic [ anel ] Daniel Black [ danblack ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            danblack Daniel Black added a comment -

            The maximum is probably MAX_FIELD_VARCHARLENGTH (65535-2-1) which seems to match GENERATION_EXPRESSION and COLUMN_DEFAULT in the information schema.

            danblack Daniel Black added a comment - The maximum is probably MAX_FIELD_VARCHARLENGTH (65535-2-1) which seems to match GENERATION_EXPRESSION and COLUMN_DEFAULT in the information schema.
            danblack Daniel Black made changes -
            Assignee Daniel Black [ danblack ] Anel Husakovic [ anel ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            anel Anel Husakovic made changes -
            Fix Version/s 10.2 [ 14601 ]
            anel Anel Husakovic added a comment -

            Pushed to 10.2 with f924a3bd6cd70c140f

            anel Anel Husakovic added a comment - Pushed to 10.2 with f924a3bd6cd70c140f
            anel Anel Husakovic made changes -
            Fix Version/s 10.2.37 [ 25112 ]
            Fix Version/s 10.3.28 [ 25111 ]
            Fix Version/s 10.4.18 [ 25110 ]
            Fix Version/s 10.5.9 [ 25109 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 115454 ] MariaDB v4 [ 158549 ]

            People

              anel Anel Husakovic
              anvolka Anastasiya Volkova
              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.