Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Description
If a trigger contains a comment in its description, it can interleave with executable comments which mysqldump adds, resulting in syntactically incorrect statement and unloadable dump.
create table t (a int); |
create trigger /* here */ tr after insert on t for each row set @a=1; |
|
|
--exec $MYSQL_DUMP test > $MYSQL_TMP_DIR/test.dump
|
--exec $MYSQL test < $MYSQL_TMP_DIR/test.dump
|
|
|
# Cleanup
|
drop table t; |
|
10.3 0ca3aaa7 |
ERROR 1064 (42000) at line 47: 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
|
mysqltest: At line 5: exec of '/mnt8t/bld/10.3-nightly/bin/mysql --defaults-file=/mnt8t/bld/10.3-nightly/mysql-test/var/my.cnf test < /mnt8t/bld/10.3-nightly/mysql-test/var/tmp/test.dump' failed, error: 256, status: 1, errno: 11
|
The line 47 which it complains about it
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger /* here */ tr after insert on t for each row set @a=1 */;; |
Note the comment /* here */ nested inside the executable comment.
Attachments
Activity
Seems OK in 10.11.
/!50003 CREATE/ /!50017 DEFINER=`ray`@`localhost`/ /*!50003 trigger tr after insert on t for each row set @a=1 */;;
DELIMITER ;
/* here */ is not in dump file.
I am probably missing something here..
Maybe it's an issue with the mariadb client as opposed to the dump client. The client is supposed to rip out comments, no? I don't understand where the /* here */ comment is stored in the db.
MariaDB [test]> show triggers from test WHERE `table` = 't' \G |
*************************** 1. row *************************** |
Trigger: tr
|
Event: INSERT
|
Table: t
|
Statement: set @a=1 |
Timing: AFTER
|
Created: 2022-12-24 20:37:59.16 |
sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
Definer: ray@localhost |
character_set_client: utf8mb3
|
collation_connection: utf8mb3_general_ci
|
Database Collation: latin1_swedish_ci
|
1 row in set (0.002 sec) |
|
It has nothing to do with the client.
The client can strip comments, but it can keep them as well.
And the client is not the only way to deliver a query to the server.
Pardon my ignorance. Still learning my way around mariadb. Is there an option in client to enable keeping comments?
Are comments for triggers stored somewhere in the db? I guess it has to be. How else will it be able to dump out of it and into sql.. Do you happen to know which table stores that info?
If I'd better rtfm, please point me to the right direction 
Thanks.
It looks like it is:
$ mariadb --help|grep comment
|
-c, --comments Preserve comments. Send comments to the server. The
|
default is --skip-comments (discard comments), enable
|
with --comments.
|
comments FALSE
|
Thanks Sergei! With -c option, I was able to replicate the same issue on version 11.
/*!50003 CREATE*/ /*!50017 DEFINER=`ray`@`localhost`*/ /*!50003 trigger /* here */ tr after insert on t for each row set |
@a=1 */;; |
|
|
ERROR 1064 (42000) at line 47: 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 |
Elena, I think now I understand what you were saying – to get rid of the part about /*!50003 so that there won't be any nested comments in the dump file.
Is it only triggers who can have a comment inside and included in version excluded comment? (I am not sure 100% about SP and functions). if so I am for Elena proposal to stop doing it for 5.0 version.
Probably the most obvious solution in this case would be to stop adding 5.0-executable limitations, we don't really expect that the dumps would be (otherwise) loadable into 4.x MySQL installations.