Open carley-citian opened 10 months ago
@carley-citian
Could you try the following and confirm?
HI @sfc-gh-tmathew !
If it's helpful I am using Gitlab CI/CD and am using the Python 3.9.18 docker image: https://hub.docker.com/layers/library/python/3.9.18/images/sha256-753586e289a94965eb389ae5397233d32c3cff0f40f81c633dcc00d277012634?context=explore
And here is the script I am using for this pipeline in my .gitlab-ci.yml file:
# install and run schemachange on changes to migrations
script:
- echo "Checking python version"
- python --version
- echo "Installing schemachange"
- pip install schemachange --progress-bar off
- echo "Running schemachange"
- ls $CI_PROJECT_DIR/migrations/
- schemachange -f $CI_PROJECT_DIR/migrations/ -a $SF_ACCOUNT -u $SF_USERNAME -r $SF_ROLE -w $SF_WAREHOUSE -d $SF_DATABASE -c $SF_DATABASE.SCHEMACHANGE.CHANGE_HISTORY --create-change-history-table --verbose
Thank you @carley-citian for responding.
Regarding the schemachange version, you reported the issue in version 3.6.1. I was asking if you could run the pipelines using the previous release of schemachange (say 3.6.0, 3.5.4 etc). 3.6.1 is the latest release. 3.6.0 was the previous release. Trying to rule out the issue between 3.6.0 and 3.6.1. Could you run your pipeline on a prior version of schemachange?
How are you pushing your changes to Gitlab Project ? through the gitlab web ui or pushed locally through an IDE like Visual Studio Code or some text editor. If using text editor, you can tell whether the text file is using LF (line feed aka unix style new line character) or CRLF(Carriage Return - Line Feed aka Windows style new line character. Also, as a test, could you test with an additional new line after the last line in your SQL and report back the results of your test?
Please share your test results to help narrow down the root cause.
Another thing that you could check is the exact failure as recorded in the query history.
I'm experiencing the same issue. Was on schemachange 3.6.0 for a while without any problem, and now this has popped up, without any changes to my venv. Problem is the same on 3.6.0 and also 3.6.1 after upgrading. Tried with snowflake-connector-python versions 3.5.0 and also when upgraded to 3.8.1.
Each line ends with CR/LF.
Several commands in the script work fine, then it fails when trying to create a SQL Language Stored Procedure that starts with a TRUNCATE TABLE statement. It is failing on the semicolon that terminates that statement. I double-checked by moving the semicolon to a separate line, same issue. The create procedure works fine in other contexts, only fails in SCHEMACHANGE.
This is an extremely common code pattern for deployment, I have a standard template for these statements and stored procedures, and they have all deployed fine in the past.
Typically when I've seen errors like this in Schemachange, it's because the entire deployment script ends in a comment, but that's not the case here. I removed all comments from the script and the problem persists.
I am unable to reproduce the error. @carley-citian Could you share the gitlab yaml file ?
Can you pin the schemachange version to the last working version in your runner aka to a time before you started facing this error ?
The
https://www.getcensus.com/blog/how-to-solve-the-unexpected-eof-syntax-error-in-snowflake
According to the snowflake python connector docs has method execute_string and execute_stream that have the "remove_comments" parameter. In order to address the comment in the last line, we will have the ability to remove comments from the SQL statement.
Will discuss and see when we can include this.
I found a cause and a workaround. When creating a SQL Stored Procedure, the outer BEGIN/END commands must be encapsulated. This can be done with $$ or with single quotes (but then all single quotes in the procedure itself have to be doubled).
I had found that in VSCode using the Snowflake plugin, I no longer needed this encapsulation to successfully execute a CREATE PROCEDURE script. But the same script does require it when deploying via schemachange. This is unfortunate, using single quotes to encapsulate means you must modify the code itself. Using $$ is better, but it throws off the syntax coloring in VSCode.
Thank you @marczellick-belonghealth for identifying the root cause. Regarding formatting of the stored proc in Visual Studio Code, the latest version of snowflake extension does seem to format the stored procedure code. See screenshot here.
Describe the bug Hi! I am getting the following error everytime I try to run my Gitlab CI/CD pipeline with schemachange on a new SQL migrations file:
I have tested the same exact code in a worksheet on Snowflake and can confirm it works. I also have run migration files that worked on my pipeline before, but recently this error is popping up for every file I try to run on the pipeline.
Here are the final few lines of my file for context -- there should be no issue with the code:
To Reproduce Steps to reproduce the behavior:
Expected behavior Expected behavior would be that the code changes are run on snowflake this step, and committed.
Schemachange (please complete the following information):
Additional context Here is the query tag given to my run:
SQL query: ALTER SESSION SET QUERY_TAG = 'schemachange 3.6.1;V1.0.3__skeleton_basedata_schema.sql'
Also here is a screenshot of the error I am getting in the log, seems like the trailing semicolon is getting cut off for some reason if that is helpful?:
I have been committing the changes manually on Snowflake worksheets for now, but would love to get my pipeline up and working again soon if anyone can help!