1

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?

Kawa
  • 1,478
  • 1
  • 15
  • 20
  • wouldn't it be easier just to set the default field values to `''` (empty string)? Then you don't need to worry about MySQL versions. – dnagirl Mar 01 '11 at 17:58
  • Because there are a lot of text fields, like I said. – Kawa Mar 01 '11 at 17:59
  • but you only set up the table definition once, so trying to avoid setting the field default seems a false economy, even if there are umpteen text fields in multiple tables. The other possibility, rather than detecting MySQL versions, is just to write your insert queries with an empty string for each text field. But that *is* a lot more work. – dnagirl Mar 01 '11 at 18:05
  • The funny part is that, if I remember correctly, I can't just specify "" as the default value in the table definition in the first place... – Kawa Mar 01 '11 at 18:09
  • possible duplicate of [how to give Default Text like 'not updated' to text type in sql](http://stackoverflow.com/questions/23886364/how-to-give-default-text-like-not-updated-to-text-type-in-sql) – M Khalid Junaid May 27 '14 at 13:45

2 Answers2

7

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 = '';
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
2

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

a1ex07
  • 36,826
  • 12
  • 90
  • 103