Prisma: Introspection: Cannot read property 'substring' of undefined [MySQL 8]

[Innomalist]

Hello,
Great project just wanted to let you know there could be an issue on Introspection module. Here is the error i have received when trying to do so:

```
Introspecting MySQL schema with 26 tables.
ERROR Introspection failed:
Cannot read property 'substring' of undefined

[Innomalist]

BTW i have also tried with a much smaller database. Same result

[pantharshit00]

@Innomalist

Can you please share your datamodel? Also, we are currently porting our introspection logic from typescript to Rust. We just need to use the updated code now(follow https://github.com/prisma/prisma2/issues/510)

So please share your datamodel so that we can try to reproduce this 🙏

[Innomalist]

Hello @pantharshit00,
Thanks for response. Sure but just to be sure i got the feature correct: This feature is supposed to read a database model from an already created database and create prisma model from it am i correct?
I am supposed to share sql model of database right?

[pantharshit00]

Yes, if introspection is failing completely, just share the schema of your database so that we can try a reproduction.

[AhmedElywa]

whmcs.pdf
you can try with this schema i have same error

[AhmedElywa]

see the error

[AhmedElywa]

Hello i try to use database with only one table in one time to know where the error in
i get error in table have

status` enum('Active','Inactive','Closed') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',

so the problem in enum type

[janpio]

Can you please share the exact steps you take in the CLI init flow?

I tried to create a database with a table with an enum as you mentioned, but could introspect and create the data model just fine - so probably we went a slighty different path during the wizard somewhere:

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               5.7.27-log - MySQL Community Server (GPL)
-- Server OS:                    Win64
-- HeidiSQL Version:             10.2.0.5599
-- --------------------------------------------------------

CREATE DATABASE IF NOT EXISTS `foo`

CREATE TABLE IF NOT EXISTS `bar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `baz` enum('A','B','C') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'A',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `bar` (`id`, `baz`) VALUES
    (1, 'B'),
    (2, 'A');

generator photon {
  provider = "photonjs"
}

datasource db {
  provider = "mysql"
  url      = "mysql://root:root@localhost:3306/"
}

model Bar {
  id  Int        @id
  baz BarBazEnum

  @@map("bar")
}

enum BarBazEnum {
  A
  B
  C
}

What is your prisma -v?
What version of MySQL are you using?
Local or remote server?

[AhmedElywa]

@janpio

➜  $ mysql -V
mysql  Ver 8.0.17 for osx10.14 on x86_64 (Homebrew)
➜  $ prisma2 -v
[email protected], binary version: 408c0c08746064a56c8ba7cb167bad49f1d2c2f7

in my local MacBook

[AhmedElywa]

I tried with your table

CREATE TABLE IF NOT EXISTS `bar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `baz` enum('A','B','C') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'A',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `bar` (`id`, `baz`) VALUES
    (1, 'B'),
    (2, 'A');

Same error
⠋ Introspecting MySQL schema with 1 tables.
ERROR Introspection failed:
Cannot read property 'substring' of undefined

[janpio]

Interesting, Prisma 2 currently does not really support MySQL 8: https://github.com/prisma/prisma2/issues/384 But as you are not getting this error, it means that in your case the user and database _do_ work and this might be a follow up error.

Here is some background on how that might be possible that it does work for your with MySQL 8: https://github.com/prisma/prisma2/issues/38#issuecomment-532176320 Can you maybe check that, this would enable us to maybe reproduce.

@Innomalist were you also testing with MySQL 8?

[AhmedElywa]

error in #384 i know how you solve it

[Innomalist]

@janpio Yes I was testing in mysql 8 too but the thing is the initial schemes I was receiving was for clients so I couldn't share them publicly so I tried to reproduce the error with a test scheme but same error doesn't happens (another error happened in later stages).
So maybe mysql 8 by nature is not the problem I don't know

[janpio]

Even with the workaround applied to make Prisma 2 work with MySQL 8 I can not reproduce the Cannot read property 'substring' of undefined error (but get https://github.com/prisma/prisma2/issues/546 before even being able to select a database/schema which is an unrelated thing).

Something else is going on here :/

[divyenduz]

@AhmedElywa Can you still reproduce this? If yes, please cmd + click the line number just below the substring error. That should open the text editor and point you to the exact line where it is failing. That might give us more information to proceed.

[Innomalist]

@divyenduz In my case the line doesn't have reference to anywhere so I would be able to click on it.

[Innomalist]

With this simple database scheme this error can be reproduced:

create database prism;

use prism;

create table customer (
  id int primary key auto_increment,
  name varchar(100),
  type enum('regular', 'vip') default 'regular'
);

create table `order` (
  id int primary key auto_increment,
  customer_id int,
  foreign key (customer_id) references customer(id),
  item varchar(100)
)

[Innomalist]

I can also confirm by removing enum field in customer table this error will be gone.

BTW: I am not sure about it but I am hopeful Prisma supports all mysql fields such as GEOMETRY fields too. I mean enum is a basic one of course.

[pantharshit00]

@Innomalist

I am unable to reproduce this. The database was introspected correctly with [email protected], binary version: 237b46b97773f5018e336550e4285e2a78c38483

generator photon {
  provider = "photonjs"
}

datasource db {
  provider = "mysql"
  url      = "mysql://<REDACTED>@localhost:3306/"
}

model Customer {
  id    Int               @id
  name  String?
  order Order[]
  type  CustomerTypeEnum?

  @@map("customer")
}

model Order {
  id       Int       @id
  customer Customer? @map("customer_id")
  item     String?

  @@map("order")
}

enum CustomerTypeEnum {
  regular
  vip
}

Can you please make sure you are on the latest version?

[Innomalist]

@pantharshit00 Thanks for response,
I am using npx so yes I think I am on latest version. Maybe this is related to having mysql version 8?
Also I am using init command not introspect. If I pass database name during init the aforementioned error occurs if I let it be empty when Prisma tries to list my databases it crashes with https://github.com/prisma/prisma2/issues/546

[pantharshit00]

Can you please tell me the output of mysqld --version instead of mysql client version? Just to be sure here.

[Innomalist]

@pantharshit00 Sadly i have removed mysql 8 in favor of mysql 5.7 to be able to use prisma (I couldn't :( ) so i can't check that. Maybe other users with same problem could tell that

[pantharshit00]

Were you able to use Prisma with MySQL 5.7. Your comment didn't make that clear enough

[Innomalist]

@pantharshit00 With mysql 5.7 this issue was gone but there was another issue I was facing on a later stage and it led to not being able to generate model files. As it was unrelated to this issue I didn't mentioned it in previous comment.

[pantharshit00]

I can confirm this bug with a Digitalocean MySQL 8 instance.

This might get automatically fixed once we use the introspection engine written in Rust instead of the one written in Typescript

[janpio]

Please coordinate with @do4gr that a schema that triggers this is added as a test case for Introspection @pantharshit00 - so we actually know we are good there.

[divyenduz]

We rewrote introspection recently. Can you please see if this issue still exists in the latest alpha or preview?

[pantharshit00]

Unable to reproduce this now and I was even able to write data to the database without any issue. Closing, please comment here or open a new issue if you are still facing this.