MySQL: Script Runs in Query Tool, Consistently Fails in Roundhouse

[cmiles74]

I'm seeing several scripts that run without issue in my query tool (DataGrip) but are failing for some reason when Roundhouse executes them. The error is always the same, "Index was outside the bounds of the array." In some cases the DDL was generated by MySQL (for a view or procedure) with an inordinate amount of parenthesis; in those cases eliminating the parenthesis fixed the issue. But I have others that look reasonable to me but are still failing.

I'm going to take a look around and see if I can figure out where and why these scripts are failing. If anyone has any pointers or has seen similar issues, I would very much appreciate any pointers.

Thank you!

Running SAMPLE_MIGRATION.sql on SAMPLE_SERVER - SAMPLE_DATABASE.
RoundhousE encountered an error.
System.IndexOutOfRangeException: Index was outside the bounds of the array.
   at roundhouse.databases.mysql.MySqlDatabase.run_sql(String sql_to_run, ConnectionType connection_type)
   at roundhouse.migrators.DefaultDatabaseMigrator.run_sql(String sql_to_run, String script_name, Boolean run_this_script_once, Boolean run_this_script_every_time, Int64 version_id, EnvironmentSet environment_set, String repository_version, String repository_path, ConnectionType connection_type)
   at roundhouse.runners.RoundhouseMigrationRunner.traverse_files_and_run_sql(String directory, Int64 version_id, MigrationsFolder migration_folder, EnvironmentSet migrating_environment_set, String repository_version, ConnectionType connection_type)
   at roundhouse.runners.RoundhouseMigrationRunner.log_and_traverse(MigrationsFolder folder, Int64 version_id, String new_version, ConnectionType connection_type)
   at roundhouse.runners.RoundhouseMigrationRunner.run()
Index was outside the bounds of the array.
System.IndexOutOfRangeException: Index was outside the bounds of the array.
   at roundhouse.databases.mysql.MySqlDatabase.run_sql(String sql_to_run, ConnectionType connection_type)
   at roundhouse.migrators.DefaultDatabaseMigrator.run_sql(String sql_to_run, String script_name, Boolean run_this_script_once, Boolean run_this_script_every_time, Int64 version_id, EnvironmentSet environment_set, String repository_version, String repository_path, ConnectionType connection_type)
   at roundhouse.runners.RoundhouseMigrationRunner.traverse_files_and_run_sql(String directory, Int64 version_id, MigrationsFolder migration_folder, EnvironmentSet migrating_environment_set, String repository_version, ConnectionType connection_type)
   at roundhouse.runners.RoundhouseMigrationRunner.log_and_traverse(MigrationsFolder folder, Int64 version_id, String new_version, ConnectionType connection_type)
   at roundhouse.runners.RoundhouseMigrationRunner.run()
   at roundhouse.console.Program.run_migrator(ConfigurationPropertyHolder configuration)
   at roundhouse.console.Program.Main(String[] args)

 An error occurred while running the script: 1

[cmiles74]

It looks like the issue revolves around declaration of delimiters in the stored procedures. The author of these scripts consistently ends their stored procedure declarations with a trailing delimiter declaration. It looks like Roundhouse is trying to find more SQL to execute after this last delimiter declaration and ends up with an invalid index (MySqlDatabase.cs).

If I remove these trailing delimiter statements then the scripts run without issue. That's what I am going to do for now. I would like to change the way this is handled in MySqlDatabase and the tokenizer so that it can handle this situation, but I am not sure if I will have the time in the next couple of weeks.

DELIMITER $$
CREATE DEFINER=`app_schema`@`%`
PROCEDURE `testProcedure`()
SQL SECURITY INVOKER
BEGIN
  ...the actual procedure...
END
$$

DELIMITER ;

[erikbra]

Thank you for the bug report. This should be fairly easy do replicate in a unit test too, if we have a script. If you have a chance to create a PR, that would be fantastic.

Just to be sure: Which version of RoundhousE are you using?

[cmiles74]

I will take a look at the test cases and put one together. Right now, I'm working against a build of the "master" branch, 0.9.2.0.

[cmiles74]

I wrote up a short project that demonstrates the issue, I also wanted to make sure I understood what the problem actually was. You can take a look at the project here...

• https://github.com/cmiles74/rh-mysql-sproc-test

I would be happy to create a new unit test... Where would I put that in the project, do you have an example I could model on?

Thank you for your help with this!

[erikbra]

Thanks a lot for an excellent example. If all bug reports were as good at this, the world would be a better place ;)

To be honest, I'm a bit confused about the way of writing tests in RoundhousE myself, it's very BDD-driven style, and I'm not very proficient in it myself. But, looking at it, it looks as if writing a new Concern for MySql in roundhouse/product/roundhouse.tests/sqlsplitters/StatementSplitterSpecs.cs would be the thing to do.

There are lots of tests there for SQLServer and PL/Sql, but not so many for e.g. MySQL. It could of course be that this is a general problem, that a trailing delimiter is a problem with all database providers, I'm not sure.

I have some plans for ramping up and improving the test, but for now, I think creating a test in the file mentioned above would be best. If you (like me) find the test a bit awkward to work with, please feel free to create a new test suite in pure NUnit in a new file next to the StatementSplitterSpecs.cs.

[cmiles74]

This behavior is kind of weird and might be specific to MySQL. I believe we need to parse the SQL into statements and then we can provide each statement to MySQL to execute individually; we don't need to send the delimiter itself.

I'll take a close look at those test cases. I have also submitted a PR with a fix. :-)

Thank you!

[cmiles74]

I've added a test just for the situation with the trailing delimiter.

[erikbra]

Closed in PR #357

[jpbulman]

If anyone is still having this issue when using older versions, adding a new line after the trailing DELIMITER ; seemed to work for me

[rigalves]

In my case, I removed some comments that I had at the end of the script and that fixed the issue.