0

I checked different post like: Is there a way to use 'pool_reset_connection' from mysql-connector-python with MariaDB 10.4.7?

But I can't solve my isue, I have a program write in python 2.7; now I'm moving everything to python 3 but I got problems with mysql.connector.

In this moment I'm using python 3.8.2 and mysql-connector-python==8.0.19 but I try with different version of python 3, but I was getting always the same error on mysql.connector when I try to close the connection.

Traceback (most recent call last):
  File "C:\Python38\lib\site-packages\mysql\connector\connection.py", line 819, in reset_session
    self.cmd_reset_connection()
  File "C:\Python38\lib\site-packages\mysql\connector\connection.py", line 1166, in cmd_reset_connection
    raise errors.NotSupportedError("MySQL version 5.7.2 and "
mysql.connector.errors.NotSupportedError: MySQL version 5.7.2 and earlier does not support COM_RESET_CONNECTION.

...
...

mysql.connector.errors.OperationalError: 1047 (08S01): Unknown command

I'm not sure how to solve it, I try in different way increasing the pool and having different pool or just shutdown the connection but in a big project after I got pool exhaust so I really need close the connection but I can't solve this issue; can someone help me pls?

I try even to change the mysql-connector library as explained in the other stackoverflow ticket but every time I change it I got other problems.

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
Carlo 1585
  • 1,455
  • 1
  • 22
  • 40
  • I try to downgrade but with no success, I'll try to upgrade my DB so but so far I try different mysql-connector-python with no success, the only one that works for me it's in python2.7. Tks for the tips – Carlo 1585 Apr 12 '20 at 15:23
  • Looking at the other question, it's about mariadb rather than mysql. Are you using mariadb? – snakecharmerb Apr 12 '20 at 15:35
  • yes, I just upgrade it to latest version MariaDB10.4 and I'm trying again but before I had 10.1 not than old... anyway without touch the DB everything was working with python2.7 :S I was using mysql-connector 2.1.6, in python 3 I'm trying to install the same version of the connector but doesn't allow me even if documentation say that python 3.4 has mysql-conmnector 2.1.6 – Carlo 1585 Apr 12 '20 at 15:38
  • @snakecharmerb I just upgrade MariaDB to 10.4, python 8.2 and latest version of mysql-connector-python8.0.19 but I still have the same issue :S – Carlo 1585 Apr 12 '20 at 15:42
  • 1
    @snakecharmerb I got it working finally, after 3 days ahahaha, instead than fix I just try a different connector called mariadb that is not imported from mysql.connector: https://pypi.org/project/mariadb/ TKS so much for the help and tips ;) – Carlo 1585 Apr 12 '20 at 15:49

1 Answers1

0

It's a problem how MySQL Connector/Python detects the server version:

Since replication doesn't work with a 2-digit major version number, MariaDB uses a so called RPL hack which was introduced with version 10.0 and sends the following version number to the client 5.5.5-10.4.13-MariaDB. See also What does the first part of the MariaDB version string mean.

Since MariaDB Connector/Python is not aware of it, it assumes that the server version number is 5.5.5 and will raise a NotSupportedError exception when checking >= 5.7.3.

You can either patch MySQL Connector/Python (fixing server_version in do_handshake()) or you can try MariaDB Connector/Python. It's currently beta, but GA is expected still this month (pip3 install mariadb)

Georg Richter
  • 5,970
  • 2
  • 9
  • 15
  • I tryd to fix it but it was always giving me a different issue so at the end I found a mariadb connector that is not part of mysql-connector: pypi.org/project/mariadb – Carlo 1585 Apr 12 '20 at 17:11
  • I know it - it was written by me :-) – Georg Richter Apr 12 '20 at 17:25
  • I'm trying to fix the server version because with mariadb connector all the query return list or tuple and not dict, so I should fix many part of my code... I got the _do_handshake() function and when I print self._server_version it give me all the time (5,5,5) but I'm not finding a way to change it without that it fail something else... how should I change it? for what? – Carlo 1585 Apr 12 '20 at 18:47
  • Sorry to annoy you, just fixed it and it worked in both way, other mariadb connector or fixing the mysql-connector ) tks so much again – Carlo 1585 Apr 12 '20 at 18:52
  • I assume you mean cursor(dictionary=True) ? Yes, it's not supported yet, but we will be implemented in next version (beta) – Georg Richter Apr 12 '20 at 19:37
  • yes, and tks to let me know so I don't implement it by myself ;) – Carlo 1585 Apr 13 '20 at 13:54
  • But I really wonder why someone wants to store a rrow in a dictionary. Dictionary contains key value pairs with a unique key, while resultset metada contain identifiers, which are not unique. e.g. `SELECT 1,2,1` or `SELECT t1.a, t2.a FROM t1 JOIN t2 WHERE t1.id=t2.id` – Georg Richter Apr 13 '20 at 15:44
  • when I do my select I prefer to have as wesult something like [{'id':1, 't': 't1' .....},{'id': 2, 't': 't2', ....}] instead than [(1, t1, ....),(2, t2, ....)] so I can get the element by their key and if some time the select query is different, I still get the correct result. It could happen that some time some query is "select t1, t2" and another time "select t2, t1" using the key/value I'll always get the correct value – Carlo 1585 Apr 13 '20 at 17:37