Cannot execute quick repair generated script

💬 General Discussion
1

Hi

I installed Kreports last week and then tried to install AlineaSol reports as well so I could compare. However the Alineasol reports installed failed. I uninstalled the product and tried to do a quick repair. The repair runs fine and then asks me to execute the following script:

/* Table : asol_reports /
/ MISSING TABLE: asol_reports /
CREATE TABLE asol_reports (id varchar(36) NOT NULL ,name nvarchar(255) NULL ,date_entered datetime NULL ,date_modified datetime NULL ,modified_user_id varchar(36) NULL ,created_by varchar(36) NULL ,description nvarchar(max) NULL ,deleted bit DEFAULT ‘0’ NULL ,assigned_user_id varchar(36) NULL ,last_run datetime NULL ,report_module nvarchar(255) NULL ,report_scope nvarchar(max) NULL ,report_fields mediumtext NULL ,report_filters mediumtext NULL ,report_charts_detail mediumtext NULL ,report_type nvarchar(max) NULL ,report_scheduled_type nvarchar(max) NULL ,report_attachment_format nvarchar(8) NULL ,report_tasks nvarchar(255) NULL ,report_charts nvarchar(4) NULL ,report_charts_engine nvarchar(8) NULL ,email_list nvarchar(max) NULL ,scheduled_images tinyint NULL ,dynamic_tables tinyint DEFAULT ‘0’ NULL ,dynamic_sql nvarchar(255) NULL ,audited_report tinyint DEFAULT ‘0’ NULL ,row_index_display tinyint DEFAULT ‘0’ NULL ,results_limit nvarchar(255) DEFAULT ‘all’ NULL ,alternative_database nvarchar(255) DEFAULT ‘-1’ NULL ,is_meta tinyint DEFAULT ‘0’ NULL ,meta_html mediumtext NULL );
/ Table : am_tasktemplates */
/COLUMNS/
/*MISMATCH WITH DATABASE - milestone_flag - ROW [name] => ‘milestone_flag’ [type] => ‘bit’ [len] => ‘1’ [default] => ‘0’ /
/ VARDEF - milestone_flag - ROW[required] => ‘’ [name] => ‘milestone_flag’ [vname] => ‘LBL_MILESTONE_FLAG’ [type] => ‘bit’ [massupdate] => ‘0’ [default] => ‘0’ [no_default] => ‘’ [comments] => ‘’ [help] => ‘’ [importable] => ‘true’ [duplicate_merge] => ‘disabled’ [duplicate_merge_dom_value] => ‘0’ [audited] => ‘’ [reportable] => ‘1’ [unified_search] => ‘’ [merge_filter] => ‘disabled’ [len] => ‘255’ [size] => ‘20’ [dbType] => ‘bool’ /
ALTER TABLE am_tasktemplates DROP CONSTRAINT DF__am_taskte__miles__01F34141 ALTER TABLE am_tasktemplates ALTER COLUMN milestone_flag bit NULL ;
ALTER TABLE am_tasktemplates ADD DEFAULT ‘0’ FOR milestone_flag ;
/ INDEXES */

This results in:

Database failure. Please refer to sugarcrm.log for details.

Which in turn shows:

11/02/15 16:04:05 [2780][23245648-bd2e-cc97-3e24-559a63a2255e][FATAL] Executing repair query: Query Failed:CREATE TABLE asol_reports (id varchar(36) NOT NULL ,name nvarchar(255) NULL ,date_entered datetime NULL ,date_modified datetime NULL ,modified_user_id varchar(36) NULL ,created_by varchar(36) NULL ,description nvarchar(max) NULL ,deleted bit DEFAULT ‘0’ NULL ,assigned_user_id varchar(36) NULL ,last_run datetime NULL ,report_module nvarchar(255) NULL ,report_scope nvarchar(max) NULL ,report_fields mediumtext NULL ,report_filters mediumtext NULL ,report_charts_detail mediumtext NULL ,report_type nvarchar(max) NULL ,report_scheduled_type nvarchar(max) NULL ,report_attachment_format nvarchar(8) NULL ,report_tasks nvarchar(255) NULL ,report_charts nvarchar(4) NULL ,report_charts_engine nvarchar(8) NULL ,email_list nvarchar(max) NULL ,scheduled_images tinyint NULL ,dynamic_tables tinyint DEFAULT ‘0’ NULL ,dynamic_sql nvarchar(255) NULL ,audited_report tinyint DEFAULT ‘0’ NULL ,row_index_display tinyint DEFAULT ‘0’ NULL ,results_limit nvarchar(255) DEFAULT N’all’ NULL ,alternative_database nvarchar(255) DEFAULT ‘-1’ NULL ,is_meta tinyint DEFAULT ‘0’ NULL ,meta_html mediumtext NULL )::: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Column, parameter, or variable #13: Cannot find data type mediumtext.

I’m not a database person at all. Can anyone give me a little guidance as I cannot now upgrade to v7.4 as it also throws the “Database failure. Please refer to sugarcrm.log for details” error.

Any help appreciated.

Thanks

Roger

2

It looks like you are using SQL SERVER for your installation. If this is the case, there is no “mediumtext” data type. You will need to alter that query and change the “mediumtext” to just “text”.

3

Thanks so much. That has worked and reports that vardefs are synced. However, if I re-run repair it throws the same script everytime (script below) is this expected or is something still wrong?

/* Table : asol_reports */
/COLUMNS/
/*MISMATCH WITH DATABASE - report_fields - ROW [name] => ‘report_fields’ [type] => ‘text’ /
/ VARDEF - report_fields - ROW[name] => ‘report_fields’ [vname] => ‘LBL_REPORT_FIELDS’ [type] => ‘mediumtext’ [comment] => ‘Fields of the report’ [dbType] => ‘mediumtext’ [len] => ‘’ [required] => ‘’ */
ALTER TABLE asol_reports ALTER COLUMN report_fields mediumtext NULL ;
;
/*MISMATCH WITH DATABASE - report_filters - ROW [name] => ‘report_filters’ [type] => ‘text’ /
/ VARDEF - report_filters - ROW[name] => ‘report_filters’ [vname] => ‘LBL_REPORT_FILTERS’ [type] => ‘mediumtext’ [comment] => ‘Filters of the report’ [dbType] => ‘mediumtext’ [len] => ‘’ [required] => ‘’ */
ALTER TABLE asol_reports ALTER COLUMN report_filters mediumtext NULL ;
;
/*MISMATCH WITH DATABASE - report_charts_detail - ROW [name] => ‘report_charts_detail’ [type] => ‘text’ /
/ VARDEF - report_charts_detail - ROW[name] => ‘report_charts_detail’ [vname] => ‘LBL_REPORT_CHARTS_DETAIL’ [type] => ‘mediumtext’ [comment] => ‘Charts of the report’ [dbType] => ‘mediumtext’ [len] => ‘’ [required] => ‘’ */
ALTER TABLE asol_reports ALTER COLUMN report_charts_detail mediumtext NULL ;
;
/*MISMATCH WITH DATABASE - meta_html - ROW [name] => ‘meta_html’ [type] => ‘text’ /
/ VARDEF - meta_html - ROW[name] => ‘meta_html’ [vname] => ‘LBL_REPORT_IS_META’ [type] => ‘mediumtext’ [comment] => ‘Meta report defined html’ [dbType] => ‘mediumtext’ [len] => ‘’ [required] => ‘’ /
ALTER TABLE asol_reports ALTER COLUMN meta_html mediumtext NULL ;
;
/ INDEXES /
/ Table : am_tasktemplates */
/COLUMNS/
/*MISMATCH WITH DATABASE - milestone_flag - ROW [name] => ‘milestone_flag’ [type] => ‘bit’ [len] => ‘1’ [default] => ‘0’ /
/ VARDEF - milestone_flag - ROW[required] => ‘’ [name] => ‘milestone_flag’ [vname] => ‘LBL_MILESTONE_FLAG’ [type] => ‘bit’ [massupdate] => ‘0’ [default] => ‘0’ [no_default] => ‘’ [comments] => ‘’ [help] => ‘’ [importable] => ‘true’ [duplicate_merge] => ‘disabled’ [duplicate_merge_dom_value] => ‘0’ [audited] => ‘’ [reportable] => ‘1’ [unified_search] => ‘’ [merge_filter] => ‘disabled’ [len] => ‘255’ [size] => ‘20’ [dbType] => ‘bool’ /
ALTER TABLE am_tasktemplates DROP CONSTRAINT DF__am_taskte__miles__0F4D3C5F ALTER TABLE am_tasktemplates ALTER COLUMN milestone_flag bit NULL ;
ALTER TABLE am_tasktemplates ADD DEFAULT ‘0’ FOR milestone_flag ;
/ INDEXES */