Search code examples
bashmariadb

MariaDB working in phpmyadmin but not in bash

This line:

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

However, this works:

"use test; SELECT * INTO outfile 'Test1618' fields terminated by ',' ENCLOSED BY '\"' lines terminated by '\n' FROM xyflagged;

I've looked at several solutions on SO, such as this and this and several more about escaping strings. Obviously, it has something to do with the space in the column name (I don't have control over that). So I tried various combinations:

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

use test; SELECT `Article number`, `Flag` INTO outfile 'Test1618' fields terminated by ',' ENCLOSED BY '\"' lines terminated by '\n' FROM xyflagged;

use test; SELECT [xyflagged].[Article number], [xyflagged].[Flag] INTO outfile 'Test1618' fields terminated by ',' ENCLOSED BY '\"' lines terminated by '\n' FROM xyflagged;

as suggested by the various answers and the brackets especially appear to have worked for some but not for me.

I've tried it like this:

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";

and also like this:

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

The error is always:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' INTO outfile 'Test1618' fields terminated by ...' at line 1

Maybe it is something basic or obvious, but I think I've tried every combination and I can't make it work.

The other suggested questions/answers that marked this as a duplicate do not address my question. The first one is very general (and ofcourse useful), and already addressed by the first answer. The second one doesn't take into account the variable in the SQL command as noted in my longish comment. Also, the answer is the only complete answer I have found and very different from any other answer I have come across (yet).

Solution

  • 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