Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24200

Syntax error in mysqldump (triggers with ; endings)

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.3.25
    • N/A
    • Backup, Triggers
    • None

    Description

      Hi,

      When dumping a database with triggers for a customer I got the current output:

      DELIMITER ;;
      		 
      /*!50003 CREATE*/ /*!50017 DEFINER=`mydb`@`%` */ /*!50003 TRIGGER trigger_LabelsUsers_insert_before BEFORE INSERT ON LabelsUsers FOR EACH ROW SET NEW.LabelsUsersID2 = MD5(CONCAT(NOW(),RAND())); */;;
      		 
      DELIMITER ;

      When restoring or running it manually I get:

      ERROR 1064 (42000): 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

      If I remove the last ; so the query looks like this:

      /*!50003 CREATE*/ /*!50017 DEFINER=`mydb`@`%` */ /*!50003 TRIGGER trigger_LabelsUsers_insert_before BEFORE INSERT ON LabelsUsers FOR EACH ROW SET NEW.LabelsUsersID2 = MD5(CONCAT(NOW(),RAND())) */;;

      then it works.

      Attachments

        Issue Links

          is part of

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6899 extra semicolon in show create event syntax

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6899 extra semicolon in show create event syntax

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 11 older comments
            danblack Daniel Black added a comment -

            If we can get rid of the /*!500003 comments, then the problem can be solved purely in mysqldump like:

            dump based solution

            		 
            --- a/client/mysqldump.c
            		 
            +++ b/client/mysqldump.c
            		 
            @@ -3508,8 +3508,8 @@ static int dump_trigger(FILE *sql_file, MYSQL_RES *show_create_trigger_rs,
            		 
                                                 C_STRING_WITH_LEN("50003"),
            		 
                                                 C_STRING_WITH_LEN(" TRIGGER"));
            		 
                 fprintf(sql_file,
            		 
            -            "DELIMITER ;;\n"
            		 
            -            "/*!50003 %s */;;\n"
            		 
            +            "DELIMITER +;\n"
            		 
            +            " %s+;\n"
            		 
                         "DELIMITER ;\n",
            		 
                         (const char *) (query_str != NULL ? query_str : row[2]));

            I've changed the delimiter here so that a trigger ending in ;, when it is getting restored in the dump isn't confused by a ;;; . The principle here of dump/restore being the same. I'm also hoping something here restores exactly the same trigger that was there previously so checksums, if any are applicable, match.

            A follow up task on the parser for 10.6 to warn and drop from the definition trailing ;+ in the parser. And a mysql_upgrade patch to remove the existing trailing unnecessary syntax.

            Second follow up task to remove any < 5.5 comments from mysqldump?

            danblack Daniel Black added a comment - If we can get rid of the /*!500003 comments, then the problem can be solved purely in mysqldump like: dump based solution --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -3508,8 +3508,8 @@ static int dump_trigger(FILE *sql_file, MYSQL_RES *show_create_trigger_rs, C_STRING_WITH_LEN("50003"), C_STRING_WITH_LEN(" TRIGGER")); fprintf(sql_file, - "DELIMITER ;;\n" - "/*!50003 %s */;;\n" + "DELIMITER +;\n" + " %s+;\n" "DELIMITER ;\n", (const char *) (query_str != NULL ? query_str : row[2])); I've changed the delimiter here so that a trigger ending in ; , when it is getting restored in the dump isn't confused by a ;;; . The principle here of dump/restore being the same. I'm also hoping something here restores exactly the same trigger that was there previously so checksums, if any are applicable, match. A follow up task on the parser for 10.6 to warn and drop from the definition trailing ;+ in the parser. And a mysql_upgrade patch to remove the existing trailing unnecessary syntax. Second follow up task to remove any < 5.5 comments from mysqldump?
            serg Sergei Golubchik added a comment -

            danblack, please, I want to review it

            serg Sergei Golubchik added a comment - danblack , please, I want to review it
            serg Sergei Golubchik added a comment -

            see my review for MDEV-6899

            serg Sergei Golubchik added a comment - see my review for MDEV-6899
            anel Anel Husakovic added a comment -

            The patch for this MDEV can be found in MDEV-6899 patch where the test case for this MDEV is added.

            anel Anel Husakovic added a comment - The patch for this MDEV can be found in MDEV-6899 patch where the test case for this MDEV is added.
            serg Sergei Golubchik added a comment -

            Will be fixed in MDEV-6899

            serg Sergei Golubchik added a comment - Will be fixed in MDEV-6899

            People

              serg Sergei Golubchik
              Desdic Kim Gert Nielsen
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.