Hi Sheeri
On 21/08/2008, at 2:28 PM, Sheeri K. Cabral wrote:
On 8/20/08, Arjen Lentz <[EMAIL PROTECTED]> wrote:
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
Drizzle started from the 6.0 code branch FWIW. Regardless, it
doesn't work on 5.0, 5.1 nor on 6.0. :) (
Well ye but I just had the 5.0 handy and I know when things changed ;-)
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)
I knew I was missing something! Um, I don't want a default
please. And, that's not actually true (example from 6.0):
mysql> create table num (i int) ;
Query OK, 0 rows affected (0.17 sec)
mysql> show create table num\G
*************************** 1. row ***************************
Table: num
Create Table: CREATE TABLE `num` (
`i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.03 sec)
the default default, which is what I'd thought was the "default
default" for every column, is DEFAULT NULL.
So I'm not sure where you get that the default is 0.
If a col is allowed to be NULL, the "default default" is NULL.
If it's NOT NULL, then you get the magic with 0, "" and 0000-00-00 and
so on.
It's quite predictable, but it always takes some explaining in
training ;-)
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 |
+---------+------+-------------------------------------------------+
I get this in 6.0:
mysql> create table b (t text not null default '');
ERROR 1101 (42000): BLOB/TEXT column 't' can't have a default value
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.
-- (that's "minus minus", not an SQL comment or something)
I can has this wiped from the existence of Drizzle?
Concur.
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).
mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
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.
-- again :(
Yep.
Look, I can explain it since I've done it a thousand times now, but
that just makes for a funny sketch, it does not mean any of it is
pretty ;-)
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.
bad RDBMS, no biscuit. The database shouldn't try to anticipate
what I want, if I don't give it the right parameters it should warn
or give me an error.
I tend to agree, but it means we either can't have non-transactional
engines, or have to abort operations in mid flight and leave a table
in a messy state. It's one thing or the other....
So....Brian, does that answer your question???
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
