Older verisons of MySQL will result in sql syntax error and eventually disabling many functionality from AMBARI UI.

[#1]

User tries to decommission/recommission nodes from Ambari UI, nothing happens on the UI and it seems like the operation did not go through.

Ambari-server Logs:

WARN [C3P0PooledConnectionPoolManager[identityToken->2s8bny9j1mxgjkn9oj5d8|79679221]-HelperThread-#0] StatementUtils:223 - Statement close FAILED.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 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1  
at sun.reflect.GeneratedConstructorAccessor198.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)  
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)  
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)  
at com.mysql.jdbc.Util.getInstance(Util.java:386)  
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)

ROOT CAUSE

The default JDBC driver installed with Ambari doesn't support MySQL 5.6.25.

NOTE BEFORE THE WORKAROUND CAN BE FOLLOWED

Make sure to delete the triggers from Ambari db before we follow the steps in the workaround section. Else it might result into an outage if there are to many triggers waiting in DB to be triggered when the connector version is fixed. Ambari Db tables to check:

• qrtz_calendars
• qrtz_fired_triggers
• qrtz_job_details
• qrtz_locks
• qrtz_paused_trigger_grps
• qrtz_scheduler_state
• qrtz_simple_triggers
• qrtz_simprop_triggers
• qrtz_triggers

WORKAROUND

Update mysql connector from http://mvnrepository.com/artifact/mysql/mysql-connector-java. Once the above is updated

ambari-server setup --jdbc-db=mysql --jdbc-driver=/usr/share/java/mysql-connector-java.jar

where jdbc-driver is the path to the new driver.