MYSQL error Field 'a_column' doesn't have a default value


#1

I’m getting this error on code that has worked up to now and I can’t for the life of me see what’s wrong.

$sql = “INSERT INTO t_table (name, interval) VALUES (’$name’, ‘$interval’)”;

I’ve tried the same code on other servers and it’s working just fine. The error points to a column I’m not using in this query and, setting default values seems to at least change the error given, but you can’t set default values for auto increment fields so it will not work as a solution anyway.

Any suggestions?


#2

Are you sure there’s no other query throwing that error?


#3

Here’s the relevant block of code - this is what returned the error:

// Attempt insert query execution
$sql = “INSERT INTO t_clients (name, interval) VALUES (’$name’, ‘$interval’)”;

if(mysqli_query($link, $sql)){
//echo “Client added successfully.”;
} else{
echo "ERROR: Could not execute $sql. " . mysqli_error($link);
exit(“Exiting”);
}

Running it on other Mysql servers does not produce the error and didn’t produce errors on this one until last weekend.


#4

What if you execute the query on the phpMyAdmin interface?


#5

This is what I get:

image

here’s what I get using a different MSQLi site:
image


#6

You have 2 choices:

  1. Insert a value for client_confirmKey
  2. Set a default value for that

#7

I can’t do either because it is an AI unique ID field. On messing about with this I found that the field it complained about was pretty random and, if I put a default value in one field, it just complained about another field. At this point, all fields have default values except confirmKey. The INSERT command I’m using does not address confirmKey so I can’t understand why MYSQLi should need to do anything with it.

OR

Does MYSQL (or the version webhost.com uses) require default values in all fields? If so, I’d expect that to appear in table creation - i.e. not let me create a table without defaults in each field.

It’s a mystery :slight_smile:


#8

You can’t leave a field blank if it doesn’t have a default value


#9

OK - some research and looking for my own silly mistakes later:

  1. I was confusing client_confirmKey with client_key - it’s the latter that is AI so the former could be given a default. Doing that made the code work - ie, ckhawand, you were right.

  2. That still didn’t explain why the error suddenly appeared on code that had been working for months and that worked on other MYSQL servers. The only clue I have for that is that the error I got is only generated by ‘Strict SQL Mode’, so maybe there have been a change made I wasn’t aware of.

Anyway, the upshot is that I now know how to make my databases more robust in different settings and that’s got to be a good thing.

Many thanks again