Read more

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

Avatar
Emanuel De
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 UI/UX Design

UI/UX Design by makandra brand

We make sure that your target audience has the best possible experience with your digital product. You get:

  • Design tailored to your audience
  • Proven processes customized to your needs
  • An expert team of experienced designers
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)
Avatar
Emanuel De
February 02, 2018Software engineer at makandra GmbH
Posted by Emanuel De to makandra dev (2018-02-02 09:39)