2.011 - MySql 5.7.13 - "Data too long for column 'name' at row 1" when banning

[arcadecontrols]

Due to a server crash, we had to migrate to a new box. Restored database and forum is mostly functional.

However, when we attempt to ban a user whose name exceeds 6 characters, we get a database error, and the verbiage: ""Data too long for column 'name' at row 1""

Data too long for column 'name' at row 1
File: /(FORUM-DIRECTORY)/Sources/ManageBans.php
Line: 757

What's obviously happening is the forum_ban_name field in the database is set to varchar(6), the forum is defaulting the ban name to the username (> 6 characters) and the database barfs on the attempt to add it.

While manually truncating the ban name to 6 characters before we hit "add" works, there's obviously different behavior happening on the new server as compared to the old. On the old box (MySql version unknown, but obviously an older version), it simply truncated the ban name to 6 characters by itself when adding to the database, without needing to manually truncate it first.

Is there a setting in MySql that I need to tweak to get it to auto-truncate? I don't think expanding the field size is the right thing to do? I'm also leery of altering code in the forum but can if that's the recommendation?

I am a moderate novice when it comes to MySql and php.  Thanks for any help!

[arcadecontrols]

Do I need to turn off strict mode? Are there downsides to turning off strict mode?

http://stackoverflow.com/questions/18459184/mysql-too-long-varchar-truncation-error-setting [nofollow]

You can disable STRICT_TRANS_TABLES and STRICT_ALL_TABLES. This allows the automatic truncation of the inserted string.

Quote from MySQL Documantation.

    Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)

[arcadecontrols]

This is the current setting:

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


I *think* I want to add this to the my.cnf to fix this at runtime:

set global sql_mode = 'ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Removing the STRICT_TRANS_TABLES setting, but leaving the other settings that are defaulted.

(Will wait for advice before proceeding - thank you for reading!)