Use TiDB with Prisma not successed

[Victorkangsh]

Question

Hi, I want deploy my prisma server with tidb, and it test well at small datamodel, but when i deploy a large datamodal it doesn't work. The error is here:

Encountered exception while applying migration. Rolling back. java.sql.SQLException: (conn=4) can't run multi schema change
java.sql.SQLException: (conn=4) can't run multi schema change
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:184)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:106)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:235)
	at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:224)
	at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:159)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at slick.jdbc.StatementInvoker.results(StatementInvoker.scala:38)
	at slick.jdbc.StatementInvoker.iteratorTo(StatementInvoker.scala:21)
	at slick.jdbc.Invoker.first(Invoker.scala:30)
	at slick.jdbc.Invoker.first$(Invoker.scala:29)
	at slick.jdbc.StatementInvoker.first(StatementInvoker.scala:15)
	at slick.jdbc.StreamingInvokerAction$HeadAction.run(StreamingInvokerAction.scala:52)
	at slick.jdbc.StreamingInvokerAction$HeadAction.run(StreamingInvokerAction.scala:51)
	at slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:275)
	at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:275)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: can't run multi schema change
Query is: ALTER TABLE `default@default`.`Account` ADD COLUMN `unionId` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL   , ADD UNIQUE INDEX `unionId_UNIQUE` (`unionId`(191) ASC), ALGORITHM = INPLACE
	at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:146)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:217)
	at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:218)
	... 15 more

This datamodal is work well on mysql.
I am not sure which side can solve this problem better and look forward to the proposal.

[winoros]

Caused by: java.sql.SQLException: can't run multi schema change
Query is: ALTER TABLE `default@default`.`Account` ADD COLUMN `unionId` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL   , ADD UNIQUE INDEX `unionId_UNIQUE` (`unionId`(191) ASC), ALGORITHM = INPLACE

You can refer to #5166
Currently, you need to do DDL one by one. So add column and add index in one sql will report the error.

[Victorkangsh]

Understand, thanks. So when does TiDB could solve this problem?

[shenli]

@Victorkangsh We will add this feature in the future.

[Victorkangsh]

Once TiDB solves this problem, I would love to put TiDB's logo and related instructions on the index web of Prisma and use TiDB in best practice examples.

TiDB almost perfectly solved the problem of prisma in distributed transactions. The infinitely horizontal expansion of the data access layer combined with the TiDB is really the best architecture.

[shenli]

@Victorkangsh Cool!
We will update our roadmap soon.

[ghost]

Confirming this still exists in master. There are actually two issues - the assertion says that the algorithm inplace is not supported, but I think since INSTANT is "better" it should be permitted. Basically this just needs to refuse if COPY is required. The second is that this is not supported as a multi-schema change:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment);
ALTER TABLE t1 ADD COLUMN `unionId` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, ADD UNIQUE INDEX `unionId_UNIQUE` (`unionId`(191) ASC), ALGORITHM = INPLACE;
ALTER TABLE t1 ADD COLUMN `unionId` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, ADD UNIQUE INDEX `unionId_UNIQUE` (`unionId`(191) ASC);

..

mysql> ALTER TABLE t1 ADD COLUMN `unionId` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, ADD UNIQUE INDEX `unionId_UNIQUE` (`unionId`(191) ASC), ALGORITHM = INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot alter table by INPLACE. Try ALGORITHM=INSTANT.
mysql> ALTER TABLE t1 ADD COLUMN `unionId` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, ADD UNIQUE INDEX `unionId_UNIQUE` (`unionId`(191) ASC);
ERROR 8200 (HY000): Unsupported multi schema change
mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-859-gccfc9b2ad
Edition: Community
Git Commit Hash: ccfc9b2ad0dcf8e447210de5f559d7fc208db968
Git Branch: master
UTC Build Time: 2020-07-29 09:37:45
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[wjhuang2016]

Confirming this still exists in master. There are actually two issues - the assertion says that the algorithm inplace is not supported, but I think since INSTANT is "better" it should be permitted. Basically this just needs to refuse if COPY is required. The second is that this is not supported as a multi-schema change:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment);
ALTER TABLE t1 ADD COLUMN `unionId` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, ADD UNIQUE INDEX `unionId_UNIQUE` (`unionId`(191) ASC), ALGORITHM = INPLACE;
ALTER TABLE t1 ADD COLUMN `unionId` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, ADD UNIQUE INDEX `unionId_UNIQUE` (`unionId`(191) ASC);

..

mysql> ALTER TABLE t1 ADD COLUMN `unionId` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, ADD UNIQUE INDEX `unionId_UNIQUE` (`unionId`(191) ASC), ALGORITHM = INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot alter table by INPLACE. Try ALGORITHM=INSTANT.
mysql> ALTER TABLE t1 ADD COLUMN `unionId` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, ADD UNIQUE INDEX `unionId_UNIQUE` (`unionId`(191) ASC);
ERROR 8200 (HY000): Unsupported multi schema change
mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-859-gccfc9b2ad
Edition: Community
Git Commit Hash: ccfc9b2ad0dcf8e447210de5f559d7fc208db968
Git Branch: master
UTC Build Time: 2020-07-29 09:37:45
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

The first issue had been resolved by #19270

[morgo]

I am going to close this as a duplicate of #14766

#14766 is actively being worked on, but will be completed as a set of sub-tasks. We will have to re-evaluate Prisma compatibility after it is complete. Thanks!