Database Schema, Default Value is NOW()

Question

I have database schema for users. It looks like...

CREATE TABLE `users` (

`id` int( 8 ) unsigned AUTO_INCREMENT,
`username` varchar( 255 ),
`password` varchar( 40 ),
`level` tinyint( 1 ) unsigned DEFAULT 1,
`time` datetime DEFAULT NOW(),
`email` varchar( 255 ),

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

There are six fields: id, username, password, level, time, email, but I want to insert only three of them - when user is registration: username, password and email. Rest of them will have default values.

Problem is that MySQL throws error: #1067 - Invalid default value for 'time'. Any ideas, guys?

Answer 1

Use CURRENT_TIMESTAMP, and change the column to timestamp.

It does the same, but works (as long as you can live with timestamp) - it is a limitation.

Discussions:

1. http://bugs.mysql.com/bug.php?id=27645
2. http://forums.mysql.com/read.php?61,67640,67771#msg-67771

So your create becomes

CREATE TABLE `users` (
`id` int( 8 ) unsigned AUTO_INCREMENT,
`username` varchar( 255 ),
`password` varchar( 40 ),
`level` tinyint( 1 ) unsigned DEFAULT 1,
`time` timestamp DEFAULT CURRENT_TIMESTAMP,
`email` varchar( 255 ),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;