Missing insert value on not null without default should fail

[bigteejay]

I searched the issues and checked the README and have not yet found any mention of this.

Is it expected to be the case that columns with a NOT NULL definition and no default value should be allowed/inserted with an empty string when none was provided? My hope/expectation would be that it would return the error from the DB when it attempts this (rather than just inserting a blank string). I did see that for most create/update actions, null is returned when the call fails (which can be a handy indicator of some error condition, but only when it is null and values aren't just remapped to blank strings and inserted silently.)

Thoughts?

[bigteejay]

For context, I have a table...

CREATE TABLE `user_table` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(30) NOT NULL,
  `time_submitted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

And issue post (via RESTClient) [{"user_id":"test"}] ...which succeeds and returns the ID.

Yet, checking the db, I see the row, with the value supplied, and blanks for the remaining NOT NULL fields.

The table previously allowed some nulls and had defaults for not null of '' (empty string). Is there any sort of caching strategy going on that might still have the old DDL active, causing it to not pick up the recent changes and properly fail when omiting required fields now?

[mevdschee]

Strict mode controls how MySQL handles input values that are invalid or missing... A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition...

This is from the docs of MySQL. Your database is not run in strict mode. Strict mode is not the default, but it has a more sensible behavior.

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_strict_all_tables

[mevdschee]

I guess we could do this check based on the database structure in a generic way. Looking at default value and nullable properties of the fields. Registered as a feature request.

[mevdschee]

Note that v1 has no ddl cache. In v2 a ddl cache is added for efficiency reasons.

[bigteejay]

Somewhat related this would be the notion of an easy to configure method of marking a field read-only (and thereby optionally ignoring it or failing). I suspect this may exist with the...

$table_authorizer = isset($table_authorizer)?$table_authorizer:null;
$record_filter = isset($record_filter)?$record_filter:null;
$column_authorizer = isset($column_authorizer)?$column_authorizer:null;
$tenancy_function = isset($tenancy_function)?$tenancy_function:null;
$input_sanitizer = isset($input_sanitizer)?$input_sanitizer:null;
$input_validator = isset($input_validator)?$input_validator:null;

....functionality. But, how escapes me thus far from what I can glean from the examples I found (if they're still accurate.)

[mevdschee]

I guess you could use the "before" handler to remove a certain field from the input, which would work in combination with a default value in the database.