Cfdbinfo not respecting the database set by a datasource

dev support
1

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 [LDEV-1980] - Lucee 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.

2

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
3

@chadmpaul We are having the same issue, with ORM set to UPDATE
it goes here: extension-hibernate/HibernateUtil.java at 3.5.5 · lucee/extension-hibernate · GitHub

and if the table doesn’t exist for the database configured in the datasource, it will go to another database and create this problem…

2 workarounds that I found:

  • Have a DB User per database ( a lot of work )
  • Set ormSettings.catalog = “database name” ( will need to maintain the DB name in the app :confused: )
4

is there a ticket for your orm problem?

I have created a new ticket for the dbinfo problem with mysql 8

5

@migueltarga I’ve replicated the issue in my locally as described in the ticket’s comment [LDEV-4359] - Lucee
This issue not happened with using hibernate 5.4.29.20-BETA OR MySQL 5.1.40.
So please try with any of one the above extensions with mentioned version.