java.sql.SQLException: Incorrect string value: '\xF0\x9F\x9A\xA7 I...' for column 'description' at row 1

[amedee]

When I upgrade the plugin in SonarQube to 2.2.0, I get this error in web.log:

2019.10.16 16:06:57 ERROR web[][o.s.s.p.Platform] Background initialization failed. Stopping SonarQube
org.apache.ibatis.exceptions.PersistenceException:

Error updating database. Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x9A\xA7 I...' for column 'description' at row 1

The error may involve org.sonar.db.rule.RuleMapper.updateDefinition-Inline

The error occurred while setting parameters

Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x9A\xA7 I...' for column 'description' at row 1

    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    at com.sun.proxy.$Proxy44.updateDefinition(Unknown Source)
    at org.sonar.db.rule.RuleDao.update(RuleDao.java:173)
    at org.sonar.server.rule.RegisterRules.update(RegisterRules.java:770)
    at org.sonar.server.rule.RegisterRules.registerRule(RegisterRules.java:376)
    at org.sonar.server.rule.RegisterRules.start(RegisterRules.java:132)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.picocontainer.lifecycle.ReflectionLifecycleStrategy.invokeMethod(ReflectionLifecycleStrategy.java:110)
    at org.picocontainer.lifecycle.ReflectionLifecycleStrategy.start(ReflectionLifecycleStrategy.java:89)
    at org.picocontainer.injectors.AbstractInjectionFactory$LifecycleAdapter.start(AbstractInjectionFactory.java:84)
    at org.picocontainer.behaviors.AbstractBehavior.start(AbstractBehavior.java:169)
    at org.picocontainer.behaviors.Stored$RealComponentLifecycle.start(Stored.java:132)
    at org.picocontainer.behaviors.Stored.start(Stored.java:110)
    at org.picocontainer.DefaultPicoContainer.potentiallyStartAdapter(DefaultPicoContainer.java:1016)
    at org.picocontainer.DefaultPicoContainer.startAdapters(DefaultPicoContainer.java:1009)
    at org.picocontainer.DefaultPicoContainer.start(DefaultPicoContainer.java:767)
    at org.sonar.core.platform.ComponentContainer.startComponents(ComponentContainer.java:135)
    at org.sonar.server.platform.platformlevel.PlatformLevel.start(PlatformLevel.java:90)
    at org.sonar.server.platform.platformlevel.PlatformLevelStartup.access$001(PlatformLevelStartup.java:46)
    at org.sonar.server.platform.platformlevel.PlatformLevelStartup$1.doPrivileged(PlatformLevelStartup.java:82)
    at org.sonar.server.user.DoPrivileged.execute(DoPrivileged.java:46)
    at org.sonar.server.platform.platformlevel.PlatformLevelStartup.start(PlatformLevelStartup.java:79)
    at org.sonar.server.platform.Platform.executeStartupTasks(Platform.java:196)
    at org.sonar.server.platform.Platform.access$400(Platform.java:46)
    at org.sonar.server.platform.Platform$1.lambda$doRun$1(Platform.java:121)
    at org.sonar.server.platform.Platform$AutoStarterRunnable.runIfNotAborted(Platform.java:371)
    at org.sonar.server.platform.Platform$1.doRun(Platform.java:121)
    at org.sonar.server.platform.Platform$AutoStarterRunnable.run(Platform.java:355)
    at java.lang.Thread.run(Thread.java:748)

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x9A\xA7 I...' for column 'description' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:52)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
... 33 common frames omitted
2019.10.16 16:06:57 INFO web[][o.s.p.StopWatcher] Stopping process

It is definitely from the shellcheck plugin, because the error goes away when I delete the plugin's jar and restart SonarQube.

SonarQube version: 7.7.

[sbaudoin]

Hello. Thanks for reporting the issue. You seem to have updated something: what was it? Can you explain what was the state of SQ before you tried to install the plugin so that I can try to reproduce the issue.

Thanks

_{Sent with GitHawk}

[amedee]

Previously I had version 2.1.0 of the plugin installed. I reinstalled version 2.1.0 of the plugin, by downloading the jar file from GitHub, copying it into the extensions directory and restarting SonarQube -> no error.

Then I delete the 2.1.0 jar, download the 2.2.0 jar and restart SonarQube -> error, as seen above.

I'll have a look at the commits between 2.1.0 and 2.2.0 to see if I can help you identify a possible cause.

[amedee]

\xF0\x9F\x9A\xA7 is the UTF-8 code for the "construction sign" emoji, see https://graphemica.com/%F0%9F%9A%A7

When using MySQL as a database, one runs into issues with such Unicode emojis, this blog post describes the issue: https://mathiasbynens.be/notes/mysql-utf8mb4
One could call it a known bug in MySQL's Unicode implementation.
I know about this obscure MySQL bug because I have also experienced in Jira: our customers can't log a ticket when their issue description contains an emoji (what we internally call the "moneybag problem", because we first encountered it in a support question from a customer who wanted to use the moneybag emoji but they couldn't log their support request).

Anyway, long story short.
Are you aware of any 🚧 emojis that you may have introduced in your code between commits e41d75d (release 2.1.0) and dabc700 (release 2.2.0)? Because that would be the most likely cause. When SonarQube loads your plugin, it tries to insert the rules descriptions into its database (hence the for column 'description' in the error). MySQL chokes on a Unicode character in one of the descriptions, returns an error, which fails the plugin upgrade, and causes SonarQube startup to abort.

[amedee]

If you can help me identify the file that contains the problematic unicode character, then I am willing to make a local build of your plugin, try it out in our SonarQube, and then submit a pull request.

[amedee]

I found the culprit by cloning your repo and naively doing

grep --color='auto' --line-number "🚧" --recursive src/

I got this:

src/main/resources/org/sonar/l10n/shellcheck/rules/shellcheck/SC1090.html:15:<h2>🚧 In <a href="https://shellcheck.storage.googleapis.com/index.html"><code>shellcheck-latest</code></a></h2>

ffa76a9 introduced 🚧, I'll leave a comment on the commit.

I'm now going for a lunch break, I'll make the change and submit a PR this afternoon.

[sbaudoin]

Hello,

Thanks for the investigation. I use a tool to write the HTML pages for the rules from the Shellcheck wiki. It appeared that I was not lucky when I built the pages: my generator processed the page https://github.com/koalaman/shellcheck/wiki/SC1090/cdc5988e45b09135f68073f8b7fdec92d6f58c86, which contains the emoji. Sorry about that.

Now the page is fine so I'm going to release a new version as well as updating my generator to warn about emojis. It should be released next week.

[amedee]

I just installed release 2.2.1 from the Github release, I can confirm that it works. Thank you!