MySQL query unknown error

Question

I'm trying to run this query on MySQL server:

CREATE  PROCEDURE forum.eventlog_create(
i_UserID      INT,
i_Source      VARCHAR(128),
i_Description TEXT,
i_Type        INT,
i_UTCTIMESTAMP DATETIME)
MODIFIES SQL DATA
BEGIN

INSERT INTO forum.EventLog
(UserID, Source, Description, ´Type´)
VALUES (i_UserID, i_Source, i_Description, i_Type);

END;

However upon executing it I get the following error:

Error Code: 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 '' at line 12

and I'm unable to fix it. I tried to search for a solution and asked a co-worker but we were unable to find the solution, as last resort I decided to ask it here.

I get error code 1064 but the right syntax near '' is the message and I dont understand what the problem could be. It would be easier if it said which syntax gives the error, I only get the line number. Thank you for your time

Possible duplicate of [How can I fix MySQL error #1064?](https://stackoverflow.com/questions/23515347/how-can-i-fix-mysql-error-1064) — Daniel W., Jan 23 '18 at 14:45
What MySQL client are you using to create this? It looks like you may need an alternate delimiter because the `INSERT` is terminated with `;` but the procedure's `END` is as well. https://stackoverflow.com/questions/10259504/delimiters-in-mysql — Michael Berkowski, Jan 23 '18 at 14:46
The error is not unkown, the error has the number `1064`. What is your MySQL version? — Daniel W., Jan 23 '18 at 14:47
Does MySQL accept value inputs without string quotes like that? Seems bad. — Jacob H, Jan 23 '18 at 14:48
You seem to have forward-ticks rather than back-ticks as your field delimiter. — Andy G, Jan 23 '18 at 14:48
@DanFromGermany sorry, you are right that it has the number, im using mysql 5.7.20 and mysql workbench 6.3.10 — Matthias Hoste, Jan 23 '18 at 14:48
@AndyG backward ticks, no ticks. Tried it but didnt make a difference — Matthias Hoste, Jan 23 '18 at 14:49
As I said, your strings aren't delimited. Don't know why everyone else is focused on the other stuff. Your error even implies that it expects the string delimiters. Or did you mean to make that a `SELECT`? — Jacob H, Jan 23 '18 at 14:58
@JacobH No it doesn't. It says that the error is near an empty string. Those quotes are the delimiters around the text that the error is near, they're not the actual text. — Barmar, Jan 23 '18 at 15:00
@Barmar so if the issue isn't with the non-delimited strings being passed to VALUES, what's the problem? — Jacob H, Jan 23 '18 at 15:00
@JacobH There are no non-delimited strings. Those are the parameters to the function. The problem is probably the delimiter, as others have commented. — Barmar, Jan 23 '18 at 15:02
I see, sorry I never worked with procedures and other delimeters before. But thank you for your help — Matthias Hoste, Jan 23 '18 at 15:29

score 3 · Accepted Answer · answered Jan 23 '18 at 15:14

There is one error caused by the escape character around type which should be either backticks or dropped and you should try setting delimiters https://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html

delimiter $$

CREATE  PROCEDURE eventlog_create(
i_UserID      INT,
i_Source      VARCHAR(128),
i_Description TEXT,
i_Type        INT,
i_UTCTIMESTAMP DATETIME)
MODIFIES SQL DATA
BEGIN

INSERT INTO forum.EventLog
(UserID, Source, Description, `Type`)
VALUES (i_UserID, i_Source, i_Description, i_Type);

END $$

delimiter ;

Shubham Dixit · Answer 2 · 2018-01-23T15:23:40.377

Try this

DELIMITER $$
CREATE PROCEDURE forumeventlog_create() 
BEGIN 
declare i_UserID INT DEFAULT 0; 
declare i_Source VARCHAR(128) DEFAULT null; 
declare i_Description TEXT DEFAULT null; 
declare i_Type INT DEFAULT 0; 
declare i_UTCTIMESTAMP DATETIME DEFAULT null ; 

INSERT INTO forum.EventLog
(UserID, Source, Description, ´Type´)
VALUES (i_UserID, i_Source, i_Description, i_Type);

END $$
DELIMITER ;

You can call this by

CALL forumeventlog_create()

OR

 DELIMITER $$
   CREATE PROCEDURE forumeventlog_create(i_UserID INT,i_Source VARCHAR(128),i_Description TEXT,i_Type INT,i_UTCTIMESTAMP DATETIME) 
            BEGIN 
            INSERT INTO forum.EventLog 
    (UserID, Source, Description, ´Type´) 
        VALUES (i_UserID, i_Source, i_Description, i_Type); 

            END $$
            DELIMITER ;

MySQL query unknown error

2 Answers2