search

weavejester / ragtime

Database-independent migration library
Eclipse Public License 1.0
610 stars 85 forks source link

Problem with 0.3.7 and postgresql #34

Closed tvaughan closed 9 years ago

tvaughan commented 10 years ago

This happens in 0.3.7 and not 0.3.6. I assume this is related to this commit: https://github.com/weavejester/ragtime/commit/717360529f7af744d6c0e28fc4b601e82915e950

Any clue? Thanks!

$ cat migrations/0001-create-extensions.up.sql 
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
$ lein with-profile ragtime ragtime migrate -d "jdbc:postgresql:redacted"
Applying 0001-create-extensions
org.postgresql.util.PSQLException: Too many update results were returned.
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleCommandStatus(AbstractJdbc2Statement.java:2733)
    at org.postgresql.core.v3.QueryExecutorImpl$1.handleCommandStatus(QueryExecutorImpl.java:452)
    at org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:2215)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1847)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2909)
    at clojure.java.jdbc.deprecated$execute_batch.invoke(deprecated.clj:423)
    at clojure.java.jdbc.deprecated$do_commands$fn__231.invoke(deprecated.clj:435)
    at clojure.java.jdbc.deprecated$transaction_STAR_.invoke(deprecated.clj:389)
    at clojure.java.jdbc.deprecated$do_commands.doInvoke(deprecated.clj:434)
    at clojure.lang.RestFn.invoke(RestFn.java:408)
    at ragtime.sql.files$run_sql_fn$fn__364$fn__365$fn__366.invoke(files.clj:80)
    at clojure.java.jdbc.deprecated$transaction_STAR_.invoke(deprecated.clj:377)
    at ragtime.sql.files$run_sql_fn$fn__364$fn__365.invoke(files.clj:77)
    at clojure.java.jdbc.deprecated$with_connection_STAR_.invoke(deprecated.clj:307)
    at ragtime.sql.files$run_sql_fn$fn__364.invoke(files.clj:76)
    at clojure.lang.AFn.applyToHelper(AFn.java:154)
    at clojure.lang.AFn.applyTo(AFn.java:144)
    at clojure.core$apply.invoke(core.clj:624)
    at ragtime.main$wrap_println$fn__298.doInvoke(main.clj:19)
    at clojure.lang.RestFn.invoke(RestFn.java:408)
    at ragtime.core$migrate.invoke(core.clj:38)
    at ragtime.core$migrate_all.invoke(core.clj:60)
    at ragtime.core$migrate_all.invoke(core.clj:53)
    at ragtime.main$migrate.invoke(main.clj:32)
    at clojure.lang.AFn.applyToHelper(AFn.java:154)
    at clojure.lang.AFn.applyTo(AFn.java:144)
    at clojure.core$apply.invoke(core.clj:626)
    at ragtime.main$_main.doInvoke(main.clj:67)
    at clojure.lang.RestFn.invoke(RestFn.java:703)
    at clojure.lang.Var.invoke(Var.java:442)
    at user$eval5$fn__7.invoke(form-init3619964908012902520.clj:1)
    at user$eval5.invoke(form-init3619964908012902520.clj:1)
    at clojure.lang.Compiler.eval(Compiler.java:6703)
    at clojure.lang.Compiler.eval(Compiler.java:6693)
    at clojure.lang.Compiler.load(Compiler.java:7130)
    at clojure.lang.Compiler.loadFile(Compiler.java:7086)
    at clojure.main$load_script.invoke(main.clj:274)
    at clojure.main$init_opt.invoke(main.clj:279)
    at clojure.main$initialize.invoke(main.clj:307)
    at clojure.main$null_opt.invoke(main.clj:342)
    at clojure.main$main.doInvoke(main.clj:420)
    at clojure.lang.RestFn.invoke(RestFn.java:421)
    at clojure.lang.Var.invoke(Var.java:383)
    at clojure.lang.AFn.applyToHelper(AFn.java:156)
    at clojure.lang.Var.applyTo(Var.java:700)
    at clojure.main.main(main.java:37)
Exception in thread "main" java.sql.BatchUpdateException: Batch entry 1 <unknown> was aborted.  Call getNextException to see the cause., compiling:(/tmp/form-init3619964908012902520.clj:1:90)
    at clojure.lang.Compiler.load(Compiler.java:7142)
    at clojure.lang.Compiler.loadFile(Compiler.java:7086)
    at clojure.main$load_script.invoke(main.clj:274)
    at clojure.main$init_opt.invoke(main.clj:279)
    at clojure.main$initialize.invoke(main.clj:307)
    at clojure.main$null_opt.invoke(main.clj:342)
    at clojure.main$main.doInvoke(main.clj:420)
    at clojure.lang.RestFn.invoke(RestFn.java:421)
    at clojure.lang.Var.invoke(Var.java:383)
    at clojure.lang.AFn.applyToHelper(AFn.java:156)
    at clojure.lang.Var.applyTo(Var.java:700)
    at clojure.main.main(main.java:37)
Caused by: java.sql.BatchUpdateException: Batch entry 1 <unknown> was aborted.  Call getNextException to see the cause.
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2762)
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleCommandStatus(AbstractJdbc2Statement.java:2733)
    at org.postgresql.core.v3.QueryExecutorImpl$1.handleCommandStatus(QueryExecutorImpl.java:452)
    at org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:2215)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1847)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2909)
    at clojure.java.jdbc.deprecated$execute_batch.invoke(deprecated.clj:423)
    at clojure.java.jdbc.deprecated$do_commands$fn__231.invoke(deprecated.clj:435)
    at clojure.java.jdbc.deprecated$transaction_STAR_.invoke(deprecated.clj:389)
    at clojure.java.jdbc.deprecated$do_commands.doInvoke(deprecated.clj:434)
    at clojure.lang.RestFn.invoke(RestFn.java:408)
    at ragtime.sql.files$run_sql_fn$fn__364$fn__365$fn__366.invoke(files.clj:80)
    at clojure.java.jdbc.deprecated$transaction_STAR_.invoke(deprecated.clj:377)
    at ragtime.sql.files$run_sql_fn$fn__364$fn__365.invoke(files.clj:77)
    at clojure.java.jdbc.deprecated$with_connection_STAR_.invoke(deprecated.clj:307)
    at ragtime.sql.files$run_sql_fn$fn__364.invoke(files.clj:76)
    at clojure.lang.AFn.applyToHelper(AFn.java:154)
    at clojure.lang.AFn.applyTo(AFn.java:144)
    at clojure.core$apply.invoke(core.clj:624)
    at ragtime.main$wrap_println$fn__298.doInvoke(main.clj:19)
    at clojure.lang.RestFn.invoke(RestFn.java:408)
    at ragtime.core$migrate.invoke(core.clj:38)
    at ragtime.core$migrate_all.invoke(core.clj:60)
    at ragtime.core$migrate_all.invoke(core.clj:53)
    at ragtime.main$migrate.invoke(main.clj:32)
    at clojure.lang.AFn.applyToHelper(AFn.java:154)
    at clojure.lang.AFn.applyTo(AFn.java:144)
    at clojure.core$apply.invoke(core.clj:626)
    at ragtime.main$_main.doInvoke(main.clj:67)
    at clojure.lang.RestFn.invoke(RestFn.java:703)
    at clojure.lang.Var.invoke(Var.java:442)
    at user$eval5$fn__7.invoke(form-init3619964908012902520.clj:1)
    at user$eval5.invoke(form-init3619964908012902520.clj:1)
    at clojure.lang.Compiler.eval(Compiler.java:6703)
    at clojure.lang.Compiler.eval(Compiler.java:6693)
    at clojure.lang.Compiler.load(Compiler.java:7130)
    ... 11 more
Error encountered performing task 'ragtime' with profile(s): 'ragtime'
Suppressed exit
weavejester commented 10 years ago

Now that's interesting. On 0.3.7 I removed the functionality that splits up large SQL files if the database is PostgreSQL, because unlike MySQL, Postgres can handle multiple updates more robustly. But it looks like there was a limit I was unaware of.

I'll need to consider how to approach this in future. Either by taking an approach like clj-sql-up, where the migration files are written in Clojure, or by sticking with SQL and using an explicit delimiter command between SQL commands.

For now, I'd advise using 0.3.6. If you happen to have any syntax suggestions on this subject, I'd be interested to know.

tvaughan commented 10 years ago

We already use https://github.com/jkk/honeysql so it would be pretty natural for us to write our migrations in clojure.

yogthos commented 10 years ago

Since the approach of splitting up large files works reliably for all databases, wouldn't it be better to simply continue using it with postgres as well. Is performance really that much of an issue here?

weavejester commented 10 years ago

The problem is that splitting up large files isn't reliable; it doesn't work with things like stored procedures.

yogthos commented 10 years ago

Another option might be to use a custom statement separator such as a comment string the way yesql does.

iwinux commented 10 years ago

@weavejester @yogthos

Just ran into this problem. This answer from SO seems to solve / work around it.

iwinux commented 10 years ago

BTW: do-commands are deprecated in favor of db-do-commands

icambron commented 10 years ago

I'm hitting this with just two SQL statements in my migration file, so I'm a bit confused about the "handles more robustly" part. Perhaps I'm missing something?

Assuming that solution from SO works, would you accept a pull request replacing do-commands with do-prepared for the Postgres case?

michaelklishin commented 10 years ago

@icambron #45 has been open for 40 days or so. Use https://github.com/michaelklishin/ragtime (released to Clojars as clojurewerkz/ragtime "0.4.0") which has a fix.

Joplin has recently switched to our fork because Ragtime isn't getting the attention it deserves.

icambron commented 10 years ago

@michaelklishin Thanks, I'll give a shot.

weavejester commented 10 years ago

Unfortunately Ragtime has been at the bottom of my priority list for a while, as I haven't done anything with SQL databases in over a year. It's definitely not getting the attention it deserves.

@michaelklishin would you be open to collaborating on Ragtime?

michaelklishin commented 10 years ago

@weavejester definitely. I'd be happy to take it over as I use Joplin quite a bit with both relational and non-relational data stores.

I can port the few fixes I have in our fork and cut a new release if you add me to the group on Clojars.

@martintrojer may be interested in joining, too.

martintrojer commented 10 years ago

Cool, let us now when ragtime is patched up and I'll move joplin back.