Upgrading 3.2 to 5.6 Restore fails

[Chuck J]

Preconverted the 3.2 hf1 db. Install 5.6 and point it to the preconverted db and everything is going great until it fails and produces the SQL error: Invalid user ID or password

Any help here? Thanks in advance

 

[Wildbar]

Try going to 5.0 or 5.2 before going to 5.6

 

[Chuck J]

Tried upgrading to 5.0 and the DB Upgrade Fails and produces this unprocessed.sql :

-- Database file: C:\MICROS\Database\Data\Rebuild\MICROS.DB
-- Database CHAR collation: 1252LATIN1, NCHAR collation:
-- Connection Character Set: cp1252
--
-- CREATE DATABASE command: CREATE DATABASE 'C:\\MICROS\\Database\\Data\\Rebuild\\MICROSNew.db' LOG ON 'MICROSNew.log' CASE IGNORE ACCENT IGNORE PAGE SIZE 4096 COLLATION '1252LATIN1' DEFINITION 0x0200313235324c4154494e310000000000000000000000000000000000000000000000000000000000000000000000000000000000637031323532000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000436f6465205061676520313235322c2057696e646f7773204c6174696e20312c205765737465726e00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006e0000006e000000700000004c00000000000000000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f252d3d5a525b593a4344575c2a29323175797a7b7c7d7e7f80812c2b6061622f4f838587898b8d8f91939597999b9d9fa1a3a5a7a9abadafb1b3b5455846352634828486888a8c8e90929496989a9c9ea0a2a4a6a8aaacaeb0b2b44764483768552069533f6a6b6c6d6ea76f9f21b522233b3c3e40497172734ea6709e24b4b3742e51545056654a364c824163284d27665d7a7b33674b3839799e427677783083838383838383878b8b8b8b93939393899d9f9f9f9f9f5f9fababababb3a9a682828282828282868a8a8a8a92929292889c9e9e9e9e9e5e9eaaaaaaaab2a8b2000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f252d3d5a525b593a4344575c2a29323175797a7b7c7d7e7f80812c2b6061622f4f828486888a8c8e90929496989a9c9ea0a2a4a6a8aaacaeb0b2b4455846352634828486888a8c8e90929496989a9c9ea0a2a4a6a8aaacaeb0b2b44764483768552069533f6a6b6c6d6ea66f9e21b422233b3c3e40497172734ea6709e24b4b2742e51545056654a364c824163284d27665d7a7b33674b3839799e427677783082828282828282868a8a8a8a92929292889c9e9e9e9e9e5f9eaaaaaaaab2a8a682828282828282868a8a8a8a92929292889c9e9e9e9e9e5e9eaaaaaaaab2a8b2000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f202122232425262728292a2b2c2d2e2f303132333435363738393a3b3c3d3e3f406162636465666768696a6b6c6d6e6f707172737475767778797a5b5c5d5e5f606162636465666768696a6b6c6d6e6f707172737475767778797a7b7c7d7e7f808182838485868788899a8b9c8d9e8f909192939495969798999a9b9c9d9effa0a1a2a3a4a5a6a7a8a9aaabacadaeafb0b1b2b3b4b5b6b7b8b9babbbcbdbebfe0e1e2e3e4e5e6e7e8e9eaebecedeeeff0f1f2f3f4f5f6d7f8f9fafbfcfdfedfe0e1e2e3e4e5e6e7e8e9eaebecedeeeff0f1f2f3f4f5f6f7f8f9fafbfcfdfeff000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f202122232425262728292a2b2c2d2e2f303132333435363738393a3b3c3d3e3f404142434445464748494a4b4c4d4e4f505152535455565758595a5b5c5d5e5f604142434445464748494a4b4c4d4e4f505152535455565758595a7b7c7d7e7f808182838485868788898a8b8c8d8e8f909192939495969798998a9b8c9d8e9fa0a1a2a3a4a5a6a7a8a9aaabacadaeafb0b1b2b3b4b5b6b7b8b9babbbcbdbebfc0c1c2c3c4c5c6c7c8c9cacbcccdcecfd0d1d2d3d4d5d6d7d8d9dadbdcdddedfc0c1c2c3c4c5c6c7c8c9cacbcccdcecfd0d1d2d3d4d5d6f7d8d9dadbdcddde9f00000000000000000080808080800000000000000000000000000000000000008000000000000000000000000000000040404040404040404040000000000000002020202020202020202020202020202020202020202020202020000000000000202020202020202020202020202020202020202020202020202000000000000000000000000000000020002000200000000000000000000000200020002020000000000000000000002000000000000000000000000000000020000000000020202020202020202020202020202020202020202020200020202020202020202020202020202020202020202020202020202020202020002020202020202020 ENCRYPTED ON KEY '?' ALGORITHM 'aes' BLANK PADDING ON JCONNECT ON CHECKSUM OFF
--
--
-- The database reload failed with the following error:
-- ***** SQL error: Syntax error near 'dba' on line 24
--
-- This script contains the statements that were not executed as a
-- result of the failure. The statement that caused the failure is
-- commented out below. To complete the reload, correct the failing
-- statement, remove the surrounding comments and execute this script.
/*
create procedure
dba.spem_alter_domain(inout p_action char(1),inout p_domain varchar(200),inout p_parameters varchar(200) default
null,inout p_old_domain varchar(200) default
null)
begin
declare err_NOTFOUND exception for sqlstate value '02000';
declare @base_domain char(200);
declare @domain char(200);
declare @column_name char(200);
declare @table_name char(200);
declare @dereference char(200);
declare @sql varchar(2000);
declare @max_id integer;
declare @cur_id integer;
declare @domain_comp char(200);
declare @PId integer;
declare @LastPid integer;
declare local temporary table dba.dmn_temp(
type_name char(200) null,
base_domain char(200) null,
width integer null,
scale integer null,
dereference char(200) null,
) on commit preserve rows;
declare local temporary table dba.dmn_temp_dtl(
p_id integer not null default
autoincrement,
type_name char(200) null,
table_name char(200) null,
column_name char(900) null,
pk char(1) null,
) on commit preserve rows;
declare local temporary table dba.view_temp(
p_id integer not null default
autoincrement,
table_name char(200) null,
view_def long varchar null,
) on commit preserve rows;
message 'Created all variables' type info to console;
//===================================================================================================
// ADDING THE DOMAIN
if p_action = 'A' then
// CREATE THE NEW DOAMIN
set @sql='CREATE DOMAIN "' || p_domain || '" ' || p_parameters || ';';
message @sql type info to console;
execute immediate @sql
end if; // ENDED ADD IF STATEMENT
//===================================================================================================
// Depending on the action - assigns the proper domain to be dropped
if(p_action = 'R') then
set @domain=p_old_domain
else
set @domain=p_domain
end if;
message 'Domain to be dropped: ' || @domain type info to console;
// Gets the passed domain to be changes and creates a
// Temp Table that dereferences the defined domain type
insert into dba.dmn_temp(type_name,base_domain,width,scale)
select u.type_name,d.domain_name,u.width,u.scale from
sysdomain as d,sysusertype as u where
u.type_name = @domain and
d.domain_id = u.domain_id;
message 'Inserted into dba.dmn_temp' type info to console;
// Temp table that houses the columns that use the above type
insert into dba.dmn_temp_dtl(type_name,table_name,column_name,pk)
select u.type_name,t.table_name,c.column_name,c.pkey from
syscolumn as c,systable as t,sysusertype as u where
u.type_name = @domain and
u.type_id = c.user_type and
t.table_id = c.table_id and
t.table_type = 'BASE';
message 'Inserted into dba.dmn_temp_dtl' type info to console;
insert into dba.view_temp(table_name,view_def)
select table_name,view_def from systable where table_name =
any(select table_name from
syscolumn as c,systable as t,sysusertype as u where
u.type_name = @domain and
u.type_id = c.user_type and
t.table_id = c.table_id and
t.table_type = 'VIEW');
message 'Inserted into dba.view_temp' type info to console;
//===================================================================================================
// ENTER CHECK IF TYPE IS 'D'(DROP) OR 'M'(Modify) OR 'R' (Rename)
// A drop, modify or rename requires that PK, FK, unique constraints and indexes dependent on
// an affected table column be dropped before altering the domain.
//
if(p_action = 'M' or p_action = 'D' or p_action = 'R') then
select Coalesce(Min(p_id),-1) into @PId
from dba.dmn_temp_dtl;
set @LastPid=-1;
while(@PId > @LastPid) loop
select table_name,column_name into @table_name,
@column_name from dba.dmn_temp_dtl where
p_id = @Pid;
call dba.Sp_DropPkFkIx(@table_Name,@column_name);
set @LastPid=@PId;
select Coalesce(Min(p_id),-1) into @PId
from dba.dmn_temp_dtl where
p_id > @LastPid
end loop
end if;
//===================================================================================================
// ENTER CHECK IF TYPE IS 'D'(DROP) OR 'M'(Modify) OR 'R' (Rename)
// A drop, modify or rename requires that any view that uses the domain be dropped before
// altering the domain.
//
if(p_action = 'M' or p_action = 'D' or p_action = 'R') then
select Coalesce(Min(p_id),-1) into @PId
from dba.view_temp;
set @LastPid=-1;
while(@PId > @LastPid) loop
select 'MICROS.' || table_name into @table_name
from dba.view_temp where
p_id = @Pid;
call dba.Sp_DropObject(@table_Name,'V');
set @LastPid=@PId;
select Coalesce(Min(p_id),-1) into @PId
from dba.view_temp where
p_id > @LastPid
end loop
end if;
//===================================================================================================
// MODIFY OR RENAMING THE DOMAIN
if(p_action = 'M' or p_action = 'R') then
// DROP THE DOMAIN
call dba.spem_drop_domain(p_action = 'D',p_domain = @domain);
// CREATE THE NEW DOMAIN
set @sql='CREATE DOMAIN "' || p_domain || '" ' || p_parameters || ';';
message @sql type info to console;
execute immediate @sql;
// NOW PUTTING ALL COLUMNS BACK TO PROPER DOMAIN
select MAX(p_id),MIN(p_id) into @max_id,@cur_id from dba.dmn_temp_dtl;
message 'Max ID: ' || @max_id type info to console;
while @cur_id <= @max_id loop // HAD TO USE LOOP BECAUSE OF AUTO COMMIT ASSOC WITH ALTER TABLE
select table_name,column_name into @table_name,@column_name from dba.dmn_temp_dtl where p_id = @cur_id;
message 'Current id: ' || @cur_id type info to console;
message 'Column name: ' || @column_name type info to console;
message 'Table name: ' || @table_name type info to console;
set @sql='ALTER TABLE ' || 'MICROS."' || @table_name || '" MODIFY "' || @column_name || '" ' || p_domain || ';';
set @cur_id=@cur_id+1;
execute immediate @sql;
message @sql type info to console
end loop
end if; // ENDED MODIFY IF STATEMENT
//===================================================================================================
// DROPPING THE DOMAIN
if p_action = 'D' then
call dba.spem_drop_domain(p_domain = @domain,p_action = 'D')
end if; // ENDED THE DELETE IF STATEMENT
//===================================================================================================
// Rebuild PK, FK, unique constraints and indexes that where dropped as part of this domain change
call dba.sp_BuildDomainDependency('D');
//rebuild views
select Coalesce(Min(p_id),-1) into @PId
from dba.view_temp;
set @LastPid=-1;
while(@PId > @LastPid) loop
set @sql=(select view_def from view_temp where p_id = @Pid);
execute immediate @sql;
set @LastPid=@PId;
select Coalesce(Min(p_id),-1) into @PId
from dba.view_temp where
p_id > @LastPid
end loop
end
go

*/
create procedure
dba.sp_DropObject(in p_ObjectName char(512),in p_ObjectType char(1))
begin
declare @pos integer;
declare @role char(128);
declare @table_name char(256);
declare @stmt varchar(2000);
message 'spem_DropObject> Dropping ',p_ObjectName,' Type: ',p_ObjectType type info to console;
-- Do not continue if the object does not exist
// NOTE: object_id only checks for existence of triggers, views, tables, procdures and functions
if p_ObjectType not in('I','D','R','K') then
if object_id(p_ObjectName) is null then
message 'spem_DropObject> Object does not exist' type info to console;
return
end if
end if;
case p_ObjectType when 'D' then
-- Domain
set @stmt=string('DROP DOMAIN ',p_ObjectName) when 'F' then
-- Function
set @stmt=string('DROP FUNCTION ',p_ObjectName) when 'G' then
-- Trigger
set @stmt=string('DROP TRIGGER ',p_ObjectName) when 'I' then
-- Index
set @stmt=string('DROP INDEX ',p_ObjectName) when 'P' then
-- Stored procedure
set @stmt=string('DROP PROCEDURE',p_ObjectName) when 'T' then
-- Table
set @stmt=string('DROP TABLE ',p_ObjectName) when 'V' then
-- view
set @stmt=string('DROP VIEW ',p_ObjectName) when 'R' then
-- foreign key
// Get table name and role names
set @pos=locate(p_objectName,'.',locate(p_objectName,'.')+1);
if @Pos <> 0 then
set @role="Right"(p_ObjectName,length(p_objectName)-@pos);
set @Table_name="Left"(p_ObjectName,@pos-1)
else
set @pos=locate(p_objectName,'.');
set @role="Right"(p_ObjectName,length(p_objectName)-@pos);
set @Table_name="Left"(p_ObjectName,@pos-1)
end if;
set @stmt=string('ALTER TABLE ',@table_name,' DROP FOREIGN KEY ',@role) when 'K' then
-- primary key
set @stmt=string('ALTER TABLE ',p_ObjectName,' DROP PRIMARY KEY')
else
message 'sp_DropObject> Unknown object type: ',p_ObjectType type info to console;
return
end case
;
message 'spem_DropObject> EXEC ' || @stmt type info to console;
execute immediate @stmt
end
go

create procedure
dba.sp_BuildDomainDependency(pCleanUp char(1) default 'D')
begin
declare @PId integer;
declare @TableId integer;
declare @CreateType char(1);
declare @CreateStmt long varchar;
declare @LastPid integer;
//If the temp table if it does not exists then return
if object_id('dba.dmn_temp_pkfkix') is null then
message 'sp_BuildDomainDependency: dmn_temp_pkfkix does not exist' type info to console;
return
end if;
//
// Build PK, FK, Unique constraints and Indexs saved in dmn_temp_pkfkix.
// Note. order of creation is important...PK, Unique, FK and then indexes
//
set @CreateType='P';
while @CreateType <> 'Q' loop
select Coalesce(Min(p_id),-1) into @PId
from dba.dmn_temp_pkfkix where
create_type = @CreateType;
set @LastPid=-1;
while(@PId > @LastPid) loop
select create_stmt into @CreateStmt
from dba.dmn_temp_pkfkix where
p_id = @PId;
message 'Executing: ' || @CreateStmt type info to console;
execute immediate @CreateStmt;
set @LastPid=@PId;
select Coalesce(Min(p_id),-1) into @PId
from dba.dmn_temp_pkfkix where
create_type = @CreateType and p_id > @LastPid
end loop;
case @CreateType when 'X' then
set @CreateType='Q' when 'F' then //Quit after FK
set @CreateType='X' when 'U' then //Process Indexes after FK
set @CreateType='F' when 'P' then //Process FK after Unique Constr.
set @CreateType='U' //Process Unique Constr. after PK
end case
end loop; // Main loop
//
if pCleanUp = 'D' then
drop table dba.dmn_temp_pkfkix
end if
end
go

This is just the tip of the iceberg of this sql., but my assumption is the issue is towards the top. What do I fix, and where do I execute the edited script? If you need more info, let me know.

 

[Moregelen]

If at all possible I'd just do a new DB... the differences are huge and it would probably be quicker to just redo the whole thing. Plus a refresh of the DB isn't always a bad thing - get rid of all the crap that accumulates over the years. You're not going to find upgrading a RES 3 DB to RES 5 easy. If you're adamant about doing it, I'd go to 4.0 first, then step yourself to 4.12 or thereabouts, before finally upgrading into 5.0, then 5.6. That would probably be your best bet of actually getting it done. Do a rebuild and verification before starting on the RES 3 db too.

 

[IHOP1]

ChuckJ,

Do you have a copy of the DB you could share with me? I have had some pretty good success with this!