From owner-freebsd-questions@freebsd.org Fri Mar 26 17:48:34 2021 Return-Path: Delivered-To: freebsd-questions@mailman.nyi.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2610:1c1:1:606c::19:1]) by mailman.nyi.freebsd.org (Postfix) with ESMTP id 9EDDE57ED31 for ; Fri, 26 Mar 2021 17:48:34 +0000 (UTC) (envelope-from freebsd@dreamchaser.org) Received: from nightmare.dreamchaser.org (ns.dreamchaser.org [66.109.141.57]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "dreamchaser.org", Issuer "R3" (verified OK)) by mx1.freebsd.org (Postfix) with ESMTPS id 4F6Twd627tz3GWC for ; Fri, 26 Mar 2021 17:48:32 +0000 (UTC) (envelope-from freebsd@dreamchaser.org) Received: from breakaway.dreamchaser.org (breakaway [192.168.151.122]) by nightmare.dreamchaser.org (8.15.2/8.15.2) with ESMTP id 12QHmUcs068664; Fri, 26 Mar 2021 11:48:30 -0600 (MDT) (envelope-from freebsd@dreamchaser.org) Reply-To: freebsd@dreamchaser.org Subject: Re: problems upgrading mysql 5.6 => 5.7 (SOLVED) To: "John R. Levine" , freebsd-questions@freebsd.org References: <20210321235845.E3B5870DD030@ary.qy> <5ec345c3-01a6-f959-0971-5836ecc85e20@dreamchaser.org> <64d150fd-9de-dacf-d88b-2dc57addaf15@iecc.com> From: Gary Aitken Message-ID: <6845a7af-c8fa-dc0d-5ba0-c735bc8aadeb@dreamchaser.org> Date: Fri, 26 Mar 2021 11:48:30 -0600 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:78.0) Gecko/20100101 Thunderbird/78.6.1 MIME-Version: 1.0 In-Reply-To: <64d150fd-9de-dacf-d88b-2dc57addaf15@iecc.com> Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit X-Greylist: Sender IP whitelisted, not delayed by milter-greylist-4.6.2 (nightmare.dreamchaser.org [192.168.151.101]); Fri, 26 Mar 2021 11:48:31 -0600 (MDT) X-Rspamd-Queue-Id: 4F6Twd627tz3GWC X-Spamd-Bar: --- Authentication-Results: mx1.freebsd.org; dkim=none; dmarc=none; spf=pass (mx1.freebsd.org: domain of freebsd@dreamchaser.org designates 66.109.141.57 as permitted sender) smtp.mailfrom=freebsd@dreamchaser.org X-Spamd-Result: default: False [-3.30 / 15.00]; ARC_NA(0.00)[]; HAS_REPLYTO(0.00)[freebsd@dreamchaser.org]; MID_RHS_MATCH_FROM(0.00)[]; FROM_HAS_DN(0.00)[]; TO_DN_SOME(0.00)[]; R_SPF_ALLOW(-0.20)[+mx:c]; NEURAL_HAM_LONG(-1.00)[-1.000]; MIME_GOOD(-0.10)[text/plain]; REPLYTO_ADDR_EQ_FROM(0.00)[]; DMARC_NA(0.00)[dreamchaser.org]; SPAMHAUS_ZRD(0.00)[66.109.141.57:from:127.0.2.255]; TO_MATCH_ENVRCPT_SOME(0.00)[]; RBL_DBL_DONT_QUERY_IPS(0.00)[66.109.141.57:from]; NEURAL_HAM_SHORT(-1.00)[-0.998]; RCPT_COUNT_TWO(0.00)[2]; NEURAL_HAM_MEDIUM(-1.00)[-1.000]; RCVD_TLS_LAST(0.00)[]; FROM_EQ_ENVFROM(0.00)[]; R_DKIM_NA(0.00)[]; MIME_TRACE(0.00)[0:+]; ASN(0.00)[asn:21947, ipnet:66.109.128.0/19, country:US]; RCVD_COUNT_TWO(0.00)[2]; MAILMAN_DEST(0.00)[freebsd-questions] X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.34 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 26 Mar 2021 17:48:34 -0000 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