# MySQL - login issue



## michael7408 (Nov 12, 2017)

freebsd-version:  11.1-RELEASE
mysql version:  5.6.38

I have a script the requires mysql, cron_create_tables.pl:

```
#!/usr/local/bin/perl -w --

use DBI;

use strict;

my $dbh = DBI->connect('DBI:mysql:mymud', 'mymud', '') or die "unable to connect to MyMUD database";

...
```

`$ perl cron_create_tables.pl`

```
DBI connect('starmud','starmud',...) failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) at cron_create_tables.pl line 7.
unable to connect to StarMUD database at cron_create_tables.pl line 7.
```

I am unable to start mysql.
I spent an entire week at home trying to get this to work and still no success.
Here are some of the things I've done/tried:

I added mysql_enable="YES" to /etc/rc.conf

`$ mysql`

```
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
```
`$ service mysql-server start`  OR
`$ /usr/local/etc/rc.d/mysql-server start`

```
/usr/local/etc/rc.d/mysql-server: WARNING: failed precmd routine for mysql
```
`$ service mysql-server status`

```
mysql is not running.
```
`$ service mysql-server stop`

```
mysql not running? (check /var/db/mysql/freebsd11.1.pid).
```
`$ sudo more /root/.mysql_secret`

```
# Password set for user 'root@localhost' at 2017-11-11 13:53:12
V=o%;*&bU)jo
```


----------



## ShelLuser (Nov 12, 2017)

First check /usr/local/etc/rc.d/mysql-server, this will tell you exactly what you need to add to /etc/rc.conf. But you don't have to add anything in order to test: `# service mysql-server onestart` can be used to... well, start it once 

Second: did you already set up a database?  If you look at the file I mentioned then you'll notice that the default database location is /var/db/mysql, is there anything there? I assume not, so look into mysql_install_db(1) to actually set up your database.

Note that you can easily find this stuff yourself: `pkg info -lx mysql56-serv | less` will quickly point these files out. Or whatever version you run.


----------



## michael7408 (Nov 12, 2017)

ShelLuser said:


> First check /usr/local/etc/rc.d/mysql-server, this will tell you exactly what you need to add to /etc/rc.conf. But you don't have to add anything in order to test: `# service mysql-server onestart` can be used to... well, start it once
> 
> Second: did you already set up a database?  If you look at the file I mentioned then you'll notice that the default database location is /var/db/mysql, is there anything there? I assume not, so look into mysql_install_db to actually set up your database.
> 
> Note that you can easily find this stuff yourself: `pkg info -lx mysql56-serv | less` will quickly point these files out. Or whatever version you run.



(FYi):
The whole point of getting mysql to run properly is because of this file cron_create_tables.pl. I can't get it working without mysql. Here are its contents:

```
#!/usr/local/bin/perl -w --

use DBI;

use strict;

my $dbh = DBI->connect('DBI:mysql:mymud', 'mymud', '') or die "unable to connect to MyMUD database";

my @now = localtime();
my $y   = 1900 + $now[5];
my $m   = 1    + $now[4];

if (++$m > 12) {
  $m = 1;
  $y = $y + 1;
}

my $sth_create_log = $dbh->prepare(sprintf(q|
  CREATE TABLE log_%04d%02d (
    t   DATETIME NOT NULL,
    cat VARCHAR(10) NOT NULL,
    log VARCHAR(20) NOT NULL,
    uid VARCHAR(20) NOT NULL,
    msg TEXT NOT NULL DEFAULT '',
    id  INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    KEY _t_ (t),
    KEY _cat_log_ (cat, log),
    KEY _uid_ (uid)
  )
|, $y, $m)) or die($dbh->errstr());

my $sth_create_chat = $dbh->prepare(sprintf(q|
  CREATE TABLE chat_%04d%02d (
    t   DATETIME NOT NULL,
    log VARCHAR(20) NOT NULL,
    uid VARCHAR(20) NOT NULL,
    msg TEXT NOT NULL DEFAULT '',
    id  INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    KEY _t_ (t),
    KEY _log_ (log),
    KEY _uid_ (uid)
  )
|, $y, $m)) or die($dbh->errstr());

$sth_create_log->execute();
$sth_create_chat->execute();
```

(FIRST):
Here are all the things I looked into that you suggested:

Other than mysql_enable="YES", I'm not sure what else needs to be enabled in /etc/rc.conf. The top part of the content in /usr/local/etc/rc.d/mysql-server is shown here:

```
#!/bin/sh
#
# $FreeBSD: head/databases/mysql56-server/files/mysql-server.in 434623 2017-02-22 17:25:22Z mmokhi $
#

# PROVIDE: mysql
# REQUIRE: LOGIN
# KEYWORD: shutdown

#
# Add the following line to /etc/rc.conf to enable mysql:
# mysql_enable (bool):   Set to "NO" by default.
#           Set it to "YES" to enable MySQL.
# mysql_limits (bool):   Set to "NO" by default.
#           Set it to yes to run `limits -e -U mysql`
#           just before mysql starts.
# mysql_dbdir (str):   Default to "/var/db/mysql"
#           Base database directory.
# mysql_confdir (str):   Default to "/usr/local/etc/mysql"
#           Base configuration directory.
# mysql_optfile (str):   Server-specific option file.
#           Set it in the rc.conf or default behaviour of
#           `mysqld_safe` itself, will be picking
#           ${mysql_confdir}/my.cnf if it exists.
# mysql_pidfile (str):   Custum PID file path and name.
#           Default to "${mysql_dbdir}/${hostname}.pid".
# mysql_args (str):   Custom additional arguments to be passed
#           to mysqld_safe (default empty).
#
```

(SECOND):
`ls -FG /var/db/mysql/`
auto.cnf
ca-key.pem
ca.pem
client-cert.pem
client-key.pem
freebsd11-slow.log
freebsd11.1.err
ib_buffer_pool
ib_logfile0
ib_logfile1
ibdata1
mysql/
mysql-bin.000001
mysql-bin.000002
mysql-bin.index
performance_schema/
private_key.pem
public_key.pem
server-cert.pem
server-key.pem
sys/

(THIRD):
I did `man mysql_install_db` but I'm uncertain how I SHOULD setup a database. Assistance would be appreciated. I'm willing to IM for a quicker resolve.

(FOURTH):
`$  pkg info -lx mysql56-serv | less`

```
mysql56-server-5.6.38:
        /usr/local/bin/innochecksum
        /usr/local/bin/my_print_defaults
        /usr/local/bin/myisam_ftdump
        /usr/local/bin/myisamchk
        /usr/local/bin/myisamlog
        /usr/local/bin/myisampack
        /usr/local/bin/mysql_convert_table_format
        /usr/local/bin/mysql_fix_extensions
        /usr/local/bin/mysql_install_db
        /usr/local/bin/mysql_plugin
        /usr/local/bin/mysql_secure_installation
        /usr/local/bin/mysql_setpermission
        /usr/local/bin/mysql_tzinfo_to_sql
        /usr/local/bin/mysql_upgrade
        /usr/local/bin/mysql_zap
        /usr/local/bin/mysqlbug
        /usr/local/bin/mysqld_multi
        /usr/local/bin/mysqld_safe
        /usr/local/bin/mysqldumpslow
        /usr/local/bin/mysqlhotcopy
        /usr/local/bin/mysqltest
        /usr/local/bin/perror
        /usr/local/bin/replace
        /usr/local/bin/resolve_stack_dump
        /usr/local/bin/resolveip
        /usr/local/etc/mysql/my.cnf.sample
        /usr/local/etc/rc.d/mysql-server
        /usr/local/lib/mysql/libmysqld.a
        /usr/local/lib/mysql/plugin/adt_null.so
        /usr/local/lib/mysql/plugin/auth.so
        /usr/local/lib/mysql/plugin/auth_test_plugin.so
        /usr/local/lib/mysql/plugin/connection_control.so
        /usr/local/lib/mysql/plugin/daemon_example.ini
        /usr/local/lib/mysql/plugin/libdaemon_example.so
        /usr/local/lib/mysql/plugin/mypluglib.so
        /usr/local/lib/mysql/plugin/mysql_no_login.so
        /usr/local/lib/mysql/plugin/qa_auth_client.so
        /usr/local/lib/mysql/plugin/qa_auth_interface.so
        /usr/local/lib/mysql/plugin/qa_auth_server.so
        /usr/local/lib/mysql/plugin/semisync_master.so
        /usr/local/lib/mysql/plugin/semisync_slave.so
        /usr/local/lib/mysql/plugin/test_udf_services.so
        /usr/local/lib/mysql/plugin/validate_password.so
        /usr/local/libdata/ldconfig/mysql56-server
        /usr/local/libexec/mysqld
        /usr/local/man/man1/my_print_defaults.1.gz
        /usr/local/man/man1/myisam_ftdump.1.gz
        /usr/local/man/man1/myisamchk.1.gz
        /usr/local/man/man1/myisamlog.1.gz
        /usr/local/man/man1/myisampack.1.gz
        /usr/local/man/man1/mysql.server.1.gz
        /usr/local/man/man1/mysql_convert_table_format.1.gz
        /usr/local/man/man1/mysql_fix_extensions.1.gz
        /usr/local/man/man1/mysql_install_db.1.gz
        /usr/local/man/man1/mysql_plugin.1.gz
        /usr/local/man/man1/mysql_secure_installation.1.gz
        /usr/local/man/man1/mysql_setpermission.1.gz
        /usr/local/man/man1/mysql_tzinfo_to_sql.1.gz
        /usr/local/man/man1/mysql_upgrade.1.gz
        /usr/local/man/man1/mysql_zap.1.gz
        /usr/local/man/man1/mysqlbug.1.gz
        /usr/local/man/man1/mysqld_multi.1.gz
        /usr/local/man/man1/mysqld_safe.1.gz
        /usr/local/man/man1/mysqldumpslow.1.gz
        /usr/local/man/man1/mysqlhotcopy.1.gz
        /usr/local/man/man1/mysqlman.1.gz
        /usr/local/man/man1/mysqltest.1.gz
        /usr/local/man/man1/perror.1.gz
        /usr/local/man/man1/replace.1.gz
        /usr/local/man/man1/resolve_stack_dump.1.gz
        /usr/local/man/man1/resolveip.1.gz
        /usr/local/man/man8/mysqld.8.gz
        /usr/local/share/licenses/mysql56-server-5.6.38/GPLv2
        /usr/local/share/licenses/mysql56-server-5.6.38/LICENSE
        /usr/local/share/licenses/mysql56-server-5.6.38/catalog.mk
        /usr/local/share/mysql/binary-configure
        /usr/local/share/mysql/bulgarian/errmsg.sys
        /usr/local/share/mysql/charsets/Index.xml
        /usr/local/share/mysql/charsets/README
        /usr/local/share/mysql/charsets/armscii8.xml
        /usr/local/share/mysql/charsets/ascii.xml
        /usr/local/share/mysql/charsets/cp1250.xml
        /usr/local/share/mysql/charsets/cp1251.xml
        /usr/local/share/mysql/charsets/cp1256.xml
        /usr/local/share/mysql/charsets/cp1257.xml
        /usr/local/share/mysql/charsets/cp850.xml
        /usr/local/share/mysql/charsets/cp852.xml
        /usr/local/share/mysql/charsets/cp866.xml
        /usr/local/share/mysql/charsets/dec8.xml
        /usr/local/share/mysql/charsets/geostd8.xml
        /usr/local/share/mysql/charsets/greek.xml
        /usr/local/share/mysql/charsets/hebrew.xml
        /usr/local/share/mysql/charsets/hp8.xml
        /usr/local/share/mysql/charsets/keybcs2.xml
        /usr/local/share/mysql/charsets/koi8r.xml
        /usr/local/share/mysql/charsets/koi8u.xml
        /usr/local/share/mysql/charsets/latin1.xml
        /usr/local/share/mysql/charsets/latin2.xml
        /usr/local/share/mysql/charsets/latin5.xml
        /usr/local/share/mysql/charsets/latin7.xml
        /usr/local/share/mysql/charsets/macce.xml
        /usr/local/share/mysql/charsets/macroman.xml
        /usr/local/share/mysql/charsets/swe7.xml
        /usr/local/share/mysql/czech/errmsg.sys
        /usr/local/share/mysql/danish/errmsg.sys
        /usr/local/share/mysql/dictionary.txt
        /usr/local/share/mysql/dutch/errmsg.sys
        /usr/local/share/mysql/english/errmsg.sys
        /usr/local/share/mysql/errmsg-utf8.txt
        /usr/local/share/mysql/estonian/errmsg.sys
        /usr/local/share/mysql/fill_help_tables.sql
        /usr/local/share/mysql/french/errmsg.sys
        /usr/local/share/mysql/german/errmsg.sys
        /usr/local/share/mysql/greek/errmsg.sys
        /usr/local/share/mysql/hungarian/errmsg.sys
        /usr/local/share/mysql/innodb_memcached_config.sql
        /usr/local/share/mysql/italian/errmsg.sys
        /usr/local/share/mysql/japanese/errmsg.sys
        /usr/local/share/mysql/korean/errmsg.sys
        /usr/local/share/mysql/magic
        /usr/local/share/mysql/my-default.cnf
        /usr/local/share/mysql/mysql-log-rotate
        /usr/local/share/mysql/mysql.server
        /usr/local/share/mysql/mysql_security_commands.sql
        /usr/local/share/mysql/mysql_system_tables.sql
        /usr/local/share/mysql/mysql_system_tables_data.sql
        /usr/local/share/mysql/mysql_test_data_timezone.sql
        /usr/local/share/mysql/mysqld_multi.server
        /usr/local/share/mysql/norwegian-ny/errmsg.sys
        /usr/local/share/mysql/norwegian/errmsg.sys
        /usr/local/share/mysql/polish/errmsg.sys
        /usr/local/share/mysql/portuguese/errmsg.sys
        /usr/local/share/mysql/romanian/errmsg.sys
        /usr/local/share/mysql/russian/errmsg.sys
        /usr/local/share/mysql/serbian/errmsg.sys
        /usr/local/share/mysql/slovak/errmsg.sys
        /usr/local/share/mysql/spanish/errmsg.sys
        /usr/local/share/mysql/swedish/errmsg.sys
        /usr/local/share/mysql/ukrainian/errmsg.sys
```


----------



## ShelLuser (Nov 13, 2017)

So from the looks of things you do have a MySQL database set up, but it doesn't seem to list any extra databases such as that mymud you mentioned.

I'd say check /usr/local/etc/my.cnf, and if that isn't there create it. Optionally using /usr/local/etc/mysql/my.cnf.sample as a template / example.  As an absolute minimum you could use something like this:


```
[mysqld]
port = 3306
bind-address = 127.0.0.1
socket = /tmp/mysql.sock

[client]
port = 3306
socket = /tmp/mysql.sock
```
This should allow you to actually start the critter and then connect to it.


----------



## michael7408 (Nov 14, 2017)

It started but now I'm encountering another problem. I'm trying to set some variables in /usr/local/etc/my.cnf. Other than sql_mode=NO_AUTO_VALUE_ON_ZERO, adding the following will prevent the mysql-server from starting:

```
NAMES utf8
TIME_ZONE = +00:00
UNIQUE_CHECKS = 0
FOREIGN_KEY_CHECKS = 0
SQL_NOTES = 0
```
I even tried these with lower case but no success. What am I doing wrong.


----------



## ShelLuser (Nov 14, 2017)

Check the logfile?  That should show you exactly why the server doesn't start. Now, it's been ages since I messed with this but considering that 'names' is the only one which doesn't have an = in between, are you sure that's correct?

But as said: check the logs, that's where you'll find more info about things like these.


----------



## michael7408 (Nov 14, 2017)

Leaving out the 'names' one didn't make a difference.
I'm learning as I go here, where's the logfile located?


----------



## Phishfry (Nov 14, 2017)

Here is mine:
/var/db/mysql/my.cnf

```
[server]
   skip-networking
   skip-name-resolv
   innodb_flush_method = O_DIRECT
   skip-innodb doublewrite
   innodb_file_per_table
```


----------



## Phishfry (Nov 14, 2017)

From the web:
"Set of sample configuration files can be found under /usr/local/share/mysql.
These configuration files include my-small.cnf, my-medium.cnf, my-large.cnf, my-innodb-heavy-4G, and my-huge.cnf."


----------

