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) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Administrator Severity:S1 (Critical)
Version:1.0.10 OS:
Assigned to: 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 :)