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

SQL parsing problem when combining Postgres's COPY ... FROM stdin and SQL domains #1446

Closed
arlampin opened this issue Oct 6, 2016 · 0 comments
Closed

Comments

@arlampin
Copy link

@arlampin arlampin commented Oct 6, 2016

What version of Flyway are you using?

4.0.3

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin, SBT plugin, ANT tasks)

Gradle and Java API

What database are you using (type & version)?

PostgreSQL 9.5.4

What operating system are you using?

Centos7

What did you do?

With the following beforeMigrate.sql:

CREATE DOMAIN some_domain AS CHARACTER(3)
CONSTRAINT some_domain_check CHECK (((VALUE) :: TEXT = ANY (ARRAY ['FOO' :: TEXT, 'BAR' :: TEXT])));

CREATE TABLE test_table (
  id          SERIAL PRIMARY KEY,
  some_column TEXT
);

COPY test_table (id, some_column) FROM stdin;
1   foobar
\.

following exception happens:

Migration beforeMigrate.sql failed
----------------------------------
SQL State  : 08P01
Error Code : 0
Message    : ERROR: unexpected message type 0x50 during COPY from stdin
  Where: COPY test_table, line 1
Location   : src/main/resources/migrations/beforeMigrate.sql (...)
Line       : 1
Statement  : CREATE DOMAIN some_domain AS CHARACTER(3)
CONSTRAINT some_domain_check CHECK (((VALUE) :: TEXT = ANY (ARRAY ['FOO'::text, 'BAR'::text])));

CREATE TABLE test_table (
  id          SERIAL PRIMARY KEY,
  some_column TEXT
);

COPY test_table (id, some_column) FROM stdin;
1       foobar
\.

        at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117)
        at org.flywaydb.core.internal.callback.SqlScriptFlywayCallback.execute(SqlScriptFlywayCallback.java:183)
        at org.flywaydb.core.internal.callback.SqlScriptFlywayCallback.beforeMigrate(SqlScriptFlywayCallback.java:121)
        at org.flywaydb.core.internal.command.DbMigrate$1.doInTransaction(DbMigrate.java:161)
        at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
        at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:157)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:959)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:917)
        at org.flywaydb.core.Flyway.execute(Flyway.java:1373)
        at org.flywaydb.core.Flyway.migrate(Flyway.java:917)
        at org.flywaydb.core.Flyway$migrate$1.call(Unknown Source)
        at org.flywaydb.gradle.task.FlywayMigrateTask.run(FlywayMigrateTask.groovy:30)
        at org.flywaydb.gradle.task.FlywayMigrateTask$run.callCurrent(Unknown Source)
        at org.flywaydb.gradle.task.FlywayCleanTask$run.callCurrent(Unknown Source)
        at org.flywaydb.gradle.task.AbstractFlywayTask.runTask(AbstractFlywayTask.groovy:68)
        ... 76 more
Caused by: org.postgresql.util.PSQLException: ERROR: unexpected message type 0x50 during COPY from stdin
  Where: COPY test_table, line 1
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2158)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:291)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:432)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:358)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:305)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:291)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:269)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:265)
        at org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:238)
        at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:114)
        ... 90 more

but, after removing the last ::TEXT from SQL, it works:

CREATE DOMAIN some_domain AS CHARACTER(3)
CONSTRAINT some_domain_check CHECK (((VALUE) :: TEXT = ANY (ARRAY ['FOO' :: TEXT, 'BAR'])));

CREATE TABLE test_table (
  id          SERIAL PRIMARY KEY,
  some_column TEXT
);

COPY test_table (id, some_column) FROM stdin;
1   foobar
\.

Both SQL files work with Postgres's own tools, and example is actually extracted from a SQL dump created with pg_dump.

@axelfontaine axelfontaine added this to the Flyway 4.1 milestone Oct 7, 2016
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Jan 30, 2017
pradheeps pushed a commit to pradheeps/flyway that referenced this issue Mar 7, 2017
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