#1172 Edit Table Problems - ref issue 1169

[Anonymous]

Originally created by: klchi...@gmail.com

Dear Angsar,

>> b) Is not a bug but a valid message, saying you have to
>> either check "Allow NULL" for that column or set a
>> different default value.

Understand, the message,  but it should not prompt it during edting
the table (also the table was empty), right?
  
Also, the same field struct do not have any problem with the "OLD"
table edit style.

Here, here the script, error for 'jmtRnID'

ALTER TABLE `jserrtrxs`
  CHANGE COLUMN `jetRnID` `jetRnID` BIGINT(20) UNSIGNED NOT NULL
AUTO_INCREMENT
FIRST,
  CHANGE COLUMN `jetRnDt` `jetRnDt` DATETIME NULL DEFAULT NULL AFTER
`jetRnID`,
  CHANGE COLUMN `usrUsID` `usrUsID` CHAR(16) NULL DEFAULT NULL COLLATE
latin1_swedish_ci AFTER `jetRnDt`,
  CHANGE COLUMN `jetDaSr` `jetDaSr` CHAR(8) NULL DEFAULT 'M' COMMENT 'Data
Error
Source' COLLATE latin1_swedish_ci AFTER `usrUsID`,
  CHANGE COLUMN `jetJbID` `jetJbID` CHAR(16) NULL DEFAULT NULL
COMMENT 'Job ID'
COLLATE latin1_swedish_ci AFTER `jetDaSr`,
  CHANGE COLUMN `jetDtFr` `jetDtFr` DATETIME NULL DEFAULT NULL AFTER
`jetJbID`,
  CHANGE COLUMN `jetDtTo` `jetDtTo` DATETIME NULL DEFAULT NULL AFTER
`jetDtFr`,
  CHANGE COLUMN `jmtRnID` `jmtRnID` INT(8) UNSIGNED NOT NULL DEFAULT NULL
AFTER
`jetDtTo`,
  CHANGE COLUMN `jetZone` `jetZone` INT(3) UNSIGNED NULL DEFAULT NULL
AFTER
`jmtRnID`,
  CHANGE COLUMN `jmcRnID` `jmcRnID` INT(8) UNSIGNED NOT NULL DEFAULT NULL
AFTER
`jetZone`,
  CHANGE COLUMN `jetLine` `jetLine` INT(3) UNSIGNED NULL DEFAULT NULL
AFTER
`jmcRnID`,
  CHANGE COLUMN `jetProt` `jetProt` TINYINT(2) UNSIGNED NULL DEFAULT '0'
COMMENT 'On
Protocol Flag' AFTER `jetLine`,
  CHANGE COLUMN `jetRems` `jetRems` VARCHAR(512) NULL DEFAULT NULL COLLATE
latin1_swedish_ci AFTER `jetProt`

[Anonymous]

Originally posted by: a...@anse.de

Again here, the error is the result of having "Allow NULL" checked in conjunction
with "Default: NULL". That is not allowed by any MySQL server, an error has to appear
somewhere.

Status: Invalid

[Anonymous]

Originally posted by: arjoc....@gmail.com

Why are you setting the default value?

This query works and the default value for `deslocacao_id` is NULL

ALTER TABLE `repara`
    CHANGE COLUMN `id` `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    CHANGE COLUMN `avaria_id` `avaria_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `id`,
    CHANGE COLUMN `deslocacao_id` `deslocacao_id` INT(10) UNSIGNED NOT NULL AFTER
`avaria_id`

[Anonymous]

Originally posted by: a...@anse.de

> This query works ...

Right, tested, runs withour error.

> ... and the default value for `deslocacao_id` is NULL

Not after the ALTER TABLE statement - look at the column definition after you set NOT
NULL - the default value is converted to an empty string. The server does that
*silently*!

So, in the table editor for such a change, the user gets warned, and the change is
not committed until the user explicitely sets a different default value. That is very
friendly in comparison to a silent changes in a table structure.

[Anonymous]

Originally posted by: klchi...@gmail.com

Hi Angus,

Thx for your reply,

The reason I used "NULL" for default, because I do not want it have any
default value, I want it from the INSERT and EDIT query.  Beside, there
is not other best option, hope you can understand from my point.

BTW, the "default value prompt", this is a bugs, because the prompt
did not fully appear on the screen, pls. try with more then 25 fields or
window height too small.

Regards,
KL Chin

[Anonymous]

Originally posted by: a...@anse.de

> The reason I used "NULL" for default, because I do not want it have any
> default value, I want it from the INSERT and EDIT query.  Beside, there
> is not other best option, hope you can understand from my point.

Well, that's very ok. But you haven't read or understand my last comment - the
changed column no longer has NULL as default if you set it to NOT NULL. Instead, it
is '' / empty string afterwards, automatically.

> did not fully appear on the screen

Yes, that was already reported in issue #1159.