I’m currently working on a product that is very tightly bound to MySQL. It’s so tightly coupled, in fact, that unit testing it while mocking the database access is meaningless. Unfortunately, accessing a MySQL database from the build server (or any machine) is not something that we can take for granted, not to mention concurrency (one person running the unit tests in parallel to another, messing each other’s sandbox) and the network I/O to an external server.
The solution I came up with would be to use an in-process, in-memory database to mock MySQL, so I looked to H2 (good) and HyperSQL (better), but neither had the same power as MySQL had.
Browsing through the Interwebs, I stumbled into mysql-je, a hack from back in 2006 which let you run MySQL as an in-process database, but it was so unstable and unmaintained that I kept looking and came across MySQL Connector/MJX. MJX is an in-process database that, unfortunately, needs access to the filesystem to store its files (you can use the temporary files’ directory), but comes in the form of handy (not so little) jars and works well when invoked from the JVM.
We need two things from this experiment:
- To be able to isolate and access our own MJX database.
- To intercept any and all calls to MySQL and handle them instead of the usual MySQL driver.
Let’s start by adding it to SBT:
val aspectj = "org.aspectj" % "aspectjrt" % "1.5.4" % "test" val mxj = "mysql" % "mysql-connector-mxj" % "5.0.12" % "test" val mxjdb = "mysql" % "mysql-connector-mxj-db-files" % "5.0.12" % "test"
And now we reload and wait, because the mysql-connector-mxj-db-files jar is almost 140MB in size (since it contains the binaries to run MySQL on multiple platforms) and takes while to download. While waiting, let’s take a look at the code, which lives in the ScalaTest unit testing environment:
package mysqlmock
import java.lang.String
import org.scalatest._
import java.sql.{Connection, DriverManager}
import java.io.File
import util.Random
import collection.JavaConversions._
import java.util.Properties
import com.mysql.management.{MysqldResourceI, MysqldResource}
trait MySqlIntercepting extends AbstractSuite {
this: Suite =>
// Note: This is almost an exact copy from the BeforeAndAfterAll trait, just so we don't force anyone to mix it in
abstract override def run(testName: Option[String], reporter: Reporter,
stopper: Stopper, filter: Filter,
configMap: Map[String, Any],
distributor: Option[Distributor], tracker: Tracker) {
var thrownException: Option[Throwable] = None
// Force registering of mysql driver
classOf[com.mysql.jdbc.Driver]
// Kill mysql driver
val drivers = enumerationAsScalaIterator(DriverManager.getDrivers)
DriverManager.deregisterDriver(drivers.find(_.isInstanceOf[com.mysql.jdbc.Driver]).head)
// Register our new faking driver
val driver = new MockMySqlDriver()
DriverManager.registerDriver(driver)
try {
super.run(testName, reporter, stopper, filter, configMap, distributor, tracker)
}
catch {
case e: Exception => thrownException = Some(e)
}
finally {
try {
driver.shutdown()
thrownException match {
case Some(e) => throw e
case None =>
}
}
catch {
case laterException: Exception =>
thrownException match {
// If both run and shutdown throw an exception, report the test exception
case Some(earlierException) => throw earlierException
case None => throw laterException
}
}
}
}
// This is a shortcut to getting a connection
protected def createConnection(): Connection = {
DriverManager.getConnection(MockMySqlDriver.internalConnectionStringPrefix)
}
}
protected[mysqlmock] object MockMySqlDriver {
val internalConnectionStringPrefix = "jdbc:test"
}
protected[mysqlmock] class MockMySqlDriver extends com.mysql.jdbc.Driver {
private val databaseName: String = Random.alphanumeric.take(50).mkString
private val databaseDir: File = new File(new File(System.getProperty("java.io.tmpdir")), databaseName)
private val portNumber = (Random.nextInt(10049 - 10011) + 10011).toString // Unused port range
private val databaseInstance = new MysqldResource(databaseDir)
private var connectedAtLeastOnce: Boolean = false
private val username = "test"
private val password = "test"
private def initializeDatabase() {
if (!connectedAtLeastOnce) {
// Only initialize the database once
val options = Map(
(MysqldResourceI.PORT -> portNumber),
(MysqldResourceI.INITIALIZE_USER -> "true"),
(MysqldResourceI.INITIALIZE_USER_NAME -> username),
(MysqldResourceI.INITIALIZE_PASSWORD -> password)
)
databaseInstance.start("mysqld-" + databaseName, options)
if (!databaseInstance.isRunning) {
throw new RuntimeException("MySQL did not start.")
}
connectedAtLeastOnce = true
}
}
def shutdown() {
if (connectedAtLeastOnce)
databaseInstance.shutdown()
}
override def acceptsURL(url: String) =
// MySQL mocking
url.startsWith("jdbc:mysql://") ||
// Internal database
url == MockMySqlDriver.internalConnectionStringPrefix ||
// Or direct calls to mxj
super.acceptsURL(url)
override def connect(connectionString: String, props: Properties): Connection = {
if (props.size() > 0) // If we came here with properties, then it's a call from ourselves
return super.connect(connectionString, props)
props.put("createDatabaseIfNotExist", "true")
props.put("server.initialize-user", "true")
props.put("--default-character-set", "utf8")
props.put("--default-collation", "utf8_general_ci")
props.put("user", username)
props.put("password", password)
// Make sure the database is running
initializeDatabase()
// Create a connection string to use with our database
val string: String = "jdbc:mysql:mxj://localhost:" + portNumber + "/" + databaseName
super.connect(string, props)
}
}
As you can see from the code above, what we’re doing is creating a database for each test suite and throwing it away in the end. In addition to having the MySqlIntercepting trait intercept any attempt to connect to MySQL and redirect it to the temporary instance, let’s take a look at how we can mix it into our tests:
class MyTests extends Suite with BeforeAndAfter with MySqlIntercepting {
before {
val myConnection = super.createConnection()
try {
val statement = myConnection.createStatement()
try {
statement.execute("some sql here to initialize the database before each test")
} finally {
statement.close()
}
} finally {
myConnection.close()
}
}
after {
val myConnection = super.createConnection()
try {
val statement = myConnection.createStatement()
try {
statement.execute("some sql here to clean up the database after each test")
} finally {
statement.close()
}
} finally {
myConnection.close()
}
}
// tests...
}
We ended up mocking MySQL without really mocking it. Access is extremely fast (with a little bit of time for bootstrapping per suite), and we’ve stripped away all of the unknown variables of using a shared, non-dedicated database server.
Addendum: Stopping in the middle of a test (without letting the test finish), for instance while debugging a test, will leave the mysqld process running, so to kill all dangling processes, use this:
ps -ef | grep MysqldResource.pid | grep -v grep | awk '{print "kill "$2}' | bash
blog://omervk.com 