MySQL Schema on 5.0.67

[jacote]

Hi,
i use MySQL Server 5.0.67 on ubuntu. When i create springbatch schema i got:

ERROR 1071 (42000) at line 3: Specified key was too long; max key length is 767 bytes

any ideas ?

[jacote]

At the momen i removed only the constraint on BATCH_JOB_INSTANCE:

constraint JOB_INST_UN unique (JOB_NAME, JOB_KEY)

Seems a bug on mysql.

If anyone have other ideas i appreciate.
Thanks

[Dave Syer]

You can remove the constraint as a workaround. I'd be interested to see how to make it work on MySQL though. Can someone raise a JIRA if they know the answer...?

[jacote]

Seems a very long term bug (since 2004) i think the only way to have it working on mysql is to change constraint key to a lower value.
I've already tried to use latin1 encoding instead of UTF-8 (that use 3 bytes for a char) without success.
I don't already know spring-batch so deep to understand why the BATCH_JOB_INSTANCE should have a JOB_KEY of 2500 indexed.

[jacote]

Ok, now i understand JOB_KEY need. But why store all the parameters and not a md5 hash of the same string that will use at maximum a varchar of 32?

[Dave Syer]

Excellent idea. http://jira.springframework.org/browse/BATCH-1146. To do that we have to pick a standard encoding from the String to bytes[]. I was going to go with UTF-8. Anyone know of a platform where that wouldn't be available?

[jacote]

I found this code that use java.security.MessageDigest to perform MD5

import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
 
public class MD5 {
 
	private static String convertToHex(byte[] data) {
        StringBuffer buf = new StringBuffer();
        for (int i = 0; i < data.length; i++) {
        	int halfbyte = (data[i] >>> 4) & 0x0F;
        	int two_halfs = 0;
        	do {
	        	if ((0 <= halfbyte) && (halfbyte <= 9))
	                buf.append((char) ('0' + halfbyte));
	            else
	            	buf.append((char) ('a' + (halfbyte - 10)));
	        	halfbyte = data[i] & 0x0F;
        	} while(two_halfs++ < 1);
        }
        return buf.toString();
    }
 
	public static String MD5(String text) 
	throws NoSuchAlgorithmException, UnsupportedEncodingException  {
		MessageDigest md;
		md = MessageDigest.getInstance("MD5");
		byte[] md5hash = new byte[32];
		md.update(text.getBytes("iso-8859-1"), 0, text.length());
		md5hash = md.digest();
		return convertToHex(md5hash);
	}
}

Bye and thanks for the ISSUE

[jacote]

Hi,
for convenience i made a little patch with the modified code (seem pass all related tests)
bye

[Dave Syer]

Thanks for the patch (but I already implemented it in two lines using MessageDigest and BigInteger with String.format). I'm just waiting for feedback on the encoding.