Connector/Python issues

[Nuno Mariz]

Hi folks,

I'm working at Oracle on the Connector/Python team and we saw this page https://docs.sqlalchemy.org/en/rel_1_2/dialects/mysql.html#module-sqlalchemy.dialects.mysql.mysqlconnector

We are aware of the following issues:

- Memory leak issues were addressed by bug#79887, bug#80621 and others.

- Issues using non-ascii characters within the SQL with Python 2 were fixed in recent releases.

- The "utf8mb4" charset is supported since release 8.0.12 and it's now the default.

But we are unsure about the rest. Could you please file bugs with more details?

We really want to fix these issues and we would appreciate your help.

Please use https://bugs.mysql.com/

As always we are interested in feedback and suggestions.

Thank you,

Nuno Mariz

[Mike Bayer]

On Wed, Mar 6, 2019 at 2:17 PM Nuno Mariz <nma...@gmail.com> wrote:
>
> Hi folks,
> I'm working at Oracle on the Connector/Python team and we saw this page https://docs.sqlalchemy.org/en/rel_1_2/dialects/mysql.html#module-sqlalchemy.dialects.mysql.mysqlconnector
>
> We are aware of the following issues:
>
> - Memory leak issues were addressed by bug#79887, bug#80621 and others.
> - Issues using non-ascii characters within the SQL with Python 2 were fixed in recent releases.
> - The "utf8mb4" charset is supported since release 8.0.12 and it's now the default.
>

Hi Nuno -

As far as the memory leak issue, it was reported here:
https://github.com/sqlalchemy/sqlalchemy/issues/4296 which is in June
2018, a year after the mysql-connector 2.1.6 release here:
https://pypi.org/project/mysql-connector/ so it's safe to assume the
leak still exists in the current version, as it looks like
mysql-connector has not been released for almost two years. On
that issue it seems that the test data is unfortunately not available
so someone would need to go through the script given and create some
fake data. It was a very difficult leak to track down and it does
not reproduce with any other MySQL driver. The leak may well have
to do with handling of warnings, as Python's warnings filter when it
suppresses warnings after the first display, stores them in a hash
that grows unbounded. If mysql-connector is producing warning
messages that are different based on the data given, you'll get a
memory leak

For the issue "additional random bytes-returned issues occur when
running under MySQL 8.0 only", below is a demonstration script. Note
that the second result set returns the data as bytearray() for no
apparent reason.

Most of these errors are easy for me to find since the SQLAlchemy test
suite hits them. I'll see if I can get you the "the values in
cursor.description are randomly sent as either bytes or text with no
discernible pattern," next.


from mysql import connector

conn = connector.connect(
user="scott", password="tiger", db="test", host="mysql80",
charset="utf8mb4")

cursor = conn.cursor()

cursor.execute("DROP TABLE IF EXISTS test_table")
cursor.execute("CREATE TABLE test_table (id INTEGER, data VARCHAR(20))")

cursor.execute("INSERT INTO test_table (id, data) VALUES (1, 'some data')")

cursor.execute("SELECT id, data FROM test_table")
row = cursor.fetchone()

print("Row is: %s" % (row, ))


cursor.execute("""
select table_schema, table_name, column_name
from information_schema.columns
where (table_schema, table_name, lower(column_name)) in
((%s, %s, %s))
""", ('test', 'test_table', 'id'))

row = cursor.fetchone()

print("Row is: %s " % (row, ))

> But we are unsure about the rest. Could you please file bugs with more details?
> We really want to fix these issues and we would appreciate your help.
>
> Please use https://bugs.mysql.com/
>
> As always we are interested in feedback and suggestions.
>
> Thank you,
> Nuno Mariz
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy-devel" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-dev...@googlegroups.com.
> To post to this group, send email to sqlalche...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy-devel.
> For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

Here's a simple test script that illustrates mysql-connector on
version 2.1.6 does not know how to handle utf8mb4 for executemany().
This is a very basic case that suggests mysql-connector should likely
have more test coverage for the utf8mb4 charset:

from mysql import connector

conn = connector.connect(

user="scott", password="tiger", db="test", host="localhost",

charset="utf8mb4")

cursor = conn.cursor()

cursor.execute("DROP TABLE IF EXISTS some_table")
cursor.execute("""
CREATE TABLE some_table (
id INTEGER NOT NULL AUTO_INCREMENT,
data VARCHAR(100),
PRIMARY KEY (id)
)

""")


cursor.executemany(
"INSERT INTO some_table (id, data) VALUES (%(id)s, %(data)s)",
[{'id': 1, 'data': 'data1'}, {'id': 2, 'data': 'data2'}]
)


output:

Traceback (most recent call last):
File "test2.py", line 22, in <module>
[{'id': 1, 'data': 'data1'}, {'id': 2, 'data': 'data2'}]
File "/home/classic/.venv3/lib/python3.7/site-packages/mysql/connector/cursor.py",
line 660, in executemany
stmt = self._batch_insert(operation, seq_params)
File "/home/classic/.venv3/lib/python3.7/site-packages/mysql/connector/cursor.py",
line 590, in _batch_insert
fmt = matches.group(1).encode(self._connection.charset)
LookupError: unknown encoding: utf8mb4


If you can confirm these two scripts with new bug numbers on your end
and reply here, I'll get you some more issues.

[Nuno Mariz]

Hi Mike,

Thanks for the reply. We will run the SQLAlchemy test suite and try to find these issues.

Keep in mind that https://pypi.org/project/mysql-connector/ is not maintained by us. The official is https://pypi.org/project/mysql-connector-python/

Maybe you should give another try, many things changed since June 2018 ;-)

Keep up with the awesome work on SQLAlchemy!

Nuno Mariz

[Nuno Mariz]

Mike,

Please check my last email regarding PyPI, Connector/Python 8.0 should be used instead. It contains the classic protocol used by SQLAlchemy and the new X DevAPI.

In the download page we recommend the upgrade https://dev.mysql.com/downloads/connector/python/2.1.html

`pip install mysql-connector-python` will do the trick.

Thanks,

Nuno

[Mike Bayer]

On Wed, Mar 6, 2019 at 5:05 PM Nuno Mariz <nma...@gmail.com> wrote:
>
> Mike,
> Please check my last email regarding PyPI, Connector/Python 8.0 should be used instead. It contains the classic protocol used by SQLAlchemy and the new X DevAPI.
> In the download page we recommend the upgrade https://dev.mysql.com/downloads/connector/python/2.1.html
>
> `pip install mysql-connector-python` will do the trick.

Hi Nuno -

ok wow, that's kind of an enormous thing that there's a package on
pypi which plainly states it's maintained by Oracle, and it's no
longer maintained, that means everything you see on the SQLAlchemy
website is not up to date, I'd have to start with this new driver all
over again and also update SQLAlchemy's website and everything else.
Let's see how the tests fare for that.

[Mike Bayer]

Hm OK it looks like mysql-connector-python is what we have in tox.ini
but locally i had mysql-connector. mysql-connector-python seems to
work close to normal so I will try to add it into CI and see if we can
take down those warnings assuming things are OK.

You *REALLY* need to get that other package off of pypi or have it
warn or something, and if Oracle isn't maintaining it you should get
them to remove the name "Oracle" from it, I'm sure Oracle is very good
at that sort of thing. I thought this was Oracle's official package.

[Mike Bayer]

Here's a bug, mysql-connector-python trying to utf-8 decode a BLOB on
Python 2 only:

from mysql import connector

conn = connector.connect(
user="scott",
password="tiger",
db="test",
host="localhost",

charset="utf8mb4",
)

cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS foo")
cursor.execute(
"""
CREATE TABLE foo (

id INTEGER NOT NULL AUTO_INCREMENT,

data BLOB,

PRIMARY KEY (id)
)

"""
)

cursor.execute(
"INSERT INTO foo (data) VALUES (%(data)s)",
{"data": "\x80\x02csqlalchemy.ext.mutable\n"},
)


cursor.execute(
"SELECT foo.data AS foo_data FROM foo WHERE foo.id = %(param_1)s",
{u"param_1": 1},
)

cursor.fetchall()

cursor.close()

output:

Traceback (most recent call last):

File "test2.py", line 35, in <module>
cursor.fetchall()
File "/home/classic/.venv2/lib/python2.7/site-packages/mysql/connector/cursor_cext.py",
line 490, in fetchall
rows = self._cnx.get_rows()
File "/home/classic/.venv2/lib/python2.7/site-packages/mysql/connector/connection_cext.py",
line 291, in get_rows
_eof = self.fetch_eof_columns()['eof']
File "/home/classic/.venv2/lib/python2.7/site-packages/mysql/connector/connection_cext.py",
line 352, in fetch_eof_columns
for col in fields:
File "/home/classic/.venv2/lib64/python2.7/encodings/utf_8.py", line
16, in decode
return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: 'utf8' codec can't decode byte 0x80 in position 0:
invalid start byte


I am also still getting the hangs on some tests, I have not identified
which but previously it was related to SELECT..FOR UPDATE

[Nuno Mariz]

I was able to reproduce the issue and I've opened a bug for it: 

It happens using Python 2.7 with the C extension enabled (default).

Thanks,
Nuno

[Mike Bayer]

I've taken the warnings out of the docs, should be up on the site
soon, however I still get a lot of binary-data related failures
including in Python 3, and I still need to isolate the hanging issue.

To see some of the binary related issues. Have a MySQL server
running with a database available, below I use the database named
"test" and the hostname for my URL below is "mysql80". The user that
logs in needs to have permissions including CREATE DATABASE / DROP
DATABASE:

$ git clone https://github.com/sqlalchemy/sqlalchemy
$ cd sqlalchemy
$ TOX_MYSQL="--dburi
mysql+mysqlconnector://scott:tiger@mysql80/test?charset=utf8mb4" tox
-e py37-cext-mysql -- test/sql/test_types.py --backend-only

Errors include:

File "/home/classic/dev/sqlalchemy/.tox/py37-cext-mysql/lib/python3.7/site-packages/mysql/connector/cursor_cext.py",
line 402, in close
self._cnx.handle_unread_result()
File "/home/classic/dev/sqlalchemy/.tox/py37-cext-mysql/lib/python3.7/site-packages/mysql/connector/connection_cext.py",
line 617, in handle_unread_result
raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found


File "/home/classic/dev/sqlalchemy/.tox/py37-cext-mysql/lib/python3.7/site-packages/mysql/connector/cursor_cext.py",
line 651, in _handle_resultset
self._rows = self._cnx.get_rows()[0]
File "/home/classic/dev/sqlalchemy/.tox/py37-cext-mysql/lib/python3.7/site-packages/mysql/connector/connection_cext.py",
line 276, in get_rows
row = self._cmysql.fetch_row()
SystemError: <method 'fetch_row' of '_mysql_connector.MySQL' objects>
returned a result with an error set

[Nuno Mariz]

Thanks Mike!

Now lets working on fixing these remaining issues.

I will run the SQLAlchemy tests and debug.

Nuno

[Mike Bayer]

another bug, in Python 3, binary values come back as decoded unicode
objects, they should come back as bytes (other drivers work), see
https://github.com/sqlalchemy/sqlalchemy/issues/4541 for user issue:

from mysql import connector

conn = connector.connect(
user="scott", password="tiger", db="test", host="localhost",

charset="utf8")

cursor = conn.cursor()

cursor.execute("DROP TABLE IF EXISTS some_table")
cursor.execute("""
CREATE TABLE some_table (
id INTEGER NOT NULL AUTO_INCREMENT,

data BLOB,
PRIMARY KEY (id)
)

""")


cursor.execute(

"INSERT INTO some_table (id, data) VALUES (%(id)s, %(data)s)",

{'id': 1, 'data': connector.Binary(b'test')}
)
cursor.execute("select data from some_table")

row = cursor.fetchone()
assert isinstance(row[0], bytes), type(row[0]) # fails

print(bytes(row[0])) # fails

[Nuno Mariz]

Hi Mike,

Thanks for the information, we are going to work on this bug too.

Nuno

[Mike Bayer]

feel free to link the bugs here so I can report back to the users
giving them to me.

sorry im not just going to bugs.mysql myself but this is kind of a
neat arrangement with you just taking them from here :)

[Nuno Mariz]

Hi Mike,

I'm working on these issues and I want to ask you if there's a simple way to provide a local wheel file or a virtualenv for testing SQLAlchemy without using the packages from PyPI?

Basically I want to test my patch.

I'm also having the following failures using using both mysql+mysqlconnector and mysql+pymysql. This is a known issue?

Tests EnumTest_mysql+mysqlconnector_8_0_16.test_variant_we_are_not_default and EnumTest_mysql+mysqlconnector_8_0_16.test_variant_we_are_default

AssertionError: Unexpected success for 'test_variant_we_are_not_default' (check constraints don't enforce on MySQL, MariaDB<10.2)

Nuno

[Mike Bayer]

On Thu, Mar 21, 2019 at 12:54 PM Nuno Mariz <nma...@gmail.com> wrote:
>
> Hi Mike,

> I'm working on these issues and I want to ask you if there's a simple way to provide a local wheel file or a virtualenv for testing SQLAlchemy without using the packages from PyPI?
> Basically I want to test my patch.

Hi Nuno -

The quickest way is to build the environment:

tox -e py37-cext-mysql --notest

then install your local version int he virtualenv tox created

.tox/py37/bin/pip install -e /path/to/your/dev/checkout/mysqlconnector

then run the tests without rebuilding:

tox -e py37-cext-mysql

The other way is to have tox pull from your git repo directly, we do
this on our CI server. The tox.ini file includes support for
arbitrary pip options, so we have a "master" build that runs like
this:

export TOX_PIP_OPTS="-c /usr/local/jenkins/scripts/dbapi_master_gits.txt"
tox -r -e py37-cext-mysql

"-c" means constrain installed software to certain versions contained
in the file.

where inside of the .txt file above, you put:

git+https://<path to your git repo>.git@<your branch>#egg=mysqlconnector

>
> I'm also having the following failures using using both mysql+mysqlconnector and mysql+pymysql. This is a known issue?
>
> Tests EnumTest_mysql+mysqlconnector_8_0_16.test_variant_we_are_not_default and EnumTest_mysql+mysqlconnector_8_0_16.test_variant_we_are_default
>
> AssertionError: Unexpected success for 'test_variant_we_are_not_default' (check constraints don't enforce on MySQL, MariaDB<10.2)

That means that the test running expects to fail because the backend
database does not enforce CHECK constraints. In MariaDB 10.2, they
introduced support for CHECK constraints. If recent versions of
MySQL 8.0 are also supporting CHECK contraints being enforced, then
there is a rule that needs to be updated to reflect this, which you
can see here: https://github.com/sqlalchemy/sqlalchemy/blob/master/test/requirements.py#L51
which is informed by the version comparison logic here:
https://github.com/sqlalchemy/sqlalchemy/blob/master/test/requirements.py#L1314
if there's a version string for MySQL where we need to enable that I
can help you with the syntax.