Hi Sheeri, Brian
On 21/08/2008, at 1:16 PM, Sheeri K. Cabral wrote:
On 8/20/08, Brian Aker <[EMAIL PROTECTED]> wrote:
There is nothing in the DB that keeps a blob/text from having a
default value except about seven lines of code (I came across this
while working on modes). Today a warning is issued and we just
change the default to a "". Unless someone has a good reason, I am
going to all a default to be set. I can guess about why this
limitation existed at one point... but now? Nope, not a reason.
Curious to read this, as there's some related foo in infoschema with
the default field for columns being "rather long" (you know ;-)
specifically because someone once chucked a bug report about a problem
with a longer default for a text/blob col.... it's either in the
sql_show.cc file (in a comment) or in one of the testcases, can't
quite recall.
Your code observation does match current reality though (MySQL 5.0.x):
create table b (t text not null default 'foo');
ERROR 1101 (42000): BLOB/TEXT column 't' can't have a default value
I may be missing something.....but can't you specify NOT NULL
DEFAULT '' ? In fact wouldn't you want to specify that?
Why is this data type different from, say, an INT? An INT doesn't
have a default value, you have to specify what you want the default
to be. (similarly, I think the DATETIME/TIMESTAMP default value of
'0000-00-00 00:00:00' should take a long walk off a short pier).
So.....what point am I missing here?
An INT generally does have a default default, it's 0.
String types have '', and temporal fields go to 0000-00-00 / 00:00:00
But the exact behaviour changed slightly from MySQL 4 to 5 (more on
this below)
It's actually not fussy about specifying a default for text/blob, just
about it being other than '':
create table b (t text not null default '');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1101 | BLOB/TEXT column 't' can't have a default value |
+---------+------+-------------------------------------------------+
The show create table shows no default though:
CREATE TABLE `b` ( `t` text NOT NULL) ENGINE=MyISAM DEFAULT
CHARSET=latin1
So, in the old MySQL 4, a column would always have a default, although
there was a compile switch to allow it to not have them. The reason
for having defaults is non-transactional engines to keep on churning
rather than chucking errors mid-way so using the compile switch would
cause trouble depending on engine.
From 4.1 you can get warnings, in 5.0 you can get additional
strictness and I think some of the strictness is actually default
which is what we see above (was testing this without any sql_mode).
If you create a table with an int column but no default (and some
other cols) and then insert without specifying the int col, 0 is
inserted (for MyISAM at least) but a warning is given that no default
value was specified in the create table for that col.
I think this behaviour is fairly messy. If no default is specified,
the server should chuck an error on an insert that doesn't have a
value for that column (aka sql_mode=strict_trans_all_tables I
believe). Because in the above, there's still the built-in what I call
"default default" that's applied when no explicit default is specified.
Jeez, this could confuse a person - easily ;-)
Cheers,
Arjen.
--
Arjen Lentz, Founder @ Open Query
Training and Expertise for MySQL in Australia and New Zealand
http://openquery.com.au/training/ (ph. +61-7-3103 0809)
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
