CODEWITHSUNDEEP
mysql
Anekdotin
Anekdotin

Reputation: 1591

mysql giving deprecated warning

I have been receiving a warning(its flooded my logs) since updating mysql.It states,

 1287, "'@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead"

under mysql

mysql> show variables like "tx_isolation";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

I also have the new variable that the warning suggests..

mysql> show variables like "transaction_isolation";
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec

Im aware that the variablew is no longer in use. I want it gone but am cautious with my queries. Is the proper procedure to delete the warning variable. If so, how?

Also checking my.cnf, i do not see any mention of either variables.

Upvotes: 10

Views: 8416

Answers (4)

Kim Myhrman
Kim Myhrman

Reputation: 326

The problem is resolved in SQLAlchemy, released as a part of version 1.2.0, backported to 1.1.15 and it was rolled out 2017-11-03. By upgrading SQLAlchemy to 1.1.15 the warning disappears. 

pip install sqlalchemy>=1.1.15

Upvotes: 30

Ioannis Chrysochos
Ioannis Chrysochos

Reputation: 182

I installed sqlalchemy==1.1.15 and the problem was the same. So, I change the command in base.py of sqlalchemy as follows:

    #cursor.execute('SELECT @@tx_isolation')
    cursor.execute('SELECT @@transaction_isolation')

You must start a fresh python in order to see the change.

Upvotes: 0

Kim Myhrman
Kim Myhrman

Reputation: 326

I got the error while using pymysql library in a flask setup. Downgrading to mysql 5.6 works but maybe not the best solution.

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35583

From MySQL 5.7.20 onwards you should changeover to using transaction_isolation. In documentation it states:

Prior to MySQL 5.7.20, use tx_isolation rather than transaction_isolation.

https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html

tx_isolation

Deprecated  5.7.20
System Variable Name    tx_isolation

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_tx_isolation

transaction_isolation

Command-Line Format         --transaction-isolation=name
System Variable (>= 5.7.20) Name    transaction_isolation

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_transaction_isolation

EDIT:

Instead of relying on the old variable, start using the new variable, as in the examples provided by the documentation

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation; 
SET GLOBAL transaction_isolation='REPEATABLE-READ'; 
SET SESSION transaction_isolation='SERIALIZABLE';

You will have to search through your code to identify where the deprecated variable has been used, and substitute it with the new variable/syntax.

Upvotes: 2

Related Questions