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 👍