MySQL error 1067 (42000): Invalid default value for ‘ban_id’

#
# Table structure for table 'phpbb_banlist'
#
CREATE TABLE phpbb_banlist (
ban_id mediumint(8) unsigned DEFAULT '0' NOT NULL auto_increment,
ban_userid mediumint(8) DEFAULT '0' NOT NULL,
ban_ip varchar(8) DEFAULT '' NOT NULL,
ban_email varchar(255),
PRIMARY KEY (ban_id),
KEY ban_ip_user_id (ban_ip,ban_userid)
);

when you run the above code for creating new table, if you get (unexpectedly) the error:

ERROR 1067 (42000) at line 35: Invalid default value for 'ban_id'

The reason would be the version problem of MySql Database (may be starting 5.x versions). It seems from 5.x version onwards, there is change in constraints part. You can’t set a DEFAULT value to an AUTO INCREMENT field.

For those, who are facing this problem, they can change the entire sql script just using this tip in Vim Editor.

s/(.*)DEFAULT '0'(.*)auto_increment/12auto_increment/

It simply remove default values when ever auto_increment is set.