| Bug #68266 | type_info_all reporting bit and decimal types incorrectly | ||
|---|---|---|---|
| Submitted: | 5 Feb 2013 2:33 | Modified: | 11 Apr 2013 23:35 |
| Reporter: | Lyle Hopkins | Email Updates: | |
| Status: | Unsupported | Impact on me: | |
| Category: | Connectors: DBD::mysql ( Perl ) | Severity: | S3 (Non-critical) |
| Version: | 4.022 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[7 Feb 2013 21:17]
MySQL Verification Team
Thank you for the bug report. We don't process DBD::mysql bus please report it to: https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql Thanks.
[11 Apr 2013 23:35]
Lyle Hopkins
The documentation for DBD::mysql: http://search.cpan.org/~capttofu/DBD-mysql-4.022/lib/DBD/mysql.pm Clearly states: ===== BUG REPORTING, ENHANCEMENT/FEATURE REQUESTS ^ Please report bugs, including all the information needed such as DBD::mysql version, MySQL version, OS type/version, etc to this link: http://bugs.mysql.com/ ===== It does not say to use the CPAN RT. Although many bugs have been reported via the CPAN RT that you have suggested, it does not appear that the maintainers are monitoring RT for this module. Before submitting this bug I could see several bugs that had been submitted here and addressed for DBD::mysql. Indeed there was a specific DBD::mysql category that this bug report has been submitted to. Now I can see that since submitting this bug, the DBD::mysql category is no longer in the bug search or reporting. Has MySQL dropped direct support for DBD::mysql? Who was/is the maintainer? Lyle
Description: According to $dbh->type_info_all for the DBD::mysql driver. The SQL_DECIMAL type has the local MySQL type name of double, and the SQL_BIT type has the local MySQL type name of char(1). It also reports an auto_increment option for the bit type. Neither bit nor char(1) can auto_increment: mysql> create temporary table bit_test ( id bit auto_increment primary key); ERROR 1063 (42000): Incorrect column specifier for column 'id' mysql> create temporary table char_test ( id char(1) auto_increment primary key); ERROR 1063 (42000): Incorrect column specifier for column 'id' The SQL_DECIMAL type is an exact numeric type, where is double is an approximate numeric type. It's important that these do not get confused. Looking at the C code in dbdimp.c: { "decimal", SQL_DECIMAL, 15, NULL, NULL, "precision,scale", 1, 0, 3, 0, 0, 0, "double", 0, 6, 2, SQL_DECIMAL, 0, 0, #if MYSQL_VERSION_ID < MYSQL_VERSION_5_0 FIELD_TYPE_DECIMAL, 1 #else MYSQL_TYPE_DECIMAL, 1 #endif }, ... { "bit", SQL_BIT, 1, NULL, NULL, NULL, 1, 0, 3, 0, 0, 0, "char(1)", 0, 0, 0, #if MYSQL_VERSION_ID < MYSQL_VERSION_5_0 SQL_BIT, 0, 0, FIELD_TYPE_TINY, 0 #else SQL_BIT, 0, 0, MYSQL_TYPE_TINY, 0 #endif }, The correct native type is used for SQL_DECIMAL, and this should be reported as decimal and not double. The native type used for SQL_BIT is either FIELD_TYPE_TINY or MYSQL_TYPE_TINY, instead of FIELD_TYPE_BIT or MYSQL_TYPE_BIT. Lyle How to repeat: use DBI; use Data::Dumper; my $dbh = DBI->connect('DBI:mysql:database=test;host=localhost;','test','test'); # Connect to mysql DB print Dumper( $dbh->type_info_all() ); # Dump out sql types Suggested fix: Without having tested, it appears the following patch should apply to fix these issues: --- dbdimp.c Tue Feb 05 02:14:27 2013 +++ dbdimp_new.c Tue Feb 05 02:23:38 2013 @@ -825,7 +825,7 @@ #endif }, { "decimal", SQL_DECIMAL, 15, NULL, NULL, "precision,scale", - 1, 0, 3, 0, 0, 0, "double", + 1, 0, 3, 0, 0, 0, "decimal", 0, 6, 2, SQL_DECIMAL, 0, 0, #if MYSQL_VERSION_ID < MYSQL_VERSION_5_0 @@ -1049,7 +1049,7 @@ }, { "decimal", SQL_NUMERIC, 15, NULL, NULL, "precision,scale", - 1, 0, 3, 0, 0, 0, "double", + 1, 0, 3, 0, 0, 0, "decimal", 0, 6, 2, SQL_NUMERIC, 0, 0, #if MYSQL_VERSION_ID < MYSQL_VERSION_5_0 @@ -1193,11 +1193,11 @@ }, { "bit", SQL_BIT, 1, NULL, NULL, NULL, - 1, 0, 3, 0, 0, 0, "char(1)", 0, 0, 0, + 1, 0, 3, 0, 0, 0, "bit", 0, 0, 0, #if MYSQL_VERSION_ID < MYSQL_VERSION_5_0 - SQL_BIT, 0, 0, FIELD_TYPE_TINY, 0 + SQL_BIT, 0, 0, FIELD_TYPE_BIT, 0 #else - SQL_BIT, 0, 0, MYSQL_TYPE_TINY, 0 + SQL_BIT, 0, 0, MYSQL_TYPE_BIT, 0 #endif }, @@ -1288,15 +1288,6 @@ SQL_BIGINT, 0, 0, FIELD_TYPE_LONGLONG, 1 #else SQL_BIGINT, 0, 0, MYSQL_TYPE_LONGLONG, 1 -#endif - }, - - { "bit auto_increment", SQL_BIT, 1, NULL, NULL, NULL, - 0, 0, 3, 0, 0, 1, "char(1) auto_increment", 0, 0, 0, -#if MYSQL_VERSION_ID < MYSQL_VERSION_5_0 - SQL_BIT, 0, 0, FIELD_TYPE_TINY, 1 -#else - SQL_BIT, 0, 0, MYSQL_TYPE_TINY, 1 #endif },