Force initilization after changing admin password

[mathiear]

Hi,

I want to deploy proxysql and change the admin password.

It is my understanding that it's currently impossible without stopping and running service proxysql initial manually before launching the playbook again.

Otherwise, the next steps fail with

task path: proxysql/tasks/config/proxysql_global_variables.yml:5
failed: [vm-ubuntu-01] (item={'key': u'admin-mysql_ifaces', 'value': u'127.0.0.1:6032'}) => {"changed": false, "item": {"key": "admin-mysql_ifaces", "value": "127.0.0.1:6032"}, "msg": "unable to connect to ProxySQL A
dmin Module.. (1045, \"ProxySQL Error: Access denied for user 'admin'@'' (using password: YES)\")"}
failed: [vm-ubuntu-01] (item={'key': u'admin-admin_credentials', 'value': u'admin:*****'}) => {"changed": false, "item": {"key": "admin-admin_credentials", "value": "admin:****"}, "msg": "unable to connect to Pr
oxySQL Admin Module.. (1045, \"ProxySQL Error: Access denied for user 'admin'@'' (using password: YES)\")"}

The only workarounds I could find were:

• inserting that in the tasks/config.yml playbook:

  template:
    dest: /etc/proxysql.cnf
    group: root
    mode: 0400
    owner: root
    src: "{{ proxysql_proxysql_cnf_template }}"
  tags:
    - skip_ansible_lint

- name: Ensure that ProxySQL is stopped before initializing
  service:
    name: proxysql
    state: stopped

- name: Ensure that ProxySQL is initialized
  command: service proxysql initial

- name: Ensure that ProxySQL is running
  service:
    enabled: "{{ proxysql_service_enabled_state | default('yes') }}"
    name: proxysql
    state: "{{ proxysql_service_state | default('started') }}"

• inserting this kind of file (during the playbook play) to prevent the service being started after installation: cat /usr/sbin/policy-rc.d

# This return code prevent systemctl from starting service when
# dpkg calls invoke-rc.d
exit 101

Do you have any other idea?

Cheers, Arnaud

[timorunge]

Hey Arnaud,

I've tested this behaviour before and I can confirm that the following is working fine:

• Bring up a new VM / Docker container without any previous installation of ProxySQL.
• Before the first run of Ansible ensure that you're setting proxysql_login_admin_user and proxysql_login_admin_password to the desired values.

I've re-tested the same with different admin credentials, this is the diff against the current master:

diff --git a/tests/docker/docker-entrypoint.sh b/tests/docker/docker-entrypoint.sh
index 76a62aa..7774015 100755
--- a/tests/docker/docker-entrypoint.sh
+++ b/tests/docker/docker-entrypoint.sh
@@ -2,7 +2,7 @@
 set -e
 
 printf "[defaults]\nroles_path=/etc/ansible/roles\n" > /ansible/ansible.cfg
-printf "[client]\nhost=127.0.0.1\npassword=admin\nport=6032\nuser=admin\n" > /ansible/.my.cnf
+printf "[client]\nhost=127.0.0.1\npassword=admin-Woo7OhRe\nport=6032\nuser=admin-aexo8Aig\n" > /ansible/.my.cnf
 
 test -z ${proxysql_use_official_repo} && \
   echo "Missing environment variable: proxysql_use_official_repo" && exit 1
diff --git a/tests/docker_test_runner.travis.yml b/tests/docker_test_runner.travis.yml
index 2d8d814..6c12357 100644
--- a/tests/docker_test_runner.travis.yml
+++ b/tests/docker_test_runner.travis.yml
@@ -31,6 +31,9 @@ docker_container_environments:
   "1_4_12":
     proxysql_use_official_repo: False
     proxysql_version: 1.4.12
+  "1_4_13":
+    proxysql_use_official_repo: False
+    proxysql_version: 1.4.13
 
 docker_container_volumes:
   "__PATH__":
diff --git a/tests/docker_test_runner.yml b/tests/docker_test_runner.yml
index 4974bb6..bc1275d 100644
--- a/tests/docker_test_runner.yml
+++ b/tests/docker_test_runner.yml
@@ -34,6 +34,9 @@ docker_container_environments:
   "1_4_12":
     proxysql_use_official_repo: False
     proxysql_version: 1.4.12
+  "1_4_13":
+    proxysql_use_official_repo: False
+    proxysql_version: 1.4.13
 
 docker_container_volumes:
   "__PATH__":
diff --git a/tests/test.yml b/tests/test.yml
index 06c7dd2..192db3f 100644
--- a/tests/test.yml
+++ b/tests/test.yml
@@ -7,9 +7,9 @@
     proxysql_service_enabled: True
     proxysql_use_official_repo: True
     proxysql_login_admin_host: 127.0.0.1
-    proxysql_login_admin_password: admin
+    proxysql_login_admin_password: admin-Woo7OhRe
     proxysql_login_admin_port: 6032
-    proxysql_login_admin_user: admin
+    proxysql_login_admin_user: admin-aexo8Aig
     proxysql_global_variables:
       login_host: "{{ proxysql_login_admin_host }}"
       login_password: "{{ proxysql_login_admin_password }}"

And can confirm that this is working on ProxySQL 1.4.12 and 1.4.13.

What's happening in the background (in a nutshell):

• The module is installing ProxySQL.
• The module is generating /etc/proxysql.cnf.
• The module is starting ProxySQL.
• Since ProxySQL was not up and running before it will use the values from the pre-generated configuration file and use them for the initialisation.

What is - indeed - problematic is a change of the admin credentials after the initial setup.

[mathiear]

I confirm it's working with proxysql_use_official_repo: False

I forgot to mention, i edited your role to install proxysql from percona repo (the same way you do it for the official repo)

So I believe there is a difference between installing the package with the URL or with a repo.

With a repo (at least pecona, I haven't tested the official yet), proxysql is started just after its installation - hence the need for the workaround.

[timorunge]

It's also working with the installation from the official repositories (repo.proxysql.com).

It might be worth to adopt the Ansible code also to be able to install from a different- non official repository. If you can share the code for the installation from the Precona repository I can take a look at it. :)

[mathiear]

Here is my code:

diff --git a/defaults/main.yml b/defaults/main.yml
index 60b9d9f..e07117d 100644
--- a/defaults/main.yml
+++ b/defaults/main.yml
@@ -14,12 +14,17 @@ proxysql_restart_on_static_variables_change: True
 # Use the official ProxySQL repository. If set to `False` the module will
 # automatically download the defined version as a package.
 # Type: Bool
-proxysql_use_official_repo: True
+proxysql_use_official_repo: False
+
+# Use the Percona repository. If set to `False` the module will
+# automatically download the defined version as a package.
+# Type: Bool
+proxysql_use_percona_repo: True
 
 # The ProxySQL version which should be installed if not using the ProxySQL
 # repository.
 # Type: Int
-proxysql_version: 1.4.12
+proxysql_version: 1.4.13
 
 # Configuration
 
diff --git a/tasks/config.yml b/tasks/config.yml
index ec4051f..c08b234 100644
--- a/tasks/config.yml
+++ b/tasks/config.yml
@@ -10,6 +10,14 @@
   tags:
     - skip_ansible_lint
 
+- name: Ensure that ProxySQL is stopped before initializing
+  service:
+    name: proxysql
+    state: stopped
+
+- name: Ensure that ProxySQL is initialized
+  command: service proxysql initial
+
 - name: Ensure that ProxySQL is running
   service:
     enabled: "{{ proxysql_service_enabled_state | default('yes') }}"
diff --git a/tasks/install.yml b/tasks/install.yml
index 3f5404f..2dec1bf 100644
--- a/tasks/install.yml
+++ b/tasks/install.yml
@@ -21,7 +21,7 @@
   until: result is succeeded
   when: proxysql_pip_dependency_pkgs
 
-- name: Install ProxySQL from repository
+- name: Install ProxySQL from official repository
   package:
     name: "{{ proxysql_pkgs }}"
     state: present
@@ -32,6 +32,17 @@
   until: result is succeeded
   when: proxysql_use_official_repo
 
+- name: Install ProxySQL from Percona repository
+  package:
+    name: "{{ proxysql_pkgs }}"
+    state: present
+    update_cache: "{{ omit if ((ansible_pkg_mgr == 'dnf') and (ansible_version is version('2.7', '<'))) else 'yes' }}"
+  delay: 10
+  register: result
+  retries: 3
+  until: result is succeeded
+  when: proxysql_use_percona_repo
+
 - name: Install ProxySQL from DEB package
   apt:
     deb: "{{ proxysql_pkg_url }}"
@@ -42,6 +53,7 @@
   until: result is succeeded
   when: >
         (not proxysql_use_official_repo) and
+        (not proxysql_use_percona_repo) and
         (ansible_os_family == "Debian")
 
 - name: Install ProxySQL from YUM package
@@ -54,4 +66,5 @@
   until: result is succeeded
   when: >
         (not proxysql_use_official_repo) and
+        (not proxysql_use_percona_repo) and
         (ansible_os_family == "RedHat")
diff --git a/tasks/main.yml b/tasks/main.yml
index ff7cf34..c5bc2e6 100644
--- a/tasks/main.yml
+++ b/tasks/main.yml
@@ -18,6 +18,8 @@
 
 - include_tasks: proxysql-official-repo.yml
 
+- include_tasks: percona-repo.yml
+
 - include_tasks: install.yml
 
 - include_tasks: config.yml
diff --git a/tasks/percona-repo.yml b/tasks/percona-repo.yml
new file mode 100644
index 0000000..57f1956
--- /dev/null
+++ b/tasks/percona-repo.yml
@@ -0,0 +1,16 @@
+---
+- name: Install Percona repo from deb package
+  apt:
+    deb: 'https://repo.percona.com/apt/percona-release_latest.{{ ansible_distribution_release | lower }}_all.deb'
+    state: present
+  when: >
+        (proxysql_use_percona_repo) and
+        (ansible_os_family == "Debian")
+
+- name: Install Percona repo from rpm package
+  yum:
+    name: 'https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-{{ __percona_repo_version }}.noarch.rpm'
+    state: present
+  when: >
+        (proxysql_use_percona_repo) and
+        (ansible_os_family == "RedHat")
diff --git a/templates/proxysql.cnf.j2 b/templates/proxysql.cnf.j2
index 92066d5..e7ebc70 100644
--- a/templates/proxysql.cnf.j2
+++ b/templates/proxysql.cnf.j2
@@ -1,3 +1,4 @@
+#jinja2: lstrip_blocks: True
 # {{ ansible_managed }}
 # file proxysql.cfg
 # proxysql.cfg in GitHub: https://github.com/sysown/proxysql/blob/master/etc/proxysql.cnf
diff --git a/vars/RedHat.yml b/vars/RedHat.yml
index b1a41e3..cec4091 100644
--- a/vars/RedHat.yml
+++ b/vars/RedHat.yml
@@ -1,4 +1,5 @@
 ---
+__percona_repo_version: 0.1-6
 
 # Type: Str
 proxysql_distribution: "{{ ansible_distribution | lower }}"

[timorunge]

Based on your code I've done some bigger changes which are released now in the 0.3.0 release.

It should basically cover everything what you've been facing. It would be appreciated if you can take a look. :)