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…
Advertisement
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!