phalcon / migrations

Generate or migrate database changes via migrations.
https://docs.phalcon.io/latest/en/db-migrations
BSD 3-Clause "New" or "Revised" License
28 stars 23 forks source link

Wrong column size for numeric columns in MySQL #139

Open wurst-hans opened 1 year ago

wurst-hans commented 1 year ago

Using Phalcon Migrations 2.2.4 with MySQL and MariaDB (current v8 releases).

When creating a dump, the migration files for tables using numeric columns (i.e. TYPE_TINYINT, TYPE_INT, TYPE_BIGINT) are created using size=1. Ex.:

new Column(
    'id',
    [
        'type' => Column::TYPE_BIGINTEGER,
        'notNull' => true,
        'autoIncrement' => true,
        'size' => 1,
        'first' => true
    ]
),

Which results in creating the tables like

CREATE TABLE `sessions` (
  `id` bigint(1) NOT NULL,
  `session_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `user_id` int(1) DEFAULT NULL,
  `data` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
  `timeout` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

But default "column size" in MySQL is 4 for TINYINT, 11 for INT and 20 for BIGINT. I know, that this is not the real internal column definition but the display representation only. But this confuses me a lot, when opening database using PhpMyAdmin and I see tons of columns having definition INT(1).