[BlueOnyx:24385] Re: In mysql, user at localhost and user at 127.0.0.1 are different.

[Tomohiro Hosaka]

Hi Michael,

Thanks for the reply.

> Why is (was) that the case?

I don't know. I've never encountered that case.
I think it's an OpenVZ-specific problem.

> In the end it's not really *that* important. The GUI creates the MySQL users and you can use them.

In the case of a new installation (create or delete), I think that's true.
But in the case of a migration, I personally think that's not enough.

---

I made the following changes in migrating from 5208R(bare metal) to
5209R(bhyve). Just a report.

1. Make a clear distinction between localhost and 127.0.0.1 in mysql.

The changes to the code are as described in the previous email.

2. We have decided not to move to suPHP and keep the DSO model.

3. Removed --chown=$DirOwner in rsync to /web.

Due to the change in user permissions, the user's disk quota was also
affected and mail could not be received.

4. A patch was applied to em-helper.pl.

If a backslash is included in the db name, an invalid
/home/.easy-migrate/mysqlusergrants.sql is created and the DB with the
same name is not created in the migrated destination.

source: show grants for user at localhost
GRANT SELECT, INSERT, UPDATE, DELETE ON `dbname\_db`.* TO 'user'@'localhost'

source: cat /home/.easy-migrate/mysqlusergrants.sql
GRANT SELECT, INSERT, UPDATE, DELETE ON `dbname\\_db`.* TO 'user'@'localhost'

destination: show grants for user at localhost
GRANT SELECT, INSERT, UPDATE, DELETE ON `dbname\\_db`.* TO 'user'@'localhost'

--- /usr/sausalito/bin/em-helper.pl 2020-03-05 06:54:15.000000000 +0900
+++ /usr/sausalito/bin/em-helper.pl.mod 2020-10-12 13:00:43.897203193 +0900
@@ -572,7 +572,7 @@
     if ($ok) {
         my $mysqlroot = $MySQL->{'sql_root'};
         my $mysqlpass = $MySQL->{'sql_rootpassword'};
-        system("mysql -u $mysqlroot -p$mysqlpass --skip-column-names
-A -e\"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''\@''',host,''';')
FROM mysql.user WHERE user NOT IN ('root','avspam')\" | mysql -u
$mysqlroot -p$mysqlpass --skip-column-names -A | sed 's/\$/;/g' >
/home/.easy-migrate/mysqlusergrants.sql");
+        system("mysql -u $mysqlroot -p$mysqlpass --skip-column-names
-A -e\"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''\@''',host,''';')
FROM mysql.user WHERE user NOT IN ('root','avspam')\" | mysql -u
$mysqlroot -p$mysqlpass --skip-column-names -A -r | sed 's/\$/;/g' >
/home/.easy-migrate/mysqlusergrants.sql");
         my $dblist = `mysql -u $mysqlroot -p$mysqlpass -ANe\"SELECT
schema_name FROM information_schema.schemata WHERE schema_name NOT IN
('mysql','test','information_schema','performance_schema','avspam6')\"`;
         $dblist = join " ", split "\n", $dblist;
         system("mysqldump -u $mysqlroot -p$mysqlpass --routines
--triggers --single-transaction --databases $dblist >
/home/.easy-migrate/mysql-all-dbs.sql");

5. new Unix::PasswdFile to retry

If the file lock fails, undef is returned and it does not work.

Unix::ConfigFile is a base class.
https://metacpan.org/release/Unix-ConfigFile/source/ConfigFile.pm#L64

We ran into this multiple times while running easy-migrate.pl, so we
prepared a dirty patch.

--- /usr/sausalito/sbin/easy-migrate.pl 2020-03-05 06:54:15.000000000 +0900
+++ /usr/sausalito/sbin/easy-migrate.pl-mod 2020-09-30 00:40:44.942912947 +0900
@@ -1912,6 +1930,13 @@

                 # Parse /etc/passwd to find out where the User has
his home directory:
                 $pw = new Unix::PasswdFile "/etc/passwd";
+                for (1..5) {
+                    $pw ||= do {
+                        print "new Unix::PasswdFile /etc/passwd
failed. sleep 1 and retry.\n";
+                        sleep 1;
+                        new Unix::PasswdFile "/etc/passwd";
+                    };
+                }
                 $userDir = $pw->home($import_username) . '/';
                 undef $pw;

6. cgiwrap will not work if mod_ruid is enabled.

This is not a change.

The error is:
    CGIWrap Error: Server UserID Mismatch
    The userid that the web server ran cgiwrap as does not match the
userid that was configured into the cgiwrap executable. This is a
configuration/setup problem with cgiwrap on this server. Please
contact the server administrator.

It worked with the following comment out.
    /etc/httpd/conf/vhosts/siteXX:
        # <IfModule mod_ruid2.c>
        #      RMode config
        #      RUidGid userXX siteXX
        # </IfModule>

If this combination doesn't work, then it would be nice to have some
sort of warning or make it impossible to configure it.

---

Thank you for your constant improvement.