MySQL Bugs: #4917: mysqlhotcopy doesn't use appropriate config files

Bug #4917	mysqlhotcopy doesn't use appropriate config files
Submitted:	5 Aug 2004 19:48	Modified:	1 Feb 2006 8:32
Reporter:	Erik Kay	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	4.1.3	OS:	Linux (Redhat Linux ES3)
Assigned to:		CPU Architecture:	Any

Description:
mysqlhotcopy isn't configured as part of the build process to use the appropriate my.cnf file for the local build.  So if you build your own mysqld and supporting tools and install it into your own directory with custom prefs, it won't pull in the right defaults.

How to repeat:
Build your own mysqld package in a custom directory, a custom socket and port and start up that database.  Now run mysqlhotcopy against that database.  Most of the other command line tools will automatically connect to the right database, but mysqlhotcopy winds up looking for a socket in /var/lib/mysql/mysql.sock.

Suggested fix:
A workaround I'm using now is to added the "mysql_read_default_file" option on the DBI->connect call to point at the my.cnf file specific to my build.  This should be done automatically as part of the build process.

note that there are two places in the file that this change needs to be made

Dear Sir,

Yes, the mysql_read_default_group=mysqlhotcopy call only reads common client options from /
etc/my.cnf and /usr/local/mysql/data/my.cnf.  Common client options include socket, port etc. 
but not mysqlhotcopy specific options.  mysqlhotcopy also does not take the same default file 
related options as the other clients do along with --help --verbose.

I ran into this in the past myself as a user.  I created an updated version of mysqlhotcopy for 
myself which hasn't been accepted in the mysql to date.  I will attach it to the bug report so that 
you may use it if you desire until this is addressed.

Unofficial mysqlhotcopy version that solves config file problems

Attachment: mysqlhotcopy (application/octet-stream, text), 35.00 KiB.

Erik, I'm not sure I understand.

What happens, I believe, is that mysqlhotcopy, being perl script, uses DBI and DBD::mysql.
Thus it uses defaults as compiled in libmysqlclient that DBD::mysql is linked with.

I think it's the correct behaviour, otherwise one won't be able upgrade DBD::mysql without fixing all the perl scripts on the system.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Sorry, I somehow missed this request for more info way back when.  I was in the process of upgrading our MySQL installation and was checking to see if this bug was fixed in the latest release.

I don't think your comment about this being an issue with the Perl DBD::mysql module is correct.  To my mind, there are two questions.  The first is whether tools that are part of the MySQL distribution should be smart enough to point at their own build, and not depend on the configuration of third party libraries.  In this case, every other tool in the distro uses the configuration that it was built with.  The one that use Perl (mysqlhotcopy as one example), don't do that.  Not only that, but mysqlhotcopy doesn't even have an option to point at a different config file manually so that you can override this broken behavior.  Both of these are bugs.

The second question is how clients that link against the shared mysql client libraries (php and perl being good examples) should behave by default when there are multiple mysql installations on a system.  This seems to be where you're taking the question, and I don't think it's relevant to this case.  I think it's fine that the client library defaults to one set of config.  It's up to the client code to provide the options of using a different default config file, etc.

Erik,

Please, try with a newer versions of 4.1.x. Look:

openxs@suse:~/dbs/4.1> bin/mysqlhotcopy --help

bin/mysqlhotcopy Ver 1.22

Usage: bin/mysqlhotcopy db_name[./table_regex/] [new_db_name | directory]

  -?, --help           display this helpscreen and exit
  -u, --user=#         user for database login if not current user
  -p, --password=#     password to use when connecting to server (if not set
                       in my.cnf, which is recommended)
  -h, --host=#         Hostname for local server when connecting over TCP/IP
  -P, --port=#         port to use when connecting to local server with TCP/IP
  -S, --socket=#       socket to use when connecting to local server

  --allowold           don't abort if target dir already exists (rename it _old)

  --addtodest          don't rename target dir if it exists, just add files to it
  --keepold            don't delete previous (now renamed) target when done
  --noindices          don't include full index files in copy
  --method=#           method for copy (only "cp" currently supported)

  -q, --quiet          be silent except for errors
  --debug              enable debug
  -n, --dryrun         report actions without doing them

  --regexp=#           copy all databases with names matching regexp
  --suffix=#           suffix for names of copied databases
  --checkpoint=#       insert checkpoint entry into specified db.table
  --flushlog           flush logs once all tables are locked
  --resetmaster        reset the binlog once all tables are locked
  --resetslave         reset the master.info once all tables are locked
  --tmpdir=#           temporary directory (instead of /tmp)
  --record_log_pos=#   record slave and master status in specified db.table
  --chroot=#           base directory of chroot jail in which mysqld operates

  Try 'perldoc bin/mysqlhotcopy' for more complete documentation

So, I see options to specify port and socket. Moreover, it works with the defaults, when built from sources:

openxs@suse:~/dbs/4.1> bin/mysqld_safe &
[1] 6432
openxs@suse:~/dbs/4.1> Starting mysqld daemon with databases from /home/openxs/dbs/4.1/var

openxs@suse:~/dbs/4.1> bin/mysqlhotcopy --user=root mysql /tmp
Locked 15 tables in 0 seconds.
Flushed tables (`mysql`.`columns_priv`, `mysql`.`db`, `mysql`.`func`, `mysql`.`help_category`, `mysql`.`help_keyword`, `mysql`.`help_relation`, `mysql`.`help_topic`, `mysql`.`host`, `mysql`.`tables_priv`, `mysql`.`time_zone`, `mysql`.`time_zone_leap_second`, `mysql`.`time_zone_name`, `mysql`.`time_zone_transition`, `mysql`.`time_zone_transition_type`, `mysql`.`user`) in 0 seconds.
Copying 45 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 15 tables (45 files) in 0 seconds (0 seconds overall).

Read http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html for the details.

If it still does not do all you want, please, specify the exact features missed in a new feature request.