Solved Mysql80 will not start

Install on a fresh disk after installation of OS. Here is the environment;
Code:
Copyright (c) 1992-2021 The FreeBSD Project.
Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994
        The Regents of the University of California. All rights reserved.
FreeBSD is a registered trademark of The FreeBSD Foundation.
FreeBSD 13.1-RELEASE releng/13.1-n250148-fc952ac2212 GENERIC amd64
FreeBSD clang version 13.0.0 (git@github.com:llvm/llvm-project.git llvmorg-13.0.0-0-gd7b669b3a303)
VT(vga): resolution 640x480
CPU: AMD FX(tm)-8350 Eight-Core Processor            (4018.42-MHz K8-class CPU)
  Origin="AuthenticAMD"  Id=0x600f20  Family=0x15  Model=0x2  Stepping=0
  Features=0x178bfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CLFLUSH,MMX,FXSR,SSE,SSE2,HTT>
  Features2=0x3e98320b<SSE3,PCLMULQDQ,MON,SSSE3,FMA,CX16,SSE4.1,SSE4.2,POPCNT,AESNI,XSAVE,OSXSAVE,AVX,F16C>
  AMD Features=0x2e500800<SYSCALL,NX,MMX+,FFXSR,Page1GB,RDTSCP,LM>
  AMD Features2=0x1ebbfff<LAHF,CMP,SVM,ExtAPIC,CR8,ABM,SSE4A,MAS,Prefetch,OSVW,IBS,XOP,SKINIT,WDT,LWP,FMA4,TCE,NodeId,TBM,Topology,PCXC,PNXC>
  Structured Extended Features=0x8<BMI1>
  SVM: NP,NRIP,VClean,AFlush,DAssist,NAsids=65536
  TSC: P-state invariant, performance statistics
real memory  = 25769803776 (24576 MB)
avail memory = 24954712064 (23798 MB)
It installed with no problems
Code:
cd /usr/ports/databases/mysql80-client/ && make install clean
cd /usr/ports/databases/mysql80-server/ && make install clean

root@Phaedra:/ # pkg info "mysql*"
mysql80-client-8.0.29
mysql80-server-8.0.29
root@Phaedra:/ #
Here is my.cnf
Code:
[client]
port                            = 3306
socket                          = /tmp/mysql.sock

[mysql]
prompt                          = \u@\h [\d]>\_
no_auto_rehash

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /storage3/db/mysql
tmpdir                          = /storage3/db/mysql_tmpdir
slave-load-tmpdir               = /storage3/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure
log-bin                         = mysql-bin
log-output                      = TABLE
master-info-repository          = TABLE
relay-log-info-repository       = TABLE
relay-log-recovery              = 1
slow-query-log                  = 1
server-id                       = 1
sync_binlog                     = 1
sync_relay_log                  = 1
binlog_cache_size               = 16M
expire_logs_days                = 30
default_password_lifetime       = 0
enforce-gtid-consistency        = 1
gtid-mode                       = ON
safe-user-create                = 1
lower_case_table_names          = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options          = BACKUP,FORCE
open_files_limit                = 32768
table_open_cache                = 16384
table_definition_cache          = 8192
net_retry_count                 = 16384
key_buffer_size                 = 256M
max_allowed_packet              = 64M
long_query_time                 = 0.5
innodb_buffer_pool_size         = 1G
innodb_data_home_dir            = /storage3/db/mysql
innodb_log_group_home_dir       = /storage3/db/mysql
innodb_data_file_path           = ibdata1:18M:autoextend
innodb_temp_data_file_path      = ibtmp1:18M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
skip-symbolic-links


[mysqldump]
max_allowed_packet              = 256M
quote_names
quick
~
I learned along time ago to keep data off of the disk where the OS has been installed so I have a separate drive, /storage3 that only has the data base and files. When I try to start it here is the results.
Code:
root@Phaedra:/ # [code]
root@Phaedra:/ # mysqld_safe &
[1] 97250
root@Phaedra:/ # 2022-05-28T21:05:31.6NZ mysqld_safe Logging to '/storage3/db/mysql/Phaedra.err'.
2022-05-28T21:05:31.6NZ mysqld_safe Starting mysqld daemon with databases from /storage3/db/mysql

root@Phaedra:/ # 2022-05-28T21:05:33.6NZ mysqld_safe mysqld from pid file /storage3/db/mysql/Phaedra.pid ended

[1]    Done                          mysqld_safe
 
root@Phaedra:/ #
And here is the error;
Code:
root@Phaedra:/storage3/db/mysql # view Phaedra.err
2022-05-28T14:50:15.399454Z 0 [Warning] [MY-011068] [Server] The syntax 'slave_load_tmpdir' is deprecated and will be removed in a future release. Please us
e replica_load_tmpdir instead.
2022-05-28T14:50:15.399470Z 0 [Warning] [MY-011069] [Server] The syntax '--master-info-repository' is deprecated and will be removed in a future release.
2022-05-28T14:50:15.399482Z 0 [Warning] [MY-011069] [Server] The syntax '--relay-log-info-repository' is deprecated and will be removed in a future release.
2022-05-28T14:50:15.399506Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use
 binlog_expire_logs_seconds instead.
2022-05-28T14:50:15.399561Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider
not using this option as it' is deprecated and will be removed in a future release.
2022-05-28T14:50:15.401217Z 0 [System] [MY-010116] [Server] /usr/local/libexec/mysqld (mysqld 8.0.29) starting as process 96556
2022-05-28T14:50:15.431871Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-05-28T14:50:16.711257Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-05-28T14:50:16.736107Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2022-05-28T14:50:16.736428Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-05-28T14:50:16.736630Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-05-28T14:50:16.996067Z 0 [System] [MY-010910] [Server] /usr/local/libexec/mysqld: Shutdown complete (mysqld 8.0.29)  Source distribution.
~
I have backups of the data base so I tried to restore one of them and got the results that one would expect when mysql was not running.
Code:
root@Phaedra:/ # mysql -u root -p movies < /storage1/backup/movies.db.backup.05.20.2022_01.10.00
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
root@Phaedra:/ #

Different lower_case_table_names settings for server ('1') and data dictionary ('0')

NOTE: My previous OS version was 12.3 and only the / disk was new. All my DB files was on /storage3.
Mysql was working fine with the old drive and OS.
I believe that the previous version I was using was mysql57. I deleted version 80 and installed version 57 with the same results. So I went back to version 80.
I did find where a new parameter could be put into my.cnf. "lower_case_table_names=1" id did nothing that I could see.

I have not been able to find the cause or a fix. I have searched the mysql forums without any success. Anything I have found is fixes with mysql running. I can not get it to start. Does anyone have any ideas of what I can do to start mysql?
 
Last edited:
Top