MariaDB working in phpmyadmin but not in bash [duplicate]

Bash will perform interpolation on any double-quoted string. backticks signify command substitution in this context. So the following doesn't work because bash attempts to run xyflaged and replace it with the output of the command.

mysql -u me -paaaaaaaaaaaaaaa -e "use test; SELECT
`xyflagged`.`Article number`, `xyflagged`.`Flag` INTO outfile
'Test1618' fields terminated by ',' ENCLOSED BY '\"' lines terminated
by '\n' FROM xyflagged";

Single quoted strings are not subject to interpolation. But, single quotes are also strange in bash because they disable all special characters. That means you can't escape a single quote with a backslash in a single quoted string becuase the backslash is not special; '\'' is not a valid bash expression.

His next one is called a "heredoc". It has the same problem of string interpolation.

mysql -u me -paaaaaaaaaaaaaaa << EOF
use test; SELECT `xyflagged`.`Article number`, `xyflagged`.`Flag` INTO outfile 'Test1618' fields terminated by ',' ENCLOSED BY '\"' lines terminated by '\n' FROM xyflagged;
EOF

But, if you put the heredoc end-of-file delineator (EOF here) within single quotes, it will disable special characters in the heredoc. This works better because since you're not enquoting the stiring in single quotes, they don't have special significance to bash:

mysql -u me -paaaaaaaaaaaaaaa << 'EOF'
use test; SELECT `xyflagged`.`Article number`, `xyflagged`.`Flag` INTO outfile 'Test1618' fields terminated by ',' ENCLOSED BY '\"' lines terminated by '\n' FROM xyflagged;
EOF

Or, you can provide the input in a file (the name / extension is irrelevant) and bash will provide that files' contents to the standard input of the program:

mysql -u me -paaaaaaaaaaaaaaa < my-commands.sql

I prefer this last invocation because if you give it a sane extension (like .sql in this case) most editors are smart enough to do syntax highlighting on the file which makes it easier to edit.

Here's an exmaple of how that might work. Given the file cmd.sql:

$ cat cmd.sql
use test;
select * 
INTO outfile '/var/lib/mysql-files/Test1618' 
fields terminated by ',' 
ENCLOSED BY '"' 
lines terminated by '\n' 
FROM test;

And a table something like this:

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` text,
  PRIMARY KEY (`id`)
);
INSERT INTO `test` VALUES (1,'erik258'),(2,'nbk'),(3,'Chiwda');

You can invoke something like this:

mysql < cmd.sql

And the result will be at the specified OUTFILE:

$ cat /var/lib/mysql-files/Test1618
"1","erik258"
"2","nbk"
"3","Chiwda"

Edit: Dynamic filename.

INTO OUTFILE doesn't have good support for variable filenames in itself. So it makes sense to replace the filename externally.

In bash, an easy way to replace content is sed.

If the command contains a replacement __DATE__:

select *
INTO outfile '/var/lib/mysql-files/test__DATE__.csv'
fields terminated by ','
ENCLOSED BY '"'
lines terminated by '\n'
FROM test;

You can use sed to replace DATE before you feed it to mysql:

sed -e "s:__DATE__:$(date +%Y-%m-%d):" cmd.sql  | mysql test

Now bash creates a pipe from sed's output to mysql's input. sed uses the filename cmd.sql as a source of input, and modifies it with the replacement expression before it is output.

Now file should exit in its expected location:

$ ls /var/lib/mysql-files
test2024-03-16.csv