jenkins-sql.md

Table definition

CREATE TABLE countries (
  id INT NOT NULL AUTO_INCREMENT,
  iso VARCHAR(2) NOT NULL,
  iso3 VARCHAR(3) NOT NULL,
  iso_numeric INT NOT NULL,
  country_name VARCHAR(64) NOT NULL,
  capital VARCHAR(64) NOT NULL,
  continent_code VARCHAR(2) NOT NULL,
  currency_code VARCHAR(3) NOT NULL,
  PRIMARY KEY(Id)
);

Data

INSERT INTO countries
  (iso, iso3, iso_numeric, country_name, capital, continent_code, currency_code)
VALUES
  ('AU', 'AUS', 36, 'Australia', 'Canberra', 'OC', 'AUD'),
  ('DE', 'DEU', 276, 'Germany', 'Berlin', 'EU', 'EUR'),
  ('US', 'USA', 840, 'United States', 'Washington', 'NA', 'USD')
;

Jenkinsfile version 1

pipeline {
  agent { label 'linux' }
  stages {
    stage('query') {
      steps {
        sh(script:'''
          mysql -N -u jenkins -ppassword1 -h 192.168.32.11 my_app -e "select json_object('iso',iso,'country_name',country_name,'currency_code',currency_code) from countries where iso='US'"
        ''')
      }
    }
  }
}

Jenkinsfile version 2

pipeline {
  agent { label 'linux'}
  environment {
    MARIADB_CREDS=credentials('mariadb-credentials')
  }
  stages {
    stage('query') {
      steps {
        sh(script:'''
          mysql -N -u $MARIADB_CREDS_USR -p$MARIADB_CREDS_PSW -h 192.168.32.11 my_app -e "select json_object('iso',iso,'country_name',country_name,'currency_code',currency_code) from countries where iso='US'"
        ''')
      }
    }
  }
}

Jenkinsfile version 3

pipeline {
  agent { label 'linux'}
  environment {
    MARIADB_CREDS=credentials('mariadb-credentials')
  }
  stages {
    stage('query') {
      steps {
        sh(script:'''
          echo -e "[client]\nuser=$MARIADB_CREDS_USR\npassword=$MARIADB_CREDS_PSW\nhost=192.168.32.11\ndatabase=my_app" | mysql --defaults-file=/dev/stdin -N -e "select json_object('iso',iso,'country_name',country_name,'currency_code',currency_code) from countries where iso='US'"
        ''')
      }
    }
  }
}

Jenkinsfile version 4

pipeline {
  agent { label 'linux'}
  environment {
    MARIADB_CREDS=credentials('mariadb-credentials')
  }
  parameters {
    choice(name: 'ISO_CODE', choices: ['US', 'AU', 'DE'], description: 'Select ISO code')
  }
  stages {
    stage('query') {
      steps {
        sh(script:'''
          echo -e "[client]\nuser=$MARIADB_CREDS_USR\npassword=$MARIADB_CREDS_PSW\nhost=192.168.32.11\ndatabase=my_app" | mysql --defaults-file=/dev/stdin -N -e "select json_object('iso',iso,'country_name',country_name,'currency_code',currency_code) from countries where iso=\\\"$ISO_CODE\\\""
        ''')
      }
    }
  }
}

Jenkinsfile version 5

pipeline {
  agent { label 'linux'}
  environment {
    MARIADB_CREDS=credentials('mariadb-credentials')
  }
  parameters {
    choice(name: 'ISO_CODE', choices: ['US', 'AU', 'DE'], description: 'Select ISO code')
  }
  stages {
    stage('query') {
      steps {
        sh(script:'''
          echo -e "[client]\nuser=$MARIADB_CREDS_USR\npassword=$MARIADB_CREDS_PSW\nhost=192.168.32.11\ndatabase=my_app" | mysql --defaults-file=/dev/stdin -N -e "select json_object('iso',iso,'country_name',country_name,'currency_code',currency_code) from countries where iso=\\\"$ISO_CODE\\\"" | jq -r ".country_name"
        ''')
      }
    }
  }
}

failed with
error: Found option without preceding group in config file: /dev/stdin at line: 1

Hi @darinpope,
I got the same error as kh-rawad and my pipeline looks something like this:

node('test') {

    // Some other stages doing stuff...

    stage('SetUpDatabase') {
        withCredentials([usernamePassword(usernamePassword(credentialsId: db_id, passwordVariable: 'DB_PSW', usernameVariable:'DB_USER'),]) {
            sh ' echo -e \\\'_1=should_be_printed_from_prepare_script\\\' | prepare_script ./database.sql'
            sh ' echo \\\'[client]\\nuser=$DB_USER\\npassword="$DB_PSW"\\\' | mysql --defaults-file="/dev/stdin" <  ./database.sql'
        }
    }
}

The prepare_script should also read from /dev/stdin and prepare the sql script.
It looks like both, the prepare_script and mysql, can't read from /dev/stdin.
Did I miss something or made a misstake?

I look forward to your reply.

changing shell to bash worked for me
https://devops.stackexchange.com/questions/13650/mysqld-error-found-option-without-preceding-group-in-config-file-root-my-cn

node('node') {

    stage('Something') {
        withCredentials([usernamePassword(usernamePassword(credentialsId: db_id, passwordVariable: 'DB_PSW', usernameVariable:'DB_USER'),]) {
                   sh '''
                   /bin/bash -c '<YOUR SCRIPT HERE>'
                   '''
        }
    }
}

Do this for every line of command
Setting up shebang at the start doesn't help even though it changes the shell
Eg:

sh '''
   #!/bin/bash
   COMMAND1
   COMMAND2
   ........
'''