[RANGER-1955] Wrong quoting in Ranger SQL install scripts

• Type: Bug
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 0.7.0
• Fix Version/s: 2.0.0
• Component/s: Ranger
• Labels: installation sql
• Assignee: Pradeep Agrawal
• Reporter: Sören Gutzeit
• Created: 16/Jan/18 10:26
• Updated: 23/Apr/19 05:52
• Resolved: 23/Apr/19 05:52

Description

We had some problems with the SQL installation scripts 007-updateBlankPolicyName.sql and 008-removeTrailingSlash.sql with our current MySQL version 5.7.16.

The problems are the double quotes. In both scripts, there is this expression included:

"%Y%m%d%H%i%s"

Our MySQL version doesn't like the double quotes in this way, so the execution of the scripts run into an exception:

2017-12-14 17:00:50,721 [I] Patch 007-updateBlankPolicyName.sql is being applied..
 2017-12-14 17:00:50,721 [JISQL] /usr/lib/jvm/jre-1.8.0-openjdk-1.8.0-1.b12.el7_4.x86_64/bin/java -cp /usr/hdp/2.6.2.0-205/ranger-admin/ews/lib/mysql-connector-java.jar:/usr/hdp/current/ranger-admin/jisql/lib/* org.apache.util.sql.Jisql -driver mysqlconj -cstring jdbc:mysql://xxx/rangert2b -u 'rangert2' -p '********' -noheader -trim -c \; -input /usr/hdp/current/ranger-admin/db/mysql/patches/007-updateBlankPolicyName.sql
 Error executing: call updateBlankPolicyName(); 
 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '%Y%m%d%H%i%s' in 'field list'
 SQLException : SQL state: 42S22 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '%Y%m%d%H%i%s' in 'field list' ErrorCode: 1054
 2017-12-14 17:00:51,199 [JISQL] /usr/lib/jvm/jre-1.8.0-openjdk-1.8.0-1.b12.el7_4.x86_64/bin/java -cp /usr/hdp/2.6.2.0-205/ranger-admin/ews/lib/mysql-connector-java.jar:/usr/hdp/current/ranger-admin/jisql/lib/* org.apache.util.sql.Jisql -driver mysqlconj -cstring jdbc:mysql://xxx/rangert2b -u 'rangert2' -p '********' -noheader -trim -c \; -query "select version from x_db_version_h where version = '007' and active = 'Y';"
 2017-12-14 17:00:51,523 [JISQL] /usr/lib/jvm/jre-1.8.0-openjdk-1.8.0-1.b12.el7_4.x86_64/bin/java -cp /usr/hdp/2.6.2.0-205/ranger-admin/ews/lib/mysql-connector-java.jar:/usr/hdp/current/ranger-admin/jisql/lib/* org.apache.util.sql.Jisql -driver mysqlconj -cstring jdbc:mysql://xxx/rangert2b -u 'rangert2' -p '********' -noheader -trim -c \; -query "delete from x_db_version_h where version='007' and active='N' and updated_by='XXX';"
 2017-12-14 17:00:51,868 [E] 007-updateBlankPolicyName.sql import failed!
Command failed after 1 tries

  (Install output from Ambari)

 

We solved the problem by replacing the double quotes by single quotes (" -> ') and restarted the installation with a new database.