MySQL, a widely-used database management system, may show the warning “[Warning] Connection attributes of length 571 were truncated” in its error log. In this blog, we will explore the reasons behind this Connection attributes warning, its implications, and how to fix it for a seamless database experience.
Recently following warning messages were noted to be a frequent entries in the MySQL error log:
2023-07-10T10:50:17.028239Z 37750794 [Warning] Connection attributes of length 572 were truncated
2023-07-10T10:50:17.053255Z 37586916 [Warning] Connection attributes of length 572 were truncated
2023-07-10T10:50:17.101978Z 37814266 [Warning] Connection attributes of length 572 were truncated
2023-07-10T10:50:17.264341Z 37221390 [Warning] Connection attributes of length 572 were truncated
2023-07-10T10:50:17.275499Z 37751433 [Warning] Connection attributes of length 572 were truncated
2023-07-10T10:50:17.284948Z 37423089 [Warning] Connection attributes of length 571 were truncated
2023-07-10T10:50:17.371124Z 37212914 [Warning] Connection attributes of length 571 were truncatedUnderstanding the Warning
The warning “[Warning] Connection attributes of length 571 were truncated” indicates that the connection attributes sent by the client exceed the allowed limit, leading to truncation of the data. Connection attributes are key-value pairs that application programs can pass to the server at connect time.
As you can see the value of the configuration option is 512 vs the attribute length was much longer than that.
mysql> show global variables like 'performance_schema_session_connect_attrs_size';
+-----------------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------------+-------+
| performance_schema_session_connect_attrs_size | 512 |
+-----------------------------------------------+-------+When a MySQL server receives connection attributes from a client application, it expects these attributes to be within a predefined limit. If this variable is set too low, it can result in truncation of the attributes and trigger the warning.
While the warning itself might not have a significant impact on the performance of the MySQL server, it serves as an alert that connection attributes are not being fully captured. Depending on the context and usage of these attributes, this truncation could lead to undesired behaviour or incomplete data.
The performance_schema database holds two tables that exposes these attribute details:
- session_account_connect_attrs: Connection attributes for the current session, and other sessions associated with the session account
- session_connect_attrs: Connection attributes for all sessions
Fixing Connection attributes of length xxx were truncated
To rectify the truncation warning and ensure that connection attributes are captured entirely, follow these steps:
# Open the MySQL configuration file (my.cnf or my.ini) using a text editor.
vi /etc/my.cnf
# Set performance_schema_session_connect_attrs_size to a suitable size, such as 1024.
performance_schema_session_connect_attrs_size = 1024
# Save the configuration file and restart the MySQL server to apply the changes.
systemctl restart mysqldAbout configuration for connection attributes
Note that, the default (-1) value of performance_schema_session_connect_attrs_size is autosized at server startup. It may happen that this autosized value may be small and if the truncation happens, you should set the value of performance_schema_session_connect_attrs_size to a larger number.
The system status variable performance_schema_session_connect_attrs_lost is good counter that shows the number of times such truncation has happened. If that becomes non-zero, it is yet another indicator to resize your performance_schema_session_connect_attrs_size.
Even though the maximum allowed value for performance_schema_session_connect_attrs_size is 1MB, the effective maximum is 64KB because the server imposes a limit of 64KB on the aggregate size of connection attribute data it can accept. Even connection function mysql_options4() imposes a limit of 64KB on the aggregate size of connection attribute data it accepts. For calls that cause this limit to be exceeded, a CR_INVALID_PARAMETER_NO error occurs.
Conclusion
MySQL’s “[Warning] Connection attributes of length 571 were truncated” can be a puzzling occurrence for administrators and developers. By understanding the cause of the warning and adjusting the performance_schema_session_connect_attrs_size variable, you can ensure that connection attributes are fully captured without any truncation issues. Keeping a close eye on the MySQL error log and maintaining an optimized configuration will help you maintain a stable and efficient database system.