I have an SQL query that executes perfectly on MariaDB 10.0.35 but fails with a syntax error on MariaDB 10.4.12. I attempted to contact them, but they told me to check forums and compare the changelogs from 10.0 to 10.4. I don't really have the time to scour through dozens of changelog files trying to figure out what is different, especially since their documentation appears to be compatible with what I am doing. Here's the query:
(SELECT 'Name','Address1','Address2', 'City', 'State', 'Zip', 'Phone', 'SSN', 'Credit Card', 'County', 'Comment', 'Company')
UNION (
SELECT name, address1, address2, city, state, zip, phone, ssn, credit_card, county, comment, company
FROM testcust
WHERE type = '0'
ORDER BY name
INTO OUTFILE '/tmp/sellers.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
LINES TERMINATED BY '\r\n'
);
And I even tried re-arranging it to put the INTO OUTFILE clause before the FROM (as it is in the example):
(SELECT 'Name','Address1','Address2', 'City', 'State', 'Zip', 'Phone', 'SSN', 'Credit Card', 'County', 'Comment', 'Company')
UNION (
SELECT name, address1, address2, city, state, zip, phone, ssn, credit_card, county, comment, company
INTO OUTFILE '/tmp/allcustomers.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
LINES TERMINATED BY '\r\n'
FROM testcust
ORDER BY name
);
but that didn't matter. (Both versions work on 10.0, by the way.)
And here is the error from MariaDB 10.4.12:
ERROR 1064 (42000): 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 '/tmp/allcustomers.csv'
And here is MariaDB's Doc for "INTO OUTFILE":
MariaDB Doc page
where they have the following example:
SELECT customer_id, firstname, surname INTO OUTFILE '/exportdata/customers.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM customers;
I have disabled secure_file_priv in /etc/my.cnf.d/secure_file_priv.cnf:
[server]
# secure_file_priv = /var/lib/mysql-files
secure_file_priv = ""
I cannot find a way to attach an SQL file for anyone that wants to import it to test this with the above query, but would be glad to send it upon request (unless I am just missing how to attach a file).
I am at a complete loss on this, and cannot seem to find different versions of their documentation to see if there really is a syntax difference — I can only assume their online docs are for the latest version.
I would really appreciate any enlightenment anyone might be able to bestow on me…
Best Answer
Use