#1067 - Invalid default value for 'membership'

Question

I am using a SQL file included in my program and once I fixed an issue that I was able to find the answer to in here (THANK YOU).. I ran it again and got the following error:

SQL query:

# Create table structure for 'member' table
CREATE TABLE member(

memberID INT( 11 ) NOT NULL AUTO_INCREMENT ,  
title VARCHAR( 80 ) NOT NULL DEFAULT  '',  
firstname VARCHAR( 80 ) NOT NULL DEFAULT  '',  
lastname VARCHAR( 80 ) NOT NULL DEFAULT  '',  
email VARCHAR( 80 ) NOT NULL DEFAULT  '',  
address VARCHAR( 80 ) NOT NULL DEFAULT  '',  
suburb VARCHAR( 80 ) NOT NULL DEFAULT  '',  
state VARCHAR( 80 ) NOT NULL DEFAULT  '',  
country VARCHAR( 80 ) NOT NULL DEFAULT  '',  
postcode VARCHAR( 11 ) NOT NULL DEFAULT  '',  
mobile VARCHAR( 80 ) NOT NULL DEFAULT  '',  
phone VARCHAR( 80 ) NOT NULL DEFAULT  '',  
fax VARCHAR( 80 ) NOT NULL DEFAULT  '',  
membership INT( 2 ) NOT NULL DEFAULT  '',  
payment INT( 2 ) NOT NULL DEFAULT  '',  
startdate BIGINT( 14 ) NOT NULL DEFAULT 0,  
enddate BIGINT( 14 ) NOT NULL DEFAULT 0,  
userID INT( 11 ) NOT NULL ,  
PRIMARY KEY ( memberID ) ,  
UNIQUE (  
memberID  
)  
) ENGINE = MYISAM ;

MySQL said:

#1067 - Invalid default value for 'membership'

What do I need to change to make this work ? this is an older script, but I am not knowledgeable enough to know what needs to be changed for MySQL 5.5 version.

Thank you so much for your help!

And I'm sure there will be other errors popping up after I fix this one..

score 7 · Answer 1 · answered Dec 09 '12 at 22:37

7

The membership column is an int, but you're giving it a default string value of ''. So change

 membership INT( 2 ) NOT NULL DEFAULT '',

to

 membership INT( 2 ) NOT NULL DEFAULT 0,

(0 or whatever default value you want for that field.)

answered Dec 09 '12 at 22:37

Marvo

17,845
8
50
74

Thank you.. I corrected this and a few other errors after fixing this one. There is another error which I will put into it's own question. – user1890162 Dec 10 '12 at 00:23
You're welcome, and welcome to StackOverflow. Remember to accept correct answers, or up-vote otherwise helpful answers. – Marvo Dec 10 '12 at 02:01

score 0 · Answer 2 · answered Jul 07 '17 at 02:09

I was getting:

Incorrect integer value: '' for column 'column_name' at row 1

I've fixed it editing /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf files. Then find the following line:

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

and replace it for

sql-mode=""

and restart MySQL: service mysql restart

#1067 - Invalid default value for 'membership'

2 Answers2