"Get list of hosts for the root user" fails to run

[bakicdj]

command -NBe 'SELECT Host FROM mysql.user WHERE User = "root" ORDER BY (Host="localhost") ASC' fails to run at my vagrant machine. Haven't tried it in production yet.

my overrides:

mysql_root_password: P4UD6d1Vw8N%##FAI9ch5aY8!

mysql_databases:
  - { name: production, collation: utf8_general_ci, encoding: utf8, replicate: 0 }
  - { name: dev, collation: utf8_general_ci, encoding: utf8, replicate: 0 }

mysql_users:
  - { name: prod_usr, host: 127.0.0.1, password: FsEhhydhiWqPHT1icdzhSiG, priv: "production.*:ALL" }
  - { name: dev_usr, host: 127.0.0.1, password: v3tmz1AgfbFgxsN5Xm6wmwN, priv: "dev.*:ALL" }

[geerlingguy]

For some reason I started getting this too, on an Ubuntu 12.04 machine. This wasn't happening for me in the past... I wonder if there's a change in the official MySQL packages for Ubuntu?

@bakicdj - What OS are you using? Also, you may want to regenerate those passwords if they're used anywhere besides a local dev machine.

[geerlingguy]

See related: #36

[bakicdj]

No, the passwords are for local vm only. I'm gonna pay with it later this day.
OS: Mint 17

Edit: my dev env os is ubuntu 14.04 x64

[diogoviannaaraujo]

Im using just docker and i get this error every time building the machine, this is really blocking me for the last 2 days, must be some change on Ubuntu, because this ansible was working some time ago and now its not. What should i do?

Using ubuntu 14.04

My error:

ansible_1 | ERROR: 1064  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE user ADD column Show_view_priv enum('N','Y') CHARACTER SET utf8 NOT ' at line 1

[geerlingguy]

See related: #40

[geerlingguy]

@bakicdj and @diogoviannaaraujo — I can only reproduce this issue on Travis CI currently, so it's really hard to debug.

Can you please provide the output of your /var/log/mysql.err log? It may have some clues.

[diogoviannaaraujo]

nsible_1 | 151001  4:04:28 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
ansible_1 | 151001  4:04:28 [Note] Plugin 'FEDERATED' is disabled.
ansible_1 | 151001  4:04:28 InnoDB: The InnoDB memory heap is disabled
ansible_1 | 151001  4:04:28 InnoDB: Mutexes and rw_locks use GCC atomic builtins
ansible_1 | 151001  4:04:28 InnoDB: Compressed tables use zlib 1.2.8
ansible_1 | 151001  4:04:28 InnoDB: Using Linux native AIO
ansible_1 | 151001  4:04:28 InnoDB: Initializing buffer pool, size = 128.0M
ansible_1 | 151001  4:04:28 InnoDB: Completed initialization of buffer pool
ansible_1 | InnoDB: The first specified data file ./ibdata1 did not exist:
ansible_1 | InnoDB: a new database to be created!
ansible_1 | 151001  4:04:28  InnoDB: Setting file ./ibdata1 size to 10 MB
ansible_1 | InnoDB: Database physically writes the file full: wait...
ansible_1 | 151001  4:04:28  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
ansible_1 | InnoDB: Setting log file ./ib_logfile0 size to 5 MB
ansible_1 | InnoDB: Database physically writes the file full: wait...
ansible_1 | 151001  4:04:28  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
ansible_1 | InnoDB: Setting log file ./ib_logfile1 size to 5 MB
ansible_1 | InnoDB: Database physically writes the file full: wait...
ansible_1 | InnoDB: Doublewrite buffer not found: creating new
ansible_1 | InnoDB: Doublewrite buffer created
ansible_1 | InnoDB: 127 rollback segment(s) active.
ansible_1 | InnoDB: Creating foreign key constraint system tables
ansible_1 | InnoDB: Foreign key constraint system tables created
ansible_1 | 151001  4:04:28  InnoDB: Waiting for the background threads to start
ansible_1 | 151001  4:04:29 InnoDB: 5.5.44 started; log sequence number 0
ansible_1 | 151001  4:04:29  InnoDB: Starting shutdown...
ansible_1 | 151001  4:04:30  InnoDB: Shutdown completed; log sequence number 1595675
ansible_1 | 151001  4:04:30 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
ansible_1 | 151001  4:04:30 [Note] Plugin 'FEDERATED' is disabled.
ansible_1 | 151001  4:04:30 InnoDB: The InnoDB memory heap is disabled
ansible_1 | 151001  4:04:30 InnoDB: Mutexes and rw_locks use GCC atomic builtins
ansible_1 | 151001  4:04:30 InnoDB: Compressed tables use zlib 1.2.8
ansible_1 | 151001  4:04:30 InnoDB: Using Linux native AIO
ansible_1 | 151001  4:04:30 InnoDB: Initializing buffer pool, size = 128.0M
ansible_1 | 151001  4:04:30 InnoDB: Completed initialization of buffer pool
ansible_1 | 151001  4:04:30 InnoDB: highest supported file format is Barracuda.
ansible_1 | 151001  4:04:30  InnoDB: Waiting for the background threads to start
ansible_1 | 151001  4:04:31 InnoDB: 5.5.44 started; log sequence number 1595675
ansible_1 | ERROR: 1064  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE user ADD column Show_view_priv enum('N','Y') CHARACTER SET utf8 NOT ' at line 1
ansible_1 | 151001  4:04:31 [ERROR] Aborting
ansible_1 | 
ansible_1 | 151001  4:04:31  InnoDB: Starting shutdown...
ansible_1 | 151001  4:04:32  InnoDB: Shutdown completed; log sequence number 1595675
ansible_1 | 151001  4:04:32 [Note] /usr/sbin/mysqld: Shutdown complete
ansible_1 | 
ansible_1 | 151001  4:04:32 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
ansible_1 | 151001  4:04:32 [Note] Plugin 'FEDERATED' is disabled.
ansible_1 | 151001  4:04:32 InnoDB: The InnoDB memory heap is disabled
ansible_1 | 151001  4:04:32 InnoDB: Mutexes and rw_locks use GCC atomic builtins
ansible_1 | 151001  4:04:32 InnoDB: Compressed tables use zlib 1.2.8
ansible_1 | 151001  4:04:32 InnoDB: Using Linux native AIO
ansible_1 | 151001  4:04:32 InnoDB: Initializing buffer pool, size = 128.0M
ansible_1 | 151001  4:04:32 InnoDB: Completed initialization of buffer pool
ansible_1 | 151001  4:04:32 InnoDB: highest supported file format is Barracuda.
ansible_1 | 151001  4:04:32  InnoDB: Waiting for the background threads to start
ansible_1 | 151001  4:04:33 InnoDB: 5.5.44 started; log sequence number 1595675
ansible_1 | 151001  4:04:33  InnoDB: Starting shutdown...
ansible_1 | 151001  4:04:34  InnoDB: Shutdown completed; log sequence number 1595675
ansible_1 | 151001  4:04:34 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
ansible_1 | 151001  4:04:34 [Note] Plugin 'FEDERATED' is disabled.
ansible_1 | 151001  4:04:34 InnoDB: The InnoDB memory heap is disabled
ansible_1 | 151001  4:04:34 InnoDB: Mutexes and rw_locks use GCC atomic builtins
ansible_1 | 151001  4:04:34 InnoDB: Compressed tables use zlib 1.2.8
ansible_1 | 151001  4:04:34 InnoDB: Using Linux native AIO
ansible_1 | 151001  4:04:34 InnoDB: Initializing buffer pool, size = 128.0M
ansible_1 | 151001  4:04:34 InnoDB: Completed initialization of buffer pool
ansible_1 | 151001  4:04:34 InnoDB: highest supported file format is Barracuda.
ansible_1 | 151001  4:04:34  InnoDB: Waiting for the background threads to start
ansible_1 | 151001  4:04:35 InnoDB: 5.5.44 started; log sequence number 1595675
ansible_1 | ERROR: 1050  Table 'plugin' already exists
ansible_1 | 151001  4:04:35 [ERROR] Aborting
ansible_1 | 
ansible_1 | 151001  4:04:35  InnoDB: Starting shutdown...
ansible_1 | 151001  4:04:36  InnoDB: Shutdown completed; log sequence number 1595675
ansible_1 | 151001  4:04:36 [Note] /usr/sbin/mysqld: Shutdown complete

[diogoviannaaraujo]

Im running ansible inside a docker container, if you have docker compose its really simple to simulate.

[geerlingguy]

A few things to try out based on some research:

1. This always works: Set root password to "" (blank). Highly insecure, but hey, it solves the issue since something's funky in certain Ubuntu environments with the setting of the MySQL root password.
2. Check if apparmor is enabled. If it is, disable it, then try again (see here).
3. Try a different version of Ansible. See if either Ansible 1.9.2 or 2.0.0 works correctly.

So far it seems, based on a ton of testing using my geerlingguy.sonar role and Travis CI, that Ansible's mysql_user is simply not updating the root password in the database... or if it is, then the password change is not sticking. I have no idea why.

Basically, even though we get a 'changed/OK' from the task Update MySQL root password for localhost root account., the password is still set to '' in certain cases. And I'm not sure what causes that. The password does successfully update in all my local test environments (running Ubuntu 12.04, 14.04, CentOS 6 and 7, and using a huge variety of different playbooks—so far I've tested all 36 server configurations I have on my local machine, and I can't get one to fail using VirtualBox VMs or VMWare).

So there's something unique about the environment on Travis CI, and in your environments.

@diogoviannaaraujo - can you send me the docker configuration you're using (if it's using public registry images) and I'll try to test things out further locally? It's a pain testing on Travis CI.

[diogoviannaaraujo]

if I restart mysql and run ansible again it goes ok. I really think its trying to connect to a stopped service.

[diogoviannaaraujo]

Ive been trying with this compose, just put it with your playbook and your inventory to localhost

ansible:

  image: williamyeh/ansible:ubuntu14.04-onbuild

  environment:
    PLAYBOOK: /tmp/playbook.yml
    INVENTORY: /tmp/inventory.ini
    REQUIREMENTS: /tmp/requirements.yml

  command: bash -c "apt-get update && ansible-playbook-wrapper || true && cat /var/log/mysql/error.log"
#  command: bash -c "apt-get update && ansible-playbook-wrapper || true && service mysql restart || true && ansible-playbook-wrapper"

  volumes:
    - .:/tmp
#    - ./mysql:/var/lib/mysql

[geerlingguy]

Sheesh, spent at least 3 hours on this so far, and what I've found is that, if in my playbook I manually run a MySQL command to update the root password, the password seems to be updated:

https://travis-ci.org/geerlingguy/ansible-role-sonar/jobs/83237306

However, using Ansible's mysql_user module does not actually run the statement to update the user (see the MySQL log later in that build). It's supposed to, right here: https://github.com/ansible/ansible-modules-core/blob/devel/database/mysql/mysql_user.py#L232

I'm going to update this role to run the password statement manually and see if that fixes it.