| Bug #102827 | beautifying SQL script | ||
|---|---|---|---|
| Submitted: | 5 Mar 2021 13:57 | Modified: | 9 Mar 2021 13:29 |
| Reporter: | Claude Renglet | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Workbench: SQL Editor | Severity: | S1 (Critical) |
| Version: | 8.0.23 | OS: | Windows (Microsoft Windows 10 Enterprise) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | WBBugReporter | ||
[5 Mar 2021 14:17]
Claude Renglet
this behaviour/bug because a character '²' (subscript 2, U+00B2, Alt 0178) is present in a column alias. Removing this character from the sql script, the beautify function is working fine.
[5 Mar 2021 20:32]
MySQL Verification Team
Thank you for the bug report. I couldn't repeat on my own. Please provide the script to test, the wb.og file and the server version you are connecting to. Thanks.
[8 Mar 2021 18:32]
Claude Renglet
Server version : 8.0.18 and 5.7.27
in a new database, create table and view
in mysql server 5.7.27, default charset is set to "latin1"
in mysql server 8.0.18, default charset is set to "utf8"
CREATE TABLE `new_table` (
`idnew_table` int(11) NOT NULL,
`volume` float DEFAULT NULL,
`weigth` float DEFAULT NULL,
`surface` float DEFAULT NULL,
PRIMARY KEY (`idnew_table`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE ALGORITHM=UNDEFINED DEFINER=`nirond`@`%` SQL SECURITY DEFINER VIEW `BugWorkBench`.`new_view` AS select `BugWorkBench`.`new_table`.`volume` AS `Volume(m³)`,`BugWorkBench`.`new_table`.`weigth` AS `Weigth(kg)`,`BugWorkBench`.`new_table`.`surface` AS `Surface(m²)` from `BugWorkBench`.`new_table`;
Refresl All
right-click on the view, select Sent to SQL Editor - Create Statement, then Beautfify/reformat the SQL String
or
right-click on the view, select Alter View => Error message : Error parsing DDL for `dbname`.`new_view`
wb.log :
19:08:09 [ERR][ sqlide-history]: Can't parse <ENTRY timestamp='19:01:14'>CREATE ALGORITHM=UNDEFINED DEFINER=`xxxxxxxx`@`%` SQL SECURITY DEFINER VIEW `BugWorkBench`.`new_view` AS select `BugWorkBench`.`new_table`.`volume` AS `Volume(m³)`,`BugWorkBench`.`new_table`.`weigth` AS `Weigth(kg)`, `BugWorkBench`.`new_table`.`Surface` As `Surface(m²)` from `BugWorkBench`.`new_table`</ENTRY>, of file: C:\Users\cr\AppData\Roaming\MySQL\Workbench\sql_history\2019-02-04
19:08:09 [ERR][ XML Functions]: LibXml: Entity: line 1:
19:08:09 [ERR][ XML Functions]: LibXml: parser
19:08:09 [ERR][ XML Functions]: LibXml: error :
19:08:09 [ERR][ XML Functions]: LibXml: Entity 'sup3' not defined
19:08:09 [ERR][ XML Functions]: LibXml: nch`.`new_view` AS select `BugWorkBench`.`new_table`.`volume` AS `Volume(m³
19:08:09 [ERR][ XML Functions]: LibXml: ^
19:08:09 [ERR][ XML Functions]: LibXml: Entity: line 1:
19:08:09 [ERR][ XML Functions]: LibXml: parser
19:08:09 [ERR][ XML Functions]: LibXml: error :
19:08:09 [ERR][ XML Functions]: LibXml: Entity 'sup2' not defined
19:08:09 [ERR][ XML Functions]: LibXml: eigth` AS `Weigth(kg)`, `BugWorkBench`.`new_table`.`Surface` As `Surface(m²
19:08:09 [ERR][ XML Functions]: LibXml: ^
19:12:05 [WRN][SqlEditorSchemaTree]: Error parsing DDL for bugworkbench.new_view: CREATE
ALGORITHM = UNDEFINED
DEFINER = `xxxxxx`@`aaa.bbb.ccc.ddd`
SQL SECURITY DEFINER
VIEW `new_view` AS
SELECT
`new_table`.`volume` AS Volume(m³),
new_table.weigth AS Weigth(kg),
new_table.surface AS Surface(m²)
FROM
new_table
19:12:21 [ERR][SQL Editor Form]: Unable to create db_mgmt_SSHConnectionRef object
19:12:21 [ERR][SQL Editor Form]: Unable to create db_mgmt_SSHConnectionRef object
19:12:22 [WRN][ mforms]: Resource file not found: mysql-logo-80.png
19:12:23 [WRN][ mforms]: Resource file not found: mysql-logo-80.png
19:12:38 [ERR][SQL Editor Form]: Unable to create db_mgmt_SSHConnectionRef object
19:12:38 [ERR][SQL Editor Form]: Unable to create db_mgmt_SSHConnectionRef object
19:17:51 [WRN][SQL Editor Form]: 0 row(s) affected, 1 warning(s):
1681 Integer display width is deprecated and will be removed in a future release.
19:19:00 [WRN][SqlEditorSchemaTree]: Error parsing DDL for bugworkbench.new_view: CREATE
ALGORITHM = UNDEFINED
DEFINER = `xxxxxx`@`aaa.bbb.ccc.ddd`
SQL SECURITY DEFINER
VIEW `new_view` AS
SELECT
`new_table`.`volume` AS Volume(m³),
new_table.weigth AS Weigth(kg),
new_table.surface AS Surface(m²)
FROM
new_table
19:22:08 [WRN][SqlEditorSchemaTree]: Error parsing DDL for bugworkbench.new_view: CREATE
ALGORITHM = UNDEFINED
DEFINER = `xxxxxx`@`aaa.bbb.ccc.ddd`
SQL SECURITY DEFINER
VIEW `new_view` AS
SELECT
`new_table`.`volume` AS Volume(m³),
new_table.weigth AS Weigth(kg),
new_table.surface AS Surface(m²)
FROM
new_table
[9 Mar 2021 13:29]
MySQL Verification Team
Hello Claude Renglet, Thank you for the bug report. Verified as described. Regards, Ashwini Patil
Description: After using the "beautify/reformat ths SQL script", some ` surrounding element names (db, table, columns, alias) are lost. 14:31:22 [ERR][sqlide_grt.py:enbeautificate:333]: Error reformating SQL: CREATE ALGORITHM=UNDEFINED */ DEFINER=`ronny`@`%` SQL SECURITY DEFINER */ VIEW `001_02_view_equip` AS select '1' AS `INFRAorEQUIP`,concat(if((length(`tb_decl_1`.`Sernbr`) > 0),if((substr(`tb_decl_1`.`Sernbr`,1,14) = 'Sernbr=Sernbr='),substr(`tb_decl_1`.`Sernbr`,8,1000),if((substr(`tb_decl_1`.`Sernbr`,1,7) <> 'Sernbr='),concat('Sernbr=',`tb_decl_1`.`Sernbr`),`tb_decl_1`.`Sernbr`)),''),if((length(`tb_decl_1`.`Codeconquest`) > 0),concat('Codeconquest=',`tb_decl_1`.`Codeconquest`),' '),if((length(`tb_decl_1`.`Extref`) > 0),concat('Extref=',`tb_decl_1`.`Extref`),' ')) AS `Unique reference number`,`tb_decl_1`.`Id_decl_1` AS `Unique reference number DMS`,concat(`tb_declfac`.`Code`,'-',`tb_declbuld`.`Code`,'-',`tb_declloc`.`Code`) AS `Code Fac-Buld-Zone`,`tb_declsite`.`Code` AS `Code Site`,`tb_declfac`.`Code` AS `Code Facility`,`tb_declbuld`.`Code` AS `Code Building`,`tb_decl_1`.`Id_declloc` AS `ID Zone`,`tb_declloc`.`Code` AS `Code Zone-Room`,if((`tb_declloc`.`Sra` = 1),'yes','no') AS `sra Zone-Room`,`tb_declloc`.`Description` AS `Zone Description`,`tb_declstrmain`.`Code` AS `Structure Code`,`tb_declstrmain`.`Description` AS `Structure Description`,`rel1_fi`.`Id_declfin` AS `ID Financial Status`,`tb_declfin`.`Code` AS `Financial Status, Code`,`rel1_fi`.`Date_begin` AS `Financial Status, Date Start`,`rel1_st`.`Id_declstatus` AS `ID Status Decommissioning`,`tb_declstatus`.`Code` AS `Code Status Decommissioning`,`tb_declstatus`.`Description` AS `Status Decommissioning`,`rel1_st`.`Date` AS `Status Decommissioning, Date Start`,`tb_decl_1`.`Description` AS `Description Object`,if((length(`tb_decl_1`.`Remark_int`) > 0),if((substr(`tb_decl_1`.`Remark_int`,1,23) = 'Remark int: Remark int:'),substr(`tb_decl_1`.`Remark_int`,12,1000),if((substr(`tb_decl_1`.`Remark_int`,1,7) <> 'Remark int:'),concat('Remark int:',`tb_decl_1`.`Remark_int`),`tb_decl_1`.`Remark_int`)),'') AS `Remark`,`tb_declmat`.`Id_declmat` AS `ID Material Surface`,`tb_declmat`.`Code` AS `Code Material Surface`,`tb_declmat`.`Description` AS `Material Surface`,`tb_declmat_1`.`Id_declmat` AS `ID Material Base`,`tb_declmat_1`.`Code` AS `Code Material Base`,`tb_declmat_1`.`Description` AS `Material Base`,`tb_decl_1`.`Surface` AS `Surface (m2)`,`tb_decl_1`.`Volume` AS `Volume (m3)`,`tb_decl_1`.`Weight` AS `Mass (kg)`,`tb_decl_1`.`Id_declradtype` AS `ID RadType`,`tb_declradtype`.`Code` AS `Code RadType`,`tb_declradtype`.`Description` AS `RadType`,`tb_declinttype`.`Id_declinttype` AS `ID IntType`,`tb_declinttype`.`Code` AS `Code IntType`,`tb_declinttype`.`Description` AS `IntType`,`tb_decl_1`.`Id_declacoeff` AS `Code Access Coeffient`,`tb_declacoeff`.`Coeff_value` AS `Value Access Coeffient`,`tb_declacoeff`.`Description` AS `Acces_coeff`,`tb_decl_1`.`Contam_surface` AS `Contaminated Surface (m2)`,`tb_decl_1`.`Contam_depth` AS `Contaminated Depth (mm)`,`tb_decl_1`.`Contam_weight` AS `Contaminated Mass (kg)`,`tb_decl_1`.`Activ_depth` AS `Activated Depth (mm)`,`tb_decl_1`.`Activ_volume` AS `Activated Volume (m3)`,`tb_decl_1`.`Activ_weight` AS `Activated Mass (kg)`,`tb_decl_1`.`Avg_doserate` AS `Dose Rate (mSv/h)`,`tb_decl_1`.`Contam_aact` AS `Surface Contamination Alpha (Bq/cm²), Average`,`tb_decl_1`.`Contam_bgact` AS `Surface Contamination Beta-Gamma (Bq/cm²), Average`,`tb_decl_1`.`Activ_activity` AS `Specific Activation (Bq/g), Average`,`tb_decltech`.`Id_decltech` AS `ID Technique`,`tb_decltech`.`Code` AS `Code Technique`,`tb_decltech`.`Title` AS `Title Technique`,`tb_decltech`.`Description` AS `Title`,`relqt1_nom`.`Surface` AS `Surface Treated (m2)`,`relqt1_nom`.`Depth` AS `Depth Treated (mm)`,`relqt1_nom`.`Weighttreated` AS `Weight treated (kg)`,`relqt1_nom`.`Weightwaste` AS `Weight Waste (kg)`,`tb_decltdnc`.`Id_decltdnc` AS `ID Primary Waste`,`tb_decltdnc`.`Code` AS `Primary Waste Code (XYZ-A0)`,`tb_decltdnc`.`Description` AS `Description Primary Waste (XYZ-A0)`,cast('' as char(50) charset utf8) AS `Concrete Access`,cast('' as char(50) charset utf8) AS `Concrete Type`,'' AS `Average Wall thickness (m)`,`relqt1_nom`.`Id_declgrpnom` AS `Id_declgrpnom`,`tb_declgrpnom`.`Code` AS `NFM-group`,`relqt1_nom`.`Id_Relqt1_nom` AS `Id_Relqtx_nom`,'' AS `Code Concrete Access`,'' AS `Code Concrete Type`,if(isnull(`tb_decl_1`.`Volume`),0,(`tb_declmat_1`.`SpecificMass` * `tb_decl_1`.`Volume`)) AS `WeightBasedVolume` from ((((((((((((((((((((`000_table_site_facility_building_zone` join `tb_decl_1`) join `tb_declloc` on((`tb_decl_1`.`Id_declloc` = `tb_declloc`.`Id_declloc`))) join `tb_declbuld` on((`tb_declloc`.`Id_declbuld` = `tb_declbuld`.`Id_declbuld`))) join `tb_declfac` on((`tb_declbuld`.`Id_declfac` = `tb_declfac`.`Id_declfac`))) join `tb_declsite` on((`tb_declfac`.`Id_declsite` = `tb_declsite`.`Id_declsite`))) join `tb_declradtype` on((`tb_decl_1`.`Id_declradtype` = `tb_declradtype`.`Id_declradtype`))) join `tb_declacoeff` on((`tb_decl_1`.`Id_declacoeff` = `tb_declacoeff`.`Id_declacoeff`))) join `tb_declinttype` on((`tb_decl_1`.`Id_declinttype` = `tb_declinttype`.`Id_declinttype`))) join `tb_declmat` on((`tb_decl_1`.`Id_declmat_surf` = `tb_declmat`.`Id_declmat`))) join `tb_declstr1` on((`tb_decl_1`.`Id_declstr1` = `tb_declstr1`.`Id_declstr1`))) join `tb_declstrmain` on((`tb_declstr1`.`Id_declstrmain` = `tb_declstrmain`.`Id_declstrmain`))) join `tb_declmat` `tb_declmat_1` on((`tb_decl_1`.`Id_declmat_main` = `tb_declmat_1`.`Id_declmat`))) left join `rel1_fi` on((`rel1_fi`.`Id_decl_1` = `tb_decl_1`.`Id_decl_1`))) left join `tb_declfin` on((`rel1_fi`.`Id_declfin` = `tb_declfin`.`Id_declfin`))) join `rel1_st` on((`rel1_st`.`Id_decl_1` = `tb_decl_1`.`Id_decl_1`))) join `tb_declstatus` on((`rel1_st`.`Id_declstatus` = `tb_declstatus`.`Id_declstatus`))) left join `relqt1_nom` on((`relqt1_nom`.`Id_decl_1` = `tb_decl_1`.`Id_decl_1`))) left join `tb_decltech` on((`relqt1_nom`.`Id_decltech` = `tb_decltech`.`Id_decltech`))) left join `tb_decltdnc` on((`relqt1_nom`.`Id_decltdnc` = `tb_decltdnc`.`Id_decltdnc`))) left join `tb_declgrpnom` on(((`tb_declgrpnom`.`Id_declsite` = `tb_declsite`.`Id_declsite`) and (`relqt1_nom`.`Id_declgrpnom` = `tb_declgrpnom`.`Id_declgrpnom`)))) where ((`tb_declsite`.`Code` = `000_table_site_facility_building_zone`.`Site`) and (`tb_declfac`.`Code` like concat(`000_table_site_facility_building_zone`.`Facility`,'%')) and (`tb_declbuld`.`Code` like concat(`000_table_site_facility_building_zone`.`Building`,'%')) and (`tb_declloc`.`Code` like concat(`000_table_site_facility_building_zone`.`Zone`,'%')) and (`tb_declfin`.`Code` like concat(`000_table_site_facility_building_zone`.`FinancialResp`,'%'))) Traceback (most recent call last): File "C:\Program Files\MySQL\MySQL Workbench 8.0 CE\modules\sqlide_grt.py", line 330, in enbeautificate result = doReformatSQLStatement(statement, True) File "C:\Program Files\MySQL\MySQL Workbench 8.0 CE\modules\sqlide_grt.py", line 233, in doReformatSQLStatement raise Exception("Error parsing statement: %s" % ast_list[0]) Exception: Error parsing statement: syntax error, unexpected '*', expecting VIEW_SYM How to repeat: not sure, but with a SQL script on one line, taken from a mysqldump output file, which has more than 3000 characters (see in the extract of the workbench log). copy paste that line into Workbench editor and click on the bouton"beautify/reformat ths SQL script" from the toolbar