enum foreign key

[Ali1Ammar]

Issue type:

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

Database system/driver:

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

TypeORM version:

[ ] latest
[ ] @next
[x ] 0.2.24

Steps to reproduce or a small repository showing the problem:

import { Entity, PrimaryColumn, ManyToMany, JoinTable, Column } from 'typeorm';
enum Symbol {c,d,a,}
@Entity()
export class A {
  @PrimaryColumn({ length: 50 })
  name: string;
  
  @PrimaryColumn({  type: 'enum', enum: Symbol})
  material: Symbol;

  @ManyToMany( type => B, qa => qa.a,)
  @JoinTable()
  b: B[];
}

@Entity()
export class B {
  @PrimaryColumn({ length: 50 })
  about: string;

  @ManyToMany( type => A,  qa => qa.b,
  )
  a: A[];
}

Error

  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL, `bAbout` varchar(50) NOT NULL, INDEX `IDX_dcaf77d75a3093e449f73035c6` ' at line 1",
  sqlState: '42000',
  index: 0,
  sql: 'CREATE TABLE `a_b_b` (`aName` varchar(50) NOT NULL, `aMaterial` enum NOT NULL, `bAbout` varchar(50) NOT NULL, INDEX `IDX_dcaf77d75a3093e449f73035c6` (`aName`, `aMaterial`), INDEX `IDX_6ee8e3e97655a7735447846d8b` (`bAbout`), PRIMARY KEY (`aName`, `aMaterial`, `bAbout`)) ENGINE=InnoDB'
}
query: ROLLBACK
[Nest] 27228   - 05/12/2020, 1:54:20 AM   [TypeOrmModule] Unable to connect to the database. Retrying (1)... +1197ms
QueryFailedError: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL, `bAbout` varchar(50) NOT NULL, INDEX `IDX_dcaf77d75a3093e449f73035c6` ' at line 1`

the problem here

aMaterial enum NOT NULL`

this should have value of enum like that

aMaterial enum ('0', '1', '2') NOT NULL

all log

[Nest] 27228   - 05/12/2020, 1:54:18 AM   [NestFactory] Starting Nest application...
[Nest] 27228   - 05/12/2020, 1:54:19 AM   [InstanceLoader] TypeOrmModule dependencies initialized +99ms
[Nest] 27228   - 05/12/2020, 1:54:19 AM   [InstanceLoader] JwtModule dependencies initialized +0ms
[Nest] 27228   - 05/12/2020, 1:54:19 AM   [InstanceLoader] AppModule dependencies initialized +1ms
query: START TRANSACTION
query: SELECT DATABASE() AS `db_name`
query: SELECT * FROM `INFORMATION_SCHEMA`.`TABLES` WHERE (`TABLE_SCHEMA` = 'learnap' AND `TABLE_NAME` = 'a') OR (`TABLE_SCHEMA` = 'learnap' AND `TABLE_NAME` = 'b') OR (`TABLE_SCHEMA` = 'learnap' AND `TABLE_NAME` = 'a_b_b')
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE (`TABLE_SCHEMA` = 'learnap' AND `TABLE_NAME` = 'a') OR (`TABLE_SCHEMA` = 'learnap' AND `TABLE_NAME` = 'b') OR (`TABLE_SCHEMA` = 'learnap' AND `TABLE_NAME` = 'a_b_b')
query: SELECT * FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` WHERE `CONSTRAINT_NAME` = 'PRIMARY' AND ((`TABLE_SCHEMA` = 'learnap' AND `TABLE_NAME` = 'a') OR (`TABLE_SCHEMA` = 'learnap' AND `TABLE_NAME` = 'b') OR (`TABLE_SCHEMA` = 'learnap' AND `TABLE_NAME` = 'a_b_b'))
query: SELECT `SCHEMA_NAME`, `DEFAULT_CHARACTER_SET_NAME` as `CHARSET`, `DEFAULT_COLLATION_NAME` AS `COLLATION` FROM `INFORMATION_SCHEMA`.`SCHEMATA`
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` = 'learnap' AND `s`.`TABLE_NAME` = 'a') OR (`s`.`TABLE_SCHEMA` = 'learnap' AND `s`.`TABLE_NAME` = 'b') OR (`s`.`TABLE_SCHEMA` = 'learnap' AND `s`.`TABLE_NAME` = 'a_b_b')) AND `s`.`INDEX_NAME` != 'PRIMARY' AND `rc`.`CONSTRAINT_NAME` IS NULL
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` = 'learnap' AND `kcu`.`TABLE_NAME` = 'a') OR (`kcu`.`TABLE_SCHEMA` = 'learnap' AND `kcu`.`TABLE_NAME` = 'b') OR (`kcu`.`TABLE_SCHEMA` = 'learnap' AND `kcu`.`TABLE_NAME` = 'a_b_b')
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'learnap' AND `TABLE_NAME` = 'typeorm_metadata'
query: CREATE TABLE `a` (`name` varchar(50) NOT NULL, `material` enum ('0', '1', '2') NOT NULL, PRIMARY KEY (`name`, `material`)) ENGINE=InnoDB
query: CREATE TABLE `b` (`about` varchar(50) NOT NULL, PRIMARY KEY (`about`)) ENGINE=InnoDB
query: CREATE TABLE `a_b_b` (`aName` varchar(50) NOT NULL, `aMaterial` enum NOT NULL, `bAbout` varchar(50) NOT NULL, INDEX `IDX_dcaf77d75a3093e449f73035c6` (`aName`, `aMaterial`), INDEX `IDX_6ee8e3e97655a7735447846d8b` (`bAbout`), PRIMARY KEY (`aName`, `aMaterial`, `bAbout`)) ENGINE=InnoDB
query failed: CREATE TABLE `a_b_b` (`aName` varchar(50) NOT NULL, `aMaterial` enum NOT NULL, `bAbout` varchar(50) NOT NULL, INDEX `IDX_dcaf77d75a3093e449f73035c6` (`aName`, `aMaterial`), INDEX `IDX_6ee8e3e97655a7735447846d8b` (`bAbout`), PRIMARY KEY (`aName`, `aMaterial`, `bAbout`)) ENGINE=InnoDB
error: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL, `bAbout` varchar(50) NOT NULL, INDEX `IDX_dcaf77d75a3093e449f73035c6` ' at line 1
    at Query.Sequence._packetToError (/home/aliammar/Desktop/learnnest/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/home/aliammar/Desktop/learnnest/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/home/aliammar/Desktop/learnnest/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/aliammar/Desktop/learnnest/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/aliammar/Desktop/learnnest/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/home/aliammar/Desktop/learnnest/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/aliammar/Desktop/learnnest/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/home/aliammar/Desktop/learnnest/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:315:20)
    at addChunk (_stream_readable.js:297:12)
    --------------------
    at Protocol._enqueue (/home/aliammar/Desktop/learnnest/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at PoolConnection.query (/home/aliammar/Desktop/learnnest/node_modules/mysql/lib/Connection.js:198:25)
    at MysqlQueryRunner.<anonymous> (/home/aliammar/Desktop/learnnest/node_modules/typeorm/driver/mysql/MysqlQueryRunner.js:161:44)
    at step (/home/aliammar/Desktop/learnnest/node_modules/tslib/tslib.js:139:27)
    at Object.next (/home/aliammar/Desktop/learnnest/node_modules/tslib/tslib.js:120:57)
    at fulfilled (/home/aliammar/Desktop/learnnest/node_modules/tslib/tslib.js:110:62)
    at processTicksAndRejections (internal/process/task_queues.js:97:5) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL, `bAbout` varchar(50) NOT NULL, INDEX `IDX_dcaf77d75a3093e449f73035c6` ' at line 1",
  sqlState: '42000',
  index: 0,
  sql: 'CREATE TABLE `a_b_b` (`aName` varchar(50) NOT NULL, `aMaterial` enum NOT NULL, `bAbout` varchar(50) NOT NULL, INDEX `IDX_dcaf77d75a3093e449f73035c6` (`aName`, `aMaterial`), INDEX `IDX_6ee8e3e97655a7735447846d8b` (`bAbout`), PRIMARY KEY (`aName`, `aMaterial`, `bAbout`)) ENGINE=InnoDB'
}
query: ROLLBACK

[Ali1Ammar]

@Entity()
export class A {
  @PrimaryColumn({ length: 50 })
  name: string;
  
  @PrimaryColumn({  type: 'enum', enum: Symbol})
  material: Symbol;

  @OneToMany( type => B, qa => qa.a,)

  b: B[];
}

@Entity()
export class B {
  @PrimaryColumn({ length: 50 })
  about: string;

  @ManyToOne( type => A,  qa => qa.b,
  )
  a: A;
}

same problem with manyToOne

[BTOdell]

Try renaming your enum to something other than Symbol. It might be conflicting with the global built-in Symbol type: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Symbol

[Ali1Ammar]

i change it same error

import { Entity, PrimaryColumn, ManyToMany, JoinTable, Column, ManyToOne, OneToMany } from 'typeorm';
enum TheSomeSpecialName {c,d,a,}
@Entity()
export class A {
  @PrimaryColumn({ length: 50 })
  name: string;
  
  @PrimaryColumn({  type: 'enum', enum: TheSomeSpecialName})
  material: TheSomeSpecialName;

  @OneToMany( type => B, qa => qa.a,)

  b: B[];
}

@Entity()
export class B {
  @PrimaryColumn({ length: 50 })
  about: string;

  @ManyToOne( type => A,  qa => qa.b,
  )
  a: A;
}

  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL, PRIMARY KEY (`about`)) ENGINE=InnoDB' at line 1",
  sqlState: '42000',
  index: 0,
  sql: 'CREATE TABLE `b` (`about` varchar(50) NOT NULL, `aName` varchar(50) NULL, `aMaterial` enum NULL, PRIMARY KEY (`about`)) ENGINE=InnoDB'
}

[Ali1Ammar]

any update?

[koheiio]

I have the same problem - Typeorm 0.2.30, MySQL

@Entity()
export class Employee extends BaseEntity {
  @PrimaryColumn({ type: 'enum', enum: Providers })
  provider!: Providers
  
  @OneToMany(() => AccessEvent, (accessEvent) => accessEvent.employee)
  accessEvents!: AccessEvent[]
}

@Entity()
export class AccessEvent extends BaseEntity {
  @PrimaryColumn({ type: 'varchar', length: 128 })
  id!: string

  @ManyToOne(() => Employee, (employee) => employee.accessEvents)
  employee!: Employee
}

Generating a migration from this results in it trying to do

`ALTER TABLE `access_event` ADD `employeeProvider` enum NULL`

It somehow is unable to retrieve the enum values. If I change Employee's primary column to @PrimaryColumn({ type: 'varchar', length: 128 }) it generates a migration with the correct typing on the column, but not for enum.

[dzcpy]

Any solutions?