Question: Unable to create MySQL database: Error: 71400050: Unable to Create db.
HC is unable to connect MySQL Server
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;
'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:
grant all privileges on *.* to root@IPAddress identified by 'password' with grant option;
However, if this command is executed in MySQL 8, MySQL would throw the following error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'password' with grant option' at line 1
MySQL is throwing this error because starting with MySQL 8 you no longer can implicitly create a user using the GRANT command. Use CREATE USER instead, followed by the GRANT statement:
mysql> CREATE USER 'root'@'IPAddress' IDENTIFIED BY 'root';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'IPAddress' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
After executing these commands, HC will be successfully able to connect with MySQL 8.