• From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Topic: TEXT fields cannot have defaults

In  sql/alter2.4rc1.sql, the last line:

ALTER TABLE `0_sys_prefs` CHANGE `value` `value` text NOT NULL default '';

should have the default removed. In the standard Chart sqls corresponding changes must be effected.

MySQL does not allow any defaults for TINYTEXT, MEDIUMTEXT, LONGTEXT and TEXT fields.
Windows MySQL v5 throws an error but Linux and other versions only raise a warning.

Such TEXT fields do not support default values of anything but NULL. As such, it's implicitly DEFAULT NULL.

Use TRIGGERS to UPDATE such TEXT fields to any desired default value.

create table my_text

(
   abc text
);

delimiter //
create trigger mytext_trigger before insert on my_text
for each row
begin
   if (NEW.abc is null ) then
      set NEW.abc = 'default text';
   end if;
end
//
delimiter ;

No explanation is given by the MySQL documentation.

TEXT columns are for things which can become more than 255 bytes long.

On MS Windows the "no DEFAULT" rule is an error, while on other platforms it is often a warning. While not a bug, it's possible to get trapped by this if you write code on a lenient platform, and later run it on a strict platform.

How to disable strict mode in MySQL 5 (Windows):

    Edit /my.ini and look for line

    sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

    Replace it with

    sql_mode='MYSQL40'

    Restart the MySQL service (assuming that it is mysql5)

    net stop mysql5
    net start mysql5

If you have root/admin access you might be able to execute

mysql_query("SET @@global.sql_mode='MYSQL40'");

apmuthu's Website