Skip to content
Snippets Groups Projects
user avatar
SakuragiYoshimasa authored
* Correct type for `report_card.dataset`

This was missed in the original migration of boolean types to
${boolean.type} in MySQL/MariaDB, and then missed again by me when I
migrated `collection_preview` over a week ago.

* Change all boolean types to `bit(1)` in MySQL

Liquibase changed their boolean type in MySQL from `bit(1)` to
`tinyint(4)` in version 4.25.1. Our JDBC driver does not recognize these
as booleans, so we needed to migrate them to `bit(1)`s.

As discussed [here](#36964), we
changed all existing `boolean` types that were in the
`001_update_migrations.yml` but not the SQL initialization file.

For new installations, this works: things in the SQL initialization file
get created with the `bit(1)` type.

However, for existing installations, there's a potential issue. Say I'm
on v42 and am upgrading to v49. In v43, a new `boolean` was added.

In this case, I'll get the `boolean` from the liquibase migration rather
than from the SQL initialization file, and it need to be changed to a
`bit(1)`.

I installed Metabase v41 with MySQL, migrated the database, and then
installed Metabase v49 and migrated again. I made a list of all the
columns that had the type `tinyint`:

```
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE,        COLUMN_DEFAULT, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_TYPE = 'tinyint' AND TABLE_SCHEMA='metabase_test';
+---------------+------------------------------+-------------------+-------------+----------------+-------------+
| TABLE_SCHEMA  | TABLE_NAME                   | COLUMN_NAME       | COLUMN_TYPE | COLUMN_DEFAULT | IS_NULLABLE |
+---------------+------------------------------+-------------------+-------------+----------------+-------------+
| metabase_test | core_user                    | is_datasetnewb    | tinyint     | 1              | NO          |
| metabase_test | metabase_field               | database_required | tinyint     | 0              | NO          |
| metabase_test | metabase_fieldvalues         | has_more_values   | tinyint     | 0              | YES         |
| metabase_test | permissions_group_membership | is_group_manager  | tinyint     | 0              | NO          |
| metabase_test | persisted_info               | active            | tinyint     | 0              | NO          |
| metabase_test | report_card                  | dataset           | tinyint     | 0              | NO          |
| metabase_test | timeline                     | archived          | tinyint     | 0              | NO          |
| metabase_test | timeline                     | default           | tinyint     | 0              | NO          |
| metabase_test | timeline_event               | archived          | tinyint     | 0              | NO          |
| metabase_test | timeline_event               | time_matters      | tinyint     | NULL           | NO          |
+---------------+------------------------------+-------------------+-------------+----------------+-------------+
10 rows in set (0.01 sec)
```

Then wrote migrations. For each column, we:

- turn it into a `bit(1)`,

- re-set the previously existing default value, and

- re-add the NOT NULL constraint, if applicable.

* Change author and add missing `dbms`

---------

Co-authored-by: default avatarJohn Swanson <john.swanson@metabase.com>
49b126ef
History
user avatar 49b126ef
Name Last commit Last update