[Maria-developers] passwordless mariadb root login with auht_socket in Debian
Hello Daniel! It seems there are some issues with the passwordless login patch you sent me some weeks ago (https://github.com/ottok/mariadb-10.0/pull/3) Can you help me debug the failing postinst script? I added 'set -x' to the mariadb-server-10.0.postinst script and when it is run during the configure phase it will exit with code 1 and make dpkg abort the installation. Last lines of execution: SET sql_log_bin=0; 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'\'';' + logger -p daemon.err -t mysqld_safe -i + /usr/sbin/mysqld --bootstrap --user=mysql --skip-grant-tables --default-storage-engine=myisam --plugin-load-add=auth_socket + set -e + echo 'SET sql_log_bin=0; USE mysql; DELIMITER //; CREATE PROCEDURE debian_plugin_install(IN plugin_name CHAR(50), IN soname CHAR(50)) BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND EXECUTE inst_plug; set @plugin_name=plugin_name; set @soname=soname ;set @install_plugin=CONCAT("INSTALL PLUGIN ",@plugin_name," SONAME '\''", @soname, "'\''");PREPARE inst_plug FROM @install_plugin ; select PLUGIN_NAME INTO @a from information_schema.plugins where PLUGIN_NAME=@plugin_name AND PLUGIN_STATUS='\''ACTIVE'\'' AND PLUGIN_TYPE='\''AUTHENTICATION'\'' AND PLUGIN_LIBRARY LIKE concat(@soname,'\''%'\'' ); DEALLOCATE PREPARE inst_plug; END// CALL debian_plugin_install('\''unix_socket'\'', '\''auth_socket'\'') // DROP PROCEDURE debian_plugin_install//' + logger -p daemon.err -t mysqld_safe -i + /usr/sbin/mysqld --bootstrap --user=mysql --skip-grant-tables --default-storage-engine=myisam --plugin-load-add=auth_socket timo@debunstable:~$ echo $? 1 timo@debunstable:~$ echo $? 0 This implies that the exit code from the line '/usr/sbin/mysqld --bootstrap --user=mysql --skip-grant-tables --default-storage-engine=myisam --plugin-load-add=auth_socket' is 1. Any ideas why? If you want to test it yourself, add to you apt sources the test repo deb http://labs.seravo.fi/~otto/mariadb-repo/ mariabd-10.0-sid-amd64/
Daniel, anybody..? 2015-02-20 22:52 GMT+02:00 Otto Kekäläinen <otto@seravo.fi>:
Hello Daniel!
It seems there are some issues with the passwordless login patch you sent me some weeks ago (https://github.com/ottok/mariadb-10.0/pull/3)
Can you help me debug the failing postinst script?
I added 'set -x' to the mariadb-server-10.0.postinst script and when it is run during the configure phase it will exit with code 1 and make dpkg abort the installation.
Last lines of execution: SET sql_log_bin=0; 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'\'';' + logger -p daemon.err -t mysqld_safe -i + /usr/sbin/mysqld --bootstrap --user=mysql --skip-grant-tables --default-storage-engine=myisam --plugin-load-add=auth_socket + set -e + echo 'SET sql_log_bin=0; USE mysql; DELIMITER //; CREATE PROCEDURE debian_plugin_install(IN plugin_name CHAR(50), IN soname CHAR(50)) BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND EXECUTE inst_plug; set @plugin_name=plugin_name; set @soname=soname ;set @install_plugin=CONCAT("INSTALL PLUGIN ",@plugin_name," SONAME '\''", @soname, "'\''");PREPARE inst_plug FROM @install_plugin ; select PLUGIN_NAME INTO @a from information_schema.plugins where PLUGIN_NAME=@plugin_name AND PLUGIN_STATUS='\''ACTIVE'\'' AND PLUGIN_TYPE='\''AUTHENTICATION'\'' AND PLUGIN_LIBRARY LIKE concat(@soname,'\''%'\'' ); DEALLOCATE PREPARE inst_plug; END// CALL debian_plugin_install('\''unix_socket'\'', '\''auth_socket'\'') // DROP PROCEDURE debian_plugin_install//' + logger -p daemon.err -t mysqld_safe -i + /usr/sbin/mysqld --bootstrap --user=mysql --skip-grant-tables --default-storage-engine=myisam --plugin-load-add=auth_socket timo@debunstable:~$ echo $? 1 timo@debunstable:~$ echo $? 0
This implies that the exit code from the line '/usr/sbin/mysqld --bootstrap --user=mysql --skip-grant-tables --default-storage-engine=myisam --plugin-load-add=auth_socket' is 1.
Any ideas why?
If you want to test it yourself, add to you apt sources the test repo
deb http://labs.seravo.fi/~otto/mariadb-repo/ mariabd-10.0-sid-amd64/
-- Check out our blog at http://seravo.fi/blog and follow @ottokekalainen
Manually executing the command result in this error : ERROR: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USE mysql; DELIMITER //; CREATE PROCEDURE debian_plugin_install(IN plugin_name C' at line 1 150223 22:44:06 [ERROR] Aborting By adding \n between each of these statements we can see that the statement causing the syntax error is "DELIMITER //;", it seems that DELIMITER is only used by mysql client and the that mysqld doesnt recognize it as an internal command. Le 23/02/2015 21:52, Otto Kekäläinen a écrit :
Daniel, anybody..?
2015-02-20 22:52 GMT+02:00 Otto Kekäläinen <otto@seravo.fi>:
Hello Daniel!
It seems there are some issues with the passwordless login patch you sent me some weeks ago (https://github.com/ottok/mariadb-10.0/pull/3)
Can you help me debug the failing postinst script?
I added 'set -x' to the mariadb-server-10.0.postinst script and when it is run during the configure phase it will exit with code 1 and make dpkg abort the installation.
Last lines of execution: SET sql_log_bin=0; 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'\'';' + logger -p daemon.err -t mysqld_safe -i + /usr/sbin/mysqld --bootstrap --user=mysql --skip-grant-tables --default-storage-engine=myisam --plugin-load-add=auth_socket + set -e + echo 'SET sql_log_bin=0; USE mysql; DELIMITER //; CREATE PROCEDURE debian_plugin_install(IN plugin_name CHAR(50), IN soname CHAR(50)) BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND EXECUTE inst_plug; set @plugin_name=plugin_name; set @soname=soname ;set @install_plugin=CONCAT("INSTALL PLUGIN ",@plugin_name," SONAME '\''", @soname, "'\''");PREPARE inst_plug FROM @install_plugin ; select PLUGIN_NAME INTO @a from information_schema.plugins where PLUGIN_NAME=@plugin_name AND PLUGIN_STATUS='\''ACTIVE'\'' AND PLUGIN_TYPE='\''AUTHENTICATION'\'' AND PLUGIN_LIBRARY LIKE concat(@soname,'\''%'\'' ); DEALLOCATE PREPARE inst_plug; END// CALL debian_plugin_install('\''unix_socket'\'', '\''auth_socket'\'') // DROP PROCEDURE debian_plugin_install//' + logger -p daemon.err -t mysqld_safe -i + /usr/sbin/mysqld --bootstrap --user=mysql --skip-grant-tables --default-storage-engine=myisam --plugin-load-add=auth_socket timo@debunstable:~$ echo $? 1 timo@debunstable:~$ echo $? 0
This implies that the exit code from the line '/usr/sbin/mysqld --bootstrap --user=mysql --skip-grant-tables --default-storage-engine=myisam --plugin-load-add=auth_socket' is 1.
Any ideas why?
If you want to test it yourself, add to you apt sources the test repo
deb http://labs.seravo.fi/~otto/mariadb-repo/ mariabd-10.0-sid-amd64/
----- Original Message -----
Manually executing the command result in this error : ERROR: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USE mysql; DELIMITER //; CREATE PROCEDURE debian_plugin_install(IN plugin_name C' at line 1 150223 22:44:06 [ERROR] Aborting
By adding \n between each of these statements we can see that the statement causing the syntax error is "DELIMITER //;", it seems that DELIMITER is only used by mysql client and the that mysqld doesnt recognize it as an internal command.
thank you -- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
DELIMITERs are (should be) definitely handled in the client. The server does not know about it. It is also not listed here http://dev.mysql.com/doc/refman/5.6/en/sql-syntax.html for same reason. -- Peter On Tue, Feb 24, 2015 at 3:05 AM, Daniel Black <daniel.black@openquery.com> wrote:
----- Original Message -----
Manually executing the command result in this error : ERROR: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USE mysql; DELIMITER //; CREATE PROCEDURE debian_plugin_install(IN plugin_name C' at line 1 150223 22:44:06 [ERROR] Aborting
By adding \n between each of these statements we can see that the statement causing the syntax error is "DELIMITER //;", it seems that DELIMITER is only used by mysql client and the that mysqld doesnt recognize it as an internal command.
thank you
-- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Thanks for your help! How should this then be re-written correctly? SET sql_log_bin=0; USE mysql; DELIMITER //; CREATE PROCEDURE debian_plugin_install(IN plugin_name CHAR(50), IN soname CHAR(50)) BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND EXECUTE inst_plug; set @plugin_name=plugin_name; set @soname=soname ;set @install_plugin=CONCAT(\"INSTALL PLUGIN \",@plugin_name,\" SONAME '\", @soname, \"'\");PREPARE inst_plug FROM @install_plugin ; select PLUGIN_NAME INTO @a from information_schema.plugins where PLUGIN_NAME=@plugin_name AND PLUGIN_STATUS='ACTIVE' AND PLUGIN_TYPE='AUTHENTICATION' AND PLUGIN_LIBRARY LIKE concat(@soname,'%' ); DEALLOCATE PREPARE inst_plug; END// CALL debian_plugin_install('unix_socket', 'auth_socket') // DROP PROCEDURE debian_plugin_install// https://github.com/ottok/mariadb-10.0/blob/master/debian/mariadb-server-10.0... Pull requests appreciated :)
The procedure is used here as it only executes "INSTALL PLUGIN unix_socket SONAME 'auth_socket';" if the plugin is not actually loaded (would output an error if it was the case). Another small issue of the actual approach is that it requires another mysqld execution just for this task which takes some times and is especially problematic when doing a package upgrade. Le 24/02/2015 10:06, Otto Kekäläinen a écrit :
Thanks for your help!
How should this then be re-written correctly?
SET sql_log_bin=0; USE mysql; DELIMITER //; CREATE PROCEDURE debian_plugin_install(IN plugin_name CHAR(50), IN soname CHAR(50)) BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND EXECUTE inst_plug; set @plugin_name=plugin_name; set @soname=soname ;set @install_plugin=CONCAT(\"INSTALL PLUGIN \",@plugin_name,\" SONAME '\", @soname, \"'\");PREPARE inst_plug FROM @install_plugin ; select PLUGIN_NAME INTO @a from information_schema.plugins where PLUGIN_NAME=@plugin_name AND PLUGIN_STATUS='ACTIVE' AND PLUGIN_TYPE='AUTHENTICATION' AND PLUGIN_LIBRARY LIKE concat(@soname,'%' ); DEALLOCATE PREPARE inst_plug; END// CALL debian_plugin_install('unix_socket', 'auth_socket') // DROP PROCEDURE debian_plugin_install//
https://github.com/ottok/mariadb-10.0/blob/master/debian/mariadb-server-10.0...
Pull requests appreciated :)
----- Original Message -----
Thanks for your help!
How should this then be re-written correctly?
Thinking strip away the procedures and work directly with a query of the information schema. perhaps even just call: set sql_log_bin=0; install plugin "unix_socket" and ignore the error if it exists..
SET sql_log_bin=0; USE mysql; DELIMITER //; CREATE PROCEDURE debian_plugin_install(IN plugin_name CHAR(50), IN soname CHAR(50)) BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND EXECUTE inst_plug; set @plugin_name=plugin_name; set @soname=soname ;set @install_plugin=CONCAT(\"INSTALL PLUGIN \",@plugin_name,\" SONAME '\", @soname, \"'\");PREPARE inst_plug FROM @install_plugin ; select PLUGIN_NAME INTO @a from information_schema.plugins where PLUGIN_NAME=@plugin_name AND PLUGIN_STATUS='ACTIVE' AND PLUGIN_TYPE='AUTHENTICATION' AND PLUGIN_LIBRARY LIKE concat(@soname,'%' ); DEALLOCATE PREPARE inst_plug; END// CALL debian_plugin_install('unix_socket', 'auth_socket') // DROP PROCEDURE debian_plugin_install//
https://github.com/ottok/mariadb-10.0/blob/master/debian/mariadb-server-10.0...
Pull requests appreciated :)
-- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
Another dirty way to do so by avoiding the use of a PROCEDURE is to use a prepared statement : SET @inst_plug=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, 'SELECT 1 LIMIT 0', "INSTALL PLUGIN unix_socket SONAME 'auth_socket'"); PREPARE inst_plug FROM @inst_plug; EXECUTE inst_plug; It will install the plugin if not present and wont return anything (and no error) on the other case. As for the idea to do it the simplest way (simply execute the INSTALL PLUGIN statement) and skip the exit on error as done for the "$password_column_fix_query" execution just few lines before ("set +e"). That way, the INSTALL PLUGIN statement could be added directly to the "$password_column_fix_query" execution rather than re-starting once more mysqld without even checking its exit status. ps: as a side note, the content of the replace_query variable set on line 160 should be directly put on the line 192 as its only called once. Le 24/02/2015 11:49, Daniel Black a écrit :
----- Original Message -----
Thanks for your help!
How should this then be re-written correctly? Thinking strip away the procedures and work directly with a query of the information schema.
perhaps even just call: set sql_log_bin=0; install plugin "unix_socket"
and ignore the error if it exists..
SET sql_log_bin=0; USE mysql; DELIMITER //; CREATE PROCEDURE debian_plugin_install(IN plugin_name CHAR(50), IN soname CHAR(50)) BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND EXECUTE inst_plug; set @plugin_name=plugin_name; set @soname=soname ;set @install_plugin=CONCAT(\"INSTALL PLUGIN \",@plugin_name,\" SONAME '\", @soname, \"'\");PREPARE inst_plug FROM @install_plugin ; select PLUGIN_NAME INTO @a from information_schema.plugins where PLUGIN_NAME=@plugin_name AND PLUGIN_STATUS='ACTIVE' AND PLUGIN_TYPE='AUTHENTICATION' AND PLUGIN_LIBRARY LIKE concat(@soname,'%' ); DEALLOCATE PREPARE inst_plug; END// CALL debian_plugin_install('unix_socket', 'auth_socket') // DROP PROCEDURE debian_plugin_install//
https://github.com/ottok/mariadb-10.0/blob/master/debian/mariadb-server-10.0...
Pull requests appreciated :)
I'm trying to engineer a fix for this but it seems really tricky. Here is what I've got so far: https://github.com/ottok/mariadb-10.0/commit/26fd165625b2e840fbda05ed11e5b7c...
Looks all right. Trying to test in a wheezy chroot. Needed https://github.com/ottok/mariadb-10.0/pull/8 applied for the wheezy version. even applying all the debian/patches/*.patch the following build error occurs dh build CMake Error at cmake/plugin.cmake:204 (INSTALL): install FILES given no DESTINATION! Call Stack (most recent call first): storage/oqgraph/CMakeLists.txt:52 (MYSQL_ADD_PLUGIN) -- OQGraph OK -- CONNECT: GCC: Some warnings disabled CMake Error at cmake/plugin.cmake:204 (INSTALL): install FILES given no DESTINATION! Call Stack (most recent call first): storage/connect/CMakeLists.txt:282 (MYSQL_ADD_PLUGIN) -- Configuring incomplete, errors occurred! make: *** [override_dh_auto_configure] Error 1 adding SET(INSTALL_SYSCONFDIR_DEB "/etc/mysql") to cmake/install_layout.cmake didn't help it. little lost what to do. ----- Original Message -----
I'm trying to engineer a fix for this but it seems really tricky. Here is what I've got so far: https://github.com/ottok/mariadb-10.0/commit/26fd165625b2e840fbda05ed11e5b7c...
-- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
If you don't want to build debian packages but only focus on the postinst script editing, you could just add the repository 'deb http://labs.seravo.fi/~otto/mariadb-repo/ sid-amd64/' to a sid virtual machine and install using my 10.0.17-1~exp1 builds (http://labs.seravo.fi/~otto/mariadb-repo/mariadb-10.0-sid-amd64/) To test changes in the postinstall file, you don't need to build the whole package, you could just use my ready-build binaries above and inject your own postinst script with 'sudo cp debian/mariadb-server-10.0.postinst /var/lib/dpkg/info/mariadb-server-10.0.postinst' (/var/lib/dpkg/info is where dpkg unpacks the scripts before running them). Or you could even run directly 'sh -x debian/mariadb-server-10.0.postinst configure' as that is the command that dpkg would run in the configure step. 2015-03-03 1:42 GMT+02:00 Daniel Black <daniel.black@openquery.com>:
Looks all right. Trying to test in a wheezy chroot.
Needed https://github.com/ottok/mariadb-10.0/pull/8 applied for the wheezy version.
I commented in the github issue why the pcre version needs to be what it is.
even applying all the debian/patches/*.patch the following build error occurs
dh build
CMake Error at cmake/plugin.cmake:204 (INSTALL): install FILES given no DESTINATION! Call Stack (most recent call first): storage/oqgraph/CMakeLists.txt:52 (MYSQL_ADD_PLUGIN)
-- OQGraph OK -- CONNECT: GCC: Some warnings disabled CMake Error at cmake/plugin.cmake:204 (INSTALL): install FILES given no DESTINATION! Call Stack (most recent call first): storage/connect/CMakeLists.txt:282 (MYSQL_ADD_PLUGIN)
-- Configuring incomplete, errors occurred! make: *** [override_dh_auto_configure] Error 1
adding SET(INSTALL_SYSCONFDIR_DEB "/etc/mysql") to cmake/install_layout.cmake didn't help it.
little lost what to do.
I don't get these any build errors.
I just ran manually the script, it ran without an error on the shell but errors arisen on the mysqld error log : ERROR: 1062 Duplicate entry 'unix_socket' for key 'PRIMARY' 150303 9:57:41 [ERROR] Aborting [...] ERROR: 1290 The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement 150303 9:57:50 [ERROR] Aborting The first one must be caused by the fact that i already had the plugin loaded, it should be avoided easily by using "REPLACE INTO" as having "false positive" errors on the log will be confusing for the user. For the second error, it must simply be the "DROP USER 'debian-sys-maint'@'localhost';" that should be replaced by a DELETE on the mysql.user table. As for the whole "operation", at first it seems that its a good idea but after some tought it will probably break certain setups, for example users that are using the root user from accounts other than root (its not a good practice but i saw it more than once in the past). There also could be users relying on the debian-sys-maint account that gets removed by the install script, there should be at least a warning notice before the package gets installed of these changes. Le 03/03/2015 09:07, Otto Kekäläinen a écrit :
If you don't want to build debian packages but only focus on the postinst script editing, you could just add the repository 'deb http://labs.seravo.fi/~otto/mariadb-repo/ sid-amd64/' to a sid virtual machine and install using my 10.0.17-1~exp1 builds (http://labs.seravo.fi/~otto/mariadb-repo/mariadb-10.0-sid-amd64/)
To test changes in the postinstall file, you don't need to build the whole package, you could just use my ready-build binaries above and inject your own postinst script with 'sudo cp debian/mariadb-server-10.0.postinst /var/lib/dpkg/info/mariadb-server-10.0.postinst' (/var/lib/dpkg/info is where dpkg unpacks the scripts before running them).
Or you could even run directly 'sh -x debian/mariadb-server-10.0.postinst configure' as that is the command that dpkg would run in the configure step.
I tried to clean up a little bit the script, i inlined the diff at the end of the mail. There is a first problem here : the "set_mysql_rootpw()" function resets the password and set the use of the "unix_socket" plugin to be used for every root users, even non-local one, i modified it so it only changes for the root@localhost user to avoid possible breaking of existing setups (which can still occur if users use the root@localhost user with a password on scripts for example). Another issue is that the "INSTALL PLUGIN unix_socket SONAME 'auth_socket';" statement will fail as $MYSQL_BOOTSTRAP runs with "--skip-grant-table" and INSTALL PLUGIN seems to require the grant table to be usable. If i am not mistaken, it can be replaced with "INSERT INTO mysql.plugin (name, dl) VALUES ('unix_socket', 'auth_socket');" then the plugin will get loaded at the next server start but i am not sure if it will load if "--skip-grant-table" is used. Which leads to another possible issue : the "set_mysql_rootpw()" function doesnt check that the "unix_socket" plugin is loaded before modifying the root user and it modifies directly the mysql.user table while in "--skip-grant-table" which can lead to a broken server with no root access if it didnt work as expected. I did work around that with the same ugly prepared statement construction i sent last time on this thread to emulate a test done on a procedure without using one. The statement PREPARE should return an "ERROR 1065 (42000): Query was empty" error if the plugin is not active and execute the "UPDATE mysql.user [...]" query if its loaded. Yet another potential problem is that the debian.cnf file gets replaced before the the migration to unix_socket has occured (and worked). Rather than having to backup the existing debian.cnf, overwriting it with the new configuration format then trying to install/configure the unix_socket auth then ifnally rollbacking or removing the backup of the file depending on the outcome, it would be safer to replace the file only once the migration has worked. If the installer is aborted in the middle of the operation, the "new" file will be kept in place and the backup will have to be manually restored (or maybe, debconf does take care of the debian_old_config.XXXXXX file by itself?). Le 02/03/2015 21:29, Otto Kekäläinen a écrit :
I'm trying to engineer a fix for this but it seems really tricky. Here is what I've got so far: https://github.com/ottok/mariadb-10.0/commit/26fd165625b2e840fbda05ed11e5b7c...
--- mariadb-server-10.0.postinst 2015-03-03 01:04:34.054732754 +0100 +++ mariadb-server-10.0.postinst 2015-03-03 02:17:46.753503859 +0100 @@ -29,19 +29,25 @@ mysql --no-defaults -u root -h localhost </dev/null >/dev/null 2>&1 } -# call with $1 = "online" to connect to the server, otherwise it bootstraps +# This function resets the root@localhost user password and enable the +# usage of the unix_socket plugin for it. +# Call with $1 = "online" to connect to the server, otherwise it bootstraps set_mysql_rootpw() { - - tfile=`mktemp` + tfile="$(mktemp)" if [ ! -f "$tfile" ]; then return 1 fi - # this avoids us having to call "test" or "[" on $rootpw + # 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" + + # This avoids us having to call "test" or "[" on $rootpw cat << EOF > $tfile -USE mysql; SET sql_log_bin=0; -UPDATE user SET password="", plugin="unix_socket" WHERE user='root'; +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; FLUSH PRIVILEGES; EOF if [ "$1" = "online" ]; then @@ -51,7 +57,7 @@ $MYSQL_BOOTSTRAP <$tfile retval=$? fi - rm -f $tfile + rm -f "$tfile" return $retval } @@ -122,81 +128,97 @@ # As the binlog cron scripts to need at least the Super_priv, I do first # the old query which always succeeds and then the new which may or may not. - # recreate the credentials file if not present or with debian-sys-maint + # Recreates the credentials file if not present or with debian-sys-maint # still there - dc=$mysql_cfgdir/debian.cnf; - if [ ! -e "$dc" -o -n "`fgrep debian-sys-maint $dc 2>/dev/null`" ]; then - if [ ! -d "$mysql_cfgdir" ]; then install -o 0 -g 0 -m 0755 -d $mysql_cfgdir; fi + dc=$mysql_cfgdir/debian.cnf + if [ ! -e "$dc" ]; then + # debian.cnf does not exists + if [ ! -d "$mysql_cfgdir" ]; then + # The configuration directory does not exists + install -o 0 -g 0 -m 0755 -d $mysql_cfgdir + fi + oldconf='' + else + # A backup of the existing debian.cnf is done + # In case the migration to auth_socket would fail, it will be restored + oldconf="$(mktemp --tmpdir=$mysql_cfgdir -t debian_old_config.XXXXXX)" + cp "$dc" "$oldconf" + fi + + fgrep -q debian-sys-maint $dc + if [ ! -e "$dc" -o $? -ne 0 ]; then + # debian.cnf does not exists or contains the debian-sys-maint user if [ -e "$dc" ]; then - oldconf=`mktemp --tmpdir=$mysql_cfgdir -t debian_old_config.XXXXXX` - cp $dc $oldconf + # A backup of the existing debian.cnf is done + # In case the migration to auth_socket would fail, it will be restored + oldconf="$(mktemp --tmpdir=$mysql_cfgdir -t debian_old_config.XXXXXX)" + cp "$dc" "$oldconf" else - oldconf='' + # There was no pre-existing debian.cnf file + oldconf='' + if [ ! -d "$mysql_cfgdir" ]; then + # The configuration directory does not exists + install -o 0 -g 0 -m 0755 -d $mysql_cfgdir + fi fi + + # (re)creation of the debian.cnf file umask 066 - cat /dev/null > $dc + > $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 + echo "# Automatically generated for Debian scripts. DO NOT TOUCH! +[client] +host = localhost +user = root +password = +socket = $mysql_rundir/mysqld.sock +[mysql_upgrade] +host = localhost +user = root +password = +socket = $mysql_rundir/mysqld.sock +basedir = /usr" >>$dc 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 \ - "USE mysql;\n" \ - "SET sql_mode='', sql_log_bin=0;\n" \ - "DROP USER 'debian-sys-maint'@'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" \ - "INSTALL PLUGIN unix_socket SONAME 'auth_socket';\n"` + # Upgrade Password column format before the root password gets set. + echo -e \ + "SET sql_log_bin=0; + ALTER TABLE mysql.user CHANGE Password Password char(41) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '' NOT NULL;" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER - # Install plugins and ignore if already there + # Install plugin(s) and ignore if already there set +e - echo "$install_plugins" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER + # Some plugins should be installed by 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. + echo -e \ + "SET sql_log_bin=0; + CREATE TABLE IF NOT EXISTS mysql.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'; + INSERT INTO mysql.plugin (name, dl) VALUES ('unix_socket', 'auth_socket');" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER set -e if ! set_mysql_rootpw; then + # The reset of the root@localhost user password and the activation of + # the auth_socket plugin for it has failed password_error="yes" - # restore old config file if exists - [ -e $oldconf ] && mv $oldconf $dc + # The debian.cnf file is restored if pre-existing + [ -e "$oldconf" ] && mv "$oldconf" "$dc" else - [ -e $oldconf ] && rm -f $oldconf - # purge debian-sys-maint user + [ -e "$oldconf" ] && rm -f "$oldconf" + # Purge of the debian-sys-maint@localhost user that is replaced with + # auth_socket on root@localhost + + # WARNING: This line might yield "The MariaDB server is running with + # the --skip-grant-tables option so it cannot execute this statement" set +e - echo "$replace_query" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER + echo -e \ + "SET sql_mode='', sql_log_bin=0; + DROP USER 'debian-sys-maint'@'localhost';" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER set -e fi ;; @@ -210,7 +232,7 @@ ;; esac -# here we check to see if we can connect as root without a password +# 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.
Sorry, i posted the wrong diff file, here is the right one. ps: i havent tried the postinst script Le 03/03/2015 02:29, Jean Weisbuch a écrit :
[...]
--- mariadb-server-10.0.postinst 2015-03-03 01:04:34.054732754 +0100 +++ mariadb-server-10.0.postinst 2015-03-03 02:17:46.753503859 +0100
--- mariadb-server-10.0.postinst 2015-03-03 01:04:34.054732754 +0100 +++ mariadb-server-10.0.postinst 2015-03-03 02:31:01.075282368 +0100 @@ -29,19 +29,25 @@ mysql --no-defaults -u root -h localhost </dev/null >/dev/null 2>&1 } -# call with $1 = "online" to connect to the server, otherwise it bootstraps +# This function resets the root@localhost user password and enable the +# usage of the unix_socket plugin for it. +# Call with $1 = "online" to connect to the server, otherwise it bootstraps set_mysql_rootpw() { - - tfile=`mktemp` + tfile="$(mktemp)" if [ ! -f "$tfile" ]; then return 1 fi - # this avoids us having to call "test" or "[" on $rootpw + # 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" + + # This avoids us having to call "test" or "[" on $rootpw cat << EOF > $tfile -USE mysql; SET sql_log_bin=0; -UPDATE user SET password="", plugin="unix_socket" WHERE user='root'; +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; FLUSH PRIVILEGES; EOF if [ "$1" = "online" ]; then @@ -51,7 +57,7 @@ $MYSQL_BOOTSTRAP <$tfile retval=$? fi - rm -f $tfile + rm -f "$tfile" return $retval } @@ -122,81 +128,83 @@ # As the binlog cron scripts to need at least the Super_priv, I do first # the old query which always succeeds and then the new which may or may not. - # recreate the credentials file if not present or with debian-sys-maint + # Recreates the credentials file if not present or with debian-sys-maint # still there - dc=$mysql_cfgdir/debian.cnf; - if [ ! -e "$dc" -o -n "`fgrep debian-sys-maint $dc 2>/dev/null`" ]; then - if [ ! -d "$mysql_cfgdir" ]; then install -o 0 -g 0 -m 0755 -d $mysql_cfgdir; fi + dc=$mysql_cfgdir/debian.cnf + fgrep -q debian-sys-maint $dc + if [ ! -e "$dc" -o $? -ne 0 ]; then + # debian.cnf does not exists or contains the debian-sys-maint user if [ -e "$dc" ]; then - oldconf=`mktemp --tmpdir=$mysql_cfgdir -t debian_old_config.XXXXXX` - cp $dc $oldconf + # A backup of the existing debian.cnf is done + # In case the migration to auth_socket would fail, it will be restored + oldconf="$(mktemp --tmpdir=$mysql_cfgdir -t debian_old_config.XXXXXX)" + cp "$dc" "$oldconf" else - oldconf='' + # There was no pre-existing debian.cnf file + oldconf='' + if [ ! -d "$mysql_cfgdir" ]; then + # The configuration directory does not exists + install -o 0 -g 0 -m 0755 -d $mysql_cfgdir + fi fi + + # (re)creation of the debian.cnf file umask 066 - cat /dev/null > $dc + > $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 + echo "# Automatically generated for Debian scripts. DO NOT TOUCH! +[client] +host = localhost +user = root +password = +socket = $mysql_rundir/mysqld.sock +[mysql_upgrade] +host = localhost +user = root +password = +socket = $mysql_rundir/mysqld.sock +basedir = /usr" >>$dc 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 \ - "USE mysql;\n" \ - "SET sql_mode='', sql_log_bin=0;\n" \ - "DROP USER 'debian-sys-maint'@'localhost';"` - # WARNING: This line might yield "The MariaDB server is running with - # the --skip-grant-tables option so it cannot execute this statement" + # Upgrade Password column format before the root password gets set. + echo -e \ + "SET sql_log_bin=0; + ALTER TABLE mysql.user CHANGE Password Password char(41) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '' NOT NULL;" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER - # 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" \ - "INSTALL PLUGIN unix_socket SONAME 'auth_socket';\n"` - - # Install plugins and ignore if already there + # Install plugin(s) and ignore if already there set +e - echo "$install_plugins" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER + # Some plugins should be installed by 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. + echo -e \ + "SET sql_log_bin=0; + CREATE TABLE IF NOT EXISTS mysql.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'; + INSERT INTO mysql.plugin (name, dl) VALUES ('unix_socket', 'auth_socket');" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER set -e if ! set_mysql_rootpw; then + # The reset of the root@localhost user password and the activation of + # the auth_socket plugin for it has failed password_error="yes" - # restore old config file if exists - [ -e $oldconf ] && mv $oldconf $dc + # The debian.cnf file is restored if pre-existing + [ -e "$oldconf" ] && mv "$oldconf" "$dc" else - [ -e $oldconf ] && rm -f $oldconf - # purge debian-sys-maint user + [ -e "$oldconf" ] && rm -f "$oldconf" + # Purge of the debian-sys-maint@localhost user that is replaced with + # auth_socket on root@localhost + + # WARNING: This line might yield "The MariaDB server is running with + # the --skip-grant-tables option so it cannot execute this statement" set +e - echo "$replace_query" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER + echo -e \ + "SET sql_mode='', sql_log_bin=0; + DROP USER 'debian-sys-maint'@'localhost';" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER set -e fi ;; @@ -210,7 +218,7 @@ ;; esac -# here we check to see if we can connect as root without a password +# 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.
I partly got started on the weekend but didn't get far. I'll continue looking this afternoon. ----- Original Message -----
Daniel, anybody..?
2015-02-20 22:52 GMT+02:00 Otto Kekäläinen <otto@seravo.fi>:
Hello Daniel!
It seems there are some issues with the passwordless login patch you sent me some weeks ago (https://github.com/ottok/mariadb-10.0/pull/3)
Can you help me debug the failing postinst script?
I added 'set -x' to the mariadb-server-10.0.postinst script and when it is run during the configure phase it will exit with code 1 and make dpkg abort the installation.
Last lines of execution: SET sql_log_bin=0; 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'\'';' + logger -p daemon.err -t mysqld_safe -i + /usr/sbin/mysqld --bootstrap --user=mysql --skip-grant-tables --default-storage-engine=myisam --plugin-load-add=auth_socket + set -e + echo 'SET sql_log_bin=0; USE mysql; DELIMITER //; CREATE PROCEDURE debian_plugin_install(IN plugin_name CHAR(50), IN soname CHAR(50)) BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND EXECUTE inst_plug; set @plugin_name=plugin_name; set @soname=soname ;set @install_plugin=CONCAT("INSTALL PLUGIN ",@plugin_name," SONAME '\''", @soname, "'\''");PREPARE inst_plug FROM @install_plugin ; select PLUGIN_NAME INTO @a from information_schema.plugins where PLUGIN_NAME=@plugin_name AND PLUGIN_STATUS='\''ACTIVE'\'' AND PLUGIN_TYPE='\''AUTHENTICATION'\'' AND PLUGIN_LIBRARY LIKE concat(@soname,'\''%'\'' ); DEALLOCATE PREPARE inst_plug; END// CALL debian_plugin_install('\''unix_socket'\'', '\''auth_socket'\'') // DROP PROCEDURE debian_plugin_install//' + logger -p daemon.err -t mysqld_safe -i + /usr/sbin/mysqld --bootstrap --user=mysql --skip-grant-tables --default-storage-engine=myisam --plugin-load-add=auth_socket timo@debunstable:~$ echo $? 1 timo@debunstable:~$ echo $? 0
This implies that the exit code from the line '/usr/sbin/mysqld --bootstrap --user=mysql --skip-grant-tables --default-storage-engine=myisam --plugin-load-add=auth_socket' is 1.
Any ideas why?
If you want to test it yourself, add to you apt sources the test repo
deb http://labs.seravo.fi/~otto/mariadb-repo/ mariabd-10.0-sid-amd64/
-- Check out our blog at http://seravo.fi/blog and follow @ottokekalainen
-- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
participants (4)
-
Daniel Black -
Jean Weisbuch -
Otto Kekäläinen -
Peter Laursen