Spring boot not executing schema.sql script

Question

I'm developing a Spring Boot web application and want to create a MySql database if it's not already created. So I've done a dump of my current database in order to have an empty schema of it. Put it in /src/main/resources, so maven brings it to /WEB-INF/classes when building the war file. That's how my application.properties is configured (according to Spring docs, that should create the DB from the script):

# DataSource settings: set here configurations for the database connection
spring.datasource.url = jdbc:mysql://localhost:3306/working_zones
spring.datasource.username = root
spring.datasource.password = password
spring.datasource.driverClassName = com.mysql.jdbc.Driver

# Specify the DBMS
spring.jpa.database = MYSQL

# Hibernate settings are prefixed with spring.jpa.hibernate.*
spring.jpa.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

And that's the error I get when I try to run the application (it complains about a non-existing DB):

2015-01-13 13:30:24.334 [main] ERROR o.s.boot.SpringApplication - Application startup failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: private javax.sql.DataSource org.springframework.boot.autoconfigure.orm.jpa.JpaBaseConfiguration.dataSource; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceAutoConfiguration$NonEmbeddedConfiguration.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceInitializer': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.UncategorizedScriptException: Failed to execute database script; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'working_zones'
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:301) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1186) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:537) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:302) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:298) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:370) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1095) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:990) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:504) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:302) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:298) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:975) ~[spring-context-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:752) ~[spring-context-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:482) ~[spring-context-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:109) ~[spring-boot-1.1.9.RELEASE.jar:1.1.9.RELEASE]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:691) [spring-boot-1.1.9.RELEASE.jar:1.1.9.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:320) [spring-boot-1.1.9.RELEASE.jar:1.1.9.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:952) [spring-boot-1.1.9.RELEASE.jar:1.1.9.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:941) [spring-boot-1.1.9.RELEASE.jar:1.1.9.RELEASE]

So it seems that Spring tries to connect to the database even schema.sql, which contains the script to create the DB, not executed. There are some related questions in Stack Overflow about that, but still can't get it working, even if I try with spring.datasource.initialize=true...

Does `Unknown database 'working_zones'` say anything to you? — Artem Bilan, Jan 13 '15 at 12:46
@ArtemBilan, the `schema.sql` script has the `CREATE DATABASE IF NOT EXISTS "working_zones"` command. It's not being executed, so the program doesn't find the DB when stablishing a JDBC connection at the beginning. That's what the question is about... — Aritz, Jan 13 '15 at 14:01
You need the database to be there to be even able to connect to it. The failure indicates that your datasource cannot connect to the database, which is correct because it doesn't exist. — M. Deinum, Jan 13 '15 at 14:08
@M.Deinum so do I need to have at least the DB created and rely on the script to create the tables/relations? Isn't there any other way to execute the whole job when application starts? — Aritz, Jan 13 '15 at 14:10
Not that I'm aware off, the name of the database is in the URL so it needs to be there before hand. See also the answer from @ArtemBilan. — M. Deinum, Jan 13 '15 at 14:12

score 3 · Accepted Answer · edited May 23 '17 at 11:53

3

Well, looks like you can't do that from the common JDBC connect: creating a database in mysql from java

Hence Spring Boot can't do that for you automatically.

Please, don't mix the DB creating with its creation for its content: tables, procedures, triggers etc.

UPDATE

Yes, you can do that on the application startup. You just a separate initializer, which has an order before dataSourceInitializer.

edited May 23 '17 at 11:53

Community

1
1

answered Jan 13 '15 at 14:11

Artem Bilan

113,505
11
91
118

Well, that's what I was asking for, thanks. I was looking for the DB creation itself from the very beginning, don't know if the question was leading you the other way. – Aritz Jan 13 '15 at 14:13

score 0 · Answer 2 · answered Aug 17 '17 at 06:20

A listerner on the application startup can resolve it. Following is the code.

public class DatabaseCreationListener implements ApplicationListener<ApplicationEnvironmentPreparedEvent> {

    private AtomicBoolean received = new AtomicBoolean(false);

    private ConfigurableEnvironment environment;

    private Pattern JDBC_URL_PATTERN = Pattern.compile("jdbc:([a-zA-Z0-9_]+)://[0-9.:]+(?:/([a-zA-Z0-9_]+))?(\\?.*)?");

    @Override
    public void onApplicationEvent(ApplicationEnvironmentPreparedEvent event) {
        // Think about twice invoking this listener
        if (!received.compareAndSet(false, true)) {
            return;
        }

        environment = event.getEnvironment();

        // ConditionalOnClass
        ClassLoader classLoader = event.getSpringApplication().getClassLoader();
        if (!isPresent("org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType", classLoader)) {
            return;
        }

        // DatabaseProperties
        val databaseProperties = bind(DatabaseProperties.PREFIX, new DatabaseProperties());
        if (!databaseProperties.shouldCreate()) {
            return;
        }

        // DataSourceProperties
        val dataSourceProperties = bind(databaseProperties.getDatasourceConfigPrefix(), new DataSourceProperties());

        // Check for connection url
        String url = dataSourceProperties.getUrl();
        if (url == null) return;
        Matcher matcher = JDBC_URL_PATTERN.matcher(url);
        if (!matcher.matches()) return;

        // Extract database provider and schema name from connection url
        String databaseProvider = matcher.group(1);
        String schemaName = matcher.group(2);
        if (isBlank(schemaName)) return;

        // Reset connection url
        dataSourceProperties.setUrl(url.replace("/" + schemaName, ""));

        // Build a new datasource and do create schema
        DataSource dataSource = buildDataSource(dataSourceProperties);
        try (Connection connection = DataSourceUtils.getConnection(dataSource)) {
            connection.createStatement().execute(createSchemaIfAbsent(databaseProvider, schemaName));
        } catch (SQLException ignored) {
        }
    }

    private <T> T bind(String prefix, T t) {
        RelaxedDataBinder binder = new RelaxedDataBinder(t, prefix);
        binder.bind(new PropertySourcesPropertyValues(environment.getPropertySources()));
        return t;
    }

    private static DataSource buildDataSource(DataSourceProperties dataSourceProperties) {
        String url = dataSourceProperties.getUrl();
        String username = dataSourceProperties.getUsername();
        String password = dataSourceProperties.getPassword();
        return new SingleConnectionDataSource(url, username, password, false);
    }

    private static String createSchemaIfAbsent(String databaseProvider, String schemaName) {
        DatabaseDialects dialects = DatabaseDialects.getDatabaseDialect(databaseProvider);
        if (dialects == null) {
            throw new IllegalArgumentException("Unknown schema：" + schemaName);
        }
        switch (dialects) {
            case MYSQL:
                return "CREATE DATABASE IF NOT EXISTS " + schemaName;
            default:
                throw new UnsupportedOperationException("Unsupported schema：" + dialects);
        }
    }
}

And Following is the DatabaseProperties.

@Data
@ConfigurationProperties(prefix = DatabaseProperties.PREFIX)
public class DatabaseProperties {

    public final static String PREFIX = "spring.database";

    private boolean autoCreate;

    private String datasourceConfigPrefix = "spring.datasource";

    public boolean shouldCreate() {
        return isAutoCreate() && isNotBlank(getDatasourceConfigPrefix());
    }
}

The listener should be actived by config in META-INF/spring.factories.

org.springframework.context.ApplicationListener=\
yourpackage.DatabaseCreationListener

If you want config syntax hint in an underlying IDE, add the optional depenedncy spring-boot-configuration-processor and file META-INF/additional-spring-configuration-metadata.json.

{
  "groups": [
    {
      "sourceType": "yourpackage.DatabaseProperties",
      "name": "spring.database",
      "type": "yourpackage.DatabaseProperties"
    }
  ],
  "properties": [
    {
      "sourceType": "yourpackage.DatabaseProperties",
      "defaultValue": false,
      "name": "auto-create",
      "type": "java.lang.Boolean"
    },
    {
      "sourceType": "youpackage.DatabaseProperties",
      "defaultValue": "spring.datasource",
      "name": "datasource-config-prefix",
      "type": "java.lang.String"
    }
  ]
}

score 0 · Answer 3 · answered Jun 27 '21 at 05:16

0

As mentioned in this github post, you can add: spring.datasource.url=jdbc:mysql://localhost:3309/course_api_db?createDatabaseIfNotExist=true

To execute the schema.sql

answered Jun 27 '21 at 05:16

Ran

657
2
13
29

Spring boot not executing schema.sql script

3 Answers3