MySQL Bugs: #7860: MySQL returning real column width even when option=3 is set

Bug #7860	MySQL returning real column width even when option=3 is set
Submitted:	13 Jan 2005 4:08	Modified:	27 Jul 2007 15:04
Reporter:	Jeff Mullins	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Microsoft Windows (Windows XP)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
MyODBC driver version 3.51.10.00
MySQL returns the real column width (the maximum field length in CHAR fields) instead of the maximum length of the field. This happens regardless of the option setting in the connection string.

How to repeat:
Imports System.Data
Imports System.Data.Odbc
Module Module1
Sub Main()
 Dim  MySQL As New OdbcConnection
 Dim  daMySQL As New OdbcDataAdapter
 Dim dsMysql As New DataSet
With MySQL
           .ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};UID=????;PWD=??????;SERVER=localhost;DATABASE=fwc;option=3"                       Try
                .Open()
            Catch ex As Exception
                Console.Write("Stack trace is: " + ex.StackTrace + ControlChars.CrLf)
                Console.Write("Error Message: " + ex.message + ControlChars.CrLf)
                GoTo cleanup
            End Try
            With daMySQL
                .SelectCommand = New OdbcCommand("SELECT * FROM checks")
                .SelectCommand.Connection = MySQL
                .InsertCommand = New OdbcCommand("INSERT INTO checks (trans_date,check_nbr,fund,payee,comments,account,status,amount) VALUES(?,?,?,?,?,?,?,?)")
                .InsertCommand.Parameters.Add(New OdbcParameter("@TRANSDATE", OdbcType.DateTime, Nothing, "trans_date"))
                .InsertCommand.Parameters.Add(New OdbcParameter("@CHECKNBR", OdbcType.Int, Nothing, "check_nbr"))
                .InsertCommand.Parameters.Add(New OdbcParameter("@FUND", OdbcType.VarChar, 50, "fund"))
                .InsertCommand.Parameters.Add(New OdbcParameter("@PAYEE", OdbcType.VarChar, 75, "payee"))
                .InsertCommand.Parameters.Add(New OdbcParameter("@COMMENTS", OdbcType.VarChar, 75, "comments"))
                .InsertCommand.Parameters.Add(New OdbcParameter("@ACCOUNT", OdbcType.VarChar, 75, "account"))
                .InsertCommand.Parameters.Add(New OdbcParameter("@STATUS", OdbcType.Char, 1, "status"))
                .InsertCommand.Parameters.Add(New OdbcParameter("@AMOUNT", OdbcType.Decimal, Nothing, "amount"))
                .InsertCommand.Connection = MySQL
                .UpdateCommand = New OdbcCommand("UPDATE checks SET trans_date=?,check_nbr=?,fund=?,payee=?,comments=?,account=?,status=?,amount=? where trans_id = ?")
                .UpdateCommand.Parameters.Add(New OdbcParameter("@TRANSDATE", OdbcType.DateTime, Nothing, "trans_date"))
                .UpdateCommand.Parameters.Add(New OdbcParameter("@CHECKNBR", OdbcType.Int, Nothing, "check_nbr"))
                .UpdateCommand.Parameters.Add(New OdbcParameter("@FUND", OdbcType.VarChar, 50, "fund"))
                .UpdateCommand.Parameters.Add(New OdbcParameter("@PAYEE", OdbcType.VarChar, 75, "payee"))
                .UpdateCommand.Parameters.Add(New OdbcParameter("@COMMENTS", OdbcType.VarChar, 75, "comments"))
                .UpdateCommand.Parameters.Add(New OdbcParameter("@ACCOUNT", OdbcType.VarChar, 75, "account"))
                .UpdateCommand.Parameters.Add(New OdbcParameter("@STATUS", OdbcType.Char, 1, "status"))
                .UpdateCommand.Parameters.Add(New OdbcParameter("@AMOUNT", OdbcType.Decimal, Nothing, "amount"))
                .UpdateCommand.Parameters.Add(New OdbcParameter("@TRANSID", OdbcType.BigInt, Nothing, "trans_id"))
                .UpdateCommand.Connection = MySQL
                Try
                    .MissingSchemaAction = MissingSchemaAction.AddWithKey
                    .Fill(dsMysql, "checks")
                Catch ex As Exception
                    GoTo cleanup
                End Try
            End With
        End With
cleanup:
MySQL.Close()
daMySQL.Dispose()

Failed to add that I was using VB.NET

Even though there is no bigint columns and the table is simple there is no new record in the table and none of the records are updated.

create table checks2 (id int auto_increment primary key, mytext varchar(50);

-----------------------------------------------

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim MySQL As New OdbcConnection
    Dim daMySQL As New OdbcDataAdapter
    Dim dsMysql As New DataSet
    With MySQL
      .ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};UID=root;PWD=;SERVER=localhost;DATABASE=test2;option=3"
      Try
        .Open()
      Catch ex As Exception
        Console.Write("Stack trace is: " + ex.StackTrace + ControlChars.CrLf)
        Console.Write("Error Message: " + ex.message + ControlChars.CrLf)
        GoTo cleanup
      End Try
      With daMySQL
        .SelectCommand = New OdbcCommand("SELECT * FROM checks2")
        .SelectCommand.Connection = MySQL
        .InsertCommand = New OdbcCommand("INSERT INTO checks (id, mytext) VALUES(?, ?)")
        .InsertCommand.Parameters.Add(New OdbcParameter("@ID", OdbcType.Int, Nothing, "id"))
        .InsertCommand.Parameters.Add(New OdbcParameter("@MYTEXT", OdbcType.VarChar, 50, "mytext"))
        .InsertCommand.Connection = MySQL

        .UpdateCommand = New OdbcCommand("UPDATE checks2 SET mytext = ? where id = ?")
        .InsertCommand.Parameters.Add(New OdbcParameter("@MYTEXT", OdbcType.VarChar, 50, "mytext"))
        .InsertCommand.Parameters.Add(New OdbcParameter("@ID", OdbcType.Int, Nothing, "id"))
        .UpdateCommand.Connection = MySQL

        Try
          .MissingSchemaAction = MissingSchemaAction.AddWithKey
          .Fill(dsMysql, "checks2")
        Catch ex As Exception
          GoTo cleanup
        End Try
      End With
    End With
cleanup:
    MySQL.Close()
    daMySQL.Dispose()

  End Sub

The old default behavior was incorrect. In 3.51.18, the way that lengths are returned in the various places they are returned will be as the standard declares they should be, but this also means that this "don't optimize widths" option will be obsolete.

It's also totally unclear what either of the code snippets attached to this bug have to do with the bug. Neither appears to do anything to access or verify the length of any columns.