Matt,
I am forwarding this to MySQL developers.
> Problem description:
> --------------------
> MySQL does not return key information about any column after the first
> in a unique multi-column key. Also, the "MUL" flag seems to indicate
> that the key is non-unique, when in fact it is.
This output format of DESCRIBE TABLE has been discussed before. It is not
easily understandable, though the above is what Monty intended it to be.
I recommend using
SHOW CREATE TABLE tablename;
to look at a table structure. It contains the foreign key definitions and
all. DESCRIBE TABLE does not contain all information.
Regards,
Heikki
----- Original Message -----
From: "Matt Solnit" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: "Henry Bequet" <[EMAIL PROTECTED]>
Sent: Saturday, December 28, 2002 2:44 AM
Subject: Bug report: UNIQUE KEY and DESCRIBE TABLE
===============================================
Bug report -- MySQL v4.06, binary distribution
===============================================
--------------
Machine specs:
--------------
Compaq Presario desktop
512 MB RAM
Windows XP Professional SP1
--------------------
Problem description:
--------------------
MySQL does not return key information about any column after the first
in a unique multi-column key. Also, the "MUL" flag seems to indicate
that the key is non-unique, when in fact it is.
There is an equivalent symptom in the MySQL C API. In the flags field
of the MYSQL_FIELD structure returned by mysql_fetch_field(), the
MULTIPLE_KEY_FLAG will only be present in the first column.
-------------
Test script:
-------------
mysql>USE test
mysql>CREATE TABLE mytable (a INT NOT NULL, b INT NOT NULL, c INT NOT
NULL, d INT NOT NULL, PRIMARY KEY (a), UNIQUE KEY (b, c));
mysql>DESCRIBE TABLE mytable;
------------------
Results:
------------------
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | | PRI | 0 | |
| b | int(11) | | MUL | 0 | |
| c | int(11) | | | 0 | |
| d | int(11) | | | 0 | |
+-------+---------+------+-----+---------+-------+
----------------
C test program:
----------------
/***********************************************
Expected output (according to manual section 8.4.1):
Column `a`: primary=1, unique=0, multiple=0
Column `b`: primary=0, unique=1, multiple=0
Column `c`: primary=0, unique=1, multiple=0
Column `d`: primary=0, unique=0, multiple=0
Actual output:
Column `a`: primary=1, unique=0, multiple=0
Column `b`: primary=0, unique=0, multiple=1
Column `c`: primary=0, unique=0, multiple=0
Column `d`: primary=0, unique=0, multiple=0
***********************************************/
#include "stdafx.h"
#include <winsock.h>
#include <mysql.h>
#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h>
MYSQL *db_connect(const char *dbname);
void db_disconnect(MYSQL *db);
void db_do_query(MYSQL *db, const char *query);
const char *server_groups[] = {
"test_libmysqld_SERVER", "embedded", "server", NULL
};
int main(int argc, char* argv[])
{
MYSQL *one;
mysql_server_init(argc, argv, (char **)server_groups);
one = db_connect("test");
const char* query = "SELECT * FROM mytable";
mysql_query(one, query);
MYSQL_RES* res = mysql_store_result(one);
int numFields = mysql_num_fields(res);
for (int i = 0; i < numFields; i++)
{
MYSQL_FIELD* fld = mysql_fetch_field(res);
char* name = strdup(fld->name);
bool isPrimary = ((fld->flags & PRI_KEY_FLAG) > 0);
bool isUnique = ((fld->flags & UNIQUE_KEY_FLAG) > 0);
bool isMulti = ((fld->flags & MULTIPLE_KEY_FLAG) > 0);
printf("column `%s`: primary=%d, unique=%d, multiple=%d\n", name,
isPrimary, isUnique, isMulti);
}
mysql_close(one);
mysql_server_end();
return 0;
}
static void
die(MYSQL *db, char *fmt, ...)
{
va_list ap;
va_start(ap, fmt);
vfprintf(stderr, fmt, ap);
va_end(ap);
(void)putc('\n', stderr);
if (db)
db_disconnect(db);
exit(EXIT_FAILURE);
}
MYSQL *
db_connect(const char *dbname)
{
MYSQL *db = mysql_init(NULL);
if (!db)
die(db, "mysql_init failed: no memory");
/*
* Notice that the client and server use separate group names.
* This is critical, because the server will not accept the
* client's options, and vice versa.
*/
mysql_options(db, MYSQL_READ_DEFAULT_GROUP, "test_libmysqld_CLIENT");
if (!mysql_real_connect(db, NULL, NULL, NULL, dbname, 0, NULL, 0))
die(db, "mysql_real_connect failed: %s", mysql_error(db));
return db;
}
void
db_disconnect(MYSQL *db)
{
mysql_close(db);
}
-----------------------
My contact information:
-----------------------
Matt Solnit <[EMAIL PROTECTED]>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
