cli/dump: dump produces inserts that are too large to be restored

[isoos]

I've created a database dump using cockroach dump [database] >output.sql. However I'm not able to restore it, neither with native psql nor with the cockroach sql commands, most likely because the generated inserts are too large.

One of the tables there has rows with binary data, 1MB in size each, and the dump seems to create insert statements in the batch of 100 rows, regardless of the size. I think the inserts fail because the aggregated size is larger than the 64M limit.

[knz]

Oh that is nicely spotted.

As a workaround, you can modify insertRows in pkg/cli/dump.go and re-build to get a custom cockroach dump with a different (eg lower) number of rows per insert.

The proper fix on our side will probably be to auto-detect a good size.

[knz]

cc @rolandcrosby @mjibson for triage and prioritization

[knz]

Oh that is nicely spotted.

As a workaround, you can modify insertRows in pkg/cli/dump.go and re-build to get a custom cockroach dump with a different (eg lower) number of rows per insert.

The proper fix on our side will probably be to auto-detect a good size.

[tbg]

@mjibson, could you help István out?

[knz]

I had sent a reply earlier today but I think it got lost in the Github outage. My answer was as follows: - as a workaround you can edit the value of `insertRows` in `pkg/cli/dump.go` to build a custom dump utility with fewer values per insert. - eventually CockroachDb should address this by adjusting the number of values per insert automatically based on the size of the data. Thanks for reporting the issue.

…

-- Raphael 'kena' Poss

[knz]

This is an extension of #28948.

[zsalab]

any news on this? maybe a workaround? can I help somehow? (I reported the #51969)

[knz]

my comment from #31676 (comment) is still applicable

[dt]

20.2 made proper binary BACKUP+RESTORE available for non-enterprise users and deprecated text-based dump, so we're not planning to go back and extend the deprecated dump functionality to add variable sized insert batches. Closing for now as wont-fix.