Repository: ClearcodeHQ/pytest-mysql (language: python)

Issue:
Cannot reproduce test_mysql.py results
### What action do you want to perform
Run generic tests as per this package
```
def test_proc(mysql_proc):
    """Check first, basic server fixture factory."""
    assert mysql_proc.running()


def test_mysql(mysql):
    """Check first, basic client fixture factory."""
    cursor = mysql.cursor()
    cursor.execute(QUERY)
    mysql.commit()
    cursor.close()
```
### What are the results
ERROR is raised `MySQLdb._exceptions.ProgrammingError: (1007, "Can't create database 'test'; database exists")`

The following code in pytest_mysql/factories/client.py, line 83:
```
mysql_conn.query(
            '''CREATE DATABASE {name}
            DEFAULT CHARACTER SET {charset}
            DEFAULT COLLATE {collation}'''
            .format(
                name=mysql_db, charset=charset, collation=collation
            )
        )
```
Creates a default "test" database without an `IF EXISTS` statement. My understanding is that the `mysql_proc` fixture already creates the database via `mysqld` or in my case with MariaDB `mysql_install_db`?

Full output below:
```
myuser@mymachine:~/work/automation$ pipenv run py.test tests --mysql-user=$USER
======================================================================================= test session starts ========================================================================================
platform linux -- Python 3.6.9, pytest-6.0.2, py-1.9.0, pluggy-0.13.1
rootdir: /home/myuser/work/automation
plugins: mysql-2.0.3
collected 2 items                                                                                                                                                                                  

tests/test_db.py EE                                                                                                                                                                          [100%]

============================================================================================== ERRORS ==============================================================================================
___________________________________________________________________________________ ERROR at setup of test_mysql ___________________________________________________________________________________

request = <SubRequest 'mysql' for <Function test_mysql>>

    @pytest.fixture
    def mysql_fixture(request):
        """
        Client fixture for MySQL server.
    
        #. Get config.
        #. Try to import MySQLdb package.
        #. Connect to mysql server.
        #. Create database.
        #. Use proper database.
        #. Drop database after tests.
    
        :param FixtureRequest request: fixture request object
    
        :rtype: MySQLdb.connections.Connection
        :returns: connection to database
        """
        config = get_config(request)
        process = request.getfixturevalue(process_fixture_name)
        if not process.running():
            process.start()
    
        mysql_host = process.host
        mysql_user = 'root'
        mysql_passwd = passwd or config['passwd']
        mysql_db = dbname or config['dbname']
    
        mysql_conn = MySQLdb.connect(
            host=mysql_host,
            unix_socket=process.unixsocket.strpath,
            user=mysql_user,
            passwd=mysql_passwd,
        )
    
        mysql_conn.query(
            '''CREATE DATABASE {name}
            DEFAULT CHARACTER SET {charset}
            DEFAULT COLLATE {collation}'''
            .format(
>               name=mysql_db, charset=charset, collation=collation
            )
        )

../../.local/share/virtualenvs/automation-n5554fMg/lib/python3.6/site-packages/pytest_mysql/factories/client.py:88: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <_mysql.connection open to 'localhost' at 0x562510e96478>, query = 'CREATE DATABASE test\n            DEFAULT CHARACTER SET utf8\n            DEFAULT COLLATE utf8_general_ci'

    def query(self, query):
        # Since _mysql releases GIL while querying, we need immutable buffer.
        if isinstance(query, bytearray):
            query = bytes(query)
>       _mysql.connection.query(self, query)
E       MySQLdb._exceptions.ProgrammingError: (1007, "Can't create database 'test'; database exists")

../../.local/share/virtualenvs/automation-n5554fMg/lib/python3.6/site-packages/MySQLdb/connections.py:259: ProgrammingError
-------------------------------------------------------------------------------------- Captured stderr setup ---------------------------------------------------------------------------------------
2020-09-22 13:28:50 0 [Warning] Ignoring user change to 'myuser' because the user was set to 'mysql' earlier on the command line

_____________________________________________________________________________ ERROR at setup of test_submit_form_to_db _____________________________________________________________________________

request = <SubRequest 'mysql' for <Function test_submit_form_to_db>>

    @pytest.fixture
    def mysql_fixture(request):
        """
        Client fixture for MySQL server.
    
        #. Get config.
        #. Try to import MySQLdb package.
        #. Connect to mysql server.
        #. Create database.
        #. Use proper database.
        #. Drop database after tests.
    
        :param FixtureRequest request: fixture request object
    
        :rtype: MySQLdb.connections.Connection
        :returns: connection to database
        """
        config = get_config(request)
        process = request.getfixturevalue(process_fixture_name)
        if not process.running():
            process.start()
    
        mysql_host = process.host
        mysql_user = 'root'
        mysql_passwd = passwd or config['passwd']
        mysql_db = dbname or config['dbname']
    
        mysql_conn = MySQLdb.connect(
            host=mysql_host,
            unix_socket=process.unixsocket.strpath,
            user=mysql_user,
            passwd=mysql_passwd,
        )
    
        mysql_conn.query(
            '''CREATE DATABASE {name}
            DEFAULT CHARACTER SET {charset}
            DEFAULT COLLATE {collation}'''
            .format(
>               name=mysql_db, charset=charset, collation=collation
            )
        )

../../.local/share/virtualenvs/automation-n5554fMg/lib/python3.6/site-packages/pytest_mysql/factories/client.py:88: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <_mysql.connection open to 'localhost' at 0x562510ec3f08>, query = 'CREATE DATABASE test\n            DEFAULT CHARACTER SET utf8\n            DEFAULT COLLATE utf8_general_ci'

    def query(self, query):
        # Since _mysql releases GIL while querying, we need immutable buffer.
        if isinstance(query, bytearray):
            query = bytes(query)
>       _mysql.connection.query(self, query)
E       MySQLdb._exceptions.ProgrammingError: (1007, "Can't create database 'test'; database exists")

../../.local/share/virtualenvs/automation-n5554fMg/lib/python3.6/site-packages/MySQLdb/connections.py:259: ProgrammingError
===================================================================================== short test summary info ======================================================================================
ERROR tests/test_db.py::test_mysql - MySQLdb._exceptions.ProgrammingError: (1007, "Can't create database 'test'; database exists")
ERROR tests/test_db.py::test_submit_form_to_db - MySQLdb._exceptions.ProgrammingError: (1007, "Can't create database 'test'; database exists")
======================================================================================== 2 errors in 5.33s =========================================================================================
```
### What are the expected results
Expect test to pass


Discussion / hints:
Is there any other configuration option you're using? Any other fixtures?
I've only set the param `pipenv run py.test tests --mysql-user=$USER`
If I leave it as default 'root', there are file permission errors on the temporary directory. Everything else is vanilla.

My reasoning here is as per MariaDB documentation:
[https://mariadb.com/kb/en/mysql_install_db/#options](https://mariadb.com/kb/en/mysql_install_db/#options)
`--user=user-name`
> The login user name to use for running mysqld. Files and directories created by mysqld will be owned by this user. You must be root to use this option. By default, mysqld runs using your current login name and files and directories that it creates will be owned by you.
I do see the error about the existing test DB as well, though only when I run the tests within docker.

If I run them on my host, everything is fine…
PS: If I provide a `--mysql-dbname` I get an error that `test.*` tables I created using the fixture do not exist:

```
_CREATE_TABLE = """
CREATE TABLE `users` (
  `id`       int(11)                       NOT NULL AUTO_INCREMENT,
  `email`    varchar(255) COLLATE utf8_bin NOT NULL,
  `password` varchar(255) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`))
"""

_INSERT = """
INSERT INTO `users` (`email`, `password`) VALUES
  (%s, %s)
"""

@pytest.fixture()
def table(mysql):
    with mysql.cursor() as cursor:
        cursor.execute(_CREATE_TABLE)
        cursor.execute(_INSERT, ("webmaster@python.org", "secret"))
        mysql.commit()

def test_get_webmaster_password(table):
    assert "secret" == get_data()[1]
```

If though I configure my application to not use `test` DB but something else *and* provide that DB via `--mysql-dbname`, then it seems to work.
Hmmm... there must be some issue or missing error when you do not change the database name. 
I suppose your docker already starts with a test database, hence it does not work. You'd have to make sure it does not have it configured or as you wrote, configure other database name
I think the error could be more meaningful, pytest-mysql could rethrow it as it's own error with proper explanation 🤔 
I think this section is relevant, at least where MariaDB is concerned: [Link to MariaDB docs](https://mariadb.com/kb/en/mysql_install_db/#not-creating-the-test-database-and-anonymous-user)

> If you run `mysql_install_db` with the `--skip-test-db` option, no test database will be created

So it appears that MariaDB creates a `test` database by default.
In my case I used the `python:3.8` image as a base and then used `apt install --yes default-mysql-server`, as that was the only package I found that would provide the binaries necessary.





> In my case I used the `python:3.8` image as a base and then used `apt install --yes default-mysql-server`, as that was the only package I found that would provide the binaries necessary.


As per this [link](https://mariadb.com/kb/en/moving-from-mysql-to-mariadb-in-debian-9/#background-information):

> In Debian 9 "Stretch" the `mysql-server` package depends on a new package called `default-mysql-server`. This package in turn depends on `mariadb-server-10.1`...
> So, the main repository difference in Debian 9 "Stretch" is that when you install the mysql-server package on Stretch you will get MariaDB 10.1 instead of MySQL

I am assuming this is still valid for Debian Buster
You should be able to verify this by attaching a shell to your container and executing `mysql --version`

Fix patch:
diff --git a/CHANGES.rst b/CHANGES.rst
index 8d3eb9d0..1268a04e 100644
--- a/CHANGES.rst
+++ b/CHANGES.rst
@@ -8,6 +8,7 @@ Feature
 +++++++
 
 - `mysql_noproc` fixture to connect to already running mysql server
+- raise more meaningful error when the test database already exists
 
 
 Deprecation


Test patch:
diff --git a/src/pytest_mysql/exceptions.py b/src/pytest_mysql/exceptions.py
new file mode 100644
index 00000000..ebe9d136
--- /dev/null
+++ b/src/pytest_mysql/exceptions.py
@@ -0,0 +1,18 @@
+class PytestMySQLException(Exception):
+    """Base plguin's exceptions"""
+
+
+class MySQLUnsupported(PytestMySQLException):
+    """Exception raised when an unsupported MySQL has been encountered."""
+
+
+class VersionNotDetected(PytestMySQLException):
+    """Exception raised when exector could not detect mysqls' version."""
+
+    def __init__(self, output):
+        """Create error message."""
+        super().__init__("Could not detect version in {}".format(output))
+
+
+class DatabaseExists(PytestMySQLException):
+    """Raise this exception, when the database already exists"""
diff --git a/src/pytest_mysql/executor.py b/src/pytest_mysql/executor.py
index 4170861c..acd594c7 100644
--- a/src/pytest_mysql/executor.py
+++ b/src/pytest_mysql/executor.py
@@ -5,17 +5,7 @@
 
 from mirakuru import TCPExecutor
 
-
-class MySQLUnsupported(Exception):
-    """Exception raised when an unsupported MySQL has been encountered."""
-
-
-class VersionNotDetected(Exception):
-    """Exception raised when exector could not detect mysqls' version."""
-
-    def __init__(self, output):
-        """Create error message."""
-        super().__init__("Could not detect version in {}".format(output))
+from pytest_mysql.exceptions import MySQLUnsupported, VersionNotDetected
 
 
 class MySQLExecutor(TCPExecutor):
diff --git a/src/pytest_mysql/factories/client.py b/src/pytest_mysql/factories/client.py
index 6da156eb..0067a171 100644
--- a/src/pytest_mysql/factories/client.py
+++ b/src/pytest_mysql/factories/client.py
@@ -20,8 +20,11 @@
 
 import pytest
 import MySQLdb
+from MySQLdb import ProgrammingError
+from _pytest.fixtures import FixtureRequest
 
 from pytest_mysql.config import get_config
+from pytest_mysql.exceptions import DatabaseExists
 from pytest_mysql.executor import MySQLExecutor
 from pytest_mysql.executor_noop import NoopMySQLExecutor
 
@@ -56,7 +59,7 @@ def mysql(
     """
 
     @pytest.fixture
-    def mysql_fixture(request) -> MySQLdb.Connection:
+    def mysql_fixture(request: FixtureRequest) -> MySQLdb.Connection:
         """
         Client fixture for MySQL server.
 
@@ -67,9 +70,8 @@ def mysql_fixture(request) -> MySQLdb.Connection:
         #. Use proper database.
         #. Drop database after tests.
 
-        :param FixtureRequest request: fixture request object
+        :param request: fixture request object
 
-        :rtype: MySQLdb.connections.Connection
         :returns: connection to database
         """
         config = get_config(request)
@@ -95,11 +97,22 @@ def mysql_fixture(request) -> MySQLdb.Connection:
 
         mysql_conn: MySQLdb.Connection = MySQLdb.connect(**connection_kwargs)
 
-        mysql_conn.query(
-            f"CREATE DATABASE {mysql_db} "
-            f"DEFAULT CHARACTER SET {charset} "
-            f"DEFAULT COLLATE {collation}"
-        )
+        try:
+            mysql_conn.query(
+                f"CREATE DATABASE {mysql_db} "
+                f"DEFAULT CHARACTER SET {charset} "
+                f"DEFAULT COLLATE {collation}"
+            )
+        except ProgrammingError as e:
+            if "database exists" in str(e):
+                raise DatabaseExists(
+                    f"Database {mysql_db} already exists. There's some test "
+                    f"configuration error. Either you start your own server "
+                    f"with the database name used in tests, or you use two "
+                    f"fixtures with the same database name on the same process "
+                    f"fixture."
+                ) from e
+            raise
         mysql_conn.query("USE %s" % mysql_db)
 
         yield mysql_conn
diff --git a/tests/test_executor.py b/tests/test_executor.py
index 8e5e2c87..3c529ea5 100644
--- a/tests/test_executor.py
+++ b/tests/test_executor.py
@@ -3,7 +3,8 @@
 import pytest
 from mock import patch
 
-from pytest_mysql.executor import MySQLExecutor, MySQLUnsupported
+from pytest_mysql.executor import MySQLExecutor
+from pytest_mysql.exceptions import MySQLUnsupported
 
 
 @pytest.mark.parametrize(