Question: Unable to create MySQL database: Error: 71400050: Unable to Create db.
or
HC is unable to connect MySQL Server
Solution:
This errors occurs if the MySQL root user specified in Hosting Controller settings doesn't has sufficient database privileges or MySQL server is not accepting connection remotely.
Grant all privileges to 'root' user and set this user in MySQL settings in Hosting Controller.
To grant privileges to root user, follow these steps:
- Open command prompt. To open command prompt, click Start, click Run, and enter cmd.exe.
- Go to /bin directory of MySQL (X:\MySQL\Bin).
- Enter command on MySQL command line: mysql> grant all privileges on *.* to root@IPAddress identified by 'password' with grant option;
- To reload the grant tables, perform a flush-privileges operation by executing this command: mysql> FLUSH PRIVILEGES;
Note:
'password' is the password of root user and it must be given in the quotes.
IPAddress is IP which is given in HC panel for MySQL settings.
Connecting To MySQL 8:
MySQL has release a new version of MySQL Database server i.e. MySQL 8. HC10 is fully compatible with MySQL 8, however, there has been a change in the syntax for assigning privileges. For previous versions of MySQL the following command would work perfectly well:
mysql> CREATE USER 'root'@'localhost' IDENTIFIED BY 'rootpassword';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'IPAddress' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
If you receive error "mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client "
Then execute following command in Mysql server
mysql -u root -p
insert the password then execute below query
alter user 'username'@'localhost' identified with mysql_native_password by 'password';