Announcement Announcement Module
Collapse
No announcement yet.
AclService -- BadSqlGrammarException Page Title Module
Move Remove Collapse
This topic is closed
X
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • AclService -- BadSqlGrammarException

    I am using the AclService with a MySQL database.
    The following code gives the following error.
    Could you tell me what I am doing wrong?

    Code:
    	
    
    	@Test
    	public void test1b() {
    
    		Authentication authRequest = new UsernamePasswordAuthenticationToken(
    				"jsmith1", "zzz1234", AuthorityUtils.createAuthorityList("ROLE_IGNORED"));
    		SecurityContextHolder.getContext().setAuthentication(authRequest);
    
    		final ObjectIdentity objectIdentity = new ObjectIdentityImpl(Employee.class, new Long(8));
    		createAcl(objectIdentity);
    	}
    
    	@Transactional(propagation = Propagation.REQUIRED, readOnly = false)
    	private void createAcl(ObjectIdentity objectIdentity) {
    		mutableAclService.createAcl(objectIdentity);
    	}
    Error:

    org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [call identity()]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorEx ception: PROCEDURE arrayer.identity does not exist
    at org.springframework.jdbc.support.SQLExceptionSubcl assTranslator.doTranslate(SQLExceptionSubclassTran slator.java:94)
    at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:406)
    at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:455)
    at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:463)
    at org.springframework.jdbc.core.JdbcTemplate.queryFo rObject(JdbcTemplate.java:471)
    at org.springframework.jdbc.core.JdbcTemplate.queryFo rObject(JdbcTemplate.java:476)
    at org.springframework.jdbc.core.JdbcTemplate.queryFo rLong(JdbcTemplate.java:480)
    at org.springframework.security.acls.jdbc.JdbcMutable AclService.createOrRetrieveSidPrimaryKey(JdbcMutab leAclService.java:219)
    at org.springframework.security.acls.jdbc.JdbcMutable AclService.createObjectIdentity(JdbcMutableAclServ ice.java:152)
    at org.springframework.security.acls.jdbc.JdbcMutable AclService.createAcl(JdbcMutableAclService.java:10 7)
    at org.mycompany.arrayer.AclTest.createAcl(AclTest.ja va:80)
    at org.mycompany.arrayer.AclTest.test1b(AclTest.java: 48)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.junit.runners.model.FrameworkMethod$1.runRefle ctiveCall(FrameworkMethod.java:44)
    at org.junit.internal.runners.model.ReflectiveCallabl e.run(ReflectiveCallable.java:15)
    at org.junit.runners.model.FrameworkMethod.invokeExpl osively(FrameworkMethod.java:41)
    at org.junit.internal.runners.statements.InvokeMethod .evaluate(InvokeMethod.java:20)
    at org.springframework.test.context.junit4.statements .RunBeforeTestMethodCallbacks.evaluate(RunBeforeTe stMethodCallbacks.java:74)
    at org.springframework.test.context.junit4.statements .RunAfterTestMethodCallbacks.evaluate(RunAfterTest MethodCallbacks.java:82)
    at org.springframework.test.context.junit4.statements .SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.springframework.test.context.junit4.SpringJUni t4ClassRunner.runChild(SpringJUnit4ClassRunner.jav a:240)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild( BlockJUnit4ClassRunner.java:50)
    at org.junit.runners.ParentRunner$3.run(ParentRunner. java:193)
    at org.junit.runners.ParentRunner$1.schedule(ParentRu nner.java:52)
    at org.junit.runners.ParentRunner.runChildren(ParentR unner.java:191)
    at org.junit.runners.ParentRunner.access$000(ParentRu nner.java:42)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRu nner.java:184)
    at org.springframework.test.context.junit4.statements .RunBeforeTestClassCallbacks.evaluate(RunBeforeTes tClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements .RunAfterTestClassCallbacks.evaluate(RunAfterTestC lassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.ja va:236)
    at org.springframework.test.context.junit4.SpringJUni t4ClassRunner.run(SpringJUnit4ClassRunner.java:180 )
    at org.apache.maven.surefire.junit4.JUnit4TestSet.exe cute(JUnit4TestSet.java:62)
    at org.apache.maven.surefire.suite.AbstractDirectoryT estSuite.executeTestSet(AbstractDirectoryTestSuite .java:140)
    at org.apache.maven.surefire.suite.AbstractDirectoryT estSuite.execute(AbstractDirectoryTestSuite.java:1 27)
    at org.apache.maven.surefire.Surefire.run(Surefire.ja va:177)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.maven.surefire.booter.SurefireBooter.ru nSuitesInProcess(SurefireBooter.java:345)
    at org.apache.maven.surefire.booter.SurefireBooter.ma in(SurefireBooter.java:1009)


    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorEx ception: PROCEDURE arrayer.identity does not exist
    at sun.reflect.NativeConstructorAccessorImpl.newInsta nce0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInsta nce(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Construc tor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:40 9)
    at com.mysql.jdbc.Util.getInstance(Util.java:384)
    at com.mysql.jdbc.SQLError.createSQLException(SQLErro r.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:3566)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:3498)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:19 59)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :2113)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionIm pl.java:2562)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionIm pl.java:2512)
    at com.mysql.jdbc.StatementImpl.executeQuery(Statemen tImpl.java:1476)
    at org.apache.commons.dbcp.DelegatingStatement.execut eQuery(DelegatingStatement.java:208)
    at org.apache.commons.dbcp.DelegatingStatement.execut eQuery(DelegatingStatement.java:208)
    at org.springframework.jdbc.core.JdbcTemplate$1QueryS tatementCallback.doInStatement(JdbcTemplate.java:4 40)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:395)
    ... 42 more

  • #2
    Well, if you look at the code, the query that is being invoked is "sidIdentityQuery", which (by default) is call identity().
    I believe you would need to change this (and the other queries using identity()) to "SELECT LAST_INSERT_ID()".

    You may want to review all the queries used by the ACL subsystem to determine if any others are not valid for MySQL (the default syntax works on most databases, except for special cases like this).

    Comment

    Working...
    X