MySQL Forums
Forum List  »  Connector/C (libmysql)

unsuitable error 2020 in prepared statement with more than one MYSQL_TIME params
Posted by: simone cecchini
Date: January 24, 2017 03:03AM

I need some help: I am trying to block-insert some data into a table.
I am using a prepared statement to do that.
I made a lot of attempts, so this message might grow a bit...

The prepared statement is:
INSERT INTO Status
(id, time_sent, t_stamp, last_maint, coords, sgsm_id, statusData_id, can_bus, checksum_a, checksum_b)
SELECT NULL,?,NULL,?,PointFromText(?),id,?,NULL,?,?
FROM Staz_GSM WHERE serial_num=?

as you can see, I use a SELECT statement to drive the insertion. The parameters which are causing trouble *when put together* are 'time_sent' (MYSQL_TYPE_DATETIME) and 'last_maint' (MYSQL_TYPE_DATE).
I declare both the bound parameters using a MYSQL_TIME struct, as suggested here:
https://dev.mysql.com/doc/refman/5.7/en/c-api-prepared-statement-date-handling.html

To make a long story short: the insertion works when I include just one of them, ie when I change the statement to include just one of the date-related parameter. It fails with both included, giving the infamous 'Got packet bigger than max_allowed_packet' bytes, error code: 2020, SQLState: HY000.

I googled the problem and have already increased the max_allowed_packet param to 160M; almost surely that is not the problem: I am testing the program with just 20 lines, and none of the other fields is really big enough to cause the problem.

I have already tried and simplified the query to just the select statement, with only those date-related parameter, but got the same error.
When I look at the server code, I can see that the statement is prepared, but the queries are not executed.

I am at a loss here, any hint is appreciated guys.
Can anyone suggest a different way to obtain the same result of the above statement?



ps: for the most curious of you. The first attempt with just one date-related parameter failed with the very same error. Than I reduced the query to a simple SELECT and then it worked, and the server log showed that the statement was prepared first and then executed.
To overcome the problem with the insert, I had to put the date-related parameter first among the bound ones. I think this behaviour is not correct,on the side of the client.

When I added the second date-related parameter, both the full insert and the simple select stopped working (same error).

Options: ReplyQuote


Subject
Views
Written By
Posted
unsuitable error 2020 in prepared statement with more than one MYSQL_TIME params
342
January 24, 2017 03:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.