Mysql access after clean Mageia 9 install

[Ar]

I copied over all the MySQL stuff from the old install to the new clean
install of Mageia9, but in the new install it is not running properly.

Mageia 9, x86_64, fully up to date.

Hostname has been set to "rooster", it is remembered after reboots.

When I try logging in to Mysql with the following command and the
password I set up in the Mag7 install, I am granted access to MySQL.
mysql -u root -p


However, when I try access the database as I have done for years with
the following and the same password:
mysql rooster -u root -p
I get the error:
ERROR 2002 (HY000): Can't connect to server on 'rooster' (115)

If I try:
mysql -h 127.0.0.1 -u root -p
I get the error:
ERROR 2002 (HY000): Can't connect to server on '127.0.0.1' (115)


Searching online suggests a configuration problem, but of what?

MySQL runs, one database is confirmed working, digikam says there's an
unspecified error with its database - If I need to I can make a new one,
but would lose ratings and other data of photos, so would like to save it.

I can't check on the digikam database without access to mysql in the
first place. I have intentionally destroyed the "mysql" database and all
the other files as if it was a new install. The system re-creates all
the files needed to run, but the same problem exists.

Does anyone have any ideas what's going on, maybe some file I forgot to
copy over. I did copy my.cnf from the older install.

[David W. Hodgins]

Post by Ar
I copied over all the MySQL stuff from the old install to the new clean
install of Mageia9, but in the new install it is not running properly.
Mageia 9, x86_64, fully up to date.
Hostname has been set to "rooster", it is remembered after reboots.
When I try logging in to Mysql with the following command and the
password I set up in the Mag7 install, I am granted access to MySQL.
mysql -u root -p
However, when I try access the database as I have done for years with
mysql rooster -u root -p
ERROR 2002 (HY000): Can't connect to server on 'rooster' (115)
mysql -h 127.0.0.1 -u root -p
ERROR 2002 (HY000): Can't connect to server on '127.0.0.1' (115)
Searching online suggests a configuration problem, but of what?
MySQL runs, one database is confirmed working, digikam says there's an
unspecified error with its database - If I need to I can make a new one,
but would lose ratings and other data of photos, so would like to save it.
I can't check on the digikam database without access to mysql in the
first place. I have intentionally destroyed the "mysql" database and all
the other files as if it was a new install. The system re-creates all
the files needed to run, but the same problem exists.
Does anyone have any ideas what's going on, maybe some file I forgot to
copy over. I did copy my.cnf from the older install.

There's more than one place where the database files are stored.

The mysql command error is likely due to the system wide mysql server not
running. The system wide database is normally stored in "/var/lib/mysql/"
What does "systemctl status mysqld.service" show?

The digikam error is due to the digikam's own database, by default that is a
mysql database stored in "~/Pictures/*.db".

I'm not sure how to go about upgrading the digikam sql db. The Mageia upgrade
procedure is not allowed to change files in /home, as the directories there may
or may not be accessible during the upgrade. For example, I use sshfs to mount
a directory during my login. The root user cannot access any files or directories
in that mount.

Regards, Dave Hodgins

[Ar]

Post by David W. Hodgins

Post by Ar
Searching online suggests a configuration problem, but of what?

There's more than one place where the database files are stored.
The mysql command error is likely due to the system wide mysql server not
running. The system wide database is normally stored in "/var/lib/mysql/"
What does "systemctl status mysqld.service" show?
The digikam error is due to the digikam's own database, by default that is a
mysql database stored in "~/Pictures/*.db".
I'm not sure how to go about upgrading the digikam sql db. The Mageia upgrade
procedure is not allowed to change files in /home, as the directories there may
or may not be accessible during the upgrade. For example, I use sshfs to mount
a directory during my login. The root user cannot access any files or directories
in that mount.

Hi David, apologies for not replying sooner.

It's taken me a long while and lots of frustration to figure out what
has gone wrong, and try fixing the clean install. So I'll make it short.

I was booting between Mageia 8 and Mageia 9 copying over files I had
missed in archiving before I decided on the clean install. However, I
read a long time ago that it was advised that user IDs on install would
be changed from 1000 upwards. So some years ago I changed the user IDs
on the system to start from 1000.

Unknown to me (because it doesn't show in the installation screen IIRC),
Mageia still makes the user 500. So logging in between the two, caused a
lot of mess in my /home drive, especially with the KDE plasma install.

As for the databases, I had a lot of problems getting MySql (MariaDB) to
work properly with sockets and a named server or 127.0.0.1. I had a lot
of "restarted server too fast, reached your limit" nonsense, which
eventually I figured could only be fixed with reboots. Permissions on
the directories weren't the only problem.

The databases themselves, Amarok was fine, the databases I made for my
work worked fine, but for some reason digikam database had lots of
issues that it was like fighting an oil fire. So I decided to rename it,
and start a new digikam database from scratch, losing tags and other
information, but that's not a Critical problem.

I still need to sort Apache permissions, but that's a different problem.

I've since got an SSD enclosure so anything I need from the old install
I plug in to USB3 and avoid making any more mess of /home.

Thanks for your help and suggestions.

[David W. Hodgins]

Post by Ar

Post by David W. Hodgins

Post by Ar
Searching online suggests a configuration problem, but of what?

There's more than one place where the database files are stored.
The mysql command error is likely due to the system wide mysql server not
running. The system wide database is normally stored in "/var/lib/mysql/"
What does "systemctl status mysqld.service" show?
The digikam error is due to the digikam's own database, by default that is a
mysql database stored in "~/Pictures/*.db".
I'm not sure how to go about upgrading the digikam sql db. The Mageia upgrade
procedure is not allowed to change files in /home, as the directories there may
or may not be accessible during the upgrade. For example, I use sshfs to mount
a directory during my login. The root user cannot access any files or directories
in that mount.

Hi David, apologies for not replying sooner.
It's taken me a long while and lots of frustration to figure out what
has gone wrong, and try fixing the clean install. So I'll make it short.
I was booting between Mageia 8 and Mageia 9 copying over files I had
missed in archiving before I decided on the clean install. However, I
read a long time ago that it was advised that user IDs on install would
be changed from 1000 upwards. So some years ago I changed the user IDs
on the system to start from 1000.
Unknown to me (because it doesn't show in the installation screen IIRC),
Mageia still makes the user 500. So logging in between the two, caused a
lot of mess in my /home drive, especially with the KDE plasma install.
As for the databases, I had a lot of problems getting MySql (MariaDB) to
work properly with sockets and a named server or 127.0.0.1. I had a lot
of "restarted server too fast, reached your limit" nonsense, which
eventually I figured could only be fixed with reboots. Permissions on
the directories weren't the only problem.
The databases themselves, Amarok was fine, the databases I made for my
work worked fine, but for some reason digikam database had lots of
issues that it was like fighting an oil fire. So I decided to rename it,
and start a new digikam database from scratch, losing tags and other
information, but that's not a Critical problem.
I still need to sort Apache permissions, but that's a different problem.
I've since got an SSD enclosure so anything I need from the old install
I plug in to USB3 and avoid making any more mess of /home.
Thanks for your help and suggestions.

Before copying files from the old install to the new install, use
"tree -ifaug /home/dave" to get a list of files with their owner and group.
Make note of any that are not $USER:$USER.

To fix the ownership problems in /home/$USER use (run as root) ...
# chown -R dave:dave /home/dave

As I use imap with dovecot, I also have to then run ...
# chgrp -R mail /home/dave/mail

In the case of my sshfs files, I make sure it's not mounted when I run the chwon
command. The root user can't change those files, so unmounting it just avoids
the error message(s).

The default starting userid is controlled by ...
$ grep -e ^UID_MIN -e ^GID_MIN /etc/login.defs
UID_MIN 1000
GID_MIN 1000

Having the first user id set 500 should only happen if you copied the login.defs
file from the old install or if /etc/passwd was copied and the old user had a
uid of 500 in that file.

Getting the uid/gid of different system level applications such as mysql the
same in two installs can not be relied on. The uid/gid of those depend on the
order packages are installed. If two applications happen to get installed in
the same rpm transaction, the order is not guaranteed, to two seemingly
identical installs may have different different uid/gid numbers for a given
application. Copying files for system level applications must be done carefully.

Regards, Dave Hodgins