PDA

View Full Version : ZRM can't handle underscores



tiredofthinking
July 11th, 2007, 07:32 AM
Hi,

I was playing with this tool and realized the product (and/or mysqlhotcopy) cannot handle tables that have underscores in them -- even though underscores are completely valid in mysql table naming.

awhq8231:/etc/mysql-zrm root# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0
schedule:INFO: ZRM for MySQL Community Edition - version 1.2
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 1.2
dailyrun:backup:INFO: backup-set=dailyrun
dailyrun:backup:INFO: backup-date=20070711101257
dailyrun:backup:INFO: host=localhost
dailyrun:backup:INFO: backup-date-epoch=1184166777
dailyrun:backup:INFO: retention-policy=5D
dailyrun:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 1.2
dailyrun:backup:INFO: mysql-version=5.0.27-log
dailyrun:backup:INFO: backup-directory=/Volumes/storage/mysql-backup/dailyrun/20070711101257
dailyrun:backup:INFO: backup-level=0
dailyrun:backup:ERROR: Output of command: 'mysqlhotcopy' is {
No tables to hot-copy at /usr/local/mysql/bin/mysqlhotcopy line 391.
}
dailyrun:backup:ERROR: mysqlhotcopy command did not succeed. Command used is "/usr/local/mysql/bin"/mysqlhotcopy --socket="/tmp/mysql.sock" --quiet http_auth./^user_info$/ "/Volumes/storage/mysql-backup/dailyrun/20070711101257" > /Volumes/storage/tmp/kdvrol5dgU 2>&1 Return value is 8448
dailyrun:backup:INFO: backup-status=Backup failed
dailyrun:backup:INFO: Backup failed
ERROR: /usr/bin/mysql-zrm did not finish successfully
awhq8231:/etc/mysql-zrm root#

awhq8231:/etc/mysql-zrm root# "/usr/local/mysql/bin"/mysqlhotcopy --socket="/tmp/mysql.sock" http_auth./^user_info$/ "/Volumes/storage/mysql-backup/dailyrun/20070711100428"
No tables to hot-copy at /usr/local/mysql/bin/mysqlhotcopy line 391.
awhq8231:/etc/mysql-zrm root#

tiredofthinking
July 11th, 2007, 09:55 AM
I fixed this by removing the regex ^ and \$ in mysql-zrm-backup so that the format passed to mysqlhotcopy is database./table_name/.

kkg
July 11th, 2007, 09:50 PM
I fixed this by removing the regex ^ and \$ in mysql-zrm-backup so that the format passed to mysqlhotcopy is database./table_name/.

This seems to be due to a bug in mysqlhotcopy. I tried with the mysqlhotcopy in 5.0.21 and mysqlhotcopy behaved in the same manner you have described.
But when I run mysqlhotcopy that is found in 5.0.24, mysqlhotcopy worked fine.

I ran mysqlhotcopy that is with 5.0.21 (This is what is installed on my machine), I get the following results

mysql@kkg:/home/kkg> "/usr/bin/"mysqlhotcopy --socket="/opt/zmc/mysql/tmp/mysql.sock" http_auth./^user_info$/ /tmp
No tables to hot-copy at /usr/bin/mysqlhotcopy line 394.


I copied the mysqlhotcopy from 5.0.24 to /tmp and ran that command and the following was the output.
mysql@kkg:/home/kkg> "/tmp/"mysqlhotcopy --socket="/opt/zmc/mysql/tmp/mysql.sock" http_auth./^user_info$/ /tmp
Locked 1 tables in 0 seconds.
Flushed tables (`http_auth`.`user_info`) in 0 seconds.
Copying 3 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 1 tables (3 files) in 0 seconds (0 seconds overall).

kkg
July 11th, 2007, 10:49 PM
The difference in the two mysqlhotcopy that is relevant to this is in the function get_list_of_tables()

The following code is probably the bug fix in the mysqlhotcopy (5.0.24). When I patched the nonworking mysqlhopycopy script with this code, the script worked correctly.

## Remove quotes around table names
my $quote = $dbh->get_info(29); # SQL_IDENTIFIER_QUOTE_CHAR
if ($quote) {
foreach (@dbh_tables) {
s/^$quote(.*)$quote$/$1/;
s/$quote$quote/$quote/g;
}
}

Ideally I would suggest getting the latest version of mysqlhotcopy and using that.

--kkg

tiredofthinking
August 2nd, 2007, 07:11 AM
Actually, using 5.1.20 and the latest ZRM I am still having a similar issue with underscores. It seems like the database name is getting entered twice?

awhq8231:~/zrm/usr/bin root# mysql-zrm-scheduler --now --backup-set casemgt-auth --backup-level 0
schedule:INFO: ZRM for MySQL Community Edition - version 1.2
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 1.2
casemgt-auth:backup:INFO: backup-set=casemgt-auth
casemgt-auth:backup:INFO: backup-date=20070802100850
casemgt-auth:backup:INFO: host=localhost
casemgt-auth:backup:INFO: backup-date-epoch=1186067330
casemgt-auth:backup:INFO: retention-policy=1M
casemgt-auth:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 1.2
casemgt-auth:backup:INFO: mysql-version=5.1.20-beta-log
casemgt-auth:backup:INFO: backup-directory=/Volumes/storage/mysql-backup/casemgt-auth/20070802100850
casemgt-auth:backup:INFO: backup-level=0
casemgt-auth:backup:ERROR: Output of command: 'mysqlhotcopy' is {
Invalid db.table name 'casemgt.casemgt`.`case_status' at /usr/local/mysql/bin/mysqlhotcopy line 855.
}
casemgt-auth:backup:ERROR: mysqlhotcopy command did not succeed. Command used is "/usr/local/mysql/bin"/mysqlhotcopy --user="backup" --password="*****" --socket="/tmp/mysql.sock" --quiet casemgt "/Volumes/storage/mysql-backup/casemgt-auth/20070802100850" > /Volumes/storage/tmp/zQbWvFGkCr 2>&1 Return value is 8448
casemgt-auth:backup:INFO: backup-status=Backup failed
casemgt-auth:backup:INFO: Backup failed
ERROR: /usr/bin/mysql-zrm did not finish successfully
awhq8231:~/zrm/usr/bin root#

My config:

backup-mode=raw
destination=/Volumes/storage/mysql-backup
retention-policy=1M
compress=1
compress-plugin=/usr/bin/gzip
#databases=casemgt http_auth
databases=casemgt
user="xxx"
password="yyy"
socket=/tmp/mysql.sock
mysql-binpath="/usr/local/mysql/bin"
mysql-binlog-path="/Volumes/db/binary-logs"
tmpdir=/Volumes/storage/tmp
#mailto="mysqldba@company.com"
html-reports=backup-status-info, backup-performance-info, backup-retention-info, backup-app-performance-info
html-report-directory=/Library/WebServer/Documents/backup-reports/
webserver-url=zzz
rss-header-location=/etc/mysql-zrm/
#pre-backup-plugin="/usr/share/mysql-zrm/plugins/pre-backup.pl"
#post-backup-plugin="/usr/share/mysql-zrm/plugins/post-backup.pl"


Any clues?

kkg
August 2nd, 2007, 09:36 PM
Actually, using 5.1.20 and the latest ZRM I am still having a similar issue with underscores. It seems like the database name is getting entered twice?

awhq8231:~/zrm/usr/bin root# mysql-zrm-scheduler --now --backup-set casemgt-auth --backup-level 0
schedule:INFO: ZRM for MySQL Community Edition - version 1.2
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 1.2
casemgt-auth:backup:INFO: backup-set=casemgt-auth
casemgt-auth:backup:INFO: backup-date=20070802100850
casemgt-auth:backup:INFO: host=localhost
casemgt-auth:backup:INFO: backup-date-epoch=1186067330
casemgt-auth:backup:INFO: retention-policy=1M
casemgt-auth:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 1.2
casemgt-auth:backup:INFO: mysql-version=5.1.20-beta-log
casemgt-auth:backup:INFO: backup-directory=/Volumes/storage/mysql-backup/casemgt-auth/20070802100850
casemgt-auth:backup:INFO: backup-level=0
casemgt-auth:backup:ERROR: Output of command: 'mysqlhotcopy' is {
Invalid db.table name 'casemgt.casemgt`.`case_status' at /usr/local/mysql/bin/mysqlhotcopy line 855.
}
casemgt-auth:backup:ERROR: mysqlhotcopy command did not succeed. Command used is "/usr/local/mysql/bin"/mysqlhotcopy --user="backup" --password="*****" --socket="/tmp/mysql.sock" --quiet casemgt "/Volumes/storage/mysql-backup/casemgt-auth/20070802100850" > /Volumes/storage/tmp/zQbWvFGkCr 2>&1 Return value is 8448
casemgt-auth:backup:INFO: backup-status=Backup failed
casemgt-auth:backup:INFO: Backup failed
ERROR: /usr/bin/mysql-zrm did not finish successfully
awhq8231:~/zrm/usr/bin root#

My config:

backup-mode=raw
destination=/Volumes/storage/mysql-backup
retention-policy=1M
compress=1
compress-plugin=/usr/bin/gzip
#databases=casemgt http_auth
databases=casemgt
user="xxx"
password="yyy"
socket=/tmp/mysql.sock
mysql-binpath="/usr/local/mysql/bin"
mysql-binlog-path="/Volumes/db/binary-logs"
tmpdir=/Volumes/storage/tmp
#mailto="mysqldba@company.com"
html-reports=backup-status-info, backup-performance-info, backup-retention-info, backup-app-performance-info
html-report-directory=/Library/WebServer/Documents/backup-reports/
webserver-url=zzz
rss-header-location=/etc/mysql-zrm/
#pre-backup-plugin="/usr/share/mysql-zrm/plugins/pre-backup.pl"
#post-backup-plugin="/usr/share/mysql-zrm/plugins/post-backup.pl"


Any clues?


It looks like a mysqlhotcopy bug.

could you run the command

"/usr/local/mysql/bin"/mysqlhotcopy --user="backup" --password="*****" --socket="/tmp/mysql.sock" --quiet casemgt /tmp

by hand and let me know what you see? Please substitute your actual password in place of "*****"

--kkg

kkg
August 2nd, 2007, 09:46 PM
It looks like a mysqlhotcopy bug.

could you run the command

"/usr/local/mysql/bin"/mysqlhotcopy --user="backup" --password="*****" --socket="/tmp/mysql.sock" --quiet casemgt /tmp

by hand and let me know what you see? Please substitute your actual password in place of "*****"

--kkg

The following bug filed on mysqlhotcopy describes your exact problem I think.
http://bugs.mysql.com/bug.php?id=27303

It als ohas a workaround documented there.

tiredofthinking
August 3rd, 2007, 06:19 AM
The following bug filed on mysqlhotcopy describes your exact problem I think.
http://bugs.mysql.com/bug.php?id=27303

It als ohas a workaround documented there.


Thanks much! Problem solved!