Incorrect integer value ” for column ‘regex’ when creating new redir

On a different site, recently moved between our old and new hosting environments, we got a variation on a theme:

Error: Unable to add new redirect (29): Field ‘last_access’ doesn’t have a default value
Raw: {“code”:”redirect”,”message”:”Unable to add new redirect”,”data”:{“status”:400,”error_code”:29,”wpdb”:”Field ‘last_access’ doesn’t have a default value”}}

In this case, the “old” site was PHP 5.3 and MariaDB 10.1; the “new” one is the same as above (PHP 7.2, MariaDB 10.2). The site was moved with a simple mysqldump. Maybe there’s a quirk in 10.2, or different handling of default values?

(Internally, things are even more complicated with HyperDB, but we’re only using it for read replication balancing, not for anything like sharding. That shouldn’t matter here, but …)

First, the “old” PHP 5.3/MariaDB 10.1 environment. I looked both at a mysqldump, and ran a SHOW CREATE TABLE query, and they appear to be identical.

CREATE TABLE med_redirection_items (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
url mediumtext NOT NULL,
regex int(11) unsigned NOT NULL DEFAULT ‘0’,
position int(11) unsigned NOT NULL DEFAULT ‘0’,
last_count int(10) unsigned NOT NULL DEFAULT ‘0’,
last_access datetime NOT NULL,
group_id int(11) NOT NULL DEFAULT ‘0’,
status enum(‘enabled’,’disabled’) NOT NULL DEFAULT ‘enabled’,
action_type varchar(20) NOT NULL,
action_code int(11) unsigned NOT NULL,
action_data mediumtext,
match_type varchar(20) NOT NULL,
title text,
PRIMARY KEY (id),
KEY url (url(200)),
KEY status (status),
KEY regex (regex),
KEY group_idpos (group_id,position),
KEY group (group_id)
) ENGINE=InnoDB AUTO_INCREMENT=564 DEFAULT CHARSET=utf8

Then, I migrate the data to our new environment. The migration is a shell script, that does a mysqldump from the old server, then an import to the new one. After that process is done, here’s the output of a SHOW CREATE TABLE in the new PHP 7.2/MariaDB 10.2 environment:

CREATE TABLE med_redirection_items (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
url mediumtext NOT NULL,
regex int(11) unsigned NOT NULL DEFAULT 0,
position int(11) unsigned NOT NULL DEFAULT 0,
last_count int(10) unsigned NOT NULL DEFAULT 0,
last_access datetime NOT NULL,
group_id int(11) NOT NULL DEFAULT 0,
status enum(‘enabled’,’disabled’) NOT NULL DEFAULT ‘enabled’,
action_type varchar(20) NOT NULL,
action_code int(11) unsigned NOT NULL,
action_data mediumtext DEFAULT NULL,
match_type varchar(20) NOT NULL,
title varchar(50) DEFAULT NULL,
PRIMARY KEY (id),
KEY url (url(200)),
KEY status (status),
KEY regex (regex),
KEY group_idpos (group_id,position),
KEY group (group_id)
) ENGINE=InnoDB AUTO_INCREMENT=551 DEFAULT CHARSET=utf8

So, in the old one, the default values for the int columns are shown as ‘0’ but for the new version they’re just 0 (maybe some sort of typing, or maybe just a change in how the output is displayed). The action_data column seems to have picked up a DEFAULT NULL that I’m not sure where it came from, and the title column has changed types altogether. I’m so very confused right now, it looks like somehow the database itself is sabotaging me.

I’m trying to find documentation on any sort of implicit type changes between MariaDB 10.1 and 10.2. Haven’t found it yet, but the day is young.

(The auto-increment counters being different isn’t surprising since I did the last instance of copying this site from the current production environment, to the new one, a few days ago.)

Updating this plugin to use explicit values for every field when doing an INSERT will probably remedy my woes, and help future-proof the plugin from any other silliness some future MySQL-alike might have lurking.

I’ve spot-checked several of the 85 sites we have using Redirection. In general, it looks like sites that we’ve had for a few years still have the ‘old’ schema, and younger sites have the new/correct schema, but I can’t immediately find a 100% verifiable pattern. If you updated the schema in 3.0, my wild guess would be that something (a previous schema change, maybe?) specific to older sites caused the schema update to fail.

Based on the datestamps in this plugin’s change log, and our usual upgrade cycles, we probably went from 2.10.1, straight to 3.2. But that would have been for all of our sites, so that doesn’t immediately explain why the upgrade worked on most sites but not all. And no, I don’t have logs from February to explain the failure 😉

Wonder if it’d be worth adding a function that triggers on every plugin upgrade, that re-verifies (and corrects if necessary) the database layout?