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

Updated . Posted . Visible to the public.
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".

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)
Last edit
Henning Koch
License
Source code in this card is licensed under the MIT License.
Posted by Emanuel to makandra dev (2018-02-02 08:39)