| Bug #99602 | mysql python connector returns empty result on certain query | ||
|---|---|---|---|
| Submitted: | 16 May 2020 15:27 | Modified: | 7 Jul 2020 13:13 |
| Reporter: | Alliumcepa Triplef | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / Python | Severity: | S2 (Serious) |
| Version: | 8.0.20-1ubuntu18.04 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[7 Jul 2020 13:13]
MySQL Verification Team
Hello Alliumcepa Triplef,
Thank you for the report and test case.
I'm trying to reproduce this issue using Connector/Python 8.0.20 and python 3.7, 3.8 etc but not seeing any issues.
- Schema
CREATE TABLE `user_log_4` (
`_` bigint unsigned NOT NULL AUTO_INCREMENT,
`ip` varchar(255) NOT NULL,
`cookie` varchar(255) NOT NULL,
`id` bigint unsigned NOT NULL,
`tm` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`_`),
KEY `ip` (`ip`) USING BTREE,
KEY `cookie` (`cookie`) USING BTREE,
KEY `tm` (`tm`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
set @id:=0;
insert into `user_log_4` values
(@id:=@id+1,INET_NTOA(@id:=@id+1),REPEAT('a',250),@id:=@id+1, NOW())
, (@id:=@id+1,INET_NTOA(@id:=@id+1),REPEAT('a',250),@id:=@id+1, NOW())
, (@id:=@id+1,INET_NTOA(@id:=@id+1),REPEAT('a',250),@id:=@id+1, NOW())
, (@id:=@id+1,INET_NTOA(@id:=@id+1),REPEAT('a',250),@id:=@id+1, NOW());
insert into `user_log_4`(`_`,`ip`,`cookie`,`id`,`tm`)
select @id:=@id+1,INET_NTOA(@id:=@id+1),REPEAT('a',250),@id:=@id+1, NOW() from
`user_log_4` k1, `user_log_4` k2, `user_log_4` k3, `user_log_4` k4,`user_log_4` k5,`user_log_4` k6, `user_log_4` k7, `user_log_4` k8, `user_log_4` k9,
`user_log_4` k0,`user_log_4` ka, `user_log_4` kb, `user_log_4` kc, `user_log_4` kd limit 10000000;
-
import mysql.connector
import sys
import platform
print("OS: {} {}".format(platform.system(), platform.release()))
print("Python:", format(sys.version))
driver = mysql.connector
print("Driver: {} {}".format(driver.__name__, driver.__version__))
print()
conn = mysql.connector.connect(host='hod03.no.oracle.com', user='ushastry', port=3333, database='test',use_pure=True)
print(type(conn))
cur = conn.cursor()
cur.execute('''
SELECT
cookie,
max(`_`) AS _,
max(`tm`) AS tm,
group_concat(`id` ORDER BY `tm` ASC) AS id
FROM
(
SELECT * FROM user_log_4 WHERE _ > 6684441 limit 1000
) as T
GROUP BY
cookie
''')
ret = cur.fetchall()
print(len(ret)) # this will always be 0
cur.execute('''
SELECT * FROM user_log_4 WHERE _ > 6684441 limit 1000
''')
ret = cur.fetchall() # this will error out
print(len(ret))
-
OS: Windows 10
Python: 3.7.5 (tags/v3.7.5:5c02a39a0b, Oct 15 2019, 00:11:34) [MSC v.1916 64 bit (AMD64)]
Driver: mysql.connector 8.0.20
<class 'mysql.connector.connection.MySQLConnection'>
1
1000
Press any key to continue . . .
-
OS: Windows 10
Python: 3.8.0 (tags/v3.8.0:fa919fd, Oct 14 2019, 19:21:23) [MSC v.1916 32 bit (Intel)]
Driver: mysql.connector 8.0.20
<class 'mysql.connector.connection.MySQLConnection'>
1
1000
Press any key to continue . . .
-
OS: Windows 10
('Python:', '2.7.16 (v2.7.16:413a49145e, Mar 4 2019, 01:37:19) [MSC v.1500 64 bit (AMD64)]')
Driver: mysql.connector 8.0.20
()
<class 'mysql.connector.connection.MySQLConnection'>
1
1000
Press any key to continue . . .
-----
If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.
Thank you for your interest in MySQL.
regards,
Umesh
Description: When the connecter is used with `use_pure=True`, certain query will let the library always return empty result and further query will error with "IndexError: bytearray index out of range". With the same code the C extension works perfectly. my server version is 8.0.20 community on ubuntu. How to repeat: import mysql.connector conn = mysql.connector.connect(host='localhost', user='user', password='password', database='database',) cur = conn.cursor() cur.execute(''' SELECT cookie, max(`_`) AS _, max(`tm`) AS tm, group_concat(`id` ORDER BY `tm` ASC) AS id FROM ( SELECT * FROM user_log_4 WHERE _ > 1684441 limit 1000 ) as T GROUP BY cookie ''') ret = cur.fetchall() print(len(ret)) # this will always be 0 cur.execute(''' SELECT * FROM user_log_4 WHERE _ > 1684441 limit 1000 ''') ret = cur.fetchall() # this will error out print(len(ret)) note the number 1684441 in query the bug only triggers when this is high enough. also when decreasing the limit the query may succeed. table schema: CREATE TABLE `user_log_4` ( `_` mediumint unsigned NOT NULL AUTO_INCREMENT, `ip` varchar(255) NOT NULL, `cookie` varchar(255) NOT NULL, `id` mediumint unsigned NOT NULL, `tm` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (`_`), KEY `ip` (`ip`) USING BTREE, KEY `cookie` (`cookie`) USING BTREE, KEY `tm` (`tm`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=3990028 DEFAULT CHARSET=utf8 the table currently has 3990027 rows and is larger than the compare in query.