{"action":"created","issue":{"url":"https://api.github.com/repos/textpattern/textpattern/issues/414","labels_url":"https://api.github.com/repos/textpattern/textpattern/issues/414/labels{/name}","comments_url":"https://api.github.com/repos/textpattern/textpattern/issues/414/comments","events_url":"https://api.github.com/repos/textpattern/textpattern/issues/414/events","html_url":"https://github.com/textpattern/textpattern/issues/414","id":44882248,"number":414,"title":"SQL Field definitions are wrong: does not run on default >= MySQL 5.6 distributions ","user":{"login":"ghost","id":10137,"avatar_url":"https://avatars.githubusercontent.com/u/10137?v=3","gravatar_id":"","url":"https://api.github.com/users/ghost","html_url":"https://github.com/ghost","followers_url":"https://api.github.com/users/ghost/followers","following_url":"https://api.github.com/users/ghost/following{/other_user}","gists_url":"https://api.github.com/users/ghost/gists{/gist_id}","starred_url":"https://api.github.com/users/ghost/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ghost/subscriptions","organizations_url":"https://api.github.com/users/ghost/orgs","repos_url":"https://api.github.com/users/ghost/repos","events_url":"https://api.github.com/users/ghost/events{/privacy}","received_events_url":"https://api.github.com/users/ghost/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/textpattern/textpattern/labels/Bug","name":"Bug","color":"e11d21"},{"url":"https://api.github.com/repos/textpattern/textpattern/labels/Priority:%20high","name":"Priority: high","color":"fad8c7"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":11,"created_at":"2014-10-04T13:01:19Z","updated_at":"2015-08-15T14:39:44Z","closed_at":null,"body":"_From [jukka.m.svahn](https://code.google.com/u/jukka.m.svahn/) on April 10, 2014 11:39:38_\n\nAll fields must either:\r\n\r\n* Have default value\r\n* Or set as NULL\r\n\r\nCurrently Textpattern is abusing:\r\n\r\n> If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.\n\n_Original issue: http://code.google.com/p/textpattern/issues/detail?id=410_"},"comment":{"url":"https://api.github.com/repos/textpattern/textpattern/issues/comments/131388707","html_url":"https://github.com/textpattern/textpattern/issues/414#issuecomment-131388707","issue_url":"https://api.github.com/repos/textpattern/textpattern/issues/414","id":131388707,"user":{"login":"vanmelick","id":10106358,"avatar_url":"https://avatars.githubusercontent.com/u/10106358?v=3","gravatar_id":"","url":"https://api.github.com/users/vanmelick","html_url":"https://github.com/vanmelick","followers_url":"https://api.github.com/users/vanmelick/followers","following_url":"https://api.github.com/users/vanmelick/following{/other_user}","gists_url":"https://api.github.com/users/vanmelick/gists{/gist_id}","starred_url":"https://api.github.com/users/vanmelick/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vanmelick/subscriptions","organizations_url":"https://api.github.com/users/vanmelick/orgs","repos_url":"https://api.github.com/users/vanmelick/repos","events_url":"https://api.github.com/users/vanmelick/events{/privacy}","received_events_url":"https://api.github.com/users/vanmelick/received_events","type":"User","site_admin":false},"created_at":"2015-08-15T14:39:44Z","updated_at":"2015-08-15T14:39:44Z","body":"You can set STRICT_TRANS_TABLES on older MySQL versions as well. I did so and performed a few tests which lead me to believe that the assumption in this issue and the resulting fix are incorrect.\r\n\r\nTable field definitions don't have to have either a default value or accept NULL.\r\nThere is a difference between:\r\n1. `NOT NULL default 1`: cannot be set to null and defaults to one if value not provided\r\n2. `NOT NULL`: cannot be set to null, so a value must be given during insert\r\n3. `default 1`: can be set to null (if supplied as value explicitly), but defaults to 1\r\n\r\nHaving NOT NULL without a default will still work in MySQL 5.6 with STRICT_TRANS_TABLES set, but you have to make sure you provide a value for that column when inserting.\r\n\r\nFixing this by adding a default in the table field definition is only correct if there really is a sensible default value for that column, but if it's a column where the value has to be supplied on inserts, setting a default just makes bugs in your code harder to find, because you want to get an error if you leave out a column that is essential. \r\n\r\nFor example, in the txp_form table you don't want the 'name' to default to an empty string, because it's the primary key, which should be unique. Making it default to an empty string leads to SQL errors about a duplicate entry, which is more confusing than getting an SQL error about column that cannot be NULL, which you would get without a default.\r\n"}}