Support System: Error #1101 - BLOB/TEXT column 'attachments' can't have a default

Hi, havving an issue getting Support System installed... seeing the following in error log:

[28-May-2016 16:29:46 UTC] WordPress database error BLOB/TEXT column 'attachments' can't have a default value for query CREATE TABLE x_x_support_tickets_messages (
				message_id bigint(20) unsigned NOT NULL auto_increment,
				site_id bigint(20) unsigned NOT NULL,
				ticket_id bigint(20) unsigned NOT NULL,
				user_id bigint(20) unsigned NOT NULL,
				admin_id bigint(20) unsigned NOT NULL,
				message_date timestamp NOT NULL default CURRENT_TIMESTAMP,
				subject varchar(255) character set utf8 NOT NULL,
				message mediumtext character set utf8 NOT NULL,
				attachments text DEFAULT '',
				PRIMARY KEY  (message_id),
				KEY ticket_id (ticket_id)
			      ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci made by require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('init'), incsub_support_check_for_upgrades, MU_Support_System_Model->upgrade_196, MU_Support_System_Model->create_tickets_messages_table, dbDelta, wpdb->print_error
[28-May-2016 16:29:46 UTC] WordPress database error Field 'qcount' doesn't have a default value for query INSERT INTO x_x_support_faq_cats (site_id, cat_name, defcat)
						VALUES (1, 'General questions', 2) made by require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('init'), incsub_support_check_for_upgrades, MU_Support_System_Model->upgrade_198, MU_Support_System_Model->update_faq_counts, MU_Support_System_Model->get_faq_categories, MU_Support_System_Model->fill_faq_cats_default, wpdb->print_error
[28-May-2016 16:29:46 UTC] WordPress database error Field 'qcount' doesn't have a default value for query INSERT INTO x_x_support_faq_cats (site_id, cat_name, defcat)
						VALUES (1, 'General questions', 2) made by require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('init'), incsub_support_check_for_upgrades, MU_Support_System_Model->upgrade_1981, MU_Support_System_Model->create_faq_cats_table, MU_Support_System_Model->fill_faq_cats_default, wpdb->print_error

When I tried running the CREATE TABLE sql via phpMyAdmin it fails with mssg:
Error #1101 - BLOB/TEXT column 'attachments' can't have a default value
( http://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html#error_er_blob_cant_have_default )
Also, see http://dev.mysql.com/doc/refman/5.7/en/blob.html

BLOB and TEXT columns cannot have DEFAULT values.

We are running 10.1.14-MariaDB on a cPanel server... more DB details via email upon request.

Please advise asap =)

Cheers, Max

  • Adam Czajczyk

    Hello WP-Networks,

    I hope you're well today and thank you for your question!

    This seems to be related to the DB engine configuration. This error comes up most often on Windows servers but it turns out that it also occurs in a Linux environment. I admit I'm not much familiar with MariaDB configuration but it should be fully or nearly fully compatible with MySQL as far as I'm aware.

    In case of MySQL there's a fix for this that requires editing MySQL config file. The solution is to comment out this line:

    sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

    Do you have access to MariaDB configuration? If so, I think you could search for similar setting and try to comment it out as well.

    Let me know please if this solves the issue.

    Best regards,
    Adam

  • wp.network

    Hi Adam Czajczyk, yes it seems that it does relate to db...

    I also found several resources mentioning this issue generally, and it seems to be long standing and gnarly...
    from refman-5.0

    Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values and coerces them to legal values for data entry. In MySQL 5.0.2 and up, that remains the default behavior, but you can enable strict SQL mode to select more traditional treatment of bad values such that the server rejects them and aborts the statement in which they occur. Section 5.1.7, “Server SQL Modes”.

    ...and so on over the years behavior was strange, and seemingly somewhat platform associated...
    and many people commented out their sql-mode= in cnf files... however, the issue seems to be specifically with STRICT_TRANS_TABLES (and likely STRICT_ALL_TABLES) and though in 5.0.2 enabling strict mode was optional (and remained so for many years) now
    from https://dev.mysql.com/doc/refman/5.7/en/faqs-sql-modes.html

    The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

    The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5. The NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For information about all available modes and MySQL's default behavior, see Section 6.1.7, “Server SQL Modes”.

    also, btw, from https://mariadb.com/kb/en/mariadb/sql_mode/
    Since MariaDB 10.1.7, SQL_MODE is by default set to NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER. For earlier versions, no default is set.

    Also involved in this is that behaviors can vary depending if storage engine is transaction/non-trasactional (eg. InnoDB/MyISAM).

    ...I was going to write more (and more carefully) yet I've kinda spent too much time on this already.

    Long story short, I believe that the correct thing to do is to stop setting the DEAULT - though, thats easy for me to say as I'm not aware of why its being done in the first place... (did find these which looked interesting https://code.djangoproject.com/ticket/22424 | http://stackoverflow.com/a/23887248 )

    In any case... moving forward:

    I ran the following SQL at the production server which does have sql mode STRICT_TRANS_TABLES and runs MariaDB 10.1.14 using only InnoDB (transactional)...

    CREATE TABLE x_x_support_tickets_messages (
    	message_id bigint(20) unsigned NOT NULL auto_increment,
    	site_id bigint(20) unsigned NOT NULL,
    	ticket_id bigint(20) unsigned NOT NULL,
    	user_id bigint(20) unsigned NOT NULL,
    	admin_id bigint(20) unsigned NOT NULL,
    	message_date timestamp NOT NULL default CURRENT_TIMESTAMP,
    	subject varchar(255) character set utf8 NOT NULL,
    	message mediumtext character set utf8 NOT NULL,
    	attachments text,
    	PRIMARY KEY  (message_id),
    	KEY ticket_id (ticket_id)
    ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

    note that I have simply taken SQL from error log and changed the line
    attachments text DEFAULT '',
    to
    attachments text,
    ...the table is created w/o error or warning and running
    SHOW CREATE TABLE x_x_support_tickets_messages
    returns

    CREATE TABLE x_x_support_tickets_messages (
     message_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
     site_id bigint(20) unsigned NOT NULL,
     ticket_id bigint(20) unsigned NOT NULL,
     user_id bigint(20) unsigned NOT NULL,
     admin_id bigint(20) unsigned NOT NULL,
     message_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     subject varchar(255) CHARACTER SET utf8 NOT NULL,
     message mediumtext CHARACTER SET utf8 NOT NULL,
     attachments text COLLATE utf8mb4_unicode_ci,
     PRIMARY KEY (message_id),
     KEY ticket_id (ticket_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

    I then spun a fresh network on MariaDB 10.0.25 that does not have a any sql modes set beyond defaults and is using non-transactional MyISAM... I installed the Support System plugin normally and the table creation did not throw any errors or warnings... then running
    SHOW CREATE TABLE x_x_support_tickets_messages
    returns

    CREATE TABLE x_x_support_tickets_messages (
     message_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
     site_id bigint(20) unsigned NOT NULL,
     ticket_id bigint(20) unsigned NOT NULL,
     user_id bigint(20) unsigned NOT NULL,
     admin_id bigint(20) unsigned NOT NULL,
     message_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     subject varchar(255) CHARACTER SET utf8 NOT NULL,
     message mediumtext CHARACTER SET utf8 NOT NULL,
     attachments text COLLATE utf8mb4_unicode_ci,
     PRIMARY KEY (message_id),
     KEY ticket_id (ticket_id)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

    ...so, then checking table structure, in both cases the DEFAULT value of attachments is actually set to NULL

    It seems to me that since TEXT columns cannot have DEFAULT values that when strict mode is not enabled then the following applies to something like
    attachments text DEFAULT '',
    from http://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html

    If a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

    If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.

    With strict mode enabled it throws an error instead (and w/ transactional engine it gets rolled back).

    Assuming that we leave STRICT_TRANS_TABLES in place, what I'd like to know now is if my solution to alter the SQL (as described above) is viable?

    Please advise =)

    Many thanks, Max

  • wp.network

    just to make sure it doesn't get lost in the mix... there is also the possibly unrelated issue of failed INSERT INTO x_x_support_faq_cats - as noted in op above:

    [28-May-2016 16:29:46 UTC] WordPress database error Field 'qcount' doesn't have a default value for query INSERT INTO x_x_support_faq_cats (site_id, cat_name, defcat)
    						VALUES (1, 'General questions', 2) made by require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('init'), incsub_support_check_for_upgrades, MU_Support_System_Model->upgrade_198, MU_Support_System_Model->update_faq_counts, MU_Support_System_Model->get_faq_categories, MU_Support_System_Model->fill_faq_cats_default, wpdb->print_error
    [28-May-2016 16:29:46 UTC] WordPress database error Field 'qcount' doesn't have a default value for query INSERT INTO x_x_support_faq_cats (site_id, cat_name, defcat)
    						VALUES (1, 'General questions', 2) made by require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('init'), incsub_support_check_for_upgrades, MU_Support_System_Model->upgrade_1981, MU_Support_System_Model->create_faq_cats_table, MU_Support_System_Model->fill_faq_cats_default, wpdb->print_error

    Should I open a separate thread for this issue or should we address this here as well?

    Thanks again, Max

  • wp.network

    ... so, I went ahead and tried this and it seems to work - as in, I have been able to attach files to support tickets.

    I did have a little hiccup when attach button failed to appear... found it was that the js was being blocked by our firewall due to a malformed/unexpected request URL... in page source from support subsite wp-admin > support > add new ticket is
    <script type='text/javascript' src='https://support.example.com/wp-content/plugins/incsub-support//assets/js/support-system.min.js?ver=2.1.9'></script>
    I don't have time to dig into this right now... went ahead and whitelisted the URL as is; would be great to have this tracked down and fixed to correct //assets/

    Cheers, Max

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.