Scientific Linux release 6.5

The syntax error in the quoted statement is obvious:
...WHERE DATE(time_end) < CURRENT_DATE; */

There should be no semicolon after CURRENT_DATE.
The question is how it appeared there.

You mentioned in the 'Affects Version' field that it's 5.5.40.
I tried to dump an identical event and got an identical dump, but without the semicolon – that is, a valid one.

Which MariaDB package are you using?
Could you please make sure that mysqldump is from the same package/version – run mysqldump --version and see if it says 'Distrib 5.5.40-MariaDB' or alike?

Hi Elena,

Thanks for looking into this.

My MariaDB installation is from RHEL 6 build RPM:

[kaisong ~]$ rpm -qa|grep Maria

MariaDB-client-5.5.40-1.el6.x86_64

MariaDB-compat-5.5.40-1.el6.x86_64

MariaDB-server-5.5.40-1.el6.x86_64

MariaDB-common-5.5.40-1.el6.x86_64

MariaDB-shared-5.5.40-1.el6.x86_64

The output of mysqldump --version output:

[kaisong ~]$ mysqldump --version

mysqldump  Ver 10.14 Distrib 5.5.40-MariaDB, for Linux (x86_64)

I defined the event as follow:

SET GLOBAL event_scheduler = ON;

DROP EVENT IF EXISTS expireToken;

CREATE EVENT expireToken

ON SCHEDULE EVERY 1 DAY

DO

    DELETE FROM Token WHERE DATE(time_end) < CURRENT_DATE;

So, could it be the semicolon causing the issue? I tried to remove the semicolon, but I am not able to to import this code anymore after I did that:

[kaisong sql_scripts]$ mysql -u root -p ares_db < STORED_PROCEDURES.sql 

Enter password: 

ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DROP PROCEDURE IF EXISTS organizations;

CREATE PROCEDURE organizations() BEGIN

	' at line 7

Do you know what I might have been missing?

Thanks,

Kai

Your syntax for CREATE EVENT seems fine to me.
I assume you made sure that the dump you are using wasn't tampered with, or maybe modified unintentionally (you know how it happens sometimes, we open a file and press a button by mistake, etc).?
If you run the same mysqldump command again, are you again getting identical dump with the semicolon after CURRENT_DATE?

Hi Elena,

Sure. It's always a good idea to go back and check the most basic things. I did try to dump from fresh every time I tested it. Here, I did it again as follow:

[kaisong@staging ~]$ mysqldump --routines --triggers --events --single-transaction -u root -p --databases my_db > ./db_dump.sql

Enter password: 

[kaisong@staging ~]$ grep EVENT db_dump.sql 

/*!50106 DROP EVENT IF EXISTS `expireToken` */;

/*!50106 CREATE*/ /*!50117 DEFINER=`root`@`localhost`*/ /*!50106 EVENT `expireToken` ON SCHEDULE EVERY 1 DAY STARTS '2014-10-20 13:07:03' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM Token WHERE DATE(time_end) < CURRENT_DATE; */ ;;

Then I dropped and created the my_db in MariaDB.

[kaisong@staging ~]$ mysql -u root -p my_db < db_dump.sql 

Enter password: 

ERROR 1064 (42000) at line 2108: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '*/' at line 1

Thanks,

Kai

Hi Kai,

Does the dump you created (and re-created) also say in the header something like below?

-- MySQL dump 10.14  Distrib 5.5.40-MariaDB, for Linux (x86_64)

--

-- Host: localhost    Database: ares_db

-- ------------------------------------------------------

-- Server version       5.5.40-MariaDB

What's your default character sets and locale? (I have no idea yet if it can be related, just covering the basics)

As I understand, you only have one event in your dump, so we can't be sure if it's specific to the event, to mysqldump, or to the server. Could you please make an experiment – create an event in a separate empty schema on the same server, dump this schema and check if the problem is persistent, regardless the schema and the contents of the event?

Hi Elena,

Yes, the dump head looks exactly like that:

[kaisong@staging ~]$ head staging_db_backup.sql -n 6

-- MySQL dump 10.14  Distrib 5.5.40-MariaDB, for Linux (x86_64)

--

-- Host: localhost    Database: ares_db

-- ------------------------------------------------------

-- Server version	5.5.40-MariaDB

How to check the default character sets and locale? I tried to dump the database with --default-character-set=utf8", but still getting the same result. I will try to see if I can recreate this on a test database.

Thanks so much for your help!

Kai

Hi Kai,

Could you please also run show create event expireToken \G on the server from which you are taking the dump, and see whether it shows the trailing semicolon in the 'Create Event' field?

Hi Elena,

Yes, it does have the semicolon in the 'Create Event' field:

MariaDB [ares_db]> show create event expireToken \G

*************************** 1. row ***************************

               Event: expireToken

            sql_mode: 

           time_zone: SYSTEM

        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `expireToken` ON SCHEDULE EVERY 1 DAY STARTS '2014-08-08 14:47:34' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM Token WHERE DATE(time_end) < CURRENT_DATE;

character_set_client: latin1

collation_connection: latin1_swedish_ci

  Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

MariaDB [ares_db]> 

By the way, I tested with just a table and a event, and the database dump file works just fine.

Thanks,

Kai

Okay, thanks, so now we know it's not mysqldump's fault, it just takes whatever it finds in show create event.
We need to find out how the semicolon gets there.

You mentioned that you create objects, including the event in question, from STORED_PROCEDURES.sql file.
Can you provide the contents of the file, e.g. attach it to the issue?
If you don't want to make it public, you can upload it to our ftp.askmonty.org/private, this way only MariaDB developers will have access to it.
If it's confidential and you can't provide it even privately, it's okay too – please remove or obfuscate whatever sensitive data there is, basically I just want to see the sequence of SET, DELIMITER, CREATE ... statements and such. And semicolons of course.

Also, please attach your my.cnf file(s) or output of SHOW VARIABLES from the server.

Thanks.

Hi Elena,

I found the problem while trying to prepare the sql script for you!

The DELIMITER is set to be "//", but I didn't use it for the EVENT.

Thanks so much for your help, and please feel free to remove this bug report!

Kai

Okay, as long as the fix made the problem go away.

I'm still mildly curious about how exactly it looked at the file, because even with the missing delimiter it's not so easy to achieve. But I suppose it's possible.

Hi Elena,

I managed to extract the problematic code in a schema.sql file and attached it to this issue.

Take any database server without a database name "test_db", you can import the schema.sql file and generate a dump file, db_dump.sql:

[kaisong ~]$ mysql -u root -p test_db < schema.sql 

Enter password: 

[kaisong ~]$ mysqldump --routines --triggers --events --single-transaction -u root -p --databases test_db > ./db_dump.sql

Enter password: 

Then, we can see the extra semicolon in the dump file:

[kaisong ~]$ grep EVENT db_dump.sql 

/*!50106 DROP EVENT IF EXISTS `fooEvent` */;

/*!50106 CREATE*/ /*!50117 DEFINER=`root`@`localhost`*/ /*!50106 EVENT `fooEvent` ON SCHEDULE EVERY 1 DAY STARTS '2014-10-21 08:28:26' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM Token WHERE DATE(time_end) < CURRENT_DATE; */ ;;

Notice, in the schema.sql file line 22, if I replace the semicolon with "//" delimiter, the problem should be fixed.

Hope this helps with debugging the issue.

Thanks,

Kai

Thanks, it's interesting.
So here is how one can actually do it:

DROP EVENT IF EXISTS fooEvent;

DELIMITER //

CREATE EVENT fooEvent

ON SCHEDULE EVERY 1 DAY

DO

    DELETE FROM Token WHERE DATE(time_end) < CURRENT_DATE;

SELECT 1 //

DELIMITER ;

It's the same in MySQL, too, probably has always been. Not sure that upstream will want to fix it, but at least next time we'll know.

Still, if you want, you can try to file a bug at bugs.mysql.com and see what they say.

After some contemplation, re-opening because it still feels like a bug, albeit minor – a problem with parsing multi-statement queries?
In the example above, if the server considers SELECT being a part of the event body, there should probably be a syntax error; or, if there is no syntax error, SELECT should become a part of the event definition which it's not – when the query is issued, the event is created, while the SELECT gets executed, no errors. So, the server is able to split them properly; but then, it shouldn't put the semicolon in the event body.

Similar to https://bugs.mysql.com/bug.php?id=77856 ?

Hi serg,
can you please review patch for 10.2

Same approach as MDEV-24200, whatever it turns out to be

danblack, please, I want to review it

Hi serg can you please review bb-10.2-anel-MDEV-6899-show-event-mysqldump-v2?

This still looks like a hack. Why the parser even puts a semicolon into the sp_proc_stmt ?

Hi,
when debuged noted that trigger_tail is invoked which is calling sp_proc_stmt.
However, I don't see the rule sp_proc_stmt using ";", but sp_proc_stmts and sp_proc_stmts1:

sp_proc_stmt:

          sp_proc_stmt_in_returns_clause

        | sp_proc_stmt_statement

        | sp_proc_stmt_leave

        | sp_proc_stmt_iterate

        | sp_proc_stmt_open

        | sp_proc_stmt_fetch

        | sp_proc_stmt_close

        ;

sp_proc_stmts:

          /* Empty */ {}

        | sp_proc_stmts  sp_proc_stmt ';'

        ;

sp_proc_stmts1:

          sp_proc_stmt ';' {}

        | sp_proc_stmts1  sp_proc_stmt ';'

        ;

sp_proc_stmts is used in

• sp_block_content which is used in sp_labeled_block

sp_proc_stmts1 is used in

• sp_if
• sp_elseifs
• simple_when_clause
• searched_when_clause
• else_clause_opt
• loop_body
• while_body
• repeat_body

None of above rules are used in trigger_tail (used in test case).
Above changes introduced with commit from 2004 e324c0cc8407bcbde70d4386ddf26c78ca6443b2fafb254d49da77ec3e0ff3be

Hi serg, holyfoot,
related to the above question:
parser puts the semicolon because it (;) is pre-processed in the lex (MY_LEX_SEMICOLON -> MY_LEX_CHAR and token (not 0) is returned).
In the grammar it acts as a lookahead token (so yyChar != YYEMPTY will be called for generating the instruction for the sp. It doesn't have any effect to change anything there.
In the next iteration single ; is processed as no_lookahead token and after that rest of the statements.
Rawbuffer for multistatements is for example in this form $34 = 0x7fffd8013780 "CREATE EVENT td.foo1\nON SCHEDULE EVERY 1 DAY\nDO\nDELETE FROM Token WHERE DATE(time_end) < CURRENT_DATE;\nSELECT 1
ev_sql_stmt: will set the body of sp to the pre-processed lex m_cpp_ptr via get_cpp_ptr() and will look like \nDELETE FROM Token WHERE DATE(time_end) < CURRENT_DATE;.
I have tried multiple things and found related commits, but couldn't find a way to remove ; from m_cpp_ptr as this is the only thing what could be done before grammar.
In grammar only thing that can be done is via set_stmt_end() and that function is also relaying on the pre-processed statement and there is already a patch to act based on the last character ; in the pre-processed stream.
However it is still not clear should the parsing error be raised or ; be removed from m_body that is created by function set_stmt_end()?!
Since there is some work already done by holyfoot in MDEV-6421, hope I will get some review comments, thoughts and hints.

I've fixed the parser to not include the semicolon into the event definition

The patch
https://github.com/MariaDB/server/commit/7e5a3ed83f709cba67385b9faf45d1a4f1686b08
is OK to push.