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 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.
            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?
            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
            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.
            anel Anel Husakovic added a comment -

            Pushed to 10.2 with f924a3bd6cd70c140f

            anel Anel Husakovic added a comment - Pushed to 10.2 with f924a3bd6cd70c140f

            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.