[Done] Database error when saving attribute comment that is to long

[Design75]

This is a fresh 1.5.5.d installation.

HTTP Server: ApachePHP versie: 5.6.30-1+deb.sury.org~xenial+1 (Zend: 2.6.0)
PHP File Uploads: On
Max upload grootte: 8M
PHP Memory Limiet: 128M
POST Max grootte: 8M
Database: MySQL 5.7.17-0ubuntu0.16.04.1
Database Host: localhost (127.0.0.1)
Database datum: 20/01/2017 22:03:22
Database Data Grootte: 520 kB
Database Index Grootte: 492 kB
MySQL Slow Query Log Status: On
MySQL Slow Query Log File: /var/lib/mysql/XXXXXX-slow.log
MySQL 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

I am entering a new option name. After that I am editing the option name to add a comment. I this comment is longer than the maximum allowed characters for the database, a database error is shown, and generates the log shown below.

[20-Jan-2017 21:51:52 Europe/Amsterdam] Request URI: /MY_ADMIN/options_name_manager.php?action=update_option_name&option_page=1&&option_order_by=products_options_id, IP address: XXX.XXX.XXX.X#1  trigger_error() called at [/home/XXXXXX/public_html/includes/classes/db/mysql/query_factory.php:167]
#2  queryFactory->show_error() called at [/home/XXXXXX/public_html/includes/classes/db/mysql/query_factory.php:139]
#3  queryFactory->set_error() called at [/home/XXXXXX/public_html/includes/classes/db/mysql/query_factory.php:266]
#4  queryFactory->Execute() called at [/home/XXXXXX/public_html/MY_ADMIN/options_name_manager.php:157]


[20-Jan-2017 21:51:52 Europe/Amsterdam] PHP Fatal error:  1406:Data too long for column 'products_options_comment' at row 1 :: update products_options
                        set products_options_name = 'Totale schijfruimte', products_options_type = '5', products_options_length = '32', products_options_comment = 'De totale schijfruimte in GB die beschikbaar is voor je website, e-mail en databases.', products_options_size = '32', products_options_sort_order = '104', products_options_images_per_row = '0', products_options_images_style = '0', products_options_rows = '0'
                        where products_options_id = '5'
                        and language_id = '2' ==> (as called by) /home/XXXXXX/public_html/MY_ADMIN/options_name_manager.php on line 157 <== in /home/XXXXXX/public_html/includes/classes/db/mysql/query_factory.php on line 167

[lat9]

Hmm, I wonder if this is another issue that is exposed by MySQL 5.7's STRICT_TRANS_TABLES setting.

[Design75]

I can not find any significant changes to 'options_name_manager.php' since ZC 1.3.8 to cause this error. So you may be on to something.
I will disable STRICT_TRANS_TABLES and see what happens.

[DrByte]

Yes, it's definitely MySQL 5.7's strict mode (which is on by default).

Two options for solution:
a) make the products_options_comment field longer in the database schema. This will potentially take up more storage space, proportionately.
b) have the options_name_manager truncate any characters longer than what the database supports. This would result in lost text ... with or without an error message that would have to be coded in if you wanted one.

[Design75]

Yes, it's definitely MySQL 5.7's strict mode (which is on by default).

Two options for solution:
a) make the products_options_comment field longer in the database schema. This will potentially take up more storage space, proportionately.
b) have the options_name_manager truncate any characters longer than what the database supports. This would result in lost text ... with or without an error message that would have to be coded in if you wanted one.

I am going to implement both options . I need the longer text on this project, but also want the user to know the text was to long he entered.

[jeking]

Yes, it's definitely MySQL 5.7's strict mode (which is on by default).

Two options for solution:
a) make the products_options_comment field longer in the database schema. This will potentially take up more storage space, proportionately.
b) have the options_name_manager truncate any characters longer than what the database supports. This would result in lost text ... with or without an error message that would have to be coded in if you wanted one.

Had a similar error after moving a client to a server with MySQL 5.7 except it was 'payment_method'. Went with option #1.