Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Inserting an enum value into an integer column yields "Incorrect integer value" #965

Closed
helmutschneider opened this issue Apr 2, 2021 · 7 comments
Labels

Comments

@helmutschneider
Copy link

helmutschneider commented Apr 2, 2021

The following program works correctly on 1.2.1 but not 1.3.2. I'm not sure if it's an intended change or a regression. Could not find anything relevant in the changelog.

using System;
using System.Threading.Tasks;
using MySqlConnector;

namespace App
{
    enum CarKind
    {
        Toyota,
    }

    sealed class Program
    {
        const string CarSchemaSql = @"
          CREATE TABLE `car` (
              `id` BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
              `kind` TINYINT UNSIGNED NOT NULL
          )";

        const string InsertCarSql = @"INSERT INTO `car`(`kind`) VALUES (?)";

        async static Task Main(string[] args)
        {
            var builder = new MySqlConnectionStringBuilder
            {
                Server = "127.0.0.1",
                UserID = "root",
                Password = "",
                Database = "my_db",
            };

            var conn = new MySqlConnection(builder.ConnectionString);

            await conn.OpenAsync();

            using var tblCmd = conn.CreateCommand();
            tblCmd.CommandText = CarSchemaSql;

            await tblCmd.PrepareAsync();
            await tblCmd.ExecuteNonQueryAsync();

            using var insertCmd = conn.CreateCommand();
            insertCmd.CommandText = InsertCarSql;
            await insertCmd.PrepareAsync();

            var param = insertCmd.CreateParameter();
            param.Value = CarKind.Toyota;
            insertCmd.Parameters.Add(param);

            // Fails on 1.3.2 with
            //   Unhandled exception. MySqlConnector.MySqlException (0x80004005): Incorrect integer value: '' for column 'kind' at row 1
            await insertCmd.ExecuteNonQueryAsync();
        }
    }
}
Unhandled exception. MySqlConnector.MySqlException (0x80004005): Incorrect integer value: '' for column 'kind' at row 1
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 867
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 50
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 135
   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 444
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 60
   at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 266
@bgrainger
Copy link
Member

bgrainger commented Apr 2, 2021

Could not find anything in relevant in the changelog.

I'm guessing the relevant change is that IgnorePrepare now defaults to false. Previously, calls to Prepare were actually ignored. If so, this may have been a bug in Prepare for a long time.

Update: This does fail on 1.2.1 with IgnorePrepare=false in the connection string.

A workaround on 1.3.2 is to explicitly add ;IgnorePrepare=true to the connection string.

@bgrainger bgrainger added the bug label Apr 2, 2021
@bgrainger
Copy link
Member

Also fails with MySQL Connector/NET 8.0.23 with MySqlException: Incorrect arguments to mysqld_stmt_execute.

@bgrainger
Copy link
Member

bgrainger commented Apr 2, 2021

Succeeds in MySqlConnector (not MySql.Data) by adding explicit type: param.MySqlDbType = MySqlDbType.Byte;.

Edit: This may actually be sending a malformed packet to the server; not completely sure. I would recommend type conversion for the parameter value instead:

param.Value = (byte) CarKind.Toyota;

@bgrainger
Copy link
Member

Most likely, the following code needs to be updated to coerce parameter values to the column types returned by the server when preparing the statement:

foreach (var parameter in parameters)
{
// override explicit MySqlDbType with inferred type from the Value
var mySqlDbType = parameter.MySqlDbType;
var typeMapping = (parameter.Value is null || parameter.Value == DBNull.Value) ? null : TypeMapper.Instance.GetDbTypeMapping(parameter.Value.GetType());
if (typeMapping is not null)
{
var dbType = typeMapping.DbTypes[0];
mySqlDbType = TypeMapper.Instance.GetMySqlDbTypeForDbType(dbType);
}
writer.Write(TypeMapper.ConvertToColumnTypeAndFlags(mySqlDbType, command.Connection!.GuidFormat));
}
var options = command.CreateStatementPreparerOptions();
foreach (var parameter in parameters)
parameter.AppendBinary(writer, options);

@helmutschneider
Copy link
Author

@bgrainger Interesting, thank you for debugging. If an implicit cast is necessary would expect it to be to the backing type of the enum and not the DB type. My example used TINYINT by coincidence. However, this is totally outside my realm and I understand if you need to support actual database enums too.

@bgrainger
Copy link
Member

The real problem might actually be failure to correctly infer the MySqlDbType for any enum type.

@bgrainger
Copy link
Member

Fixed in 1.3.3.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Development

No branches or pull requests

2 participants