SQL Errors

Below are some of the more common SQL errors a user may encounter, along with guidance on how to troubleshoot them.

Error: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

This is a generic SQL error that indicates a problem with the SQL syntax. If you’re unsure of which query or specific line of SQL was at fault, open the Accelerator log file and search for this error message. Before the error occurs, there should be information about which query was being launched - most of the time this will be an audience query from a bulk/marketing campaign.

Toward the bottom of the stack trace you will see Caused by. The last Caused by of that trace should provide more specific details about what part of the query was incorrect. It may be as simple as a missing keyword if the error happens every time the query is run. If it’s happening intermittently, it may be an issue with the underlying data, or it could be due to data variables used by the query. For example, an audience query that contains a FreeMarker if condition may be sending different SQL each time it runs.

2023-01-18 11:10:41,612 WARN [mg-task-USER_TASK3] c.m.a.s.AsyncTaskRunner Error executing background task Count SQL ContextData: '[ContextDataName]'

com.messagegears.accelerator.modules.datasource.DatasourceReaderInitializationException:java.sql.SQLException: java.util.concurrent.ExecutionException: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:

syntax error line 21 at position 119 unexpected '',''.

These errors indicate that there is invalid SQL being used in a supplemental data query called [ContextDataName].  The line and position number indicate what part of the query has a syntax problem, so the resolution will be to investigate and modify the supplemental data query.

You may see a similar message for an audience query, and the troubleshooting/resolution would be the same.

If this only happens intermittently when this query is used, then the issue is likely with the data variables or the underlying data itself.

Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'message' at row 1

The data being inserted by the query exceeds the allowable limit of the column.  For example, if the application attempted to insert a 100 character string into a column that only allows 50 characters, this error would be shown.

In this specific example, the message column is referring to a field on the accelerator.notification table (in Accelerator’s MySQL database).  This table stores notifications and, in this case, error messages returned by an external audience database.  The message field is currently varchar(2000), so if the target database returns an error message longer than 2,000 characters, this error will be thrown by Accelerator.  The length of this field will be extended in a future release of Accelerator, so this specific error should become deprecated at that time.

Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: Single-row subquery returns more than one row.

This error may be thrown by Snowflake or any other target database.  It indicates that the SQL query contains a subquery which is only allowed to find one result, but instead it’s finding more than one.

This is usually something that happens as part of a campaign trigger insert; the SQL statement is meant to insert a row into a target database table, but it’s finding more than one result with a subquery, so the insert statement fails.  In this scenario, check the campaign settings to see which pre- and post-campaign triggers are in use.  Then, investigate each step of the campaign trigger.  Check each subquery in the affected SQL statement by running it individually/separately from the full SQL.  If any of them return more than one result, you’ll need to either modify the SQL or modify the underlying data so that the subquery can only find one result.

Failed to launch campaign 'Invalid query; nested exception is org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [insert into {schema.database.table_name} ({field_names})

If you see a message like this, where the query references inserting data into a table, this error will be from a campaign trigger.  Follow the steps from the previous example to determine whether it was a pre- or post-campaign trigger, which one it was, and which step of the trigger ran into an error.  This is likely to be a SQL syntax error, so you’ll need to review the SQL to make sure the insert script is valid for the destination table.

If it was a pre-campaign trigger that failed, then the job itself likely failed to go out as well.  If you do not see a request ID on the job analytics page, that indicates the job did not make it to the MessageGears Cloud, and emails were not delivered to recipients.  The job can be relaunched, but the SQL syntax error would need to be resolved first.

If it was a post-campaign trigger, this error means the job was successfully sent to the MessageGears Cloud, but the database insert failed after the job launched.  You should see a request ID on the job analytics page in this case, which means that the job itself went out.  There is no need to relaunch the campaign, and doing so would result in duplicate emails being delivered.  In this scenario, review the SQL for errors and make changes as needed.  In order to make up for the missed insert, the best option is to do a one-time manual insert directly in your target database.