{"action":"created","issue":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/23176","repository_url":"https://api.github.com/repos/cockroachdb/cockroach","labels_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/23176/labels{/name}","comments_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/23176/comments","events_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/23176/events","html_url":"https://github.com/cockroachdb/cockroach/issues/23176","id":300768606,"number":23176,"title":"1.1.5: Issue updating row - index seems broken, and select queries shows inconsistent results / duplicate rows with wrong data","user":{"login":"bsnyder788","id":3028415,"avatar_url":"https://avatars3.githubusercontent.com/u/3028415?v=4","gravatar_id":"","url":"https://api.github.com/users/bsnyder788","html_url":"https://github.com/bsnyder788","followers_url":"https://api.github.com/users/bsnyder788/followers","following_url":"https://api.github.com/users/bsnyder788/following{/other_user}","gists_url":"https://api.github.com/users/bsnyder788/gists{/gist_id}","starred_url":"https://api.github.com/users/bsnyder788/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bsnyder788/subscriptions","organizations_url":"https://api.github.com/users/bsnyder788/orgs","repos_url":"https://api.github.com/users/bsnyder788/repos","events_url":"https://api.github.com/users/bsnyder788/events{/privacy}","received_events_url":"https://api.github.com/users/bsnyder788/received_events","type":"User","site_admin":false},"labels":[{"id":78928345,"url":"https://api.github.com/repos/cockroachdb/cockroach/labels/bug","name":"bug","color":"fc2929","default":true},{"id":560660669,"url":"https://api.github.com/repos/cockroachdb/cockroach/labels/high%20priority","name":"high priority","color":"b60205","default":false},{"id":239311849,"url":"https://api.github.com/repos/cockroachdb/cockroach/labels/investigation","name":"investigation","color":"eb6420","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/cockroachdb/cockroach/milestones/11","html_url":"https://github.com/cockroachdb/cockroach/milestone/11","labels_url":"https://api.github.com/repos/cockroachdb/cockroach/milestones/11/labels","id":2543483,"number":11,"title":"2.0","description":"","creator":{"login":"dianasaur323","id":5768024,"avatar_url":"https://avatars0.githubusercontent.com/u/5768024?v=4","gravatar_id":"","url":"https://api.github.com/users/dianasaur323","html_url":"https://github.com/dianasaur323","followers_url":"https://api.github.com/users/dianasaur323/followers","following_url":"https://api.github.com/users/dianasaur323/following{/other_user}","gists_url":"https://api.github.com/users/dianasaur323/gists{/gist_id}","starred_url":"https://api.github.com/users/dianasaur323/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dianasaur323/subscriptions","organizations_url":"https://api.github.com/users/dianasaur323/orgs","repos_url":"https://api.github.com/users/dianasaur323/repos","events_url":"https://api.github.com/users/dianasaur323/events{/privacy}","received_events_url":"https://api.github.com/users/dianasaur323/received_events","type":"User","site_admin":false},"open_issues":244,"closed_issues":499,"state":"open","created_at":"2017-05-25T18:08:15Z","updated_at":"2018-02-28T14:12:49Z","due_on":null,"closed_at":null},"comments":3,"created_at":"2018-02-27T19:49:20Z","updated_at":"2018-02-28T14:47:01Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"**BUG REPORT**\r\n\r\n% cockroach version                                                                                                                                                                                                                                                               cockroach\r\nBuild Tag:    v1.1.5\r\nBuild Time:   2018/02/05 17:40:57\r\nDistribution: CCL\r\nPlatform:     linux amd64\r\nGo Version:   go1.8.3\r\nC Compiler:   gcc 6.3.0\r\nBuild SHA-1:  ec52ded95a5235d71f26b6cbc59924bd6733fb3a\r\nBuild Type:   release-gnu\r\n\r\n2. Please describe the issue you observed:\r\n\r\nWe have a strange issue where an index is out of sync and is violating a unique constraint and won't let us easily, manually fix the issue.\r\n\r\n```\r\nroot@cockroach:26257/ecs> SELECT * FROM task@task_by_location_synced WHERE ((location_id = 'xxx') AND (synchronized = false)) AND (initiation_time = '2018-02-27 15:19:24.281+00:00');\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n|                        uuid                         |    crm     |     location_id      | active |           end_time           |                                    entity_uuid                                    | initiated_from_ecs |        initiation_time        | message |    operation    | operation_description |                                 org_uuid                                 | parent_task_uuid | progress |          start_time          | status  | synchronized |                       task_id                        | task_type | user_full_name | username |\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n| urn:yyy:task:2a11a3e9-a071-4624-94a7-5fcbd4fb0657 | 0000 | xxx | false  | 2018-02-25 20:28:40.28+00:00 | xxx:urn:fake:entity:abcd | false              | 2018-02-27 15:19:24.281+00:00 | NULL    | fakeType | fakeType       | xxx:urn:fake:parent:abcd | NULL             |      100 | 2018-02-25 20:05:12.83+00:00 | success | false        | urn:yyy:task:d2e4018b-ebfc-4f26-ac21-cfc713f359c1 | test       | System         | tester    |\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n(1 row)\r\n\r\nTime: 128.560204ms\r\n```\r\n\r\nEverything looks fine in that above return.  But if you change synchronized in the query to \"true\", we get:\r\n\r\n```\r\nroot@cockroach:26257/ecs> SELECT * FROM task@task_by_location_synced WHERE ((location_id = 'xxx') AND (synchronized = true)) AND (initiation_time = '2018-02-27 15:19:24.281+00:00');\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n|                        uuid                         |    crm     |     location_id      | active |           end_time           |                                    entity_uuid                                    | initiated_from_ecs |        initiation_time        | message |    operation    | operation_description |                                 org_uuid                                 | parent_task_uuid | progress |          start_time          | status  | synchronized |                       task_id                        | task_type | user_full_name | username |\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n| urn:yyy:task:2a11a3e9-a071-4624-94a7-5fcbd4fb0657 | 0000 | xxx | false  | 2018-02-25 20:28:40.28+00:00 | xxx:urn:fake:entity:abc | false              | 2018-02-27 15:19:24.281+00:00 | NULL    | fakeType | fakeType       | xxx:urn:fake:parent:abcd | NULL             |      100 | 2018-02-25 20:05:12.83+00:00 | success | false        | urn:yyy:task:d2e4018b-ebfc-4f26-ac21-cfc713f359c1 | test       | System         | tester    |\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n(1 row)\r\n\r\nTime: 120.803796ms\r\n```\r\n\r\nAs you can see even though we query for synchronized=true directly the resulting data shows that synchronized is still false.\r\n\r\nIf I query it in the following manner the data comes back like so with it being correct in terms of what I queried for:\r\n\r\n```\r\nroot@cockroach:26257/ecs> SELECT synchronized FROM task@task_by_location_synced WHERE ((location_id = 'xxx') AND (synchronized = false)) AND (initiation_time = '2018-02-27 15:19:24.281+00:00');\r\n+--------------+\r\n| synchronized |\r\n+--------------+\r\n| false        |\r\n+--------------+\r\n(1 row)\r\n\r\nTime: 37.420711ms\r\n\r\nroot@cockroach:26257/ecs> SELECT synchronized FROM task@task_by_location_synced WHERE ((location_id = 'xxx') AND (synchronized = true)) AND (initiation_time = '2018-02-27 15:19:24.281+00:00');\r\n+--------------+\r\n| synchronized |\r\n+--------------+\r\n| true         |\r\n+--------------+\r\n(1 row)\r\n\r\nTime: 41.600935ms\r\n```\r\n\r\nIf I query just the base table on the PRIMARY key I get the following:\r\n\r\n```\r\nroot@cockroach:26257/ecs> SELECT * FROM task WHERE \"uuid\" = 'urn:xxx:task:2a11a3e9-a071-4624-94a7-5fcbd4fb0657';\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n|                        uuid                         |    crm     |     location_id      | active |           end_time           |                                    entity_uuid                                    | initiated_from_ecs |        initiation_time        | message |    operation    | operation_description |                                 org_uuid                                 | parent_task_uuid | progress |          start_time          | status  | synchronized |                       task_id                        | task_type | user_full_name | username |\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n| urn:xxx:task:2a11a3e9-a071-4624-94a7-5fcbd4fb0657 | 0000 | xxx | false  | 2018-02-25 20:28:40.28+00:00 | xxx:urn:fake:entity:abc | false              | 2018-02-27 15:19:24.281+00:00 | NULL    | fakeType | fakeType       | xxx:urn:fake:parent:abcd | NULL             |      100 | 2018-02-25 20:05:12.83+00:00 | success | false        | urn:yyy:task:d2e4018b-ebfc-4f26-ac21-cfc713f359c1 | test       | System         | tester    |\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n(1 row)\r\n\r\nTime: 134.96595ms\r\n```\r\n\r\nIf I try to manually update the base row to show synchronized = false, I get the following error:\r\n\r\n```\r\nroot@cockroach:26257/ecs> UPDATE task SET synchronized = true WHERE \"uuid\" = 'urn:xxx:task:2a11a3e9-a071-4624-94a7-5fcbd4fb0657';\r\npq: duplicate key value (location_id,synchronized,initiation_time)=('urn:xxx:task:2a11a3e9-a071-4624-94a7-5fcbd4fb0657',true,'2018-02-27 15:19:24.281+00:00') violates unique constraint \"task_by_location_synced\"\r\n```\r\n\r\nThus, our row is stuck in a bad state where that field cannot be updated.\r\n\r\nThe schema is as follows:\r\n```\r\n+-----------------------+-----------+-------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\r\n|         Field         |   Type    | Null  | Default |                                                                                               Indices                                                                                                |\r\n+-----------------------+-----------+-------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\r\n| uuid                  | STRING    | false | NULL    | {\"primary\",\"task_by_native_id\",\"task_by_location_synced\",\"task_by_entity_synced\",\"task_by_org_synced\",\"task_by_org\",\"task_by_org_type\",\"task_by_entity\",\"task_by_org_user\",\"continuity_task_by_org\"} |\r\n| crm                   | STRING    | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| location_id           | STRING    | true  | NULL    | {\"task_by_native_id\",\"task_by_location_synced\"}                                                                                                                                                      |\r\n| active                | BOOL      | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| end_time              | TIMESTAMP | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| entity_uuid           | STRING    | true  | NULL    | {\"task_by_entity_synced\",\"task_by_entity\"}                                                                                                                                                           |\r\n| initiated_from_ecs    | BOOL      | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| initiation_time       | TIMESTAMP | true  | NULL    | {\"task_by_native_id\",\"task_by_location_synced\",\"task_by_entity_synced\",\"task_by_org_synced\",\"task_by_org\",\"task_by_org_type\",\"task_by_entity\",\"task_by_org_user\",\"continuity_task_by_org\"}           |\r\n| message               | STRING    | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| operation             | STRING    | true  | NULL    | {\"continuity_task_by_org\"}                                                                                                                                                                           |\r\n| operation_description | STRING    | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| org_uuid              | STRING    | true  | NULL    | {\"task_by_org_synced\",\"task_by_org\",\"task_by_org_type\",\"task_by_org_user\",\"continuity_task_by_org\"}                                                                                                  |\r\n| parent_task_uuid      | STRING    | true  | NULL    | {\"continuity_task_by_org\"}                                                                                                                                                                           |\r\n| progress              | INT       | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| start_time            | TIMESTAMP | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| status                | STRING    | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| synchronized          | BOOL      | true  | NULL    | {\"task_by_location_synced\",\"task_by_entity_synced\",\"task_by_org_synced\",\"continuity_task_by_org\"}                                                                                                    |\r\n| task_id               | STRING    | true  | NULL    | {\"task_by_native_id\"}                                                                                                                                                                                |\r\n| task_type             | STRING    | true  | NULL    | {\"task_by_org_type\"}                                                                                                                                                                                 |\r\n| user_full_name        | STRING    | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| username              | STRING    | true  | NULL    | {\"task_by_org_user\"}                                                                                                                                                                                 |\r\n+-----------------------+-----------+-------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\r\n\r\nroot@cockroach:26257/ecs> show indexes from task;\r\n+-------+-------------------------+--------+-----+------------------+-----------+---------+----------+\r\n| Table |          Name           | Unique | Seq |      Column      | Direction | Storing | Implicit |\r\n+-------+-------------------------+--------+-----+------------------+-----------+---------+----------+\r\n| task  | primary                 | true   |   1 | uuid             | ASC       | false   | false    |\r\n| task  | task_by_native_id       | false  |   1 | location_id      | ASC       | false   | false    |\r\n| task  | task_by_native_id       | false  |   2 | task_id          | ASC       | false   | false    |\r\n| task  | task_by_native_id       | false  |   3 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_native_id       | false  |   4 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_location_synced | false  |   1 | location_id      | ASC       | false   | false    |\r\n| task  | task_by_location_synced | false  |   2 | synchronized     | ASC       | false   | false    |\r\n| task  | task_by_location_synced | false  |   3 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_location_synced | false  |   4 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_entity_synced   | false  |   1 | entity_uuid      | ASC       | false   | false    |\r\n| task  | task_by_entity_synced   | false  |   2 | synchronized     | ASC       | false   | false    |\r\n| task  | task_by_entity_synced   | false  |   3 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_entity_synced   | false  |   4 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_org_synced      | false  |   1 | org_uuid         | ASC       | false   | false    |\r\n| task  | task_by_org_synced      | false  |   2 | synchronized     | ASC       | false   | false    |\r\n| task  | task_by_org_synced      | false  |   3 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_org_synced      | false  |   4 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_org             | false  |   1 | org_uuid         | ASC       | false   | false    |\r\n| task  | task_by_org             | false  |   2 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_org             | false  |   3 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_org_type        | false  |   1 | org_uuid         | ASC       | false   | false    |\r\n| task  | task_by_org_type        | false  |   2 | task_type        | ASC       | false   | false    |\r\n| task  | task_by_org_type        | false  |   3 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_org_type        | false  |   4 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_entity          | false  |   1 | entity_uuid      | ASC       | false   | false    |\r\n| task  | task_by_entity          | false  |   2 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_entity          | false  |   3 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_org_user        | false  |   1 | org_uuid         | ASC       | false   | false    |\r\n| task  | task_by_org_user        | false  |   2 | username         | ASC       | false   | false    |\r\n| task  | task_by_org_user        | false  |   3 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_org_user        | false  |   4 | uuid             | ASC       | false   | true     |\r\n| task  | continuity_task_by_org  | false  |   1 | org_uuid         | ASC       | false   | false    |\r\n| task  | continuity_task_by_org  | false  |   2 | parent_task_uuid | ASC       | false   | false    |\r\n| task  | continuity_task_by_org  | false  |   3 | synchronized     | ASC       | false   | false    |\r\n| task  | continuity_task_by_org  | false  |   4 | operation        | ASC       | false   | false    |\r\n| task  | continuity_task_by_org  | false  |   5 | initiation_time  | DESC      | false   | false    |\r\n| task  | continuity_task_by_org  | false  |   6 | uuid             | ASC       | false   | true     |\r\n+-------+-------------------------+--------+-----+------------------+-----------+---------+----------+\r\n(37 rows)\r\n```\r\n\r\nLet me know if there is anything else I can provide to help get to the bottom of this issue. I am willing to screen share if necessary, etc.\r\n\r\nThanks!\r\n"},"comment":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/comments/369261723","html_url":"https://github.com/cockroachdb/cockroach/issues/23176#issuecomment-369261723","issue_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/23176","id":369261723,"user":{"login":"bsnyder788","id":3028415,"avatar_url":"https://avatars3.githubusercontent.com/u/3028415?v=4","gravatar_id":"","url":"https://api.github.com/users/bsnyder788","html_url":"https://github.com/bsnyder788","followers_url":"https://api.github.com/users/bsnyder788/followers","following_url":"https://api.github.com/users/bsnyder788/following{/other_user}","gists_url":"https://api.github.com/users/bsnyder788/gists{/gist_id}","starred_url":"https://api.github.com/users/bsnyder788/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bsnyder788/subscriptions","organizations_url":"https://api.github.com/users/bsnyder788/orgs","repos_url":"https://api.github.com/users/bsnyder788/repos","events_url":"https://api.github.com/users/bsnyder788/events{/privacy}","received_events_url":"https://api.github.com/users/bsnyder788/received_events","type":"User","site_admin":false},"created_at":"2018-02-28T14:47:01Z","updated_at":"2018-02-28T14:47:01Z","author_association":"CONTRIBUTOR","body":"Also, after the delete and before the re-insert, the following queries do correctly return 0 rows.\r\n\r\n```SELECT * FROM task@task_by_location_synced WHERE ((location_id = 'man01.ilandcloud.com') AND (synchronized = true)) AND (initiation_time = '2018-02-27 15:19:24.281+00:00');```\r\n\r\n```SELECT * FROM task@task_by_location_synced WHERE ((location_id = 'man01.ilandcloud.com') AND (synchronized = false)) AND (initiation_time = '2018-02-27 15:19:24.281+00:00');```\r\n\r\nAfter the re-insert they are back to the exact behavior of the opening comment.\r\n"}}
{"actor":{"display_login":"bsnyder788"}}