Author Topic: DB upgrade problems from 1.6.2.5 to 1.7.1.13 (1.6.1S7 Updating QA forms)  (Read 3335 times)

emilec

  • Newbie
  • *
  • Posts: 43
  • Karma: 4
    • View Profile
    • Email
This problem looks very similar to this topic: http://forum.queuemetrics.com/index.php?topic=861.0

When the DB test starts I get the following, which I expect:
Quote
Checking current version of table: 'agenti_noti'   

Error   Java Error: Errore DB: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'aliases' in 'field list' The required database table 'agenti_noti' is missing some fields. Are you updating from a previous version of QM?   

You then get the warning to backup your database
click next
and then wait.
show processlist on mysql cli shows the schema updates taking place.
After about 10-15min the page fails with a java time-out error and offers you the session restart button which takes you back to the dbtest page which again shows

Quote
Checking current version of table: 'agenti_noti'   

Error   Java Error: Errore DB: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'aliases' in 'field list' The required database table 'agenti_noti' is missing some fields. Are you updating from a previous version of QM?   
but this time clicking next immediately returns
Quote
The database upgrade and repair process was terminated successfully.
Below you can find the performed check details:   
Test performed   Test result   
1.6.1S7   Updating QA forms with issue information   Err   

Looking at the mysql bin logs I have:
Quote
CREATE TEMPORARY TABLE temp_513824 ( myInt INT NOT NULL, myStr CHAR(30) NOT NULL )
INSERT INTO temp_513824 ( myInt, myStr ) VALUES ( '', 'x' )
DROP TEMPORARY TABLE `temp_513824` /* generated by server */
ALTER TABLE `queue_log` CHANGE `call_id` `call_id` VARCHAR( 200 ) NOT NULL
ALTER TABLE `queue_log` CHANGE `data2` `data2` VARCHAR( 200 ) NOT NULL
ALTER TABLE `queue_log` CHANGE `data3` `data3` VARCHAR( 200 ) NOT NULL
ALTER TABLE `queue_log` CHANGE `data4` `data4` VARCHAR( 200 ) NOT NULL
UPDATE dbversion SET version_id = 21
CREATE TABLE `export_calls` (`call_id` int(11) NOT NULL auto_increment,`job_id` int(11) default NULL,`call_status` enum('L','C','X','D') default NULL,`ast_uniqueid` varchar(100) default NULL,`sys_dt_creazione` datetime default NULL,`sys_user_creazione` int(11) default '0',`sys_dt_modifica` datetime default NULL,`sys_user_modifica` int(11) default '0',`sys_optilock` int(11) default '0',`ast_queue` varchar(100) default NULL,`ast_timestart` int(10) unsigned default NULL,`ast_waittime` int(10) unsigned default NULL,`ast_talktime` int(10) unsigned default NULL,`ast_agent` varchar(100) default NULL,`ast_caller` varchar(100) default NULL,`ast_callstatus` varchar(10) default NULL,`ast_queuepos` int(10) unsigned default NULL,`ast_disconnection` varchar(100) default NULL,`ast_attempts` int(10) unsigned default NULL,`ast_stints` int(10) unsigned default NULL,`ast_server` varchar(100) default NULL,`ast_ivr` varchar(100) default NULL,`ast_dnis` varchar(100) default NULL,`job_type_code` smallint(6) NOT NULL,`job_param1` varchar(128) NOT NULL,`job_param2` varchar(128) NOT NULL,`job_param3` varchar(128) NOT NULL,PRIMARY KEY  (`call_id`)) ENGINE=MyISAM
CREATE TABLE `export_jobs` (`job_id` int(11) NOT NULL auto_increment,`job_name` varchar(100) NOT NULL,`job_status` varchar(2) NOT NULL,`security_key` varchar(50) NOT NULL,`sys_dt_creazione` datetime NOT NULL,`sys_user_creazione` int(11) NOT NULL,`sys_dt_modifica` datetime NOT NULL,`sys_user_modifica` int(11) NOT NULL,`sys_optilock` int(11) NOT NULL,`job_folder` varchar(200) NOT NULL,`job_class` varchar(100) NOT NULL,`job_params` varchar(200) NOT NULL,PRIMARY KEY  (`job_id`)) ENGINE=MyISAM
UPDATE dbversion SET version_id = 22
ALTER TABLE broadcast_msg ADD  for_agentid   INT( 11 ) NOT NULL AFTER `for_everyone`
UPDATE dbversion SET version_id = 23
CREATE TABLE `qm_tasks` ( `task_id` int(11) NOT NULL auto_increment, `to_user` int(11) NOT NULL, `to_class` int(11) NOT NULL, `task_type` int(11) NOT NULL, `task_status` tinyint(4) NOT NULL, `related_to` int(11) NOT NULL, `refers_to` int(11) NOT NULL, `message` text NOT NULL, `data1` text, `data2` text, `data3` text, `valid_from` datetime NOT NULL, `expires_on` datetime NOT NULL, `processed_on` datetime NOT NULL, `sys_dt_creazione` datetime NOT NULL, `sys_user_creazione` int(11) NOT NULL, `sys_dt_modifica` datetime NOT NULL, `sys_user_modifica` int(11) NOT NULL, `sys_optilock` int(11) NOT NULL, PRIMARY KEY  (`task_id`), KEY `unread_tasks` (`to_user`,`task_status`), KEY `tasks_class` (`to_class`,`task_status`), KEY `ext_reference` (`refers_to`,`task_type`), KEY `my_own` (`sys_user_creazione`)) ENGINE=MyISAM
UPDATE dbversion SET version_id = 24
ALTER TABLE qa_comments ADD  enabled   BOOL NOT NULL DEFAULT '1' AFTER `comment_id`
UPDATE dbversion SET version_id = 25
CREATE TABLE `qa_forms_items_attr` (`attribute_id` INT( 11 ) NOT NULL AUTO_INCREMENT ,`form_id` INT( 11 ) NOT NULL ,`item_code` VARCHAR( 5 ) NOT NULL ,`attr_type` SET( 'rule', 'weight', 'shortcut' ) NOT NULL ,`attr_value` VARCHAR( 256 ) NOT NULL ,`sys_dt_creazione` DATETIME NOT NULL ,`sys_user_creazione` INT( 11 ) NOT NULL ,`sys_dt_modifica` DATETIME NOT NULL ,`sys_user_modifica` INT( 11 ) NOT NULL ,`sys_optilock` INT( 11 ) NOT NULL ,PRIMARY KEY ( `attribute_id` )) ENGINE = MYISAM
UPDATE dbversion SET version_id = 26
ALTER TABLE qm_tasks ADD  notes   TEXT NOT NULL AFTER `data3`
UPDATE dbversion SET version_id = 27
SET @@session.pseudo_thread_id=12/*!*/;
CREATE TEMPORARY TABLE temp_572536 ( myInt INT NOT NULL, myStr CHAR(30) NOT NULL )
INSERT INTO temp_572536 ( myInt, myStr ) VALUES ( '', 'x' )
DROP TEMPORARY TABLE `temp_572536` /* generated by server */
DELIMITER ;
ROLLBACK /* added by mysqlbinlog */;

And in the general log I have:
Quote
SELECT task_id, to_user, to_class, task_type, task_status, related_to, refers_to, message, data1, data2, data3, notes, valid_from, expires_on, processed_on, sys_dt_creazione, sys_user_creazione, sys_dt_modifica, sys_user_modifica, sys_optilock, pID, pFamily FROM qm_tasks WHERE task_type = 101;
ERROR 1054 (42S22): Unknown column 'pID' in 'field list'

I then manually ran
Quote
alter table qm_tasks add column pID varchar(255) DEFAULT NULL;
alter table qm_tasks add column pFamily varchar(255) DEFAULT NULL;
alter table qm_tasks add KEY `pFamily` (`pFamily`,`pID`);

I then restarted the db test and the upgrade continued and completed successfully
Quote
1.6.1S7 Updating QA forms with issue information Ok
1.6.1S7 Adding qa_forms.seckey_queues field Ok
1.6.1S7 Adding call_status.seckey_queues field Ok
1.6.1S8 Adding export_jobs.job_type_code field Ok
1.6.1S8 Removing job_class field in the export_job table Ok
1.6.1S9 Creating agent_history table Ok
1.6.1S9 Creating QA Performance tracker rules table Ok
1.6.1S9 Creating QA Performance tracker rules table Ok
1.6.1S9 Adding pause_codes.isPayable field Ok
1.6.1S9 Creating Payroll agents notes table Ok
1.6.1S10 Adding new events to agent history table Ok
1.6.1S10 Adding qa_perftrack_rules.averaged field Ok
1.6.1S10 Remove the score target in performance tracker rule table Ok
1.7.0MG Adding qa_data.grader_type field Ok
1.7.0MG Fix Payroll Notes table (step 1) Ok
1.7.0MG Fix Payroll Notes table (step 2) Ok
1.7.0 Adding queue_log.data5 field Ok
1.7.0 Creating table ivr Ok
1.7.0 Creating table dnis Ok
1.7.0 QA: Set sectionweights default to 0 Ok
1.7.2 Adding agenti_noti.aliases field Ok
1.7.2 Creating record_tags table Ok





QueueMetrics

  • Loway
  • Hero Member
  • *
  • Posts: 2999
  • Karma: 39
    • View Profile
    • QueueMetrics
Looks fine to me!