Troubleshooting exercise - NGINX 2

curl http://127.0.0.1/ -v

Logs:

Trying 127.0.0.1:80...
* Connected to 127.0.0.1 (127.0.0.1) port 80 (#0)
> GET / HTTP/1.1
> Host: 127.0.0.1
> User-Agent: curl/7.79.1
> Accept: */*
> 
* Mark bundle as not supporting multiuse
< HTTP/1.1 500 Internal Server Error
< Server: nginx/1.22.0
< Date: Tue, 13 Dec 2022 09:41:51 GMT
< Content-Type: text/html
< Transfer-Encoding: chunked
< Connection: keep-alive
< X-Powered-By: PHP/5.4.16
< 
* Connection #0 to host 127.0.0.1 left intact

The logs suggest that the connection between the client, Nginx and PHP services is established. But there is an error in some requests.

I think it will be beneficial to see the code of the page that sends requests.

sudo lsof -i -P -n | grep LISTEN

Logs:

rpcbind   2590       rpc    8u  IPv4  15812      0t0  TCP *:111 (LISTEN)
rpcbind   2590       rpc   11u  IPv6  15815      0t0  TCP *:111 (LISTEN)
php-fpm   2957      root    6u  IPv4  17676      0t0  TCP *:9000 (LISTEN)
php-fpm   3027     nginx    0u  IPv4  17676      0t0  TCP *:9000 (LISTEN)
php-fpm   3028     nginx    0u  IPv4  17676      0t0  TCP *:9000 (LISTEN)
php-fpm   3029     nginx    0u  IPv4  17676      0t0  TCP *:9000 (LISTEN)
php-fpm   3030     nginx    0u  IPv4  17676      0t0  TCP *:9000 (LISTEN)
php-fpm   3031     nginx    0u  IPv4  17676      0t0  TCP *:9000 (LISTEN)
master    3088      root   13u  IPv4  18122      0t0  TCP 127.0.0.1:25 (LISTEN)
sshd      3202      root    3u  IPv4  19565      0t0  TCP *:22 (LISTEN)
sshd      3202      root    4u  IPv6  19574      0t0  TCP *:22 (LISTEN)
nginx    10321      root    6u  IPv4  75171      0t0  TCP *:80 (LISTEN)
nginx    10321      root    7u  IPv6  75172      0t0  TCP *:80 (LISTEN)
nginx    10322     nginx    6u  IPv4  75171      0t0  TCP *:80 (LISTEN)
nginx    10322     nginx    7u  IPv6  75172      0t0  TCP *:80 (LISTEN)

This gives me the idea that we are dealing with PHP service. So I will find index.php file and look into it.

To find the location of the file, I will look into nginx.conf. Probably there is a better way of doing it, but this is the one I know. cat /etc/nginx/nginx.conf

Logs:

# For more information on configuration, see:
#   * Official English Documentation: http://nginx.org/en/docs/
#   * Official Russian Documentation: http://nginx.org/ru/docs/

user nginx;
worker_processes auto;
error_log /var/log/nginx/error.log;
pid /run/nginx.pid;

# Load dynamic modules. See /usr/share/doc/nginx/README.dynamic.
include /usr/share/nginx/modules/*.conf;

events {
    worker_connections 1024;
}

http {
    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';

    access_log  /var/log/nginx/access.log  main;

    sendfile            on;
    tcp_nopush          on;
    tcp_nodelay         on;
    keepalive_timeout   65;
    types_hash_max_size 4096;

    include             /etc/nginx/mime.types;
    default_type        application/octet-stream;

    # Load modular configuration files from the /etc/nginx/conf.d directory.
    # See http://nginx.org/en/docs/ngx_core_module.html#include
    # for more information.
    include /etc/nginx/conf.d/*.conf;

    server {
        listen       80;
        listen       [::]:80;
        server_name  _;
        root         /usr/share/nginx/html;
        index index.php;

        # Load configuration files for the default server block.
        include /etc/nginx/default.d/*.conf;

    location / {
      try_files $uri $uri/ /index.html;
    }

        error_page 404 /404.html;
        location = /404.html {
        }

        error_page 500 502 503 504 /50x.html;
        location = /50x.html {
        }

    # pass the PHP scripts to FastCGI server liistening on the socket
        location ~ \.php$ {
          try_files $uri =404;
          fastcgi_pass 127.0.0.1:9000;
          fastcgi_index inde/usr/share/nginx/html/index.htmlx.php;
          fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
          include fastcgi_params;
        }
    }

}

Ok, I can see fastcgi_index is located at usr/share/nginx/html/index.htmlx.php. Let's read into it.

cat /usr/share/nginx/html/index.php

Logs:

<?php                                                                                             

    $servername = 'localhost';                                                                        
    $username = 'cyf';                                                                               
    $password = 'Eix#ah6aib0u';                                                                   

    // Create connection                                                                              
    $conn = new mysqli($servername, $username, $password);                                 

    $sql = "SELECT * from cyf_user";                                                   
    $result = $conn->query($sql);                                                                     

    while($row = $result->fetch_assoc()){                                                             
        echo "My name is: " . $row["Name"] . "<br/>";
    }                                                                                                 

?>

This shows that my server is trying to connect to a MySQL database. This was written in a provided readme but better to confirm.

mysql

Logs:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Running mysql gives the error. The socket is not established because mysql isn't running. Time to look at my.conf and error logs.

The /etc (et-see) directory is where a Linux system's configuration files live.

cd etc/ ls

I can seemy.cnf my.cnf.d

Let's read the config file:
`cat my.cnf`

Logs:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=616

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]                          
default-character-set=utf8        

[mysql]                           
default-character-set=utf8        

[mysqld]                          
collation-server = utf8_unicode_ci
character-set-server = utf8

sudo cat /var/log/mysqld.log

Logs:

2022-11-17T11:48:58.214432Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.31) initializing of server in progress as process 6214
2022-11-17T11:48:58.225981Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-11-17T11:48:58.961271Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-11-17T11:49:00.428310Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: V<P6>pbb0w+G
2022-11-17T11:49:03.941146Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31) starting as process 6260
2022-11-17T11:49:03.953756Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-11-17T11:49:04.239739Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-11-17T11:49:04.543174Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-11-17T11:49:04.543660Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-11-17T11:49:04.569366Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-11-17T11:49:04.569537Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2022-11-17T13:14:40.523680Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.31).
2022-11-17T13:14:41.670620Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.31)  MySQL Community Server - GPL.
2022-11-17T13:14:42.097477Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2022-11-17T13:14:42.099375Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31) starting as process 7364
2022-11-17T13:14:42.111979Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-11-17T13:14:42.497437Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-11-17T13:14:42.830619Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-11-17T13:14:42.830711Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-11-17T13:14:42.882217Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-11-17T13:14:42.882421Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2022-11-17T14:50:57.329935Z 9 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2022-11-17T16:12:02.364194Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.31).
2022-11-17T16:12:03.811902Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.31)  MySQL Community Server - GPL.
2022-12-12T09:42:51.189665Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2022-12-12T09:42:51.195260Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31) starting as process 2952
2022-12-12T09:42:51.670794Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-12T09:42:53.930147Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-12T09:42:55.292515Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-12-12T09:42:55.292953Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-12-12T09:42:55.363087Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2022-12-12T09:42:55.364376Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-12-12T10:00:50.398946Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.31).
2022-12-12T10:00:51.715488Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.31)  MySQL Community Server - GPL.
2022-12-12T10:00:52.248970Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2022-12-12T10:00:52.250871Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31) starting as process 4083
2022-12-12T10:00:52.266082Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2022-12-12T10:00:52.266106Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8mb3_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
2022-12-12T10:00:52.276058Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-12T10:00:52.658807Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-12T10:00:52.988291Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-12-12T10:00:52.988329Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-12-12T10:00:53.041558Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-12-12T10:00:53.041796Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2022-12-12T10:01:13.878387Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.31).
2022-12-12T10:01:15.386846Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.31)  MySQL Community Server - GPL.
2022-12-12T10:01:15.747275Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2022-12-12T10:01:15.749126Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2022-12-12T10:01:15.749159Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31) starting as process 4189
2022-12-12T10:01:15.750374Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2022-12-12T10:01:15.750390Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8mb3_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
2022-12-12T10:01:15.756060Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-12T10:01:16.066519Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-12T10:01:16.315121Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-12-12T10:01:16.315160Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-12-12T10:01:16.344484Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-12-12T10:01:16.344701Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2022-12-12T10:03:15.382627Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.31).
2022-12-12T10:03:16.738567Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.31)  MySQL Community Server - GPL.
2022-12-12T10:03:17.096385Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2022-12-12T10:03:17.098230Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31) starting as process 4469
2022-12-12T10:03:17.099387Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2022-12-12T10:03:17.099405Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8mb3_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
2022-12-12T10:03:17.104711Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-12T10:03:17.426676Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-12T10:03:17.654569Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-12-12T10:03:17.654605Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-12-12T10:03:17.654860Z 0 [ERROR] [MY-010262] [Server] Can't start server: Bind on TCP/IP port: Permission denied
2022-12-12T10:03:17.654872Z 0 [ERROR] [MY-010257] [Server] Do you already have another mysqld server running on port: 616 ?
2022-12-12T10:03:17.655452Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-12-12T10:03:19.203991Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.31)  MySQL Community Server - GPL.
2022-12-12T10:20:05.302456Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2022-12-12T10:20:05.307538Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31) starting as process 2986
2022-12-12T10:20:05.412107Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2022-12-12T10:20:05.412136Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8mb3_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
2022-12-12T10:20:05.558132Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-12T10:20:06.909746Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-12T10:20:07.725465Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-12-12T10:20:07.725528Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-12-12T10:20:07.726638Z 0 [ERROR] [MY-010262] [Server] Can't start server: Bind on TCP/IP port: Permission denied
2022-12-12T10:20:07.726652Z 0 [ERROR] [MY-010257] [Server] Do you already have another mysqld server running on port: 616 ?
2022-12-12T10:20:07.727083Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-12-12T10:20:09.227482Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.31)  MySQL Community Server - GPL.

Ok, I can see that error suggests the following: Do you already have another mysqld server running on port: 616 ?

But strangely when I listed all the ports 616 didn't appear in the list. So my first assumption is that it is not busy. After multiple attempts to start mysqld, I decided to check what ports are free to use and which ones are booked by the system.

sudo lsof -i TCP:616

No output

My finding that 616 tcp,udp sco-sysmgr SCO System Administration Server So this port is booked by the system and shouldn't be assigned to anything else.

Let's change it to 3306 commonly used by MySQL.

But before I do that I want to see nginx error log to compare what error I am getting now and to any errors in the future.

sudo cat /var/log/nginx/error.log

Logs:

2022/12/13 09:37:38 [error] 10322#10322: *1 FastCGI sent in stderr: "PHP message: PHP Warning:  mysqli::mysqli(): (HY000/2002): No such file or directory in /usr/share/nginx/html/index.php on line 8
PHP message: PHP Warning:  mysqli::query(): Couldn't fetch mysqli in /usr/share/nginx/html/index.php on line 11
PHP message: PHP Fatal error:  Call to a member function fetch_assoc() on a non-object in /usr/share/nginx/html/index.php on line 13" while reading response header from upstream, client: 127.0.0.1, server: _, request: "GET / HTTP/1.1", upstream: "fastcgi://127.0.0.1:9000", host: "localhost"
2022/12/13 09:41:41 [error] 10322#10322: *3 FastCGI sent in stderr: "PHP message: PHP Warning:  mysqli::mysqli(): (HY000/2002): No such file or directory in /usr/share/nginx/html/index.php on line 8

Now changing port in my.cnf sudo nano /etc/my.cnf

port = 3306

Restart MySQL sudo systemctl restart mysqld

Looking at mysqld logs again shows that now the connection is established.

Logs:


2022-12-14T08:59:25.501349Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31) starting as process 16913
2022-12-14T08:59:25.502683Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2022-12-14T08:59:25.502699Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8mb3_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
2022-12-14T08:59:25.508291Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-14T08:59:25.777976Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-14T08:59:26.010599Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-12-14T08:59:26.010663Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-12-14T08:59:26.042281Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-12-14T08:59:26.042480Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL C

Let's try curl curl http://127.0.0.1/ -v`

Logs:

Trying 127.0.0.1:80...
* Connected to 127.0.0.1 (127.0.0.1) port 80 (#0)
> GET / HTTP/1.1
> Host: 127.0.0.1
> User-Agent: curl/7.79.1
> Accept: */*
> 
* Mark bundle as not supporting multiuse
< HTTP/1.1 500 Internal Server Error
< Server: nginx/1.22.0
< Date: Tue, 13 Dec 2022 09:41:51 GMT
< Content-Type: text/html
< Transfer-Encoding: chunked
< Connection: keep-alive
< X-Powered-By: PHP/5.4.16
< 
* Connection #0 to host 127.0.0.1 left intact

Still an internal error. But mysqld logs don't show any error, let's look at Nginx logs.

sudo cat /var/log/nginx/error.log

Logs:

3048#3048: *25 FastCGI sent in stderr: "PHP message: PHP Warning:  mysqli::mysqli(): The server requested authentication method unknown to the client [caching_sha2_password] in /usr/share/nginx/html/index.php on line 8" while reading response header from upstream, client: 127.0.0.1, server: _, request: "GET /index.php HTTP/1.1", upstream: "fastcgi://127.0.0.1:9000", host: "127.0.0.1"

This suggests something to do with the authentication method unknown to the client. After some googling, official docs suggest adding default-authentication-plugin=mysql_native_password to my.cnf file.

Let's try to do that. Restart the service, still not seeing results. But we will keep the changes.

Let's look at the actual Database and see if it has fields and data we need. mysql -u cyf -p Enter password: Logs:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;

Logs:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| users              |
+--------------------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM users;

Logs: ERROR 1046 (3D000): No database selected

mysql> SHOW DATABASES;

Logs:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| users              |
+--------------------+
3 rows in set (0.00 sec)

mysql> SHOW TABLES;

Logs: ERROR 1046 (3D000): No database selected

mysql> USE users; Logs:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> SHOW TABLES;

Logs:

+-----------------+
| Tables_in_users |
+-----------------+
| cyf_user        |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM cyf_user;

Logs:

+-----------+------+
| Name      | ID   |
+-----------+------+
| Margarita |  123 |
| Berkeli   |  345 |
+-----------+------+
2 rows in set (0.00 sec)

I understand that I shouldn't be adding all this information about databases in open-source issues in GitHub but this is done purely so the purpose of the exercise. I can see that there is a database called users and it has a table cyf_user and our names and ids in it. This looks like what we need.

Ok what do we know at this point we have:

a client who talks to Nginx (port 80) - works,
Nginx talks to PHP-FPM service (port 9000) which can serve the PHP applications -works.
PHP application needs to connect to the MySQL database via the localhost and UNIX socket - not sure.

Hmmm It looks like because we specify in our PHP file that we will connect to the server "localhost", we will automatically be connected via socket rather than port. So to make it work on the same machine, we could potentially delete the port from my.cnf and it would still work. But let's keep it for now and test it later.

Ok, maybe we can play around with PHP code and see how it responds.

Let's add an assertion to show whether the connection is successful or not. For now, just print it.

sudo nano /usr/share/nginx/html/index.php

  if ($conn -> connect_errno) {
  echo "Failed to connect to MySQL: " . $conn -> connect_error;
}

Do the curl, not a failure output but still an error.

Let's add a condition to our results so that we receive code 200 even without getting results. At this stage, we just want to see if our connection to MySQL is good or not.

sudo nano /usr/share/nginx/html/index.php

  if($result = $conn->query($sql)){                                                                     

        while($row = $result->fetch_assoc()){                                                             
                echo "My name is: " . $row["Name"] . "<br/>";
        }}

curl -v http://127.0.0.1/index.php

Logs:

*   Trying 127.0.0.1:80...
* Connected to 127.0.0.1 (127.0.0.1) port 80 (#0)
> GET /index.php HTTP/1.1
> Host: 127.0.0.1
> User-Agent: curl/7.79.1
> Accept: */*
> 
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Server: nginx/1.22.0
< Date: Wed, 14 Dec 2022 09:15:30 GMT
< Content-Type: text/html
< Transfer-Encoding: chunked
< Connection: keep-alive
< X-Powered-By: PHP/5.4.16
< 
* Connection #0 to host 127.0.0.1 left intact

And then let's change the socket path in our my.cnf file and see how our PHP app responds. Will it print out the error that we added that the connection couldn't be established?

curl -v http://127.0.0.1/index.php

Logs:

*   Trying 127.0.0.1:80...
* Connected to 127.0.0.1 (127.0.0.1) port 80 (#0)
> GET /index.php HTTP/1.1
> Host: 127.0.0.1
> User-Agent: curl/7.79.1
> Accept: */*
> 
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Server: nginx/1.22.0
< Date: Wed, 14 Dec 2022 09:40:08 GMT
< Content-Type: text/html
< Transfer-Encoding: chunked
< Connection: keep-alive
< X-Powered-By: PHP/5.4.16
< 
Failed to connect to MySQL: No such file or directory             
* Connection #0 to host 127.0.0.1 left intact

Ok so changing the socket broke the connection. That tells us that before the connection was good, but it wasn't returning the data we need.

Looking at how we tried to access our database before, we hit an error when trying to access tables without specifying what database name was first.

Looking at our PHP code, there is no reference to the database. Let's fix it.

sudo nano /usr/share/nginx/html/index.php

   $db = 'users';                                                                 

        // Create connection                                                                              
        $conn = new mysqli($servername, $username, $password, $db);

curl -v http://127.0.0.1/index.php

Logs:

*   Trying 127.0.0.1:80...
* Connected to 127.0.0.1 (127.0.0.1) port 80 (#0)
> GET /index.php HTTP/1.1
> Host: 127.0.0.1
> User-Agent: curl/7.79.1
> Accept: */*
> 
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Server: nginx/1.22.0
< Date: Wed, 14 Dec 2022 09:48:00 GMT
< Content-Type: text/html
< Transfer-Encoding: chunked
< Connection: keep-alive
< X-Powered-By: PHP/5.4.16
< 
My name is: Margarita<br/>My name is: Berkeli<br/>
* Connection #0 to host 127.0.0.1 left intact

Great, we are getting data back!!! Now we just need to edit PHP code to print out only my name and id.

Final code:

<?php                                                                                            
        $servername = 'localhost';                                                                        
        $username = 'cyf';                                                                               
        $password = 'Eix#ah6aib0u';
        $db = 'users';                                                                 

        // Create connection                                                                              
        $conn = new mysqli($servername, $username, $password, $db, NULL, '/var/lib/mysql/mysql.sock');
        if ($conn -> connect_errno) {
  echo "Failed to connect to MySQL: " . $conn -> connect_error;
    exit();
}
        $sql = "SELECT * FROM cyf_user";                                                   
       if($result = $conn->query($sql)){                                                                     

        while($row = $result->fetch_assoc()){                                                             
                echo "My name is: " . $row["Name"] . "<br/>";
        }}                                                                                              
    $conn -> close();
?>

curl -v http://127.0.0.1/index.php

Logs:

*   Trying 127.0.0.1:80...
* Connected to 127.0.0.1 (127.0.0.1) port 80 (#0)
> GET /index.php HTTP/1.1
> Host: 127.0.0.1
> User-Agent: curl/7.79.1
> Accept: */*
> 
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Server: nginx/1.22.0
< Date: Wed, 14 Dec 2022 11:41:30 GMT
< Content-Type: text/html
< Transfer-Encoding: chunked
< Connection: keep-alive
< X-Powered-By: PHP/5.4.16
< 
My name is Margarita and my ID is 123<br/>                         
* Connection #0 to host 127.0.0.1 left intact

Let's now test my theory that if I remove the port from my.cnf it should still work on this machine as localhost uses socket in this case, not the port.

sudo nano /etc/my.cnf Remove port = 3360

sudo systemctl restart mysqld

curl -v http://127.0.0.1/index.php

Logs:

*   Trying 127.0.0.1:80...
* Connected to 127.0.0.1 (127.0.0.1) port 80 (#0)
> GET /index.php HTTP/1.1
> Host: 127.0.0.1
> User-Agent: curl/7.79.1
> Accept: */*
> 
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Server: nginx/1.22.0
< Date: Wed, 14 Dec 2022 11:16:54 GMT
< Content-Type: text/html
< Transfer-Encoding: chunked
< Connection: keep-alive
< X-Powered-By: PHP/5.4.16
< 
My name is Margarita and my ID is 123<br/>                         
* Connection #0 to host 127.0.0.1 left intact

Looks like it is correct as we are still getting a response.

RitaGlushkova / immersive-go-course

Troubleshooting exercise - NGINX 2 #66