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?
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 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.
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?
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?
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 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
The maximum is probably MAX_FIELD_VARCHARLENGTH (65535-2-1) which seems to match GENERATION_EXPRESSION and COLUMN_DEFAULT in the information schema.
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.
Bug
Minor
MDEV-14474 INFORMATION_SCHEMA.CHECK_CONSTRAINTS
oh, i see the problem is known