problems upgrading mysql 5.6 => 5.7

[Gary Aitken]

I'm revisiting a problem I never solved a year ago... 11.4 RELEASE

mysql 5.6 was upgraded to 5.7 as a result of a libreoffice install. I've
never been able to use it (mysql) since due to problems upgrading the dbs.

mysqld was always started from a user account (I think...)
The dbs are (I think) in their state prior to the mysql upgrade.
I'm trying to follow the upgrade instructions at
https://dev.mysql.com/doc/refman/5.7/en/upgrade-binary-package.html
When attempting to upgrade in place, the first step is to start the server,
but when doing so I get an access error:

$ mysqld
[Note] mysqld (mysqld 5.7.32-log) starting as process 11334 ...
[Warning] One can only use the --user switch if running as root
...
[Note] InnoDB: Completed initialization of buffer pool
[ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
...
[ERROR] Aborting

$ cat ~/.my.cnf:
[mysqld]
datadir=/hd2/home/me/mysql_data
tmpdir=/hd2/home/me/tmp
innodb_strict_mode=on

The data directory is writable by the user starting mysqld, as are all of the files in it:

$ ls -l
-rw------- 1 me me 56 Oct 15 2016 auto.cnf
-rw-r----- 1 me me 10040 Mar 21 12:47 breakaway.dreamchaser.org.err
-rw------- 1 me me 5 Jan 7 2020 breakaway.pid
-rw------- 1 me me 50331648 Feb 6 2020 ib_logfile0
-rw------- 1 me me 50331648 Oct 15 2016 ib_logfile1
-rw------- 1 me me 27262976 Feb 6 2020 ibdata1
drwx------ 2 me me 2560 Oct 15 2016 mysql
-rw------- 1 me me 0 Feb 8 2020 mysql-bin.index
-rw------- 1 me me 6 Oct 15 2016 mysql_upgrade_info
drwx------ 2 me me 2560 Oct 15 2016 performance_schema

The system directory /var/db/mysqld exists and is owned/writeable by the mysql user, as are all of the files in it.

mysqld is not writing the .err file in the user mysql_data directory.
However, if I use mysqld_safe it fails with the same error, but writes
everything to the .err file.

It appears to be reading ~/.my.cnf properly; if I remove the tmpdir line it complains about not being able to write the temp file:

mysqld: Can't create/write to file '/var/db/mysql_tmpdir/ibCm5Ujq' (Errcode: 13 - Permission denied)

Any ideas what's going on? Everything I've searched for implies the
permissions aren't set properly, but I don't see that.

Gary
_______________________________________________
freebsd-...@freebsd.org mailing list
https://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to "freebsd-questi...@freebsd.org"

[John Levine]

It appears that Gary Aitken <fre...@dreamchaser.org> said:
>$ cat ~/.my.cnf:
>[mysqld]
>datadir=/hd2/home/me/mysql_data
>tmpdir=/hd2/home/me/tmp
>innodb_strict_mode=on
>
>The data directory is writable by the user starting mysqld, as are all of the files in it:
>
>$ ls -l
>-rw------- 1 me me 56 Oct 15 2016 auto.cnf
>-rw-r----- 1 me me 10040 Mar 21 12:47 breakaway.dreamchaser.org.err
>-rw------- 1 me me 5 Jan 7 2020 breakaway.pid
>-rw------- 1 me me 50331648 Feb 6 2020 ib_logfile0
>-rw------- 1 me me 50331648 Oct 15 2016 ib_logfile1
>-rw------- 1 me me 27262976 Feb 6 2020 ibdata1
>drwx------ 2 me me 2560 Oct 15 2016 mysql
>-rw------- 1 me me 0 Feb 8 2020 mysql-bin.index
>-rw------- 1 me me 6 Oct 15 2016 mysql_upgrade_info
>drwx------ 2 me me 2560 Oct 15 2016 performance_schema
>
>The system directory /var/db/mysqld exists and is owned/writeable by the mysql user, as are all of the files in it.

If you're using the mysql-server package, Mysql runs as the mysql
user, user ID 88, and all of its data files should belong
to mysql.

Unless you really REALLY know what you are doing, nothing but mysql should be
writing the files in its database directory so nobody else should have write access to it.

[Gary Aitken]

On 3/21/21 5:58 PM, John Levine wrote:

> If you're using the mysql-server package, Mysql runs as the mysql
> user, user ID 88, and all of its data files should belong
> to mysql.
>
> Unless you really REALLY know what you are doing, nothing but mysql should be
> writing the files in its database directory so nobody else should have write access to it.

Well, I really *don't* know what I'm doing...
I always started mysqld from a user account. I didn't pay attention to the
owner/protections on the files, just set the data dir, and at one point I
changed the permissions of everything in that account to 600; I may have
forced the owner/group as well. I knew mysqld ran as mysql but I thought it
only did that when started by root.
Anyway...

I chmod'd and chown'ed things to mysql:mysql and now get the following:

[Warning] Can't create test file /hd2/home/me/mysql_data/breakaway.lower-test
[Note] mysqld (mysqld 5.7.33-log) starting as process 14257 ...
[Warning] Can't create test file /usr/home/me/mysql_data/breakaway.lower-test
[Warning] Can't create test file /usr/home/me/mysql_data/breakaway.lower-test

[Warning] One can only use the --user switch if running as root

mysqld: File './mysql-bin.index' not found (Errcode: 13 - Permission denied)
[ERROR] Aborting
[Note] Binlog end

There is an old mysql-bin.index file:
~/mysql_data$ ls -l mysql-bin.index
-rwxrwx--- 1 mysql mysql 0 Feb 8 2020 mysql-bin.index
and the directory:
~/mysql_data$ ls -dl .
drwxrwxr-x 12 mysql mysql 1024 Mar 22 13:19 .

so I don't understand what it is complaining about.

aside:
It was suggested to me that I go straight to version 8. However, when
I deinstalled mysql57-client and mysql57-server it also wanted to deinstall
libreoffice. I deleted using -f but when I tried to reinstall
mysql80-server it wanted to reinstall mysql57-client and mysql80-client both,
but then I got this:

Proceed with this action? [y/N]: y
[1/3] Fetching mysql80-server-8.0.23.txz: 100% 18 MiB 263.4kB/s 01:10
[2/3] Fetching mysql57-client-5.7.33.txz: 100% 2 MiB 397.0kB/s 00:05
[3/3] Fetching mysql80-client-8.0.23.txz: 100% 4 MiB 398.4kB/s 00:10
Checking integrity... done (2 conflicting)
- mysql57-client-5.7.33 conflicts with mysql80-server-8.0.23 on /usr/local/bin/mysql_ssl_rsa_setup
- mysql80-client-8.0.23 conflicts with mysql57-client-5.7.33 on /usr/local/bin/mysql
Checking integrity... done (0 conflicting)
Conflicts with the existing packages have been found.
One more solver iteration is needed to resolve them.
The following 3 package(s) will be affected (of 0 checked):

Installed packages to be REMOVED:
libreoffice: 7.0.4.2

New packages to be INSTALLED:
mysql80-client: 8.0.23
mysql80-server: 8.0.23

So I tried deleting libreoffice, installing mysql80, then libreoffice, but
libreoffice wants to replace mysql80-client with mysql57-client.

That's using pkg to install, as opposed to building from ports.

Gary

[John R. Levine]

>> If you're using the mysql-server package, Mysql runs as the mysql
>> user, user ID 88, and all of its data files should belong
>> to mysql.
>>
>> Unless you really REALLY know what you are doing, nothing but mysql should
>> be
>> writing the files in its database directory so nobody else should have
>> write access to it.
>
> Well, I really *don't* know what I'm doing...
> I always started mysqld from a user account. I didn't pay attention to the
> owner/protections on the files, just set the data dir, and at one point I
> changed the permissions of everything in that account to 600; I may have
> forced the owner/group as well. I knew mysqld ran as mysql but I thought it
> only did that when started by root.

Assuming you have superuser access to your freebsd box, I think you will
find that things work better when you run them the way they expect to be
run. The mysql-server script expects to he run by root, usually at system
startup, and then switch to the mysql user.

I do agree that you should get mysql 5.7 working before you try to go to 8.

Regards,
John Levine, jo...@taugh.com, Primary Perpetrator of "The Internet for Dummies",
Please consider the environment before reading this e-mail. https://jl.ly

[Gary Aitken]

On 3/22/21 5:42 PM, John R. Levine wrote:
>>> If you're using the mysql-server package, Mysql runs as the mysql
>>> user, user ID 88, and all of its data files should belong
>>> to mysql.
>>>
>>> Unless you really REALLY know what you are doing, nothing but mysql should be
>>> writing the files in its database directory so nobody else should have write access to it.
>>
>> Well, I really *don't* know what I'm doing...
>> I always started mysqld from a user account.  I didn't pay attention to the
>> owner/protections on the files, just set the data dir, and at one point I
>> changed the permissions of everything in that account to 600; I may have
>> forced the owner/group as well.  I knew mysqld ran as mysql but I thought it
>> only did that when started by root.
>
> Assuming you have superuser access to your freebsd box, I think you will find that things work better when you run them the way they expect to be run.  The mysql-server script expects to he run by root, usually at system startup, and then switch to the mysql user.

It turns out the problem had little to do with permissions or the account
used to start the server, and everything to do with mysql 5.7 default
specifications/changes.

/usr/local/etc/mysql/my.cnf for 5.7 specifies:
innodb_data_file_path = ibdata1:128M:autoextend
and the file in my datadir was smaller.
My ibdata1 file had size 27262976
1024 * 1024 = 1048576
27262976 / 1048576 = 26
I had to add the following to ~/.my.cnf:
innodb_data_file_path=ibdata1:26M:autoextend
innodb_temp_data_file_path=ibtmp1:26M:autoextend
innodb_data_home_dir=/usr/home/me/mysql_data
innodb_log_group_home_dir=/usr/home/me/mysql_data
The server then starts up fine from a normal user account using a data dir
with owner/group of user:user and file permissions of 600.

I found this in the following bug report:
https://bugs.mysql.com/bug.php?id=96497

Gary