INTO OUTPUT Error 2
Good day, I am trying to run the following command but it does not seem to work.
[root@ ]# 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 [root@ ]# 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