PooledDB setsession argument does not work on new connection.

[Hermit-xx]

Create a pooledDB, get a connection , and set setsession argument to autocommit = 1, its ok.

BUT when the connection is Not Available, pooledDB try to get new connection, the PooledDB argument setsession is not working, it will be change to autocommit 1 to 0.

Here an example code.

from DBUtils.PooledDB import PooledDB
import pymysql
pool = PooledDB(creator=pymysql,
                   maxconnections=4,  
                   mincached=2, 
                   maxcached=0, 
                   blocking=True,  
                   maxusage=None,  
                   setsession=['SET AUTOCOMMIT = 1'],    # Pay attention to this
                   host='127.0.0.1',
                   port=3306,
                   user='root',
                   password='pwd',
                   database='alerter',
                   charset='utf8'
                   )

insert_sql = "INSERT INTO `alerter`.`al_play_log` (`src_type`, `src_ip`, `led_style`, `led_color`, `led_flag`, `text`, `play_time`, `tone`) " \
             "VALUES (4, '192.168.0.210', 15, '[\"#00FFFF\"]', '[0.05, 1]', 'content', 0, NULL)"

conn = pool.connection()
cursor = conn.cursor()
ret = cursor.execute(insert_sql)
conn.close()

print('new connection create,check log, then use mysql kill command to kill current connect.')
input()
conn = pool.connection()
cursor = conn.cursor()
ret = cursor.execute(insert_sql)
conn.close()

Execute the code according to the following steps

1. run the code, Pool init and insert sql.
2. The code is paused, at this point, goto mysql use show processlist cmd get current thread id, and use kill cmd to kill it.
3. then return to code, Press any key to continue code execution.
4. then check mysql general log.

It can be found that autocommit does not take effect on new connecton.

This is the log of my database

2022-05-16T03:13:48.758866Z   207 Connect   root@localhost on alerter using TCP/IP
2022-05-16T03:13:48.760342Z   207 Query SET AUTOCOMMIT = 0
2022-05-16T03:13:48.761637Z   207 Query SET AUTOCOMMIT = 1
2022-05-16T03:13:48.766279Z   208 Connect   root@localhost on alerter using TCP/IP
2022-05-16T03:13:48.767477Z   208 Query SET AUTOCOMMIT = 0
2022-05-16T03:13:48.768686Z   208 Query SET AUTOCOMMIT = 1
2022-05-16T03:13:48.769913Z   208 Query ROLLBACK
2022-05-16T03:13:48.770811Z   207 Query ROLLBACK
2022-05-16T03:13:48.772879Z   208 Query INSERT INTO `alerter`.`al_play_log` (`src_type`, `src_ip`, `led_style`, `led_color`, `led_flag`, `text`, `play_time`, `tone`) VALUES (4, '192.168.0.210', 15, '["#00FFFF"]', '[0.05, 1]', 'content', 0, NULL)
2022-05-16T03:13:48.783426Z   208 Query ROLLBACK
2022-05-16T03:13:52.059120Z   192 Query show processlist
2022-05-16T03:13:59.223979Z   192 Query kill 207
2022-05-16T03:14:02.462124Z   192 Query kill 208
2022-05-16T03:14:13.903155Z   209 Connect   root@localhost on alerter using TCP/IP
***2022-05-16T03:14:13.904506Z    209 Query SET AUTOCOMMIT = 0***
2022-05-16T03:14:13.906474Z   209 Query INSERT INTO `alerter`.`al_play_log` (`src_type`, `src_ip`, `led_style`, `led_color`, `led_flag`, `text`, `play_time`, `tone`) VALUES (4, '192.168.0.210', 15, '["#00FFFF"]', '[0.05, 1]', 'content', 0, NULL)
2022-05-16T03:14:13.908776Z   209 Query ROLLBACK
2022-05-16T03:14:13.956031Z   209 Quit  

[Cito]

Hi @Hermit-xx - why did you close this again? Is this not relevant any more? Did you solve it (how)?

[Hermit-xx]

Hello @Cito , After commit this issue, I checked the package version, I found out that I was using version 1.3 when this problem appeared. Then I try to update to the latest version and that solved it ...

[Cito]

Thank you, glad to hear 👍