Merge lp:~cjwatson/storm/restore-mysql into lp:storm

[Colin Watson]

Restore MySQL support.

We've had some requests to put this back, and it's reasonably straightforward to support. The only tricky bit was arranging for automatic test database provisioning; the only near-equivalent of postgresfixture that I could find is pytest-specific, but it's not too hard to inline the necessary setup and teardown code.

MySQL support was dropped in https://code.launchpad.net/~ack/storm/drop-mysql-support/+merge/288652, but it's clear that the people doing that work were labouring under the assumption that Storm is only used within Canonical, which is demonstrably untrue.

[Colin Watson (cjwatson)]

https://paste.ubuntu.com/p/CpVtdGRZgm/ is a diff of the three "new" (restored) files in this branch against the revision immediately before their previous removal, which may make them easier to review. As you can see, there are a few changes for Python 3 support, test directory reorganization, and to support automatic test database provisioning, but they're relatively short.

[Ioana Lasc (ilasc)]

LGTM

review: Approve

=== modified file '.bzrignore'
--- .bzrignore	2020-05-26 10:28:24 +0000
+++ .bzrignore	2021-04-06 09:31:39 +0000
@@ -3,6 +3,7 @@
 storm.egg-info
 build-stamp
 db
+db-mysql
 .db.lock
 dist
 debian/files
 
=== modified file 'MANIFEST.in'
--- MANIFEST.in	2020-05-26 10:28:24 +0000
+++ MANIFEST.in	2021-04-06 09:31:39 +0000
@@ -5,4 +5,4 @@
 include storm/docs/Makefile
 prune storm/docs/_build
 
-prune db
+prune db db-mysql
 
=== modified file 'NEWS'
--- NEWS	2020-12-07 22:38:36 +0000
+++ NEWS	2021-04-06 09:31:39 +0000
@@ -7,6 +7,7 @@
 - Add optional case_sensitive argument to Comparable.startswith,
   Comparable.endswith, and Comparable.contains_string.  This is only
   supported in the PostgreSQL backend.
+- Restore MySQL support.
 
 0.24 (2020-06-12)
 =================
 
=== modified file 'README'
--- README	2020-05-26 10:28:24 +0000
+++ README	2021-04-06 09:31:39 +0000
@@ -22,7 +22,8 @@
  * Storm is well designed (different classes have very clear
    boundaries, with small and clean public APIs).
  * Designed from day one to work both with thin relational
-   databases, such as SQLite, and big iron systems like PostgreSQL.
+   databases, such as SQLite, and big iron systems like PostgreSQL
+   and MySQL.
  * Storm is easy to debug, since its code is written with a KISS
    principle, and thus is easy to understand.
  * Designed from day one to work both at the low end, with trivial
@@ -89,6 +90,8 @@
 is below.
 
  $ dev/ubuntu-deps
+ $ echo "$PWD/** rwk," | sudo tee /etc/apparmor.d/local/usr.sbin.mysqld >/dev/null
+ $ sudo aa-enforce /usr/sbin/mysqld
  $ make develop
  $ make check
 
@@ -103,15 +106,15 @@
 The following instructions assume that you're using Ubuntu.  The same procedure
 will probably work without changes on a Debian system and with minimal changes
 on a non-Debian-based linux distribution.  In order to run the test suite, and
-exercise all supported backends, you will need to install PostgreSQL, along
-with the related Python database drivers:
+exercise all supported backends, you will need to install MySQL and
+PostgreSQL, along with the related Python database drivers:
 
  $ sudo apt-get install \
+       mysql-server \
        postgresql pgbouncer \
        build-essential
 
-These will take a few minutes to download (its a bit under 200MB all
-together).
+These will take a few minutes to download.
 
 The Python dependencies for running tests can mostly be installed with
 apt-get:
@@ -135,42 +138,20 @@
 This ensures that all dependencies are available, downloading from
 PyPI as appropriate.
 
-Setting up database users and access security
----------------------------------------------
-
-PostgreSQL needs to be setup to allow TCP/IP connections from
-localhost.  Edit /etc/postgresql/8.3/main/pg_hba.conf and make sure
-the following line is present:
-
- host all all 127.0.0.1/32 trust
-
-This will probably (with PostgresSQL 8.4) entail changing 'md5' to
-'trust'.
-
-In order to run the two-phase commit tests, you will also need to
-change the max_prepared_transactions value in postgres.conf to
-something like
-
-  max_prepared_transactions = 200
-
-Now save and close, then restart the server:
-
- $ sudo /etc/init.d/postgresql-8.4 restart
-
-Lets create our PostgreSQL user now.  As noted in the Ubuntu PostgreSQL
-documentation, the easiest thing is to create a user with the same name as your
-username.  Run the following command to create a user for yourself (if prompted
-for a password, leave it blank):
-
- $ sudo -u postgres createuser --superuser $USER
-
-Creating test databases
------------------------
-
-The test suite needs some local databases in place to exercise PostgreSQL
-functionality. Run:
-
- $ createdb storm_test
+Database setup
+--------------
+
+Most database setup is done automatically by the test suite.  However,
+Ubuntu's default MySQL packaging ships an AppArmor profile that prevents it
+from writing to a local data directory.  To allow the test suite to do this,
+you will need to grant it access, which is most easily done by adding a line
+such as this to /etc/apparmor.d/local/usr.sbin.mysqld:
+
+  /path/to/storm/** rwk,
+
+Then reload the profile:
+
+  $ sudo aa-enforce /usr/sbin/mysqld
 
 Running the tests
 -----------------
 
=== modified file 'dev/test'
--- dev/test	2019-11-21 01:57:01 +0000
+++ dev/test	2021-04-06 09:31:39 +0000
@@ -21,10 +21,16 @@
 #
 import glob
 import optparse
+import os
 import re
+import shutil
+import socket
+import subprocess
+import sys
+import time
 import unittest
-import sys
-import os
+
+from pkg_resources import parse_version
 
 
 def add_eggs_to_path():
@@ -111,6 +117,120 @@
     return wrapper
 
 
+def with_mysql(runner_func):
+    """If possible, wrap a test runner with code to set up MySQL.
+
+    Loosely based on the approach taken by pytest-mysql, although
+    implemented separately.
+    """
+    try:
+        import MySQLdb
+    except ImportError:
+        return runner_func
+
+    from six.moves.urllib.parse import (
+        urlencode,
+        urlunsplit,
+        )
+
+    def wrapper():
+        basedir = os.path.abspath("db-mysql")
+        datadir = os.path.join(basedir, "data")
+        unix_socket = os.path.join(basedir, "mysql.sock")
+        logfile = os.path.join(basedir, "mysql.log")
+        if os.path.exists(basedir):
+            shutil.rmtree(basedir)
+        os.makedirs(basedir)
+
+        mysqld_version_output = subprocess.check_output(
+            ["mysqld", "--version"], universal_newlines=True).rstrip("\n")
+        version = re.search(
+            r"Ver ([\d.]+)", mysqld_version_output, flags=re.I).group(1)
+        if ("MariaDB" not in mysqld_version_output and
+                parse_version(version) >= parse_version("5.7.6")):
+            subprocess.check_call([
+                "mysqld",
+                "--initialize-insecure",
+                "--datadir=%s" % datadir,
+                "--tmpdir=%s" % basedir,
+                "--log-error=%s" % logfile,
+                ])
+        else:
+            subprocess.check_call([
+                "mysql_install_db",
+                "--datadir=%s" % datadir,
+                "--tmpdir=%s" % basedir,
+                ])
+        with open("/dev/null", "w") as devnull:
+            server_proc = subprocess.Popen([
+                "mysqld_safe",
+                "--datadir=%s" % datadir,
+                "--pid-file=%s" % os.path.join(basedir, "mysql.pid"),
+                "--socket=%s" % unix_socket,
+                "--skip-networking",
+                "--log-error=%s" % logfile,
+                "--tmpdir=%s" % basedir,
+                "--skip-syslog",
+                # We don't care about durability of test data.  Try to
+                # persuade MySQL to agree.
+                "--innodb-doublewrite=0",
+                "--innodb-flush-log-at-trx-commit=0",
+                "--innodb-flush-method=O_DIRECT_NO_FSYNC",
+                "--skip-innodb-file-per-table",
+                "--sync-binlog=0",
+                ], stdout=devnull)
+
+        try:
+            start_time = time.time()
+            while time.time() < start_time + 60:
+                code = server_proc.poll()
+                if code is not None and code != 0:
+                    raise Exception("mysqld_base exited %d" % code)
+
+                sock = socket.socket(socket.AF_UNIX, socket.SOCK_STREAM)
+                try:
+                    sock.connect(unix_socket)
+                    break
+                except socket.error:
+                    pass  # try again
+                finally:
+                    sock.close()
+
+                time.sleep(0.1)
+
+            try:
+                connection = MySQLdb.connect(
+                    user="root", unix_socket=unix_socket)
+                try:
+                    cursor = connection.cursor()
+                    try:
+                        cursor.execute(
+                            "CREATE DATABASE storm_test CHARACTER SET utf8;")
+                    finally:
+                        cursor.close()
+                finally:
+                    connection.close()
+                uri = urlunsplit(
+                    ("mysql", "root@localhost", "/storm_test",
+                     urlencode({"unix_socket": unix_socket}), ""))
+                os.environ["STORM_MYSQL_URI"] = uri
+                os.environ["STORM_MYSQL_HOST_URI"] = uri
+                return runner_func()
+            finally:
+                subprocess.check_call([
+                    "mysqladmin",
+                    "--socket=%s" % unix_socket,
+                    "--user=root",
+                    "shutdown",
+                    ])
+        finally:
+            if server_proc.poll() is None:
+                server_proc.kill()
+            server_proc.wait()
+
+    return wrapper
+
+
 if __name__ == "__main__":
     runner = os.environ.get("STORM_TEST_RUNNER")
     if not runner:
@@ -119,6 +239,7 @@
     if not runner_func:
         sys.exit("Test runner not found: %s" % runner)
     runner_func = with_postgresfixture(runner_func)
+    runner_func = with_mysql(runner_func)
     sys.exit(runner_func())
 
 # vim:ts=4:sw=4:et
 
=== modified file 'dev/ubuntu-deps'
--- dev/ubuntu-deps	2019-06-05 10:53:34 +0000
+++ dev/ubuntu-deps	2021-04-06 09:31:39 +0000
@@ -7,11 +7,14 @@
 
 apt_get install --no-install-recommends \
     build-essential \
+    libmysqlclient-dev \
     libpq-dev \
+    mysql-server \
     pgbouncer \
     postgresql \
     python-dev \
     python-fixtures \
+    python-mysqldb \
     python-psycopg2 \
     python-setuptools \
     python-six \
 
=== modified file 'setup.py'
--- setup.py	2021-04-06 09:31:39 +0000
+++ setup.py	2021-04-06 09:31:39 +0000
@@ -25,6 +25,8 @@
 
 tests_require = [
     "fixtures >= 1.3.0",
+    "mysqlclient",
+    "mysqlclient < 2.0.0; python_version < '3'",
     "pgbouncer >= 0.0.7",
     "postgresfixture",
     "psycopg2 >= 2.3.0",
 
=== added file 'storm/databases/mysql.py'
--- storm/databases/mysql.py	1970-01-01 00:00:00 +0000
+++ storm/databases/mysql.py	2021-04-06 09:31:39 +0000
@@ -0,0 +1,246 @@
+#
+# Copyright (c) 2006, 2007 Canonical
+#
+# Written by Gustavo Niemeyer <[e-mail]>
+#
+# This file is part of Storm Object Relational Mapper.
+#
+# Storm is free software; you can redistribute it and/or modify
+# it under the terms of the GNU Lesser General Public License as
+# published by the Free Software Foundation; either version 2.1 of
+# the License, or (at your option) any later version.
+#
+# Storm is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+#
+
+from __future__ import print_function
+
+from datetime import time, timedelta
+from array import array
+import sys
+
+from storm.databases import dummy
+
+try:
+    import MySQLdb
+    import MySQLdb.converters
+except ImportError:
+    MySQLdb = dummy
+
+from storm.database import (
+    Connection,
+    ConnectionWrapper,
+    Database,
+    Result,
+    )
+from storm.exceptions import (
+    DatabaseModuleError,
+    OperationalError,
+    wrap_exceptions,
+    )
+from storm.expr import (
+    compile,
+    compile_select,
+    Insert,
+    is_safe_token,
+    Select,
+    SQLToken,
+    Undef,
+    )
+from storm.variables import Variable
+
+
+compile = compile.create_child()
+
[e-mail](Select)
+def compile_select_mysql(compile, select, state):
+    if select.offset is not Undef and select.limit is Undef:
+        select.limit = sys.maxsize
+    return compile_select(compile, select, state)
+
[e-mail](SQLToken)
+def compile_sql_token_mysql(compile, expr, state):
+    """MySQL uses ` as the escape character by default."""
+    if is_safe_token(expr) and not compile.is_reserved_word(expr):
+        return expr
+    return '`%s`' % expr.replace('`', '``')
+
+
+class MySQLResult(Result):
+
+    @staticmethod
+    def from_database(row):
+        """Convert MySQL-specific datatypes to "normal" Python types.
+
+        If there are any C{array} instances in the row, convert them
+        to strings.
+        """
+        for value in row:
+            if isinstance(value, array):
+                yield value.tostring()
+            else:
+                yield value
+
+
+class MySQLConnection(Connection):
+
+    result_factory = MySQLResult
+    param_mark = "%s"
+    compile = compile
+
+    def execute(self, statement, params=None, noresult=False):
+        if (isinstance(statement, Insert) and
+            statement.primary_variables is not Undef):
+
+            result = Connection.execute(self, statement, params)
+
+            # The lastrowid value will be set if:
+            #  - the table had an AUTO INCREMENT column, and
+            #  - the column was not set during the insert or set to 0
+            #
+            # If these conditions are met, then lastrowid will be the
+            # value of the first such column set.  We assume that it
+            # is the first undefined primary key variable.
+            if result._raw_cursor.lastrowid:
+                for variable in statement.primary_variables:
+                    if not variable.is_defined():
+                        variable.set(result._raw_cursor.lastrowid,
+                                     from_db=True)
+                        break
+            if noresult:
+                result = None
+            return result
+        return Connection.execute(self, statement, params, noresult)
+
+    def to_database(self, params):
+        for param in params:
+            if isinstance(param, Variable):
+                param = param.get(to_db=True)
+            if isinstance(param, timedelta):
+                yield str(param)
+            else:
+                yield param
+
+    def is_disconnection_error(self, exc, extra_disconnection_errors=()):
+        # http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
+        return (isinstance(exc, (OperationalError,
+                                 extra_disconnection_errors)) and
+                exc.args[0] in (2006, 2013)) # (SERVER_GONE_ERROR, SERVER_LOST)
+
+
+class MySQL(Database):
+
+    connection_factory = MySQLConnection
+    _exception_module = MySQLdb
+    _converters = None
+
+    def __init__(self, uri):
+        super(MySQL, self).__init__(uri)
+        if MySQLdb is dummy:
+            raise DatabaseModuleError("'MySQLdb' module not found")
+        self._connect_kwargs = {}
+        if uri.database is not None:
+            self._connect_kwargs["db"] = uri.database
+        if uri.host is not None:
+            self._connect_kwargs["host"] = uri.host
+        if uri.port is not None:
+            self._connect_kwargs["port"] = uri.port
+        if uri.username is not None:
+            self._connect_kwargs["user"] = uri.username
+        if uri.password is not None:
+            self._connect_kwargs["passwd"] = uri.password
+        for option in ["unix_socket"]:
+            if option in uri.options:
+                self._connect_kwargs[option] = uri.options.get(option)
+
+        if self._converters is None:
+            # MySQLdb returns a timedelta by default on TIME fields.
+            converters = MySQLdb.converters.conversions.copy()
+            converters[MySQLdb.converters.FIELD_TYPE.TIME] = _convert_time
+            self.__class__._converters = converters
+
+        self._connect_kwargs["conv"] = self._converters
+        self._connect_kwargs["use_unicode"] = True
+        self._connect_kwargs["charset"] = uri.options.get("charset", "utf8")
+
+    def _raw_connect(self):
+        raw_connection = ConnectionWrapper(
+            MySQLdb.connect(**self._connect_kwargs), self)
+
+        # Here is another sad story about bad transactional behavior.  MySQL
+        # offers a feature to automatically reconnect dropped connections.
+        # What sounds like a dream, is actually a nightmare for anyone who
+        # is dealing with transactions.  When a reconnection happens, the
+        # currently running transaction is transparently rolled back, and
+        # everything that was being done is lost, without notice.  Not only
+        # that, but the connection may be put back in AUTOCOMMIT mode, even
+        # when that's not the default MySQLdb behavior.  The MySQL developers
+        # quickly understood that this is a terrible idea, and removed the
+        # behavior in MySQL 5.0.3.  Unfortunately, Debian and Ubuntu still
+        # have a patch for the MySQLdb module which *reenables* that
+        # behavior by default even past version 5.0.3 of MySQL.
+        #
+        # Some links:
+        #   http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html
+        #   http://dev.mysql.com/doc/refman/5.0/en/mysql-reconnect.html
+        #   http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
+        #
+        # What we do here is to explore something that is a very weird
+        # side-effect, discovered by reading the code.  When we call the
+        # ping() with a False argument, the automatic reconnection is
+        # disabled in a *permanent* way for this connection.  The argument
+        # to ping() is new in 1.2.2, though.
+        if MySQLdb.version_info >= (1, 2, 2):
+            raw_connection.ping(False)
+
+        return raw_connection
+
+    def raw_connect(self):
+        with wrap_exceptions(self):
+            return self._raw_connect()
+
+
+create_from_uri = MySQL
+
+
+def _convert_time(time_str):
+    h, m, s = time_str.split(":")
+    if "." in s:
+        f = float(s)
+        s = int(f)
+        return time(int(h), int(m), s, (f-s)*1000000)
+    return time(int(h), int(m), int(s), 0)
+
+
+# --------------------------------------------------------------------
+# Reserved words, MySQL specific
+
+# The list of reserved words here are MySQL specific.  SQL92 reserved words
+# are registered in storm.expr, near the "Reserved words, from SQL1992"
+# comment.  The reserved words here were taken from:
+#
+# http://dev.mysql.com/doc/refman/5.4/en/reserved-words.html
+compile.add_reserved_words("""
+    accessible analyze asensitive before bigint binary blob call change
+    condition current_user database databases day_hour day_microsecond
+    day_minute day_second delayed deterministic distinctrow div dual each
+    elseif enclosed escaped exit explain float4 float8 force fulltext
+    high_priority hour_microsecond hour_minute hour_second if ignore index
+    infile inout int1 int2 int3 int4 int8 iterate keys kill leave limit linear
+    lines load localtime localtimestamp lock long longblob longtext loop
+    low_priority master_ssl_verify_server_cert mediumblob mediumint mediumtext
+    middleint minute_microsecond minute_second mod modifies no_write_to_binlog
+    optimize optionally out outfile purge range read_write reads regexp
+    release rename repeat replace require return rlike schemas
+    second_microsecond sensitive separator show spatial specific
+    sql_big_result sql_calc_found_rows sql_small_result sqlexception
+    sqlwarning ssl starting straight_join terminated tinyblob tinyint tinytext
+    trigger undo unlock unsigned use utc_date utc_time utc_timestamp varbinary
+    varcharacter while xor year_month zerofill
+    """.split())
 
=== modified file 'storm/docs/tutorial.rst'
--- storm/docs/tutorial.rst	2020-05-26 10:28:24 +0000
+++ storm/docs/tutorial.rst	2021-04-06 09:31:39 +0000
@@ -36,7 +36,7 @@
     >>> database = create_database("sqlite:")
     >>> store = Store(database)
 
-Two databases are supported at the moment: SQLite and PostgreSQL.
+Three databases are supported at the moment: SQLite, MySQL, and PostgreSQL.
 The parameter passed to :py:func:`~storm.database.create_database` is an
 URI, as follows:
 
@@ -45,7 +45,7 @@
     # database = create_database(
     #     "scheme://username:password@hostname:port/database_name")
 
-The ``scheme`` may be ``sqlite`` or ``postgres``.
+The ``scheme`` may be ``sqlite``, ``mysql``, or ``postgres``.
 
 Now we have to create the table that will actually hold the data
 for our class.
 
=== modified file 'storm/tests/databases/base.py'
--- storm/tests/databases/base.py	2020-02-10 15:30:23 +0000
+++ storm/tests/databases/base.py	2021-04-06 09:31:39 +0000
@@ -333,6 +333,8 @@
         that works with data in memory, in fact) becomes a dangerous thing.
 
         For PostgreSQL, isolation level must be SERIALIZABLE.
+        For MySQL, isolation level must be REPEATABLE READ (the default),
+        and the InnoDB engine must be in use.
         For SQLite, the isolation level already is SERIALIZABLE when not
         in autocommit mode.  OTOH, PySQLite is nuts regarding transactional
         behavior, and will easily offer READ COMMITTED behavior inside a
 
=== added file 'storm/tests/databases/mysql.py'
--- storm/tests/databases/mysql.py	1970-01-01 00:00:00 +0000
+++ storm/tests/databases/mysql.py	2021-04-06 09:31:39 +0000
@@ -0,0 +1,167 @@
+#
+# Copyright (c) 2006, 2007 Canonical
+#
+# Written by Gustavo Niemeyer <[e-mail]>
+#
+# This file is part of Storm Object Relational Mapper.
+#
+# Storm is free software; you can redistribute it and/or modify
+# it under the terms of the GNU Lesser General Public License as
+# published by the Free Software Foundation; either version 2.1 of
+# the License, or (at your option) any later version.
+#
+# Storm is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+#
+from __future__ import print_function
+
+import os
+
+from six.moves.urllib.parse import urlunsplit
+
+from storm.databases.mysql import MySQL
+from storm.database import create_database
+from storm.expr import Column, Insert
+from storm.uri import URI
+from storm.variables import IntVariable, UnicodeVariable
+
+from storm.tests.databases.base import (
+    DatabaseTest, DatabaseDisconnectionTest, UnsupportedDatabaseTest)
+from storm.tests.databases.proxy import ProxyTCPServer
+from storm.tests.helper import TestHelper
+
+
+def create_proxy_and_uri(uri):
+    """Create a TCP proxy to a Unix-domain database identified by `uri`."""
+    proxy = ProxyTCPServer(uri.options["unix_socket"])
+    proxy_host, proxy_port = proxy.server_address
+    proxy_uri = URI(urlunsplit(
+        ("mysql", "%s:%s" % (proxy_host, proxy_port), "/storm_test",
+         "", "")))
+    return proxy, proxy_uri
+
+
+class MySQLTest(DatabaseTest, TestHelper):
+
+    supports_microseconds = False
+
+    def is_supported(self):
+        return bool(os.environ.get("STORM_MYSQL_URI"))
+
+    def create_database(self):
+        self.database = create_database(os.environ["STORM_MYSQL_URI"])
+
+    def create_tables(self):
+        self.connection.execute("CREATE TABLE number "
+                                "(one INTEGER, two INTEGER, three INTEGER)")
+        self.connection.execute("CREATE TABLE test "
+                                "(id INT AUTO_INCREMENT PRIMARY KEY,"
+                                " title VARCHAR(50)) ENGINE=InnoDB")
+        self.connection.execute("CREATE TABLE datetime_test "
+                                "(id INT AUTO_INCREMENT PRIMARY KEY,"
+                                " dt TIMESTAMP, d DATE, t TIME, td TEXT) "
+                                "ENGINE=InnoDB")
+        self.connection.execute("CREATE TABLE bin_test "
+                                "(id INT AUTO_INCREMENT PRIMARY KEY,"
+                                " b BLOB) ENGINE=InnoDB")
+
+    def test_wb_create_database(self):
+        database = create_database("mysql://un:pw@ht:12/db?unix_socket=us")
+        self.assertTrue(isinstance(database, MySQL))
+        for key, value in [("db", "db"), ("host", "ht"), ("port", 12),
+                           ("user", "un"), ("passwd", "pw"),
+                           ("unix_socket", "us")]:
+            self.assertEqual(database._connect_kwargs.get(key), value)
+
+    def test_charset_defaults_to_utf8(self):
+        result = self.connection.execute("SELECT @@character_set_client")
+        self.assertEqual(result.get_one(), ("utf8",))
+
+    def test_charset_option(self):
+        uri = URI(os.environ["STORM_MYSQL_URI"])
+        uri.options["charset"] = "ascii"
+        database = create_database(uri)
+        connection = database.connect()
+        result = connection.execute("SELECT @@character_set_client")
+        self.assertEqual(result.get_one(), ("ascii",))
+
+    def test_get_insert_identity(self):
+        # Primary keys are filled in during execute() for MySQL
+        pass
+
+    def test_get_insert_identity_composed(self):
+        # Primary keys are filled in during execute() for MySQL
+        pass
+
+    def test_execute_insert_auto_increment_primary_key(self):
+        id_column = Column("id", "test")
+        id_variable = IntVariable()
+        title_column = Column("title", "test")
+        title_variable = UnicodeVariable(u"testing")
+
+        # This is not part of the table.  It is just used to show that
+        # only one primary key variable is set from the insert ID.
+        dummy_column = Column("dummy", "test")
+        dummy_variable = IntVariable()
+
+        insert = Insert({title_column: title_variable},
+                        primary_columns=(id_column, dummy_column),
+                        primary_variables=(id_variable, dummy_variable))
+        self.connection.execute(insert)
+        self.assertTrue(id_variable.is_defined())
+        self.assertFalse(dummy_variable.is_defined())
+
+        # The newly inserted row should have the maximum id value for
+        # the table.
+        result = self.connection.execute("SELECT MAX(id) FROM test")
+        self.assertEqual(result.get_one()[0], id_variable.get())
+
+    def test_mysql_specific_reserved_words(self):
+        reserved_words = """
+            accessible analyze asensitive before bigint binary blob call
+            change condition current_user database databases day_hour
+            day_microsecond day_minute day_second delayed deterministic
+            distinctrow div dual each elseif enclosed escaped exit explain
+            float4 float8 force fulltext high_priority hour_microsecond
+            hour_minute hour_second if ignore index infile inout int1 int2
+            int3 int4 int8 iterate keys kill leave limit linear lines load
+            localtime localtimestamp lock long longblob longtext loop
+            low_priority master_ssl_verify_server_cert mediumblob mediumint
+            mediumtext middleint minute_microsecond minute_second mod modifies
+            no_write_to_binlog optimize optionally out outfile purge range
+            read_write reads regexp release rename repeat replace require
+            return rlike schemas second_microsecond sensitive separator show
+            spatial specific sql_big_result sql_calc_found_rows
+            sql_small_result sqlexception sqlwarning ssl starting
+            straight_join terminated tinyblob tinyint tinytext trigger undo
+            unlock unsigned use utc_date utc_time utc_timestamp varbinary
+            varcharacter while xor year_month zerofill
+            """.split()
+        for word in reserved_words:
+            self.assertTrue(self.connection.compile.is_reserved_word(word),
+                            "Word missing: %s" % (word,))
+
+
+class MySQLUnsupportedTest(UnsupportedDatabaseTest, TestHelper):
+
+    dbapi_module_names = ["MySQLdb"]
+    db_module_name = "mysql"
+
+
+class MySQLDisconnectionTest(DatabaseDisconnectionTest, TestHelper):
+
+    environment_variable = "STORM_MYSQL_URI"
+    host_environment_variable = "STORM_MYSQL_HOST_URI"
+    default_port = 3306
+
+    def create_proxy(self, uri):
+        """See `DatabaseDisconnectionMixin.create_proxy`."""
+        if "unix_socket" in uri.options:
+            return create_proxy_and_uri(uri)[0]
+        else:
+            return super(MySQLDisconnectionTest, self).create_proxy(uri)
 
=== modified file 'storm/tests/store/base.py'
--- storm/tests/store/base.py	2020-03-18 16:50:12 +0000
+++ storm/tests/store/base.py	2021-04-06 09:31:39 +0000
@@ -901,6 +901,8 @@
         result2 = self.store.find(Foo, Foo.id != 10)
         self.assertEqual(foo in result1.union(result2), True)
 
+        if self.__class__.__name__.startswith("MySQL"):
+            return
         self.assertEqual(foo in result1.intersection(result2), False)
         self.assertEqual(foo in result1.intersection(result1), True)
         self.assertEqual(foo in result1.difference(result2), True)
@@ -1352,6 +1354,8 @@
         result2 = self.store.find((Foo, Bar), Bar.foo_id == Foo.id)
         self.assertEqual((foo, bar) in result1.union(result2), True)
 
+        if self.__class__.__name__.startswith("MySQL"):
+            return
         self.assertEqual((foo, bar) in result1.intersection(result2), True)
         self.assertEqual((foo, bar) in result1.difference(result2), False)
 
@@ -1468,6 +1472,8 @@
         result2 = self.store.find(Foo.title)
         self.assertEqual(u"Title 10" in result1.union(result2), True)
 
+        if self.__class__.__name__.startswith("MySQL"):
+            return
         self.assertEqual(u"Title 10" in result1.intersection(result2), True)
         self.assertEqual(u"Title 10" in result1.difference(result2), False)
 
@@ -5733,6 +5739,9 @@
         self.assertEqual(result3.avg(Foo.id), 10)
 
     def test_result_difference(self):
+        if self.__class__.__name__.startswith("MySQL"):
+            self.skipTest("Skipping ResultSet.difference tests on MySQL")
+
         result1 = self.store.find(Foo)
         result2 = self.store.find(Foo, id=20)
         result3 = result1.difference(result2)
@@ -5750,6 +5759,9 @@
                          ])
 
     def test_result_difference_with_empty(self):
+        if self.__class__.__name__.startswith("MySQL"):
+            self.skipTest("Skipping ResultSet.difference tests on MySQL")
+
         result1 = self.store.find(Foo, id=30)
         result2 = EmptyResultSet()
 
@@ -5760,11 +5772,17 @@
                          ])
 
     def test_result_difference_incompatible(self):
+        if self.__class__.__name__.startswith("MySQL"):
+            self.skipTest("Skipping ResultSet.difference tests on MySQL")
+
         result1 = self.store.find(Foo, id=10)
         result2 = self.store.find(Bar, id=100)
         self.assertRaises(FeatureError, result1.difference, result2)
 
     def test_result_difference_count(self):
+        if self.__class__.__name__.startswith("MySQL"):
+            self.skipTest("Skipping ResultSet.difference tests on MySQL")
+
         result1 = self.store.find(Foo)
         result2 = self.store.find(Foo, id=20)
 
@@ -5782,6 +5800,9 @@
         self.assertEqual(result2.count(), 3)
 
     def test_result_intersection(self):
+        if self.__class__.__name__.startswith("MySQL"):
+            self.skipTest("Skipping ResultSet.intersection tests on MySQL")
+
         result1 = self.store.find(Foo)
         result2 = self.store.find(Foo, Foo.id.is_in((10, 30)))
         result3 = result1.intersection(result2)
@@ -5799,6 +5820,9 @@
                          ])
 
     def test_result_intersection_with_empty(self):
+        if self.__class__.__name__.startswith("MySQL"):
+            self.skipTest("Skipping ResultSet.intersection tests on MySQL")
+
         result1 = self.store.find(Foo, id=30)
         result2 = EmptyResultSet()
         result3 = result1.intersection(result2)
@@ -5806,11 +5830,17 @@
         self.assertEqual(len(list(result3)), 0)
 
     def test_result_intersection_incompatible(self):
+        if self.__class__.__name__.startswith("MySQL"):
+            self.skipTest("Skipping ResultSet.intersection tests on MySQL")
+
         result1 = self.store.find(Foo, id=10)
         result2 = self.store.find(Bar, id=100)
         self.assertRaises(FeatureError, result1.intersection, result2)
 
     def test_result_intersection_count(self):
+        if self.__class__.__name__.startswith("MySQL"):
+            self.skipTest("Skipping ResultSet.intersection tests on MySQL")
+
         result1 = self.store.find(Foo, Foo.id.is_in((10, 20)))
         result2 = self.store.find(Foo, Foo.id.is_in((10, 30)))
         result3 = result1.intersection(result2)
 
=== added file 'storm/tests/store/mysql.py'
--- storm/tests/store/mysql.py	1970-01-01 00:00:00 +0000
+++ storm/tests/store/mysql.py	2021-04-06 09:31:39 +0000
@@ -0,0 +1,108 @@
+#
+# Copyright (c) 2006, 2007 Canonical
+#
+# Written by Gustavo Niemeyer <[e-mail]>
+#
+# This file is part of Storm Object Relational Mapper.
+#
+# Storm is free software; you can redistribute it and/or modify
+# it under the terms of the GNU Lesser General Public License as
+# published by the Free Software Foundation; either version 2.1 of
+# the License, or (at your option) any later version.
+#
+# Storm is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+#
+from __future__ import print_function
+
+import os
+
+from storm.database import create_database
+
+from storm.tests.store.base import StoreTest, EmptyResultSetTest
+from storm.tests.helper import TestHelper
+
+
+class MySQLStoreTest(TestHelper, StoreTest):
+
+    def setUp(self):
+        TestHelper.setUp(self)
+        StoreTest.setUp(self)
+
+    def tearDown(self):
+        TestHelper.tearDown(self)
+        StoreTest.tearDown(self)
+
+    def is_supported(self):
+        return bool(os.environ.get("STORM_MYSQL_URI"))
+
+    def create_database(self):
+        self.database = create_database(os.environ["STORM_MYSQL_URI"])
+
+    def create_tables(self):
+        connection = self.connection
+        connection.execute("CREATE TABLE foo "
+                           "(id INT PRIMARY KEY AUTO_INCREMENT,"
+                           " title VARCHAR(50) DEFAULT 'Default Title') "
+                           "ENGINE=InnoDB")
+        connection.execute("CREATE TABLE bar "
+                           "(id INT PRIMARY KEY AUTO_INCREMENT,"
+                           " foo_id INTEGER, title VARCHAR(50)) "
+                           "ENGINE=InnoDB")
+        connection.execute("CREATE TABLE bin "
+                           "(id INT PRIMARY KEY AUTO_INCREMENT,"
+                           " bin BLOB, foo_id INTEGER) "
+                           "ENGINE=InnoDB")
+        connection.execute("CREATE TABLE link "
+                           "(foo_id INTEGER, bar_id INTEGER,"
+                           " PRIMARY KEY (foo_id, bar_id)) "
+                           "ENGINE=InnoDB")
+        connection.execute("CREATE TABLE money "
+                           "(id INT PRIMARY KEY AUTO_INCREMENT,"
+                           " value NUMERIC(6,4)) "
+                           "ENGINE=InnoDB")
+        connection.execute("CREATE TABLE selfref "
+                           "(id INT PRIMARY KEY AUTO_INCREMENT,"
+                           " title VARCHAR(50),"
+                           " selfref_id INTEGER,"
+                           " INDEX (selfref_id),"
+                           " FOREIGN KEY (selfref_id) REFERENCES selfref(id)) "
+                           "ENGINE=InnoDB")
+        connection.execute("CREATE TABLE foovalue "
+                           "(id INT PRIMARY KEY AUTO_INCREMENT,"
+                           " foo_id INTEGER,"
+                           " value1 INTEGER, value2 INTEGER) "
+                           "ENGINE=InnoDB")
+        connection.execute("CREATE TABLE unique_id "
+                           "(id VARCHAR(36) PRIMARY KEY) "
+                           "ENGINE=InnoDB")
+        connection.commit()
+
+
+class MySQLEmptyResultSetTest(TestHelper, EmptyResultSetTest):
+
+    def setUp(self):
+        TestHelper.setUp(self)
+        EmptyResultSetTest.setUp(self)
+
+    def tearDown(self):
+        TestHelper.tearDown(self)
+        EmptyResultSetTest.tearDown(self)
+
+    def is_supported(self):
+        return bool(os.environ.get("STORM_MYSQL_URI"))
+
+    def create_database(self):
+        self.database = create_database(os.environ["STORM_MYSQL_URI"])
+
+    def create_tables(self):
+        self.connection.execute("CREATE TABLE foo "
+                                "(id INT PRIMARY KEY AUTO_INCREMENT,"
+                                " title VARCHAR(50) DEFAULT 'Default Title') "
+                                "ENGINE=InnoDB")
+        self.connection.commit()