{"action":"created","issue":{"url":"https://api.github.com/repos/pingcap/tidb/issues/7863","repository_url":"https://api.github.com/repos/pingcap/tidb","labels_url":"https://api.github.com/repos/pingcap/tidb/issues/7863/labels{/name}","comments_url":"https://api.github.com/repos/pingcap/tidb/issues/7863/comments","events_url":"https://api.github.com/repos/pingcap/tidb/issues/7863/events","html_url":"https://github.com/pingcap/tidb/pull/7863","id":368568182,"node_id":"MDExOlB1bGxSZXF1ZXN0MjIxNzE0MjQ4","number":7863,"title":"executor: add an variable to compatible with MySQL insert for OGG","user":{"login":"jackysp","id":4352397,"node_id":"MDQ6VXNlcjQzNTIzOTc=","avatar_url":"https://avatars1.githubusercontent.com/u/4352397?v=4","gravatar_id":"","url":"https://api.github.com/users/jackysp","html_url":"https://github.com/jackysp","followers_url":"https://api.github.com/users/jackysp/followers","following_url":"https://api.github.com/users/jackysp/following{/other_user}","gists_url":"https://api.github.com/users/jackysp/gists{/gist_id}","starred_url":"https://api.github.com/users/jackysp/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jackysp/subscriptions","organizations_url":"https://api.github.com/users/jackysp/orgs","repos_url":"https://api.github.com/users/jackysp/repos","events_url":"https://api.github.com/users/jackysp/events{/privacy}","received_events_url":"https://api.github.com/users/jackysp/received_events","type":"User","site_admin":false},"labels":[{"id":970807266,"node_id":"MDU6TGFiZWw5NzA4MDcyNjY=","url":"https://api.github.com/repos/pingcap/tidb/labels/component/executor","name":"component/executor","color":"000000","default":false},{"id":564600738,"node_id":"MDU6TGFiZWw1NjQ2MDA3Mzg=","url":"https://api.github.com/repos/pingcap/tidb/labels/status/LGT2","name":"status/LGT2","color":"5319e7","default":false},{"id":770872605,"node_id":"MDU6TGFiZWw3NzA4NzI2MDU=","url":"https://api.github.com/repos/pingcap/tidb/labels/status/all%20tests%20passed","name":"status/all tests passed","color":"2cbe4e","default":false},{"id":541179553,"node_id":"MDU6TGFiZWw1NDExNzk1NTM=","url":"https://api.github.com/repos/pingcap/tidb/labels/type/compatibility","name":"type/compatibility","color":"e99695","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":6,"created_at":"2018-10-10T09:16:20Z","updated_at":"2018-10-11T02:44:41Z","closed_at":null,"author_association":"MEMBER","pull_request":{"url":"https://api.github.com/repos/pingcap/tidb/pulls/7863","html_url":"https://github.com/pingcap/tidb/pull/7863","diff_url":"https://github.com/pingcap/tidb/pull/7863.diff","patch_url":"https://github.com/pingcap/tidb/pull/7863.patch"},"body":"<!--\r\nThank you for contributing to TiDB! Please read TiDB's [CONTRIBUTING](https://github.com/pingcap/tidb/blob/master/CONTRIBUTING.md) document **BEFORE** filing this PR.\r\n-->\r\n\r\n### What problem does this PR solve? <!--add issue link with summary if exists-->\r\nTiDB optimizes it insert flow with checking unique keys when committing the transaction which will make it not compatible with MySQL. For OGG it will cause some unexpected errors.\r\nThere is an option called \"handlecollisions\" in OGG. If it is ON, OGG will try to insert one record in the transaction when it meets some duplicated key error, OGG will handle the collision error and convert the insert statement to an update statement, then commit the transaction. It is the same behavior of insert on duplicate key update in MySQL or upset in PG, but OGG seems don't want to use these non-standard SQLs. It expects to meet the insert error and handle it. But TiDB checks the unique constrait when committing the transaction for insert statement. See the following example:\r\n\r\n```\r\ncreate table t (i int key);\r\ninsert into t values (1);\r\nbegin;\r\ninsert into t values (1);            -- OGG expects to meet the error here!\r\nupdate t set i = 1 where i = 1;\r\ncommit;                              -- TiDB returns the error here!\r\n```\r\n\r\nBtw: It really hurts the performance.\r\n\r\n### What is changed and how it works?\r\nAdd an variables to compatible with MySQL insert behavior.\r\n\r\n### Check List <!--REMOVE the items that are not applicable-->\r\n\r\nTests <!-- At least one of them must be included. -->\r\n\r\n - Unit test\r\n\r\nCode changes\r\n\r\n - Has exported function/method change\r\n\r\nSide effects\r\n\r\n - Possible performance regression\r\nIf \"ON\" it will cause at least 10x slower than \"OFF\" when bulking insert.\r\n\r\nRelated changes\r\n\r\n - Need to cherry-pick to the release branch\r\n - Need to update the documentation\r\n - Need to be included in the release note\r\n"},"comment":{"url":"https://api.github.com/repos/pingcap/tidb/issues/comments/428800067","html_url":"https://github.com/pingcap/tidb/pull/7863#issuecomment-428800067","issue_url":"https://api.github.com/repos/pingcap/tidb/issues/7863","id":428800067,"node_id":"MDEyOklzc3VlQ29tbWVudDQyODgwMDA2Nw==","user":{"login":"shenli","id":1192573,"node_id":"MDQ6VXNlcjExOTI1NzM=","avatar_url":"https://avatars0.githubusercontent.com/u/1192573?v=4","gravatar_id":"","url":"https://api.github.com/users/shenli","html_url":"https://github.com/shenli","followers_url":"https://api.github.com/users/shenli/followers","following_url":"https://api.github.com/users/shenli/following{/other_user}","gists_url":"https://api.github.com/users/shenli/gists{/gist_id}","starred_url":"https://api.github.com/users/shenli/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/shenli/subscriptions","organizations_url":"https://api.github.com/users/shenli/orgs","repos_url":"https://api.github.com/users/shenli/repos","events_url":"https://api.github.com/users/shenli/events{/privacy}","received_events_url":"https://api.github.com/users/shenli/received_events","type":"User","site_admin":false},"created_at":"2018-10-11T02:44:41Z","updated_at":"2018-10-11T02:44:41Z","author_association":"MEMBER","body":"That's could hurt the performance of the whole cluster. It is better to set the server scope (or even user scope) variable."}}
{"actor":{"display_login":"shenli"}}