如何使用flyway创建数据库

如何使用flyway创建数据库

本文介绍了如何使用flyway创建数据库?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

问题:是否可以在迁移脚本中创建一个新的数据库然后连接到它?怎么样?

Question: Is it possible to create a new DB in a migration script and then connect to it? How?

我的场景:我正在尝试在我的 Java 项目(使用 Jersey2.4 + tomcat 7 + PostgreSQL 9.3.1 + EclipseLink 的 RESTful 应用程序)中使用 flyway 来管理使用 git 的不同开发人员之间的更改.我写了我的初始化脚本并运行它:

My Scenario:I'm trying to use flyway in my Java project (RESTful application using Jersey2.4 + tomcat 7 + PostgreSQL 9.3.1 + EclipseLink) for managing the changes between different developers which are using git. I wrote my init script and ran it with:

PGPASSWORD='123456' psql -U postgres -f migration/V1__initDB.sql

而且效果很好.问题是我不能用我的脚本创建新的数据库.当我在脚本中包含以下行时:

and it worked fine. The problem is that I can't create new DB with my scripts. when I include the following line in my script:

CREATE DATABASE my_database OWNER postgres ENCODING 'UTF8';

我收到此错误:

org.postgresql.util.PSQLException: ERROR: CREATE DATABASE cannot run inside a transaction block
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:395)
    at com.googlecode.flyway.core.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:230)
    at com.googlecode.flyway.core.dbsupport.SqlScript.execute(SqlScript.java:89)
    at com.googlecode.flyway.core.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:72)
    at com.googlecode.flyway.core.command.DbMigrate$2.doInTransaction(DbMigrate.java:252)
    at com.googlecode.flyway.core.command.DbMigrate$2.doInTransaction(DbMigrate.java:250)
    at com.googlecode.flyway.core.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:56)
    at com.googlecode.flyway.core.command.DbMigrate.applyMigration(DbMigrate.java:250)
    at com.googlecode.flyway.core.command.DbMigrate.access$700(DbMigrate.java:47)
    at com.googlecode.flyway.core.command.DbMigrate$1.doInTransaction(DbMigrate.java:189)
    at com.googlecode.flyway.core.command.DbMigrate$1.doInTransaction(DbMigrate.java:138)
    at com.googlecode.flyway.core.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:56)
    at com.googlecode.flyway.core.command.DbMigrate.migrate(DbMigrate.java:137)
    at com.googlecode.flyway.core.Flyway$1.execute(Flyway.java:872)
    at com.googlecode.flyway.core.Flyway$1.execute(Flyway.java:819)
    at com.googlecode.flyway.core.Flyway.execute(Flyway.java:1200)
    at com.googlecode.flyway.core.Flyway.migrate(Flyway.java:819)
    at ir.chom.MyApp.<init>(MyApp.java:28)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at org.glassfish.hk2.utilities.reflection.ReflectionHelper.makeMe(ReflectionHelper.java:1117)
    at org.jvnet.hk2.internal.Utilities.justCreate(Utilities.java:867)
    at org.jvnet.hk2.internal.ServiceLocatorImpl.create(ServiceLocatorImpl.java:814)
    at org.jvnet.hk2.internal.ServiceLocatorImpl.createAndInitialize(ServiceLocatorImpl.java:906)
    at org.jvnet.hk2.internal.ServiceLocatorImpl.createAndInitialize(ServiceLocatorImpl.java:898)
    at org.glassfish.jersey.server.ApplicationHandler.createApplication(ApplicationHandler.java:300)
    at org.glassfish.jersey.server.ApplicationHandler.<init>(ApplicationHandler.java:279)
    at org.glassfish.jersey.servlet.WebComponent.<init>(WebComponent.java:302)
    at org.glassfish.jersey.servlet.ServletContainer.init(ServletContainer.java:167)
    at org.glassfish.jersey.servlet.ServletContainer.init(ServletContainer.java:349)
    at javax.servlet.GenericServlet.init(GenericServlet.java:160)
    at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1280)
    at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1091)
    at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:5176)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5460)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.StandardContext.reload(StandardContext.java:3954)
    at org.apache.catalina.loader.WebappLoader.backgroundProcess(WebappLoader.java:426)
    at org.apache.catalina.core.ContainerBase.backgroundProcess(ContainerBase.java:1345)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1530)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1540)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1540)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1519)
    at java.lang.Thread.run(Thread.java:724)

这似乎是使用 autocommit 选项的 JDBC 的问题.可以通过以下方式禁用此选项:

It seems that this is a problem with JDBC that uses autocommit option. This option can be disabled with something like this:

Connection connection = dataSource.getConnection();
Connection.setAutoCommit(false);        // Disables auto-commit.

但我不知道如何将此选项传递给飞路连接.此外,如果我解决了这个问题,我认为将密码传递给 c 命令时会出现问题.

but I don't know how to pass this option to flyway connection. Also if I solve this I think I will have problem with passing password to c command.

推荐答案

Flyway 始终在 jdbc 连接字符串中使用的数据库内运行.

Flyway always operates within the database used in the jdbc connection string.

一旦连接,所有脚本都会在一个事务中运行.由于事务中不支持 CREATE DATABASE,您将无法完成您想要的.

Once connected, all scripts run within a transaction. As CREATE DATABASE is not supported within transactions, you will not be able to accomplish what you want.

然而,您可以做的是创建一个模式.Flyway 甚至会为你做这件事,如果你把它指向一个不存在的.

What you can do however, is create a schema instead. Flyway will even do this for you, if you point it at a non-existing one.

这篇关于如何使用flyway创建数据库?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-12 18:28