Prisma1: Prisma CLI is writing `schema` property in docker-compose file instead of `database` property for MySQL

[BudgieInWA]

After running prisma init on an existing MySQL database (and successfully introspecting) the prisma server is unable to use to the database.

? Set up a new Prisma server or deploy to an existing server? Use existing database
? What kind of database do you want to deploy to? MySQL
? Does your database contain existing data? Yes
? Enter database host 172.17.0.1
? Enter database port 3306
? Enter database user api_readonly
? Enter database password [hidden]
? Please select the schema you want to introspect my_db
Introspecting database my_db 41.9s
Created datamodel definition based on 65 tables.
? Select the programming language for the generated Prisma client Prisma JavaScript Client

Created 3 new files:                                                                          

  prisma.yml           Prisma service definition
  datamodel.prisma    GraphQL SDL-based datamodel (foundation for database)
  docker-compose.yml   Docker configuration file

Next steps:

  1. Open folder: cd hello-world
  2. Start your Prisma server: docker-compose up -d
  3. Deploy your Prisma service: prisma deploy
  4. Read more about introspection:
     http://bit.ly/prisma-introspection

datamodel.prisma contains a sensible looking schema based on the tables in my_db. docker-compose.yml looks like:

version: '3'
services:
  prisma:
    image: prismagraphql/prisma:1.31
    restart: always
    ports:
    - "4466:4466"
    environment:
      PRISMA_CONFIG: |
        port: 4466
        # uncomment the next line and provide the env var PRISMA_MANAGEMENT_API_SECRET=my-secret to activate cluster security
        # managementApiSecret: my-secret
        databases:
          default:
            connector: mysql
            host: 172.17.0.1
            schema: my_db
            user: api_readonly
            password: blah
            rawAccess: true
            port: '3306'
            migrations: false

Trying to start the server with docker-compose up results in:

Recreating helloworld_prisma_1 ... 
Recreating helloworld_prisma_1 ... done
Attaching to helloworld_prisma_1
prisma_1  | Exception in thread "main" java.lang.RuntimeException: Unable to load Prisma config: com.prisma.config.InvalidConfiguration: Only Postgres connectors specify a schema. If they do they also need to specify a database. Other connectors only specify a database.
prisma_1  |     at scala.sys.package$.error(package.scala:26)
prisma_1  |     at com.prisma.config.ConfigLoader$.load(ConfigLoader.scala:40)
prisma_1  |     at com.prisma.local.PrismaLocalDependencies.<init>(PrismaLocalDependencies.scala:48)
prisma_1  |     at com.prisma.local.PrismaLocalMain$.delayedEndpoint$com$prisma$local$PrismaLocalMain$1(PrismaLocalMain.scala:13)
prisma_1  |     at com.prisma.local.PrismaLocalMain$delayedInit$body.apply(PrismaLocalMain.scala:8)
prisma_1  |     at scala.Function0.apply$mcV$sp(Function0.scala:34)
prisma_1  |     at scala.Function0.apply$mcV$sp$(Function0.scala:34)
prisma_1  |     at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)
prisma_1  |     at scala.App.$anonfun$main$1$adapted(App.scala:76)
prisma_1  |     at scala.collection.immutable.List.foreach(List.scala:388)
prisma_1  |     at scala.App.main(App.scala:76)
prisma_1  |     at scala.App.main$(App.scala:74)
prisma_1  |     at com.prisma.local.PrismaLocalMain$.main(PrismaLocalMain.scala:8)
prisma_1  |     at com.prisma.local.PrismaLocalMain.main(PrismaLocalMain.scala)
helloworld_prisma_1 exited with code 1

I tried specifying database instead of schema:

-            schema: my_db
+            database: my_db

which got me further:

Recreating helloworld_prisma_1 ... 
Recreating helloworld_prisma_1 ... done
Attaching to helloworld_prisma_1
prisma_1  | No log level set, defaulting to INFO.
prisma_1  | [INFO] {} - Started.
prisma_1  | [INFO] {} - Started.
prisma_1  | Exception in thread "main" java.sql.SQLSyntaxErrorException: (conn=791126) Access denied for user 'api_readonly'@'%' to database 'prisma'
prisma_1  |     at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:163)
prisma_1  |     at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:106)
prisma_1  |     at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:235)
prisma_1  |     at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:224)
prisma_1  |     at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:159)
prisma_1  |     at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
prisma_1  |     at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
prisma_1  |     at slick.jdbc.StatementInvoker.results(StatementInvoker.scala:38)
prisma_1  |     at slick.jdbc.StatementInvoker.iteratorTo(StatementInvoker.scala:21)
prisma_1  |     at slick.jdbc.Invoker.first(Invoker.scala:30)
prisma_1  |     at slick.jdbc.Invoker.first$(Invoker.scala:29)
prisma_1  |     at slick.jdbc.StatementInvoker.first(StatementInvoker.scala:15)
prisma_1  |     at slick.jdbc.StreamingInvokerAction$HeadAction.run(StreamingInvokerAction.scala:52)
prisma_1  |     at slick.jdbc.StreamingInvokerAction$HeadAction.run(StreamingInvokerAction.scala:51)
prisma_1  |     at slick.dbio.DBIOAction$$anon$4.$anonfun$run$3(DBIOAction.scala:239)
prisma_1  |     at scala.collection.Iterator.foreach(Iterator.scala:937)
prisma_1  |     at scala.collection.Iterator.foreach$(Iterator.scala:937)
prisma_1  |     at scala.collection.AbstractIterator.foreach(Iterator.scala:1425)
prisma_1  |     at scala.collection.IterableLike.foreach(IterableLike.scala:70)
prisma_1  |     at scala.collection.IterableLike.foreach$(IterableLike.scala:69)
prisma_1  |     at scala.collection.AbstractIterable.foreach(Iterable.scala:54)
prisma_1  |     at slick.dbio.DBIOAction$$anon$4.run(DBIOAction.scala:239)
prisma_1  |     at slick.dbio.DBIOAction$$anon$4.run(DBIOAction.scala:237)
prisma_1  |     at slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:275)
prisma_1  |     at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:275)
prisma_1  |     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
prisma_1  |     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
prisma_1  |     at java.lang.Thread.run(Thread.java:748)
prisma_1  | Caused by: java.sql.SQLException: Access denied for user 'api_readonly'@'%' to database 'prisma'
prisma_1  | Query is: CREATE SCHEMA IF NOT EXISTS `prisma` DEFAULT CHARACTER SET latin1;
prisma_1  |     at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:146)
prisma_1  |     at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:217)
prisma_1  |     at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:218)
prisma_1  |     ... 24 more
helloworld_prisma_1 exited with code 1

I expect to be able to specify the database that the connector uses by setting the database or schema key of the connector config.

I am using Prisma CLI version prisma/1.31.1 (linux-x64) node-v8.9.1 on Ubuntu 18.04, and the same behaviour was observed on osX.

[whileoneloop]

It turns out the user in the connection config needs permission to create and alter database objects in a 'prisma' db (regardless of the database property value in the config).

[BudgieInWA]

In that case, the only problem is that prisma init generates a config that uses schema instead of database for the mysql connector.

[pantharshit00]

Yes, Prisma requires access to the prisma database(management schema in case of pg) to store metadata about the applied migrations and other stuff.

Also, I can confirm the problem that cli is generating schema instead of database for MySQL.

[fEyebrow]

@BudgieInWA Do you find the way to solve the problem?

[pantharshit00]

Also, same is true for mongodb

[chemicalkosek]

I can confirm that I can't deploy Prisma Server with mysql as database.
The same errror: 'Access denied for user '*'@'%' to database 'prisma'.
I'm starting fresh.
I'm using Dokku for deployment.
Steps to reproduce (just change postgres to mysql):
https://www.prisma.io/tutorials/deploy-prisma-to-dokku-ct15
Postgres deployment worked fine

[tin-ilakovac]

When prisma init introspects MySql, docker-compose.yml gets created. This file contains a line
"migrations: false"

Why then is migrations enforced, and 'prisma' DB needed?

The need for the 'prisma' DB is a problem when creation of additional DBs is not allowed.

[pantharshit00]

@g1t1n migrations: false tells the prisma server to use introspection and not generate the column by itself. The "prisma" database is still required in order to store the introspected datamodel so that prisma server can parse it at times.

[otech47]

It turns out the user in the connection config needs permission to create and alter database objects in a 'prisma' db (regardless of the database property value in the config).

How did you enable this permission? @whileoneloop

[whileoneloop]

It turns out the user in the connection config needs permission to create and alter database objects in a 'prisma' db (regardless of the database property value in the config).

How did you enable this permission? @whileoneloop

@otech47 - grant sufficient MySQL database permissions to the user for modifying the 'prisma' db.

[janpio]

I looked into this one with @do4gr:

The problem is not that it outputs the wrong stuff into docker-compose.yml, but that it asks the first question in the first place:

? Please select the schema you want to introspect my_db

Note that it says schema here instead of database.

This means that the data structure actually only has a schema and not a database, so it can also only write that.

Aside: Postgres has database and schema, MySQL only has database.

---

We found this code that seems to be responsible for asking these questions for Postgres and MySQL:

https://github.com/prisma/prisma/blob/b23bc459f2ae1e1bcad4466fbe4903e2dc872a3e/cli/packages/prisma-cli-core/src/utils/EndpointDialog.ts#L555-L604

Here it asks for the database - but only for Postgres:
https://github.com/prisma/prisma/blob/b23bc459f2ae1e1bcad4466fbe4903e2dc872a3e/cli/packages/prisma-cli-core/src/utils/EndpointDialog.ts#L579-L584

And here it asks for the schema - for both Postgres and MySQL:
https://github.com/prisma/prisma/blob/b23bc459f2ae1e1bcad4466fbe4903e2dc872a3e/cli/packages/prisma-cli-core/src/utils/EndpointDialog.ts#L595-L603

So a first try to fix this might be to move the type === DatabaseType.postgres from database to schema.

---

Curious thing:

Why does the introspection actually work with no database (which MySQL needs) and a schema (which MySQL doesn't have)!? The server fails because that, but the introspection just deals with it and produces a valid datamodel:

Introspecting database my_db 41.9s
Created datamodel definition based on 65 tables.

Fixing the bug above, might thus break introspection for MySQL.

This is the code that somehow just runs with schema instead of database and still works:

https://github.com/prisma/prisma/blob/b23bc459f2ae1e1bcad4466fbe4903e2dc872a3e/cli/packages/prisma-cli-core/src/utils/EndpointDialog.ts#L351-L421

[ulziibadrakh-p]

is there any solution?

[fenixsolorzano]

This is still a problem, no updates?

[Kennytian]

Try to change docker-compose.yml

-            schema: my_db
+            database: my_db

1. docker rm -f xxx_prisma_1

1. docker-compose up -d

It's works to me