Mysqli INSERT not working unless all columns are p

问题:

I was working on a website, I just moved its database from a server running CentOS 6 to a server running CentOS 7, and now my queries don't work unless all columns are present in the query.

$sql = 'INSERT INTO users(name, user, huser, hpass, mail, salt) VALUES (?, ?, ?, ?, ?, ?)';

/* Prepare statement */
$stmt = $conn->prepare($sql);
if($stmt === false) {
  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->errno . ' ' . $conn->error, E_USER_ERROR);
}


/* Bind parameters. Types: s = string, i = integer, d = double,  b = blob */
$stmt->bind_param('ssssss', $name, $user, $huser, $hpass, $email, $salt);

/* Execute statement */
$stmt->execute();
mysqli_close($conn);
echo "Register succeeded! Please wait...";

This script was running fine, the only change done is the mysql server address and credentials. If I add some dummy variables and complete all columns in the query it works fine.

Is this a setting I need to change? I'd like to keep my queries minimal, the empty columns will be used later. Is this bad practice? The fact that changing servers broke this makes me think it's either a difference in mysqli versions, or the configuration of mysqld.

回答1:

This script was running fine

I find that very hard to believe. You have specified that none of the 10 attributes can be null, only 2 have default values (id, access), and your script only sets 6 of the values on INSERT.

If you have specified that a value MUST NOT BE NULL then you must either specify a default or set a value.

If the script works on the old server, it must have a different schema to that above.