Misconfiguration of MySQL socket pointer in PHP config.

[jails]

Hi,

The PHP --with-mysql-sock[=DIR] compiling option sets the MySQL Unix socket pointer for all MySQL extensions, including PDO_MYSQL.

On Drydock v5.7.3, PHP has been compiled with /tmp/mysql.sock as socket path:

php -r "echo ini_get('pdo_mysql.default_socket');"
// output => /tmp/mysql.sock

However /var/run/mysqld/mysqld.sock seems to be the MySQL Unix socket pointer (not 100% sure but shouldn't be hard to verify on your side through MySQL config files).

This misconfiguration doesn't allow to use localhost as hostname for all PHP MySQL drivers and make connections impossible.

This issue can be circumvented by using 127.0.0.1 as hostname which will force PHP drivers to use a network socket (instead of the misconfigured unix socket).

It's not a big deal but it's kindda not obvious that localhost can't be used as hostname and I wouldn't be surprised that other PHP users experienced the same issue.

If PHP can't be recompiled easily, it would be great to have at least the following symlink by default on instances once mysqld is started (as a workaround):

sudo ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock

Thank you!

[rageshkrishna]

Hi @jails! Thanks for reporting this. We have a new set of images that are scheduled to be released at the end of next week. I'll make sure we have one of these solutions in place for that release and update this issue once the new images are available.

[jails]

Awesome 👍

[jails]

Hello @rageshkrishna, did you tried to changes some database configuration recently to fix this issue ? Because I'm not able to create databases anymore. I'm getting the following error:

mysql -e 'CREATE DATABASE `test`;'
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

My config file looks like:

language: php

php:
  - 7.1

services:
  - mysql

branches:
  only:
    - master

build:
  ci:
    - mysql -e 'CREATE DATABASE `test`;'
...

Thank you.

[niranjan92]

hi @jails, I am able to run the build with your config https://app.shippable.com/github/niranjan92/samplePhp/runs/7/1/console.

As per docs http://docs.shippable.com/ci/mysql/ , can you please try granting permissions to shippable user?

language: php

php:
  - 7.1

services:
  - mysql

branches:
  only:
    - master

build:
  ci:
    - mysql -e "CREATE USER shippable@localhost IDENTIFIED BY ''; GRANT ALL ON *.* TO shippable@localhost; FLUSH PRIVILEGES;"
    - mysql -e 'CREATE DATABASE `test`;'

If this doesn't help, please share the link to the build.

[jails]

Ha right just added mysql -e "CREATE USER shippable@localhost IDENTIFIED BY ''; GRANT ALL ON *.* TO shippable@localhost; FLUSH PRIVILEGES;" and it's now passing. Don't know how I was able to make it works so far without that.

Thank you @niranjan92

[jails]

Hi @niranjan92,
I'm hitting this issue again and it seems to be random fails.
Looks like if I'm waiting long enough between test runs, everything is working fine but I'm not quite sure it's related.

The error is now the following:

mysql -e "CREATE USER shippable@localhost IDENTIFIED BY ''; GRANT ALL ON *.* TO shippable@localhost; FLUSH PRIVILEGES;"
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Does the fact I'm playing with the root password like so:

mysql -e "ALTER USER root@localhost IDENTIFIED BY 'root';"

may be the issue ? Indeed if some things are cached between builds and the testing env is not completely immutable, the second run would require the setted password of the first run to execute the CREATE USER command ?

Here is the last failed build url of my private project:
https://app.shippable.com/github/crysalead/staffplanning/runs/42/1/console

[a-murphy]

Since the MySQL database is running in the build container and a new container is started for each job, it's very unlikely that you're able to affect the next run without deliberately caching information or adding additional volume mounts to the container (neither of which you seem to be doing).

It does look like there may be a connection between the length of time it took to start MySQL and the errors, although there aren't enough errors to be sure. The shorter start times being more likely to fail makes me suspect that MySQL may not have completely started when your first command runs. The mysql_start step does check if MySQL is running, but it's possible that it's a false positive in these cases. Could you try adding sleep 5 before the other commands in your ci section to see if an extra five seconds helps?

I tried to reproduce the issue with the YML above and with the ALTER USER command added, running each several times, and did not see any errors. Any other database configuration specific to your project that you think might be related?

[jails]

Thank you @a-murphy, I'll add a sleep 5 and keep you posted.

[niranjan92]

@jails we have fixed the original issue by creating a symlink for /tmp/mysql.sock when we start mysqld. The changes are available on AMI v5.8.2 onwards

Please let us know if this is working for you so that we can close the issue.

[mbrodala]

We have just stumbled upon this after switching from v5.7.3 to v5.10.4:

mysqladmin -u root password root
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

I tought there was a symlink from /var/run/mysqld/mysqld.sock to /tmp/mysql.sock by now.

Even -h 127.0.0.1 does not work:

mysqladmin -h 127.0.0.1 -u root password root
mysqladmin: connect to server at '127.0.0.1' failed
error: 'Can't connect to MySQL server on '127.0.0.1' (111)'
Check that mysqld is running on 127.0.0.1 and that the port is 3306.
You can check this by doing 'telnet 127.0.0.1 3306'

[a-murphy]

Both mysqladmin -u root password root and mysqladmin -h 127.0.0.1 -u root password root are working for me with drydock/u16phpall:v5.10.4. The symlink is created in the mysql_start step.

Any chance something in your script is removing the contents of /tmpor restarting MySQL? Or was there something you were updating to make v5.7.3 work that could be a problem now that there is a symlink?

[mbrodala]

@a-murphy We never did something special for v5.7.3 or any previous version, you can check build 2524 of project 58ab0bf60bfe57100058d7a6.

[mbrodala]

Apparently the line mysqladmin -h 127.0.0.1 -u root password root works fine with v5.7.3, see build 2525 of the same project.

[a-murphy]

I still haven't been able to reproduce your error. Have you tried clearing the cache? (There's a "reset cache" option on the project settings page.) Or moving the mysqladmin command earlier in your script to see if something else in the script could be conflicting? Checking the MySQL logs (/var/log/mysql/error.log) in the on_failure section might help as well.

[mbrodala]

@a-murphy I just retried with resetting caches before building but the result is the same (see build 2536).

I'll try to build a small repository which manually pulls in the latest drydock image via pre_ci_boot not not break other projects.

[mbrodala]

Hm, I tried this now and it worked just fine (network socket, UNIX socket).

However, there is no interaction with MySQL at all until the mysqladmin call as you can see in the build output of 58ab0bf60bfe57100058d7a6. Every single command executed during build is shown there.

[rageshkrishna]

That's definitely strange. It looks like MySQL is terminating for some reason in the older builds where mysqladmin failed to connect.

Getting a tail of the mysql error log in on_failure as suggested above might tell us why.

[mbrodala]

@rageshkrishna I've added this to the original project now and this is what I get:

build_on_failure 11s
  tail /var/log/mysql/error.log 0s  
2018-01-12T09:25:25.625187Z 0 [Note] Shutting down plugin 'MEMORY'
2018-01-12T09:25:25.625194Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2018-01-12T09:25:25.625236Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2018-01-12T09:25:25.625240Z 0 [Note] Shutting down plugin 'MyISAM'
2018-01-12T09:25:25.625257Z 0 [Note] Shutting down plugin 'CSV'
2018-01-12T09:25:25.625262Z 0 [Note] Shutting down plugin 'sha256_password'
2018-01-12T09:25:25.625265Z 0 [Note] Shutting down plugin 'mysql_native_password'
2018-01-12T09:25:25.625411Z 0 [Note] Shutting down plugin 'binlog'
2018-01-12T09:25:25.625854Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete


  ...
  mysql_stop 10s  
================= Stopping mysql ===================

�mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

(See build 2539)

So, MySQL is being shutdown during build? 🤔

[mbrodala]

@a-murphy @rageshkrishna We see the same issue again after upgrading the images from 5.7.3 to the latest version (6.3.4):

mysqladmin -h 127.0.0.1 -u root password root
mysqladmin: connect to server at '127.0.0.1' failed
error: 'Can't connect to MySQL server on '127.0.0.1' (111)'
Check that mysqld is running on 127.0.0.1 and that the port is 3306.
You can check this by doing 'telnet 127.0.0.1 3306'

See for example build 373 of project 59de2a3a09b9a50700f24741. Resetting the cache does not make a difference. Oddly this does seem to work fine on a different project, e.g. build 2959 of project 58ab0bf60bfe57100058d7a6.

Any ideas?

[manishas]

@mbrodala this issue seems to have gone cold... is this still a problem for you?

[mbrodala]

@manishas Since we're running mysql-based tests on the latest images just fine, the issue seems to be resolved.

[manishas]

👍