SELECT LAST_INSERT_ID() Syntax Error After Upgrade

dev support
1

Good evening, all!

I’ve run a Lucee-powered site on VPS hosting at Host Media for several years now.

Due to some issues caused by incompatible upgrades, we’ve migrated to a new VPS, which is using Lucee 5.2.7.63 and MySQL 5.5.56.

This is really the worst possible time to make changes that might affect this site, as it is a small company that provides after school classes and they need to start this year’s registrations or will lose a lot of income.

If anyone can give me some help troubleshooting, I’ll owe you big time!

When I attempt to perform an insert from an order form, I get the following error message:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘SELECT LAST_INSERT_ID() AS oID’ at line 26

From what I am reading in the MySQL KB, the syntax should be the same.

I am able to run the query in SQLYog without a problem using the SQL from the dump.

This is the query (again, hasn’t changed in 3 years):

<!--- This function Insertes a new order in the v_orders table --->
<cffunction name = "startNewOrder" >
	<cfargument name = "oParentFirstName" >	        	
	<cfargument name = "oParentLastName" >	
	<cfargument name = "oParentStreetAddress" >	
	<cfargument name = "oParentCity" >	
	<cfargument name = "oParentState" >	
	<cfargument name = "oParentZip" >	
	<cfargument name = "oParentPhone">        
	<cfargument name = "oParentEmail">
	<cfargument name = "oChildFirstName" >	
	<cfargument name = "oChildLastName" >
	<cfargument name = "oChildSchoolName" >	
	<cfargument name = "oChildTeacherLastName" >
	<cfargument name = "oChildGrade">
	<cfargument name = "oChildAftercare">        
    <cfargument name = "oChildAllergies">
	<cfargument name = "oOrderClassID">
	<cfargument name = "oOrderDate">
    <cfargument name = "oOrderDescription">	
    <cfargument name = "oTotalCost">  
    <cfargument name = "oWaiverText"> 
    <cfargument name = "oOrderNote">  
    <cfargument name = "oOrderActive">             
	<CFQUERY NAME = "insertNewOrder" DATASOURCE="#Request.DSN#">
		Insert Into v_orders 		(oParentFirstName, oParentLastName, oParentStreetAddress, oParentCity, oParentState, oParentZip, oParentPhone, oParentEmail, oChildFirstName, oChildLastName, oChildSchoolName, oChildTeacherLastName, oChildGrade, oChildAftercare, oChildAllergies, oOrderClassID, oOrderDate, oOrderDescription, oTotalCost, oWaiverText, oOrderNote, oOrderActive)
		Values 					( 
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oParentFirstName#">, 
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oParentLastName#">,
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oParentStreetAddress#">,  
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oParentCity#">, 
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oParentState#">, 
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oParentZip#">, 
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oParentPhone#">, 
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oParentEmail#">, 
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oChildFirstName#">,            
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oChildLastName#">,
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oChildSchoolName#">,             
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oChildTeacherLastName#">,
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oChildGrade#">, 
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oChildAftercare#">, 
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oChildAllergies#">,
        <cfqueryparam cfsqltype ="CF_SQL_INTEGER" value="#arguments.oOrderClassID#">,
        <cfqueryparam cfsqltype ="CF_SQL_DATE"    value="#arguments.oOrderDate#">,
		<cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oOrderDescription#">,
        <cfqueryparam cfsqltype ="CF_SQL_DECIMAL" value="#arguments.oTotalCost#">, 
		<cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oWaiverText#">,
        <cfqueryparam cfsqltype ="CF_SQL_VARCHAR" value="#arguments.oOrderNote#">, 
        <cfqueryparam cfsqltype ="CF_SQL_INTEGER" value="#arguments.oOrderActive#">             
        );  
        SELECT LAST_INSERT_ID() AS oID;             
	</CFQUERY>
	<cfreturn insertNewOrder>
</cffunction>

And here is the data passed (please forgive the huge strings, the owner’s husband is an attorney and insisted on saving the exact language, word for word).

Insert Into v_orders (oParentFirstName, oParentLastName, oParentStreetAddress, oParentCity, oParentState, oParentZip, oParentPhone, oParentEmail, oChildFirstName, oChildLastName, oChildSchoolName, oChildTeacherLastName, oChildGrade, oChildAftercare, oChildAllergies, oOrderClassID, oOrderDate, oOrderDescription, oTotalCost, oWaiverText, oOrderNote, oOrderActive)

Values (
‘Douglas’,
‘Roberson’,
‘6901 Calle Del Paz N’,
‘Boca Raton’,
‘FL’,
‘33433’,
‘5612990075’,
‘d@123.com’,
‘Deebo’,
‘Picks’,
‘Waters Edge Elementary School’,
‘Grumblebutt’,
‘3’,
‘Yes’,
‘Food’,
200,
{d ‘2018-08-03’},
‘Waters Edge Elementary School - Fall 2018 - 9-12-2018-12-19-2018- Total Cost: $225’,
225,
‘As a parent of a child enrolling in Fun Chefs, LLC, I understand that I and/or my child/ward (hereafter referred to as ‘‘participant’’) will be engaged in cooking activities at Fun Chefs, LLC. While in the course of such activities, the participant my have direct contact or exposure with various appliances, equipment and tools (hereinafter referred to as ‘‘kitchen tools’’) associated with cooking. I understand that such kitchen tools are capable of causing serious personal injury and property damage. I understand there are other inherent risks associated with participating in cooking activities, including operating hot stoves, boiling water, hot oil, and ovens. There is a risk of slipping and falling on food spills. Likewise, there are risk associated with eating and ingesting food and drinks, including choking, food allergic reactions, and anaphylactic shock. There may be cross-contamination with foods that cause food allergies, and trace amounts of those foods. I agree to assume those risks and forever release, indemnify and hold harmless Fun Chefs, LLC and/or any of its officers, director, cooking school staff, agent, contractor, or representatives (’‘Fun Chefs, LLC’‘) for any personal injury and property damage which could potentially result from participants engagement in any Fun Chefs, LLC activities. Furthermore, I give Fun Chefs, LLC the authority to seek emergency medical treatment for participant, should it be deemed warranted by Fun Chefs, LLC and/or anyone under its employ. I know of no other condition that would prevent participant from engaging in activities. This waiver, release, and assumption of risk is binding on all heirs and assigns. I grant Fun Chefs the right to photograph, reproduce, and use participant picture, silhouette, and other reproductions of physical likeness, in connection with any Fun Chefs, L.L.C. promotions. For the safety, welfare and proper maintenance of all participants, Fun Chefs, LLC reserves the right to dismiss a participant whose conduct or influence is adverse to the best interests of Fun Chefs, LLC or the other participants. Such conduct or influence includes but is not limited to weapons, drugs, drug paraphernalia, vandalism, non-compliance with program rules and procedures, failure to follow safety instructions, inappropriate behavior or the omission or misrepresentation of any medical or mental history of the participant.’,
‘Inactive’,
0
);
SELECT LAST_INSERT_ID() AS oID;

Again, works as expected in SQLYog, bombs in Lucee - hopefully it is something simple, like the datasource setup or something!

Edit: changed email address as I used one of my real ones and didn’t want a bot to grab it and send me love letters :slight_smile:

2

Just a follow up - I saw an update for Lucee, so now am at 5.2.8.50 and an update for the MySQLdatasource extension (now at 8.0.11).

No change in error.

3

Doesn’t it automatically return the identity key in result structure. Something like result.generatedkey?

4

Also do you have allow multiple Queries set to true in your datasource?

5

Yes, try removing the SELECT LAST_INSERT_ID(); query and instead add a result attribute to your cfquery tag:

<cfquery name= "insertNewOrder" datasource="#Request.DSN#" result="result">
INSERT ...etc
</cfquery>
<cfset oID = result.generated_key>
6

Thanks, all, for the answers!

@kabutotx yes, you’re absolutely right - when hositng moved, they didn’t carry over all of the settings and the issue was multiple queries set to true.

@Julian_Halliwell thank you for the code example - I have never used result.generated_key before and I think that would be better than combining queries.

1 Like