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

[30 Apr 2020 11:45] Lawrence Cooke
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.
[23 Jun 2020 11:56] MySQL Verification Team
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
[24 Jun 2020 22:10] Lawrence Cooke
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.
[26 Jun 2020 12:40] MySQL Verification Team
Hello Lawrence Cooke,

Thank you for the details.
Verified as described.

Regards,
Ashwini Patil