Bug #48526 Data type for float and double is incorrectly reported in InnoDB table monitor
Submitted: 4 Nov 2009 9:13 Modified: 20 Jun 2010 17:17
Reporter: Oli Sennhauser Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1.40 OS:Any
Assigned to: Satya B
Tags: double, FLOAT, innodb_table_monitor
Triage: Triaged: D3 (Medium)

[4 Nov 2009 9:13] Oli Sennhauser
Description:
Having a table with float and double the data types are incorrectly reported in innodb table monitor.

How to repeat:
CREATE TABLE buggy (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, f1 INT
, f2 FLOAT
, f3 DOUBLE
, f4 INT
) ENGINE=InnoDB;

enable InnoDB Monitor:

TABLE: name test/buggy, id 0 146, columns 8, indexes 1, appr.rows 0
  COLUMNS:
id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4;
f1: DATA_INT DATA_BINARY_TYPE len 4;
f2: type 9 DATA_BINARY_TYPE len 4;
f3: type 10 DATA_BINARY_TYPE len 8;
f4: DATA_INT DATA_BINARY_TYPE len 4;
DB_ROW_ID: DATA_SYS prtype 256 len 6;
DB_TRX_ID: DATA_SYS prtype 257 len 6;
DB_ROLL_PTR: DATA_SYS prtype 258 len 7;                      
  INDEX: name PRIMARY, id 0 281, fields 1/7, uniq 1, type 3
   root page 220, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  id DB_TRX_ID DB_ROLL_PTR f1 f2 f3 f4

Suggested fix:
Types should be reported correctly as:

#define DATA_FLOAT      9
#define DATA_DOUBLE     10
[9 Nov 2009 7:12] Jimmy Yang
This is due to in function dtype_print(), we have not process the float and double datatype:

dtype_print() 
{
 switch (mtype) {
        case DATA_VARCHAR:
             fputs("DATA_VARCHAR", stderr);
             break;
 ......
        default:  <==== Double and float get here, thus we have type 9, type 10
             fprintf(stderr, "type %lu", (ulong) mtype);
             break;
}

Fix by adding following type to dtype_print()

#define DATA_FLOAT      9
#define DATA_DOUBLE     10
#define DATA_DECIMAL    11      /* decimal number stored as an ASCII string */
#define DATA_VARMYSQL   12      /* any charset varying length char */
#define DATA_MYSQL      13      /* any charset fixed length char */
                                /* NOTE that 4.1.1 used DATA_MYSQL and
                                DATA_VARMYSQL for all character sets, and the
                                charset-collation for tables created with it
                                can also be latin1_swedish_ci */

Thanks
Jimmy
[9 Nov 2009 7:29] Jimmy Yang
Output after the fix:

Notice column f2 and f3 are of DATA_FLOAT and DATA_DOUBLE now.
--------------------------------------
TABLE: name test/buggy, id 0 13, columns 8, indexes 1, appr.rows 0
  COLUMNS: id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4; f1: DATA_INT DATA_BINARY_TYPE len 4; f2: DATA_FLOAT DATA_BINARY_TYPE len 4; f3: DATA_DOUBLE DATA_BINARY_TYPE len 8; f4: DATA_INT DATA_BINARY_TYPE len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
  INDEX: name PRIMARY, id 0 15, fields 1/7, uniq 1, type 3
   root page 50, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  id DB_TRX_ID DB_ROLL_PTR f1 f2 f3 f4
[30 Nov 2009 8:54] 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/92029

3215 Satya B	2009-11-30
      Applying InnoDB snapshot 5.1-ss6242, part 4. Fixes BUG#48526
      
      1. BUG#48526 - Data type for float and double is incorrectly 
                     reported in InnoDB table monitor
      
      Detailed revision comments:
      
      r6188 | jyang | 2009-11-18 07:14:23 +0200 (Wed, 18 Nov 2009) | 8 lines
      branches/5.1: Fix bug #48526 "Data type for float and
      double is incorrectly reported in InnoDB table monitor".
      Certain datatypes are not printed correctly in
      dtype_print().
      
      rb://204 Approved by Marko.
[30 Nov 2009 13:35] 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/92090

3229 Satya B	2009-11-30
      Applying InnoDB Plugin 1.0.6 snapshot, part 9. Fixes BUG#48526, BUG#48317
      
      applied revisions: r6197, r6198, r6199
      r6197 - BUG#48317
      r6199 - BUG#48526
      
      Detailed revision comments:
      
      r6197 | calvin | 2009-11-19 09:32:55 +0200 (Thu, 19 Nov 2009) | 6 lines
      branches/zip: merge the fix of bug#48317 (CMake file)
      
      Due to MySQL changes to the CMake, it is no longer able
      to build InnoDB plugin as a static library on Windows.
      The fix is proposed by Vlad of MySQL.
      
      r6198 | vasil | 2009-11-19 09:44:31 +0200 (Thu, 19 Nov 2009) | 4 lines
      branches/zip:
      
      Add ChangeLog entry for r6197.
      
      r6199 | vasil | 2009-11-19 12:10:12 +0200 (Thu, 19 Nov 2009) | 31 lines
      branches/zip: Merge r6159:6198 from branches/5.1:
      
        ------------------------------------------------------------------------
        r6187 | jyang | 2009-11-18 05:27:30 +0200 (Wed, 18 Nov 2009) | 9 lines
        Changed paths:
           M /branches/5.1/btr/btr0btr.c
        
        branches/5.1: Fix bug #48469 "when innodb tablespace is
        configured too small, crash and corruption!". Function
        btr_create() did not check the return status of fseg_create(),
        and continue the index creation even there is no sufficient
        space.
        
        rb://205 Approved by Marko
        
        
        ------------------------------------------------------------------------
        r6188 | jyang | 2009-11-18 07:14:23 +0200 (Wed, 18 Nov 2009) | 8 lines
        Changed paths:
           M /branches/5.1/data/data0type.c
        
        branches/5.1: Fix bug #48526 "Data type for float and
        double is incorrectly reported in InnoDB table monitor".
        Certain datatypes are not printed correctly in
        dtype_print().
        
        rb://204 Approved by Marko.
        
        
        ------------------------------------------------------------------------
[1 Dec 2009 9:13] Satya B
patch queued to 5.1-bugteam storage/innobase and for the plugin storage/innodb_
plugin.

NULL merged to 6.0 and will be merged to 5.5.*
[2 Dec 2009 8:04] Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:satya.bn@sun.com-20091130133520-z0m04c2d63wl83bj) (merge vers: 5.1.42) (pib:13)
[10 Dec 2009 2:03] Paul Dubois
Noted in 5.1.42.

The InnoDB Table Monitor reported the FLOAT and DOUBLE data types
incorrectly. 

Setting report to NDI pending push to 5.5.x.
[16 Dec 2009 8:35] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:42] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:satya.bn@sun.com-20091202140050-nh3ebk6s3bziv8cb) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:48] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[18 Dec 2009 2:06] Paul Dubois
Noted in 5.5.1, 6.0.14 changelogs.
[12 Mar 2010 14:11] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:27] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:41] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[5 May 2010 15:02] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 17:11] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 5:46] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:15] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:43] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 23:23] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[15 Jun 2010 8:08] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:23] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 11:45] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:23] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:10] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)