Column Task.task_key must be case sensitive
aabeling opened this issue · 7 comments
We are storing the data in a mysql database.
While updating the tasks in sonarquest with the tasks from sonarqube we got a NonUniqueResultException
because the search for an existing task by key with value "AWc3A2KEoXX3DuVBrVTc" produced two results: one with
AWc3A2KEoXX3DuVBrVTc
and the other one with
AWc3A2KEoXX3DuVBrVTC
I was able to fix that in our mysql by
alter table Task modify task_key varchar(256) character set binary
but I am not sure if that is the best solution.
This is mysql-specific, I think.
How can this be included in the flyway scripts?
I spent some time on research with this issue and I think this really is Mysql specific. The h2 db is case sensitive by default.
Tbh a db independent and correct solution could be to use a different key field and to remove the unique constraint from "key" while simultaneously writing new SQL statements that search the column with case sensitivity.
That requires a lot of work.
After having a look at the resulting schema the column type VARBINARY should work which is supported by H2 and MySQL.
btw: task_key should be indexed
Thanks for looking into this again!
We keep the index then and I see if I can update the flyway scripts and test the change later today.
More info:
our database specialist suggested to use
alter table Task modify task_key varchar(256) collate latin1_bin
This again leads to the problem of different scripts for different database engines. The flyway FAQs suggest:
https://flywaydb.org/documentation/faq.html#db-specific-sql
I tried to set the task.task_key column to VARBINARY as discussed above. This required the Task field to be set with a columnDefinition accordingly otherwise SpringBoot did not start the application.
However, under my test H2 database, when the gm tries to insert tasks into the modified table, the following exception is thrown (where 'AWdD43RWfimTzgTv4Ewy' is the SonarQube key):
Hexadecimal string contains non-hex character: "AWdD43RWfimTzgTv4Ewy"; SQL statement:
insert into task (id, enddate, gold, task_key, participation_id, quest_id, startdate, status, title, world_id, xp, component, debt, issue_key, severity, type, task_type) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'STANDARD') -- (NULL, ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, 'STANDARD') [90004-197]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.util.StringUtils.convertHexToBytes(StringUtils.java:926)
at org.h2.value.Value.convertTo(Value.java:1075)
at org.h2.table.Column.convert(Column.java:177)
at org.h2.command.dml.Insert.insertRows(Insert.java:166)
at org.h2.command.dml.Insert.update(Insert.java:134)
at org.h2.command.CommandContainer.update(CommandContainer.java:102)
at org.h2.command.Command.executeUpdate(Command.java:261)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:199)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:153)
at sun.reflect.GeneratedMethodAccessor157.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
at com.sun.proxy.$Proxy89.executeUpdate(Unknown Source)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
I am at a loss on how to solve this... any ideas?
See b35a603