Hi all,
I have same error (using mariadb-server-10.0 (10.0.16-2~exp1~ubuntu1)).
I try to use the latest debian .postinst file we can find here : http://anonscm.debian.org/cgit/pkg-mysql/mariadb-10.0.git/log/debian/mariadb-server-10.0.postinst
There is into a partial improvement (there is no more sql syntax error), but there is instead a new error with mysqld and skip-grant-tables option :
150410 14:47:42 [Note] InnoDB: Waiting for purge to start
150410 14:47:42 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.22-71.0 started; log sequence number 1616887
150410 14:47:42 [Note] Plugin 'FEEDBACK' is disabled.
ERROR: 1290 The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
150410 14:47:42 [ERROR] Aborting
I try to remove this option in the postinst script, but nothing change, so I understand (or try to) that it is not this script who runs mysqd in the time of the error.
There is also a thread about this update (passwordless root account feature) here : https://lists.launchpad.net/maria-developers/msg08314.html . This (breaking) update seems to come with the debian/ubuntu package mariadb-server-10.0.16 .
In this thread, an answer is proposed for this new error, so I try it and replace ~L.177 :
"INSTALL PLUGIN unix_socket SONAME 'auth_socket';\n"`
by
"INSERT INTO mysql.plugin (name, dl) VALUES ('unix_socket', 'auth_socket');\n"`
With this, it's better, but… there is a new error ! Witch is :
150410 15:46:11 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.22-71.0 started; log sequence number 1616797
150410 15:46:11 [Note] Plugin 'FEEDBACK' is disabled.
150410 15:46:11 [ERROR] Function 'unix_socket' already exists
150410 15:46:11 [Warning] Couldn't load plugin named 'unix_socket' with soname 'auth_socket'.
150410 15:46:11 [Note] Server socket created on IP: '127.0.0.1'.
After that, I try to replace new INSERT with REPLACE, as maybe the error is that.
So, I change the line above by :
"REPLACE INTO mysql.plugin (name, dl) VALUES ('unix_socket', 'auth_socket');\n"`
So, with, that, I have no more error in var/log/mysql. Happy !
But… The package won't install correctly.
I have a new error :
Paramétrage de mariadb-server-10.0 (10.0.16-2~exp1~ubuntu1) ...
Job for mysql.service failed. See "systemctl status mysql.service" and "journalctl -xe" for details.
invoke-rc.d: initscript mysql, action "start" failed.
The command "systemctl status mysql.service" tells me that mysql_safe can't log on both syslog and error.log.
So I comment the "syslog" line in /etc/mysql/mariadb.conf.d/mysqld_safe.cnf
And… with all that, it is finally working (but I'm not really sure).
I need to replace my old databases thought.
So to work : stop mysql, remove the deamon, remove package mariadb-server,
remove /var/lib/mysql, remove .postinst, comment syslog line in mysqld_safe.cnf,
reinstall mariadb-server (with error), change the .postinst by new one
(see below), re-run configure dpkg…
Note : To achieve my tests, I used that commands.
I had totally also reinstall mysql-common but I think it was not necessary (mark with ## below).
# one time only : backup my old tables and conf
mv /var/lib/mysql /var/lib/mysql.bak
## if mysql-common resintall
## mv /etc/mysql /etc/mysql.bak
# and remove all mariadb for tests
rm /var/lib/dpkg/info/mariadb-server-10.0.postinst
service mysql stop
killall -9 mysqld
apt-get remove --purge mariadb-server mariadb-server-core-10.0
## if mysql-common resintall
## apt-get remove --purge mysql-common
rm -rf /var/lib/mysql
## if mysql-common resintall
## rm -rf /etc/mysql
apt-get install mariadb-server
# the default .postinst is creating and mariadb installation crash with error.
# now, I change it by new one with an editor (i put my new in mariadb-server-10.0.postinst.new)
rm /var/lib/dpkg/info/mariadb-server-10.0.postinst
cp /var/lib/dpkg/info/mariadb-server-10.0.postinst.new /var/lib/dpkg/info/mariadb-server-10.0.postinst
# and run config
dpkg --configure -a
And the content of my final /var/lib/dpkg/info/mariadb-server-10.0.postinst.new
(Note there is only 1 few change with http://anonscm.debian.org/cgit/pkg-mysql/mariadb-10.0.git/tree/debian/mariadb-server-10.0.postinst?id=d95a590459ba2f23591630498642450d10011434 ,
witch is the line ~177 : REPLACE INTO ...
#!/bin/bash -e
. /usr/share/debconf/confmodule
if [ -n "$DEBIAN_SCRIPT_DEBUG" ]; then set -v -x; DEBIAN_SCRIPT_TRACE=1; fi
${DEBIAN_SCRIPT_TRACE:+ echo "#42#DEBUG# RUNNING $0 $*" 1>&2 }
export PATH=$PATH:/sbin:/usr/sbin:/bin:/usr/bin
# This command can be used as pipe to syslog. With "-s" it also logs to stderr.
ERR_LOGGER="logger -p daemon.err -t mysqld_safe -i"
# This will make an error in a logged command immediately apparent by aborting
# the install, rather than failing silently and leaving a broken install.
set -o pipefail
invoke() {
if [ -x /usr/sbin/invoke-rc.d ]; then
invoke-rc.d mysql $1
else
/etc/init.d/mysql $1
fi
}
MYSQL_BOOTSTRAP="/usr/sbin/mysqld --bootstrap --user=mysql --disable-log-bin --skip-grant-tables --default-storage-engine=myisam --plugin-load-add=auth_socket"
# This function resets the root@localhost user password and enable the
# usage of the unix_socket plugin for it.
set_mysql_rootpw() {
tfile="$(mktemp)"
if [ ! -f "$tfile" ]; then
return 1
fi
# - The mysqld statement is like that in mysql_install_db because the
# server is not already running. This has some implications:
# - GRANT is not possible with --skip-grant-tables and "INSERT
# (user,host..) VALUES" is not --ansi compliant but will have to do.
# The reset_root statement is used to verify that the unix_socket plugin
# is active before resetting the root@localhost password ; if the plugin
# is not active, it will fail with "ERROR 1065 (42000): Query was empty"
# purge off the root@127.0.0.1 / root@::1 entries created by
# mysql_install_db and double check they have no authention
# This avoids us having to call "test" or "[" on $rootpw
cat << EOF > $tfile
SET sql_log_bin=0;
SET @reset_root=IF( (SELECT 1 FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='unix_socket' AND PLUGIN_STATUS='ACTIVE' AND PLUGIN_TYPE='AUTHENTICATION' AND PLUGIN_LIBRARY LIKE CONCAT('auth_socket','%') )=1, "UPDATE mysql.user SET Password='', Plugin='unix_socket' WHERE User='root' AND Host='localhost'", '');
PREPARE reset_root FROM @reset_root;
EXECUTE reset_root;
DELETE FROM mysql.user WHERE User='root' AND Host!='localhost' AND Password='' AND Plugin='';
EOF
$MYSQL_BOOTSTRAP <$tfile
retval=$?
rm -f "$tfile"
return $retval
}
# This is necessary because mysql_install_db removes the pid file in /var/run
# and because changed configuration options should take effect immediately.
# In case the server wasn't running at all it should be ok if the stop
# script fails. I can't tell at this point because of the cleaned /var/run.
set +e; invoke stop; set -e
case "$1" in
configure)
mysql_statedir=/usr/share/mysql
mysql_datadir=/var/lib/mysql
mysql_logdir=/var/log/mysql
mysql_rundir=/var/run/mysqld
mysql_cfgdir=/etc/mysql
mysql_upgradedir=/var/lib/mysql-upgrade
# Ensure the existence and right permissions for the database and
# log files.
if [ ! -d "$mysql_statedir" -a ! -L "$mysql_statedir" ]; then mkdir "$mysql_statedir"; fi
if [ ! -d "$mysql_datadir" -a ! -L "$mysql_datadir" ]; then mkdir "$mysql_datadir" ; fi
if [ ! -d "$mysql_logdir" -a ! -L "$mysql_logdir" ]; then mkdir "$mysql_logdir" ; fi
# When creating an ext3 jounal on an already mounted filesystem like e.g.
# /var/lib/mysql, you get a .journal file that is not modifyable by chown.
# The mysql_statedir must not be writable by the mysql user under any
# circumstances as it contains scripts that are executed by root.
set +e
chown -R 0:0 $mysql_statedir
chown -R mysql $mysql_datadir
chown -R mysql:adm $mysql_logdir
chmod 2750 $mysql_logdir
set -e
# This is important to avoid dataloss when there is a removed
# mysql-server version from Woody lying around which used the same
# data directory and then somewhen gets purged by the admin.
db_set mariadb-server/postrm_remove_database false || true
# Clean up old flags before setting new one
rm -f $mysql_datadir/debian-*.flag
# Flag data dir to avoid downgrades
touch $mysql_datadir/debian-10.0.flag
# initiate databases. Output is not allowed by debconf :-(
# This will fail if we are upgrading an existing database; in this case
# mysql_upgrade, called from the /etc/init.d/mysql start script, will
# handle things.
# Debian: beware of the bashisms...
# Debian: can safely run on upgrades with existing databases
set +e
bash /usr/bin/mysql_install_db --rpm --cross-bootstrap --user=mysql --disable-log-bin 2>&1 | $ERR_LOGGER
set -e
# recreate the credentials file if not present or with debian-sys-maint
# still there
dc=$mysql_cfgdir/debian.cnf;
set +e
grep -sq "^\s*user\s*=\s*debian-sys-maint\s*$" "$dc"
ret=$?
set -e
if [ $ret -ne 1 ]; then
if [ ! -d "$mysql_cfgdir" ]; then install -o 0 -g 0 -m 0755 -d $mysql_cfgdir; fi
if [ -e "$dc" ]; then
oldconf=`mktemp --tmpdir=$mysql_cfgdir -t debian_old_config.XXXXXX`
cp $dc $oldconf
else
oldconf=''
fi
umask 066
cat /dev/null > $dc
umask 022
echo "# Automatically generated for Debian scripts. DO NOT TOUCH!" >>$dc
echo "[client]" >>$dc
echo "host = localhost" >>$dc
echo "user = root" >>$dc
echo "password = " >>$dc
echo "socket = $mysql_rundir/mysqld.sock" >>$dc
echo "[mysql_upgrade]" >>$dc
echo "host = localhost" >>$dc
echo "user = root" >>$dc
echo "password = " >>$dc
echo "socket = $mysql_rundir/mysqld.sock" >>$dc
echo "basedir = /usr" >>$dc
else
oldconf=''
fi
# If this dir chmod go+w then the admin did it. But this file should not.
chown 0:0 $dc
chmod 0600 $dc
# Update privilege tables
password_column_fix_query=`/bin/echo -e \
"USE mysql;\n" \
"SET sql_log_bin=0;\n" \
"ALTER TABLE user CHANGE Password Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL;"`
# Upgrade password column format before the root password gets set.
# NOTE: Lines like this apparently really need to be formatted this way
# for mysqld to process the correclty (;-delimiter, newlines etc)
echo "$password_column_fix_query" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER
# Replace old maintenance user with auth_socket usage if migrating
replace_query=`/bin/echo -e \
"SET sql_mode='', sql_log_bin=0;\n" \
"DELETE FROM mysql.user WHERE user='debian-sys-maint' AND host='localhost';"`
# WARNING: This line might yield "The MariaDB server is running with
# the --skip-grant-tables option so it cannot execute this statement"
# Some plugins should installed per default. The query sequence is supposed
# to be aborted if the CREATE TABLE fails due to an already existent table in which case the
# admin might already have chosen to remove one or more plugins. Newlines are necessary.
install_plugins=`/bin/echo -e \
"USE mysql;\n" \
"SET sql_log_bin=0;\n" \
"CREATE TABLE IF NOT EXISTS plugin (name char(64) COLLATE utf8_bin NOT NULL DEFAULT '', " \
" dl char(128) COLLATE utf8_bin NOT NULL DEFAULT '', " \
" PRIMARY KEY (name)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='MySQL plugins';\n" \
"REPLACE INTO mysql.plugin (name, dl) VALUES ('unix_socket', 'auth_socket');\n"`
# "INSTALL PLUGIN unix_socket SONAME 'auth_socket';\n"`
# Install plugins and ignore if already there
set +e
echo "$install_plugins" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER
set -e
if ! set_mysql_rootpw; then
password_error="yes"
# restore old config file if exists
[ -e $oldconf ] && mv $oldconf $dc
else
[ -e $oldconf ] && rm -f $oldconf
# purge debian-sys-maint user
set +e
echo "$replace_query" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER
set -e
fi
;;
abort-upgrade|abort-remove|abort-configure)
;;
*)
echo "postinst called with unknown argument '$1'" 1>&2
exit 1
;;
esac
# here we check to see if we can connect as root without a password
# this should catch upgrades from previous versions where the root
# password wasn't set. if there is a password, or if the connection
# fails for any other reason, nothing happens.
if [ "$1" = "configure" ]; then
if [ "$password_error" = "yes" ]; then
db_input high mariadb-server/error_setting_password || true
db_go
fi
fi
db_stop # in case invoke failes
#DEBHELPER#
exit 0