phpMyAdmin can't find syntax for Create EVENT

Question

-mysql 5.6.2 -GLOBAL event_scheduler = ON

Using phpMyAdmin client on MYSQL database. I'm not setting a Delimiter, as I know you can't in this statement. If I remove the last ';', it fails with 'error near END.' In below format, fails with:

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 64

#Begin statement

CREATE EVENT airshipmentsnotinlong
ON SCHEDULE every 1 HOUR
ON COMPLETION PRESERVE
DO
    BEGIN
        INSERT into WORKORDERS
            (
            id
            ,client_id 
            ,method
            ,carrier_id 
            ,carrier 
            ,username 
            ,password
            ,blnumber 
            ,containernumber 
            ,bookingnum 
            ,adddate 
            ,moddate 
            ,isdone
            )

        SELECT 
            DISTINCT 'null' as ID
            ,cs.customer_id as client_id
            ,'justin' as method
            ,cs.carrier_id
            ,c.scac
            ,'' as user
            ,'' as pass
            ,cs.blnumber
            ,cs.container
            ,'' as book
            ,now() as adate
            ,now() as modate
            ,'0' as done
        FROM CUSTOMERSHIPMENTS CS

        LEFT JOIN 
            SHIPMENTS S
                ON 
                cs.container = s.containernumber
                and cs.blnumber = s.blnumber

        LEFT JOIN 
            CARRIERS C
                ON 
                cs.carrier_id = c.id
        WHERE 
            cs.hostcompany_id = cs.company_id
            and cs.container like '.air%'
            and cs.isactive = 1
            and cs.hostcompany_id = company_id 
            and cs.carrier_id in (176,180,222,224,226,227,228,261,271,292,297)
            and cs.date > NOW() - INTERVAL 3 MONTH
            and cs.blnumber <> ''
            #and s.status = ''
            and cs.blnumber not in
                (
                SELECT
                    blnumber
                FROM
                    workorder_log
                WHERE
                    cdate > now()-interval 75 minute
                )
                ;
    END

That particular message usually means a misplaced bracket ( '(' or ')' ) — Strawberry, Dec 23 '14 at 23:29

O. Jones · Answer 1 · 2014-12-24T01:09:06.147

1

Your understanding to the contrary notwithstanding, you need to set the delimiter. Do this.

DELIMITER $$

CREATE EVENT airshipmentsnotinlong
ON SCHEDULE every 1 HOUR
ON COMPLETION PRESERVE
DO
    BEGIN
        ...your event's INSERT statement here including the closing semicolon ...
    END $$

DELIMITER ;

In PHPMyAdmin, instead of wrapping your definition in DELIMITER $$ / DELIMITER ; you set the delimiter to something besides ; in the box right below the query. You then terminate your definition with that same delimiter, as I have shown in END$$.

The error message you're getting is protesting the missing END, which MySQL doesn't see because it comes after the closing delimiter.

edited Dec 24 '14 at 01:09

answered Dec 24 '14 at 00:04

O. Jones

103,626
17
118
172

Your understanding is to the contrary. I tried saving you the time from answering what didn't need to be answered. You Can't set a delimiter in an SQL statement in phpMyAdmin consol. I've already tried, and you get an error at 'DELIMITER. You set it in a text box below the SQL box. I do that, and still get the error. – user3242558 Dec 24 '14 at 00:12
http://stackoverflow.com/questions/16801550/creating-functions-in-phpmyadmin-error-access-denied-you-need-the-super-privi/16802061#16802061 – user3242558 Dec 24 '14 at 00:14
See my edit. This works for me. If you're on a hosting service you may have an old version of phpMyAdmin, or the service may not allow events on their server. – O. Jones Dec 24 '14 at 01:10

phpMyAdmin can't find syntax for Create EVENT

1 Answers1