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. :) (
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.
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?
>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 :(
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.
So....Brian, does that answer your question???
-Sheeri
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
