Synchronizing the model modifies UNSIGNED BIGINT to UNSIGNED BIGINT(19)

Question

Environment:

Ubuntu 14.04
MySql Workbench 6.2.4
MariaDB 10

When I try to synchronize the model with the database schema and I have en a table a defined type UNSIGNED BIGINT (that is UNSIGNED BIGINT(20)) then it becomes UNSIGNED BIGINT(19).

The same process with forward engineering works fine to these data types.

Model

Synchronizing model

CREATE TABLE IF NOT EXISTS `test`.`table` (
  `id` BIGINT(19) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

Forward engineering

CREATE TABLE IF NOT EXISTS `test.`table1` (
`id` BIGINT UNSIGNED NOT NULL,
PRIMARY KEY(`id`))
ENGINE = InnoDB;

Could it be a bug in MySql Workbench?

score 1 · Answer 1 · answered Feb 18 '17 at 21:40

1

Things like (19) on the end of BIGINT (and other INTs) mean nothing. It does not matter whether it is present or absent. Nor does the number matter; that is (20) is OK, too.

All flavors will compile and perform identically.

answered Feb 18 '17 at 21:40

Rick James

135,179
13
127
222

The only difference would be that It could save only 19 digits instead of 20 – Joe Feb 18 '17 at 21:51
No. A `BIGINT` _always_ has room for 64 bits, one of which may be the sign (`SIGNED`, the default, versus `UNSIGNED`). If you also say `ZEROFILL`, then the `(n)` says "pad the output with `0s` to `n` digits. (This is a very rarely used feature.) – Rick James Feb 18 '17 at 21:55
Similarly `INT` always has room for 32 bits (`SIGNED`: +/- 2 billion; `UNSIGNED`: 0 to 4 billion -- usually more than sufficient for an `id`). 32bits = 4bytes, half the size of `BIGINT`. – Rick James Feb 18 '17 at 21:57
I get what you mean, so what would it be the goal of the M here BIGINT[(M)]? https://mariadb.com/kb/en/mariadb/bigint/ Wouldn't it be easier take M out? – Joe Feb 20 '17 at 07:40
1

The _only_ use for `(M)` is if you also say `ZEROFILL`. Otherwise, it has neither benefit, nor harm. `SHOW CREATE TABLE`, and (apparently) your synchronization tool, creates a number if you did not supply one; this adds to the confusion. Removing it is "unnecessary effort" unless it is somehow causing trouble in your sync. – Rick James Feb 20 '17 at 16:48

Joe · Accepted Answer · 2017-02-20T10:56:04.637

The solution I found is to define de type like

UNSIGNED BIGINT(20) --OK

instead of

UNSIGNED BIGINT  --KO

MySql bug

In practice it seems not to affect as we can see in the example Exemple

create table test.biginttest (a BIGINT, b BIGINT(10), c BIGINT(15) ZEROFILL);

INSERT INTO test.biginttest VALUES (-10,10,-10);

INSERT INTO test.biginttest VALUES (9223372036854775808,9223372036854775808,9223372036854775808);

select * from test.biginttest;

Result

Synchronizing the model modifies UNSIGNED BIGINT to UNSIGNED BIGINT(19)

2 Answers2