B21 test result: Issue in b20 unable to be migrated to b21

I found the reason why the Chinese attachment name becames
garbage characters.
It was because the locale.default.charset was set to UTF-8
when the file was attached in B20.

I set the  locale.default.charset to BIG5 in B21 and as long
as the time of attaching was set to BIG5 in B20, it shows
correct attachment file name in B21.

Fred

-----Original Message-----
From: FRED.YY.LIN ªL«É©µ
Sent: Thursday, December 21, 2006 6:12 PM
To: 'devscarab.tigris.org'
Subject: RE: B21 test result: Issue in b20 unable to be
migrated to b21


Thanks Hussayn,

My environment is:
Apache Ant 1.6.5 up
MYSQL server 5.0, MYSQL administrator, MYSQL Query Browser
Java SDK j2sdk1.4.2 up

Below is the error message for "ant
migrate-from-b20"

migrate-from-b20:
      [sql] Executing file:
D:InstallScarab_trackerscarab-0.21targetscarab
databaseinstancesqlmysqlupgrademysql-upgrade-1.0b20-1.0
b21-1.sql
      [sql] Executing file:
D:InstallScarab_trackerscarab-0.21targetscarab
databaseinstancesqlmysqlupgrademysql-upgrade-1.0b20-1.0
b21-2.sql
      [sql] Failed to execute:    CREATE TABLE
SCARAB_NOTIFICATION_STATUS ( ACTIVITY_ID BIGINT NOT NULL,
CREATOR_ID MEDIUMINT NOT NULL, RECEIVER_ID MEDIUMINT NO
T NULL, STATUS MEDIUMINT NOT NULL, CREATION_DATE DATETIME,
CHANGE_DATE DATETIME, STATUS_COMMENT VARCHAR(255), PRIMARY
KEY(ACTIVITY_ID,CREATOR_ID,RECEIVER_ID), FOREIGN KEY
(CREATOR_ID) REFERENCES TURBINE_USER (USER_ID), FOREIGN KEY
(ACTIVITY
_ID) REFERENCES SCARAB_ACTIVITY (ACTIVITY_ID), INDEX
IX_NOTIF_STATUS (STATUS) )
      [sql] java.sql.SQLException: Can't create table
'.scarabtestscarab_notif
ication_status.frm' (errno: 150)
      [sql] Failed to execute:    CREATE TABLE
SCARAB_NOTIFICATION_FILTER ( MODULE_ID MEDIUMINT NOT NULL,
USER_ID MEDIUMINT NOT NULL, ACTIVITY_TYPE VARCHAR(30) NOT
NULL, MANAGER_ID MEDIUMINT default 0 NOT NULL, FILTER_STATE
INTEGER(1) default 0 NOT NULL, SEND_SELF INTEGER(1) default
0 NOT NULL, SEND_FAILURES INTEGER(1) default 0 NOT NULL,
PRIMARY KEY(MODULE_ID,USER_ID,ACTIVITY_TYPE,MANAGER_ID),
FOREIGN KEY (USER_ID) REFERENCES TURBINE_USER (USER_ID),
FOREIGN KEY (MODULE_ID) REFERENCES SCARAB_MODULE (MODULE_ID)
)
      [sql] java.sql.SQLException: Can't create table
'.scarabtestscarab_notif
ication_filter.frm' (errno: 150)
      [sql] Executing file:
D:InstallScarab_trackerscarab-0.21targetscarab
databaseinstancesqlmysqlupgrademysql-upgrade-1.0b20-1.0
b21-3.sql
      [sql] Failed to execute:   DROP INDEX LIST_ID ON
SCARAB_R_MODULE_USER_ATTRIBUTE
      [sql] java.sql.SQLException: Error on rename of
'.scarabtest#sql-50c_c4'
 to '.scarabtestscarab_r_module_user_attribute' (errno:
150)
      [sql] Failed to execute:   CREATE UNIQUE INDEX LIST_ID
ON SCARAB_R_MODULE_USER_ATTRIBUTE
(LIST_ID,MODULE_ID,USER_ID,ISSUE_TYPE_ID,ATTRIBUTE_ID,INTERN
AL_AT
TRIBUTE)
      [sql] java.sql.SQLException: Duplicate key name
'LIST_ID'      [sql] Failed to execute:   DROP TABLE
SCARAB_ISSUE
      [sql] java.sql.SQLException: Cannot delete or update a
parent row: a foreign key constraint fails
      [sql] Failed to execute:   CREATE TABLE SCARAB_ISSUE (
PRIMARY KEY(ISSUE_ID), FOREIGN KEY (MODULE_ID) REFERENCES
SCARAB_MODULE (MODULE_ID), FOREIGN KEY (TYPE_ID) REFERENCES
SCARAB_ISSUE_TYPE (ISSUE_TYPE_ID), FOREIGN KEY
(CREATED_TRANS_ID) REFERENCES SCARAB_TRANSACTION
(TRANSACTION_ID), INDEX IX_TYPE_MODULE (TYPE_ID, MODULE_ID),
INDEX IX_COUNT_PREFIX (ID_COUNT, ID_PREFIX) ) SELECT
i.ISSUE_ID,
 i.ID_PREFIX, i.ID_COUNT, i.ID_DOMAIN, i.TYPE_ID,
i.MODULE_ID, i.CREATED_TRANS_I
D, max(a.transaction_id) LAST_TRANS_ID, i.DELETED, i.MOVED
FROM SCARAB_ISSUE_BAC
KUP_b20_b21 i LEFT OUTER JOIN SCARAB_ACTIVITY a ON
(i.ISSUE_ID = a.ISSUE_ID) GROUP BY i.ISSUE_ID
      [sql] java.sql.SQLException: Table 'scarab_issue'
already exists
      [sql] 14 of 20 SQL statements executed successfully

BUILD SUCCESSFUL
Total time: 13 seconds


mysql-upgrade-1.0b20-1.0b21-1.sql works well

mysql-upgrade-1.0b20-1.0b21-2.sql has some key declared as
midinteger in b21 while they were integer in b20,
although SET FOREIGN_KEY_CHECKS = 0;  MySql still give a
errno: 150.
I change the inconsistent data type back to integer, it
works.

please see the attachment and change the file extension back
to sql if you want to use it.

mysql-upgrade-1.0b20-1.0b21-3.sql has more complicated
problem.
I failed to drop the indicated table, as a result, I ALTER
them.
I add the new column in the right position. and add the
required foreign key, index.  please see the attachment.
In this case, I didn't copy the issues from the
SCARAB_ISSUE_BACKUP_b20_b21 table although it was created.
All data remains in the scarab_issue and just the
LAST_TRANS_ID column was added after CREATED_TRANS_ID.

After I adopted the attached two modified SQL files, it
works. I see all the old issues. The query, attribute and
issue type were correct.
However, I encounterred a new problem.

I attached some files with Chinese file name in B20. They
became some garbage characters in B21.
However, the Chinese issue content was fine in B21.

I uploaded a new file with Chinese file name. It showed
Chinese chars in B21.
I set the parameters as below for both B21 and B20.

#set export file to big5 character set
scarab.dataexport.encoding = BIG5

scarab.attachment.remove.permanent=false

#make  BIG5 file name allowable
locale.default.charset=BIG5

#set email encoding, default is UTF-8
scarab.email.encoding=BIG5

Actually, when I use B20 to access the issues, they are
shown in Chinese.
I am wondering if it is an migration problem or not.
Any suggestion ?

Fred


-----Original Message-----
From: Hussayn Dabbous [mailto:hussayn.dabboussaxess.de]
Sent: Wednesday, December 20, 2006 4:17 PM
To: devscarab.tigris.org
Subject: Re: B21 test result: Issue in b20 unable to be
migrated to b21


Hi, Fread;

The row SCARAB_ISSUE.LAST_TRANS_ID is a new column in the
database schema.
The table scarab_notification_status is a new table in
release 0.21.

The fact, that you do not see the LAST_TRANS_ID column and
the SCARAB_NOTIFICAZTION_STATUS
table in your RDBMS initially points to an error during
database upgrade.

It all looks like the migrate-to-b20 script did NOT run, or
at least it did not run
successfully.

So first of all you must be aware of the fact, that we only
fully support MySQL, PostgreSQL and
partially support Oracle. If you run a differnet RDBMS, you
are still nmot lost, but need to
do some additional work, see step 2 below.

Now proceed as follows:

1.) Look into your database
     Check the table definition of SCARAB_ISSUE. It should
look similar to this:

+------------------+------------+------+-----+---------+----
---+
| Field            | Type       | Null | Key | Default |
Extra |
+------------------+------------+------+-----+---------+----
---+
| ISSUE_ID         | int(11)    |      | PRI | 0       |    
  |
| ID_PREFIX        | char(4)    |      |     |         |    
  |
| ID_COUNT         | int(10)    |      | MUL | 0       |    
  |
| ID_DOMAIN        | char(127)  | YES  |     | NULL    |    
  |
| TYPE_ID          | int(11)    | YES  | MUL | NULL    |    
  |
| MODULE_ID        | int(11)    |      |     | 0       |    
  |
| CREATED_TRANS_ID | bigint(20) | YES  |     | NULL    |    
  |
| LAST_TRANS_ID    | bigint(20) | YES  |     | NULL    |    
  |
| DELETED          | int(1)     | YES  |     | 0       |    
  |
| MOVED            | int(1)     | YES  |     | NULL    |    
  |
+------------------+------------+------+-----+---------+----
---+

Probably in your installation the LAST_TRANS_ID column is
missing.

2.) Check, thast the script

    
/target/scarab/database/instance/sql/<yourDB>/upgrade/
mysql-upgrade-1.0b20-1.0b21-*.sql

     exists. where <yourDB> denotes either
mysql,oracle or postgres. If you use another RDBMS,
     forget it or migrate the scripts from the mysql
distribution to your RDBMS before
     proceeding. Here your help could flow back to Scarab by
publishjing your migration scripts
     to the scarab-devlist 

3.) revert your database to the b20 compatible version. If
you dont have a backup,
     recreate it from scratch using your b20 system.

4.) apply ant again migrate-from-b20
     Check carefully for any errors occuring in this step.
     If you encounter errors here, feel free to post the
console output to this list.
     We can have a look into it and try to get further in
this issue.

5.) If you do not encounter errors, you should now find the
missing column in SCARAB_ISSUE
     and the missing table SCARAB_NOTIFICATION_STATUS


FRED.YY.LIN ??? schrieb:
> more information
> When I want to enter a new issue of the b20 defined
issue type, it shows:
>
> Alert! com.workingdogs.village.DataSetException: Column
name: LAST_TRANS_ID does not exist!
>
> -----Original Message-----
> From: FRED.YY.LIN ??? [mailto:FRED.YY.LINDELTA.COM.TW]
> Sent: Wednesday, December 20, 2006 1:42 PM
> To: devscarab.tigris.org
> Subject: B21 test result: Issue in b20 unable to be
migrated to b21
>
>
>
> Dear all,
>
> In order to migrate my data in b20 to b21, I carefully
rebuild a blank b20 and put into some test data and then
migrate it to b21.
>
>
> However, it is not working. Here is w I have done. Hope
it help to the migration of other users.
>
> 1. build a whold new b20 and ant create-db to create a
new database.
> 2. select global attributes and create a attribute test
with dropdown data type and put into some value test1,
test2, test3
> 3. create another global attirbute with drown data type
named "another test" and put some value like ss,
pp, rrr, ttt ....
> 4. create a global issue type QQQQ and put the
"test" and "description" attribute into
the attribute group1 and "another test",
"priority" in attribute group 2.
> 5. create a new module "TEST" and QQQQ is the
only issue type.
> 6. input 5 to 6 issues in QQQQ issue type
>
> 7. Query the issues to make sure they are stored
properly.
> 8. quit b20
>
>
> 9. build a new b21 using ant in the build folder
> 10. ant migrate-from-b20
>
>
> 11. login in to B21 and enter the Test module created
in step 5
> 12. check the global attribute and global issue type.
they are the same as defined in the step 2,3
> 13. check the module QQQQ issue type. it is the same
defined in the step 4
> 14. Query "most recent",  Fail: Alert! There
was an error processing your query: Unknown column
'SCARAB_ISSUE.LAST_TRANS_ID' in 'on clause'
> 15. Query all my issue in all modules, Fail, There are
no matching issues.
> 16. Query  my issue in this modules. Fail, There are no
matching issues.
> 17. Try Advanced Query. query condition: Issur ID range
from 1 to 10000,
>
>       Fail:  Alert! There was an error processing your
query: Unknown column 'SCARAB_ISSUE.LAST_TRANS_ID' in 'on
clause'
> 18. public query and private query fails in the same
way as below:
>       select public or private query. create new query.
>       select issue type: "All" is
unselectable, select the number below "All",
>
>       Fail: Alert! There was an error processing your
query: Unknown column 'SCARAB_ISSUE.LAST_TRANS_ID' in 'on
clause'
>
>      Select QQQQ issue type, it enters the Advanced
Query screen and then fails as step 17.
>
> In sum, the number of issues can be seen when I new a
public or private query but the contents cannot be
retrieved.
>
> The error message on the Tomcat console is:
>
> [ERROR] BasePeer - -getPendingNotifications():
org.apache.torque.TorqueException
> : java.sql.SQLException: Table
'scarabtest.scarab_notification_status' doesn't exist
>
> Hope it helps.
>
> Fred
>
>
************************************************************
************************************************************
*
> This email message, including any attachments, is for
the sole use of the intended recipient(s) and may contain
confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If
you are not the intended recipient, please contact the
sender by reply e-mail and destroy all copies of the
original message. [Delta Electronic, INC. Taiwan]
>
************************************************************
************************************************************
*
>
>
------------------------------------------------------------
---------
> To unsubscribe, e-mail: dev-unsubscribescarab.tigris.org
> For additional commands, e-mail: dev-helpscarab.tigris.org
>
>
>
************************************************************
************************************************************
*
> This email message, including any attachments, is for
the sole use of the intended recipient(s) and may contain
confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If
you are not the intended recipient, please contact the
sender by reply e-mail and destroy all copies of the
original message. [Delta Electronic, INC. Taiwan]
>
************************************************************
************************************************************
*
>
>
------------------------------------------------------------
---------
> To unsubscribe, e-mail: dev-unsubscribescarab.tigris.org
> For additional commands, e-mail: dev-helpscarab.tigris.org
>


--
Dr. Hussayn Dabbous
SAXESS Software Design GmbH
Neuenhöfer Allee 125
50935 Köln

tel.: +49 221 560 11 0
fax.: +49 221 560 11 20

mailto:infosaxess.de
http://www.saxess.de
http://www.saxess.com
http://www.saxess.org

------------------------------------------------------------
---------
To unsubscribe, e-mail: dev-unsubscribescarab.tigris.org
For additional commands, e-mail: dev-helpscarab.tigris.org


************************************************************
************************************************************
*
This email message, including any attachments, is for the
sole use of the intended recipient(s) and may contain
confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If
you are not the intended recipient, please contact the
sender by reply e-mail and destroy all copies of the
original message. [Delta Electronic, INC. Taiwan]
************************************************************
************************************************************
*

------------------------------------------------------------
---------
To unsubscribe, e-mail: dev-unsubscribescarab.tigris.org
For additional commands, e-mail: dev-helpscarab.tigris.org