Liquibase: SQL syntax error in a MySQL stored procedure

Question

I tried to run the MySQL stored procedure SQL script by Liquibase, but never worked.

The content of db.storedprocedure.xml:

<changeSet author="zzz" id="1" runOnChange="true" runInTransaction="true">
    <sqlFile path="changelogs/change_03.sql"
         relativeToChangelogFile="true"
         endDelimiter="$$"
         stripComments="false"
         splitStatements="false"/>
</changeSet>

The content of my SQL file change_03.sql:

$$

CREATE PROCEDURE `liqui01`.`User_Search`(
    INOUT id INT,
    OUT name VARCHAR(50)
)
BEGIN

    SET @sql = CONCAT("SELECT id, name FROM user WHERE da.MarketId = ", id );

    PREPARE stmt from @sql;
    EXECUTE stmt;

END$$

It shows the error like:

Unexpected error running Liquibase: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$ ...

I've tried to change the "$$" to other delimiters, or put the SQL inside <sql> tags of the XML file, all didn't work.

Any advise would be appreciated!

Update 1

@Shadow has given the correct answer (unfortunately I cannot mark it as the answer because it's in the comments), removing the delimeter lines from the sql scripts will make it work, thanks for him!

Now the question is: How to use "endDelimiter" parameter?

Answer 1

liquibase internals add the delimiter command to begin of command, you must remove or comment the fist line with $$:

-- $$

CREATE PROCEDURE `liqui01`.`User_Search`(

...

END$$

Answer 2

I searched the internet for a solution. It appears Liquibase will create stored procedures just fine on the update command. But falls apart on the updateSQL command as it outputs invalid SQL.

My solution was to add comments to the changesets sql tag. With the aim of using regex to sanitize the output sql.

The .sql file I am using has some keywords liquibase does not like e.g. DELIMITER, so I use <modifysql> and <regExpReplace> to lint / sanitize the .sql file. This way, the .sql file is consumable by liquibase and runs successfully on update without errors.

However, we'd still output invalid SQL on updateSQL. To resolve this, I use another regex search/replace on the invalid outputted .sql file looking for the comments; -- DELIMITER $$ etc.

Example:

<changeSet id="Test1" author="author_name" context="master" runOnChange="true">
    <validCheckSum>any</validCheckSum>
    <sql><![CDATA[
        DROP PROCEDURE IF EXISTS `sp_myProcedure`;
        -- DELIMITER $$
    ]]></sql>
    <createProcedure path="yourpath/procs/sp_myProcedure.sql" relativeToChangelogFile="true" />
    <sql><![CDATA[
        -- $$ DELIMITER;
    ]]></sql>
    <modifySql>
        <regExpReplace replace="[^}]+CREATE PROCEDURE" with="CREATE PROCEDURE"/>
    </modifySql>
    <modifySql>
        <regExpReplace replace="END\s*\$\$[^}]*" with="END"/>
    </modifySql>
</changeSet>  

Answer 3

Please refer the official Liquibase documentation link : here which mentions about escaping the delimiter character $$ like \$\$.

When setting an end-delimiter, note that certain DBMS and operating systems require delimiter values to be escaped. For example, a $$ end-delimiter with mysql requires escaping as: end-delimiter="$$".

Answer 4

I'm going to make an assumptions with your architecture in my answer. I'm assuming you want to process multiple files in different sites within the same SharePoint tenant. So, not across tenants.

To achieve what you're asking for, I created a Parse JSON action which takes in the following structure (as an example, obviously the structure is the key point here, not the data) ...

Scenario 1 - Specific Files

[
  {
    "SiteName": "ExampleSolution",
    "FileName": "/Shared Documents/General/Book.xlsx"
  },
  {
    "SiteName": "TestSite",
    "FileName": "/Shared Documents/Test Folder/Document.docx"
  }
]

The SP tenant needs to be authenticated to with the appropriate user.

Then, in a For Each action, loop through each item and retrieve the contents of each document using the Get file content using path action.

Site Address = concat('https://yourtenant.sharepoint.com/sites/', items('For_each')?['SiteName'])

File Path = File Name (from Dynamic Content)

It will then retrieve the contents dynamically using those expressions.

File 1 (Excel Document)

File 2 (Word Document)

Scenario 2 - All Files

If you want to do it for all files, just change it up slightly ...

[
  {
    "FolderName": "/Shared Documents/General",
    "SiteName": "ExampleSolution"
  },
  {
    "FolderName": "/Shared Documents/Test Folder",
    "SiteName": "TestSite"
  }
]

Site Address = concat('https://yourtenant.sharepoint.com/sites/', items('For_each')?['SiteName'])

File Identifier = Folder Name (from Dynamic Content)

Output - Folder 1

[
  {
    "Id": "%252fShared%2bDocuments%252fGeneral%252fBook.xlsx",
    "Name": "Book.xlsx",
    "DisplayName": "Book.xlsx",
    "Path": "/Shared Documents/General/Book.xlsx",
    "LastModified": "2021-12-24T02:56:14Z",
    "Size": 15330,
    "MediaType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    "IsFolder": false,
    "ETag": "\"{23948609-0DA0-43E0-994C-2703FEEC8567},7\"",
    "FileLocator": "dataset=aHR0cHM6Ly9icmFka2RpeG9uLnNoYXJlcG9pbnQuY29tL3NpdGVzL0V4YW1wbGVTb2x1dGlvbg==,id=JTI1MmZTaGFyZWQlMmJEb2N1bWVudHMlMjUyZkdlbmVyYWwlMjUyZkJvb2sueGxzeA==",
    "LastModifiedBy": null
  },
  {
    "Id": "%252fShared%2bDocuments%252fGeneral%252fTest%2bDocument.docx",
    "Name": "Test Document.docx",
    "DisplayName": "Test Document.docx",
    "Path": "/Shared Documents/General/Test Document.docx",
    "LastModified": "2021-12-30T11:49:28Z",
    "Size": 17959,
    "MediaType": "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
    "IsFolder": false,
    "ETag": "\"{7A3C7133-02FC-4A63-9A58-E11A815AB351},8\"",
    "FileLocator": "dataset=aHR0cHM6Ly9icmFka2RpeG9u etc",
    "LastModifiedBy": null
  },
  {
    "Id": "%252fShared%2bDocuments%252fGeneral%252fHierarchy.xlsx",
    "Name": "Hierarchy.xlsx",
    "DisplayName": "Hierarchy.xlsx",
    "Path": "/Shared Documents/General/Hierarchy.xlsx",
    "LastModified": "2022-01-07T02:49:38Z",
    "Size": 41719,
    "MediaType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    "IsFolder": false,
    "ETag": "\"{C919454C-48AB-4897-AD8C-E3F873B52E50},72\"",
    "FileLocator": "dataset=aHR0cHM6Ly9icmFka2RpeG9uL etc",
    "LastModifiedBy": null
  }
]

Output - Folder 2

[
  {
    "Id": "%252fShared%2bDocuments%252fTest%2bFolder%252fTest.xlsx",
    "Name": "Test.xlsx",
    "DisplayName": "Test.xlsx",
    "Path": "/Shared Documents/Test Folder/Test.xlsx",
    "LastModified": "2022-01-09T11:08:31Z",
    "Size": 17014,
    "MediaType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    "IsFolder": false,
    "ETag": "\"{CCF71CE7-89E7-4F89-B5CB-0F078E22C951},163\"",
    "FileLocator": "dataset=aHR0cHM6Ly9icmFka2RpeG9u etc",
    "LastModifiedBy": null
  },
  {
    "Id": "%252fShared%2bDocuments%252fTest%2bFolder%252fDocument.docx",
    "Name": "Document.docx",
    "DisplayName": "Document.docx",
    "Path": "/Shared Documents/Test Folder/Document.docx",
    "LastModified": "2022-01-09T11:08:16Z",
    "Size": 17293,
    "MediaType": "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
    "IsFolder": false,
    "ETag": "\"{317C5767-04EC-4264-A58B-27A3FA8E4DF3},3\"",
    "FileLocator": "dataset=aHR0cHM6Ly9icmFka2RpeG etc",
    "LastModifiedBy": null
  }
]

From here, just process each file individually using one of the files actions like in the first scenario above.

Note: You'll need to work through sub folders and recursion. There doesn't appear to be a way to do that easily.

You've provided very little information but it should be enough for you to adapt it accordingly.

Also, I strongly recommend you use a means other than a hardcoded JSON document in the action itself. There are way better means for housing that information which wouldn't result in a need to update the action itself everytime you want to add or delete a file.

The concept of the loop and and the expressions are the most important part to grasp as they will give you what you want.