Install the memcached plugin on MySQL and access it from Java

Put the memcached plugin in MySQL

1. Install libevent-devel

/bin/bash

# yum -y install libevent-devel

2. Add memcache settings to my.cnf

/etc/my.cnf

daemon_memcached_option = "-p11211"
daemon_memcached_engine_lib_name = innodb_engine.so
daemon_memcached_r_batch_size = 1
daemon_memcached_w_batch_size = 1

3. Install the memcached plugin

mysql

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
mysql> show plugins;

Confirm that the plugin called daemon_memcached is included

4. Create a test table

mysql

mysql> use test
mysql> create table user (id varchar(16), passwd varchar(256), primary key(id)) engine=InnoDB;
mysql> insert into user (id, passwd) values ('user01', '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8');

• The value of the inserted passwd column is sha256 of "password".

5. Make it accessible via memcache

mysql

mysql> insert into innodb_memcache.containers (`name`,`db_schema`,`db_table`,`key_columns`,`value_columns`,`flags`,`cas_column`,`expire_time_column`,`unique_idx_name_on_key`) VALUES ('user', 'test', 'user', 'id', 'passwd',0,0,0,'PRIMARY');
mysql> UNINSTALL PLUGIN daemon_memcached;INSTALL PLUGIN daemon_memcached soname "libmemcached.so";

** @@ (value in name column of innodb_memcache.containers) accessible as .key **

• $ telnet 127.0.0.1 11211 * and when you type * get @@ user.user01 *, it's OK if the sha256 string is returned.

6. Implement the Java side

↓ Download spymemcached from around http://www.java2s.com/Code/Jar/s/Downloadspymemcached27jar.htm

• • Of course, Maven can be used *

This time, when POSTing ID and Pass in JSON, 200 is returned if there is user registration, and 401 is returned if there is no user registration (ID and Pass do not match). Create Rest API with JavaEE JAX-RS. I tried to.

Memcache.java

package memcached;

import java.net.InetSocketAddress;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.util.Map;

import javax.enterprise.context.RequestScoped;
import javax.ws.rs.Consumes;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Response;
import javax.ws.rs.core.Response.ResponseBuilder;

import net.spy.memcached.MemcachedClient;

@RequestScoped
@Path("/auth")
@Produces("application/json")
@Consumes("application/json")
public class Memcache {

	@POST
	public Response auth (Map<String, String> param) {
		ResponseBuilder response = null;
		try {
			MemcachedClient memcachedClient = new MemcachedClient (new InetSocketAddress("192.168.56.102", 11211));
			if ( sha256( param.get("password") ).equals( memcachedClient.get( "@@user." + param.get("id") ).toString() ) ) {
				response = Response.status(200);
			} else {
				response = Response.status(401);
			}
		} catch (Exception e) {
			response = Response.status(400);
		}
		return response.build();
	}

	private String sha256(String plaintext) throws Exception{
		MessageDigest md = MessageDigest.getInstance("SHA-256");
		StringBuilder sb = new StringBuilder();
		md.update(plaintext.getBytes());
	    for (byte b : md.digest()) {
	        String hex = String.format("%02x", b);
	        sb.append(hex);
	    }
	    return sb.toString();
	}
}

** "value_columns: must be mapped to CHAR, VARCHAR, or BLOB columns" **, so it is impossible to handle JSON type fields as they are with memcached **: cry: (telnet) There is a response when I try it, but the contents are messed up) → https://dev.mysql.com/doc/refman/5.6/ja/innodb-memcached-internals.html I'm not sure if CAST is okay, but please be able to handle it without doing anything. > Oracle

(Addition) Using generated column, I generated a TEXT type column from a JSON type column and specified it in value_columns.

mysql

mysql> create table userinfo (id varchar(16), infojson json, userinfo text as (cast(infojson as char) engine=InnoDB;
mysql> insert into innodb_memcache.containers (`name`,`db_schema`,`db_table`,`key_columns`,`value_columns`,`flags`,`cas_column`,`expire_time_column`,`unique_idx_name_on_key`) VALUES ('userinfo', 'test', 'userinfo', 'id', 'userinfo',0,0,0,'PRIMARY');
mysql> UNINSTALL PLUGIN daemon_memcached;INSTALL PLUGIN daemon_memcached soname "libmemcached.so";

As a result, ** NG! ** It would be possible if the reverse (from text type to json type is generated column), but since the update query using the json function cannot be used, the taste is halved. ..