Change default database prefix or show other databases

[spacing]

So I recently posted this issue on this forum and just now discovered what was the problem.

I just remembered when I setup my site originally I imported the db from other host with a different prefix and restored it through the root phpmyadmin user not the admin user, thus leading to vesta not recognizing any db and showing the only existing one as empty.

Is there any way vesta can recognize this db created by the root user and also is there any way I can change the default prefix for db names on vesta?

[tjebbeke]

The prefix is always the username.

Vesta has it own config files per user. To add your self created database you can edit the database config file of the specific user.
/usr/local/vesta/data/users/{username}/db.conf
DB='user_dbname' DBUSER='user_dbuser' MD5='md5password' HOST='localhost' TYPE='mysql' CHARSET='UTF8' U_DISK='1' SUSPENDED='no' TIME='09:21:29' DATE='2015-01-16'

[shankarnewton]

Hi Guys,
is there any way to change the config so that each time when i create a new database, the prefix is not included.
I feel somethings prefix are helpful, but sometimes they messup and am unable to have a dedicated db seperately for a single application.

[tjebbeke]

Hi Guys,
is there any way to change the config so that each time when i create a new database, the prefix is not included.
I feel somethings prefix are helpful, but sometimes they messup and am unable to have a dedicated db seperately for a single application.

No, it isn't possible. When you don't take the username as the prefix, every user can add the same database. Of course, there is an error handler to avoid this. But, I don't think it's a good idea when you have a lot of users.

[spacing]

The prefix is always the username.

Vesta has it own config files per user. To add your self created database you can edit the database config file of the specific user.
/usr/local/vesta/data/users/{username}/db.conf
DB='user_dbname' DBUSER='user_dbuser' MD5='md5password' HOST='localhost' TYPE='mysql' CHARSET='UTF8' U_DISK='1' SUSPENDED='no' TIME='09:21:29' DATE='2015-01-16'

I actually ended up moving my existing db to the default admin_ prefix one but vestaCP is still showing the admin_dbname database as 0mb and not recognizing it properly despite it being full (300Mb +) on phpmyadmin, any way to fix this?

[tjebbeke]

Vesta use this code to check the usage:

Code: Select all

get_mysql_disk_usage() {
    mysql_connect $HOST
    query="SELECT SUM( data_length + index_length ) / 1024 / 1024 \"Size\"
        FROM information_schema.TABLES WHERE table_schema='$database'"
    usage=$(mysql_query "$query" |tail -n1)
    if [ "$usage" == 'NULL' ] || [ "${usage:0:1}" -eq '0' ]; then
        usage=1
    fi
    export LC_ALL=C
    usage=$(printf "%0.f\n"  $usage)
}

You can debug what the output is of the query.

Code: Select all

SELECT SUM( data_length + index_length ) / 1024 / 1024 \"Size\" FROM information_schema.TABLES WHERE table_schema='admin_dbname'

[spacing]

Vesta use this code to check the usage:

Code: Select all

get_mysql_disk_usage() {
    mysql_connect $HOST
    query="SELECT SUM( data_length + index_length ) / 1024 / 1024 \"Size\"
        FROM information_schema.TABLES WHERE table_schema='$database'"
    usage=$(mysql_query "$query" |tail -n1)
    if [ "$usage" == 'NULL' ] || [ "${usage:0:1}" -eq '0' ]; then
        usage=1
    fi
    export LC_ALL=C
    usage=$(printf "%0.f\n"  $usage)
}

You can debug what the output is of the query.

Code: Select all

SELECT SUM( data_length + index_length ) / 1024 / 1024 \"Size\" FROM information_schema.TABLES WHERE table_schema='admin_dbname'

I get this error

[tjebbeke]

try:
SELECT SUM( data_length + index_length ) / 1024 / 1024 "Size" FROM information_schema.TABLES WHERE table_schema='admin_dbname'