proxysql-admin-common

#!/bin/bash
# This script contains functions common to proxysql-admin related scripts.
# (currently only Percona XtraDB cluster in combination with ProxySQL is supported)
# Version 2.0
###############################################################################################

# This program is copyright 2016-2022 Percona LLC and/or its affiliates.
#
# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
# MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
#
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation, version 2 or later
#
# You should have received a copy of the GNU General Public License version 2
# along with this program; if not, write to the Free Software Foundation, Inc.,
# 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA.

# shellcheck disable=SC2181

#-------------------------------------------------------------------------------
#
# Step 1 : Bash internal configuration
#


#-------------------------------------------------------------------------------
#
# Step 2 : Global variables
#


#-------------------------------------------------------------------------------
#
# Step 3 : Helper functions
#


# Executes a SQL query with the (fully) specified server
#
# Globals:
#   DEBUG
#   TIMEOUT
#
# Arguments:
#   1: lineno
#   2: the name of the user
#   3: the user's password
#   4: the hostname of the server
#   5: the port used to connect to the server
#   6: the query to be run
#   7: (optional) arguments to the mysql client
#   8: (optional) additional options, space separated
#      Available options:
#       "hide_output"
#         This will not show the output of the query when DEBUG is set.
#         Used to stop the display of sensitve information (such as passwords)
#         from being displayed when debugging.
#
function exec_sql() {
  local lineno=$1
  local user=$2
  local password=$3
  local hostname=$4
  local port=$5
  local query=$6
  local args=""
  local more_options=""
  local retvalue
  local retoutput
  local default_auth=""
  local defaults=""

  if [[ $# -ge 7 ]]; then
    args=$7
  fi

  if [[ $# -ge 8 ]]; then
    more_options=$8
  fi

  debug "$lineno" "exec_sql : $user@$hostname:$port ==> $query"

  if [[ $MYSQL_CLIENT_VERSION == "8.0" ]]; then
    default_auth="default-auth=mysql_native_password"
  fi

  defaults=$(printf '[client]\nuser=%s\npassword="%s"\nhost=%s\nport=%s\nconnect-timeout=%s\n%s' \
    "${user}" \
    "${password}" \
    "${hostname}" \
    "${port}" \
    "${TIMEOUT}" \
    "${default_auth}"
  )

  # shellcheck disable=SC2086
  if [[ $USE_STDIN_FOR_CREDENTIALS -eq 1 ]]; then
    retoutput=$(printf "%s" "${defaults}" | mysql --defaults-file=/dev/stdin --protocol=tcp --unbuffered --batch --silent ${args} -e "$query")
    retvalue=$?
  else
    retoutput=$(mysql --defaults-file=<(echo "${defaults}") --protocol=tcp --unbuffered --batch --silent ${args} -e "$query")
    retvalue=$?
  fi

  if [[ $DEBUG -eq 1 ]]; then
    local number_of_newlines=0
    local dbgoutput=$retoutput

    if [[ " $more_options " =~ [[:space:]]hide_output[[:space:]] ]]; then
      dbgoutput="**** data hidden ****"
    fi

    if [[ -n $dbgoutput ]]; then
      number_of_newlines=$(printf "%s" "${dbgoutput}" | wc -l)
    fi

    if [[ $retvalue -ne 0 ]]; then
      debug "" "--> query failed $retvalue"
    elif [[ -z $dbgoutput ]]; then
      debug "" "--> query returned $retvalue : <query returned no data>"
    elif [[ ${number_of_newlines} -eq 0 ]]; then
      debug "" "--> query returned $retvalue : ${dbgoutput}"
    else
      debug "" "--> query returned $retvalue : <data follows>"
      printf "%s\n" "${dbgoutput}" | while IFS= read -r line; do
        debug "" "----> $line"
      done
    fi
  fi

  printf "%s" "${retoutput}"
  return $retvalue
}


# Executes a SQL query on proxysql
#
# Globals:
#   PROXYSQL_USERNAME
#   PROXYSQL_PASSWORD
#   PROXYSQL_HOSTNAME
#   PROXYSQL_PORT
#
# Arguments:
#   1: lineno
#   2: The SQL query
#   3: (optional) Additional arguments to the mysql client for the query
#   4: (optional) more options, see exec_sql
#
function proxysql_exec() {
  local lineno=$1
  local query=$2
  local args=""
  local more_options=""

  if [[ $# -ge 3 ]]; then
    args=$3
  fi

  if [[ -z $args ]]; then
    args="--skip-column_names"
  fi

  if [[ $# -ge 4 ]]; then
    more_options=$4
  fi

  exec_sql "$lineno" "$PROXYSQL_USERNAME" "$PROXYSQL_PASSWORD" \
           "$PROXYSQL_HOSTNAME" "$PROXYSQL_PORT" \
           "$query" "$args" "$more_options"

  return $?
}


# Executes a SQL query on a specific node in the cluster
#
# Globals:
#   CLUSTER_USERNAME
#   CLUSTER_PASSWORD
#
# Arguments:
#   1: lineno
#   2: cluster host
#   3: cluster port
#   4: The SQL query
#   5: Additional arguments to the mysql client for the query
#   6: (optional) more options, see exec_sql
#
function cluster_exec() {
  local lineno=$1
  local cluster_host=$2
  local cluster_port=$3
  local query=$4
  local args=""
  local more_options=""

  if [[ $# -ge 5 ]]; then
    args=$5
  fi

  if [[ $# -ge 6 ]]; then
    more_options=$6
  fi

  exec_sql "$lineno" "$CLUSTER_USERNAME" "$CLUSTER_PASSWORD" \
           "$cluster_host" "$cluster_port" \
           "$query" "$args" "$more_options"

  return $?
}


# Executes a SQL query on the CLUSTER_HOSTNAME/CLUSTER_PORT
# specified in the config file.
#
# Globals:
#   CLUSTER_USERNAME
#   CLUSTER_PASSWORD
#   CLUSTER_HOSTNAME
#   CLUSTER_PORT
#
# Arguments:
#   1: lineno
#   2: The SQL query
#   3: Additional arguments to the mysql client for the query
#   4: (optional) more options, see exec_sql
#
function mysql_exec() {
  local lineno=$1
  local query=$2
  local args=""
  local more_options=""

  if [[ $# -ge 3 ]]; then
    args=$3
  fi

  if [[ $# -ge 4 ]]; then
    more_options=$4
  fi

  cluster_exec "$lineno" "$CLUSTER_HOSTNAME" "$CLUSTER_PORT" \
           "$query" "$args" "$more_options"

  return $?
}


# Executes a SQL query on a cluster ndde with the monitor credentials
#
# Globals:
#   CLUSTER_HOSTNAME
#   CLUSTER_PORT
#
# Arguments:
#   1: lineno
#   2: The monitor username
#   3: The monitor password
#   4: Additional arguments to the mysql client for the query
#   5: The SQL query
#   6: (optional) more options, see exec_sql
#
function monitor_exec() {
  local lineno=$1
  local user=$2
  local password=$3
  local args=$4
  local query=$5
  local more_options=""

  if [[ $# -ge 6 ]]; then
    more_options=$7
  fi

  exec_sql "$lineno" "$user" "$password" \
           "$CLUSTER_HOSTNAME" "$CLUSTER_PORT" \
           "$query" "$args" "$more_options"

  return $?
}


# This will move the configuration from memory to the runtime (load)
# and from memory to disk (save)
#
# Globals:
#   None
#
# Arguments:
#   1: the proxysql data that is being loaded and saved
#      (for example "MYSQL USERS" or "MYSQL SERVERS").
#   2: the lineno where this function was invoked
#   3: (optional) call "SAVE $1 FROM RUNTIME" after
#      loading data to runtime.  This is done for
#      MYSQL USERS to load the encrypted version of the
#      password field.
#
# This function will exit the program if an error occurs while
# loaded to runtime or saving to disk.
#
function proxysql_load_to_runtime_save_to_disk() {
  local data_type=$1
  local lineno=$2
  local reload_from_runtime=0

  if [[ $# -ge 3 ]]; then
    reload_from_runtime=$3
  fi

  proxysql_exec "$LINENO" "LOAD ${data_type} TO RUNTIME"
  check_cmd $? "$lineno" "Failed to load the ${data_type} configuration to runtime."\
                       "\n-- Please check the ProxySQL configuration and status."
  debug "$lineno" "Loaded ${data_type} to runtime"

  if [[ $reload_from_runtime -eq 1 ]]; then
    # This has a specific purpose for the MYSQL USERS
    # This will cause the password field to be loaded with the encrypted version
    # of the password field
    proxysql_exec "$LINENO" "SAVE ${data_type} FROM RUNTIME"
    check_cmd $? "$lineno" "Failed to load the ${data_type} configuration from the runtime."\
                         "\n-- Please check the ProxySQL configuration and status."
    debug "$lineno" "Saved ${data_type} from runtime"
  fi

  proxysql_exec "$LINENO" "SAVE ${data_type} TO DISK;"
  check_cmd $? "$lineno" "Failed to save the ${data_type} configuration to disk."\
                       "\n-- Please check the ProxySQL configuration and status."
  debug "$lineno" "Saved ${data_type} to disk"
}


# Update mysql server version details in proxysql db
# Globals:
#   WRITER_HOSTGROUP_ID
#   CLUSTER_HOSTNAME (overwrites)
#   CLUSTER_PORT (overwrites)
#
# Arguments:
#   None
function update_mysql_version()
{
  local cluster_node

  # Find a cluster node that belongs to the cluster with $WRITER_HOSTGROUP_ID
  cluster_node=$(find_cluster_node "$WRITER_HOSTGROUP_ID")
  check_cmd $? "$LINENO" "Could not find a primary cluster node"

  # Reset the central cluster node (so that calls to mysql_exec) will
  # work with this new node, rather than the node in the config file
  CLUSTER_HOSTNAME=$(echo -e "$cluster_node" | cut -f1)
  CLUSTER_PORT=$(echo -e "$cluster_node" | cut -f2)
  cluster_connection_check

  proxysql_mysql_version_string=$(proxysql_exec "$LINENO" "select variable_value from global_variables where variable_name like 'mysql-server_version'")
  check_cmd $? "$LINENO"  "Failed to select the mysql-server_version variables from ProxySQL."\
                          "\n-- Please check the ProxySQL connection parameters and status."
  mysql_version_string=$(mysql_exec "$LINENO" "SELECT VERSION();" | tail -1 | cut -d'-' -f1 )
  check_cmd $? "$LINENO"  "Failed to select the mysql version info from Cluster node."\
                          "\n-- Please check the PXC connection parameters and status."
  if [[ $proxysql_mysql_version_string != "$mysql_version_string" ]]; then
    proxysql_exec "$LINENO" \
    "UPDATE global_variables
      SET variable_value='$mysql_version_string'
      WHERE variable_name='mysql-server_version';"
    check_cmd $? "$LINENO"  "Failed to set the mysql-server_version variables in ProxySQL."\
                          "\n-- Please check the ProxySQL connection parameters and status."
    echo "ProxySQL MySQL version changed to $mysql_version_string"
    proxysql_load_to_runtime_save_to_disk "MYSQL VARIABLES" $LINENO
  fi
}

# Adds an application user to ProxySQL
#
# Globals:
#   WRITER_HOSTGROUP_ID
#   CLUSTER_HOSTNAME (overwrites)
#   CLUSTER_PORT (overwrites)
#
# Arguments:
#   None
#
function adduser(){
  debug "$LINENO" "adduser ()"

  local cluster_node

  # Find a cluster node that belongs to the cluster with $WRITER_HOSTGROUP_ID
  cluster_node=$(find_cluster_node "$WRITER_HOSTGROUP_ID")
  check_cmd $? "$LINENO" "Could not find a primary cluster node"

  # Reset the central cluster node (so that calls to mysql_exec) will
  # work with this new node, rather than the node in the config file
  CLUSTER_HOSTNAME=$(echo -e "$cluster_node" | cut -f1)
  CLUSTER_PORT=$(echo -e "$cluster_node" | cut -f2)
  cluster_connection_check

  local cluster_app_write_username
  local cluster_app_write_password
  local safe_cluster_app_write_username
  local safe_cluster_app_write_password

  echo -e "\nAdding PXC application user to the ProxySQL database"
  echo -n "Enter the PXC application user name: "
  read -r cluster_app_write_username
  while [[ -z "$cluster_app_write_username" ]]
  do
    echo -n "No input entered. Enter the PXC application user name: "
    read -r cluster_app_write_username
  done
  safe_cluster_app_write_username=${cluster_app_write_username//\'/\'\'}

  read -r -s -p  "Enter the PXC application user password: " cluster_app_write_password
  while [[ -z "$cluster_app_write_password" ]]
  do
    read -r -s -p  "No input entered. Enter the PXC application user password: " cluster_app_write_password
  done
  safe_cluster_app_write_password=${cluster_app_write_password//\'/\'\'}

  # check to see if the user already exists
  local check_user
  local check_cluster_user

  check_user=$(proxysql_exec "$LINENO" \
                  "SELECT username
                   FROM mysql_users
                   WHERE username='$safe_cluster_app_write_username'")
  check_cmd $? "$LINENO" "Could not retrieve the user from ProxySQL."\
                       "\n-- Check the ProxySQL connection parameters and status."
  if [[ -n "$check_user" ]]; then
    error "$LINENO" "The application user '$cluster_app_write_username' already exists in ProxySQL."
    exit 1
  fi

  check_cluster_user=$(mysql_exec "$LINENO" "SELECT user,host FROM mysql.user where user='$safe_cluster_app_write_username'")
  check_cmd $? "$LINENO" "Could not retrieve the user from PXC."\
                       "\n-- Check the PXC connection parameters and status."
  if [[ -z "$check_cluster_user" ]]; then
    local check_param
    echo -e "\n\n"
    read -r -p "The application user '$cluster_app_write_username' does not exist in PXC. Would you like to proceed [y/n] ? " check_param
    case $check_param in
      y|Y)
        proxysql_exec "$LINENO" \
          "INSERT INTO mysql_users
            (username,password,active,default_hostgroup)
           VALUES
            ('$safe_cluster_app_write_username','$safe_cluster_app_write_password',1,$WRITER_HOSTGROUP_ID);"
        check_cmd $? "$LINENO" "Failed to add the PXC application user: '$cluster_app_write_username' to ProxySQL."\
                             "\n-- Please check the ProxySQL connection parameters and status."
        echo -e "\nPlease create the user ${BD}$cluster_app_write_username${NBD} in PXC to access the application through ProxySQL"
      ;;
      n|N)
        exit 0
      ;;
      *)
        error "" "Please type [y/n]!"
        exit 1
      ;;
    esac
  else
    proxysql_exec "$LINENO" \
      "INSERT INTO mysql_users
          (username,password,active,default_hostgroup)
       values
          ('$safe_cluster_app_write_username','$safe_cluster_app_write_password',1,$WRITER_HOSTGROUP_ID);"
    check_cmd $? "$LINENO" "Failed to add the PXC application user: '$cluster_app_write_username' to ProxySQL."\
                         "\n-- Please check the ProxySQL connection parameters and status."
  fi

  proxysql_load_to_runtime_save_to_disk "MYSQL USERS" "$LINENO" "1"
}

# Check to see if the cluster is in mysql_galera_hostgroups
#
# Globals:
#   None
#
# Arguments:
#   Parameter 1 : the writer hostgroup id
#
function cluster_in_proxysql_check() {
  local write_hg=$1

  # As scheduler does not use the mysql_galera_hostgroups table,
  # skip this check when scheduler is active
  local is_scheduler_active
  is_scheduler_active=$(proxysql_exec "$LINENO" \
                         "SELECT active
                          FROM runtime_scheduler
                          WHERE comment like '% hgW:${write_hg}%'")


  if [[ $is_scheduler_active -eq 1 ]]; then
     return
  fi

  # Check to see if mysql_galera_hostgroups already has an
  # entry for the worker hostgroup
  local hostgroup_in_use
  hostgroup_in_use=$(proxysql_exec "$LINENO" \
                      "SELECT count(*)
                       FROM mysql_galera_hostgroups
                       WHERE
                          writer_hostgroup = $write_hg")
  check_cmd $? "$LINENO" "Galera hostgroup retrieval failed."\
                       "\n-- Please check the ProxySQL connection parameters and status."

  # If there are no hostgroups with this writer hostgroup id
  # then return failure
  if [[ $hostgroup_in_use -eq 0 ]]; then
    error "$LINENO" "The cluster (with writer hostgroup:$WRITER_HOSTGROUP_ID) has not been configured in ProxySQL"
    exit 1
  fi
}

# Synchronizes the users between ProxySQL and PXC
#
# This function was created to auto sync all the existing users already
# in MySQL to proxySQL's mysql_users table.  As there is not much point
# in having users in ProxySQL that don't exist in MySQL, this function
# will delete any users from ProxySQL that were not found in MySQL.
#
# Going forward you can add/remove application users in MySQL then
# rerun proxysql-admin with the --syncusers switch to replicate the changes
# to ProxySQL.
#
# LIMITATIONS: Will not work properly in cases where the same user name
#              exists in MySQL with several hosts and different passwords.
#              This will cause ProxySQL to throw a "UNIQUE constraint failed"
#              error message.
#
# Globals:
#   WRITER_HOSTGROUP_ID
#   SYNCUSERS
#   CLUSTER_HOSTNAME (overwrites)
#   CLUSTER_PORT (overwrites)
#   SINGLE_SERVER
#
# Arguments:
#   None
#
function syncusers() {
  debug "$LINENO" "syncusers ()"

  local mysql_version
  local password_field
  local changes_made=0

  # If a single server has been specified with --server, use that server
  # and skip the cluster check (it may be a standalone node)
  if [[ -n $SINGLE_SERVER ]]; then
    local ws_address

    ws_address=$(separate_ip_port_from_address "$SINGLE_SERVER")
    CLUSTER_HOSTNAME=$(echo "$ws_address" | cut -d' ' -f1)
    CLUSTER_PORT=$(echo "$ws_address" | cut -d' ' -f2)

    echo -e "\nSyncing user accounts from server(${CLUSTER_HOSTNAME}:${CLUSTER_PORT}) to ProxySQL"

  else
    cluster_in_proxysql_check "${WRITER_HOSTGROUP_ID}"

    local cluster_node

    # Get current MySQL users, filter out header row and mysql.sys user
    # Find a cluster node that belongs to the cluster with $WRITER_HOSTGROUP_ID
    cluster_node=$(find_cluster_node "$WRITER_HOSTGROUP_ID")
    check_cmd $? "$LINENO" "Could not find a primary cluster node"

    # Reset the central cluster node (so that calls to mysql_exec) will
    # work with this new node, rather than the node in the config file
    CLUSTER_HOSTNAME=$(echo -e "$cluster_node" | cut -f1)
    CLUSTER_PORT=$(echo -e "$cluster_node" | cut -f2)

    echo -e "\nSyncing user accounts from PXC(${CLUSTER_HOSTNAME}:${CLUSTER_PORT}) to ProxySQL"

  fi

  # Check that we can connect to CLUSTER_HOSTNAME:CLUSTER_PORT
  cluster_connection_check

  mysql_version=$(mysql_exec "$LINENO" "SELECT VERSION();" | tail -1 | cut -d'.' -f1,2 )
  check_cmd $? "$LINENO" "Could not connect to the server."\
                       "\n-- Please check the server connection parameters and status."

  case $mysql_version in
    5.5 | 5.6)
      password_field="Password"
      ;;
    5.7 | 8.0)
      password_field="authentication_string"
      ;;
    10.0 | 10.1 | 10.2 | 10.3 | 10.4 | 10.5 | 10.6 | 10.11)
      password_field="Password"
      ;;
     *)
      error "$LINENO" "Unexpected database server version: ${mysql_version}"\
                    "\n-- This version of proxysql-admin needs to be updated."
      exit 1
      ;;
  esac

  # Filter out the internal system users
  mysql_users=$(mysql_exec "$LINENO" "SELECT User,${password_field} FROM mysql.user where ${password_field}!=''" "" "hide_output" |
                  grep -E -v "^(mysql.sys|mysql.session|mysql.infoschema|mysql.pxc)" |
                  sort |
                  uniq )
  check_cmd $? "$LINENO" "Failed to load the user list from the server."\
                       "\n-- Please check the server connection parameters and status."

  # Checking whether user is part of proxysql admin user list
  # Get current ProxySQL users and filter out header row
  proxysql_users=$(get_proxysql_users)

  # TEST FOR USERS THAT EXIST IN MYSQL BUT NOT IN PROXYSQL HERE AND ADD

  # Escape all backslashes here, because the read will evaluate
  # the escaped chars
  mysql_users=${mysql_users//\\/\\\\}

  # Declare associative arrays
  declare -A mysql_users_full_hash    # Hash of MySQL users
  declare -A proxysql_users_full_hash # Hash of ProxySQL users
  local username password

  # Populate MySQL users hash
  while read -r mysql_user; do
    if [[ -z $mysql_user ]]; then
      continue
    fi

    # Add MySQL user to hash
    username=$(echo "$mysql_user" | cut -f1)
    password=$(echo "$mysql_user" | cut -f2)
    mysql_users_full_hash["${username}"]="$password"

  done < <(printf "%s\n" "${mysql_users}")

  # Populate ProxySQL users hash
  while read -r proxysql_user; do
    if [[ -z $proxysql_user ]]; then
      continue
    fi

    # Extract ProxySQL username
    username=$(echo "$proxysql_user" | cut -f1)
    password=$(echo "$proxysql_user" | cut -f2)

    # Add ProxySQL user to hash
    proxysql_users_full_hash["${username}"]="$password"

  done < <(printf "%s\n" "${proxysql_users}")

  unset username
  unset password

  # Disable nounset to be able to search in the associative array
  set +o nounset

  # Check MySQL users against ProxySQL
  for user in "${!mysql_users_full_hash[@]}"; do

    username=${user}
    password="${mysql_users_full_hash[${username}]}"

    # Since we're using single quotes within the SQL statement, only need
    # to escape the single quotes for SQL
    password=${password//\'/\'\'}

    debug "$LINENO" "Processing MySQL user: '${username}'"

    # Check if the user exists in ProxySQL
    if [[ -n ${proxysql_users_full_hash[${username}]} ]]; then

      # Perform an exact match for the user and password
      if [[ "${proxysql_users_full_hash[${username}]}" == "${mysql_users_full_hash[${username}]}" ]]; then
        debug "$LINENO" "MySQL user: '${username}' already exists in ProxySQL, continuing"
        # User already exists in ProxySQL, we may skip this user
        continue

      else

        # If the same user exists in ProxySQL, but with a different password,
        # then remove all such users from ProxySQL

        echo "Removing existing user from ProxySQL: ${username}"
        proxysql_exec "$LINENO" "DELETE FROM mysql_users WHERE username='${username}' and default_hostgroup=$WRITER_HOSTGROUP_ID"
        check_cmd $? "$LINENO" "Failed to delete the user (${username}) from ProxySQL database."\
                             "\n-- Please check the ProxySQL connection parameters and status."
        proxysql_exec "$LINENO" "DELETE FROM mysql_query_rules WHERE username='${username}' and destination_hostgroup in($WRITER_HOSTGROUP_ID,$READER_HOSTGROUP_ID)"
        check_cmd $? "$LINENO" "Failed to delete the query rule for user (${username}) from ProxySQL database."\
                             "\n-- Please check the ProxySQL connection parameters and status."
        changes_made=1
      fi
    fi

    # Check for ProxySQL admin user
    local is_proxysql_admin_user
    is_proxysql_admin_user=$(proxysql_admin_user_check "${username}")
    if [[ $is_proxysql_admin_user -eq 1 ]]; then
      echo -e "\nNote : '${username}' is in proxysql admin user list, this user cannot be added to ProxySQL"\
              "\n-- (For more info, see https://github.com/sysown/proxysql/issues/709)"
    else
      # Verify that the user does not exist in ProxySQL
      check_user=$(proxysql_exec "$LINENO" "SELECT username from mysql_users where username='${username}'")
      if [[ -z $check_user ]]; then
        # It is confirmed that the user does not exist in ProxySQL, let's add it
        echo "Adding user to ProxySQL: ${username}"
        proxysql_exec "$LINENO" \
          "INSERT INTO mysql_users
           (username, password, active, default_hostgroup)
           VALUES
            ('${username}', '${password}', 1, $WRITER_HOSTGROUP_ID)"
        check_cmd $? "$LINENO" "Failed to add the user (${username}) from PXC to ProxySQL database."\
                             "\n-- Please check the ProxySQL connection parameters and status."
        if [[ $ADD_QUERY_RULE -eq 1 ]];then
          add_query_rule "${username}"
        fi
        changes_made=1
      else
        echo "Cannot add the user (${username}). The user (${username}) already exists in ProxySQL database with different hostgroup."
        check_user=""
      fi
    fi
  done # End of for loop

  # Remove users from ProxySQL that do not exist in MySQL
  if [[ $SYNCUSERS -eq 1 ]]; then

    # Get all proxysql users
    proxysql_users=$(get_proxysql_users)

    # Unset the hash
    declare -A proxysql_users_full_hash

    # Populate ProxySQL users hash
    while read -r proxysql_user; do
      if [[ -z $proxysql_user ]]; then
        continue
      fi

      # Extract ProxySQL username
      username=$(echo "$proxysql_user" | cut -f1)
      password=$(echo "$proxysql_user" | cut -f2)

      # Add ProxySQL user to hash
      proxysql_users_full_hash["${username}"]="$password"

    done < <(printf "%s\n" "${proxysql_users}")

    # Check ProxySQL users against MySQL
    for username in "${!proxysql_users_full_hash[@]}"; do
      debug "$LINENO" "Processing ProxySQL user(for removal):${username}"

      if [[ -z ${mysql_users_full_hash["${username}"]} ]]; then
        # Delete the ProxySQL user
        echo -e "\nRemoving (non-MySQL) user from ProxySQL: ${username}"
        proxysql_exec "$LINENO" "DELETE FROM mysql_users WHERE username='${username}' and default_hostgroup=$WRITER_HOSTGROUP_ID"
        check_cmd $? "$LINENO" "Failed to delete the user (${username}) from ProxySQL database."\
                             "\n-- Please check the ProxySQL connection parameters and status."
        proxysql_exec "$LINENO" "DELETE FROM mysql_query_rules WHERE username='${username}' and destination_hostgroup in($WRITER_HOSTGROUP_ID,$READER_HOSTGROUP_ID)"
        check_cmd $? "$LINENO" "Failed to delete the query rule for user (${username}) from ProxySQL database."\
                               "\n-- Please check the ProxySQL connection parameters and status."
        changes_made=1
      fi
    done
  fi

  # Save the configuration
  if [[ $changes_made -eq 1 ]]; then
    proxysql_load_to_runtime_save_to_disk "MYSQL USERS" $LINENO
    proxysql_load_to_runtime_save_to_disk "MYSQL QUERY RULES" $LINENO
  fi

  # Re-enable nounset
  set -o nounset
}

# Returns a list of users from the ProxySQL database
#
# Globals:
#   WRITER_HOSTGROUP_ID
#
# Arguments:
#   None
#
function get_proxysql_users() {
  local proxysql_users
  proxysql_users=$(proxysql_exec "$LINENO" \
                    "SELECT username,password
                      FROM mysql_users
                      WHERE password!='' AND default_hostgroup=$WRITER_HOSTGROUP_ID" "" "hide_output")
  check_cmd $? "$LINENO" "Failed to load user list from ProxySQL database."\
                       "\n-- Please check the ProxySQL connection parameters and status."
  proxysql_users=$(echo "$proxysql_users" |
                      sort |
                      uniq )
  printf "%s" "$proxysql_users"
}

# Checks if a user is a ProxySQL admin user
#
# Globals:
#   None
#
# Arguments:
#   1: the name of the use to be checked
#
# Outputs (to stdout):
#   1 if the user is a proxysql admin user
#   0 if the user is not a proxysql admin user
#
function proxysql_admin_user_check(){
  local userchk=$1
  local proxysql_admin_users
  local is_proxysql_admin_user

  mapfile -t proxysql_admin_users < <(proxysql_exec "$LINENO" \
                            "select variable_value
                             from global_variables
                             where variable_name like 'admin-%_credentials'" |
                              sed 's/;/\n/g'|
                              cut -d':' -f1 |
                              grep -v variable_value)
  # shellcheck disable=SC2199
  if [[ " ${proxysql_admin_users[@]} " =~ [[:space:]]${userchk}[[:space:]] ]]; then
    is_proxysql_admin_user=1
  else
    is_proxysql_admin_user=0
  fi
  printf "%s" "$is_proxysql_admin_user"
}


# Returns success if the native scheduler is in use.
# Returns success(0) if the native proxysql scheduler is in use.
# Else returns failure(1)
#
# In this case, the native scheduler is used if entries are
# found in mysql_galera_hostgroups, mysql_group_replication_hostgroups,
# or mysql_replication_hostgroups.
#
# Globals:
#   None
#
# Arguments:
#   Parameter 1:  line number
#
# Returns:
#   0 (success)   the native scheduler is being used
#   1 (failure)   the native scheduler is not being used
#   255 (failure) error, unable to connect to proxysql
#
function is_native_scheduler_in_use()
{
  local lineno=$1
  local count

  count=$(proxysql_exec "$LINENO" \
          "SELECT COUNT(*)
            FROM runtime_mysql_group_replication_hostgroups
            WHERE active=1")
  [[ $? -ne 0 ]] && return 255
  [[ $count -gt 0 ]] && return 0

  count=$(proxysql_exec "$LINENO" \
          "SELECT COUNT(*)
            FROM runtime_mysql_galera_hostgroups
            WHERE active=1")
  [[ $? -ne 0 ]] && return 255
  [[ $count -gt 0 ]] && return 0

  count=$(proxysql_exec "$LINENO" \
          "SELECT COUNT(*)
            FROM runtime_mysql_replication_hostgroups")
  [[ $? -ne 0 ]] && return 255
  [[ $count -gt 0 ]] && return 0

  return 1
}

function is_go_scheduler_in_use()
{
  local lineno=$1
  local count

  count=$(proxysql_exec "$LINENO" \
          "SELECT COUNT(*)
            FROM runtime_scheduler
            WHERE active=1 AND
            filename LIKE '%pxc_scheduler_handler'")
  [[ $? -ne 0 ]] && return 255
  [[ $count -gt 0 ]] && return 0
  return 1
}

function is_other_scheduler_in_use()
{
  local lineno=$1
  local count

  count=$(proxysql_exec "$LINENO" \
          "SELECT COUNT(*)
            FROM runtime_scheduler
            WHERE active=1 AND
            filename NOT LIKE '%pxc_scheduler_handler'")
  [[ $? -ne 0 ]] && return 255
  [[ $count -gt 0 ]] && return 0
  return 1
}


function native_scheduler_capability_check()
{
  local lineno=$1
  if is_go_scheduler_in_use "${lineno}"; then
    # TODO: better error messages
    error "${lineno}" "Unable to setup the native ProxySQL scheduler." \
                    "\n---- The Percona go scheduler is currently in use" \
                    "\n---- and must be removed."
    return 1;
  fi

  if is_other_scheduler_in_use "${lineno}"; then
    # TODO: better error messages
    error "${lineno}" "Unable to setup the native ProxySQL scheduler." \
                    "\n---- Another scheduler is currently in use" \
                    "\n---- and must be removed."
    return 1;
  fi
  return 0;
}

function go_scheduler_capability_check()
{
  local lineno=$1
  if is_native_scheduler_in_use "${lineno}"; then
    # TODO: better error messages
    error "${lineno}" "Unable to setup the Percona go scheduler." \
                    "\n---- The ProxySQL native scheduler is in use" \
                    "\n---- and must be removed."
    return 1;
  fi

  if is_other_scheduler_in_use "${lineno}"; then
    # TODO: better error messages
    error "${lineno}" "Unable to setup the Percona go scheduler." \
                    "\n---- Another scheduler is in use" \
                    "\n---- and must be removed."
    return 1;
  fi

  return 0;
}