ResourceDatabasePopulator should ignore MySQL single-line comments in SQL scripts [SPR-16594]

[spring-projects-issues]

Urs Wiss opened SPR-16594 and commented

In MySQL, starting a line with a hash key is a valid comment (see https://dev.mysql.com/doc/refman/5.7/en/comments.html)

Currently, ResourceDatabasePopulator doesn't recognize lines starting with a hash key as comments. See the example https://github.com/urswiss/spring-jdbc-comment, it's based on h2 but it nonetheless illustrates the point I think.

---

No further details from SPR-16594

[spring-projects-issues]

Juergen Hoeller commented

The comment prefix is configurable already. Are you specifically asking for "#" to be considered as a comment prefix by default, next to "--"?

[spring-projects-issues]

Urs Wiss commented

yes.
Let me give you some background of how we discovered this issue. I had to add some new testdata to an existing sql. Since we are using MySQL, I had the script connected to the database in IntelliJ. I used the IntelliJ Shortcut for adding a comment (CMD + /), which resulted in a comment that started with a '#'. I ran the script against the database from the IDE and everything worked fine. When I ran the script from inside the testcase however, I got a strange 'invalid constraint' exception. After investigating for a while we discovered that the statement on the line below the comment was not executed, since it also had been commented out.
So yes it would have saved me (and possibly others) time if the "#" had been considered as a comment prefix in addition to "--".
Note that there are also comments embedded between /* and */, but that seems to work fine.

[prateekkapoor]

Can I pick this up?

[prateekkapoor]

I was facing this issue with spring for prepopulating data in database. After analyzing sprring code I figured out that it can be fixed by adding "#" to DEFAULT_COMMENT_PREFIXES in ScriptUtils class.

Let me know if this correct approach and I can take this up.

[sbrannen]

After analyzing spring code I figured out that it can be fixed by adding "#" to DEFAULT_COMMENT_PREFIXES in ScriptUtils class.

Let me know if this correct approach and I can take this up.

Yes, that would be the correct approach; however, doing so may be a breaking change for users whose SQL scripts utilize # for a different purpose.

So please hold off on submitting a PR until the team has decided if we want to make this change.