INTO OUTFILE ERROR 2

esgaroth32

New Member
Credits
0
Good day, I am trying to run the following command but it does not seem to work.
Code:
[[email protected] ]# mysql -pPassword asteriskcdrdb -s -b -e "select 'Account ID','Destination','Operator','Provider','Date','BillSec','Rate id','Cost' UNION select accountcode,dst,'PBX',route_name,date_format(calldate,'%Y/%c/%e %H:%i'),billsec as Duration,route_id,round(cost,5) from cdr where calldate > '$prev_date' and calldate < '$cur_date' and cost > 0 and accountcode='$accountcode' UNION select '','','','','','',concat('R',sum(round(cost,5))),'' from cdr where calldate > '$prev_date' and calldate < '$cur_date' and cost > 0 and accountcode='$accountcode' INTO OUTFILE '/tmp/VoipBilling-$date/CDR/$accountcode-$date.csv' FIELDS TERMINATED BY ',';"
Error is: ERROR 1 (HY000) at line 1: Can't create/write to file '/tmp/VoipBilling-/CDR/-.csv' (Errcode: 2)

Note the filepath in the error code and the filepath specified differs. Almost as if Mariadb does not know to create the folder with the date i.e /tmp/VoipBilling-2019-06-22/CDR/BILLTEST-2019-06-22.csv''
Instead it wants to write it as '/tmp/VoipBilling-/CDR/-.csv'
When I run the same command but change the output path to not use the $date it works. i.e
Code:
[[email protected] ]# mysql -pPassword asteriskcdrdb -s -b -e "select 'Account ID','Destination','Operator','Provider','Date','BillSec','Rate id','Cost' UNION select accountcode,dst,'PBX',route_name,date_format(calldate,'%Y/%c/%e %H:%i'),billsec as Duration,route_id,round(cost,5) from cdr where calldate > '$prev_date' and calldate < '$cur_date' and cost > 0 and accountcode='$accountcode' UNION select '','','','','','',concat('R',sum(round(cost,5))),'' from cdr where calldate > '$prev_date' and calldate < '$cur_date' and cost > 0 and accountcode='$accountcode' INTO OUTFILE '/tmp/VoipBilling/CDR/accountcode.csv' FIELDS TERMINATED BY ',';"
So it is safe to say this is not a folder permision but the command not being able to create the folder with $date specified.
Please assist
 


JulienCC

Active Member
Credits
0
Hello,

First you should put you SQL in a .sql file and not use 2km long single line commands.

Here is your 'properly' formmated code :
SQL:
SELECT 
    'Account ID','Destination','Operator','Provider','Date','BillSec',
    'Rate id','Cost'
UNION
SELECT
    accountcode,dst,'PBX',route_name,
    date_format(calldate,'%Y/%c/%e %H:%i'),
    billsec as Duration,route_id,round(cost,5)
FROM cdr
WHERE
    calldate > '$prev_date'
    and calldate < '$cur_date'
    and cost > 0
    and accountcode='$accountcode'
UNION
SELECT
    '','','','','','',
    concat('R',sum(round(cost,5))),
    ''
FROM cdr
WHERE
    calldate > '$prev_date'
    and calldate < '$cur_date'
    and cost > 0
    and accountcode='$accountcode'
INTO OUTFILE '/tmp/VoipBilling-$date/CDR/$accountcode-$date.csv' FIELDS TERMINATED BY ',';
That being said, you are using some undefined shell variables prev_date, date and cur_date. So ofcourse they will all be replaced by empty values unless you give us more context about how this script is called.

There's also a high chance that mysql won't create to missing folder. Since you are using some shell variables you could add this before the actual command :
Code:
# mkdir -p '/tmp/VoipBilling-$date/CDR/'
If you need a one liner
Code:
# mkdir -p '/tmp/VoipBilling-$date/CDR/' && mysql -p... PUT YOU ACTUAL COMMAND HERE
 


Members online


Top