| Bug #10130 | MyODBC error in auto_increment PrimaryKeys | ||
|---|---|---|---|
| Submitted: | 24 Apr 2005 16:53 | Modified: | 26 Jan 2006 0:17 |
| Reporter: | Armando Bañuelos | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.11.00 | OS: | Windows (WinXP) |
| Assigned to: | Sergey Vlasenko | CPU Architecture: | Any |
[24 Apr 2005 17:48]
Jorge del Conde
Thanks for your bug-report. This behaviour is also present in 3.51.11-2
[24 Oct 2005 19:07]
Peter Harvey
I have tested this using c/odbc v3.51.12 using myodbc3m as follows; myodbc3m -b mydsn myid mypwd < sql.txt > result.txt where sql.txt has single line of; help example This invokes SQLColumns. The result.txt file shows nullable for column 1 but column 1 is a pk. This may require changes to code when determining nullable and this occurs in several places - search for NOT_NULL_FLAG and take into account mysqlclientlib version as required.
[22 Dec 2005 11:49]
Sergey Vlasenko
Index: myodbc3/catalog.c
===================================================================
--- myodbc3/catalog.c (revision 29)
+++ myodbc3/catalog.c (working copy)
@@ -802,7 +802,7 @@
row[15]= strdup_root(alloc,buff);
}
- if ( (curField->flags & (NOT_NULL_FLAG | AUTO_INCREMENT_FLAG)) == NOT_NULL_FLAG )
+ if ( (curField->flags & NOT_NULL_FLAG) == NOT_NULL_FLAG )
{
sprintf(buff,"%d",SQL_NO_NULLS);
row[10]= strdup_root(alloc,buff);
Index: myodbc3/results.c
===================================================================
--- myodbc3/results.c (revision 29)
+++ myodbc3/results.c (working copy)
@@ -247,7 +247,7 @@
if ( pibScale )
*pibScale= field->decimals;
if ( pfNullable )
- *pfNullable= (((field->flags & (NOT_NULL_FLAG | AUTO_INCREMENT_FLAG)) ==
+ *pfNullable= (((field->flags & (NOT_NULL_FLAG)) ==
NOT_NULL_FLAG) ?
SQL_NO_NULLS :
SQL_NULLABLE);
@@ -436,8 +436,7 @@
case SQL_DESC_NULLABLE:
case SQL_COLUMN_NULLABLE:
*(SQLINTEGER *)NumericAttributePtr= (((field->flags &
- (NOT_NULL_FLAG |
- AUTO_INCREMENT_FLAG)) ==
+ (NOT_NULL_FLAG)) ==
NOT_NULL_FLAG) ?
SQL_NO_NULLS :
SQL_NULLABLE);
[24 Jan 2006 19:44]
Peter Harvey
sergey; please apply fix (if not in source repo already) and close.
[25 Jan 2006 16:57]
Sergey Vlasenko
Fix is available in 3.51.12
[26 Jan 2006 0:17]
Mike Hillyer
Closing - the docs team does not maintain the C/ODBC changelog.
[19 Sep 2006 4:31]
Jeremy Prine
This bug is still occurring; I am using version 3.51.12 of the MySQL ODBC driver, under Windows, and connecting to MySQL 5.0.22-community-nt on the same Windows machine. My table structure, as returned by "SHOW CREATE TABLE Posts":
CREATE TABLE `posts` (
`PostId` int(4) NOT NULL auto_increment,
`ParentPostId` int(4) default NULL COMMENT 'Post that this post can be considered a "child" of',
PRIMARY KEY (`PostId`),
KEY `Posts_ParentPostId__Posts_PostId` (`ParentPostId`),
CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`ParentPostId`) REFERENCES `posts` (`PostId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Stuff that we post on the site'
I am also using ODBC .NET, like the previous bug-reporter, and am attempting to use DbDataAdapter.FillSchema(). The DataTable I pass it has its Columns collection correctly populated with all column information, except that AllowDBNull is 'true' for the 'PostId' column (my primary key column), and the PrimaryKey column collection is always zero-length.
Test code used:
(C# console application using Visual Studio .NET 2005 and .NET
---------------
using System;
using System.Data;
using System.Data.Odbc;
namespace OdbcTest
{
class Program
{
static void Main(string[] args)
{
OdbcConnection connection = new OdbcConnection("DSN=FireHazardGames");
connection.Open();
OdbcCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM Posts";
cmd.CommandType = CommandType.Text;
OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
DataTable schemaTable = new DataTable("Posts");
adapter.FillSchema(schemaTable, SchemaType.Mapped);
foreach (DataColumn column in schemaTable.Columns)
{
Console.WriteLine
(
column.ColumnName + ":"
+ " AllowDBNull=" + column.AllowDBNull.ToString()
+ ", AutoIncrement=" + column.AutoIncrement.ToString()
+ ", Unique=" + column.Unique.ToString()
);
}
if (schemaTable.PrimaryKey.Length == 0)
Console.WriteLine("\nNo primary key is defined for table Posts.");
schemaTable.Dispose();
adapter.Dispose();
cmd.Dispose();
connection.Close();
connection.Dispose();
Console.WriteLine("\n\nPress any key to exit.");
while (!Console.KeyAvailable)
{
}
}
}
}
---------------
Using the configuration I described, the following output is produced by the above test code:
---------------
PostId: AllowDBNull=True, AutoIncrement=True, Unique=True
ParentPostId: AllowDBNull=True, AutoIncrement=False, Unique=False
No primary key is defined for table Posts.
Press any key to exit.
---------------
[28 Sep 2006 9:29]
Tonci Grgin
Works in 3.51.13. More in BUG#22463.
Description: I found this error with the MyODBC using ODBC.NET when querying the PrimaryKeys. The error is because the auto_increment columns are marked as NULLABLE. That should be changed to NOT_NULL cause they are really primary keys and must be NOT_NULL. I remove the autoincrement feature of the column, and the the column is markes as NOT_NULL, and the ODBC.NET driver returns the correct PrimaryKey information. I think of a bug and do a trace with ODBC directly and the primary keys columns are being retrieved OK (with and without auto_increment columns). The I use ODBC directly (SQLColumns function) to retrieve the column information of the auto_increment primary key. The result was: -> field "IS_NULLABLE" is marked as "YES" -> field "NULLABLE" is marked as "SQL_NULLABLE" so I think ODBC.NET assumes that the column is not being a real primary key because is marked as nullable by the driver... Thanks in advance How to repeat: The example of this table is: create table example ( id int(3) not null auto_increment, example varchar(30) not null, primary key (id) ); The Code in VC#.NET is OdbcCommand cmd = connection.CreateCommand(); cmd.CommandText = "SELECT * FROM example"; DataSet dataSet = new DataSet(); OdbcDataAdapter da = new OdbcDataAdapter(cmd); DataTable dataTable = da.FillSchema(dataSet, SchemaType.Source, "example")[0]; The result is: dataTable.PrimaryKey = {Length=0} dataTable.Columns["id"].AllowDBNull = true The Code in ODBC: unsigned char TABLE_NAME[50]; unsigned char COLUMN_NAME[50]; short KEY_SEQ; unsigned char PK_NAME[50]; SQLPrimaryKeys(hstmt, NULL, 0, NULL, 0, "example", SQL_NTS); SQLFetch(hstmt); SQLGetData(hstmt,3,SQL_C_CHAR,TABLE_NAME,sizeof(TABLE_NAME),&cbData); SQLGetData(hstmt,4,SQL_C_CHAR,COLUMN_NAME,sizeof(COLUMN_NAME),&cbData); SQLGetData(hstmt,5,SQL_C_SHORT,&KEY_SEQ,sizeof(KEY_SEQ),&cbData); SQLGetData(hstmt,6,SQL_C_CHAR,PK_NAME,sizeof(PK_NAME),&cbData); The result is: TABLE_NAME = "example" COLUMN_NAME = "id" KEY_SEQ = 1 PK_NAME = "PRIMARY" ================ short nullable; unsigned char isNullable[5]; SDWORD cbData; // Output length of data SQLColumns(hstmt, NULL, 0, NULL, 0, "example", SQL_NTS, NULL, 0); SQLFetch(hstmt); SQLGetData(hstmt, 11, SQL_C_SHORT, &nullable, sizeof(SQL_C_SSHORT), &cbData); SQLGetData(hstmt,18,SQL_C_CHAR,isNullable,sizeof(isNullable),&cbData); The result is: nullable = SQL_NULLABLE isNullable = "YES" Suggested fix: I think you should change the retrieved values for auto_increment primary keys to be not nullable.. because by default a Primary Keys is not null and the driver incorrectly report it as nullable...