prisma: The startup query `SELECT @@socket` should be ignored when connecting to a remote database

Bug description

I have a server that is using the TDSQL for MySQL distribution database provided by Tencent Cloud.

The problem is Prisma Client will get a error when startup:

2021-03-23T17:11:16.362175968+08:00 prisma:info Starting a mysql pool with 91 connections.
2021-03-23T17:11:16.373958504+08:00 prisma:info Fetched a connection from the pool
2021-03-23T17:11:16.373999662+08:00 prisma:info Encountered error during initialization:
2021-03-23T17:11:16.38391327+08:00 prisma:error  in    0: user_facing_errors::Error::new_non_panic_with_current_backtrace
2021-03-23T17:11:16.383934881+08:00    1: query_engine::error::<impl core::convert::From<query_engine::error::PrismaError> for user_facing_errors::Error>::from
2021-03-23T17:11:16.383941043+08:00    2: query_engine::error::PrismaError::render_as_json
2021-03-23T17:11:16.383946603+08:00    3: query_engine::main::main::{{closure}}::{{closure}}
2021-03-23T17:11:16.383954968+08:00    4: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll
2021-03-23T17:11:16.383959586+08:00    5: std::thread::local::LocalKey<T>::with
2021-03-23T17:11:16.383963003+08:00    6: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll
2021-03-23T17:11:16.383966609+08:00    7: async_io::driver::block_on
2021-03-23T17:11:16.384001264+08:00    8: async_global_executor::reactor::block_on
2021-03-23T17:11:16.384038635+08:00    9: std::thread::local::LocalKey<T>::with
2021-03-23T17:11:16.384044606+08:00   10: async_std::task::builder::Builder::blocking
2021-03-23T17:11:16.384048593+08:00   11: query_engine::main
2021-03-23T17:11:16.384078069+08:00   12: std::sys_common::backtrace::__rust_begin_short_backtrace
2021-03-23T17:11:16.384082347+08:00   13: std::rt::lang_start::{{closure}}
2021-03-23T17:11:16.384126229+08:00   14: core::ops::function::impls::<impl core::ops::function::FnOnce<A> for &F>::call_once
2021-03-23T17:11:16.38413206+08:00              at /rustc/cb75ad5db02783e8b0222fee363c5f63f7e2cf5b/library/core/src/ops/function.rs:259:13
2021-03-23T17:11:16.384136979+08:00       std::panicking::try::do_call
2021-03-23T17:11:16.384150434+08:00              at /rustc/cb75ad5db02783e8b0222fee363c5f63f7e2cf5b/library/std/src/panicking.rs:379:40
2021-03-23T17:11:16.384155794+08:00       std::panicking::try
2021-03-23T17:11:16.384176193+08:00              at /rustc/cb75ad5db02783e8b0222fee363c5f63f7e2cf5b/library/std/src/panicking.rs:343:19
2021-03-23T17:11:16.38418046+08:00       std::panic::catch_unwind
2021-03-23T17:11:16.384183135+08:00              at /rustc/cb75ad5db02783e8b0222fee363c5f63f7e2cf5b/library/std/src/panic.rs:396:14
2021-03-23T17:11:16.384185549+08:00       std::rt::lang_start_internal
2021-03-23T17:11:16.384193123+08:00              at /rustc/cb75ad5db02783e8b0222fee363c5f63f7e2cf5b/library/std/src/rt.rs:51:25
2021-03-23T17:11:16.384200237+08:00   15: main
2021-03-23T17:11:16.384203643+08:00 Error querying the database: Server error: `ERROR HY000 (1193): Unknown system variable 'socket''

So I had a check of the Prisma client startup queries, it’s executed:

Connect      | root@172.17.0.1 on mydb using TCP/IP
Query        | SELECT @@socket 
Query        | SELECT @@max_allowed_packet
Query        | SELECT @@wait_timeout

Prisma will always select the @@socket variable, it’s not necessary for the remote database in my opinion.

How to reproduce

Deploy a server on Tencent Cloud with a TDSQL database, with Prisma Client.

Expected behavior

Prisma startup will not execute the SELECT @@socket, because TDSQL is not defined.
The server will startup correctly.

Environment & setup

OS: CentOS 8
Database: TDSQL with MySQL
Node.js version: 14.15.4
Prisma version:

prisma               : 2.19.0
@prisma/client       : 2.19.0
Current platform     : rhel-openssl-1.1.x
Query Engine         : query-engine c1455d0b443d66b0d9db9bcb1bb9ee0d5bbc511d (at node_modules/@prisma/engines/query-engine-rhel-openssl-1.1.x)
Migration Engine     : migration-engine-cli c1455d0b443d66b0d9db9bcb1bb9ee0d5bbc511d (at node_modules/@prisma/engines/migration-engine-rhel-openssl-1.1.x)
Introspection Engine : introspection-core c1455d0b443d66b0d9db9bcb1bb9ee0d5bbc511d (at node_modules/@prisma/engines/introspection-engine-rhel-openssl-1.1.x)
Format Binary        : prisma-fmt c1455d0b443d66b0d9db9bcb1bb9ee0d5bbc511d (at node_modules/@prisma/engines/prisma-fmt-rhel-openssl-1.1.x)
Studio               : 0.358.0

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 19 (9 by maintainers)

Most upvoted comments

I tested the fix in your server and it works well now with prefer_socket=false.

A dev prisma release will be available with the fix when https://github.com/prisma/prisma/pull/7572 will be merged.

I tested it using yarn resolutions and specifying the exact @prisma/engines version which had the fix.

I am going to close this.

Feel free to reset your database as it created the tables when I tested it out:

And thank you soooo much for setting this server for us ❤️

pantharshit00 on Jun 11, 2021

Sorry, this is not our highest priority right now. But I assigned it explicitly to @pantharshit00 now to take a look at the server you provided (I got the email, thanks!) and have a look what is going on here.

OK, I hope @pantharshit00 will check the problem as soon as possible because I can’t afford the CVM for a long time. 😃

xuqingkuang on Jun 10, 2021

Are you all on Tencent Cloud @wxqqh @charlyzeng @liuwt12138 as well?

Next step for us is to get a reproduction via prisma/e2e-tests#1537 so we can experience the problem ourselves. Then we can investigate deeper if what @xuqingkuang identified above is really the root cause, and how we can work around that.

(If one of you can supply us a (empty!) testing database with this behavior, we would be very happy. You can contact me via jan@prisma.io to send a connection string)

Thank you for your reply, I had sent the CVM that be able to access the TD-SQL database information with my mail x@kxq.io, please have a check.

xuqingkuang on Jun 2, 2021

Any update here? We have the exactly same problem, is there any workaround?

 prisma:engine {
  prisma:engine   is_panic: false,
  prisma:engine   message: "Error querying the database: Server error: `ERROR HY000 (1193): Unknown system variable 'socket''",
  prisma:engine   backtrace: '   0: user_facing_errors::Error::new_non_panic_with_current_backtrace\n' +
  prisma:engine     '   1: query_engine::error::<impl core::convert::From<query_engine::error::PrismaError> for user_facing_errors::Error>::from\n' +
  prisma:engine     '   2: query_engine::error::PrismaError::render_as_json\n' +
  prisma:engine     '   3: query_engine::main::main::{{closure}}::{{closure}}\n' +
  prisma:engine     '   4: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll\n' +
  prisma:engine     '   5: std::thread::local::LocalKey<T>::with\n' +
  prisma:engine     '   6: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll\n' +
  prisma:engine     '   7: async_io::driver::block_on\n' +
  prisma:engine     '   8: async_global_executor::reactor::block_on\n' +
  prisma:engine     '   9: std::thread::local::LocalKey<T>::with\n' +
  prisma:engine     '  10: async_std::task::builder::Builder::blocking\n' +
  prisma:engine     '  11: query_engine::main\n' +
  prisma:engine     '  12: std::sys_common::backtrace::__rust_begin_short_backtrace\n' +
  prisma:engine     '  13: std::rt::lang_start::{{closure}}\n' +
  prisma:engine     '  14: core::ops::function::impls::<impl core::ops::function::FnOnce<A> for &F>::call_once\n' +
  prisma:engine     '             at /rustc/2fd73fabe469357a12c2c974c140f67e7cdd76d0/library/core/src/ops/function.rs:259:13\n' +
  prisma:engine     '      std::panicking::try::do_call\n' +
  prisma:engine     '             at /rustc/2fd73fabe469357a12c2c974c140f67e7cdd76d0/library/std/src/panicking.rs:379:40\n' +
  prisma:engine     '      std::panicking::try\n' +
  prisma:engine     '             at /rustc/2fd73fabe469357a12c2c974c140f67e7cdd76d0/library/std/src/panicking.rs:343:19\n' +
  prisma:engine     '      std::panic::catch_unwind\n' +
  prisma:engine     '             at /rustc/2fd73fabe469357a12c2c974c140f67e7cdd76d0/library/std/src/panic.rs:431:14\n' +
  prisma:engine     '      std::rt::lang_start_internal\n' +
  prisma:engine     '             at /rustc/2fd73fabe469357a12c2c974c140f67e7cdd76d0/library/std/src/rt.rs:51:25\n' +
  prisma:engine     '  15: main\n' +
  prisma:engine     '  16: __libc_start_main\n' +
  prisma:engine     '  17: <unknown>\n'
  prisma:engine } +0ms
  prisma:client Error: Error querying the database: Server error: `ERROR HY000 (1193): Unknown system variable 'socket''

ImJoeHs on May 28, 2021