| Bug #27747 | database metadata doesn't return sufficient column default info | ||
|---|---|---|---|
| Submitted: | 11 Apr 2007 4:52 | Modified: | 24 Oct 2007 17:59 |
| Reporter: | Nick Griffiths | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 5.0, 5.1 | OS: | Linux |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
[11 Apr 2007 5:53]
Mark Matthews
Not actually a bug with the JDBC driver, but with the server, in that when the JDBC driver uses "SHOW COLUMNS" to get the data, it's returned the way you're reporting. If you're using MySQL-5.0 or newer, you could use "useInformationSchema=true" in your JDBC URL properties, and the driver will use the INFORMATION_SCHEMA instead, which returns this information correctly. Unfortunately, until MySQL-5.1, the INFORMATION_SCHEMA doesn't contain enough information to fully-implement any of the foreign-key related methods in DatabaseMetadata (they'll be missing the restrict/cascade constraints), so be aware of that. (thus I'm changing the category to MySQL Server INFORMATION_SCHEMA, since "SHOW COLUMNS" needs to be fixed to be consistent with INFORMATION_SCHEMA).
[11 Apr 2007 6:07]
Nick Griffiths
great, that should hold me over :)
[11 Apr 2007 9:56]
Sveta Smirnova
Thank you for the report. Verified as described. Should be feature request, though.
[18 Apr 2007 13:38]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/24774 ChangeSet@1.2456, 2007-04-18 18:35:22+05:00, gluh@mysql.com +16 -0 Bug#27747 database metadata doesn't return sufficient column default info
[8 Jun 2007 10:41]
Alexander Barkov
The patch http://lists.mysql.com/commits/24774 looks ok. But I suggest to share code between the "SHOW CREATE TABLE" and the "SHOW COLUMNS" routines. Consider moving this code into a separate function, then reuse it for both "SHOW" commands: /* Again we are using CURRENT_TIMESTAMP instead of NOW because it is more standard */ has_now_default= table->timestamp_field == field && field->unireg_check != Field::TIMESTAMP_UN_FIELD; has_default= (field->type() != FIELD_TYPE_BLOB && !(field->flags & NO_DEFAULT_VALUE_FLAG) && field->unireg_check != Field::NEXT_NUMBER && !((thd->variables.sql_mode & (MODE_MYSQL323 | MODE_MYSQL40)) && has_now_default)); if (has_default) { packet->append(STRING_WITH_LEN(" default ")); if (has_now_default) packet->append(STRING_WITH_LEN("CURRENT_TIMESTAMP")); else if (!field->is_null()) { // Not null by default type.set(tmp, sizeof(tmp), field->charset()); field->val_str(&type); if (type.length()) { String def_val; uint dummy_errors; /* convert to system_charset_info == utf8 */ def_val.copy(type.ptr(), type.length(), field->charset(), system_charset_info, &dummy_errors); append_unescaped(packet, def_val.ptr(), def_val.length()); } else packet->append(STRING_WITH_LEN("''")); } else if (field->maybe_null()) packet->append(STRING_WITH_LEN("NULL")); // Null as default else packet->append(tmp); } The new function prototype could be: bool store_default_value(Field *field, String *str); Or it can be a Field class method: bool Field::store_default_value(String *str);
[29 Jun 2007 23:41]
Peter Gulutzan
Regarding the 2007-04-11 comment "(thus I'm changing the category to MySQL Server INFORMATION_SCHEMA, since "SHOW COLUMNS" needs to be fixed to be consistent with INFORMATION_SCHEMA) ..." This fix won't ensure that SHOW COLUMNS "shows" all that one can find out via information_schema. Examples: create table t1 (s1 varbinary(5) default 0x00); show columns in t1; select hex(column_default) from information_schema.columns where table_name='t1'; create table t2 (s1 char(4) default 'NULL'); show columns in t2; select column_default,ifnull(column_default,'!') from information_schema.columns where table_name='t2'; create table t3 (s1 varchar(5) default ' '); show columns in t3; select length(column_default) from information_schema.columns where table_name='t3'; But generally speaking the information will be available to connectors even if SHOW doesn't display it, and it will be possible to distinguish between NULL and ''.
[12 Sep 2007 7:25]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/34076 ChangeSet@1.2524, 2007-09-12 12:21:11+05:00, gluh@mysql.com +17 -0 Bug#27747 database metadata doesn't return sufficient column default info added get_field_default_value() function which obtains default value from the field (used in store_create_info() & get_schema_column_record() functions)
[13 Sep 2007 9:13]
Alexander Barkov
The patch http://lists.mysql.com/commits/34076 is ok to push
[20 Sep 2007 9:01]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/34417 ChangeSet@1.2529, 2007-09-20 13:54:46+05:00, gluh@mysql.com +17 -0 Bug#27747 database metadata doesn't return sufficient column default info added get_field_default_value() function which obtains default value from the field (used in store_create_info() & get_schema_column_record() functions)
[24 Sep 2007 8:31]
Bugs System
Pushed into 5.0.50
[24 Sep 2007 8:35]
Bugs System
Pushed into 5.1.23-beta
[24 Oct 2007 17:59]
Paul DuBois
Noted in 5.0.50, 5.1.23 changelogs. SHOW COLUMNS returned NULL instead of the empty string for the Default value of columns that had no default specified.
[12 May 2012 16:21]
Paul DuBois
Correction: The Default column *now* shows NULL for columns that have no DEFAULT clause in the column definition.
Description: returning column metadata from DatabaseMetaData.getColumns can't distinguish between a column with no default value and a string column with a default of '' (empty string). Both columns return an empty string through the metadata api. How to repeat: table definition: create table testtable ( fun bigint not null, time varchar(50) default '' ) java snippet: // ... get metadata for columns in table String funDefaultValue = null; String timeDefaultValue = null; while (mdrs.next()) { String colName = mdrs.getString("COLUMN_NAME"); if ("fun".equals(colName)) { funDefaultValue = mdrs.getString("COLUMN_DEF"); } else { timeDefaultValue = mdrs.getString("COLUMN_DEF"); } } funDefaultValue and timeDefaultValue are both '', but no default is defined for funDefaultValue. Suggested fix: shouldn't the case where no default is specifed return null, rather than an empty string (as well as which, that makes little sense for a non string character)?