Bug #23613 Adminsitrator creates invalid SQL if tables contains ANY numeric fields

• Submitted: 25 Oct 2006 4:56
• Modified: 25 Oct 2006 23:36
• Reporter: Daniel Kasak (Candidate Quality Contributor)
• Status: Closed
• Category: MySQL Administrator
• Severity: S1 (Critical)
• Version: 1.0.10
• CPU Architecture: Any

[25 Oct 2006 4:56] Daniel Kasak

Description:
MySQL administrator is creating invalid SQL when creating / editing tables that contain numeric fields.

How to repeat:
Create a table and include a numeric field. Here's an example of the SQL generated for me by MySQL administrator:

CREATE TABLE `NUS`.`ArchiveBoxes` (
  `BoxID` SMALLINT(null) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `MyStamp` timestamp(14)  NOT NULL,
  `ArchiveDate` DATE(null)  NOT NULL,
  `DestoyDate` DATE(null) ,
  `ArchiveTypeID` TINYINT(null) UNSIGNED NOT NULL,
  `Contents` TEXT(null)  NOT NULL,
  `Location` varchar(255)  NOT NULL,
  `GridRef` char(3)  NOT NULL
);

See the (null) after the column type? Here's what it should be:

CREATE TABLE `NUS`.`ArchiveBoxes` (
  `BoxID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `MyStamp` timestamp(14)  NOT NULL,
  `ArchiveDate` DATE  NOT NULL,
  `DestoyDate` DATE ,
  `ArchiveTypeID` TINYINT UNSIGNED NOT NULL,
  `Contents` TEXT  NOT NULL,
  `Location` varchar(255)  NOT NULL,
  `GridRef` char(3)  NOT NULL
);

Simple enough to change in the 'confirm table creation' dialog that has a textview that allows you to hand-edit the SQL? Wrong. It seems administrator is now ignoring any changes that you do here, and using it's original SQL.

Suggested fix:
Don't put the string:

(null)

after the column type of numeric fields.

[25 Oct 2006 6:18] Valeriy Kravchuk

Thank you for a problem report. Please, try to repeat with a newer version, 1.2.4 rc (from GUI Tools 5.0r4), and inform about the results.

[25 Oct 2006 23:36] Daniel Kasak

It's fixed. Interesting. I wasn't aware there were any updates to Administrator until I found it in the 'GUI Tools' package. Sorry :)