MySQL Bugs: #75542: Incorrect arguments to mysqld_stmt

Bug #75542	Incorrect arguments to mysqld_stmt_execute error
Submitted:	18 Jan 2015 0:50	Modified:	17 Mar 2015 17:20
Reporter:	Igor Prokopenkov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / Python	Severity:	S2 (Serious)
Version:	2.0.2	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	connector, cursor, prepared, python 3

Description:
# mysql -V
mysql  Ver 14.14 Distrib 5.6.17, for Linux (x86_64) using  EditLine wrapper
# python3 -V
Python 3.3.5

Error occurs on prepared cursor with utf-8 encoded statement param

        try:
            #error
            self.__ads_insert.execute("INSERT INTO `ads` " \
                "(`sites_id`, `id_on_site`, `url`, `date`, `phones_id`, `title`, `price_grn`, `address`, `text`, `rooms`) VALUES " \
                "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", params)
            ads_id = self.__ads_insert.lastrowid
        except mysql.connector.errors.ProgrammingError:
            self.__logger.exception("connector version: {}".format(mysql.connector.version.VERSION_TEXT))
            #OK
            self.__cursor.execute("INSERT INTO `ads` " \
                "(`sites_id`, `id_on_site`, `url`, `date`, `phones_id`, `title`, `price_grn`, `address`, `text`, `rooms`) VALUES " \
                "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", params)

2015-01-18 02:48:07 ERROR rsdb.mysql connector version: 2.0.2
Traceback (most recent call last):
  File "/home/igorek/PycharmProjects/rsdb/rsdb/mysql.py", line 71, in insert_ad
    "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", params)
  File "/usr/lib/python3.3/site-packages/mysql/connector/cursor.py", line 1084, in execute
    parameters=self._prepared['parameters'])
  File "/usr/lib/python3.3/site-packages/mysql/connector/connection.py", line 1606, in cmd_stmt_execute
    result = self._handle_binary_result(packet)
  File "/usr/lib/python3.3/site-packages/mysql/connector/connection.py", line 1545, in _handle_binary_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1210 (HY000): Incorrect arguments to mysqld_stmt_execute

How to repeat:
CREATE TABLE IF NOT EXISTS `ads` (
  `id` int(10) unsigned NOT NULL,
  `sites_id` smallint(5) unsigned NOT NULL,
  `id_on_site` varchar(20) CHARACTER SET utf8 NOT NULL,
  `url` varchar(255) CHARACTER SET utf8 NOT NULL,
  `date` datetime NOT NULL,
  `phones_id` int(10) unsigned NOT NULL,
  `title` varchar(255) CHARACTER SET utf8 NOT NULL,
  `price_grn` int(11) NOT NULL,
  `address` varchar(255) CHARACTER SET utf8 NOT NULL,
  `text` mediumtext CHARACTER SET utf8 NOT NULL,
  `rooms` tinyint(3) unsigned NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

        import pickle
        params = pickle.loads(b'\x80\x03(K\x01J[\xb0\x8e\nX`\x00\x00\x00http://kharkov.kha.olx.ua/m/obyavlenie/sdam-odnokomnatnuyu-kvartiru-IDbZbTl.html?code=fa13... 22:14:00q\x01KGXJ\x00\x00\x00\xd0\x9e\xd0\xb1\xd1\x8a\xd1\x8f\xd0\xb2\xd0\xbb\xd0\xb5\xd0\xbd\xd0\xb8\xd0\xb5: \xd0\xa1\xd0\xb4\xd0\xb0\xd0\xbc \xd0\xbe\xd0\xb4\xd0\xbd\xd0\xbe\xd0\xba\xd0\xbe\xd0\xbc\xd0\xbd\xd0\xb0\xd1\x82\xd0\xbd\xd1\x83\xd1\x8e \xd0\xba\xd0\xb2\xd0\xb0\xd1\x80\xd1\x82\xd0\xb8\xd1\x80\xd1\x83q\x02M\xac\rXY\x00\x00\x00\xd0\xa5\xd0\xb0\xd1\x80\xd1\x8c\xd0\xba\xd0\xbe\xd0\xb2 , \xd0\x94\xd0\xb7\xd0\xb5\xd1\x80\xd0\xb6\xd0\xb8\xd0\xbd\xd1\x81\xd0\xba\xd0\xb8\xd0\xb9 , \xd0\x9f\xd0\xb0\xd0\xb2\xd0\xbb\xd0\xbe\xd0\xb2\xd0\xbe \xd0\x9f\xd0\xbe\xd0\xbb\xd0\xb5 \xd0\xba\xd0\xb0\xd1\x84\xd0\xb5 \xd0\xa2\xd1\x80\xd0\xbe\xd1\x8f\xd0\xbd\xd0\xb4\xd0\xb0q\x03X\xfe\x00\x00\x00\xd0\xa1\xd0\xb4\xd0\xb0\xd0\xbc \xd0\xbe\xd0\xb4\xd0\xbd\xd0\xbe\xd0\xba\xd0\xbe\xd0\xbc\xd0\xbd\xd0\xb0\xd1\x82\xd0\xbd\xd1\x83\xd1\x8e \xd0\xba\xd0\xb2\xd0\xb0\xd1\x80\xd1\x82\xd0\xb8\xd1\x80\xd1\x83 \xd0\xbd\xd0\xb0 \xd0\x9f\xd0\xb0\xd0\xb2\xd0\xbb\xd0\xbe\xd0\xb2\xd0\xbe\xd0\xbc \xd0\x9f\xd0\xbe\xd0\xbb\xd0\xb5. \xd0\xa1\xd0\xbe\xd1\x81\xd1\x82\xd0\xbe\xd1\x8f\xd0\xbd\xd0\xb8\xd0\xb5 \xd1\x85\xd0\xbe\xd1\x80\xd0\xbe\xd1\x88\xd0\xb5\xd0\xb5. \xd0\x92\xd1\x81\xd1\x91 \xd0\xbd\xd0\xb5\xd0\xbe\xd0\xb1\xd1\x85\xd0\xbe\xd0\xb4\xd0\xb8\xd0\xbc\xd0\xbe\xd0\xb5 \xd0\xb4\xd0\xbb\xd1\x8f \xd0\xbf\xd1\x80\xd0\xbe\xd0\xb6\xd0\xb8\xd0\xb2\xd0\xb0\xd0\xbd\xd0\xb8\xd1\x8f \xd0\xb5\xd1\x81\xd1\x82\xd1\x8c. \xd0\xa7\xd0\xb8\xd1\x81\xd1\x82\xd0\xbe. \xd0\xa2\xd0\xb5\xd0\xbf\xd0\xbb\xd0\xbe. \xd0\xa3\xd1\x8e\xd1\x82\xd0\xbd\xd0\xbe. \xd0\xa2\xd1\x80\xd0\xb0\xd0\xbd\xd1\x81\xd0\xbf\xd0\xbe\xd1\x80\xd1\x82 \xd1\x80\xd1\x8f\xd0\xb4\xd0\xbe\xd0\xbc.q\x04K\x01tq\x05.')

cursor = connection.cursor(prepared=True)
cursor.execute("INSERT INTO `ads` " \
    "(`sites_id`, `id_on_site`, `url`, `date`, `phones_id`, `title`, `price_grn`, `address`, `text`, `rooms`) VALUES " \
    "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", params)

Thank you for the report.

I get error "_pickle.UnpicklingError: invalid load key, ':'." when try to run code provided. Could you please attache test case as a file to avoid corruption, possible if use copy-paste from the bug report page.

Please load attached file with

params = pickle.load(open("params.dat", "rb"))

Verified.

Thanks for the bug report.

Fix is to change following line in method mysql.connector.utils.lc_int

change line 
if i <= 255:
  ....

to

if i < 251:
  ....

Noted in 2.0.4 changelog.

Encoding failure could occur for prepared cursors with UTF-8
statement parameters.

Noted in 2.1.2 changelog.