Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.11, 11.4, 11.8
-
None
-
MariaDB dump 10.19 Distrib 10.6.21-MariaDB, for debian-linux-gnu (x86_64)
Description
When you create a stored procedure, where the last line ends with a comment, mariadb-dump will just add ";;" after this comment.
When you execute the following statement in a JDBC-query
CREATE PROCEDURE usp_ebean_drop_column(IN p_table_name VARCHAR(255), IN p_column_name VARCHAR(255)) |
-- play-ebean-start
|
BEGIN
|
CALL usp_ebean_drop_foreign_keys(p_table_name, p_column_name);
|
SET @sql = CONCAT('ALTER TABLE `', p_table_name, '` DROP COLUMN `', p_column_name, '`'); |
PREPARE stmt FROM @sql; |
EXECUTE stmt; |
END
|
-- play-ebean-end |
a backup will get corrupt, because mariadb-dump will create the following output in the dump file:
DELIMITER ;;
|
CREATE DEFINER=`root`@`%` PROCEDURE `usp_ebean_drop_column`(IN p_table_name VARCHAR(255), IN p_column_name VARCHAR(255)) |
BEGIN
|
CALL usp_ebean_drop_foreign_keys(p_table_name, p_column_name);
|
SET @sql = CONCAT('ALTER TABLE `', p_table_name, '` DROP COLUMN `', p_column_name, '`'); |
PREPARE stmt FROM @sql; |
EXECUTE stmt; |
END
|
-- play-ebean-end ;;
|
DELIMITER ;
|
Note the --play-ebean-end ;; line. mariadb-dump just adds the ;;
The following command/options were used: mysqldump --hex-blob --routines --single-transaction --skip-extended-insert --complete-insert --no-autocommit -p testdb
We could repoduce this in our java application or just by pasting this query in Dbeeaver. Adding a newline after the comment seems not to help (seems that jdbc-driver trims the query)
We could not reproduce it with the mariadb CLI client
You may also take a look at this issue https://github.com/ebean-orm/ebean/issues/3577 where we discovered the bug and the workaround.
Workaround: don't let your procedures end with a comment.
Thanks for the report.
--delimiter $BEGINEND-- comment-end$--delimiter ;--exec $MYSQL_DUMP -R test > $MYSQL_TMP_DIR/1.dump--cat_file $MYSQL_TMP_DIR/1.dump--exec $MYSQL test < $MYSQL_TMP_DIR/1.dump11.8 6f1161aa34cbb178b00fc24cbc46e2e0e2af767a
ERROR 1064 (42000) at line 32: 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 'DELIMITER ;/*!50003 SET sql_mode = @saved_sql_mode */ ;/*!50003...' at line 6