Feature #7574
closedmyamiweb database does not set database port
0%
Description
We are setting up Leginon using a managed DB service we run on site. The database is on a non-standard port (not 3306).
This test works:
php -r "mysql_connect('niftydb:12345', 'usr_object', 'usr_pass', 'leginondb'); echo mysql_stat();"
Uptime: 2574466 Threads: 7 Questions: 250 Slow queries: 0 Opens: 37 Flush tables: 1 Open tables: 31 Queries per second avg: 0.000
But the database initialization done through the setup website fails with a message saying something in wrong with the hostname, username, or password.
I took a look into the code and I find that you are using the older 'mysql' as well as newer 'mysqli' code. It looks like the older mysql code took "host:port" as the hostname and worked, but mysqli wants the port as a separate parameter when a new object is created.
I'm not certain this is the issue, but that's my best guess. I am unable to find php logs anywhere, and no errors were displayed in the browser, despite being set for display_errors = On.
Updated by Anchi Cheng over 5 years ago
- Assignee set to Sargis Dallakyan
Sargis, please look into this. Thanks.
Updated by Sargis Dallakyan over 5 years ago
Sure, will look into this. It's been a while since I did database initialization through the setup website. I have been doing it though centos(7)AutoInstallation.py mostly.
Ben, what OS, php and mysql versions are you using? Does it work when you use standard mysql port (3306)? Thanks for your time.
Updated by Ben McGough over 5 years ago
CentOS Linux release 7.6.1810 (Core)
PHP 5.4.16 (cli) (built: Oct 30 2018 19:30:51)
Server version: 5.5.5-10.3.5-MariaDB-10.3.5+maria~jessie mariadb.org binary distribution
I don't have a database set up on a standard port so I don't know if it works, but I assume it does.
I did not do the installation on this system, I'm just trying to help them get it working with our database service. I will contact the installer and see if I can run the centos7AutoInstaller.py script myself and maybe get it working that way.
Updated by Ben McGough over 5 years ago
I tried running the centos7installer.py script, but it seems to assume a database on the local host.
I dug down into the newDBsetup.php and included files and it looks to me like the mysqli code is used here, which doesn't seem to support "host:port" in the host field, but has a separate field for port.
And chance this could be changed to support an off-box database? In the Leginon documentation it talks about the database server being on a different host. I know this is different from a non-standard port, but I don't see how this script would work for a separate database server either.
Updated by Sargis Dallakyan over 5 years ago
That's right, centos7installer.py script installs a database on the local host.
I've looked at Database Initialization through the setup website and, indeed, there is no option to specify mysql port. If you can't run mysql on a standard port or do port forwarding, you can edit myamiweb/inc/mysql.inc and replace line 44:$link = @mysqli_connect($host, $this->db_user, $this->db_pass);
with$link = @mysqli_connect($host, $this->db_user, $this->db_pass, null, 12345);
There might be other places that needs changes, but I hope this get you started.
Updated by Anchi Cheng over 5 years ago
There is a hidden feature that allows you to specify port number in sinedon.cfg for the python calls. See Issue #3028
Updated by Anchi Cheng about 4 years ago
- Tracker changed from Bug to Feature
- Subject changed from database port does not seem to work to myamiweb database does not set database port
- Status changed from New to Won't Fix or Won't Do
- Priority changed from High to Low
- Target version set to Appion/Leginon Future Version
- Affected Version deleted (
Appion/Leginon 3.4) - Show in known bugs deleted (
No)
Final notes: centos7installer.py is written only for same-box database installation. Please see Database_Server_Installation if you only want to install a database.
A shortcut could be going through centos7installer.py there as well and then follow steps to allow connections from others and adjust privilege level accordingly. You can then modify your webserver config.php to use that database server.
We have converted to use only mysqli with php. Looking at how wide-spread the change is required to allow the optional port setting, I would like to put this to Won't do Status unless there is a strong case for this in the future.
An off-box database server is common in leginon/appion installation. They still use the same 3306 default mysql port. Using the default allows easier configuration for firewall etc since it is standard.