Cannot connect to Sphinx with PHP and PDO

I seem to be having issues I do not understand…

I have installed MySQL 8.0.27 I have installed Sphinx, created an index, filled it and all is OK through the terminal. Am able to query the Spinx index, without issues. So searchd and the indexer are doing their job.

I have created a simple PHP interface to search through this index with PHP. But this is where things are getting strange…

I am NOT able to connect to the sphinx instance (which does work through the terminal..) –> SQLSTATE[HY000] [2002] Permission denied

My sphinx.conf

source page
{
        type = mysql

        sql_host = localhost
        sql_db   = db
        sql_user = user
        sql_pass = password
        sql_port = 3306

        sql_query = SELECT p.id, p.parent_id, p.date_add, p.version, p.content, p.pagenumber, p.status, pp.filename, pp.pages  FROM page p LEFT JOIN parent_page pp ON p.parent_id=pp.id

        sql_field_string = content
}

index pagedata
{
        source = wob
        path = /etc/sphinx/data/page
        morphology = none

        min_word_len = 2
        min_prefix_len = 0
}

searchd
{
    listen = 9312
    listen = 9306:mysql41
    log = /var/log/sphinx/searchd.log
    query_log = /var/log/sphinx/query.log

    read_timeout = 5
    max_children = 30
        pid_file                = /etc/sphinx/sphinx-searchd.pid
        seamless_rotate         = 1
        preopen_indexes         = 1
        unlink_old              = 1
        workers                 = threads # for RT to work
        binlog_path             = /var/log/sphinx/
}

This is the Sphinx status

Sphinx 3.3.1 (commit b72d67b)
Copyright (c) 2001-2020, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/etc/sphinx/etc/sphinx.conf'...

searchd status
--------------
uptime: 4419 
connections: 6 
maxed_out: 0 
command_search: 3 
command_snippet: 0 
command_update: 0 
command_delete: 0 
command_keywords: 0 
command_persist: 0 
command_status: 3 
command_flushattrs: 0 
agent_connect: 0 
agent_retry: 0 
queries: 2 
dist_queries: 0 
query_wall: 0.009 
query_cpu: OFF 
dist_wall: 0.000 
dist_local: 0.000 
dist_wait: 0.000 
query_reads: OFF 
query_readkb: OFF 
query_readtime: OFF 
avg_query_wall: 0.005 
avg_query_cpu: OFF 
avg_dist_wall: 0.000 
avg_dist_local: 0.000 
avg_dist_wait: 0.000 
avg_query_reads: OFF 
avg_query_readkb: OFF 
avg_query_readtime: OFF 
qcache_cached_queries: 0 
qcache_used_bytes: 0 
qcache_hits: 0 
sql_parse_error: 2 
sql_dummy: 1 
sql_select: 3 
sql_select_sysvar: 2 

This is the code I use for connecting to Sphinx from my PHP script… Which causes the error mentioned in the top of this post…

<?php
try {
    $pdo = new PDO("mysql:host=localhost:9306");
    // set the PDO error mode to exception
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec('SET NAMES utf8');
} catch (PDOException $e) {
    echo "Connection failed to API database: " . $e->getMessage();
}

Connecting through the terminal is no problem and queries are working fine and fast…

[root@web03 bin]# mysql -h0 -P9306
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 3.3.1 (commit b72d67b) 

Copyright (c) 2000, 2021, 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> 

It failes on the $pdo = new PDO() part. Cannot make connection. I have tried replacing localhost with 127.0.0.1, have tried replacing the port to 9312 (see config) and all combinations between these. No dice.

Does anybody have a clue or a tip? It is driving me mad…

Answer

Just installed Manticore Search 4.2 and it had the exact same issue… So that got me thinking.

Turns out it was because of SELinux… This post had the solution for me. So it had nothing to do with Sphinx or Manticore Search… It was my SELinux settings that prevented me from making Database connections from http user.

The answer has 4 simple steps to permanently allow that and everything works like a charm!