Z
Z
Zakharov Alexander2017-10-07 02:12:33
MySQL
Zakharov Alexander, 2017-10-07 02:12:33

MySQL, jdbc, insert/update executeBatch hangs endlessly and won't let go?

Hello.
There is a base on MySQL (5.7.17/19), jdbc 6.0.5 (6.0.6, 8.0.8), java 1.8.144 (Linux/Windows). The base is small. I use the newfangled JSON data type very actively. Triggers have been made that monitor changes, and if the user has changed JSON, they save new data, and add old data to the log table. So everything works fine.
But the database has a service for synchronizing user data with 1C. Getting data from 1C is not a problem, and everything is fine here. As soon as I receive the data, I create a preparedStatement and fill it with addBatch:

BasicDBList rows = (BasicDBList)com.mongodb.util.JSON.parse( json_data );
            // Обновить данные cv_users
            StringBuilder sb = new StringBuilder();
            sb.append("UPDATE cv_users SET json_version=?, json_data=cast(? as JSON) WHERE id=?; ");
=           ps = conn_mysql.prepareStatement(sb.toString());
            Options.debugMessage("Формирую executeBatch CheckPermissionAndUpdateCVUsers");
            for(int i=0; i<=rows.size()-1; i++) {
                BasicDBObject rows_i = (BasicDBObject)rows.get(i);
                String  str_id = (String)rows_i.get("id");
                id = Integer.parseInt(str_id);
                String  str_json_version = (String)rows_i.get("<blockquote></blockquote>json_version");
                json_version = Integer.parseInt(str_json_version);
                json_data = ((BasicDBObject) rows_i.get("json_data")).toString();
                ps.setInt(1, json_version);
                ps.setString(2, json_data);
                ps.setInt(3, id);
=               ps.addBatch();
            }
            Options.debugMessage("Выполняю executeBatch CheckPermissionAndUpdateCVUsers "+rows.size()+" шт.");
=           int[] res_update = ps.executeBatch();

The data is filled in normally (the loop works out completely), but executeBatch does not. This is the problem. I can't get it to run.
What I have already tried:
0. Different versions of the connector, mysql-server (the last one too), put the base on Linux / Windows.
1. By logging, I found an entry on which everything stops:
my.cnf:

log_output = "FILE"
general_log_file = F:/install/mysql/5.7.19/mysql-5.7.19-winx64/query.log
general_log = 1
log_error = F:/install/mysql/5.7.19/mysql-5.7.19 -winx64/error.log
# https://dev.mysql.com/doc/refman/5.7/en/server-sys...
log_error_verbosity = 3

tail: There is definitely no problem with the data. I tried to copy the request itself directly from the log to the mysql console - it works fine: maybe after 200-300 or +-). Moreover, the other entry will also have no problems and its command from the logs will be executed in the console. 3. I noticed that timeouts do not work. Those. if the operation hangs on, then it is forever, until I throw off the update process in the server console or restart the server. 4. There is nothing special in the mysql error logs.
59d802331c5be304865961.png
59d80a140ab1f196407808.png
ps.executeBatch();
5. I tried to run only this record (without others) through the update - it stops again. I typed a pack of such stop recordings - to no avail. There is nothing special about these records.
6. I tried to send not the entire executeBatch, but in parts. For example, both 100 and 10 pieces, it still stops on this record.
7. Tried to change the request. For example
sb.append("UPDATE cv_users SET json_version=?, json_data=? WHERE id=?; "); // Removed cast
Starts to fall on another record.
Actually the question is - what else can you see or configure?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question