grouper-dev - [grouper-dev] RE: sortString0 lenght

Subject: Grouper Developers Forum

List archive

[grouper-dev] RE: sortString0 lenght

From: Chris Hyzer <>
To: Gagné Sébastien <>
Cc: "" <>
Subject: [grouper-dev] RE: sortString0 lenght
Date: Tue, 23 Oct 2012 18:20:30 +0000
Accept-language: en-US

Yeah, in Oracle to have CHAR instead of BYTE is the best, I don’t know how to get DDLutils to do that though…

It inserted in my oracle DB just fine. Some setting is different.

If you want to switch everything to char, here is how to do it. I haven’t tested it though, so at your own risk:

select 'ALTER TABLE '||utc.table_name||

' MODIFY ('||column_name||' VARCHAR2('||char_length||' char));' as script

from user_tab_cols utc, user_tables ut

where utc.table_name = ut.table_name and utc.table_name like 'GROUPER%' and

UTC.DATA_TYPE like 'VARCHAR2%'

;

Yeah, Im not sure what the number to add is, maybe we need a configuration option so people can tweak at runtime. I have a feeling that 3 or 4 would be fine…

https://bugs.internet2.edu/jira/browse/GRP-859

I fixed this is a default to 3 but can configure different if needed… will be in the final 2.1.3

Thanks,

Chris

From: Gagné Sébastien [mailto:]
Sent: Tuesday, October 23, 2012 1:20 PM
To: Chris Hyzer
Cc:
Subject: RE: sortString0 lenght

I get the same result as you do (did it via GSH).

gsh 0% testString = "H13_FRA2007, Questions d’histoire de la litérature"

H13_FRA2007, Questions d’histoire de la litérature

gsh 1% GrouperUtil.truncateAscii(testString, 50)

H13_FRA2007, Questions d’histoire de la litératu

I tried to manually insert “H13_FRA2007, Questions d’histoire de la litératu” and it failed, size=51.

That apostrophe (a quote in English?) has a char value of 8217 that might be the problem. It’s different than the usual ' (which has a value of 39).

I think a better patch would be something like :

If (input.charAt(i) >= 128)

++asciiLength;

If (input.charAt(i) >= ???)

++asciiLength;

But I’m not sure at which value the 3 byte is required. If I remember correctly UTf8 can have 4 bytes character right ? Maybe it would be safer to have asciiLength+=4 ?

The other option would be to change the column type to VARCHAR2(50 CHAR), but it might require too much change, but it would simplify everything that has to work with the DB.

De : Chris Hyzer []
Envoyé : 23 octobre 2012 12:34
À : Gagné Sébastien
Cc :
Objet : RE: sortString0 lenght

The truncate method take it down to this:

H13_FRA2007, Questions d’histoire de la litératu

Which is 48 long, and I assumed the quote and the é take two buytes, so it should be 50 in the DB. But for some reason it is 51. Maybe one of the special chars is 3 bytes in the DB???

Can you manually put that string in a row in the table (temporarily?) and see if it works?

Also, please run this and see what it prints out:

String testString = "H13_FRA2007, Questions d’histoire de la litérature";

System.out.println(GrouperUtil.truncateAscii(testString, 50));

Maybe this is the patch we need just to be safe:

GrouperUtil.truncateAscii()

FROM:

//keep count of non ascii chars

if (!isAscii(input.charAt(i))) {

asciiLength++;

}

TO:

//keep count of non ascii chars

if (!isAscii(input.charAt(i))) {

asciiLength+=2;

}

Thanks,

Chris

From: Gagné Sébastien []
Sent: Tuesday, October 23, 2012 11:46 AM
To: Chris Hyzer
Cc:
Subject: RE: sortString0 lenght

Full stack trace below, I copied everything, but maybe you would only be interested in the last part I marked (my global try..catch).

We are using Oracle database

According to the stack trace : group dExtension: H13_FRA2007, Questions d’histoire de la literature

I checked in my DB and found some truncated strings. I think you are right about accents character taking 2 characters in the DB. I found strings with accents, some of them were truncated they were 50-“number of accents there is” in length

Here are some examples, all of them were truncated :

50: H13_CHM1600, Chimie de l'environnement - introduct

49: A12_113710, Littératures de langues anglaise et f

48: H13_113810, Études anglaises et Littérature comp

I think I found the problem :

H13_FRA2007, Questions d’histoire de la litérature

Is exactly 50 characters according to String.length()

But the accents makes it 51 characters long in the DB.

So there might an error in truncateAscii when input is exactly 50 characters long

2012-10-23 01:38:07,508: [main] ERROR JDBCExceptionReporter.logExceptions(234) - - ORA-12899: value too large for column "DV_GROUPER"."GROUPER_MEMBERS"."SORT_STRING0" (actual: 51, maximum: 50)

2012-10-23 01:38:07,509: [main] ERROR JDBCExceptionReporter.logExceptions(234) - - ORA-12899: value too large for column "DV_GROUPER"."GROUPER_MEMBERS"."SORT_STRING0" (actual: 51, maximum: 50)

2012-10-23 01:38:07,509: [main] ERROR GrouperUtil.logErrorNextException(620) - - Next exception

java.sql.SQLException: ORA-12899: value too large for column "DV_GROUPER"."GROUPER_MEMBERS"."SORT_STRING0" (actual: 51, maximum: 50)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)

at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1135)

at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:12402)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)

at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)

at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)

at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:268)

at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)

at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)

at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)

at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)

at edu.internet2.middleware.grouper.hibernate.ByObject.save(ByObject.java:214)

at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3StemDAO$1.callback(Hib3StemDAO.java:148)

at edu.internet2.middleware.grouper.hibernate.HibernateSession.callbackHibernateSession(HibernateSession.java:571)

at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3StemDAO.createChildGroup(Hib3StemDAO.java:110)

at edu.internet2.middleware.grouper.Stem$5.callback(Stem.java:2073)

at edu.internet2.middleware.grouper.hibernate.HibernateSession.callbackHibernateSession(HibernateSession.java:571)

at edu.internet2.middleware.grouper.Stem.internal_addChildGroup(Stem.java:1991)

at edu.internet2.middleware.grouper.Stem.internal_addChildGroup(Stem.java:1964)

at edu.internet2.middleware.grouper.Stem.internal_addChildGroup(Stem.java:1939)

at edu.internet2.middleware.grouper.GroupSave$1$1.callback(GroupSave.java:379)

at edu.internet2.middleware.grouper.GrouperSession.callbackGrouperSession(GrouperSession.java:801)

at edu.internet2.middleware.grouper.GroupSave$1.callback(GroupSave.java:274)

at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3TransactionDAO$1.callback(Hib3TransactionDAO.java:66)

at edu.internet2.middleware.grouper.hibernate.HibernateSession.callbackHibernateSession(HibernateSession.java:571)

at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3TransactionDAO.transactionCallback(Hib3TransactionDAO.java:56)

at edu.internet2.middleware.grouper.hibernate.GrouperTransaction.callbackGrouperTransaction(GrouperTransaction.java:87)

at edu.internet2.middleware.grouper.hibernate.GrouperTransaction.callbackGrouperTransaction(GrouperTransaction.java:106)

at edu.internet2.middleware.grouper.GroupSave.save(GroupSave.java:267)

at ca.umontreal.dgtic.grouper.acadLoader.AcadLoader.createOrUpdateGrouperGroup(AcadLoader.java:945)

at ca.umontreal.dgtic.grouper.acadLoader.AcadLoader.createOrUpdateGrouperGroup(AcadLoader.java:879)

at ca.umontreal.dgtic.grouper.acadLoader.AcadLoader.traiterDepartement(AcadLoader.java:412)

at ca.umontreal.dgtic.grouper.acadLoader.AcadLoader.traiterImportationComplete(AcadLoader.java:369)

at ca.umontreal.dgtic.grouper.acadLoader.AcadLoader.chargerInscriptions(AcadLoader.java:184)

at ca.umontreal.dgtic.grouper.acadLoader.AcadLoader.main(AcadLoader.java:76)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at bsh.Reflect.invokeMethod(Unknown Source)

at bsh.Reflect.invokeStaticMethod(Unknown Source)

at bsh.Name.invokeMethod(Unknown Source)

at bsh.BSHMethodInvocation.eval(Unknown Source)

at bsh.BSHPrimaryExpression.eval(Unknown Source)

at bsh.Interpreter.eval(Unknown Source)

at edu.internet2.middleware.grouper.app.gsh.ShellHelper.eval(ShellHelper.java:63)

at edu.internet2.middleware.grouper.app.gsh.GrouperShell.run(GrouperShell.java:428)

at edu.internet2.middleware.grouper.app.gsh.GrouperShell.grouperShellHelper(GrouperShell.java:232)

at edu.internet2.middleware.grouper.app.gsh.GrouperShell.main(GrouperShell.java:162)

at edu.internet2.middleware.grouper.app.gsh.GrouperShellWrapper.main(GrouperShellWrapper.java:31)

2012-10-23 01:38:07,511: [main] ERROR GrouperUtil.logErrorNextException(620) - - Next exception