MySQL Bugs: #96660: mysqldump output file issues

Bug #96660	mysqldump output file issues
Submitted:	26 Aug 2019 16:48	Modified:	27 Aug 2019 15:01
Reporter:	KIRK FRANKS	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	Ver 8.0.17 for Linux on x86_64	OS:	CentOS
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump csv-file sql-file

Description:
Running (/usr/bin/mysqldump -t -u root -pmypassword -T/tmp Kirk_BP_copy store_stats --fields-terminated-by=',' --where="data_date >= '2019-06-01' AND data_date <= '2019-07-31'"), as my normal user, produces sql and txt files in /tmp as expected. However the sql file is empty and the txt file belongs to mysql. I put these in /tmp because of the secure-file-priv needed and my need to generate output directories on the fly.

    Here is the listing from /tmp for these files:
    -rw-r----- 1 mysql   mysql    3348 Aug 26 11:18 store_stats.txt
    -rw-rw-r-- 1 theatro theatro     0 Aug 26 11:18 store_stats.sql

But when I run (/usr/bin/mysqldump -t -u root -pmypassword --replace Kirk_BP_copy store_stats --where="data_date >= '2019-06-01' AND data_date <= '2019-07-31'" 2>/dev/null | grep REPLACE > ../dumps/ad-hoc/Kirk_BP_copy/2019-06-01_2019-07-31_store_stats.sql), as my normal user, the file produced is not empty. 

    Here's its listing:
    -rwxr-xr-x 1 theatro theatro  3702 Aug 26 11:17 2019-06-01_2019-07-31_store_stats.sql

As mentioned initially:
--  the txt file looks good, but is owned by mysql...causing me automation headaches
--  the sql file is empty ... an error but it's not affecting my automation, since I run the 2nd command

The my.cnf is:
(base) [theatro@arts_server Kirk_BP_copy]$ cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authen...
# default-authentication-plugin=mysql_native_password

secure-file-priv=/tmp

bind-address = 0.0.0.0

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

How to repeat:
This is repeatable in my lab, and I suspect it will be in yours.

Hi Mr. Franks,

Thank you for your bug report.

However, I do not think that what you have reported is a bug. First of all, you use two totally separate set of options, each coming with its own conditions and limitations. Your first run results in instructing the server to  produce a file, that is created and written to by the server itself. And server is run under user `mysql`. MySQL server does not know what is Unix user that you are using.

Your second command is producing SQL file by mysqldump, which is run by your user. So, this is all expected behaviour that is fully documented our Reference Manual, chapter on mysqldump.

Not a bug.

Sinisa,

Your comment, 'MySQL server does not know what is Unix user that you are using', does not give me enough insight into the issue I face. This is in regard the CLI execution:

/usr/bin/mysqldump -t -u root -pmypassword -T/tmp Kirk_BP_copy store_stats --fields-terminated-by=',' --where="data_date >= '2019-06-01' AND data_date <= '2019-07-31'"

which produces both the sql and txt files:

-rw-r----- 1 mysql mysql 3348 Aug 26 11:18 store_stats.txt
-rw-rw-r-- 1 theatro theatro 0 Aug 26 11:18 store_stats.sql

The mysqldump command does not invoke output redirection, which would impart ownership of the output to theatro. But you can see, one file is owned by theatro, and the other by mysql. So, if the MySQL server does not know the user, how could it give ownership to the theatro user? It appears mysqldump knew the user theatro and produced the sql file. But if it interfaces to the MySQL server for the production of the txt file, then it certainly could have conveyed the knowledge of the theatro user to MySQL and the server could have used that information to give theatro ownership of the txt file. Perhaps it's not a bug, but it seems to be worthy of a feature request.

Could you show me some sample code or explain how a non-root user can change the ownership of the txt file to be theatro, without invoking sudo? I have this command embedded in a python script which is executed by theatro and cron. I have a workaround in my python script, but it involves a nasty use of sudo being passed the password. It's a hack and a security faux pas I would like to remove.

Regards,
-Kirk

The answer is simple.

You gave the wrong option for that command. 

Read our Reference Manual .....