When inserting data in bulk, parsing parameter errors were found

[wengchengjian]

Hi! I am currently working on a command line program to connect to a database to create test data, and I have used your library in it. A bug was found during batch insertion. When the fields in the MySQL table are uppercase, the parameters cannot be parsed

Brief description of bug

Self-Diagnosis

• [x] I have reviewed the documentation.
• [x] I have reviewed the Wiki.
• [x] I have tried to use the latest version.
• [x] I have searched the issues for an answer to my question.
• [x] I have searched the web for an answer to my question.

Environment(for bug reports)

• [x] Operating System: Windows
• [x] Library Version: 0.32.2

My configuration/Related code

Similar table structure

create table USER_PACKET_INFO
(
    PACKET_ID   bigint auto_increment primary key,
    PACKET_NAME varchar(100) not null,
    DEVICE_ID   bigint       not null,
    LINK_ID     int          not null,
    CENTER_ID   varchar(32)  null,
    CUSTOMER_ID bigint       null,
    data_source_id bigint null,
    user_id bigint null
);

Generated insert values statement

INSERT INTO USER_PACKET_INFO(PACKET_ID, PACKET_NAME, DEVICE_ID, LINK_ID, CENTER_ID, CUSTOMER_ID, data_source_id, user_id) VALUES (:PACKET_ID, :PACKET_NAME, :DEVICE_ID, :LINK_ID, :CENTER_ID, :CUSTOMER_ID, :data_source_id, :user_id)

I found that named_ params#parse_named_params method cannot parse parameters correctly, and can only parse data_source_ id and user_id parameter caused an error in generating insert SQL

Specific error code fragments

            MaybeInNamedParam => match c {
                b'a'..=b'z' | b'_' => {
                    params.push((i - 1, 0, Vec::with_capacity(16)));
                    params[cur_param].2.push(*c);
                    state = InNamedParam;
                }
                _ => rematch = true,
            },
            InNamedParam => match c {
                b'a'..=b'z' | b'0'..=b'9' | b'_' => params[cur_param].2.push(*c),
                _ => {
                    params[cur_param].1 = i;
                    cur_param += 1;
                    rematch = true;
                }
            },

Reason for error

Only lowercase characters were matched when parsing characters

Solution

            MaybeInNamedParam => match c {
                b'a'..=b'z' | b'A'..=b'Z' | b'_' => {
                    params.push((i - 1, 0, Vec::with_capacity(16)));
                    params[cur_param].2.push(*c);
                    state = InNamedParam;
                }
                _ => rematch = true,
            },
            InNamedParam => match c {
                b'a'..=b'z' | b'A'..=b'Z' | b'0'..=b'9' | b'_' => params[cur_param].2.push(*c),
                _ => {
                    params[cur_param].1 = i;
                    cur_param += 1;
                    rematch = true;
                }
            },

[blackbeam]

@wengchengjian, hi.

It seems that you've overlooked the corresponding section in the docs.

tldr; parameter name convention (almost) follows the rust variable identifier convention. Let me cite:

• parameter name must start with either _ or a..z
• parameter name may continue with _, a..z and 0..9

[wengchengjian]

@blackbeam Sorry, I couldn't reply to your message in a timely manner. I already understand what you mean, but I have encountered another problem. When using the batch function, it feels inefficient, like executing SQL one by one.

[blackbeam]

@wengchengjian, yeah. I believe this was previously discussed somewhere in mysql_async or rust-mysql-common issues. Long story short - batch execution is in fact executes SQL one by one. To speed things up you have to build a large query yourself, or consider using LOAD DATA .. (in case of many inserts)