Breton It Breton It - 2 months ago 9
MySQL Question

ERROR 1582 (42000) Incorrect parameter count in the call to native function 'FROM_UNIXTIME'

When I try to convert the timestamp in the following query, using bash

docker exec compose_TSOwncloudMySQL_1 mysql -h localhost -udockerdev -pdocker owc -e "
SELECT DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%Y%m%d timestamp%h:%i:%s') AS 'date_formatted',
oc_ldap_user_mapping.ldap_dn,
oc_activity.subject,
oc_activity.file,
oc_activity.subjectparams
FROM oc_activity INNER JOIN oc_ldap_user_mapping ON oc_activity.user = oc_ldap_user_mapping.owncloud_name
ORDER BY oc_activity.timestamp;"> /home/dockerdmz/tsowncloud/log_owc/owc_$DATE.log`


I have this error:

ERROR 1582 (42000) at line 2: Incorrect parameter count in the call to native function 'FROM_UNIXTIME'


When I run this query in MySQL admin page, it works well.

SELECT DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%Y%m%d %h:%i:%s') AS 'date_formatted',
oc_ldap_user_mapping.ldap_dn,
oc_activity.subject,
oc_activity.file,
oc_activity.subjectparams
FROM oc_activity INNER JOIN oc_ldap_user_mapping ON oc_activity.user = oc_ldap_user_mapping.owncloud_name
ORDER BY oc_activity.timestamp;


When I run this bash code (without timestamp conversion), it works well

Docker exec compose_TSOwncloudMySQL_1 mysql -h localhost -udockerdev -pdocker owc -e "
SELECT oc_activity.timestamp,
oc_ldap_user_mapping.ldap_dn,
oc_activity.subject,
oc_activity.file,
oc_activity.subjectparams
FROM oc_activity INNER JOIN oc_ldap_user_mapping ON oc_activity.user = oc_ldap_user_mapping.owncloud_name
ORDER BY oc_activity.timestamp;"> /home/dockerdmz/tsowncloud/log_owc/owc_$DATE.log

Answer

Ugh. It's a bash backtick thing.

Try escaping the backticks with backslashes when you use bash, like so.

 -e " SELECT DATE_FORMAT(FROM_UNIXTIME(\`timestamp\`),   ...

Pro tip: Avoid using reserved words (such as timestamp in your case) for column or table names. That way you don't have to wrap them in backticks in your queries, and you can use the same queries in various contexts (bash, php, etc).

Comments