8000 Add constants and tests related to query timeouts (#1033) · sanchezg/PyMySQL@ea79b32 · GitHub
[go: up one dir, main page]
Skip to content

Commit ea79b32

Browse files< 10000 /a>
Nothing4YouNothing4You
authored
Add constants and tests related to query timeouts (PyMySQL#1033)
1 parent 01ddf9d commit ea79b32

File tree

4 files changed

+168
-11
lines changed

4 files changed

+168
-11
lines changed

‎pymysql/constants/ER.py

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -470,5 +470,8 @@
470470
WRONG_STRING_LENGTH = 1468
471471
ERROR_LAST = 1468
472472

473+
# MariaDB only
474+
STATEMENT_TIMEOUT = 1969
475+
QUERY_TIMEOUT = 3024
473476
# https://github.com/PyMySQL/PyMySQL/issues/607
474477
CONSTRAINT_FAILED = 4025

‎pymysql/tests/base.py

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -49,6 +49,14 @@ def mysql_server_is(self, conn, version_tuple):
4949
)
5050
return server_version_tuple >= version_tuple
5151

52+
def get_mysql_vendor(self, conn):
53+
server_version = conn.get_server_info()
54+
55+
if "MariaDB" in server_version:
56+
return "mariadb"
57+
58+
return "mysql"
59+
5260
_connections = None
5361

5462
@property

‎pymysql/tests/test_SSCursor.py

Lines changed: 90 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,15 +1,8 @@
1-
import sys
1+
import pytest
22

3-
try:
4-
from pymysql.tests import base
5-
import pymysql.cursors
6-
from pymysql.constants import CLIENT, ER
7-
except Exception:
8-
# For local testing from top-level directory, without installing
9-
sys.path.append("../pymysql")
10-
from pymysql.tests import base
11-
import pymysql.cursors
12-
from pymysql.constants import CLIENT, ER
3+
from pymysql.tests import base
4+
import pymysql.cursors
5+
from pymysql.constants import CLIENT, ER
136

147

158
class TestSSCursor(base.PyMySQLTestCase):
@@ -122,6 +115,92 @@ def test_SSCursor(self):
122115
cursor.execute("DROP TABLE IF EXISTS tz_data")
123116
cursor.close()
124117

118+
def test_execution_time_limit(self):
119+
# this method is similarly implemented in test_cursor
120+
121+
conn = self.connect()
122+
123+
# table creation and filling is SSCursor only as it's not provided by self.setUp()
124+
self.safe_create_table(
125+
conn,
126+
"test",
127+
"create table test (data varchar(10))",
128+
)
129+
with conn.cursor() as cur:
130+
cur.execute(
131+
"insert into test (data) values "
132+
"('row1'), ('row2'), ('row3'), ('row4'), ('row5')"
133+
)
134+
conn.commit()
135+
136+
db_type = self.get_mysql_vendor(conn)
137+
138+
with conn.cursor(pymysql.cursors.SSCursor) as cur:
139+
# MySQL MAX_EXECUTION_TIME takes ms
140+
# MariaDB max_statement_time takes seconds as int/float, introduced in 10.1
141+
142+
# this will sleep 0.01 seconds per row
143+
if db_type == "mysql":
144+
sql = (
145+
"SELECT /*+ MAX_EXECUTION_TIME(2000) */ data, sleep(0.01) FROM test"
146+
)
147+
else:
148+
sql = "SET STATEMENT max_statement_time=2 FOR SELECT data, sleep(0.01) FROM test"
149+
150+
cur.execute(sql)
151+
# unlike Cursor, SSCursor returns a list of tuples here
152+
self.assertEqual(
153+
cur.fetchall(),
154+
[
155+
("row1", 0),
156+
("row2", 0),
157+
("row3", 0),
158+
("row4", 0),
159+
("row5", 0),
160+
],
161+
)
162+
163+
if db_type == "mysql":
164+
sql = (
165+
"SELECT /*+ MAX_EXECUTION_TIME(2000) */ data, sleep(0.01) FROM test"
166+
)
167+
else:
168+
sql = "SET STATEMENT max_statement_time=2 FOR SELECT data, sleep(0.01) FROM test"
169+
cur.execute(sql)
170+
self.assertEqual(cur.fetchone(), ("row1", 0))
171+
172+
# this discards the previous unfinished query and raises an
173+
# incomplete unbuffered query warning
174+
with pytest.warns(UserWarning):
175+
cur.execute("SELECT 1")
176+
self.assertEqual(cur.fetchone(), (1,))
177+
178+
# SSCursor will not read the EOF packet until we try to read
179+
# another row. Skipping this will raise an incomplete unbuffered
180+
# query warning in the next cur.execute().
181+
self.assertEqual(cur.fetchone(), None)
182+
183+
if db_type == "mysql":
184+
sql = "SELECT /*+ MAX_EXECUTION_TIME(1) */ data, sleep(1) FROM test"
185+
else:
186+
sql = "SET STATEMENT max_statement_time=0.001 FOR SELECT data, sleep(1) FROM test"
187+
with pytest.raises(pymysql.err.OperationalError) as cm:
188+
# in an unbuffered cursor the OperationalError may not show up
189+
# until fetching the entire result
190+
cur.execute(sql)
191+
cur.fetchall()
192+
193+
if db_type == "mysql":
194+
# this constant was only introduced in MySQL 5.7, not sure
195+
# what was returned before, may have been ER_QUERY_INTERRUPTED
196+
self.assertEqual(cm.value.args[0], ER.QUERY_TIMEOUT)
197+
else:
198+
self.assertEqual(cm.value.args[0], ER.STATEMENT_TIMEOUT)
199+
200+
# connection should still be fine at this point
201+
cur.execute("SELECT 1")
202+
self.assertEqual(cur.fetchone(), (1,))
203+
125204
def test_warnings(self):
126205
con = self.connect()
127206
cur = con.cursor(pymysql.cursors.SSCursor)

‎pymysql/tests/test_cursor.py

Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,8 @@
22
from pymysql.tests import base
33
import pymysql.cursors
44

5+
import pytest
6+
57

68
class CursorTest(base.PyMySQLTestCase):
79
def setUp(self):
@@ -18,6 +20,7 @@ def setUp(self):
1820
"insert into test (data) values "
1921
"('row1'), ('row2'), ('row3'), ('row4'), ('row5')"
2022
)
23+
conn.commit()
2124
cursor.close()
2225
self.test_connection = pymysql.connect(**self.databases[0])
2326
self.addCleanup(self.test_connection.close)
@@ -129,6 +132,70 @@ def test_executemany(self):
129132
finally:
130133
cursor.execute("DROP TABLE IF EXISTS percent_test")
131134

135+
def test_execution_time_limit(self):
136+
# this method is similarly implemented in test_SScursor
137+
138+
conn = self.test_connection
139+
db_type = self.get_mysql_vendor(conn)
140+
141+
with conn.cursor(pymysql.cursors.Cursor) as cur:
142+
# MySQL MAX_EXECUTION_TIME takes ms
143+
# MariaDB max_statement_time takes seconds as int/float, introduced in 10.1
144+
145+
# this will sleep 0.01 seconds per row
146+
if db_type == "mysql":
147+
sql = (
148+
"SELECT /*+ MAX_EXECUTION_TIME(2000) */ data, sleep(0.01) FROM test"
149+
)
150+
else:
151+
sql = "SET STATEMENT max_statement_time=2 FOR SELECT data, sleep(0.01) FROM test"
152+
153+
cur.execute(sql)
154+
# unlike SSCursor, Cursor returns a tuple of tuples here
155+
self.assertEqual(
156+
cur.fetchall(),
157+
(
158+
("row1", 0),
159+
("row2", 0),
160+
("row3", 0),
161+
("row4", 0),
162+
("row5", 0),
163+
),
164+
)
165+
166+
if db_type == "mysql":
167+
sql = (
168+
"SELECT /*+ MAX_EXECUTION_TIME(2000) */ data, sleep(0.01) FROM test"
169+
)
170+
else:
171+
sql = "SET STATEMENT max_statement_time=2 FOR SELECT data, sleep(0.01) FROM test"
172+
cur.execute(sql)
173+
self.assertEqual(cur.fetchone(), ("row1", 0))
174+
175+
# this discards the previous unfinished query
176+
cur.execute("SELECT 1")
177+
self.assertEqual(cur.fetchone(), (1,))
178+
179+
if db_type == "mysql":
180+
sql = "SELECT /*+ MAX_EXECUTION_TIME(1) */ data, sleep(1) FROM test"
181+
else:
182+
sql = "SET STATEMENT max_statement_time=0.001 FOR SELECT data, sleep(1) FROM test"
183+
with pytest.raises(pymysql.err.OperationalError) as cm:
184+
# in a buffered cursor this should reliably raise an
185+
# OperationalError
186+
cur.execute(sql)
187+
188+
if db_type == "mysql":
189+
# this constant was only introduced in MySQL 5.7, not sure
190+
# what was returned before, may have been ER_QUERY_INTERRUPTED
191+
self.assertEqual(cm.value.args[0], ER.QUERY_TIMEOUT)
192+
else:
193+
self.assertEqual(cm.value.args[0], ER.STATEMENT_TIMEOUT)
194+
195+
# connection should still be fine at this point
196+
cur.execute("SELECT 1")
197+
self.assertEqual(cur.fetchone(), (1,))
198+
132199
def test_warnings(self):
133200
con = self.connect()
134201
cur = con.cursor()

0 commit comments

Comments
 (0)
0