BLOB/TEXT column cant have a default value for query CREATE TABLE

[Max]

Hi, have had an issue during setup at a new multisite network… (using a remote host for dedicated database server (setup via WHM), db VPS is running 10.1.14-MariaDB).

Pre-Installation Wizard completes with no red =)

Setup Wizard is yielding red in DB Tables Setup:

BulletProof Security Pro Database Tables Setup
Error: Unable to create DB Table xxx_xxx_bpspro_arq_quarantine
Error: Unable to create DB Table xxx_xxx_bpspro_arq_exclude
Error: Unable to create DB Table xxx_xxx_bpspro_seclog_ignore
xxx_xxx_bpspro_login_security DB Table created Successfully!
xxx_xxx_bpspro_dbm_monitor DB Table created Successfully!
Error: Unable to create DB Table xxx_xxx_bpspro_db_backup
Error: Unable to create DB Table xxx_xxx_bpspro_folder_lock

and here are the associated log entries:

[26-May-2016 08:49:28 UTC] WordPress database error BLOB/TEXT column 'arq_quarantine_source' can't have a default value for query CREATE TABLE xxx_xxx_bpspro_arq_quarantine (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  arq_quarantine_source text default '' NOT NULL,
  arq_quarantine_backup text default '' NOT NULL,
  arq_quarantine_qpath text default '' NOT NULL,
  UNIQUE KEY id (id)
    ) made by do_action('admin_init'), call_user_func_array, bulletproof_security_admin_init, dbDelta, wpdb->print_error
	
[26-May-2016 08:49:28 UTC] WordPress database error BLOB/TEXT column 'arq_exclude_source' can't have a default value for query CREATE TABLE xxx_xxx_bpspro_arq_exclude (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  arq_exclude_source text default '' NOT NULL,
  UNIQUE KEY id (id)
    ) made by do_action('admin_init'), call_user_func_array, bulletproof_security_admin_init, dbDelta, wpdb->print_error
	
[26-May-2016 08:49:28 UTC] WordPress database error BLOB/TEXT column 'user_agent_bot' can't have a default value for query CREATE TABLE xxx_xxx_bpspro_seclog_ignore (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  user_agent_bot text default '' NOT NULL,
  UNIQUE KEY id (id)
    ) made by do_action('admin_init'), call_user_func_array, bulletproof_security_admin_init, dbDelta, wpdb->print_error
	
[26-May-2016 08:49:28 UTC] WordPress database error BLOB/TEXT column 'folder_name' can't have a default value for query CREATE TABLE xxx_xxx_bpspro_folder_lock (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  folder_name text default '' NOT NULL,
  status VARCHAR(60) DEFAULT '' NOT NULL,
  curr_perms VARCHAR(4) DEFAULT '' NOT NULL,
  orig_perms VARCHAR(4) DEFAULT '' NOT NULL,
  path text default '' NOT NULL,
  last_mod VARCHAR(10) DEFAULT '' NOT NULL,
  ulock_time VARCHAR(10) DEFAULT '' NOT NULL,
  hlock_time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  UNIQUE KEY id (id)
    ) made by do_action('admin_init'), call_user_func_array, bulletproof_security_admin_init, dbDelta, wpdb->print_error
	
[26-May-2016 08:49:28 UTC] WordPress database error BLOB/TEXT column 'bps_table_name' can't have a default value for query CREATE TABLE xxx_xxx_bpspro_db_backup (
  bps_id bigint(20) NOT NULL auto_increment,
  bps_table_name text default '' NOT NULL,
  bps_desc text default '' NOT NULL,
  bps_job_type varchar(9) default '' NOT NULL,
  bps_frequency varchar(7) default '' NOT NULL,
  bps_last_job varchar(30) default '' NOT NULL,
  bps_next_job varchar(30) default '' NOT NULL,
  bps_next_job_unix varchar(10) default '' NOT NULL,  
  bps_email_zip varchar(10) default '' NOT NULL,
  bps_job_created datetime default '0000-00-00 00:00:00' NOT NULL,
  UNIQUE KEY bps_id (bps_id)
    ) made by do_action('admin_init'), call_user_func_array, bulletproof_security_admin_init, dbDelta, wpdb->print_error

Currently the only other installed plugin is LudicrousDB –> tables are distributed across three databases: global, users, sites

I copied user tables back into global database and then removed LudicrousDB… then re-ran the BPS Pre-Installation Wizard and the Setup Wizard: this time it succeeds…

…so, now that the tables are created in global database I have put LudicrousDB back into place and things seem to be ok…
It would be very nice to be able to figure out what went on here…
Please advise w/ any insights re db issues, happy to answer any Qs =)

Kind Regards, Max

ps. also, it’d be nice to be able to BPS Monitor for more than one database!

[AITpro Admin]

Great Job on fixing the issue/problem!

When I google this search term:  “BLOB/TEXT column cant have a default value for query CREATE TABLE” I see that this is a known issue/problem with MySQL versions 5 to 5.5 and maybe higher versions.
http://stackoverflow.com/questions/3466872/why-cant-a-text-column-have-a-default-value-in-mysql
http://bugs.mysql.com/bug.php?id=21532

MariaDB is modeled after MySQL and has the same Core structure as MySQL and most of the same functionality as MySQL.  Since you are the first person to report this issue/problem then this is obviously an isolated issue/problem.  We will look into this further, but since this is the first time anyone has reported this issue/problem then I think it is smarter to handle this like an isolated issue/problem instead of changing things that work in all other cases.

We will install and test LudicrousDB, but this is going to be a low priority thing so it will take some time to get around to testing this plugin.  The DB Monitor feature is pending additional work to be considered fully completed.  Currently DB monitoring of specific tables is actually more of a nuisance than a benefit.  The DB Monitor check for new tables created is beneficial, but the check for actual changes made to specific tables is too general.  DB Monitor has been put on the back burner for a while and we will get back to focusing on developing that feature further.  For now I recommend just using the check for new tables created and not using the Update Time and Table Size advanced checking.

I checked the BPS Pro Development Task Schedule and DB Monitor is going to be worked on in BPS Pro 11.9.  Looks like adding an additional option to monitor additional/other databases is going to be added.  The primary task is to get DB Monitor to list the exact table|column|row where the change was made and other details about the DB Query and Data.

[Max]

Cheers! Yeah, this was an odd one… re LudicrousDB: I’m using a simple setup drawn from the examples provided in the plugin code… specifically, my db-config.php has

$wpdb->save_queries = false;
$wpdb->persistent = false;
$wpdb->max_connections = 20;
$wpdb->check_tcp_responsiveness = true;
$wpdb->add_database(array(
	'host'     => 'IP:PORT',
	'user'     => 'db_usr',
	'password' => 'pass',
	'name'     => 'db_name',
	'timeout'  => '0.4',
));
$wpdb->add_database(array(
	'host'     => 'IP:PORT',
	'user'     => 'db_usr',
	'password' => 'pass',
	'name'     => 'db_name',
	'timeout'  => '0.4',
	'dataset'  => 'users',
));
$wpdb->add_database(array(
	'host'     => 'IP:PORT',
	'user'     => 'db_usr',
	'password' => 'pass',
	'name'     => 'db_name',
	'timeout'  => '0.4',
	'dataset'  => 'sites1',
));
$wpdb->add_callback('user_db_callback');
function user_db_callback($query, $wpdb) {
	if ( preg_match("/^{$wpdb->base_prefix}users$/i", $wpdb->table) )
		return 'users';
}
$wpdb->add_callback('usermeta_db_callback');
function usermeta_db_callback($query, $wpdb) {
	if ( preg_match("/^{$wpdb->base_prefix}usermeta$/i", $wpdb->table) )
		return 'users';
}
$wpdb->add_callback('sites1_db_callback');
function sites1_db_callback($query, $wpdb) {
	// Multisite blog tables are "{$base_prefix}{$blog_id}_*"
	if ( preg_match("/^{$wpdb->base_prefix}\d+_/i", $wpdb->table) )
		return 'sites1';
} 

I highly recommend that you try this out… its pretty awesome generally =)

Also, since such setups can offer advantages even for single site instances and esp. as most multisite networks that grow (or want to) will eventually need to do something similar re DB structure its really great to hear that you’re aiming to allow for multiple database monitoring soon(ish)… thanks also for the config guidance re current Monitor system.

addendum: would love to know any feedback you have re. some htaccess for http to https redirect that I have been working with (and will add to the appropriate custom code area in BPS) – esp the bit about using THE_REQUEST to avoid rewriting internal requests…

### BEGIN HTTPS Catch-All
# first line optional, from https://codex.wordpress.org/Administration_Over_SSL
# An important idea in this block is using THE_REQUEST, which ensures only actual http 
# requests are rewritten and not local direct file requests, like an include or fopen
#RewriteCond %{THE_REQUEST} ^[A-Z]{3,9}\ /(.*)\ HTTP/ [NC]
RewriteCond %{HTTPS} !=on [NC]
RewriteRule ^ https://%{HTTP_HOST}%{REQUEST_URI} [R=301,QSA,L]
# note use of NC and QSA - QSA can be esp important, generally better than using QUERY_STRING
# note in RewriteRule that we are only matching against the void preceding new line which avoids
# expensive pattern matching and value capture (specifically, '^' instead of '^/?(.*)' or similar)
### END HTTPS Catch-All

I am pretty confident in this, have run it in production for awhile (and I did recently share similar in some threads in this forum)… would appreciate any critique =)

…and yeah, re the Query String Removal… perhaps the OP in that thread should have been asked if they actually even use any cache/cdn that will not cache resources with query string as – imho – catering to the testing tool generally isn’t the best way to go… any case, thanks for the fair warning, mostly I just liked your code =)

Kind Regards, Max

[AITpro Admin]

Yep, if I had a Live Production Network|Multisite site then my first priority would be distributing the database|database tables.  Originally we almost decided to combine all of our sites into 1 Network|Multisite years ago, but decided against doing that for the exact reason that 1 database would then be handling X times as many requests, Queries, etc. so we decided to leave all the sites independently installed with their own DB’s.  Your SSL/HTTPS htaccess code looks good to me. 😉

[Max]

In researching based upon a similar issue with another plugin I’ve 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 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.

[in fact, I’ve tried to adapt my support ticket text from the other plugin issue I mentioned – https://premium.wpmudev.org/forums/topic/new-blog-templates-error-1101-blobtext-column-attachments-cant-have-a-default-value#post-1090103 ]

I used xxx_xxx_bpspro_arq_quarantine in this post yet the same results and solution(?) seem to apply to all the tables reported in my initial post re this issue…

Long story shortish, 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 xxx_xxx_bpspro_arq_quarantine (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  arq_quarantine_source text NOT NULL,
  arq_quarantine_backup text NOT NULL,
  arq_quarantine_qpath text NOT NULL,
  UNIQUE KEY id (id)
    )

note that I have simply taken SQL from error log and changed the lines

arq_quarantine_source text default '' NOT NULL,
arq_quarantine_backup text default '' NOT NULL,
arq_quarantine_qpath text default '' NOT NULL,

to

arq_quarantine_source text NOT NULL,
arq_quarantine_backup text NOT NULL,
arq_quarantine_qpath text NOT NULL,
  

…the table is created w/o error or warning and running
SHOW CREATE TABLE xxx_xxx_bpspro_arq_quarantine

returns

CREATE TABLE xxx_xxx_bpspro_arq_quarantine (
 id bigint(20) NOT NULL AUTO_INCREMENT,
 time datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 arq_quarantine_source text COLLATE utf8mb4_unicode_ci NOT NULL,
 arq_quarantine_backup text COLLATE utf8mb4_unicode_ci NOT NULL,
 arq_quarantine_qpath text COLLATE utf8mb4_unicode_ci NOT NULL,
 UNIQUE KEY id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I then spun a fresh network on different server using MariaDB 10.0.25 that does not have a any sql modes set and is using non-transactional MyISAM… I installed the BPS Pro plugin normally and the table creation did not throw any errors or warnings… then running

SHOW CREATE TABLE xxx_xxx_bpspro_arq_quarantine

returns

CREATE TABLE dev_marv1n_bpspro_arq_quarantine (
 id bigint(20) NOT NULL AUTO_INCREMENT,
 time datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 arq_quarantine_source text NOT NULL,
 arq_quarantine_backup text NOT NULL,
 arq_quarantine_qpath text NOT NULL,
 UNIQUE KEY id (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

…so, then checking table structure, in both cases the DEFAULT value of TEXT columns is actually set to none

It seems to me that since TEXT columns cannot have DEFAULT values ( http://dev.mysql.com/doc/refman/5.7/en/blob.html ) that when strict mode is not enabled then something like

arq_quarantine_qpath text default '' NOT NULL,

gets interpreted as

arq_quarantine_qpath text NOT NULL,

and the column DEFAULT value is set to none rather than NULL because of NOT NULL

while something like

example text default '',

gets interpreted as

example text,

and the column DEFAULT value is set to NULL

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?

CHeers, Max

ps. I have no idea why the table creations seemed to succeed during earlier testing as though STRICT_TRANS_TABLES were not set when I tried installing again after removing LudicrousDB… the other plugin issue occurred since LudicrousDB was removed and trying to run the unaltered SQL at STRICT server now consistently returns an error (as expected) – quirky stuff =)

[AITpro Admin]

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?

Good question that I do not have an answer for…yet. I will have to do thorough testing before making any changes to anything.

[AITpro Admin]

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.

[AITpro Admin]

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”.  So you will get Credit/Kudos for a BugFix in the BPS Changelog.  Post your Credit/Kudos info you would like displayed in the Changlelog.  The Credit/Kudos format is:  Special Thanks to X:  Y (the URL you would like to use).  X is the name you want displayed and Y is the URL you want displayed.

All BPS Create Table SQL code has been changed/fixed.  All error checking code related to these DB Tables has been tested and no changes are needed.

[Max]

Thanks very much for sharing the further details… also thanks for the kudos offer =)

please go ahead and use
Max Fein https://wp-networks.com

Cheers, Max

[Author]

Posts