MySQL: Column cannot be null

Question

First of all, I googled this question but didn't find any similar situations. So I appreсiate any help.

I have a table:

CREATE TABLE tinypurses.purses (
  id SMALLINT(6) NOT NULL AUTO_INCREMENT,
  Time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  keyyy CHAR(100) DEFAULT NULL,
  LastSeed SMALLINT(6) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  INDEX purses_id_index (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 2
CHARACTER SET utf8
COLLATE utf8_general_ci;

When I try insert a new record into it, I got an error:

Database error: [MySQL][ODBC 5.1 Driver][mysqld-5.1.47-community]Column 'LastSeed' cannot be null

I'm confused because I explicitly set value for column 'LastSeed':

statement.prepare(connection(), "insert into purses (time, keyyy, LastSeed) values(?, ?, ?)");
statement.param(1).set_as_systemtime(sysTime);
statement.param(2).set_as_string(key);
statement.param(3).set_as_long(1);
if (!stmt.execute())
{
    LOG << statement.last_error(); // got this error here
}

Moreover, this code works fine on Windows XP and 7, but not on Windows 8 (with the same version of MySQL server).

Does anybody have any ideas why it works on a such strange manner?

Thank you.

Arun Kumar · Accepted Answer · 2013-09-30 10:30:10Z

2

Don't use the field name as "key" . it's a keyword of mysql.

answered Sep 30, 2013 at 10:30

Arun Kumar

1,6331 gold badge18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tmporaries Over a year ago

ok, I failed, but I renamed not related columns when posted initial question. I got this error even if column has a valid name.

tmporaries Over a year ago

yeah, it works fine on XP and 7. Looks like used version of MySQL Server or ODBC connector is very old for Windows 8 - something goes wrong.

geomagas · Accepted Answer · 2013-09-30 10:36:36Z

2

Even if you didn't explicitly set value for column LastSeed, setting a DEFAULT constraint in the field definition (as you do) should suffice.

Since you say that it works on other versions of Windows, and since the error is obviously irrelevant, I would investigate the ODBC driver installed.

answered Sep 30, 2013 at 10:36

geomagas

3,2901 gold badge19 silver badges28 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:26:34Z

-1

MySQL on Win XP thru Win 7 by default behave incorrectly with regards to NULL values and whether or not it will allow an insert of a NULL value. ie: on windows MySQL by default will allow an insert of a NULL value into a NOT NULL field. Here is why

MySQL on *nix and apparently now Win 8 behave correctly. The reason that you can not insert '0' into a NOT NULL field is because in MySQL '0' === NULL.

3.3.4.6. Working with NULL Values

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Feb 18, 2014 at 22:35

BroknDodge

7116 silver badges9 bronze badges

1 Comment

ajiang Over a year ago

As the documentation you linked to shows, 0 and '0' (and any other value) are both not NULL

Collectives™ on Stack Overflow

MySQL: Column cannot be null

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related