ERROR 1064 ER_PARSE_ERROR: Unhandled exception Type: ER_PARSE_ERROR (1064)

Issue

Using the SET statement inside a stored procedure returns an error. For example, consider a stored procedure that contains statements like

BEGIN
SET @@sql_mode = PIPES_AS_CONCAT;
...
<some_statement_that_uses_pipes_for_concatenation>

When you run this stored procedure for the first time, it returns a syntax error. However, if you run the same stored procedure again, in the same session, it runs successfully.

When a SingleStore query is run, its plan is generated and then compiled. However, the stored procedure definition is compiled right away (like other DDL queries), when the procedure is created. The queries inside the stored procedure are not compiled until the stored procedure is run for the first time. For SET queries, this delay in compiling means that the stored procedure will be generated as though the variable specified in the SET statement was not set.

On the first run, these queries are compiled, which causes the stored procedure to recompile. Once the stored procedure has been recompiled, the queries inside it have been compiled as well. Therefore, when the stored procedure is executed for a second time, it runs successfully.

Solution

Avoid using SET statements inside a stored procedure, especially for setting those variables that can potentially affect query execution. Set the variables globally instead.

Last modified: July 5, 2021

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined
  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined
  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
    cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
    --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
    --bundle undefined \
    --new-bundle-format -
    Verified OK