MySQL Insert requires specifying all fields?

Question

When I execute a pdo that specifies some but not all fields in a table, it inserts fine on one server, but fails to insert on the other server.

FOR EXAMPLE, ON THE PROBLEM SERVER...

With table that has three fields id, time, and intervention...

If I run this statement, it inserts as expected.

$query = "INSERT INTO data_agreements (`id`, `time`, `intervention`) VALUES (?, ?, ?)";
$q = $pdo->prepare($query);
$q->execute(array("x", "y", "z"));

But if I run the following statement, it fails to insert.

$query = "INSERT INTO data_agreements (`id`, `time`) VALUES (?, ?)";
$q = $pdo->prepare($query);
$q->execute(array("x", "y"));

The problem server is an IIS with MySql 5, PHP 5.2.6. On a different server Apache with MySql 5, PHP 5.2.6, both statements insert as expected. So I am wondering if there is some MySQL install/setting that I need to set on the problem server to tell it to accept statements that specify only a subset of fields in a table. ?

Thanks!

What error message is mySQL giving you? See [How to squeeze error message out of PDO?](http://stackoverflow.com/q/3726505) — Pekka, Aug 08 '12 at 13:57
maybe id and/or time are UNIQUE / PRIMARY KEYs and hence a second insertion is refused? — Raffael, Aug 08 '12 at 14:12
There is nothing syntactically wrong with your SQL statement. If the bind parameters are valid, then the most likely explanation for the behavior you observe is that there is a CONSTRAINT that is (or would be) violated. Could be a primary key, a unique key, a foreign key, a not null constraint, or it could be a trigger that is throwing an exception. You'd really need the MySQL error information to debug this. (Another possibility is that this statement is executed in the context of a transaction that is being rolled back.) — spencer7593, Aug 08 '12 at 14:23

spencer7593 · Answer 1 · 2012-08-08T19:23:10.663

There is nothing syntactically wrong with your SQL statement. There is no specific "setting" in the MySQL server that controls this behavior, but there are some settings that can influence whether a particular INSERT statement will succeed or not, for example, the setting that allows for invalid dates (i.e. non-existent dates such as June 31), and the setting that allows for zero dates ('0000-00-00') to be stored. (This behavior has changed across versions, where the "old" (e.g. V3.23) default was to allow invalid dates, and the "newer" (e.g. v5.1) default is to disallow invalid dates.)

If the values supplied for the bind parameters are valid, then the most likely explanation for the behavior you observe is that there is a CONSTRAINT that is (or would be) violated.

This could be primary key constraint, a unique key constraint, a foreign key constraint, a NOT NULL constraint, or it could even be a trigger that is throwing an exception.

For example, the column intervention may be defined as NOT NULL and not have a default value specified.

The MySQL error information might be useful for debugging this.

Another possibility is that this statement is actually succeeding, but is being executed in the context of a transaction that is subsequently being rolled back rather than committed.)

There's not enough information in your question to tell.

I would suggest that you compare the table definitions on the two databases. Easiest way (for me) would be to grab the output from a SHOW CREATE TABLE intervention statement from each database, and look for differences. It's also possible that one of the databases has a BEFORE INSERT FOR EACH ROW trigger which is missing on the other.

score 1 · Accepted Answer · answered Aug 08 '12 at 14:38

1

I bet column intervention do not allow null values.

try to alter the table and see if it works:

ALTER table data_agreements Modify intervention varchar(50) null DEFAULT '';

and try to execute again the query. I assumed that intervention is string but, of course, you can modify it that fits your needs.

Thanks.

answered Aug 08 '12 at 14:38

John Woo

258,903
69
498
492

1

Yes, NOT NULL was the problem. On the LAMP server, it performs the insert even though the fields are NOT NULL there too. Not sure why they differ, but at least I now know the source of the problem. Thanks! – David Farrell Aug 12 '12 at 20:08

ypercubeᵀᴹ · Answer 3 · 2012-08-08T14:08:43.240

INSERT INTO data_agreements 
  (id, time) 
VALUES 
  (?, ?) ;

will run fine, if the other fields, intervention in this case, have been defined with a DEFAULT attribute or with AUTO_INCREMENT when the table was created. If not, you have to explicitly provide a value to the INSERT statement.

Check with SHOW CREATE TABLE data_agreements in the 2 installations, how the tables have been defined.

The TIMESTAMP columns of a table have a non-obvious functionality. Only one of them can have a DEFAULT CURRENT_TIMESTAMP attribute (and if you don't state that, only the first one of them in a table gets this default).

The issue you have may have been caused if you had defined one table with more than one timestamp columns and then dropped the first one.

MySQL Insert requires specifying all fields?

3 Answers3