Running MySQL query file from Grails controller

Other Questions MySQL
1

I’m working with Grails 3.0.9 and need to run a MySQL script file from inside a controller method. My approach isn’t working and I’m getting syntax errors.

Here’s my current implementation:

import groovy.sql.Sql
import grails.util.Holders

def void resetTables() {
    String scriptPath = 'scripts/reset_tables.sql'
    String queryContent = new File(scriptPath).text
    def connection = Sql.newInstance(Holders.config.dataSource.url, 
                                   Holders.config.dataSource.username, 
                                   Holders.config.dataSource.password, 
                                   Holders.config.dataSource.driverClassName)
    connection.execute(queryContent)
}

My SQL file reset_tables.sql contains:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE user_data;
TRUNCATE order_info;
TRUNCATE product_details;
SET FOREIGN_KEY_CHECKS = 1;

The error I’m seeing is:

WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Warning Code: 1064, SQLState: 42000
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'TRUNCATE user_data;

The weird thing is that when I run this same SQL script directly in MySQL it works fine. So the issue seems to be with how I’m executing it through Grails, not the SQL itself. What am I doing wrong here?

2

JDBC can’t handle multiple SQL statements in one execute call. When you dump the whole file as a single string, the driver thinks it’s malformed SQL instead of separate commands. I ran into this same issue migrating legacy database scripts in Grails. You need to parse the SQL file and run each statement individually. Split the content by semicolons, filter out empty lines, then loop through and execute each statement. Also, use the dataSource bean directly instead of creating a new SQL connection - it’ll use your existing connection pool properly. This approach has worked for me across different MySQL versions and complex migration scripts.

3

totally agree! that error is usually from running multiple commands at once. you need to split by ‘;’ and run each line separately. also, check for extra spaces or new lines in your sql file, they might mess things up!