As designed \XF\Db\Schema\Alter :: changeColumn produces invalid SQL when attempting to modify a column from INT to BLOB

[DragonByte Tech]

Affected version

2.0.7

Given a column e.g. `keywords` INT(10) UNSIGNED NOT NULL DEFAULT '0', if you try to alter it like so:

$sm->alterTable('xf_dbtech_mail_template', function(Alter $table)
{
   $table->changeColumn('keywords', 'mediumblob')->nullable(true);
});

The SchemaManager will produce the following SQL:

ALTER TABLE `xf_dbtech_mail_template`
MODIFY COLUMN `keywords` MEDIUMBLOB UNSIGNED DEFAULT '0'

Which fails with

• XF\Db\InvalidQueryException: MySQL statement prepare error [1064]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNSIGNED DEFAULT '0'' at line 2
• src/XF/Db/AbstractStatement.php:212

I can work around this issue by doing this instead:

$sm->alterTable('xf_dbtech_mail_template', function(Alter $table)
{
   $table->changeColumn('keywords', 'mediumblob')
      ->nullable(true)
      ->unsigned(false)
      ->setDefault(null)
   ;
});

But in my opinion, changeColumn should reset the column definition so that you don't have to "undo" keywords or default values from the previous configuration.


Fillip

 

[Chris D]

There’s a method to reset the definition. Use that when the changes are such that you would need to rewrite the whole definition.

 

[DragonByte Tech]

EDIT: I found it: $table->changeColumn('keywords')->resetDefinition()->type('mediumblob')->nullable(true);


Fillip