Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Postgres COPY FROM STDIN fails on data containing string delimiters #1876

Closed
brodgers-oreilly opened this issue Dec 20, 2017 · 10 comments
Closed

Comments

@brodgers-oreilly
Copy link

@brodgers-oreilly brodgers-oreilly commented Dec 20, 2017

Hi,

I've been playing around with Flyway for database migrations and came across an interesting error with processing a native Postgres data dump.

Flyway command line versions (linux)
3.2.1
4.0.3
5.0.2

[edit] Issue occurs on Flyway Migrate step of a .sql file containing native Postgres data dump

I have a ~3 MB data-only dump file, the contents of which I can't disclose at the moment. About 5,000 lines in, there's a copy from stdin statement for ~430 records that includes some HTML + JSTL markup language within. Immediately following this statement, Flyway appears to skip reading ~8500 lines and continue processing the file.

Surprisingly, since the sections where Flyway stops and restarts reading the file end on valid statements, no errors get thrown! The issue was initially caught by developers when they found expected data was missing from a handful of tables. The behavior of Flyway was finally caught by logging all SQL statements on Postgres side.

pseudo sql

-- Table 1
disable triggers on table1
copy table1 from stdin;
enable triggers on table1

-- Table 2
disable triggers on table2
copy table2 from stdin;
enable triggers on table2

-- Repeat

Actual Processing mid file

disable triggers on table1
copy table1 from stdin;

(note enable trigger on table1 doesn't get run)
(skip 8500 lines)

disable triggers on tableXXX
copy tableXXX from stdin;

Notes on bad behavior

Everything else in the file appears to be processed normally. Since no errors were thrown from Flyway, it has been difficult to ascertain what the precise issue is. Suspect it has something to do with Flyway parsing statements and getting hung up on some of the additional markup language which may contain reserved sql words and symbols ( semicolon would be a good candidate )

Expected behavior

Reading and executing all statements in file just as if it was imported via Postgres command line. The Postgres native import of the same works and was used to ensure the SQL statements within the file were valid.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Dec 20, 2017

Please provide the smallest possible SQL migration file that we can run to reproduce the issue.

@brodgers-oreilly
Copy link
Author

@brodgers-oreilly brodgers-oreilly commented Dec 21, 2017

Haven't found exactly what it is to produce a minimal file. Also have yet to receive verification on what contents I can share (potentially proprietary info).

Is there a place we could drop off the contents that would not be public?

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Dec 21, 2017

Can't you just cut back the statements and the data to the bare minimum that is causing the error and anonymize the data to ensure no proprietary info is leaked?

Private support channels are available to Flyway Pro and Enterprise customers if you prefer to go down that option.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jan 16, 2018

Any news?

@brodgers-oreilly
Copy link
Author

@brodgers-oreilly brodgers-oreilly commented Jan 16, 2018

@brodgers-oreilly
Copy link
Author

@brodgers-oreilly brodgers-oreilly commented Feb 1, 2018

Got approvals from both my company and 3rd party vendor.

V1_0_0__Main_Restore.txt

@brodgers-oreilly
Copy link
Author

@brodgers-oreilly brodgers-oreilly commented Feb 1, 2018

What we actually saw with logging turned on

[2017-12-15 18:14:06 CST][User: {user}][DB: {database}]LOG: execute : ALTER TABLE blc_sc_fld DISABLE TRIGGER ALL
[2017-12-15 18:14:06 CST][User: {user}][DB: {database}]LOG: statement: COPY blc_sc_fld (sc_fld_id, archived, created_by, date_created, date_updated, updated_by, admin_addition_status, sndbx_catalog_flag, sndbx_orig_item_id, sndbx_orig_record_id, sndbx_id, sndbx_archived_flag, sndbx_deleted_flag, sndbx_tmplt_record_id, sndbx_tier, site_disc, fld_key, lob_value, value) FROM stdin
[2017-12-15 18:14:06 CST][User: {user}][DB: {database}]LOG: execute : ALTER TABLE blc_sndbx_property ENABLE TRIGGER ALL
[2017-12-15 18:14:06 CST][User: {user}][DB: {database}]LOG: execute : ALTER TABLE blc_sndbx_property_override DISABLE TRIGGER ALL

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Feb 26, 2018

The SQL file you provided is 36734 lines. Please cut it back to the smallest subset that reproducibly demonstrates the error.

@brodgers-oreilly
Copy link
Author

@brodgers-oreilly brodgers-oreilly commented Feb 26, 2018

@axelfontaine axelfontaine changed the title Flyway import of Postgres native data dump issue Postgres COPY FROM STDIN fails on data containing string delimiters Feb 26, 2018
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Feb 26, 2018
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Feb 26, 2018

It turned out to be the ' characters that were tripping up the parser.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants