MySQL 5.7.10 Installation Anomalies

[rtwingfield]

With the announcement of MySQL v5.5, a major switch from the venerable MyISAM storage engine to the InnoDB storage engine is now the default. (One must wonder how this may affect your legacy applications. For example, things written incorporating the Perl DBI.)

This is the presentation of make config options:

Apparently it makes no difference whether you check INNOBASE or not . . .you're going to get it by default. I have many questions regarding these additional options and why some seem to install or not. (BTW, if you want to continue with the MyISAM engine . . .then how and where?)

For example, I simply elected to run with the displayed "default", the only one selected being OPENSSL. By the way, I have installed, deinstalled, reinstalled numerous times and I've discovered several anomalies that in my opinion just to not work as advertised.

Expecting OPENSSL to install certificates, keys, etc. as did at one time, I was able to find the following objects installed:

root@bravo:/var/db/mysql/data # ls -lh
total 131196
-rw-r-----  1 mysql  mysql  56B Feb 25 17:27 auto.cnf
-rw-------  1 mysql  mysql  1.6K Feb 25 17:27 ca-key.pem
-rw-r--r--  1 mysql  mysql  1.0K Feb 25 17:27 ca.pem
-rw-r--r--  1 mysql  mysql  1.1K Feb 25 17:27 client-cert.pem
-rw-------  1 mysql  mysql  1.6K Feb 25 17:27 client-key.pem
-rw-r-----  1 mysql  mysql  323B Feb 25 17:27 ib_buffer_pool
-rw-r-----  1 mysql  mysql  64M Feb 26 19:18 ib_logfile0
-rw-r-----  1 mysql  mysql  64M Feb 25 18:07 ib_logfile1
drwxr-xr-x  2 mysql  mysql  512B Feb 26 19:18 ibdata
drwxr-x---  2 mysql  mysql  2.0K Feb 25 19:09 mysql
drwxr-x---  2 mysql  mysql  4.0K Feb 25 17:27 performance_schema
-rw-------  1 mysql  mysql  1.6K Feb 25 17:27 private_key.pem
-rw-r--r--  1 mysql  mysql  451B Feb 25 17:27 public_key.pem
-rw-r--r--  1 mysql  mysql  1.1K Feb 25 17:27 server-cert.pem
-rw-------  1 mysql  mysql  1.6K Feb 25 17:27 server-key.pem
drwxr-x---  2 mysql  mysql  4.5K Feb 25 17:27 sys

Notice the *.pem files. But now as a result of a recent reinstall, these objects were not installed. All that was installed . . .as follows:

root@bravo:/var/db/mysql # ls -lh
total 110712
-rw-r-----  1 mysql  mysql  56B Feb 28 22:32 auto.cnf
-rw-r-----  1 mysql  mysql  408B Feb 28 22:32 ib_buffer_pool
-rw-r-----  1 mysql  mysql  48M Feb 28 22:32 ib_logfile0
-rw-r-----  1 mysql  mysql  48M Feb 28 22:32 ib_logfile1
-rw-r-----  1 mysql  mysql  12M Feb 28 22:32 ibdata1
drwxr-x---  2 mysql  mysql  2.0K Feb 28 22:32 mysql
drwxr-x---  2 mysql  mysql  4.0K Feb 28 22:32 performance_schema
drwxr-x---  2 mysql  mysql  4.5K Feb 28 22:32 sys

. . .so what happened to the *.pem files?

One curious thing that I've noticed is that the make config prompt now displays mysql57-server-5.7.10_1 rather than mysql57-server-5.7.10 . . .this latter version? did install the *.pem files.

Another question: Why is the performance_schema installed -- it wasn't selected for installation via make config?

Most annoying: The only way to get the # /usr/local/libexec/mysqld --initialize --user=mysql system to install anything . . .specifically the /var/db/mysql/mysql databse directory and content schema is to execute the command as indicated, . . .without any additional options, and most important the --defaults-extra-file=[I]~somewhere[/I]/my.cnf

OK . . .fine. What I've had to do to configure the system for the InnoDB engine . . .as we need it configured, was to manually create the following directory archetcture that agrees with the rc.d/mysql-server script, the content of a custom /var/db/mysql/my.cnf file, and of course, arguments in /etc/rc.conf.

There are numerous Notes, Warnings, and Errors in the /var/log/mysql/error.log
For example:

2016-02-29T05:47:54.451959Z 0 [Note] Plugin 'FEDERATED' is disabled.

Which is OK I guess, because we didn't ask for it to be installed.

More disconcerting is typical of the following:

2016-02-29T05:47:54.452887Z 0 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to [URL]http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html[/URL] for how to resolve the issue.
mysqld: Table 'mysql.plugin' doesn't exist
2016-02-29T05:47:54.452938Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2016-02-29T05:47:54.453564Z 0 [Warning] InnoDB: Cannot open table mysql/gtid_executed from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to [URL]http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html[/URL] for how to resolve the issue.
mysqld: Table 'mysql.gtid_executed' doesn't exist
2016-02-29T05:47:54.453588Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-02-29T05:47:54.453638Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

And finally a plethora of similar Errors regarding the performance schema database engine.

2016-02-29T05:47:54.458520Z 0 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
2016-02-29T05:47:54.458552Z 0 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
2016-02-29T05:47:54.458583Z 0 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
2016-02-29T05:47:54.458614Z 0 [ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure
2016-02-29T05:47:54.458641Z 0 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
2016-02-29T05:47:54.458672Z 0 [ERROR] Native table 'performance_schema'.'objects_summary_global_by_type' has the wrong structure
2016-02-29T05:47:54.458703Z 0 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure

Whats with this "wrong structure" gripe? This was installed from a fresh port.

. . .so frustrating and a tremendous waste of lost time . . .days . . .weeks

Finally, in spite of all of this, the server starts and runs, executes queries, etc. I just have to wonder what else is broken . . .waiting to bite while my back is turned.

 

[SirDice]

With the announcement of MySQL v5.7, a major switch from the venerable MyISAM storage engine to the InnoDB storage engine is now the default.

InnoDB has been the default engine since 5.5.

 

[rtwingfield]

InnoDB has been the default engine since 5.5.

You are correct, Sir; below is the original announcement from Oracle

MySQL 5.5 Reference Manual / ... / InnoDB as the Default MySQL Storage Engine
14.1.2 InnoDB as the Default MySQL Storage Engine

MySQL has a well-earned reputation for being easy-to-use and delivering performance and scalability. In previous versions of MySQL, MyISAM was the default storage engine. In our experience, most users never changed the default settings. With MySQL 5.5, InnoDB becomes the default storage engine. Again, we expect most users will not change the default settings. But, because of InnoDB, the default settings deliver the benefits users expect from their RDBMS: ACID Transactions, Referential Integrity, and Crash Recovery.

Admittedly, the following statement in the Ref. v 5.7 does not suggest that the InnoDB has just become the default, but . . . it is easy to surmise from the statement, "14.1.1 InnoDB as the Default MySQL Storage Engine" . . .that this is a new feature if you're only reading from the v5.7 Ref.

Again with apologies, I will correct the date/version in my original post. But I stand by my frustration with the awkwardness of the installation.

 

[SirDice]

Something that has bitten me more than once. Try first starting it without any custom settings in my.cnf (don't create the file, let it use the builtin defaults). Make sure /var/db/mysql/* is empty and simply start the service. The startup scripts will actually create the initial databases automatically if they don't exist.

 

[rtwingfield]

Something that has bitten me more than once. Try first starting it without any custom settings in my.cnf (don't create the file, let it use the builtin defaults). Make sure /var/db/mysql/* is empty and simply start the service. The startup scripts will actually create the initial databases automatically if they don't exist.

That is exactly what I've discovered and what I was describing when I stated:

Most annoying: The only way to get the # /usr/local/libexec/mysqld --initialize --user=mysql system to install anything . . .specifically the /var/db/mysql/mysql databse directory and content schema is to execute the command as indicated, . . .without any additional options, and most important the --defaults-extra-file=~somewhere/my.cnf

This sort of anomaly is an example of things not working as advertised . . .what I recall (some decades ago) IBM referring to as "an undocumented feature."

 

[SirDice]

By starting the service I meant service mysql-server start. It's the FreeBSD's rc(8) scripts that set up the initial databases.

 

[rtwingfield]

I want to come back to this question:

Why is the performance_schema installed -- it wasn't selected for installation via make config?

. . .and if is is an essential component of the InnoDB (installed by default, but not documented), then what to do regrding the ERROR messages, etc?

And finally a plethora of similar Errors regarding the performance schema database engine.

(See above in original post.)

 

[rtwingfield]

By starting the service I meant service mysql-server start. It's the FreeBSD's rc(8) scripts that set up the initial databases.

Yes, thanks, I realize that. Perhaps it would be useful to other readers to have a copy of my mods to the rc.d/mysql-server script. I salted the script with diagnostic "GOT HERE" and other echo messages that helped me to "trace" what was going on with the process: (I hope the code is "as-executed" by now.)

#!/bin/sh
#
# $FreeBSD: head/databases/mysql57-server/files/mysql-server.in 405742 2016-01-10 20:07:48Z pi $
#

# 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_optfile (str):  Server-specific option file.
#  Default to "${mysql_dbdir}/my.cnf".
# 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).
#

. /etc/rc.subr

name="mysql"
rcvar=mysql_enable

load_rc_config $name

: ${mysql_enable="NO"}
: ${mysql_limits="NO"}
: ${mysql_dbdir="/var/db/mysql"}
: ${mysql_optfile="${mysql_dbdir}/my.cnf"}

mysql_user="mysql"
mysql_limits_args="-e -U ${mysql_user}"
: ${hostname:=`/bin/hostname`}
pidfile=${mysql_pidfile:-"${mysql_dbdir}/${hostname}.pid"}
command="/usr/sbin/daemon"
command_args="-c -f /usr/local/bin/mysqld_safe --defaults-extra-file=${mysql_optfile} --user=${mysql_user} --datadir=${mysql_dbdir} --pid-file=${pidfile
} ${mysql_args}"

procname="/usr/local/libexec/mysqld"
start_precmd="${name}_prestart"
start_postcmd="${name}_poststart"
mysql_install_db="/usr/local/bin/mysql_install_db"
mysql_install_db_args="--defaults-extra-file=${mysql_optfile} --basedir=/usr/local --datadir=${mysql_dbdir} --mysqld-file=${procname}"

mysql_create_auth_tables()
{
echo "*** GOT HERE in create!  mysql_dbdir=$mysql_dbdir  mysql_optfile=$mysql_optfile"
#  eval $mysql_install_db $mysql_install_db_args >/dev/null 2>/dev/null
#  *** Don't blackhole these messages!!!
#  eval $mysql_install_db $mysql_install_db_args
#  [ $? -eq 0 ] && chown -R ${mysql_user}:${mysql_user} ${mysql_dbdir}

#  . . .procname="/usr/local/libexec/mysqld"
#  $procname  --defaults-extra-file=${mysql_optfile}  --initialize  --user=mysql
    $procname  --initialize  --user=mysql
}

mysql_prestart()
{
echo "*** GOT HERE in prestart!  mysql_dbdir=$mysql_dbdir  mysql_optfile=$mysql_optfile  "
#  if [ ! -d "${mysql_dbdir}" ]; then
  if [ ! -d "${mysql_dbdir}/mysql/." ]; then
  echo "*** Will attempt to create the auth tables"
  mysql_create_auth_tables || return 1
  else
  echo "*** Located $mysql_dbdir"
  fi

  if checkyesno mysql_limits; then
  eval `/usr/bin/limits ${mysql_limits_args}` 2>/dev/null
  else
  return 0
  fi
}

mysql_poststart()
{
  local timeout=15
  while [ ! -f "${pidfile}" -a ${timeout} -gt 0 ]; do
  timeout=$(( timeout - 1 ))
  sleep 1
  done
  return 0
}

run_rc_command "$1"

Notice that I changed from mysql_install_db to /usr/local/libexec/[B]mysqld[/B]. The MySQL Ref. and diagnostic messages "suggest/recommend" changing from the deprecated mysql_install_db to mysqld . . .so I did.

Also, perhaps it will be useful to someone if I share the following /var/db/mysql/my.cnf script. As you can see, there are a lot of configuration arguments that need to be applied to the real-world installation. Having to run the "install procedure" without knowledge of this recipe . . .is just not good.

Once the default configuration is installed, then you'll need to create directories and move things around as I've outlined in my original post. Note that you have to create the directories-- the InnoDB will not! Once the directory structure is in place, then include your custom my.cnf in the /var/db/mysql directory and restart the server, service start mysql-server.

#  As customized to provide for the InnoDB.
#
#  Run the following command to initialize the database schema:  . . .but will not work
#  /usr/local/libexec/mysqld --defaults-file=/var/db/mysql_bu/my.cnf --initialize --user=mysql


[mysqld]
#password = ''
basedir=/var/db/mysql
datadir=/var/db/mysql/data

lc_messages_dir = /usr/local/share/mysql/english
explicit_defaults_for_timestamp = YES

#
# Data files must be able to hold your data and indexes.
# Make sure that you have enough free disk space.
#
innodb_data_home_dir = /var/db/mysql/data/ibdata
innodb_data_file_path = ibdata1:50M;ibdata2:50M:autoextend
# --------------------------------------------------------

#
# Set buffer pool size to 50-80% of your computer's memory
innodb_buffer_pool_size=256M

#
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=64M
innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1


skip-external-locking
max_connections=200
read_buffer_size=1M
sort_buffer_size=1M
# ? read_rnd_buffer_size = 4M
#
# Set key_buffer to 5 - 50% of your RAM depending on how much
# you use MyISAM tables, but keep key_buffer_size + InnoDB
# buffer pool size < 80% of your RAM
key_buffer_size = 256M

bind-address = 192.168.1.241
port  = 3306
socket  = /tmp/mysql.sock

max_allowed_packet = 1M
table_open_cache = 256
# myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
# thread_concurrency = 8 . . .what? !!!
log-error=/var/log/mysql/error.log
# log_error_verbosity = 3  . . .this doesn't work!  . . .or didn't for me.

[client]
#password  = ''
port  = 3306
socket  = /tmp/mysql.sock

[mysqld_safe]
log-error=/var/log/mysql/error.log

#skip-networking
#skip-grant-tables

log-bin=mysql-bin
binlog_format=mixed
server-id = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout