Cfdbinfo not respecting the database set by a datasource

MySQL 8.0.15
MySQL Connector 8.0.15
Lucee 5.3.1.94-RC
Windows 10

I have 2 databases that both have a table called "contacts:, but are otherwise completely different. There are 2 datasources to access each database, but they share credentials which have access to all databases (not root, but that doesn’t make a difference).

The result will be a query object that contains the details for both contacts tables, with the “table_cat” column clearly showing the two database names, so it could be filtering by database, but it isn’t. The fix is to use different credentials for each datasource and limit those credential’s access to just the one database each.

Either https://luceeserver.atlassian.net/browse/LDEV-1980 was not actually effective, or this is a different issue.

This previously worked on MySQL 5.6 and 5.7, so add it to the list of issues from the move to 8.

I am seeing similar behavior with AWS Aurora w/ MySQL 8 compat. (8.0.mysql_aurora.3.02.1)

We’re using ORM on a database server that has multiple copies of the database, one for each customer. When ORM is doing its bit to update the schema, it runs the following query.

SELECT
	TABLE_SCHEMA AS TABLE_CAT,
	NULL AS TABLE_SCHEM,
	TABLE_NAME,
	CASE
		WHEN TABLE_TYPE = 'BASE TABLE' THEN CASE
			WHEN TABLE_SCHEMA = 'mysql'
			OR TABLE_SCHEMA = 'performance_schema' THEN 'SYSTEM TABLE'
			ELSE 'TABLE'
		END
		WHEN TABLE_TYPE = 'TEMPORARY' THEN 'LOCAL_TEMPORARY'
		ELSE TABLE_TYPE
	END AS TABLE_TYPE,
	TABLE_COMMENT AS REMARKS,
	NULL AS TYPE_CAT,
	NULL AS TYPE_SCHEM,
	NULL AS TYPE_NAME,
	NULL AS SELF_REFERENCING_COL_NAME,
	NULL AS REF_GENERATION
FROM
	INFORMATION_SCHEMA.TABLES
WHERE
	TABLE_NAME = 'User'
HAVING
	TABLE_TYPE IN ('TABLE', 'VIEW', null,
	null,
	null)
ORDER BY
	TABLE_TYPE,
	TABLE_SCHEMA,
	TABLE_NAME

If the database user is not limited to a single database, then this returns a row for each database that has that table name. It would seem that a minor tweak to include the TABLE_SCHEMA in the where clause would mitigate this problem.

1 Like