Missing languages after server upgrade

After a server upgrade, one or more locales are missing. This article describes the troubleshooting to resolve the problem.

Overview

The old v3 system has custom fields in the tables (name_fr and description_fr). After an upgrade from v3 to v4, some of the tables were missing their columns, because the update scripts do not handle the custom language fields (feature_value, asset_application etc.).

In this case, adding the fields with the Add Locale server action is also not possible, because there were errors from already existing columns from the old v3 system.

Back up database by exporting the schema

Before you proceed with the following instructions, backup your database.

Add missing columns

As of censhare 5.3.5, an SQL update script "add-missing-languages.sql" that adds any missing localization columns is generated at build time by the XSLT transformation databse/xsl/create-missing-languages.xsl .

All you need to do is to copy add-missing-languages.sql to the database and execute it:

CODE

{ #as corpus@application_host
echo -n "enter database host username [oracle] : "
read usr
[ -z $usr ]&&usr=oracle
while echo -n "enter database hostname : "
do read server
[ -z $server ]||break
done
echo -n "enter scp port [22] : "
read port
[ -z $port ]&&port=22
echo;
echo "Executing this command: scp -P$port ~/css/database/add-missing-languages.sql $usr@$server:"
echo;
scp -P$port ~/css/database/add-missing-languages.sql $usr@$server:
echo "# now do this:
ssh -p$port $usr@$server
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
sqlplus corpus/corpus @add-missing-languages.sql"
}

For older versions follow these instructions:

CODE

oracle@db-server$ sqlplus corpus/[password]
SQL>

-- I know that this can be done with plain SQL!
set serveroutput on lines 200
DECLARE
CURSOR fix_lang IS
select table_name,rtrim(column_name,'DE') col from user_tab_columns where column_name like '%!_DE' escape '!'
minus
select table_name,rtrim(column_name,'FR') col from user_tab_columns where column_name like '%!_FR' escape '!' ;
fix_lang_rec fix_lang%rowtype;
BEGIN
dbms_output.put_line('============ execute the statements below ==============');
IF NOT fix_lang%ISOPEN THEN
OPEN fix_lang;
END IF;
LOOP
FETCH fix_lang INTO fix_lang_rec;
EXIT WHEN fix_lang%NOTFOUND;
dbms_output.put_line('ALTER TABLE '|| fix_lang_rec.table_name || ' ADD (' || fix_lang_rec.col || 'FR VARCHAR(256));');
dbms_output.put_line('UPDATE '||fix_lang_rec.table_name || ' set ' || fix_lang_rec.col || 'FR = ' || fix_lang_rec.col || 'DE WHERE ' 
|| fix_lang_rec.col || 'FR is null ;');
END LOOP;
dbms_output.put_line('commit;');
END;
/

-- and execute the output in a screen session
-- but WARNING: if the generated output is more than 20 lines, 
-- then copy the output in txt file and execute it in series by 20 or something. 
-- Example of broken symbols: 
-- DESCRIPTION_FR VARCHAR(256));^MUPDATE EVENT <== see the broken symbol "^M"

Add missing languages

This is done in the censhare Admin Client, in the Configuration/Server/General configuration.

Prepare the file custom.properties

CODE

test -d ~/cscs/app/modules/client/javaclient || mkdir -p ~/cscs/app/modules/client/javaclient 
test -d ~/cscs/app/modules/client/webclient || mkdir -p ~/cscs/app/modules/client/webclient 
cp ~/css/app/modules/client/javaclient/module-custom.properties ~/cscs/app/modules/client/javaclient/module-custom_fr.properties; 
cp ~/css/app/modules/client/webclient/module-custom.properties ~/cscs/app/modules/client/webclient/module-custom_fr.properties

Modify the db-schema.patch

The file path is /opt/corpus/cscs/app/config/db-schema.patch:

CODE

<p:add select="/schema/locales"><locale name="fr"/></p:add>

Restart the censhare server.

Error messages

CODE

2013.03.21-12:34:30.756 INFO : T016: DataSchema: server.open: initializing DataSchema...
2013.03.21-12:34:31.004 SEVERE : T016: CacheService: server.open: reload cached table feature_type failed!
com.censhare.support.transaction.TransactionException: Can"t execute QBE query
at com.censhare.manager.dataobject.DataObjectServiceImpl.executeQuery(DataObjectServiceImpl.java:99)
...
Caused by: java.sql.SQLException: ORA-00904: "T0"."DESCRIPTION_NL": invalid identifier

from SQL statement : SELECT t0.key, t0.name, t0.name_de, t0.name_nl, t0.description, t0.description_de, 
t0.description_nl, t0.enabled, t0.tcn, t0.ROWID FROM feature_type t0