I have this error while inserting batch in mysql:
java.sql.BatchUpdateException: Packet for query is too large (70,566,811 > 67,108,864). You can change this value on the server by setting the 'max_allowed_packet' variable.
I need to read the insights from a Google-API day by day and save them using fetchMultiDailyMetricsTimeSeries Method. Everyday, the program should get executed to read the info related to 3 days ago,but I discovered that Google is not updated for last three days and Now I am obligated to update the info in my table for at least 3 months. Here arise the problem that we receive loads of data on a field typed JSON in mysql returned from API. So, I cannot change the table structure since there are other programs reading from this table and this problem will be no longer valid after updating the table.
The query is a simple Insert that I need to to consider ON DUPLICATE UPDATE. In fact, I cannot change the max-allowed-pocket variable (The DBA does not want to change it).
I have split my list in a way that for every 500 records, the program goes to do the insert. However, it seems that it is not enough and the received data is too large even in 500 records limit. right now, I am trying to make the break-point smaller and also fetch the info for a shorter loop of days like 10 so that there would not be a problem but this would be too slow since I need to perform these for all the clients (more than 20) and for 90 days.
I use JAVA and JDBC Prepared Statement.
What can I do else?