Mysql problem

Discussion in 'Installation/Configuration' started by lollollollol, Dec 18, 2012.

  1. lollollollol

    lollollollol Member

    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
    		[B][COLOR="Red"]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', '')[/COLOR][/B]
    		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:
     
  2. lollollollol

    lollollollol Member

    Sorry, an important detail:
    Database in created:
    Code:
    show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    Etc...
    | c0test             |
    | dbispconfig        |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
     
  3. till

    till Super Moderator Howtoforge Staff HowtoForge Supporter ISPConfig Developer

    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
     
  4. lollollollol

    lollollollol Member

    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 ?
     
  5. till

    till Super Moderator Howtoforge Staff HowtoForge Supporter ISPConfig Developer

    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'.
     
  6. lollollollol

    lollollollol Member

    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.
     

Share This Page