Different database name in SELECT and connectionURL, does this matter?

Issue

We have a custom Java code that connects to MySQL (8.0.23-14). Developers are using one database connection to trigger several queries, not too much parallel load though. But for the JDBCConnection the URL they have is jdbc:mysql://HOST:3306/DBNAME whereas the SELECT queries are run on different databases as well using the same connection object (like FROM DBNAME1.Tablename, FROM DBNAME2.TableName2 and so on). We just get the responses fine, but when I noticed the processes on MySQL Server, I see them getting created on the main database being used in the initial connection obviously. Does this impact performance of database(s)/query responses in any way? Is it advised instead to use different Connection objects for different database specific queries so that the databases used SELECT should match always with the one used in the connection? In that scenario, how to handle queries with multiple databases?
Please advise.

Solution

USE db1;    -- or whatever is specified by the "connection"
SELECT ... FROM db2.tbl ...;

db2 rules in the execution of the query. db1 in the PROCESSLIST is just misleading. No "impact".

Do you have any evidence to the contrary?

Maybe, only maybe, there is some obscure case where the collation of a Stored Procedure, the collation of the connection, and the collation of the table being accessed don’t align. If so, you should probably make all of them consistent, regardless of DATABASE.

The main purpose of USE is to specify which DATABASE to use when it is not otherwise stated (as in FROM db2.tbl). Databases are primarily a simplistic tool for organizing one’s TABLEs and Stored Procedures and a few other things.

It is quite OK to connect to one db, then modify tables in other dbs. The one thing to watch for is GRANT permissions to the various dbs. For security reasons, it is generally not wise to GRANT ALL ON *.* to anyone other than the admin. It is OK to GRANT one user to full (or partial) access to a subset of the Databases; this seems to be your case.

Answered By – Rick James

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published