| Bug #101282 | list index out of range in _DATE_to_python() while using json_extract | ||
|---|---|---|---|
| Submitted: | 23 Oct 2020 9:59 | Modified: | 3 Nov 2020 6:36 |
| Reporter: | Mitesh Jat | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / Python | Severity: | S2 (Serious) |
| Version: | 8.0.13 | OS: | Linux (5.4.0-48-generic #52-Ubuntu SMP) |
| Assigned to: | CPU Architecture: | x86 (x86_64) | |
| Tags: | connector, MySQL, python | ||
[23 Oct 2020 10:32]
Mitesh Jat
MySQL Server Version: 5.7
[2 Nov 2020 12:23]
MySQL Verification Team
Hello Mitesh Jat,
Thank you for the report and feedback.
I'm not seeing any issues with with the Connector/Python 8.0.22(only diff being that I'm using VS 2019 and Python version 3.7.8). Could you please upgrade Connector/Python and report us back if you are still seeing the issue? 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.
-
--
Opened connection <mysql.connector.connection.MySQLConnection object at 0x000002202DD84B48>
OS: Windows 10
Python: 3.7.8 (tags/v3.7.8:4b47a5b6ba, Jun 28 2020, 08:53:46) [MSC v.1916 64 bit (AMD64)]
Driver: mysql.connector 8.0.22
MySQL Server version : 8.0.22
Inside Constructor
Executed sql: select json_extract(info,"$.json_object.address.pincode") pincode from `bug`.demo on cursor: MySQLCursor: select json_extract(info,"$.json_object...
[('"600052"',)]
Press any key to continue . . .
.
--
Opened connection <mysql.connector.connection.MySQLConnection object at 0x00000145910B4B88>
OS: Windows 10
Python: 3.7.8 (tags/v3.7.8:4b47a5b6ba, Jun 28 2020, 08:53:46) [MSC v.1916 64 bit (AMD64)]
Driver: mysql.connector 8.0.22
MySQL Server version : 5.7.32
Inside Constructor
Executed sql: select json_extract(info,"$.json_object.address.pincode") pincode from `bug`.demo on cursor: MySQLCursor: select json_extract(info,"$.json_object...
[('"600052"',)]
Press any key to continue . . .
regards,
Umesh
[2 Nov 2020 12:47]
MySQL Verification Team
- VS 2019, Python 3.8.0, Connector/Python 8.0.22 and MySQL Server 5.7 - no issues observed
Opened connection <mysql.connector.connection.MySQLConnection object at 0x0365B370>
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.22
MySQL Server version : 5.7.32
Inside Constructor
Executed sql: select json_extract(info,"$.json_object.address.pincode") pincode from `bug`.demo on cursor: MySQLCursor: select json_extract(info,"$.json_object...
[('"600052"',)]
Press any key to continue . . .
[3 Nov 2020 6:36]
Mitesh Jat
Dear Umesh, Kindly try to repeat on Linux Machine instead of Windows.
[5 Nov 2020 8:58]
MySQL Verification Team
On OL7, MySQL Server 5.7.32, Python 3.8 and Connector/Python 8.0.22 PYTHONPATH="./8.0.22/lib/python3.8/site-packages/" python3.8 Bug101282.py Opened connection <mysql.connector.connection.MySQLConnection object at 0x7f4484e23970> OS: Linux 5.4.17-2011.1.2.el7uek.x86_64 Python: 3.8.6 (default, Nov 4 2020, 09:39:13) [GCC 4.8.5 20150623 (Red Hat 4.8.5-39.0.3)] Driver: mysql.connector 8.0.22 MySQL Server version : 5.7.32 Inside Constructor Executed sql: select json_extract(info,"$.json_object.address.pincode") pincode from `bug`.demo on cursor: MySQLCursor: select json_extract(info,"$.json_object... [('"600052"',)]
Description: In mysql.connector package, _DATE_to_python() function is throwing *list index out of range* Error. $ python3 working.py Opened connection <mysql.connector.connection.MySQLConnection object at 0x7fa1d8017c10> Inside Constructor Executed sql: select json_extract(info,"$.json_object.address.pincode") pincode from `sample_db`.demo on cursor: MySQLCursor: select json_extract(info,"$.json_object... list index out of range ERROR: Failed to fetchall cursor trace working.py: 70 ERROR: Failed to fetchall cursor trace /home/mitesh/.local/lib/python3.8/site-packages/mysql/connector/cursor.py: 896 ERROR: Failed to fetchall cursor trace /home/mitesh/.local/lib/python3.8/site-packages/mysql/connector/connection.py: 506 ERROR: Failed to fetchall cursor trace /home/mitesh/.local/lib/python3.8/site-packages/mysql/connector/connection.py: 506 ERROR: Failed to fetchall cursor trace /home/mitesh/.local/lib/python3.8/site-packages/mysql/connector/conversion.py: 407 ERROR: Failed to fetchall cursor trace /home/mitesh/.local/lib/python3.8/site-packages/mysql/connector/conversion.py: 581 ERROR: Failed to fetchall cursor trace /home/mitesh/.local/lib/python3.8/site-packages/mysql/connector/conversion.py: 472 How to repeat: ## Create demo table and populate sample data ## mysql> create table demo (info json not null); Query OK, 0 rows affected (0.02 sec) mysql> describe demo; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | info | json | NO | | NULL | | +-------+------+------+-----+---------+-------+ mysql> insert into demo (info) values ('{\"id\": \"823e486a-3847-40e6-b661-a8d28ed1abcd\", \"metadata\": null, \"json_object\": {\"address\": {\"city\": \"Tiruvallur\", \"state\": \"tamil nadu\", \"address\": \"Pillaiyar kovil street\", \"pincode\": \"600052\"}, \"recent_biller_id\": \"f9d28edf-a070-4181-a79c-4c8f8f55efgh\", \"recent_rental_amount\": \"0\"}, \"object_type\": \"HOUSE_RENTAL\"}'); Query OK, 1 row affected (0.00 sec) mysql> select * from demo; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"id": "823e486a-3847-40e6-b661-a8d28ed1abcd", "metadata": null, "json_object": {"address": {"city": "Tiruvallur", "state": "tamil nadu", "address": "Pillaiyar kovil street", "pincode": "600052"}, "recent_biller_id": "f9d28edf-a070-4181-a79c-4c8f8f55efgh", "recent_rental_amount": "0"}, "object_type": "HOUSE_RENTAL"} | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select json_extract(info,"$.json_object.address.pincode") pincode from demo; +----------+ | pincode | +----------+ | "600052" | +----------+ 1 row in set (0.01 sec) ## The query is working in mysql prompt ## ################################################################## ## however, same query fails with mysql.connector : python code## #!/usr/bin/env python3 import mysql.connector import traceback class MySQLConnection: def __init__(self): self.host = '10.1.1.29' self.port = 3306 self.user = 'dev_adhoc_read' self.password = 'HCfDT4qJ' self.dbname = 'sample_db' self.connection = None try: self.connection = mysql.connector.connect(host=self.host, port=self.port, database=self.dbname, user=self.user, password=self.password) except Exception as err: print("Error Opening Connection: " + str(err)) else: # self.connection.set_session(autocommit=True) print("Opened connection " + str(self.connection)) def __enter__(self): print("Returning connection " + str(self.connection)) return self def __exit__(self, exc_type, exc_value, traceback): print("Trying to close Connection: " + str(self.connection)) self.connection.close() print("Closed connection " + str(self.connection)) return False class MySQLQueryHelper: def __init__(self, connection): self.connection = connection print("Inside Constructor") self.cursor = None def __enter__(self): return self def execute_sql(self, sql): try: self.sql = sql ## DW-418: Disabling Buffered mode to avoid full fetch in memory self.cursor = self.connection.cursor() self.cursor.execute(self.sql) print("Executed sql: " + str(self.sql) + " on cursor: " + str(self.cursor)) except Exception as err: self.cursor = None print(f'Error Executing SQL: {err} -> {sql}') raise Exception(err) return self.cursor if __name__ == '__main__': try: conn = MySQLConnection() helpr = MySQLQueryHelper(conn.connection) query = 'select json_extract(info,"$.json_object.address.pincode") pincode from `sample_db`.demo' cursor = helpr.execute_sql(query) batch_size = 10 rows = cursor.fetchall() print(rows) except Exception as e: print(e) traceback.format_exc() tb = e.__traceback__ while tb: print("ERROR: Failed to fetchall cursor trace {}: {}".format(tb.tb_frame.f_code.co_filename, tb.tb_lineno)) tb = tb.tb_next ######################################################## This python script fails with *list index out of range* Error as given in Description. ~ Suggested fix: Suggested fix in mysql/connector/conversion.py: by adding *except IndexError* section for in try block, as given below: 464 def _DATE_to_python(self, value, dsc=None): # pylint: disable=C0103 465 """ 466 Returns DATE column type as datetime.date type. 467 """ 468 if isinstance(value, datetime.date): 469 return value 470 try: 471 parts = value.split(b'-') 472 return datetime.date(int(parts[0]), int(parts[1]), int(parts[2])) 473 except ValueError: 474 return None 475 except IndexError: 476 return None 477 except Exception: 478 return None The above code worked for us.