Bug #48759 some values in advisor expression generate MySQLSyntaxErrorException in logs
Submitted: 13 Nov 2009 12:33 Modified: 9 Nov 2015 16:32
Reporter: Andrii Nikitin Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Enterprise Monitor: Server Severity:S3 (Non-critical)
Version:2.1.0.1096 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[13 Nov 2009 12:33] Andrii Nikitin 
Description:
It looks that if value ends with '\' character invalid SQL generated and error is printed in dashboard logs:

com.mysql.etools.exception.ExpressionException: E1802: ExpressionException
com.mysql.etools.monitor.bo.rule.SqlExpressionEvaluator.evaluateBooleanExpression

How to repeat:
1. Make sure expression used by 'broad_host_specifier' data item is cut by group_concat_max_let and ends with '\' character. 

SELECT GROUP_CONCAT('\\\\\n* ', '\'',user,'\'' ORDER BY user) as user FROM mysql.user WHERE host = '%';

E.g. you may add following user and limit group_concat_max_len as following :

create user '__a'@'%'; -- <-- this should be first user in order
set global group_concat_max_len=12; <-- in real life bigger values lead to the bug

then statement above should return:
+--------------+
| user         |
+--------------+
| \\
* '__a',\ |
+--------------+

2. Restart MEM agent to make sure new group_concat_max_len is applied to agent connection

3. Schedule rule "Account Has An Overly Broad Host Specifier" from "Security" group for this server.

4. Check a while and observe error in mysql-monitor.log :

2009-11-13 12:37:52,899 ERROR [em-worker-pool-thread-1:com.mysql.advisor.com.mysql.etools.monitor.bo.rule.ListeningRuleEvaluator] account_has_broad_host_specifier.name: (%user% != THRESHOLD)(("\\
* '__a',\" != ""))
com.mysql.etools.exception.ExpressionException: E1802: ExpressionException: [account_has_broad_host_specifier.name, java.lang.RuntimeException: account_has_broad_host_specifier.name SQL: SELECT (("\\
* '__a',\" != "")): com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"\\
* '__a',\" != ""))' at line 1

SQL: SELECT (("\\
* '__a',\" != ""))
ErrorCode: 1064
, (%user% != THRESHOLD), {THRESHOLD="", %user%="\\
* '__a',\"}]
	at com.mysql.etools.monitor.bo.rule.SqlExpressionEvaluator.evaluateBooleanExpression(SqlExpressionEvaluator.java:51)
	at com.mysql.etools.monitor.bo.rule.FailoverExpressionEvaluator.evaluateBooleanExpression(FailoverExpressionEvaluator.java:57)
	at com.mysql.etools.monitor.bo.rule.RuleEvaluator.evaluateThresholds(RuleEvaluator.java:65)
	at com.mysql.etools.monitor.bo.rule.ListeningRuleEvaluator.scanThresholds(ListeningRuleEvaluator.java:409)
	at com.mysql.etools.monitor.bo.rule.ListeningRuleEvaluator.evaluate(ListeningRuleEvaluator.java:215)
	at com.mysql.etools.monitor.bo.rule.ListeningRuleEvaluator.processDatum(ListeningRuleEvaluator.java:346)
	at com.mysql.etools.monitor.bo.rule.ListeningRuleEvaluator.access$100(ListeningRuleEvaluator.java:49)
	at com.mysql.etools.monitor.bo.rule.ListeningRuleEvaluator$1.saved(ListeningRuleEvaluator.java:72)
	at com.mysql.etools.monitor.pom.InstanceAttributeListenerManager.notifyListeners(InstanceAttributeListenerManager.java:122)
	at com.mysql.etools.monitor.pom.hib.HibDcNgReaderWriter$14.run(HibDcNgReaderWriter.java:1475)
	at com.mysql.etools.util.LogServiceTaskDecorator$LoggingRunnable.run(LogServiceTaskDecorator.java:69)
	at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.RuntimeException: account_has_broad_host_specifier.name SQL: SELECT (("\\
* '__a',\" != "")): com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"\\
* '__a',\" != ""))' at line 1

SQL: SELECT (("\\
* '__a',\" != ""))
ErrorCode: 1064

	at com.mysql.etools.monitor.bo.rule.SqlExpressionEvaluator.evaluateExpression(SqlExpressionEvaluator.java:75)
	at com.mysql.etools.monitor.bo.rule.SqlExpressionEvaluator.evaluateBooleanExpression(SqlExpressionEvaluator.java:48)
	... 18 more
Caused by: com.mysql.util.jdbctemplate.SQLRuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"\\
* '__a',\" != ""))' at line 1

SQL: SELECT (("\\
* '__a',\" != ""))
ErrorCode: 1064

	at com.mysql.util.jdbctemplate.JDBCTemplate.executeQuery(JDBCTemplate.java:150)
	at com.mysql.util.jdbctemplate.JDBCTemplate.getSingleObject(JDBCTemplate.java:252)
	at com.mysql.etools.monitor.bo.rule.SqlExpressionEvaluator$1.execute(SqlExpressionEvaluator.java:68)
	at com.mysql.etools.monitor.pom.hib.HibernateExecutor$3.execute(HibernateExecutor.java:155)
	at com.mysql.etools.monitor.pom.hib.HibernateExecutor.asTransaction(HibernateExecutor.java:188)
	at com.mysql.etools.monitor.pom.hib.HibernateExecutor.execute(HibernateExecutor.java:158)
	at com.mysql.etools.monitor.pom.hib.SchemaMaintenanceExecutor.execute(SchemaMaintenanceExecutor.java:194)
	at com.mysql.etools.monitor.bo.rule.SqlExpressionEvaluator.evaluateExpression(SqlExpressionEvaluator.java:72)
	... 19 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"\\
* '__a',\" != ""))' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.Util.getInstance(Util.java:381)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3536)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3468)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1957)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2086)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2237)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
	at com.mysql.util.jdbctemplate.JDBCTemplate.executeQuery(JDBCTemplate.java:147)
	... 26 more

Suggested fix:
While data item expression may be fixed not to provide strings ending with "\", even if truncated with group_concat_max_len, I am sure error should be fixed on server side.
[13 Nov 2009 12:59] Andrii Nikitin 
Workaround can be adding TRIM() into data item expression :

e.g. for broad_host_specifier will look:

SELECT trim(trailing '\\' from GROUP_CONCAT('\\\\\n* ', '\'',user,'\'' ORDER BY user)) as user FROM mysql.user WHERE host = '%'
[9 Nov 2015 16:32] Mark Leith 
Not an issue in 3.x.