Critical Patch for Ubuntu 16 and Mysql 5.7.x servers Topic is solved

[skamasle]

Hi

As always I say sorry for my bad english and a lot of mistakes in my write.

If you use ubuntu 16.04 or use mysql 5.7 whit vestacp I strongly recomended you apply this temporary patch until vesta team deploy official one.

Why this is critital if all working fine ?

Vesta fail when store mysql password into user/db.conf on mysql 5.7+ so you can create 100 databases / users today and tomorrow whant transfer to orther server or just backup it, whit default install your password not get saved

This work only whit mysql 5.7.6+ earlier versions of 5.7 like 5.7.5 may not work.

The problem cause this 3 bad problems:

1- Your password not stored in user/db.conf so when you make a databases backup you not backup your actual mysql user password.
2- If you not backup you cant restore it in your server or in others servers.
3- Also fail rabuild function when you try restore mysql database from orther server whit correct db.conf it cant restore it because rebuild function isnt prepared for mysql 5.7

So you can apply patch :

Login in your server as root->

##########

Code: Select all

cd /usr/local/vesta/func

# backup db. sh and rebuild.sh

Code: Select all

mv db.sh db-bk.sk
mv rebuild.sh rebuild-bk.sh

# Download new files:

Code: Select all

wget https://raw.githubusercontent.com/Skamasle/vesta/master/func/db.sh
wget https://raw.githubusercontent.com/Skamasle/vesta/master/func/rebuild.sh

Code: Select all

chmod +x db.sh rebuild.sh

###########

db.sh solve first problem and rebuild.sh solve second and third problems ( backup restauration )

Why no pull request ?

Not is most elegant solution, working fine for now, save passwords and restore it, but I think vesta team will do better job for this and rebuild some esential functions to do better support for new mysql versions.

Cat this break something ?

I dont think ( only test this in ubuntu 16.04 ), but whitout patch you not store passwords, always you can revert patch just delete files and mv back older ones.

How to test y you afected by bug ?

Code: Select all

cat /usr/local/vesta/data/users/YOURUSER/db.conf 

Code: Select all

DB='test2_3' DBUSER='test2_3' MD5='' HOST='localhost' TYPE='mysql' CHARSET='UTF8' U_DISK='1' SUSPENDED='no' TIME='22:26:16' DATE='2016-11-28'
DB='test2_d' DBUSER='test2_d' MD5='*CC2AADF4E7EC1C90E7136D2B3B9D14F3CD' HOST='localhost' TYPE='mysql' CHARSET='UTF8' U_DISK='1' SUSPENDED='no' TIME='22:26:16' DATE='2016-11-28'
DB='test2_t' DBUSER='test2_t' MD5='' HOST='localhost' TYPE='mysql' CHARSET='UTF8' U_DISK='1' SUSPENDED='no' TIME='22:26:16' DATE='2016-11-28'
DB='test2_test3' DBUSER='test2_43' MD5='' HOST='localhost' TYPE='mysql' CHARSET='UTF8' U_DISK='1' SUSPENDED='no' TIME='22:26:16' DATE='2016-11-28'
DB='test2_x' DBUSER='test2_x' MD5='*196BDEDE2AE4F84CA44C47D54D78478C7E2' HOST='localhost' TYPE='mysql' CHARSET='UTF8' U_DISK='1' SUSPENDED='no' TIME='22:26:16' DATE='2016-11-28'

As you can see no MD5 string was added:

Code: Select all

MD5=''

When working

Code: Select all

MD5='*196BDEDE2AE4F84CA44C47D54D78478C7E2'

How can solve empty md5 value ?

Solved in next responwe -> viewtopic.php?f=20&t=13239#p53622

When you restore backup in mysql 5.7 you can run from mysql cli

SELETC user FROM mysql.users;

Then you can check your user was not restored.

This is reported also here: https://github.com/serghey-rodin/vesta/issues/959

All code changes and recomendations are accepted.

[skamasle]

I found a server whit some password missed I do a script to complement the patch

It will fix missed paswords from user/db.conf

Is a simple script, I supose you cant connect to mysql whitout password because you have standard vesta install whit /root/.my.cnf and you login as root, I dont spend time to do extra checks but maybe do it when get some time

Here a script:

Code: Select all

#!/bin/bash
# Maks Skamasle
# Fix db.conf missing passwords in vestacp when run mysql 5.7 / ubuntu 16
user=$1
if [ -e /usr/local/vesta/data/users/${user}/db.conf ]; then
	conf=/usr/local/vesta/data/users/${user}/db.conf 
	get_db_user=$(v-list-databases $user plain | awk '{ print  $2 }')

	for u in $get_db_user
	do
		md5=$(mysql -e "SHOW CREATE USER $u" | grep password |cut -f8 -d \' )
		sed -i "s/DBUSER='$u' MD5=''/DBUSER='$u' MD5='$md5'/" $conf
		echo "Mysql User $u Fixed"
	done
fi

You can download and run it

Code: Select all

wget http://mirror.skamasle.com/vestacp/fix/fix-mysql57.sh

Code: Select all

bash fix-mysql57.sh USER

USER is a vestacp user ex:

Code: Select all

bash fix-mysql57.sh admin

This only get md5 for each mysqluser and insert back in your db.conf if is missing.

[Rhandy]

So. I think this the reason when today I move one site from one user to other. I lost DB.

[skamasle]

I think you only can lost passwords but not database, but I not know if move functions have orther problems

[skurudo]

Thanks, skamasle
sticky topic for 60 days, until 0.9.18 release (it'll be sooner, I hope)

skid know about this prob, drew his attention to it :)

[mestresan]

WoW... your script fixed my problem

thank you very much

[skamasle]

Oficial fix is out

You can use same procedure to fix, fork is updated :)

[fedekrum]

One question:
It can be said that problem will not happen on a fresh Vesta installation at a fresh Ubuntu 16 server IF you run the following before restoring any user ?

Code: Select all

curl https://raw.githubusercontent.com/serghey-rodin/vesta/04d617d756656829fa6c6a0920ca2aeea84f8461/func/db.sh > /usr/local/vesta/func/db.sh
curl https://raw.githubusercontent.com/serghey-rodin/vesta/04d617d756656829fa6c6a0920ca2aeea84f8461/func/rebuild.sh > /usr/local/vesta/func/rebuild.sh

(The downloaded files make reference to db.sh and rebuild.sh from this commit : "MySQL 5.7 support. Fixes #959. Thanks Maks Skamasle!")

[skamasle]

One question:
It can be said that problem will not happen on a fresh Vesta installation at a fresh Ubuntu 16 server IF you run the following before restoring any user ?

Code: Select all

curl https://raw.githubusercontent.com/serghey-rodin/vesta/04d617d756656829fa6c6a0920ca2aeea84f8461/func/db.sh > /usr/local/vesta/func/db.sh
curl https://raw.githubusercontent.com/serghey-rodin/vesta/04d617d756656829fa6c6a0920ca2aeea84f8461/func/rebuild.sh > /usr/local/vesta/func/rebuild.sh

(The downloaded files make reference to db.sh and rebuild.sh from this commit : "MySQL 5.7 support. Fixes #959. Thanks Maks Skamasle!")

Hi, you can download that files, are the same, and fix same issue.

[dimahna]

any help here ?