Tidb: Use TiDB with Prisma not successed

Created on 7 Jan 2019 · 8Comments · Source: pingcap/tidb

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.

siinfra typquestion

Source

Victorkangsh

All 8 comments

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 https://github.com/pingcap/tidb/issues/5166
Currently, you need to do DDL one by one. So add column and add index in one sql will report the error.

winoros on 7 Jan 2019

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

Victorkangsh on 7 Jan 2019

@Victorkangsh We will add this feature in the future.

shenli on 8 Jan 2019

👍1

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.

Victorkangsh on 8 Jan 2019

@Victorkangsh Cool!
We will update our roadmap soon.

shenli on 8 Jan 2019

👍1

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)

nullnotnil on 30 Jul 2020

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 https://github.com/pingcap/tidb/pull/19270