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

xuqingkuang · 2021-03-23T09:53:56Z

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

The text was updated successfully, but these errors were encountered:

xuqingkuang · 2021-03-23T11:41:18Z

After investigating deeper I found the query is triggered by mysql_async library: https://github.com/blackbeam/mysql_async/blob/master/src/conn/mod.rs#L681

But I have no idea how to disable the opt, the comment wrote this address may be incorrect in some cases (i.e. docker), but the issue still exists even the params of ?prefer_socket=false added to the database url.

https://github.com/blackbeam/mysql_async/blob/master/src/opts.rs#L372

pantharshit00 · 2021-03-23T15:11:11Z

We don't pass every config option to mysql async. I will mark this as a bug for now as we can refactor that part of code.

mysql_async is wrapped by quaint: https://github.com/prisma/quaint

janpio · 2021-03-23T16:56:06Z

We should also add a e2e test to cover this: prisma/ecosystem-tests#1537

matthewmueller · 2021-03-31T14:36:03Z

This sounds like an issue with mysql-async, do you mind opening an issue there and linking to this issue? If it turns out to be an issue in quaint we can investigate.

janpio · 2021-03-31T21:25:11Z

This is fundamentally an issue with Prisma and Quaint.
We should figure out if we want to change this behavior of not working on this specific database host, and if so find a way to do that. If our underlying library does not allow us to do that, we should find a way or try to implement a way in that library so we can do that in the future.

ImJoeHs · 2021-05-28T10:39:03Z

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''

wxqqh · 2021-05-28T12:12:49Z

hi friends，I ran into the same problem！
How to deal with it?

charlyzeng · 2021-05-31T06:45:06Z

I ran into the same problem too!

liuwt12138 · 2021-05-31T06:55:42Z

This problem also bothered me a lot.

janpio · 2021-05-31T17:17:32Z

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

Next step for us is to get a reproduction via prisma/ecosystem-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)

xuqingkuang · 2021-06-02T10:26:39Z

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 · 2021-06-08T13:36:49Z

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)

Hi, Jan.

The server I provided seems you didn't login yet, do you have any progress?

janpio · 2021-06-08T19:23:26Z

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.

xuqingkuang · 2021-06-10T07:57:47Z

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. :-)

pantharshit00 · 2021-06-10T09:46:23Z

Thanks for the details @xuqingkuang. I was able to reproduce in that VM.
@pimeys Will it be possible to expose prefer_socket in connection uri so that user can control this?

I will also ask people in the team for setting up a tencent cloud account which we can use in end to end tests.

xuqingkuang · 2021-06-10T10:14:07Z

Thanks for the details @xuqingkuang. I was able to reproduce in that VM.
@pimeys Will it be possible to expose prefer_socket in connection uri so that user can control this?

I will also ask people in the team for setting up a tencent cloud account which we can use in end to end tests.

I am feeling SELECT @@socket is a side effect, just make sure the arguments pass through to mysql_async correctly, and SELECT @@socket is not executed when Prisma connect will be OK.

So the problem isn't to use end to end tests to make Prisma working on TD-SQL, but the pass argument issue and the issue is able to be checked by the unit test of generating the URL to mysql_async

pantharshit00 · 2021-06-10T10:21:06Z

Here is the piece of code which is handling the parameters: https://github.com/prisma/quaint/blob/336f6d2f524740dd7dce956a37ca859cb10977a6/src/connector/mysql.rs#L155

It doesn't handle prefer_socket so we will add that. Unit test will be added to this file itself.

Regarding e2e, we have this huge repo which tests every dev release of Prisma with almost every cloud platform/service provide/db we can think of and we will like to ensure Prisma is not broken in any of those: https://github.com/prisma/e2e-tests/

pantharshit00 · 2021-06-10T20:54:52Z

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 #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 ❤️

lilee · 2024-04-23T12:27:15Z

mysql://user:password@host:port/db?prefer_socket=false is not effective

error: Unknown system variable 'socket' is already exists

pantharshit00 added bug/2-confirmed Bug has been reproduced and confirmed. kind/bug A reported bug. process/candidate team/client Issue for team Client. topic: connections labels Mar 23, 2021

janpio mentioned this issue Mar 23, 2021

[Database] Tencent Cloud, TDSQL for MySQL prisma/ecosystem-tests#1537

Open

matthewmueller removed the process/candidate label Mar 31, 2021

blackbeam mentioned this issue Apr 13, 2021

Add max_allowed_packet and wait_timeout options. blackbeam/mysql_async#151

Closed

pantharshit00 added the process/candidate label May 28, 2021

xuqingkuang closed this as completed Jun 2, 2021

xuqingkuang reopened this Jun 2, 2021

matthewmueller removed the process/candidate label Jun 2, 2021

janpio assigned pantharshit00 Jun 8, 2021

pantharshit00 removed their assignment Jun 10, 2021

pantharshit00 added the process/candidate label Jun 10, 2021

pantharshit00 mentioned this issue Jun 10, 2021

feat(mysql): pass prefer_socket to mysql driver prisma/quaint#299

Merged

pimeys closed this as completed in prisma/quaint#299 Jun 10, 2021

pantharshit00 reopened this Jun 10, 2021

pantharshit00 mentioned this issue Jun 10, 2021

Pass prefer_socket to mysql driver prisma/prisma-engines#2008

Merged

pantharshit00 closed this as completed Jun 10, 2021

janpio removed the process/candidate label Jun 10, 2021

Lizhooh mentioned this issue Mar 27, 2023

Error: FUNCTION GET_LOCK does not exist #18508

Closed

dengfuping mentioned this issue Apr 23, 2024

[Bug]: Connect OceanBase with prisma failed: Unknown system variable 'socket' oceanbase/oceanbase#1936

Open

dengfuping mentioned this issue Apr 29, 2024

prefer_socket doesn't work when connect OceanBase with prisma #24010

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

xuqingkuang commented Mar 23, 2021

xuqingkuang commented Mar 23, 2021 •

edited

pantharshit00 commented Mar 23, 2021

janpio commented Mar 23, 2021

matthewmueller commented Mar 31, 2021

janpio commented Mar 31, 2021 •

edited

ImJoeHs commented May 28, 2021

wxqqh commented May 28, 2021 •

edited

charlyzeng commented May 31, 2021

liuwt12138 commented May 31, 2021

janpio commented May 31, 2021

xuqingkuang commented Jun 2, 2021

xuqingkuang commented Jun 8, 2021

janpio commented Jun 8, 2021

xuqingkuang commented Jun 10, 2021

pantharshit00 commented Jun 10, 2021 •

edited

xuqingkuang commented Jun 10, 2021

pantharshit00 commented Jun 10, 2021

pantharshit00 commented Jun 10, 2021 •

edited

lilee commented Apr 23, 2024 •

edited

The startup query SELECT @@socket should be ignored when connecting to a remote database #6207

The startup query SELECT @@socket should be ignored when connecting to a remote database #6207

Comments

xuqingkuang commented Mar 23, 2021

Bug description

How to reproduce

Expected behavior

Environment & setup

xuqingkuang commented Mar 23, 2021 • edited

pantharshit00 commented Mar 23, 2021

janpio commented Mar 23, 2021

matthewmueller commented Mar 31, 2021

janpio commented Mar 31, 2021 • edited

ImJoeHs commented May 28, 2021

wxqqh commented May 28, 2021 • edited

charlyzeng commented May 31, 2021

liuwt12138 commented May 31, 2021

janpio commented May 31, 2021

xuqingkuang commented Jun 2, 2021

xuqingkuang commented Jun 8, 2021

janpio commented Jun 8, 2021

xuqingkuang commented Jun 10, 2021

pantharshit00 commented Jun 10, 2021 • edited

xuqingkuang commented Jun 10, 2021

pantharshit00 commented Jun 10, 2021

pantharshit00 commented Jun 10, 2021 • edited

lilee commented Apr 23, 2024 • edited

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

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

xuqingkuang commented Mar 23, 2021 •

edited

janpio commented Mar 31, 2021 •

edited

wxqqh commented May 28, 2021 •

edited

pantharshit00 commented Jun 10, 2021 •

edited

pantharshit00 commented Jun 10, 2021 •

edited

lilee commented Apr 23, 2024 •

edited