MySQL Bugs: #99411: Workbench does not honor sync sql

Bug #99411	Workbench does not honor sync sql_mode in sync with any source
Submitted:	30 Apr 2020 11:45	Modified:	26 Jun 2020 12:40
Reporter:	Lawrence Cooke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	8.0.20	OS:	MacOS (10.15.4)
Assigned to:		CPU Architecture:	Any

Description:
When syncing a model using the menu option "sync model" , the sql_mode used in the script is set to what is written in the settings for "sql mode for sync"

When syncing using "sync with any source"  the SQL mode for the script that is generated is set to "'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

This is neither the default set in the settings nor is it the sql_mode of the server. 

How to repeat:
Use the sync with any source option in the  model  and view the sql_mode being set by the script that is generated. 

Suggested fix:
SQL mode should honor either the default set in the settings for syncing, or should honor what is set in the connection settings or should be set to what the server itself is set to.

Hello Lawrence Cooke,

Thank you for the bug report.
I tried to reproduce your issue on workbench 8.0.20 using dummy model but I am not seeing any issues at my end.

Regards,
Ashwini Patil

Hi,

When you sync your model using "Syncronize Model" what SQL mode does it set it to for you? 

I see "SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ALLOW_INVALID_DATES';"

When you sync your model using "Syncronize with any source" what SQL mode does it set it to for you? 

I see "SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

If I go into model options and uncheck the use defaults from global settings, click on "MySQL" and set the SQL Mode to "traditional"

If I then use the "Sync Model" option I see:
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='Traditional';

if I use the "sync with any source" then the SQL mode shows as:
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

This is the bug, when using "sync with any source" it should be reading the models SQL mode setting, and use that SQL MODE. It neither reads the global setting nor the model set setting.

Hello Lawrence Cooke,

Thank you for the details.
Verified as described.

Regards,
Ashwini Patil