mysql error 'NULL' at line 1

Question

I got this error when tried to execute this:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

Can't seems to find what is the problem. Appreciate if anyone can help

SET @sql = NULL;

SELECT
  GROUP_CONCAT(
    DISTINCT CONCAT (
      "SUM(IF(DATE(FROM_UNIXTIME(machine_stop)) = '",
      DATE(FROM_UNIXTIME(machine_stop)),"' ,
      (machine_start-machine_stop)/3600, 0)) AS ",
      DATE(FROM_UNIXTIME(machine_stop))
    )
  ) INTO @sql
FROM
  downtime_data
WHERE
  DATE(FROM_UNIXTIME(machine_stop)) >= DATE(NOW()) - INTERVAL 7 DAY;

SET @sql = CONCAT("SELECT
                     failure_code, ", @sql, " 
                   FROM
                     downtime_data 
                   WHERE
                     p.machine='HH1' AND
                     DATE(FROM_UNIXTIME(machine_stop)) >= DATE(NOW()) - INTERVAL 7 DAY 
                   GROUP BY
                     failure_code,
                     DATE(FROM_UNIXTIME(machine_stop))"
                 );

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Same problem#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'is NULL' at line 1 — Wahsei, Jun 09 '16 at 13:06
Print the `@sql` variable after the first select statement. There's a possibility if the `@sql` variable is still `NULL` after the first select statement then you will get an exception while executing `stmt`. — 1000111, Jun 09 '16 at 13:12
Or try to change in the main query like this: `SET @sql = CONCAT("SELECT failure_code, ", IFNULL(@sql,''), ...." ` — 1000111, Jun 09 '16 at 13:13
Remove the `SET @SQL = NULL` entirely. There's no path through the rest of the code where @SQL will not be assigned a new value. If you must initialize it to clear old content, simply set it to an empty string. — Ken White, Jun 09 '16 at 13:17

1000111 · Accepted Answer · 2016-06-09T18:09:56.540

7

If by any chance the @sql variable still holds NULL value after the first select statement then you are going to encounter an exception later on while executing the prepare statement.

Look at the following select statement using CONCAT

SET @sql := NULL; SELECT CONCAT('abc',@sql,'def');

The result is NULL. Although you might expect the result to be abcdef.

In order to get abcdef you need to do this

SET @sql := NULL; SELECT CONCAT('abc',IFNULL(@sql,''),'def');

You may try any of the following if it resolves the issue:

Either

1) SET @sql := '';

OR

2) If you want to keep this line SET @sql = NULL; then change the portion of the final query like this SET @sql = CONCAT("SELECT failure_code ", IF(@sql IS NULL, '',CONCAT(',',@sql)),

Here's the final query:

SET @sql = CONCAT("SELECT
                     failure_code ", IF(@sql IS NULL, '',CONCAT(',',@sql)), " 
                   FROM
                     downtime_data 
                   WHERE
                     p.machine='HH1' AND
                     DATE(FROM_UNIXTIME(machine_stop)) >= DATE(NOW()) - INTERVAL 7 DAY 
                   GROUP BY
                     failure_code,
                     DATE(FROM_UNIXTIME(machine_stop))"
                 );

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

edited Jun 09 '16 at 18:09

answered Jun 09 '16 at 13:19

1000111

13,169
2
28
37

I think the error came from this PREPARE stmt FROM @sql; MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1 – Wahsei Jun 09 '16 at 13:28
1

Execute this lines and you will get the same. `SET @sql := NULL; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;`. That's what I've pointed out in my answer. By the way, have you tried any of the change mentioned in my answer? – 1000111 Jun 09 '16 at 13:32
`SET @\`sql\` := CONCAT('SELECT failure_code', IF(@\`sql\` IS NOT NULL, CONCAT(', ', @\`sql\`), ''))` – wchiquito Jun 09 '16 at 13:35
That's why I told you to use `IFNULL`. Probably I failed to make you understand. – 1000111 Jun 09 '16 at 13:45
`SELECT failure_code, FROM downtime_data != SELECT failure_code FROM downtime_data` – wchiquito Jun 09 '16 at 13:46
Thanks @wchiquito. Got the important part out of sight. Honestly I didn't notice it's you commented there. I thought the PO. Sorry mate! – 1000111 Jun 09 '16 at 13:53
@Wahsei Please check the query now. I've made a correction there. – 1000111 Jun 09 '16 at 13:54
Remember to remove the comma (`,`) after the column name `failure code`. – wchiquito Jun 09 '16 at 13:58
The comma (`,`) is removed now @Wahsei. Thanks again @wchiquito ! – 1000111 Jun 09 '16 at 14:03

score 0 · Answer 2 · edited Jul 20 '16 at 02:09

0

I got it, there are no row from the query under this condition

DATE(FROM_UNIXTIME(machine_stop)) >= DATE(NOW()) - INTERVAL 7 DAY

So it returned NULL

edited Jul 20 '16 at 02:09

Pang

9,564
146
81
122

answered Jun 09 '16 at 13:35

Wahsei

289
2
6
16

score 0 · Answer 3 · answered Aug 17 '23 at 16:33

I was facing this issue but let me share with you unique solution which I found out, I was working on 2 stored procedures simultaneously, on a new session if I run 1st sp I face this error but when I run 1st sp after running 2nd sp then the error is gone and I get my result.

It is something to do with mysql session variables which was getting set in 2nd sp and working for 1st one as well.

mysql error 'NULL' at line 1

3 Answers3