Read more

Mysql::Error: BLOB/TEXT column can't have a default value

Emanuel
February 02, 2018Software engineer at makandra GmbH
mysql> SELECT @@global.version;
+------------------+
| @@global.version |
+------------------+
| 5.6.30           |
+------------------+
1 row in set (0,00 sec)

MySQL 5.6 Reference Manual Show archive.org snapshot says "BLOB and TEXT columns cannot have DEFAULT values".

Illustration web development

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

  • We build reliable cloud solutions with Infrastructure as code
  • We are experts in security, Linux and databases
  • We support your dev team to perform
Read more Show archive.org snapshot

If you want to run migrations in development here are two variants which might help. If you are not sure about the side effects (e.g. your application is broken when it doesn't set additional default values on application side, too) do not do this in production.

0. Change your application so it works without database defaults

If you know that no data is inserted by migration, remove the default from the old migration and replace it with an application-side default.

You can use a gem like has_defaults for that.

1. Temporally change the migration code

Replace things like:

create_table "some_table", :force => true do |t|
  t.column "some_field", :text, :default => "some default", :null => false
end

with:

create_table "some_table", :force => true do |t|
  t.column "some_field", :text, :null => false
end

Then run rake db:migrate and stash you changes in the migration files.

2. Temporally change the sql mode

mysql> SELECT @@global.sql_mode;
+--------------------------------------------+
| @@global.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0,00 sec)


mysql> SET @@global.sql_mode='MYSQL40';
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT @@global.sql_mode;
+-----------------------------+
| @@global.sql_mode           |
+-----------------------------+
| MYSQL40,HIGH_NOT_PRECEDENCE |
+-----------------------------+
1 row in set (0,00 sec)

Run rake db:migrate and restore your previous settings:

mysql> SET @@global.sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0,00 sec)


mysql> SELECT @@global.sql_mode;
+--------------------------------------------+
| @@global.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0,00 sec)
Emanuel
February 02, 2018Software engineer at makandra GmbH
Posted by Emanuel to makandra dev (2018-02-02 09:39)