How to Fix GitLab CI Not Fail When There Is an SQL Error

In a GitLab CI/CD pipeline, it’s crucial to ensure that jobs fail correctly when SQL errors occur. However, the default behavior of a job might not always align with your expectations. Sometimes, even though an SQL error is triggered, the GitLab CI pipeline continues without failing. This can cause confusion and lead to undetected issues in your workflow.

Scenario Overview

Imagine you have an SQL script that is executed in a GitLab CI pipeline via a shell script. The SQL script intentionally contains an error, and you want the GitLab CI job to fail when this error occurs. However, even though the SQL error is thrown, the job continues successfully in the pipeline, which isn’t the desired behavior.

Here’s the breakdown of the files involved:

Toto.sql

select 1 from idontexist -- generate sql error

This script attempts to select data from a non-existent table (idontexist), which will trigger an SQL error.

Toto.sh

#!/bin/bash
sqlplus -s username/password@database @toto.sql

This shell script runs the SQL script using SQL*Plus, which connects to the database and executes toto.sql.

Gitlab-ci.yml

script:
  - |
    chmod +x ./toto.sh
    ./toto.sh

This is the GitLab CI configuration file, where we specify that toto.sh should be executed in the pipeline.

The Issue

Despite the fact that the SQL script generates an error (ORA-00942: table or view does not exist), the GitLab CI job continues without failing. The job result is marked as “succeeded,” which should not be the case when an error occurs.

Why Does This Happen

The root cause of this issue is that the SQL command executed by SQLPlus doesn’t propagate the error back to the shell script properly. By default, SQLPlus doesn’t exit with a non-zero exit code when an SQL error occurs, unless it is explicitly configured to do so.

As a result, even though the error is logged, the shell script still exits with a status of 0 (indicating success), leading GitLab CI to mark the job as successful. We need to adjust this behavior so that the error is properly propagated, and the job fails when an error occurs.

How to Fix It

To make the GitLab CI job fail when an SQL error occurs, we need to handle the exit status of the SQL command properly within the shell script. Let’s go through a few methods to achieve this.

Modify the toto.sh Script to Exit on SQL Error

The first step is to explicitly check the exit status of the sqlplus command after executing the SQL script. If an error occurs, we can make sure the script exits with a non-zero status, causing the GitLab job to fail.

Updated toto.sh

#!/bin/bash
sqlplus -s username/password@database @toto.sql
# Check the exit status of sqlplus command
if [ $? -ne 0 ]; then
  echo "SQL error occurred. Failing the job."
  exit 1  # Exit with a non-zero status to fail the job
fi

Explanation:

• The $? variable holds the exit status of the last executed command (in this case, sqlplus).
• If sqlplus fails (i.e., returns a non-zero exit code), the script will print an error message and exit with status 1. This triggers a failure in the GitLab CI pipeline.

Adding More SQL Error Detection

You may want to add further error detection for more specific SQL errors. For instance, if you encounter an error like ORA-00942, which indicates a missing table or view, you might want to log a more detailed message to help troubleshoot the issue.

Here’s an enhanced version of the toto.sh script that can handle specific SQL errors more gracefully:

Enhanced toto.sh

#!/bin/bash
sqlplus -s username/password@database @toto.sql
sql_exit_code=$?

if [ $sql_exit_code -ne 0 ]; then
  echo "SQL error occurred with exit code: $sql_exit_code"
  
  # Additional handling based on specific error codes (optional)
  if [[ $sql_exit_code -eq 942 ]]; then
    echo "ORA-00942: table or view does not exist. Please check your query."
  fi
  
  exit 1  # Fail the job
fi

Explanation

• We capture the exit code of sqlplus in the sql_exit_code variable.
• If the exit code is non-zero, we print a message with the specific error code.
• You can customize this section to handle different types of SQL errors and provide more user-friendly logs based on the error code.

Update .gitlab-ci.yml for Error Handling

Another improvement can be made in the .gitlab-ci.yml file itself. Using the set -e option in Bash ensures that the script exits immediately when any command fails. This is useful when you have multiple commands in the script, and you want the pipeline to fail immediately when any of them fails.

Updated .gitlab-ci.yml

script:
  - |
    set -e  # Exit immediately if any command fails
    chmod +x ./toto.sh
    ./toto.sh

Explanation:

• The set -e command will make the script exit immediately if any command within the script returns a non-zero exit code, causing the GitLab CI job to fail early.

Final Thought

Ensuring that your GitLab CI job fails correctly when an SQL error occurs is crucial for maintaining a reliable pipeline. By modifying your shell script to properly check for SQL errors and handling them appropriately, you can ensure that the pipeline reflects the failure as expected.