Page 1 of 2

Critical Patch for Ubuntu 16 and Mysql 5.7.x servers

Posted: Tue Nov 29, 2016 1:42 pm
by 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.

Re: Critical Patch for Ubuntu 16 and Mysql 5.7.x servers

Posted: Fri Dec 02, 2016 8:24 pm
by 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.

Re: Critical Patch for Ubuntu 16 and Mysql 5.7.x servers

Posted: Mon Dec 05, 2016 8:25 pm
by Rhandy
So. I think this the reason when today I move one site from one user to other. I lost DB.

Re: Critical Patch for Ubuntu 16 and Mysql 5.7.x servers

Posted: Mon Dec 05, 2016 9:30 pm
by skamasle
I think you only can lost passwords but not database, but I not know if move functions have orther problems

Re: Critical Patch for Ubuntu 16 and Mysql 5.7.x servers

Posted: Tue Dec 06, 2016 6:17 am
by 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 :)

Re: Critical Patch for Ubuntu 16 and Mysql 5.7.x servers

Posted: Wed Dec 07, 2016 10:00 am
by mestresan
WoW... your script fixed my problem

thank you very much

Re: Critical Patch for Ubuntu 16 and Mysql 5.7.x servers

Posted: Fri Dec 09, 2016 7:44 pm
by skamasle
Oficial fix is out

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

Re: Critical Patch for Ubuntu 16 and Mysql 5.7.x servers

Posted: Sun Dec 18, 2016 11:34 am
by 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!")

Re: Critical Patch for Ubuntu 16 and Mysql 5.7.x servers

Posted: Sun Dec 18, 2016 8:01 pm
by skamasle
fedekrum wrote: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.

Re: Critical Patch for Ubuntu 16 and Mysql 5.7.x servers

Posted: Tue Aug 29, 2017 3:29 pm
by dimahna
Image
any help here ?