HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)
-   Installation/Configuration (http://www.howtoforge.com/forums/forumdisplay.php?f=27)
-   -   Mysql problem (http://www.howtoforge.com/forums/showthread.php?t=59989)

lollollollol 18th December 2012 13:19

Mysql problem
 
Hello,

Debian7, Ispconfig 3.0.4.6, Physical server at OVH.
mysql-server: Installed: 5.5.24+dfsg-4

I have discoverd an issue in my ispconfig3: "add new database" doesn't insert database & users in Mysql:

Here the logs from mysql after create a database:
Code:

121218 13:02:30        30755 Connect        ispconfig@localhost on
                30755 Query        SET NAMES utf8
                30755 Query        SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'
                30755 Init DB        dbispconfig
                30755 Query        SELECT * FROM sys_session WHERE session_id = 'rlvdl22gk2qrp5toqevrtner22'
                30755 Query        SET NAMES utf8
                30755 Query        SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'
                30755 Init DB        dbispconfig
                30755 Query        SELECT config FROM sys_ini WHERE sysini_id = 1
                30755 Init DB        dbispconfig
                30755 Query        SELECT client_id FROM sys_group WHERE groupid = 0
                30755 Init DB        dbispconfig
                30755 Query        SELECT client_id FROM sys_group WHERE groupid = 0
                30755 Init DB        dbispconfig
                30755 Query        SELECT count(database_id) as dbnum FROM web_database WHERE database_name = 'c0test' AND server_id = '1'
                30755 Init DB        dbispconfig
                30755 Query        SELECT count(*) as number FROM `web_database` WHERE database_user = 'c0test' AND database_id != 0
                30755 Init DB        dbispconfig
                30755 Query        INSERT INTO `web_database` (`server_id`, `type`, `database_name`, `database_user`, `database_password`, `database_charset`, `remote_access`, `active`, `remote_ips`, `sys_userid`, `sys_groupid`, `sys_perm_user`, `sys_perm_group`, `sys_perm_other`) VALUES ('1', 'mysql', 'c0test', 'c0test', PASSWORD('testtesttest'), '', 'n', 'y', '', '1', '1', 'riud', 'riud', '')
                30755 Init DB        dbispconfig
                30755 Query        UPDATE web_database SET sys_groupid = 0, sys_perm_group = 'ru' WHERE database_id = 15
                30755 Init DB        dbispconfig
                30755 Query        SELECT client.limit_client FROM sys_user, client WHERE sys_user.userid = 1 AND sys_user.client_id = client.client_id
                30755 Init DB        dbispconfig
                30755 Query        SELECT * FROM `web_database` WHERE database_id = 15
                30755 Init DB        dbispconfig
                30755 Query        INSERT INTO sys_datalog (dbtable,dbidx,server_id,action,tstamp,user,data) VALUES ('web_database','database_id:15','1','i','1355832150','admin','a:2:{s:3:\"new\";a:15:{s:11:\"database_id\";s:2:\"15\";s:10:\"sys_userid\";s:1:\"1\";s:11:\"sys_groupid\";s:1:\"0\";s:13:\"sys_perm_user\";s:4:\"riud\";s:14:\"sys_perm_group\";s:2:\"ru\";s:14:\"sys_perm_other\";s:0:\"\";s:9:\"server_id\";s:1:\"1\";s:4:\"type\";s:5:\"mysql\";s:13:\"database_name\";s:6:\"c0test\";s:13:\"database_user\";s:6:\"c0test\";s:17:\"database_password\";s:41:\"*7032DC61FAF4DEC266EBA762C328FF49AB0EB7E7\";s:16:\"database_charset\";s:0:\"\";s:13:\"remote_access\";s:1:\"n\";s:10:\"remote_ips\";s:0:\"\";s:6:\"active\";s:1:\"y\";}s:3:\"old\";a:15:{s:11:\"database_id\";N;s:10:\"sys_userid\";N;s:11:\"sys_groupid\";N;s:13:\"sys_perm_user\";N;s:14:\"sys_perm_group\";N;s:14:\"sys_perm_other\";s:0:\"\";s:9:\"server_id\";N;s:4:\"type\";N;s:13:\"database_name\";N;s:13:\"database_user\";N;s:17:\"database_password\";N;s:16:\"database_charset\";s:0:\"\";s:13:\"remote_access\";N;s:10:\"remote_ips\";s:0:\"\";s:6:\"active\";N;}}')
                30755 Init DB        dbispconfig
                30755 Query        UPDATE sys_session SET last_updated = '2012-12-18 12:02:30' WHERE session_id = 'rlvdl22gk2qrp5toqevrtner22'
                30755 Init DB        dbispconfig
                30755 Query        DELETE FROM sys_session WHERE last_updated < '2012-12-18 11:38:30'
                30755 Quit       
121218 13:02:31        30756 Connect        ispconfig@localhost on
                30756 Query        SET NAMES utf8
                30756 Query        SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'
                30756 Init DB        dbispconfig
                30756 Query        SELECT * FROM sys_session WHERE session_id = 'rlvdl22gk2qrp5toqevrtner22'
                30756 Query        SET NAMES utf8
                30756 Query        SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'
                30756 Init DB        dbispconfig
                30756 Query        SELECT groupid, name FROM sys_group WHERE groupid != 1 ORDER BY name
                30756 Init DB        dbispconfig
                30756 Query        SELECT server_id,server_name FROM server WHERE 1 AND db_server = 1 ORDER BY server_name
                30756 Init DB        dbispconfig
                30756 Query        SELECT count(*) AS anzahl FROM web_database WHERE 1
                30756 Init DB        dbispconfig
                30756 Query        SELECT * FROM web_database WHERE 1  LIMIT 0, 15
                30756 Init DB        dbispconfig
                30756 Query        SELECT config FROM sys_ini WHERE sysini_id = 1
                30756 Init DB        dbispconfig
                30756 Query        SELECT client.limit_client FROM sys_user, client WHERE sys_user.userid = 1 AND sys_user.client_id = client.client_id
                30756 Init DB        dbispconfig
                30756 Query        UPDATE sys_session SET last_updated = '2012-12-18 12:02:31' WHERE session_id = 'rlvdl22gk2qrp5toqevrtner22'
                30756 Init DB        dbispconfig
                30756 Query        DELETE FROM sys_session WHERE last_updated < '2012-12-18 11:38:31'
                30756 Quit

But I can't connect in mysql with this new database:
Code:

# mysql -u c0test -p
Enter password:
ERROR 1045 (28000): Access denied for user 'c0test'@'localhost' (using password: YES)

And it's normal...
Code:

mysql> select host, user from mysql.user;
+-------------------+------------------+
| host              | user            |
+-------------------+------------------+
| 127.0.0.1        | root            |
| ::1              | root            |
| localhost        |                  |
| localhost        | c0base1          |
etc...
| localhost        | c1base9          |
| localhost        | debian-sys-maint |
| localhost        | ispconfig        |
| localhost        | root            |
| mail.karmaweb.biz |                  |
| mail.karmaweb.biz | root            |
+-------------------+------------------+
19 rows in set (0.00 sec)

No database inserted, no user inserted.

If you have an idea what might be going on, you're welcome, I do not understand ... :confused:

lollollollol 18th December 2012 13:26

Sorry, an important detail:
Database in created:
Code:

show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
Etc...
| c0test            |
| dbispconfig        |
| mysql              |
| performance_schema |
| test              |
+--------------------+


till 18th December 2012 13:35

The most likely reason is that you change dthe mysql root password after you installed ispconfig and the new password was not set in /usr/local/ispconfug/server/lib/mysql_clientdb.conf

lollollollol 18th December 2012 13:48

Hello Till,

You are right about the fact that I was forced to change the password for mysql root, I had some small problems with the installation.

Unfortunately the password in /usr/local/ispconfig/server/lib/mysql_clientdb.conf is good .

Besides the base is created, which proves that ispconfig can access mysql, isn't it ?

till 18th December 2012 13:54

Quote:

Besides the base is created, which proves that ispconfig can access mysql, isn't it ?
Mysql users have all kin of different permissions, so if you altered the root user, it might be that the user does not has permissions to create the mysql user while he has permissions to create the database. Check all users ib mysql with the username that is in the mysql_clientdb.conf and ensure that they have set all permissions to 'y'.

lollollollol 18th December 2012 14:28

Hi,
Of course you are right (as usual) !
As you can see below, I have "too much" root user...

Code:

select host, user from mysql.user;
+-------------------+------------------+
| host              | user            |
+-------------------+------------------+
| 127.0.0.1        | root            |
| ::1              | root            |
| localhost        |                  |
etc.
| localhost        | root            |
| mail.karmaweb.biz |                  |
| mail.karmaweb.biz | root            |
+-------------------+------------------+
21 rows in set (0.00 sec)

One for localhost, and one for 127.0.0.1...
root@127.0.0.1 have all privileges, not root@localhost.

I messed up my mysql configuration.
It's now corrected.

By the way: Thanks for the fantastic job on ispconfig!
This is a must.


All times are GMT +2. The time now is 15:49.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.