修复testContainers Init脚本中的MySQL定界符语法错误

Question

我想使用 testcontainers 测试存储过程。所以我正在使用包含存储过程定义的 SQL 脚本初始化我的容器，但我

java.lang.IllegalStateException: Failed to load ApplicationContext
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'initializer' defined in com.api.bankApi.services.TransactionServiceTest$TestConfig: Invocation of init method failed; nested exception is org.springframework.data.r2dbc.connectionfactory.init.ScriptStatementFailedException: Failed to execute SQL script statement #7 of class path resource [schema.sql]: DELIMITER $$ CREATE PROCEDURE make_transaction(IN trans_type varchar(45),IN v_amount decimal(17,2),IN trans_id varchar(255) ) exitSub:BEGIN DECLARE EXIT HANDLER for SQLEXCEPTION BEGIN DECLARE errNom INT UNSIGNED DEFAULT 0; nested exception is io.r2dbc.spi.R2dbcBadGrammarException: [1064] [42000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE PROCEDURE make_transaction(IN trans_type varchar(45),IN v_am' at line 1
Caused by: org.springframework.data.r2dbc.connectionfactory.init.ScriptStatementFailedException: Failed to execute SQL script statement #7 of class path resource [schema.sql]: DELIMITER $$ CREATE PROCEDURE make_transaction(IN trans_type varchar(45),IN v_amount decimal(17,2),IN trans_id varchar(255) ) exitSub:BEGIN DECLARE EXIT HANDLER for SQLEXCEPTION BEGIN DECLARE errNom INT UNSIGNED DEFAULT 0; nested exception is io.r2dbc.spi.R2dbcBadGrammarException: [1064] [42000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE PROCEDURE make_transaction(IN trans_type varchar(45),IN v_am' at line 1
Caused by: io.r2dbc.spi.R2dbcBadGrammarException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE PROCEDURE make_transaction(IN trans_type varchar(45),IN v_am' at line 1

从 mysql 文档中收到此语法错误分隔符语法似乎是正确的我想知道可能是什么问题

这是初始化代码

@TestConfiguration
    static class TestConfig {
        @Bean
        public ConnectionFactoryInitializer initializer() {
            ConnectionFactoryOptions options = MySQLR2DBCDatabaseContainer.getOptions(database);

            ConnectionFactoryInitializer initializer = new ConnectionFactoryInitializer();
            initializer.setConnectionFactory(ConnectionFactories.get(options));

            CompositeDatabasePopulator populator = new CompositeDatabasePopulator();
            populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("schema.sql")));
            populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("data.sql")));
            initializer.setDatabasePopulator(populator);

            return initializer;
        }
    }

注意：当我删除存储过程代码，脚本运行正常。并且存储过程在我的本地数据库以及

脚本

DROP TABLE IF EXISTS `bank_account`;

CREATE TABLE `bank_account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `balance` decimal(17,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


DROP TABLE IF EXISTS `transactions`;

CREATE TABLE `transactions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `transaction_type` varchar(255) DEFAULT NULL,
  `reference` varchar(255) DEFAULT NULL,
  `amount` decimal(17,2) DEFAULT NULL,
  `creation_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `error_log`;

CREATE TABLE `error_log` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `date_in` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `corrected_by` varchar(255) NOT NULL DEFAULT '',
  `proc_name` varchar(50) NOT NULL DEFAULT '',
  `node_id` int NOT NULL DEFAULT '-1',
  `err_no` int NOT NULL DEFAULT '0',
  `err_text` varchar(255) DEFAULT '',
  `row_no` int NOT NULL DEFAULT '0',
  `value1` varchar(255) DEFAULT '',
  `value2` varchar(255) DEFAULT '',
  `text1` varchar(255) DEFAULT '',
  `text2` varchar(255) DEFAULT '',
  `long_text` mediumtext,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `err_log_time_idx` (`date_in`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=COMPACT;

DELIMITER $$

CREATE  PROCEDURE make_transaction(IN trans_type varchar(45),IN v_amount decimal(17,2),IN trans_id varchar(255) )
exitSub:BEGIN
 
        DECLARE EXIT HANDLER for SQLEXCEPTION
        BEGIN
          DECLARE errNom INT UNSIGNED DEFAULT 0;
          DECLARE errText VARCHAR (50);
          GET DIAGNOSTICS CONDITION 1 errText = MESSAGE_TEXT, errNom = MYSQL_ERRNO;
          INSERT INTO error_log(proc_name,err_no,err_text,row_no,value1,long_text)
               VALUES ('make_transaction',errNom,errText,rowNo,concat('trans_id:',trans_id),'EXCEPTION');
        END;
        if v_amount < 0 then select 'negative amounts not allowed' as description; LEAVE exitSub; end if;
        if not exists(select `reference` from transactions where `reference`=trans_id)  then
            insert into transactions (transaction_type,amount,`reference`)
            values(trans_type,v_amount,trans_id);
            update bank_account set balance = if(trans_type='DEPOSIT',balance+v_amount,balance-v_amount);
            select 'success' as description;
            LEAVE exitSub;
        end if;
        select 'dupicate transaction' as description;

END $$
DELIMITER ;

编辑中运行良好：

经过研究，我使用了 testcontainers initscript 它现在执行而不会中断，但现在似乎除了创建存储过程之外所有其他语句都在执行。因为当我运行测试时，对于执行该过程的测试，我收到 PROCEDURE does not exit 错误。

private static final MySQLContainer database = new MySQLContainer("mysql:8");
    static {
        try {
            final String script = Resources.toString(Resources.getResource("schema.sql"), Charsets.UTF_8);
            database.start();
            ScriptUtils.executeDatabaseScript(new JdbcDatabaseDelegate(database,""), "schema.sql", script, false, false, DEFAULT_COMMENT_PREFIX, 
                    DEFAULT_STATEMENT_SEPARATOR, "$$", "$$$");
        } catch (Exception ex) {
            Logger.getLogger(TransactionServiceTest.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

失败的测试错误

TransactionServiceTest.depositFundsSuccess expectation "assertNext" failed (expected: onNext();
actual:onError(org.springframework.r2dbc.BadSqlGrammarException: 
executeMany; bad SQL grammar [call make_transaction(?,?,?)]; 
nested exception is io.r2dbc.spi.R2dbcBadGrammarException: [1305] [42000] PROCEDURE test.make_transaction does not exist))

Answer 1

因此，最好的方法是将SQL文件复制到容器中，并在使用 /docker-entrypoint-initdb.d的容器的初始化中执行。这是SQL团队的建议。因此，这就是实现它的方法。

 private static final MySQLContainer database = new MySQLContainer("mysql:8");
    static {
        try {
            database.withCopyFileToContainer(MountableFile.forClasspathResource("schema2.sql"), "/docker-entrypoint-initdb.d/schema.sql");
            database.start();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }       
    }