User-defined functions in my code cause Lucee's connection to MySQL get messed up!

dev
1

I get this message (below) about a user that is not defined with my datasource when I have a udfunction in my code! Took me many hours to figure this out…any idea why this happens or how to fix this?

If I remove the “udfGetInvoiceDatesandTechs(1606427059) As InvoiceDateTech” part of the query the page loads fine.

Message The user specified as a definer (‘appliancDBA1’@‘%’) does not exist
SQL SELECT DISTINCT addresses., customers., businessaddresses., invoices., Date_Format(invoices.Time, ‘%T’) as StringTime, Date_Format(invoices.Time2, ‘%T’) as StringTime2, invoicesb.*, udfGetInvoiceDatesandTechs(1606427059) As InvoiceDateTech, Date_Format(Invoice_Date, ‘%Y-%m-%d’) as RealInvoice_Date From addresses, (customers
2

This does not appear to be a Lucee issue. If udfGetInvoiceDatesandTechs() is a UDF defined in the MySQL server, then the issue is purely a MySQL one (i.e. the definer of that UDF is specified as ‘appliancDBA1’ and no such user exists on the MySQL server).

If the UDF is a Lucee UDF, then you would need to output the result of it, not the name of it, i.e.

<cfquery...>
select ... , #udfGetInvoiceDatesandTechs( someValue )#
...
</cfquery>

(and obviously use <cfqueryparam where appropriate).

3

And this could be demonstrated by using a different client - other than Lucee - to connect to the DB (eg: use MySQL Workbench, or the shell client) using the same user credentials as you use with your Lucee data source, and run the same query. You’ll likely get the same result.

It looks to me like a permissions thing on the DB.

4

Of course! Ugh! Thank you!

1 Like
5

I spoke too soon. There is an issue! I’m actually on a different server then the one prior and got the same error message. There’s something off with Lucee and it’s connection to mySQL. BUT I found out what caused this current issue! Wanted to share in case someone else has this issue, or I do in the future and forget what caused it. LOL

I have two web pages set up that are identical to show the different way Lucee is behaving in each.
https://domainname.com/testcstone.cfm
https://domainname.com/cstone/testcstone.cfm

When in the folder ‘cstone’ it’s calling for a user ‘admin’ that is not referenced in my datasource connection in Lucee. The datasource user is applianceschedul_cstone.

MYSTERY SOLVED! Turns out I had an application.cfc file that had this in it:
this.datasources[“cstone”] = {
class: ‘com.mysql.cj.jdbc.Driver’
, bundleName: ‘com.mysql.cj’
, bundleVersion: ‘8.0.30’
, connectionString: ‘jdbc:mysql://localhost:3306/cstone?characterEncoding=UTF-8&serverTimezone=America/Chicago&maxReconnects=3’
, username: ‘admin’

So apparently, this overwrote what I entered into Lucee’s datasource area!
That was a few hours of my life I’ll never get back…