4

I use MySQL5.5 + REST(Jersey) + Spring Security + Spring OAuth2. Right now I'm doing performance testing and noticed that

org.springframework.security.oauth2.provider.token.store.JdbcTokenStore.readAuthentication method is working really slow.. especialy this part of the method:

authentication = jdbcTemplate.queryForObject(selectAccessTokenAuthenticationSql,
                    new RowMapper<OAuth2Authentication>() {
                        public OAuth2Authentication mapRow(ResultSet rs, int rowNum) throws SQLException {
                            return deserializeAuthentication(rs.getBytes(2));
                        }
                    }, extractTokenKey(token));

In order to improve performance I'm going to add MySQL index to the following query:

private static final String DEFAULT_ACCESS_TOKEN_AUTHENTICATION_SELECT_STATEMENT = "select token_id, authentication from oauth_access_token where token_id = ?";

on token_id field but the main issue is that regarding the official oauth2 Spring database schema, for example https://github.com/spring-projects/spring-security-oauth/blob/master/spring-security-oauth2/src/test/resources/schema.sql

create table oauth_access_token (
  token_id VARCHAR(256),
  token LONGVARBINARY,
  authentication_id VARCHAR(256) PRIMARY KEY,
  user_name VARCHAR(256),
  client_id VARCHAR(256),
  authentication LONGVARBINARY,
  refresh_token VARCHAR(256)
);

token_id is VARCHAR(256) and due to a MySQL bugs or limitations I'm unable to add this index (for example MySQL Specified key was too long)..

So my question is - is it mandatory to have token_id as VARCHAR(256) or I can change it to VARCHAR(255) ?

Community
  • 1
  • 1
alexanoid
  • 24,051
  • 54
  • 210
  • 410
  • Check the content of that column and see what is in there. I would still expect it that it the size needs to be a power of 2 due to the fact that a byte representation is stored in there. – M. Deinum Jun 05 '15 at 05:58
  • Also, I have found another oauth db scheme that uses VARCHAR(255) - https://gist.github.com/leolin310148/3b2cb7d83ba0ec9e1d58 – alexanoid Jun 05 '15 at 06:35
  • Did you try it and it didn't work? The token value format depends on your implementation, so it's impossible to say in general. The default tokens are UUIDs though, so they would fit in 255 chars I would think. – Dave Syer Jun 05 '15 at 11:03
  • Yep, I have added the PK on token_id, it works but unfortunately it reduces performance.. – alexanoid Jun 05 '15 at 11:46
  • Did you solved somehow the performance of oauth2 jdbc? Thanks – bilak Sep 20 '16 at 12:54

0 Answers0