Mysql – What are these Errors in a Previously Successful MySQL Script

MySQLmysql-8.0

Several years ago I began work on a project that uses MySQL for the database. Much of the development happened prior to MySQL 8, but I last deployed the project on an early version of MySQL 8. And, have updated the deployment to 8.0.15 since then.

That prior deployment went smoothly including just running a large DB creation sql script. And it's still working (though hidden / not default) at codehawkins.com/index.php.

I haven't worked on this (or anything SQL) in about a year, but went to deploy a dev instance of it in order to explore continuing development. On a fresh install of MySQL 8.0.19 and using MySQL Workbench, my previously working code is full of errors and odd warnings…

For example, warnings and errors for one function are indicated in comments below:

-- The initial drop function line warns me that the function doesn't exist.
--   I don't remember seeing that when using "IF EXISTS".
-- More importantly, the warning at this point tells me that code 
--   prior to this point is not likely the cause of later errors, 
--   as it parsed this DROP command correctly.
DROP FUNCTION IF EXISTS fnGetLUID;
CREATE FUNCTION fnGetLUID(newUse VARCHAR(128))
  RETURNS BIGINT
BEGIN
-- The following line has an error indicating that a semi-colon is expected.
--   Yet there is one, and again, this used to work.
  INSERT INTO LUID (usedFor) VALUES (newUse);
-- An error here indicates that "RETURN" is not valid at this position.
  RETURN LAST_INSERT_ID();
-- Likewise, error reports "END" is not valid at this position.
END;

The entire script is at https://github.com/HumanJHawkins/CommunityPress/blob/master/documentation/v4lSchema.sql if useful to understanding this.

Were there changes in mySQL between 8.0.15 and 8.0.19 that break this, or is there some configuration on how to parse the language that I am missing?

Thanks in advance for any help.

Best Answer

I didn't realize this was a two-part question when I asked it:

What are the errors? Thanks to @Akina for the comment that answered this part of it... MySQL and/or MySQL workbench is unable to distinguish the delimiters that end individual statements from the delimiters that end the creation of the function. So typically one must set a different delimiter outside the creation of the function, then return the delimiter to normal after. I.e. 

DELIMITER $$;
-- Create DB object here, using semi-colon at end of all statements except the last one.
DELIMITER ;

See https://www.mysqltutorial.org/mysql-stored-procedure/mysql-delimiter/ for more detail. As one who has never had to do that, it sounded like an annoying bit of extra work. But...

Why did this always work before? Previously, I was running my script via JetBrains DataGrip. I believed the interface there to basically be just a terminal into mySQL. However, it turns out that DataGrip was automatically handling the necessary parsing of statements. So the manual delimiter handling was unnecessary within that environment.

Related Question