MySQL 5.0 does not like TEXT NOT NULL fields with no default value

[Former user]

When creating a mysql table definition for a String column, SA generates the field as:

colname TEXT NOT NULL

It turns out that MySQL doesn't accept this definition without a default value:

http://wordpress.org/support/topic/52753 (can't find a more authoritative source)

This was brought to light when I was helping a guy on the TG group:

http://groups.google.com/group/turbogears/browse_thread/thread/512406ff0ba100b1/09409a7386a13a2c?lnk=gst&rnum=2#09409a7386a13a2c

I don't think this is a big deal, but it'll save people some effort if the mysql SA driver would supply a default value of an empty string.

[jek]

The issue in the TG post looks to be due to trying to use a TEXT column as a primary key and then trying to use the entire thing as the table index. That won't fly in MySQL, and probably what they want is a VARCHAR or CHAR in the first place (e.g. String(128))

MySQL TEXT and BLOB types can not take a default:

mysql> CREATE TABLE blagga (abc TEXT NOT NULL DEFAULT '');
ERROR 1101 (42000): BLOB/TEXT column 'abc' can't have a default value

I think the wordpress bug is describing a situation where they had existing INSERTs that omitted those NOT NULL columns and older versions of MySQL were "helpfully" supplying an empty string instead of bombing out. You can mimic that behavior on a case-by-case basis by providing {{{default=''}}} in the SQLAlchemy table definition.

[Mike Bayer]

Removing milestone: 0.4.0 (automated comment)