New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MariaDb VIRTUAL columns don't accept [NULL | NOT NULL] #2691

Open
StrikeForceZero opened this Issue Aug 21, 2018 · 0 comments

Comments

Projects
None yet
1 participant
@StrikeForceZero

StrikeForceZero commented Aug 21, 2018

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
[x] 0.2.7 (or put your version here)

Steps to reproduce or a small repository showing the problem:

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

@Entity()
export class Foo {
    @PrimaryColumn()
    public id: number;

    @Column({
        name: 'ipv4_bin',
        type: 'varbinary',
        length: 16,
    })
    public ipv4_bin: string;

    @Column({
        name: 'ipv4_text',
        type: 'varbinary',
        length: 16,
        asExpression: 'INET_NTOA(ipv4_bin)',
        generatedType: 'VIRTUAL',
    })
    public readonly ipv4_text: string;
}

This more or less creates this schema:

CREATE TABLE `foo` (
  `id`        int                                            NOT NULL,
  `ipv4_bin`  varbinary(16)                                  NOT NULL,
  `ipv4_text` varbinary(16) AS (INET_NTOA(ipv4_bin)) VIRTUAL NOT NULL,
  PRIMARY KEY (`id`)
)

The issue is https://github.com/typeorm/typeorm/blob/master/src/driver/mysql/MysqlQueryRunner.ts#L1500-L1503
adds the [NULL | NOT NULL] on a virtual column, which is fine in MySQL 5.7, but apparently not in mariaDB... I can't find anything hinting this in their documentation. Only Errors and articles saying so.

https://mariadb.com/kb/en/library/generated-columns/

https://www.percona.com/blog/2016/03/04/virtual-columns-in-mysql-and-mariadb/

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, PRIMARY KEY (`id`)'

Adding a check to the lines I referred to above

if (!(isMariaDb && column.asExpression) && !column.isNullable)
            c += " NOT NULL";
if (!(isMariaDb && column.asExpression) && column.isNullable)
            c += " NULL";

seems to work fine.

While the handling of VIRTUAL [NULL | NOT NULL] should probably need to be confirmed by mariaDb and documented, its current iteration doesn't allow it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment