Reconfigure SQL Server failes - 1.3.x Branch

**Levitas** · Jun 1st 2016

Hello there

if you try to change the sql server you have to re enter the database root user.
The user is disabled by the imscp setup (since 1.3.x)

If you want to change the sql server (e.g. 5.5 to 5.7) you have to reenter the root user.
The root user cannot login and the update / reconfigure fails.

greets

/edit
sql manager -> sql server

**Nuxwin** · Jun 1st 2016

@Levitas

You're wrong

The SQL root user is not disabled. It is just not longer used by i-MSCP (excepted during installation when really needed)
When you're asked for the SQL root user, you must provide the correct info. Here, I presume that you entered a bad hostname. Try with 127.0.0.1 too It is a known issue. E.g, in some contexts (for instance, with MySQL 5.7), the root@localhost entry is not created (or removed) (only the root@127.0.0.1 entry stay there).

To resume, when asked for the root SQL user by the i-MSCP installer, you must enter in order:

The username: root
The password: <your SQL root password>
The hostname: localhost or 127.0.0.1 if localhost doesn't work

i-MSCP doesn't alters any data for the SQL root user (that user is created by Debian package maintainer script).

BTW: What is the SQL manager for you

**Levitas** · Jun 1st 2016

Okay. Thanks for your fast reply. So the problem seems to exist because of the update to 5.7.
I thought that the root login will be disabled by imscp.

I'll try to connect as root@127.0.0.1 instead of root.
I'll edit this post if im done.

Thank you.

**Nuxwin** · Jun 1st 2016

No !

You must still enter root for the username but when you're asked for the hostname, you must enter 127.0.0.1. There is one dialog for the SQL root username and another for the hostname.

**Levitas** · Jun 1st 2016

Didnt noticed you answered so i'll write it down here

If i try to use the username root@127.0.0.1 or 'root'@'127.0.0.1' the installer tries use 'root'@'127.0.0.1'@'localhost'
even if i use "127.0.0.1" as the hostname
Did i do something wrong? (The password is correct)

Images:

Nuxwin:

**Nuxwin** · Jun 1st 2016

@Levitas

There is something strange (127.0.0.1 seems mapped to localhost on your system). Well, could you provide me an access?

**Levitas** · Jun 1st 2016

done.

I'll send the details in a pn

**Nuxwin** · Jun 1st 2016

@Levitas

Problem found. On your SQL installation, there is no common entries for the SQL root user. There is only an entry for root@::1 at this moment:

SQL

mysql> select user, host from user;+------------------+-----------+| user | host |+------------------+-----------+| <dummy> | % || <dummy> | 127.0.0.1 || <dummy> | 127.0.0.1 || root | ::1 || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || debian-sys-maint | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost || <dummy> | localhost |+------------------+-----------+28 rows in set (0,00 sec)mysql> \q

Note: <dummy> users are another users.

I've fixed the problem by re-creating the missing users using the debian-sys-maint user:

SQL

# mysql -h 127.0.0.1 -u debian-sys-maint -p<password>mysql> CREATE USER 'root'@'localhost' IDENTIFIED BY '<password>';Query OK, 0 rows affected (0,00 sec)mysql> GRANT ALL PRIVILEGES on *.* to 'root'@'localhost' WITH GRANT OPTION;Query OK, 0 rows affected (0,00 sec)mysql> CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY '<password>';Query OK, 0 rows affected (0,01 sec)mysql> GRANT ALL PRIVILEGES on *.* to 'root'@'127.0.0.1' WITH GRANT OPTION;Query OK, 0 rows affected (0,01 sec)mysql> flush privileges;Query OK, 0 rows affected (0,00 sec)mysql> \q

Once done, I was able to connect using root@localhost user:

SQL

root@web1:~# mysql -u root -p<password>
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1989
Server version: 5.7.12 MySQL Community Server (GPL)
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT user, host FROM user WHERE user = 'root';
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+------+-----------+
3 rows in set (0,00 sec)
mysql> \q

Display More

You can now rerun the i-MSCP installer.

**batZen** · Jun 26th 2016

i think i got a similiar problem, after upgrading to 1.3.0 i can't login with the root user anymore...

Code

+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
+------+-----------+
2 rows in set (0.00 sec)

this users are created, other user like the new imscp_user works..

**Nuxwin** · Jun 26th 2016

@batZen

For unknow reason, root@localhost entry is removed. Use 127.0.0.1.

Reconfigure SQL Server failes - 1.3.x Branch

Share

Daily visitors