Topic: mysqli_connect error mystery

[wdepot]

I am planning to do a major upgrade to a web site and for the updated version I have created a new ibay in which to run the updated code. As part of the process I am creating an updated MySQL database to which I will be copying values from the original database using a PHP conversion program that I am writing. I am creating this conversion program in the new ibay. Connecting to the new database and creating the updated tables works just fine.

However when I try to connect to the old database I get:

Fatal error: Uncaught mysqli_sql_exception: Access denied for user

I'm basically doing

Code: [Select]

$this->dblink = mysqli_connect($server, $username, $password, $database); where $server is localhost and the user, password and database are identical to what appears in the old web site code. The mystery is that if I put the conversion program in the original ibay with absolutely no changes it connects to the original database just fine. I've checked the ibay settings for both ibays under Web Hosting and they are identical. Access information under Information Bays is also the same for both ibays. I can't see anything under phpMyAdmin that would indicate that a user is restricted to being used by a certain ibay. Is anyone able to tell me why identical code works in one ibay but not another that has the same settings?

[Jean-Philippe Pialasse]

where lives old db (version of mariadb/mysql)?
where lives nee db (version of db engine)?
what type of password is used on old?
what type of password is used on new?
any more errors in logs(php and db)?

and the obvious have you check you use the right username , password, db name and address  for both?

[wdepot]

where lives old db (version of mariadb/mysql)?
where lives nee db (version of db engine)?
what type of password is used on old?
what type of password is used on new?
any more errors in logs(php and db)?

and the obvious have you check you use the right username , password, db name and address  for both?

Your question about where the databases are located led me to the source of the error. The old database resides in the standard SME Server location for MySQL files as it and the ibay that accesses it were created long before I had added the MariaDB 10.5 contribution to the server in order to be able to test osCommerce 4 and ZenCart which will not work under MariaDB 5.5.

The new ibay in which I am creating the conversion program was created after the MariaDB 10.5 contribution had been installed. I had created the new database under MariaDB 10.5 and had specified the maridb105.sock per the contribution instructions in the mysqli_connect statement for the new database. However in the connection to the old database I didn't bother specifying the socket since leaving that out connects using the default PHP connection for MySQL which I assumed would be the MariaDB 5.5 native to SME Server.

After looking at the db logs as you suggested I found that the connection errors were not shown in the standard MariaDB logs but were instead shown in the logs for MariaDB 10.5. Once I specified that the old db connection was supposed to use mysql.sock I had no trouble connecting.

Apparently adding the MariaDB 10.5 contribution secretly changes the default PHP database socket for all ibays that are created after it is installed but does not change it for any ibays created before the contribution is installed. It would have been nice if this database socket change showed up in the Web Hosting contribution as it would have made spotting the difference between the ibays much easier.

Is there a config setting for SMEServer that will show the PHP MySQL socket setting for a specific ibay or, for that matter, any ibay specific PHP settings?

[Jean-Philippe Pialasse]

good to see you solve it.

i would rather go to the fact that your app did default to last value as two connexions are set, but i might be wrong. 

there is indeed a way to set a default value for db
i know pdo_mysql could set default socket
also mysqli can set more default values https://www.php.net/manual/en/mysqli.configuration.php

all those could be configured at php.ini, at php-fpm.d/ibays.conf but are not in sme

[wdepot]

good to see you solve it.

i would rather go to the fact that your app did default to last value as two connexions are set, but i might be wrong. 

there is indeed a way to set a default value for db
i know pdo_mysql could set default socket
also mysqli can set more default values https://www.php.net/manual/en/mysqli.configuration.php

all those could be configured at php.ini, at php-fpm.d/ibays.conf but are not in sme

Actually the connection to the old database is being done before the connection to the new database so the lack of specifying the socket to use would have been expected to be the default socket regardless of what socket I used for the new database.

Now that I found the instructions on db keys for PHP under SME Server I checked the settings for the two ibays I found an "MDB10=enabled"  setting on the new ibay that doesn't exist on the old ibay. That led me to double check my custom templates for ibays.conf and sure enough I had added the setting to add

Code: [Select]

php_admin_value[mysqli.default_socket] = /var/lib/mysql/mariadb105.sock
php_admin_value[pdo_mysql.default_socket] = /var/lib/mysql/mariadb105.sock to the conf file when the setting was enabled. I had totally forgotten about it. I'm sure I did that when testing ZenCart and osCommerce 4 in other ibays since both required MariaDB 10 or later and I don't think either specified the socket for the MySQL connection and I was having trouble finding the file where they were doing their database connection which makes it hard to change the PHP code to specify a socket other than the default.

I obviously had also enabled the custom setting on the new ibay and forgotten about it since it has been quite a while since I had time to work on that project. Since I created the database connection class for my updated web site to include the ability to specify the MySQL socket if needed I'm going to delete the custom DB property for that ibay.

I may still create the new database under MariaDB 10.5 since under utf8mb4 some of the VARCHAR(255) field indexes don't work on MariaDB 5.5 unless you specify a length of 190 on the index which isn't needed under 10.5. Since SME 11 which, I hear, uses MariaDB 10.5 by default is likely to be out before I get the web site update finished I figure I might as well do the new version of the site using the newer version of MariaDB.

[Jean-Philippe Pialasse]

do not alter the template for php-fpm.d/ibays.conf. you might end with similar situations and also hide security updates. this template is critical.