{question}
How can we configure SingleStore to enforce the VARCHAR length?
{question}
{answer}
By default, SingleStore truncates string values when the length of the string is greater than the specified column value. Below is an example of this behavior:
mysql> CREATE TABLE test_table ( str_col VARCHAR(8) );
Query OK, 0 rows affected (0.14 sec)
mysql> INSERT INTO test_table (str_col) VALUES ("abcdefghijklmnopqrstuvwxyz");
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM test_table;
+----------+
| str_col |
+----------+
| abcdefgh |
+----------+
1 row in set (0.04 sec)
If the application requires SingleStore to enforce the VARCHAR length, then the above behavior can be controlled by the data_conversion_compatibility_level variable. By default, it is set to 6.0. Click here to learn more about the engine variable.
mysql> SHOW VARIABLES LIKE '%data_conversion_compatibility_level%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| data_conversion_compatibility_level | 6.0 |
+-------------------------------------+-------+
1 row in set (0.01 sec)
The data_conversion_compatibility_level variable specifies the level of data conversion behavior to use. Higher levels throw errors for integer under/overflow, illegal conversion of strings to integers, and string truncation issues. Values can be either 7.0, 6.5, or 6.0. This variable can sync to all aggregators and all leaves. It’s recommended to set this to the highest available level for new application development.
To avoid truncation issues, we need to set data_conversion_compatibility_level variable as 6.5 which will validate the length and, instead of truncating, it will throw an error as below.
Command:
set global data_conversion_compatibility_level="6.5";
Error:
mysql> INSERT INTO test_table (str_col) VALUES ("abcdefghijklmnopqrstuvwxyz");
ERROR 1406 (22001): Leaf Error (10.0.1.246:3307): Data too long for column 'str_col'
Note: Test the settings before implementing the same in the production as it might impact your applications due to the stricter check.
{answer}