Database users after migration?

Discussion in 'Plugins/Modules/Addons' started by Taleman, Jun 22, 2017.

  1. Taleman

    Taleman Member

    Debian Jessie, ISPConfig version 3.1.3.
    I'm testing multiserver setup where I migrated from older ISPConfig 3. I got static websites working after fixing websites where IP address was given as number to use "*".
    Website using a database does not work, it can not access the database. In my setup web server is host aweb5 and all other services including database is on host ispc5. The database user works when accessing from ispc5, but not from aweb5.
    My suspicion is this is because the database user still has server set to the old IP number from the previous server setup. I tried to confirm this by creating a new user with the correct IP number as host. Now the user can log in but seems does not have any rights to use the intended database. I am a n00b on these database things and seems did not create the user correctly.
    But this seems to show that migration tool does not set up database users correctly when after the migration IP numbers have changed and web host is separate from database host.
     
  2. Taleman

    Taleman Member

    Fixed the rights to the database. Now this website works on the TARGET setup.
    I did not find where in the database the users are stored. I was thinking fixing them with a SQL query, but can not find the place to fix. It sems to me I have to do this same fix for every databaseuser.
     
  3. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    Have you checked the contents of the database settings inside ISPConfig? Normally the migration tool checks if db server and web server are the same and if not the public ip that you entered during migration is inserted into remote access for the databases.
     
  4. Taleman

    Taleman Member

    In ISPConfig Panel the System | Server * settings do have the correct IP numbers. During migration run I gave the IP Numbers for the four servers in this multiserver setup, and indicated which hosts websites and which hosts everything else.
     
  5. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    I don't mean the website settings but the "remote access" setting at the databases.
     
  6. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    The system -> server settings are not altered by the migration script. This has to be done when setting up the server as it is not related to migration but part of the base setup.
     
  7. Taleman

    Taleman Member

    Remote access is ticked, but the IP number is the old one. I just noticed the Site is wrong in the database settings in ISPConfig Panel for this one database.
    It looks to me all database users have wrong host. It is either the old IP number, localhost or percent sign. The percent sign might work but the others not.
     
  8. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    Localhost means remote access is disabled inside ISPConfig, percent means it is enabled without an ip specified.
    It seems there is a bug in the ip address handling for remote access until 1.6.9. Should be resolved in next version that will be released soon.
     
  9. Taleman

    Taleman Member

    I finally found the database users are stored in mysql.user -table. But there are users where host is localhost and should stay that way. I did not come up with a query to do the change correctly. Maybe tomorrow after some rest. I can change the old IP-number to new, that should not go wrong. For those localhost and % hosts I need to do some thinking. Tomorrow.
    I was planning to put the TARGET system to production use this weekend, so I have to fix this now and not wait for updated migration tool.
     
  10. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    The mysql.user table is the system part. ISPConfig stores the ip in web_database in the remote_ips column. You could update it via sql query there and then do a resync of the databases via Tools -> Resync in ISPConfig.
     

Share This Page