| Bug #93510 | Invalid data returned by the cursor | ||
|---|---|---|---|
| Submitted: | 6 Dec 2018 16:37 | Modified: | 16 Dec 2018 16:25 |
| Reporter: | Kamil Kubis | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / Python | Severity: | S1 (Critical) |
| Version: | 8.0.13 | OS: | Debian |
| Assigned to: | CPU Architecture: | x86 | |
[6 Dec 2018 16:37]
Kamil Kubis
[13 Dec 2018 9:01]
Umesh Shastry
Hello Kamil,
Thank you for your bug report.
I'm unable to reproduce reported issue using Python 2.7.15, Connector/Python v 8.0.13 and MySQL Server 5.7.24/5.6.42 with dummy data. Could you please provide exact reproducible test case? If the data you need to attach is more than 3MB, you should create a compressed archive of the data and a README file that describes the data with a filename that includes the bug number (recommended filename: mysql-bug-data-93510.zip) and upload one to sftp.oracle.com. 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.
##
create database if not exists test;
use test;
drop table if exists keyvalue;
CREATE TABLE `keyvalue` (
`id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`)
) PARTITION BY KEY()
PARTITIONS 32;
-- 2B rows
for(my $i=1; $i <= 2112640599; $i++) {
print "$i\n";
}
- CLI with --local-infile
mysql> load data local infile 'bulk.dmp' into table keyvalue fields terminated by ',';
Query OK, 2112640599 rows affected (3 hours 38 min 59.54 sec)
Records: 2112640599 Deleted: 0 Skipped: 0 Warnings: 0
import mysql.connector
import sys
print(sys.version)
print 'mysql connector version: ' + mysql.connector.__version__
settings = {
'host': 'xxxx.xxx.oracle.com',
'database': 'test',
'user': 'ushastry',
'password': '',
'port': 3333
}
conn = mysql.connector.connect(database=settings['database'], host=settings['host'], user=settings['user'], password=settings['password'], port=settings['port'])
cursor = conn.cursor(raw=True)
sql = 'SELECT * FROM test.keyvalue WHERE id > 1886629616 AND id <= 1888819066'
#Number of rows returned in my case: 2189450
batch_size = 1000000
cursor.execute(sql)
rows = cursor.fetchmany(batch_size)
print type(rows)
print len(rows)
rows = cursor.fetchmany(batch_size)
print type(rows)
print len(rows)
rows = cursor.fetchmany(batch_size)
print type(rows)
print len(rows)
rows = cursor.fetchmany(batch_size)
print type(rows)
print len(rows)
cursor.close()
conn.close()
- Python 2.7.5
PYTHONPATH="./lib/python2.7/site-packages/" python bug.py
2.7.5 (default, Jun 25 2015, 03:51:47)
[GCC 4.8.3 20140911 (Red Hat 4.8.3-9)]
mysql connector version: 8.0.13
<type 'list'>
1000000
<type 'list'>
1000000
<type 'list'>
189450
<type 'list'>
0
- Python 2.7.15
D:\MySource>python bug.py
2.7.15 (v2.7.15:ca079a3ea3, Apr 30 2018, 16:30:26) [MSC v.1500 64 bit (AMD64)]
mysql connector version: 8.0.13
<type 'list'>
1000000
<type 'list'>
1000000
<type 'list'>
189450
<type 'list'>
0
thanks,
Umesh
[13 Dec 2018 15:05]
Kamil Kubis
Hi Umesh, I cannot change the status to "Open", but I can reproduce it without any issues. I have reviewed your solution, and what I found out is, that you are using different linux (redhat) to the one I experience the bug on. I do run bug.py script on official python image with is based on debian. As a test I run the script inside of centos/python-27-centos7 image. There is no bug in there. However I will attach the sql file witch data I'm testing on, but please keep in mind that to be able to replicate an issue, you need to switch to debian. Best regards, Kamil
[13 Dec 2018 15:22]
Kamil Kubis
/support/incoming/mysql-bug-data-93510.zip file added.
[14 Dec 2018 12:52]
Umesh Shastry
Thank you for the requested data, if possible could you please post here output of SHOW CREATE TABLE vcstats.voi_kamil\G? Thank you!
[14 Dec 2018 13:19]
Kamil Kubis
Of course. Sorry, I should include create statement in my file in the first place: CREATE TABLE vcstats.voi_kamil ( impressionID BIGINT UNSIGNED DEFAULT '0' NOT NULL ) ENGINE = InnoDB; Also, FYI. I'm using AWS MySQL RDS, version: 5.6.34-log
[16 Dec 2018 8:00]
Umesh Shastry
Thank you, still no luck with the exact table and data.
-- MySQL 5.6.42 hosted on a remote server
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.42-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database vcstats;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE vcstats.voi_kamil
-> (
-> impressionID BIGINT UNSIGNED DEFAULT '0' NOT NULL
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> \q
Bye
bin/mysql -uroot -S /tmp/mysql_ushastry.sock vcstats < mysql-bug/vcstats_voi_kamil.sql
### Debian9
-- Python 2.7.15 - installed using source
wget https://www.python.org/ftp/python/2.7.15/Python-2.7.15.tar.xz
tar -xf Python-2.7.15.tar.xz
cd Python-2.7.15
./configure
make -j32
make altinstall
chmod -v 755 /usr/lib/usr/lib/x86_64-linux-gnu/libpython2.7.so.1.0
root@umshastr:/home/ushastry/Downloads# cat bug.py
import mysql.connector
import sys
print(sys.version)
print 'mysql connector version: ' + mysql.connector.__version__
settings = {
'host': 'xxxxx.no.oracle.com',
'database': 'vcstats',
'user': 'ushastry',
'password': '',
'port': 3333
}
conn = mysql.connector.connect(database=settings['database'], host=settings['host'], user=settings['user'], password=settings['password'], port=settings['port'])
cursor = conn.cursor(raw=True)
sql = 'select impressionID from vcstats.voi_kamil WHERE impressionID > 2886629616 AND impressionID <= 2888819066;'
#Number of rows returned in my case: 2189450
batch_size = 1000000
cursor.execute(sql)
rows = cursor.fetchmany(batch_size)
print type(rows)
print len(rows)
rows = cursor.fetchmany(batch_size)
print type(rows)
print len(rows)
rows = cursor.fetchmany(batch_size)
print type(rows)
print len(rows)
rows = cursor.fetchmany(batch_size)
print type(rows)
print len(rows)
cursor.close()
conn.close()
root@umshastr:/home/ushastry/Downloads# cat /etc/*release
PRETTY_NAME="Debian GNU/Linux 9 (stretch)"
NAME="Debian GNU/Linux"
VERSION_ID="9"
VERSION="9 (stretch)"
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"
root@umshastr:/home/ushastry/Downloads# python2.7 --version
Python 2.7.15
root@umshastr:/home/ushastry/Downloads#
root@umshastr:/home/ushastry/Downloads# PYTHONPATH="/usr/lib/python2.7/dist-packages/" python2.7 bug.py
2.7.15 (default, Dec 16 2018, 02:06:15)
[GCC 6.3.0 20170516]
mysql connector version: 8.0.13
<type 'list'>
1000000
<type 'list'>
1000000
<type 'list'>
189450
<type 'list'>
0
root@umshastr:/home/ushastry/Downloads# PYTHONPATH="/usr/lib/python2.7/dist-packages/" python2.7 bug.py
2.7.15 (default, Dec 16 2018, 02:06:15)
[GCC 6.3.0 20170516]
mysql connector version: 8.0.13
<type 'list'>
1000000
<type 'list'>
1000000
<type 'list'>
189450
<type 'list'>
0
[16 Dec 2018 15:28]
Kamil Kubis
Hi Umesh, I will try to run compiled from source version of Python on debian. Did you try running it, as I do, inside docker container based of official python:2.7 image? I suspect it's to do with some underlying packages version, but as I do not know how you installed Debian and what package versions you have, I cannot replicate steps you took. Thanks for looking it again. Best regards, Kamil
[16 Dec 2018 15:28]
Kamil Kubis
Hi Umesh, I will try to run compiled from source version of Python on debian. Did you try running it, as I do, inside docker container based of official python:2.7 image? I suspect it's to do with some underlying packages version, but as I do not know how you installed Debian and what package versions you have, I cannot replicate steps you took. Thanks for looking it again. Best regards, Kamil
[16 Dec 2018 16:25]
Kamil Kubis
Hi Umesh, I took a bit different approach. I did run bug.py script like: python -mtrace --trace bug.py I did select the part of trace which we are investigating: (Attached as file) I did run then: pip uninstall mysql-connector-python pip install mysql-connector-python==8.0.11 python -mtrace --trace bug.py And here is a trace when it works: (Attached as file) Above should hopefully be enough to find the cause of an issue. I would be really interested to see the trace from your debian successful run on 8.0.13 connector. Best regards, Kamil
[16 Dec 2018 16:27]
Kamil Kubis
8.0.13 connector - NOT working version
Attachment: bug.8.0.13.txt (text/plain), 3.87 KiB.
[16 Dec 2018 16:27]
Kamil Kubis
8.0.11 connector - working version
Attachment: bug.8.0.11.txt (text/plain), 4.25 KiB.
[12 Jan 2:27]
Rolph Lundt
I see this too: ✸ 8.0.13 ✸ Fedora ✸ MySQL 5.7. --------------------------------------------------- ✸ Large table (10M records, 3GB in size) ✸ C extension ✸ Unbuffered resultset ✸ After resultset is fully fetched, fetchmany() returns the 1st record ad infinitum. ✸ This happens regardless of number fetched (large or small). ✸ Triggered when you fetch any number not evenly divisible into the resultset size
