org.jsimpledb.kv.mysql.MySQLKVDatabase Maven / Gradle / Ivy
Show all versions of jsimpledb-kv-mysql Show documentation
/*
* Copyright (C) 2015 Archie L. Cobbs. All rights reserved.
*/
package org.jsimpledb.kv.mysql;
import com.mysql.jdbc.MysqlErrorNumbers;
import com.mysql.jdbc.exceptions.MySQLTimeoutException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import org.jsimpledb.kv.KVTransactionException;
import org.jsimpledb.kv.RetryTransactionException;
import org.jsimpledb.kv.sql.SQLKVDatabase;
import org.jsimpledb.kv.sql.SQLKVTransaction;
/**
* MySQL variant of {@link SQLKVDatabase}.
*
*
* Automatically creates the key/value table on startup if it doesn't already exist.
*/
public class MySQLKVDatabase extends SQLKVDatabase {
public static final int INNODB_NORMAL_INDEX_SIZE = 767;
public static final int INNODB_LARGE_INDEX_SIZE = 3072;
private static final int DEFAULT_LOCK_TIMEOUT = 10; // 10 seconds
private boolean innodbLargePrefix;
/**
* Configure the use of InnoDB large prefixes.
*
*
* MySQL InnoDB indexes are normally limited to {@value #INNODB_NORMAL_INDEX_SIZE} bytes, but you can
* go up to {@value #INNODB_LARGE_INDEX_SIZE} bytes if you set these parameters in {@code /etc/my.cnf}:
*
* [mysqld]
* innodb_large_prefix = true
* innodb_file_per_table = true
* innodb_file_format = barracuda
*
*
*
* Setting this property to true increases the maximum key length from {@value #INNODB_NORMAL_INDEX_SIZE}
* to {@value #INNODB_LARGE_INDEX_SIZE} bytes and adds {@code ROW_FORMAT=DYNAMIC} to the table creation
* statement in {@link #initializeDatabaseIfNecessary initializeDatabaseIfNecessary()}. This property has
* no effect if the table already exists at startup.
*
*
* Default is false.
*
* @param innodbLargePrefix true for InnoDB large prefixes
* @see Limits on InnoDB Tables
*/
public void setInnodbLargePrefix(boolean innodbLargePrefix) {
this.innodbLargePrefix = innodbLargePrefix;
}
@Override
protected void initializeDatabaseIfNecessary(Connection connection) throws SQLException {
final int indexSize = this.innodbLargePrefix ? INNODB_LARGE_INDEX_SIZE : INNODB_NORMAL_INDEX_SIZE;
final String rowFormat = this.innodbLargePrefix ? " ROW_FORMAT=DYNAMIC" : "";
final String sql = "CREATE TABLE IF NOT EXISTS " + this.quote(this.getTableName()) + " (\n"
+ " " + this.quote(this.getKeyColumnName()) + " VARBINARY(" + indexSize + ") NOT NULL,\n"
+ " " + this.quote(this.getValueColumnName()) + " LONGBLOB NOT NULL,\n"
+ " PRIMARY KEY(" + this.quote(this.getKeyColumnName()) + ")\n"
+ ") ENGINE=InnoDB default charset=utf8 collate=utf8_bin" + rowFormat;
try (final Statement statement = connection.createStatement()) {
this.log.debug("auto-creating table `" + this.getTableName() + "' if not already existing:\n{}", sql);
statement.execute(sql);
}
}
@Override
protected void configureConnection(Connection connection) throws SQLException {
try (final Statement statement = connection.createStatement()) {
statement.execute("SET innodb_lock_wait_timeout = " + DEFAULT_LOCK_TIMEOUT);
statement.execute("SET SESSION sql_mode = 'TRADITIONAL'"); // force error if key or value is too long
}
}
@Override
protected SQLKVTransaction createSQLKVTransaction(Connection connection) throws SQLException {
return new MySQLKVTransaction(this, connection);
}
/**
* Encloses the given {@code name} in backticks.
*/
@Override
public String quote(String name) {
return "`" + name + "`";
}
/**
* Appends {@code LIMIT 1} to the statement.
*/
@Override
public String limitSingleRow(String sql) {
return sql + " LIMIT 1";
}
@Override
public KVTransactionException wrapException(SQLKVTransaction tx, SQLException e) {
switch (e.getErrorCode()) {
case MysqlErrorNumbers.ER_LOCK_WAIT_TIMEOUT:
return new RetryTransactionException(tx, e);
case MysqlErrorNumbers.ER_LOCK_DEADLOCK:
return new RetryTransactionException(tx, e);
default:
if (e instanceof MySQLTimeoutException)
return new RetryTransactionException(tx, e);
return super.wrapException(tx, e);
}
}
}