synchronize drops (and then re-adds) json column in mariadb

[tmoens]

Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[ ] latest
[ ] @next
[0.2.13] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

I created and entity with a json column. Here is part of the entity:

@Entity()
export class Sport {

  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({nullable: false})
  name: string;

  // columns skipped

  @Column('json', {
    nullable: true,
    default: '{}',
  })
  lexicon: object;

}

I'll note that after creation, the "lexicon" column in the database is typed longtext.
Whenever synchronize is run, I see:

query: ALTER TABLE `sport` DROP COLUMN `lexicon`
query: ALTER TABLE `sport` ADD `lexicon` json NULL DEFAULT '{}'

Which, of course deletes any data I had in that column.

I'm using MariaDB 10.2.10. In fact I upgraded to this version today specifically because it has support for json columns.

Before today I was just using a varchar column rather than a json column and storing strings - but of course that means converting objects to strings and back.

[JaffParker]

Same issue here. I investigated a bit and found that TypeORM thinks that the column name was changed, even though in the log they look exactly the same.

[vlapo]

I do not see this behaviour on mysql driver so it is mariadb specific. Maybe some difference in information schema.

[JaffParker]

Could it be a difference between mysql and mysql2? I'll try the next time I'm at my laptop. Still seems like a bug more than a behavior specificality...

[JaffParker]

I tried both mysql and mysql2 drivers with both MariaDB and MySQL, there's no difference.

Here's one entity that suffers:

import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm'

@Entity('users')
export class User {
  @PrimaryGeneratedColumn()
  id: string

  @Column({ unique: true })
  email: string

  @Column({ nullable: true })
  password: string

  @Column('simple-json', { name: 'profile' })
  profile: {
    firstName: string
    lastName: string
  }

  @Column({ default: true })
  active: boolean
}

Every time I restart the app with synchronization enabled, here's the log that I get:

columns changed in "users". updating: profile
query: ALTER TABLE `users` DROP COLUMN `profile`
query: ALTER TABLE `users` ADD `profile` text NOT NULL

Interesting thing is that it's not only the JSON columns that are being re-added every time. I have a primary generated uuid column in one table that gets re-added and for some reason a regular string column in another table.

[myemuk]

This bug is still there. I'm using MariaDB 10.3.14 and typeorm 0.2.16

[myemuk]

The reason is that mariadb uses longtext instead. Type "simple-json" works fine.

The solution can be by inserting else if (column.type === "json") { return "longtext"; } to method normalizeType in file MysqlDriver.ts

[Menci]

I think migration from old text type to new json type shouldn't involve column dropping.

[winzaa123]

this is bug in typeorm 0.2.19 too.

[eliaspn]

same here, i encounter the same road-block. The columns are altered on each start. I'm running MariaDb 10.2.27
i confirm that what myemuk said above does the trick, thank you

[diana-ghindaoanu]

Same problem here with typeorm 0.2.22. Any idea when the issue will be fixed?

[hrueger]

Unfortunately, for me this issue exists in 0.2.24...

It's only happening when my entity has a big column length: With 1 000 or 10 000 I don't have this issue, bit with 100 000 I do...

My DB Config:

    Server: localhost via TCP/IP
    Server-Type: MariaDB
    Server-Connection: SSL is not used
    Server-Version: 10.1.38-MariaDB - mariadb.org binary distribution
    Protocoll-Version: 10
    User: root@localhost
    Server-Encoding: UTF-8 Unicode (utf8)

My Entity:

import {
  Column,
  Entity,
  PrimaryColumn,
} from "typeorm";

@Entity()
export class Config {
  @PrimaryColumn()
  public key: string;

  @Column({length: 1000000000})
  public value: string;
}

The log:

[2020-03-05T18:05:09.117Z][QUERY]: SET NAMES utf8mb4;
[2020-03-05T18:05:09.122Z][QUERY]: START TRANSACTION
[2020-03-05T18:05:09.124Z][QUERY]: SELECT DATABASE() AS `db_name`
[2020-03-05T18:05:09.127Z][QUERY]: SELECT * FROM `INFORMATION_SCHEMA`.`TABLES` WHERE (`TABLE_SCHEMA` = 'testagtickets' AND `TABLE_NAME` = 'ticket') OR (`TABLE_SCHEMA` = 'testagtickets' AND `TABLE_NAME` = 'user') OR (`TABLE_SCHEMA` = 'testagtickets' AND `TABLE_NAME` = 'config')
[2020-03-05T18:05:09.129Z][QUERY]: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE (`TABLE_SCHEMA` = 'testagtickets' AND `TABLE_NAME` = 'ticket') OR (`TABLE_SCHEMA` = 'testagtickets' AND `TABLE_NAME` = 'user') OR (`TABLE_SCHEMA` = 'testagtickets' AND `TABLE_NAME` = 'config')
[2020-03-05T18:05:09.131Z][QUERY]: SELECT * FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` WHERE `CONSTRAINT_NAME` = 'PRIMARY' AND ((`TABLE_SCHEMA` = 'testagtickets' AND `TABLE_NAME` = 'ticket') OR (`TABLE_SCHEMA` = 'testagtickets' AND `TABLE_NAME` = 'user') OR (`TABLE_SCHEMA` = 'testagtickets' AND `TABLE_NAME` = 'config'))
[2020-03-05T18:05:09.133Z][QUERY]: SELECT `SCHEMA_NAME`, `DEFAULT_CHARACTER_SET_NAME` as `CHARSET`, `DEFAULT_COLLATION_NAME` AS `COLLATION` FROM `INFORMATION_SCHEMA`.`SCHEMATA`
[2020-03-05T18:05:09.134Z][QUERY]: SELECT `s`.* FROM `INFORMATION_SCHEMA`.`STATISTICS` `s` LEFT JOIN `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS` `rc` ON `s`.`INDEX_NAME` = `rc`.`CONSTRAINT_NAME` WHERE ((`s`.`TABLE_SCHEMA` = 'testagtickets' AND `s`.`TABLE_NAME` = 'ticket') OR (`s`.`TABLE_SCHEMA` = 'testagtickets' AND `s`.`TABLE_NAME` = 'user') OR (`s`.`TABLE_SCHEMA` = 'testagtickets' AND `s`.`TABLE_NAME` = 'config')) AND `s`.`INDEX_NAME` != 'PRIMARY' AND `rc`.`CONSTRAINT_NAME` IS NULL
[2020-03-05T18:05:09.136Z][QUERY]: SELECT `kcu`.`TABLE_SCHEMA`, `kcu`.`TABLE_NAME`, `kcu`.`CONSTRAINT_NAME`, `kcu`.`COLUMN_NAME`, `kcu`.`REFERENCED_TABLE_SCHEMA`, `kcu`.`REFERENCED_TABLE_NAME`, `kcu`.`REFERENCED_COLUMN_NAME`, `rc`.`DELETE_RULE` `ON_DELETE`, `rc`.`UPDATE_RULE` `ON_UPDATE` FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `kcu` INNER JOIN `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS` `rc` ON `rc`.`constraint_name` = `kcu`.`constraint_name` WHERE (`kcu`.`TABLE_SCHEMA` = 'testagtickets' AND `kcu`.`TABLE_NAME` = 'ticket') OR (`kcu`.`TABLE_SCHEMA` = 'testagtickets' AND `kcu`.`TABLE_NAME` = 'user') OR (`kcu`.`TABLE_SCHEMA` = 'testagtickets' AND `kcu`.`TABLE_NAME` = 'config')
[2020-03-05T18:05:09.552Z][QUERY]: SELECT VERSION() AS `version`
[2020-03-05T18:05:09.556Z][QUERY]: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'testagtickets' AND `TABLE_NAME` = 'typeorm_metadata'
[2020-03-05T18:05:09.569Z]renaming column "value" in to "value"
[2020-03-05T18:05:09.573Z]columns changed in "config". updating: value
[2020-03-05T18:05:09.576Z][QUERY]: ALTER TABLE `config` DROP COLUMN `value`
[2020-03-05T18:05:09.661Z][QUERY]: ALTER TABLE `config` ADD `value` varchar(1000000) NOT NULL
[2020-03-05T18:05:09.786Z][QUERY]: COMMIT
[2020-03-05T18:05:09.790Z][QUERY]: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'testagtickets' AND `TABLE_NAME` = 'migrations'
[2020-03-05T18:05:09.806Z][QUERY]: SELECT * FROM `testagtickets`.`migrations` `migrations` ORDER BY `id` DESC
[2020-03-05T18:05:09.810Z]No migrations are pending

As you can see, it's renaming value to value, then dropping the column and readding it.

[Menci]

@hrueger Seems your problem is not same as this one (about json columns). Maybe open a new issue?

[aykutcan]

Any update on that ?

my json columns drops everytime i sync db.

[STR4NG3R]

Use simple-json column data type to avoid that

[thefat32]

Doing that you loose CHECK (JSON_VALID) constraint

[catt-stefano]

I used to have this problem until I realized that while I was using and connecting to a mariadb, I had mysql set as the type in the TypeOrmModuleOptions.
Once I changed it to mariadb it was no longer doing all those nasty column drops

[SloCompTech]

Thanks @catt-stefano, your solution works.

[JesperBalslev]

@catt-stefano This helped me. Finally no more DROP/ALTER table crap! You deserve a cookie.

[Korhm]

@catt-stefano Thank you, this also solved the behavior on my side :)