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

  • wp.network
    • The Bug Hunter

    ...well, then my brain turned back on and I noticed that I had gotten confused there for a minute (it was a long night =)...

    This issue is with the Support System plugin, not NBT - I'd appreciate it if Staff can please update the thread title appropriately. Apologies for the mistake.

    Thanks, Max

  • Adam Czajczyk
    • Support Gorilla

    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
    • The Bug Hunter

    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
    • The Bug Hunter

    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
    • The Bug Hunter

    ... 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

  • Adam Czajczyk
    • Support Gorilla

    Hello Max!

    First of all, thank you for this extremely in-depth investigation and sharing its results. I read it carefully and I think you're right on both issues. It would be best if we just updated the plugin and it should solve the issue.

    I forwarded both issues to the plugin developers and hopefully they'll release a fix soon.

    Best regards,
    Adam

    • wp.network
      • The Bug Hunter

      Cheers Adam Czajczyk ...I also heard back from BPS Pro dev re similar issue:

      Looking at what WordPress is doing for DB Schema setup in: /wp-admin/includes/schema.php

      comment_content text NOT NULL,
      comment_agent varchar(255) NOT NULL default '',

      So your code modifications are good and should work fine. What we need to check is to make sure any DB value checks in any/all BPS code for these particular DB Tables is NOT looking for a default value of blank/nothing.

      What I do not know yet is if we need to change any checks for this anywhere. Logically using: if ( $wpdb->num_rows == 0 ) { would be the solution instead of checking for a blank value. I will be testing changing the DB Schema code and error checking sometime today and will post the results of testing.

      ...As it turns out this was a mistake/bug and the TEXT Type Create Table SQL code was never supposed to have or use “default”.

      I'll look forward to a Support System update addressing this, as well as the Field 'qcount' issue.

      I think that I'll open a separate ticket for the //assets/ issue as its not just that particular resource.

      Thanks again, Max

      (also, what do you think... can I get about a grip of points for this? =)

    • wp.network
      • The Bug Hunter

      Adam Czajczyk Predrag Dubajic any updates on this SQL issue?

      ...as I mentioned above, I also found something similar w/ a security plugin and I notice that they have included a fix in recent release - see
      http://www.ait-pro.com/aitpro-blog/5253/bulletproof-security-pro/whats-new-in-bulletproof-security-pro-11-9/

      BugFix: Remove “default” from TEXT Type Create Table SQL code.

      (it'd also be great to address the issue w/ malformed resource URLs)

      Kind Regards, Max

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.