3

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...

2
  • SELECT .. INTO OUTFILE do not return records - nothing to UNION.
    – Akina
    Commented Mar 13, 2020 at 4:54
  • The first select is just to name the columns in the CSV file. These queries work fine in MariaDB 10.0, just not 10.4. Commented Mar 13, 2020 at 19:18

1 Answer 1

2

Use

SELECT *
FROM ( SELECT 'Name','Address1','Address2', 'City', 'State', 'Zip', 'Phone', 
              'SSN', 'Credit Card', 'County', 'Comment', 'Company'
       UNION ALL       
       SELECT name, address1, address2, city, state, zip, phone, 
              ssn, credit_card, county, comment, company 
       FROM testcust  
       WHERE type = '0'         
     ) subquery
ORDER BY name = 'Name' DESC, name
INTO OUTFILE '/tmp/sellers.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ',' 
LINES TERMINATED BY '\r\n'
);
1
  • At first, I got another syntax error but it was different: "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 ')' at line 15" and then just saw that it was an extra one, probably just left over from my version. I took that out and the syntax errors were gone but then I got user permissions error so I made sure the user had all the GRANTS correctly and it worked beautifully! And on both 10.0 and 10.4! Thank you very much for this answer, Akina! Commented Mar 14, 2020 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.