{"action":"created","issue":{"url":"https://api.github.com/repos/simpleinvoices/simpleinvoices/issues/246","repository_url":"https://api.github.com/repos/simpleinvoices/simpleinvoices","labels_url":"https://api.github.com/repos/simpleinvoices/simpleinvoices/issues/246/labels{/name}","comments_url":"https://api.github.com/repos/simpleinvoices/simpleinvoices/issues/246/comments","events_url":"https://api.github.com/repos/simpleinvoices/simpleinvoices/issues/246/events","html_url":"https://github.com/simpleinvoices/simpleinvoices/issues/246","id":184622519,"number":246,"title":"system_defaults not updated","user":{"login":"JPG-Consulting","id":4283191,"avatar_url":"https://avatars.githubusercontent.com/u/4283191?v=3","gravatar_id":"","url":"https://api.github.com/users/JPG-Consulting","html_url":"https://github.com/JPG-Consulting","followers_url":"https://api.github.com/users/JPG-Consulting/followers","following_url":"https://api.github.com/users/JPG-Consulting/following{/other_user}","gists_url":"https://api.github.com/users/JPG-Consulting/gists{/gist_id}","starred_url":"https://api.github.com/users/JPG-Consulting/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/JPG-Consulting/subscriptions","organizations_url":"https://api.github.com/users/JPG-Consulting/orgs","repos_url":"https://api.github.com/users/JPG-Consulting/repos","events_url":"https://api.github.com/users/JPG-Consulting/events{/privacy}","received_events_url":"https://api.github.com/users/JPG-Consulting/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2016-10-22T10:20:41Z","updated_at":"2016-10-25T18:55:57Z","closed_at":null,"body":"I know this has been reported earlier but with a fresh install of `master` the system_defaults are **NOT** saved due to the `new_id` field in the table `si_system_defaults`.\r\n\r\nI've got error reporting on and it says the vale has been saved but once back to the main screen it has **NOT** been saved. Checked the database and it has **NOT** been saved.\r\n\r\nI've enabled general log in MySQL in order to log the full SQL queries, the query gets executed as requested on code so I copy and past the query directlly to MySQL and get:\r\n\r\n`#1364 - Field 'new_id' doesn't have a default value`\r\n\r\nSteps are easy... Remove all tables from mySQL, Install `master`, go through the install process and install all... and there you go, preferences can **NOT** be saved.\r\n\r\nOriginal report: https://github.com/simpleinvoices/simpleinvoices/pull/190\r\n\r\nCurrent SQL patch code:\r\n```\r\n    $patch['240']['name'] = \"si_system_defaults - add composite primary key\";\r\n    $patch['240']['patch'] = \"\r\n\t\tALTER TABLE  `\".TB_PREFIX.\"system_defaults` ADD `new_id` INT( 11 ) NOT NULL FIRST;\r\n\t\tUPDATE `\".TB_PREFIX.\"system_defaults` SET new_id = id;\r\n\t\tALTER TABLE  `\".TB_PREFIX.\"system_defaults` DROP `id`;\r\n\t\tALTER TABLE  `\".TB_PREFIX.\"system_defaults` DROP INDEX `name`;\r\n\t\tALTER TABLE  `\".TB_PREFIX.\"system_defaults` CHANGE `new_id` `id` INT( 11 ) NOT NULL;\r\n\t\tALTER TABLE  `\".TB_PREFIX.\"system_defaults` ADD PRIMARY KEY(`domain_id`,`id` );\";\r\n    $patch['240']['date'] = \"20100305\";\r\n```\r\n\r\nThe `new_id` is never removed and therefore everything fails!\r\n\r\n...and here is my MySQL log for this patch:\r\n\r\n```\r\n    2016-10-22T11:05:42.525738Z   26 Query\tSELECT * FROM si_sql_patchmanager WHERE sql_patch_ref = '240'\r\n`\r\n    2016-10-22T11:05:42.526738Z   26 Query\tALTER TABLE  `si_system_defaults` ADD `new_id` INT( 11 ) NOT NULL FIRST;\r\n\r\n    2016-10-22T11:05:42.532738Z   26 Query\tUPDATE `si_system_defaults` SET new_id = id;\r\n\r\n    2016-10-22T11:05:42.532738Z   26 Query\tALTER TABLE  `si_system_defaults` DROP `id`;\r\n\r\n    2016-10-22T11:05:42.537739Z   26 Query\tINSERT INTO si_sql_patchmanager ( sql_patch_ref , sql_patch , sql_release , sql_statement ) VALUES ('240', 'si_system_defaults - add composite primary key', '20100305', '\\r\\n\t\tALTER TABLE  `si_system_defaults` ADD `new_id` INT( 11 ) NOT NULL FIRST;\\r\\n\t\tUPDATE `si_system_defaults` SET new_id = id;\\r\\n\t\tALTER TABLE  `si_system_defaults` DROP `id`;\\r\\n\t\tALTER TABLE  `si_system_defaults` DROP INDEX `name`;\\r\\n\t\tALTER TABLE  `si_system_defaults` CHANGE `new_id` `id` INT( 11 ) NOT NULL;\\r\\n\t\tALTER TABLE  `si_system_defaults` ADD PRIMARY KEY(`domain_id`,`id` );\\r\\n\t\tALTER TABLE  `si_system_defaults` DROP `new_id`;')\r\n```\r\n\r\nAs you can see it stops when dropping `id` as it is the point where MySQL triggers an error due to being unable to remove `id` as it is part of the primary key.\r\n\r\nAfter debuging for some time here is the patch:\r\nhttps://github.com/simpleinvoices/simpleinvoices/pull/247"},"comment":{"url":"https://api.github.com/repos/simpleinvoices/simpleinvoices/issues/comments/256140107","html_url":"https://github.com/simpleinvoices/simpleinvoices/issues/246#issuecomment-256140107","issue_url":"https://api.github.com/repos/simpleinvoices/simpleinvoices/issues/246","id":256140107,"user":{"login":"apmuthu","id":2995044,"avatar_url":"https://avatars.githubusercontent.com/u/2995044?v=3","gravatar_id":"","url":"https://api.github.com/users/apmuthu","html_url":"https://github.com/apmuthu","followers_url":"https://api.github.com/users/apmuthu/followers","following_url":"https://api.github.com/users/apmuthu/following{/other_user}","gists_url":"https://api.github.com/users/apmuthu/gists{/gist_id}","starred_url":"https://api.github.com/users/apmuthu/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/apmuthu/subscriptions","organizations_url":"https://api.github.com/users/apmuthu/orgs","repos_url":"https://api.github.com/users/apmuthu/repos","events_url":"https://api.github.com/users/apmuthu/events{/privacy}","received_events_url":"https://api.github.com/users/apmuthu/received_events","type":"User","site_admin":false},"created_at":"2016-10-25T18:55:57Z","updated_at":"2016-10-25T18:55:57Z","body":"Nowhere in your patch do you change the `new_id` to `id` after dropping the old field `id` and hence your patch will cause errors in usage. Nowhere in your patch do you create a new compound PRIMARY key.\r\n\r\nIn the versions of MySQL (atleast <= 5.1), dropping a PRIMARY key field is possible and hence the existing patch does not fail.\r\n\r\nThe final version of the *si_system_defaults* table should be:\r\n![2016-10-26_002359](https://cloud.githubusercontent.com/assets/2995044/19699905/c0efbc2a-9b12-11e6-87bf-3fcadbe174e4.png)\r\n"}}
{"actor":{"display_login":"apmuthu"}}