Hi Edwin,

Please can you help me resolve an issue for creating a new dynamic db and db user.

Both the New Database and New Database User are being created correctly and the new user is being assigned to the new database correctly. The problem is that the new user only has permission to access the new database from my public IP 199.xxx.xxx.xx instead of the localhost IP (127.0.0.1).

I've been working with my host over the past week and we are at the point now of checking your script to ensure that the SQL syntax is correct.

In the multisites.cfg.php file we've used a range of values and combinations for the " define( 'MULTISITES_DB_GRANT_HOST', ''); " and also the "New DB server: " field in the template. We've tried 'localhost' , '124.0.0.1' , ' % ' and blank.

When we try 'localhost' or 'blank' we get an error message saying 'cannot connect to mySQL database'. We look in the control panel and see that the new database and user has been created however the permission to access the database is restricted to the front-end IP 199.xxx.xxx.xx as mentioned above.

When we try use '124.0.0.1' we get the following SQL message:

Create User error [1044]: Query = [GRANT ALL PRIVILEGES ON `new_dynamic_db`.* TO `new_dynamic_db_user`@`199.xxx.xxx.xx` IDENTIFIED BY 'db_password_for_current_template' WITH GRANT OPTION;], DB Message = [Access denied for user 'super_sql_user'@'%' to database 'new_dynamic_db' SQL=GRANT ALL PRIVILEGES ON `new_dynamic_db`.* TO `new_dynamic_db_user`@`199.xxx.xxx.xx` IDENTIFIED BY 'db_password_for_current_template' WITH GRANT OPTION;]

My host has said that this SQL message above is the actual syntax that is being used by your script and it shows that the query is assigning the public IP 199.xxx.xxx.xx to the 'new_dynamic_db_user' so this is the issue.

1a) Can you please take a look at your script and let me know if there is a syntax issue on your side?

1b) If not, why is the dynamic new user being assigned the public IP in the SQL query?

2) Could the problem be regarding the 'db_password_for_current_template'? I ask this because this password is for the existing Joomla database that is being used as a template for the new dynamic database. Should this be the password used in the SQL syntax? Or should the sql query rather use the new dynamic password created for the new user or perhaps the 'MULTISITES_DB_ROOT_PSW'?

Your assistance with this is greatly appreciated.

Thanks and regards.

Tony

Which is your JMS Template rule ?
Did you setup the "New Server Host" value to specify the 127.0.0.1 or localhost ?
Does the website that is used as template contain a specific "global configuration" server DB host value ?

The IP that is used is either the MULTISITES_DB_GRANT_HOST or the one that you have specified in the JMS template rule.


If you read carefully the error message returned, you can see that it try to create a user with a wildcard for the host but that you use a MySQL root user that does not has the privileges to create the user. (Access Denied to User)

So setup correctly your MySQL root user to allow creating additional users.

Hi Edwin,

Thanks for your response.

I have tried all combinations of the host in the template and in the multisites.cfg.php file.

Scenario 1:
When I left the MULTISITES_DB_GRANT_HOST blank and then inputted 'localhost' or '127.0.0.1' into the New Server Host in the template then Joomla just takes me to the admin login page and I cannot login via the current browser (it says the user does not exist). I can login via another browser or clear the cache and login however. Also the new DB and DB User is created in MySQL however the IP is restricted to the front end IP.

Scenario 2:
When I use the IP 127.0.0.1 in the MULTISITES_DB_GRANT_HOST file and leave the template blank (or input localhost) then I get the SQL error message that I already pasted and the database is created but the user is not created.

I've written to my host to ask them to double check again the permissions for the SQL Root user however I've already asked them before and they have confirmed the settings are correct.

Are you able to send me the SQL query that the script generates so that we can determine if there is some type of issue with the query and my host? My host has requested to see the query so that they can troubleshoot on their side.

Thanks and regards.

There is no SQL script that generate that.
This is coded in PHP to compute the DB creation and the MySQL user creation and the SQL that is used is the one that is displayed when you get an error.