Member Avatar for AndrisP

Is the method in MySQL to set in SQL script dynamic log file name?
e.g in Oracle PL/SQL

set define on
column sdate new_value sdate
select to_char(sysdate,'YYYY.MM.DD_HH.MI') sdate from dual;
spool 'logs/install_&sdate..log';

e.g. in PG/SQL

\o ./logs/install_`date +"%Y-%m-%d_%H%M"`.log

but I cant find similar in MySQL

\T ??????
Edited by AndrisP
  • 3 Contributors
  • 6 Replies
  • 2K Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by AndrisP

Dani AI

Generated

you are right: the mysql client cannot compute a dynamic name for tee/\T inside a .sql script. The client itself interprets tee and treats the argument literally (no shell expansion), and tee works only in interactive mode. In older manuals this is explicit: quote characters are taken as part of the file name; in current docs tee simply records statements and their output when enabled. ()

Two practical approaches that keep your install flow simple:

  • Let the shell create the timestamp, then pass it to mysql at connect time. Example on Unix-like shells:

    TS=$(date +%Y%m%d-%H%M%S)
LOG=./logs/install_$TS.log
mysql --tee="$LOG" -u user -p dbname < install.sql

    This gives you a full transcript (statements plus results) because tee logs everything printed by the client. Remember tee is interactive-only; feeding your script via standard input keeps the session interactive for logging purposes. (dev.mysql.com)

  • If you must pick the file name from inside the script, use pager to hand results to the OS and let the shell build the path:

    pager cat > ./logs/install_$(date +%Y%m%d-%H%M%S).log
source make_tables.sql
nopager

    pager runs an external command (pipelines are allowed), so shell substitution works. Caveat: pager captures result sets, not every character the client prints; for a full transcript combine this with a static tee if needed. Also note pager is Unix-only in many versions of the classic client. (dev.mysql.com)

is right that server logs live in a different world; they are not a substitute for an install transcript. And ’s SELECT ... INTO OUTFILE is great for exporting query results, but it will not capture errors, prompts, or the full session output you typically want during installs. (dev.mysql.com)

Recommended Answers

All 6 Replies

Member Avatar for rproffitt

This is a rabbit hole. How deep you want to go is up to you. For me I found log rotation to suffice and here's the link I used to refresh my memory on this subject.

But there are many rabbit holes here. That is, MySQL has more than one log. I think it's best to find and use a log rotation script and use that to rotate/maintain/control log growth.

Read https://dev.mysql.com/doc/refman/5.7/en/log-file-maintenance.html for examples.

Member Avatar for AndrisP

That's not what I'm looking for - its for install script

commented: On many MySQL installs, the logrotate is included. If not, pick a rabbit hole and choose what drink you want. (Yes, Alice in Wonderland references.) +12
Member Avatar for cereal

Hi Andris,

you may want to use SELECT ... INTO OUTFILE 'file_name_here' and perhaps use CONCAT() to create the query, if you do:

set @file = concat('/tmp/file_', UNIX_TIMESTAMP(), '.log');
select 'hello' into outfile @file;

It will not work, so you have to do:

set @file := concat("/tmp/file_", UNIX_TIMESTAMP(), ".log");
set @query := concat("select 'hello' into outfile '", @file, "'");
prepare stmt from @query;
execute stmt;
deallocate prepare stmt;

As suggested here:

But it's mandatory that the destination file does not exists, otherwise the query will fail, this is done to avoid overwriting files with random content. The alternative, if you want to append results, for example, is to use mysqldump or something like this:

mysql -uVAR -pVAR -e "SELECT 'hello';" > /tmp/random.log

To execute, if using PHP, from exec().

// Edit
But looking at previous answers, now I'm not sure is this you where searching for.

Edited by cereal
Member Avatar for AndrisP

- this do not workd inside SQL script file. I want to set command in script file

tee ./dinamic_file_name_here.log

or similar command

\T ./dinamic_file_name_here.log

but file name can not set dynamically to run script from command prompt e.g.

\T ./dinamic_file_name_here.log
\. ./make_tables.sql
\. ./install_routines.sql
\. ./install_triggers.sql
\. ./insert_start_setup.sql
Edited by AndrisP
Member Avatar for rproffitt

More to the point:

Log control at runtime. The system variables associated with log tables and files enable runtime control over logging:

The global log_output system variable indicates the current logging destination. It can be modified at runtime to change the destination.

From https://dev.mysql.com/doc/refman/5.7/en/log-destinations.html

However my bet is you'll have to do the flush command for MySQL to start using the new log file.

Member Avatar for AndrisP

Not so beautiful but I found another solution - add --tee to make connection

mysql -u <username> -p -D <database> --tee ./install_$(date +"%Y-%m-%d_%H-%M").log

MySQL does not support dynamic filename in SQL script

Edited by AndrisP
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.