{"action":"created","issue":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/28948","repository_url":"https://api.github.com/repos/cockroachdb/cockroach","labels_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/28948/labels{/name}","comments_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/28948/comments","events_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/28948/events","html_url":"https://github.com/cockroachdb/cockroach/issues/28948","id":352885958,"node_id":"MDU6SXNzdWUzNTI4ODU5NTg=","number":28948,"title":"cli,sql: `cockroach dump` is broken and cannot be used to export all data","user":{"login":"knz","id":642886,"node_id":"MDQ6VXNlcjY0Mjg4Ng==","avatar_url":"https://avatars3.githubusercontent.com/u/642886?v=4","gravatar_id":"","url":"https://api.github.com/users/knz","html_url":"https://github.com/knz","followers_url":"https://api.github.com/users/knz/followers","following_url":"https://api.github.com/users/knz/following{/other_user}","gists_url":"https://api.github.com/users/knz/gists{/gist_id}","starred_url":"https://api.github.com/users/knz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/knz/subscriptions","organizations_url":"https://api.github.com/users/knz/orgs","repos_url":"https://api.github.com/users/knz/repos","events_url":"https://api.github.com/users/knz/events{/privacy}","received_events_url":"https://api.github.com/users/knz/received_events","type":"User","site_admin":false},"labels":[{"id":916404262,"node_id":"MDU6TGFiZWw5MTY0MDQyNjI=","url":"https://api.github.com/repos/cockroachdb/cockroach/labels/A-cli","name":"A-cli","color":"4e7f05","default":false},{"id":78928345,"node_id":"MDU6TGFiZWw3ODkyODM0NQ==","url":"https://api.github.com/repos/cockroachdb/cockroach/labels/C-bug","name":"C-bug","color":"c5def5","default":false},{"id":469980185,"node_id":"MDU6TGFiZWw0Njk5ODAxODU=","url":"https://api.github.com/repos/cockroachdb/cockroach/labels/S-3-ux-surprise","name":"S-3-ux-surprise","color":"f07700","default":false}],"state":"open","locked":false,"assignee":{"login":"rolandcrosby","id":60245,"node_id":"MDQ6VXNlcjYwMjQ1","avatar_url":"https://avatars3.githubusercontent.com/u/60245?v=4","gravatar_id":"","url":"https://api.github.com/users/rolandcrosby","html_url":"https://github.com/rolandcrosby","followers_url":"https://api.github.com/users/rolandcrosby/followers","following_url":"https://api.github.com/users/rolandcrosby/following{/other_user}","gists_url":"https://api.github.com/users/rolandcrosby/gists{/gist_id}","starred_url":"https://api.github.com/users/rolandcrosby/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rolandcrosby/subscriptions","organizations_url":"https://api.github.com/users/rolandcrosby/orgs","repos_url":"https://api.github.com/users/rolandcrosby/repos","events_url":"https://api.github.com/users/rolandcrosby/events{/privacy}","received_events_url":"https://api.github.com/users/rolandcrosby/received_events","type":"User","site_admin":false},"assignees":[{"login":"rolandcrosby","id":60245,"node_id":"MDQ6VXNlcjYwMjQ1","avatar_url":"https://avatars3.githubusercontent.com/u/60245?v=4","gravatar_id":"","url":"https://api.github.com/users/rolandcrosby","html_url":"https://github.com/rolandcrosby","followers_url":"https://api.github.com/users/rolandcrosby/followers","following_url":"https://api.github.com/users/rolandcrosby/following{/other_user}","gists_url":"https://api.github.com/users/rolandcrosby/gists{/gist_id}","starred_url":"https://api.github.com/users/rolandcrosby/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rolandcrosby/subscriptions","organizations_url":"https://api.github.com/users/rolandcrosby/orgs","repos_url":"https://api.github.com/users/rolandcrosby/repos","events_url":"https://api.github.com/users/rolandcrosby/events{/privacy}","received_events_url":"https://api.github.com/users/rolandcrosby/received_events","type":"User","site_admin":false},{"login":"piyush-singh","id":21692034,"node_id":"MDQ6VXNlcjIxNjkyMDM0","avatar_url":"https://avatars0.githubusercontent.com/u/21692034?v=4","gravatar_id":"","url":"https://api.github.com/users/piyush-singh","html_url":"https://github.com/piyush-singh","followers_url":"https://api.github.com/users/piyush-singh/followers","following_url":"https://api.github.com/users/piyush-singh/following{/other_user}","gists_url":"https://api.github.com/users/piyush-singh/gists{/gist_id}","starred_url":"https://api.github.com/users/piyush-singh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/piyush-singh/subscriptions","organizations_url":"https://api.github.com/users/piyush-singh/orgs","repos_url":"https://api.github.com/users/piyush-singh/repos","events_url":"https://api.github.com/users/piyush-singh/events{/privacy}","received_events_url":"https://api.github.com/users/piyush-singh/received_events","type":"User","site_admin":false}],"milestone":null,"comments":1,"created_at":"2018-08-22T10:06:31Z","updated_at":"2018-08-28T18:52:17Z","closed_at":null,"author_association":"MEMBER","body":"tldr: `cockroach dump` is a \"best effort\" tool which is unable to dump some types of data out of CockroachDB. It is even rather brittle. There is no guarantee it can migrate data out of CockroachDB, but it can even break between versions without tests catching the breakage. **Users may be disappointed to discover that CockroachDB does not provide a free/open source data export facility** -- `cockroach dump` is not a suitable offering.\r\n\r\n### Details\r\n\r\n`cockroach dump` has an \"interesting\" design in that it performs dumps\r\nas follows:\r\n\r\n- obtains the SQL type of names via `information_schema.columns`;\r\n- independently, sends a SELECT query to the table over pgwire;\r\n- when the data comes back, it instanciates `Datums` by parsing the\r\n  data received from pgwire; which parser to use is determined\r\n  by a switch on the type name string obtained from\r\n  `information_schema.columns`;\r\n- it then uses a datum formatter to write the SQL representation\r\n  of the datums into the dump.\r\n\r\nThere are major, major, irremediable problems with this approach:\r\n\r\n- the pgwire conversion of the data is lossy. That's\r\n  irremediable. Because of this `cockroach dump` can never fully\r\n  guarantee it can dump a table accurately. Really, `cockroach dump` should\r\n  read the K/V pairs in binary format and decode them losslessly as datums.\r\n\r\n- `lib/pq` which is used to obtain the values over pgwire doesn't\r\n  necessarily know about all the types supported by the CockroachDB\r\n  node on the other side. When it does not know of a type, it will pass the pgwire\r\n  encoded bytes as-is as a Go `[]byte` to the `cockroach dump` reader,\r\n  which won't have any logic to deal further with that. There are two\r\n  compounded problems here:\r\n\r\n  - `cockroach dump` should really talk over pgwire directly instead\r\n    of using `lib/pq`.\r\n  - `cockroach dump` should use the `pgwire` package (the\r\n    encode/decode functions therein) to ensure it gets the same data\r\n    that was sent. This may be insufficient though because\r\n    we don't guarantee exact roundtrip, see the point above.\r\n\r\n- there is no guarantee whatsoever that `information_schema.columns`\r\n  contains enough information to decide on an in-memory value type for\r\n  CockroachDB.\r\n\r\n  PRs #28945 and #28949 are attempting to make `cockroach dump`\r\n  more resistant here by providing it a CockroachDB-specific extension\r\n  to `information_schema.columns`, called `crdb_sql_type`. However,\r\n  what `cockroach dump` should really do is go get the descriptor\r\n  protobuf, extract the actual `sqlbase.ColumnType` from that, and\r\n  then use `(*ColumnType).ToDatumType()`. "},"comment":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/comments/416700134","html_url":"https://github.com/cockroachdb/cockroach/issues/28948#issuecomment-416700134","issue_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/28948","id":416700134,"node_id":"MDEyOklzc3VlQ29tbWVudDQxNjcwMDEzNA==","user":{"login":"mjibson","id":41181,"node_id":"MDQ6VXNlcjQxMTgx","avatar_url":"https://avatars1.githubusercontent.com/u/41181?v=4","gravatar_id":"","url":"https://api.github.com/users/mjibson","html_url":"https://github.com/mjibson","followers_url":"https://api.github.com/users/mjibson/followers","following_url":"https://api.github.com/users/mjibson/following{/other_user}","gists_url":"https://api.github.com/users/mjibson/gists{/gist_id}","starred_url":"https://api.github.com/users/mjibson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mjibson/subscriptions","organizations_url":"https://api.github.com/users/mjibson/orgs","repos_url":"https://api.github.com/users/mjibson/repos","events_url":"https://api.github.com/users/mjibson/events{/privacy}","received_events_url":"https://api.github.com/users/mjibson/received_events","type":"User","site_admin":false},"created_at":"2018-08-28T18:52:17Z","updated_at":"2018-08-28T18:52:17Z","author_association":"MEMBER","body":"Can you list specific examples of known things that don't work? You mentioned float truncation, denormalized floats, nans, arrays in chat. I agree about arrays, but would still like specific examples on all of these."}}
{"actor":{"display_login":"mjibson"}}