cli/dump: error while dumping uuid columns

[markharding]

Describe the problem

SQL Dump fails when using UUIDs

To Reproduce

1. ./cockroach dump minds notifications --certs-dir=/cockroach-certs --host=cockroachdb-public > dump.sql
   outputs: command terminated with exit code 1
2. dump.sql outputs:

CREATE TABLE notifications (
	uuid UUID NOT NULL DEFAULT gen_random_uuid(),
	to_guid INT NOT NULL,
	from_guid INT NULL,
	created_timestamp TIMESTAMP NULL DEFAULT now():::TIMESTAMP,
	read_timestamp TIMESTAMP NULL,
	notification_type STRING(20) NULL,
	data JSONB NULL,
	entity_guid STRING NULL,
	CONSTRAINT "primary" PRIMARY KEY (uuid ASC),
	INDEX to_guid_idx (to_guid ASC),
	FAMILY "primary" (uuid, to_guid, from_guid, created_timestamp, read_timestamp, notification_type, data, entity_guid)
);
Error: unknown []byte type: 3905b4f0-8d91-4ffd-9067-6dd67bf55327, uuid: uuid
Failed running "dump"

Expected behavior

Dump should succeed as with other tables.

Additional data / screenshots

root@cockroachdb-public:26257/minds> select * from notifications where uuid='3905b4f0-8d91-4ffd-9067-6dd67bf55327';
+--------------------------------------+--------------------+--------------------+----------------------------------+----------------+-------------------+--------------------------------------+--------------------+
|                 uuid                 |      to_guid       |     from_guid      |        created_timestamp         | read_timestamp | notification_type |                 data                 |    entity_guid     |
+--------------------------------------+--------------------+--------------------+----------------------------------+----------------+-------------------+--------------------------------------+--------------------+
| 3905b4f0-8d91-4ffd-9067-6dd67bf55327 | 441939549992521728 | 100000000000000063 | 2018-11-15 14:48:26.911028+00:00 | NULL           | comment           | {"description": "test comment here"} | 887249682567274501 |
+--------------------------------------+--------------------+--------------------+----------------------------------+----------------+-------------------+--------------------------------------+--------------------+
(1 row)

Time: 2.730664ms

Environment:

• CockroachDB version 2.1
• Server OS: K8S (AWS)
• Client app: cockroach sql

Additional context
What was the impact?
Unable to do a dump of table

Nothing in the error logs besides the output paster above

Attempted to delete the row for 3905b4f0-8d91-4ffd-9067-6dd67bf55327, issues persists. dump.sql output:

CREATE TABLE notifications (
	uuid UUID NOT NULL DEFAULT gen_random_uuid(),
	to_guid INT NOT NULL,
	from_guid INT NULL,
	created_timestamp TIMESTAMP NULL DEFAULT now():::TIMESTAMP,
	read_timestamp TIMESTAMP NULL,
	notification_type STRING(20) NULL,
	data JSONB NULL,
	entity_guid STRING NULL,
	CONSTRAINT "primary" PRIMARY KEY (uuid ASC),
	INDEX to_guid_idx (to_guid ASC),
	FAMILY "primary" (uuid, to_guid, from_guid, created_timestamp, read_timestamp, notification_type, data, entity_guid)
);
Error: unknown []byte type: 4ad53908-dabe-4689-b9be-1ba4f3ffa99e, uuid: uuid
Failed running "dump"

[knz]

cc @mjibson @rolandcrosby this is an extension of #28948

[knz]

worth checking if inet and perhaps other "interesting" types have the same problem

[markharding]

Are there any temporary workarounds to this? We're going to need to have some way to backup data.

[rolandcrosby]

Hi @markharding, I just tested this on CockroachDB 2.1.1 and it seemed to work fine. See below for my reproduction steps. I believe your client version may be from the 2.0 series, which may lead to issues when running client-side commands like cockroach dump. Can you check what version you're using on the client and server? When you start a cockroach sql shell, it should emit a line like this at startup:

# Server version: CockroachDB CCL v2.1.1 (x86_64-unknown-linux-gnu, built 2018/11/19 18:24:21, go1.10.3) (same version as client)

If the client and server aren't both from the 2.1 series, please try upgrading and let me know if you still run into this issue.

Input files (in the node's external I/O directory)

create.sql

CREATE TABLE notifications (
	uuid UUID NOT NULL DEFAULT gen_random_uuid(),
	to_guid INT NOT NULL,
	from_guid INT NULL,
	created_timestamp TIMESTAMP NULL DEFAULT now():::TIMESTAMP,
	read_timestamp TIMESTAMP NULL,
	notification_type STRING(20) NULL,
	data JSONB NULL,
	entity_guid STRING NULL,
	CONSTRAINT "primary" PRIMARY KEY (uuid ASC),
	INDEX to_guid_idx (to_guid ASC),
	FAMILY "primary" (uuid, to_guid, from_guid, created_timestamp, read_timestamp, notification_type, data, entity_guid)
);

input.tsv

3905b4f0-8d91-4ffd-9067-6dd67bf55327	441939549992521728	100000000000000063	2018-11-15 14:48:26.911028+00:00	NULL	comment	{"description": "test comment here"}	887249682567274501

Attempted reproduction steps

First, I imported the data and schema you provided:

root@:26257/defaultdb> import table notifications create using 'nodelocal:/create.sql' csv data ('nodelocal:/input.tsv') with delimiter = e'\t', nullif = 'NULL';
        job_id       |  status   | fraction_completed | rows | index_entries | system_records | bytes
+--------------------+-----------+--------------------+------+---------------+----------------+-------+
  401952841039511553 | succeeded |                  1 |    1 |             1 |              0 |   166
(1 row)

Time: 390.622975ms

root@:26257/defaultdb> select * from notifications;
                  uuid                 |      to_guid       |     from_guid      |        created_timestamp         | read_timestamp | notification_type |                 data                 |    entity_guid
+--------------------------------------+--------------------+--------------------+----------------------------------+----------------+-------------------+--------------------------------------+--------------------+
  3905b4f0-8d91-4ffd-9067-6dd67bf55327 | 441939549992521728 | 100000000000000063 | 2018-11-15 14:48:26.911028+00:00 | NULL           | comment           | {"description": "test comment here"} | 887249682567274501
(1 row)

Time: 20.442057ms

Then, I ran cockroach dump:

roland@roland-dump-0001:~$ ./cockroach dump defaultdb --insecure
CREATE TABLE notifications (
	uuid UUID NOT NULL DEFAULT gen_random_uuid(),
	to_guid INT NOT NULL,
	from_guid INT NULL,
	created_timestamp TIMESTAMP NULL DEFAULT now():::TIMESTAMP,
	read_timestamp TIMESTAMP NULL,
	notification_type STRING(20) NULL,
	data JSONB NULL,
	entity_guid STRING NULL,
	CONSTRAINT "primary" PRIMARY KEY (uuid ASC),
	INDEX to_guid_idx (to_guid ASC),
	FAMILY "primary" (uuid, to_guid, from_guid, created_timestamp, read_timestamp, notification_type, data, entity_guid)
);

INSERT INTO notifications (uuid, to_guid, from_guid, created_timestamp, read_timestamp, notification_type, data, entity_guid) VALUES
	('3905b4f0-8d91-4ffd-9067-6dd67bf55327', 441939549992521728, 100000000000000063, '2018-11-15 14:48:26.911028+00:00', NULL, 'comment', '{"description": "test comment here"}', '887249682567274501');

[markharding]

Aha! You are correct, the cockroachdb-client-secure pod was not updated.

Confirmed v2.1.1 resolves this.

[BenChand]

Hi,

I still get this problem when running any version after v2.1.0 (where both the server and client use the same version).

Environment
Windows 10
Cockroach db v2.1.3
Cockroach client v2.1.3

To reproduce

PS> cockroach.exe start --insecure --listen-addr=localhost
PS> cockroach.exe sql --insecure
root@:26257/defaultdb> create database hi;
root@:26257/defaultdb> SET database = hi;
root@:26257/hi> create table t1 (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name String);
root@:26257/hi> insert into t1 (name) VALUES ('a'), ('b'), ('c');
root@:26257/hi> \q
PS> cockroach dump hi --dump-mode=data --insecure
Error: unknown []byte type: 6cde2645-bd30-4d55-a10e-8298a41ac491, id: uuid
Failed running "dump"

[knz]

@BenChand please file a new issue