MySQL text field default values and version differences

In developing my board software, I was informed of a problem with MySQL 5.1.36 regarding text fields and default values. This occurs when registering a new user, which is the first thing done after installing.

The users table has, among various other things, a signature field defined as "TEXT NOT NULL". When registering, this field is not specified in the INSERT query's field list. On my local testing/development server running MySQL 5.1.30 this works fine. On my live server running开发者_运维问答 MySQL 5.0.91 this also works. But then this tester comes up to me and says that it doesn't work on his testing server running 5.1.36, the error message complaining that the query must specify a default value.

There are a lot of TEXT fields in play here. My original plan is to detect MySQL 5.1.36 and have the installer decline to run, but only if the behavior is unique to 5.1.36. Does .37 or higher do the same? Did I just miss something?

This behavior is not unique to 5.1.36, In fact I think it's premature to assume that the MySQL version is relevant to this issue at all.

I recommend that you check which SQL_MODE is being used:

select @@SQL_MODE;

If the SQL_MODE is empty, then I would expect the insert to succeed (with a warning) in all of the versions you listed.

If the SQL_MODE is more strict, such as TRADITIONAL or STRICT_ALL_TABLES, then I would expect the insert to fail in all of the versions you listed with the error: ERROR 1364 (HY000): Field 'your_field' doesn't have a default value

Possible fixes:

1) Change the columns to explicitly allow NULL values:

ALTER TABLE your_table MODIFY COLUMN your_column TEXT NULL;

2) Force the SQL_MODE to ''. The best way to do this is to set it in my.cnf and restart mysql, but you can also set it at the MySQL prompt for testing:

SET SESSION SQL_MODE = '';

Can't you just change all your text columns to allow NULL ? Mysql doesn't let you specify DEFAULT for text/blob columns.