MySQL Bugs: #77197: Valid stored procedure code labelled _SYNTAX

Bug #77197	Valid stored procedure code labelled _SYNTAX_ERROR in Routine Group
Submitted:	30 May 2015 21:25	Modified:	9 Dec 2015 17:38
Reporter:	Jack Almeida	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	6.3.3	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Workbench Routine Group _SYNTAX_ERROR

Description:
When adding stored procedures to a Routine Group, the Workbench appends _SYNTAX_ERROR to the procedure for some valid SQL statements. Can be successfully forward-engineered to the database, and executes correctly. I have my workbench preferences->modeling->MySQL set for version 5.6, matching the running 5.6 database. Here are 2 snippets that the workbench labels _SYNTAX_ERROR, which compile and run fine on the database server:

1) DIAGNOSTICS
------------------
CREATE DEFINER='test_usr'@'127.0.0.1' PROCEDURE test.event_log(
	IN p_log_level VARCHAR(8)
	,IN p_caller VARCHAR(64)
	,IN p_checkpoint SMALLINT(6)
	,IN p_message_custom VARCHAR(255)
)
COMMENT 'Shared event log.'
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
	DECLARE l_error_code INTEGER;
	DECLARE l_sql_state CHAR(5);
	DECLARE l_message TEXT;

	-- WORKBENCH REPORTS 'GET' AS AN ERROR
	GET CURRENT DIAGNOSTICS CONDITION 1
		l_error_code=MYSQL_ERRNO,
		l_sql_state=RETURNED_SQLSTATE,
		l_message=MESSAGE_TEXT;

	INSERT INTO test.event_log( time_captured, user, log_level, caller, error_code, sql_state, message, checkpoint, message_custom )
	VALUES( NOW(), CURRENT_USER(), IFNULL(p_log_level,'NONE'), IFNULL(p_caller,'UNKNOWN'), IFNULL(l_error_code,-1), IFNULL(l_sql_state,'00000'), l_message, p_checkpoint, p_message_custom );

	COMMIT;
END$$

2) Also happens with DEFAULT( COLUMN )
--------------------
...
	INSERT INTO test.user(
		id
		,first_name
		,last_name
		,middle_initial
		,date_of_birth
		,email
		,date_confirmed
		,disabled
	)
	VALUES(
		NULL
		,NULLIF( TRIM( p_first_name ), '' )
		,NULLIF( TRIM( p_last_name ), '' )
		,UPPER( NULLIF( TRIM( p_middle_initial ), '' ) )
		-- WORKBENCH REPORTS DEFAULT( column ) AS AN ERROR
		-- ,IFNULL( p_date_of_birth, DEFAULT( date_of_birth ) )
		-- WORKBENCH ACCEPTS HARDCODED DEFAULT
		,IFNULL( p_date_of_birth, '0000-00-00 00:00:00' )
		,test.FORMAT_EMAIL( p_email )
		,NULL
		,DEFAULT
	);
...

How to repeat:
1. CREATE ER DIAGRAM

2. ADD TABLE:
DROP TABLE IF EXISTS `test`.`event_log`;

CREATE TABLE IF NOT EXISTS `test`.`event_log` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `time_captured` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user` VARCHAR(128) NOT NULL DEFAULT 'UNKNOWN',
  `log_level` VARCHAR(8) NOT NULL DEFAULT 'INFO',
  `caller` VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN',
  `error_code` INT(11) NOT NULL DEFAULT -1,
  `sql_state` CHAR(5) NOT NULL DEFAULT '00000',
  `message` TEXT NULL,
  `checkpoint` SMALLINT(6) NULL,
  `message_custom` TEXT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

3. ADD ROUTINE GROUP AND ROUTINE
CREATE DEFINER='test_usr'@'127.0.0.1' PROCEDURE test.event_log(
	IN p_log_level VARCHAR(8)
	,IN p_caller VARCHAR(64)
	,IN p_checkpoint SMALLINT(6)
	,IN p_message_custom VARCHAR(255)
)
COMMENT 'Shared event log.'
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
	DECLARE l_error_code INTEGER;
	DECLARE l_sql_state CHAR(5);
	DECLARE l_message TEXT;

	-- WORKBENCH REPORTS 'GET' AS AN ERROR
	GET CURRENT DIAGNOSTICS CONDITION 1
		l_error_code=MYSQL_ERRNO,
		l_sql_state=RETURNED_SQLSTATE,
		l_message=MESSAGE_TEXT;

	INSERT INTO test.event_log( time_captured, user, log_level, caller, error_code, sql_state, message, checkpoint, message_custom )
	VALUES( NOW(), CURRENT_USER(), IFNULL(p_log_level,'NONE'), IFNULL(p_caller,'UNKNOWN'), IFNULL(l_error_code,-1), IFNULL(l_sql_state,'00000'), l_message, p_checkpoint, p_message_custom );

	COMMIT;
END$$

Hello Jack Almeida,

Thank you for the report.

Thanks,
Umesh

What version is set for your model. GET [CURRENT] DIAGNOSTICS has been introduced in MySQL 5.6.4. For earlier versions WB will report a syntax error. Currently it only shows that for CURRENT, but should in fact do it for GET, but that's a different problem which has been fixed already.

The version can be set in Model -> Model Options which by default inherit the values from the WB preferences, but you can override them for each model individually, if needed.

Hi,

When I viewed the Model Options->MySQL display, it listed version 5.1 but was greyed-out because the "Use defaults from global settings" checkbox was checked. I unchecked the "Use defaults from global settings" checkbox and changed the "Target MySQL Version" to 5.6 and clicked "OK". After a restart of the Workbench, the routines still show up as having _SYNTAX_ERROR. I also noticed some new problems appearing since changing the "Target MySQL Version" parameter. The "Apply Changes" button for the "SQL Scripts" doesn't enable when you make a change to the script—there isn't a way to save script changes.

- Is this a problem because the original model was created with a previous version of the workbench?
- Is the file now corrupted?

If so, how can I update it to use the newer workbench?

Thank you,
Jack

You don't need to restart WB when changing the options. They will immediately take effect include syntax error check. But you are right, I also saw a different problem there where a procedure with an error was just named _SYNTAX_ERROR, without name part. We will fix this.

The Apply/Cancel button problem is known and should be solved with the next release.

Hi,

I rebuilt the model starting with a new mwb file in the newest version of the windows workbench 6.3.3, running on windows 7 pro. Things are better; however, I found that if there is an error in one of your routine_groups, it adds an entry into the Routines area of the model overview labelled <routine_group>_SYNTAX_ERROR<N>. After the error is corrected, the label remains. A new <routine_group>_SYNTAX_ERROR<N> is added every time the model is saved, so you end up with a list of duplicate <routine_group>_SYNTAX_ERROR<N> entries. The work around is to manually delete them from the Routines area of the model overview after fixing the routine_group code. This is the same problem I had with the original file with one exception: in the older file, when I attempted to manually delete the <routine_group>_SYNTAX_ERROR<N> entry from the model overview, it would delete the next valid routine, leaving the <routine_group>_SYNTAX_ERROR<N> entry.

This probably should go into a second report, but since upgrading to version 6.3.3, I can't update scripts under "SQL Scripts" in the model overview using the "Apply Changes" and "Discard Changes" buttons, they seem permanently disabled. I create a new script, add some sql, save it, then modify it—neither "Apply Changes" nor "Discard Changes" ever gets enabled. The work around is to resave it and replace the existing file.

Thank you.

The apply/cancel button problem will be fixed in the next release that is appearing soon. The syntax_error problem needs more time and a fix will not be available anytime soon.

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 6.3.6 release, and here's the changelog entry:

When adding stored procedures to a Routine Group, Workbench would
sometimes append "_SYNTAX_ERROR" to the procedures.

Thank you for the bug report.