MySQL Bugs: #115539: Commands out of sync when running LOAD DATA LOCAL INFILE

Bug #115539	Commands out of sync when running LOAD DATA LOCAL INFILE
Submitted:	8 Jul 2024 9:12	Modified:	9 Jul 2024 13:42
Reporter:	Manuel Matonin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When running multiple statements separated by semicolons then Commands out of sync is raised when local file does not exist.

How to repeat:

    /*
    CREATE TABLE `_multi_statement_test` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `message` TEXT,  
        PRIMARY KEY (`id`)  
    ) ENGINE=INNODB;
    */

    mysql = mysql_init(NULL);

    if (mysql_real_connect(mysql, host_name, user_name, password,
        db_name, port_num, NULL, CLIENT_MULTI_STATEMENTS) == NULL)
    {
        printf("mysql_real_connect() failed\n");
        printf("Error: %s\n", mysql_error(mysql));
        mysql_close(mysql);
        exit(1);
    }

again:

    /* execute multiple statements */
    status = mysql_query(mysql,
        "SET @TS_VAR = now(); LOAD DATA LOCAL INFILE '_test.csv' REPLACE INTO TABLE _multi_statement_test");
    if (status)
    {
        printf("Could not execute statement(s)");
        printf("Error: %s\n", mysql_error(mysql));
        printf("Error No: %d\n", mysql_errno(mysql));
        mysql_close(mysql);
        exit(0);
    }

    /* process each statement result */
    do {
        /* did current statement return data? */
        result = mysql_store_result(mysql);
        if (result)
        {
            /* ignore processing results */
            mysql_free_result(result);
        }
        else          /* no result set or error */
        {
            if (mysql_field_count(mysql) == 0)
            {
                printf("%lld rows affected\n",
                    mysql_affected_rows(mysql));
            }
            else  /* some error occurred */
            {
                printf("Could not retrieve result set\n");
                printf("Error: %s\n", mysql_error(mysql));
                break;
            }
        }
        /* more results? -1 = no, >0 = error, 0 = yes (keep looping) */
        if ((status = mysql_next_result(mysql)) > 0) {
            printf("Could not execute statement\n");
            printf("Error: %s\n", mysql_error(mysql));
            printf("Error No: %d\n", mysql_errno(mysql));
        }
    } while (status == 0);

    goto again;

Suggested fix:
When one of the multi statement queries fails with an error "not found (OS errno 2 - No such file or directory)" then it is not expected that client library will raise "Commands out of sync". For example, query error will not raise "Commands out of sync" error.

Hi Mr. Matonin,

Thank you for your bug report.

However, this is a forum for the bug with repeatable test cases.

Hence, we would be grateful if you can upload the CSV file, which is missing in your bug report.

Please, use "Files" tab to upload a file.

Thanks in advance.

The issue reported is about the non-existing file "_test.csv", that is, to reproduce the error the file must not exist.

HI Mr. Matonin,

What you reported is not a bug.

Simply,  CLIENT_MULTI_RESULTS or CLIENT_MULTI_STATEMENTS can only be used with CALL() SQL query command that calls stored routines.

This is all documented in our Reference Manual.

Hi AI,

Please read carefully the initial description in the bug report and existing documentation: https://dev.mysql.com/doc/c-api/8.0/en/c-api-multiple-queries.html

API mysql_query() should not generate "Commands out of sync" error when local file does not exist.

Hi Mr. Matonin,

We actually wrote our own program and tested it on 8.0.38 and 8.4.1.

In our program , no error was reported what so ever.

Hence it is affecting latest 8.0, 8.4 and 9.0.

Hence , this is even more severe bug than the one you reported.

Verified as reported.

Thank you .....