Re: [OpenOLAT] Re: problem with statistics

[Florian Gnägi]

Hi Oronzo

The error tells you exactly what is wrong: you have mixed collations in your database. 

- Fix the wrong settings in your database: new created tables must have the same collation, e.g. set it to utf8_general

- Fix the wrong collations in all existing tables, e.g. set them to utf8_general. You have to fix this for each table field. 

If you don’t know what I’m talking about then ask your database administrator or consult the MySQL manual. This is not an OpenOLAT issue, it is a wrong database configuration. 

Cheers

Florian

Am 09.11.2015 um 17:34 schrieb oronzo lezzi <oronzo...@gmail.com>:

I have found this errors in olat.log

RuntimeException while updating the statistics: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [                                update o_stat_daily as old,                                    (select businesspath,date(creationdate) day, count(*) cnt from o_stat_temptable group by businesspath,day) as delta                                SET old.value=old.value+delta.cnt                                    where old.day=delta.day and old.businesspath=delta.businesspath;                            ]; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' ^%^ >>>stack of 1.cause::org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [
                                update o_stat_daily as old,
                                    (select businesspath,date(creationdate) day, count(*) cnt from o_stat_temptable group by businesspath,day) as delta
                                SET old.value=old.value+delta.cnt
                                    where old.day=delta.day and old.businesspath=delta.businesspath;
                            ]; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException:
                            Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' ->
                            at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
                            at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
                            at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
                            at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
                            at org.olat.course.statistic.StatisticUpdater.updateStatistic(StatisticUpdater.java:139) at org.olat.course.statistic.StatisticUpdateManagerImpl$1.run(StatisticUpdateManagerImpl.java:161)
                            at org.olat.core.commons.services.taskexecutor.model.DBSecureRunnable.run(DBSecureRunnable.java:42) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
                            at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)>>>stack of 2.cause::java.sql.SQLException: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' ->  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2499) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:844) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:748) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
2015-11-09 17:04:22,401 [mpTaskSpringExecutor-2] INFO  StatisticUpdater  - OLAT::INFO ^%^ N1-I16733 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistic<DailyStatisticUpdater>: END. duration=115 milliseconds
2015-11-09 17:04:22,401 [mpTaskSpringExecutor-2] INFO  StatisticUpdateManagerImpl  - OLAT::INFO ^%^ N1-I16734 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistics: done with updater org.olat.course.statistic.StatisticUpdater@1db2e8fc[DailyStatisticUpdater]
2015-11-09 17:04:22,401 [mpTaskSpringExecutor-2] INFO  StatisticUpdateManagerImpl  - OLAT::INFO ^%^ N1-I16735 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistics: starting updater org.olat.course.statistic.StatisticUpdater@eccbcb[WeeklyStatisticUpdater]
2015-11-09 17:04:22,401 [mpTaskSpringExecutor-2] INFO  StatisticUpdater  - OLAT::INFO ^%^ N1-I16736 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistic<WeeklyStatisticUpdater>: START
2015-11-09 17:04:22,517 [mpTaskSpringExecutor-2] ERROR StatisticUpdater  - OLAT::ERROR ^%^ N1-E74 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistic<WeeklyStatisticUpdater>: RuntimeException while updating the statistics: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [                                update o_stat_weekly as old,                                    (select businesspath,concat(year(creationdate),'-',lpad(week(creationdate,3),2,'0')) week, count(*) cnt from o_stat_temptable group by businesspath,week) as delta                                SET old.value=old.value+delta.cnt                                    where old.week=delta.week and old.businesspath=delta.businesspath;                                                        ]; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' ^%^ >>>stack of 1.cause::org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [
                                update o_stat_weekly as old,
                                    (select businesspath,concat(year(creationdate),'-',lpad(week(creationdate,3),2,'0')) week, count(*) cnt from o_stat_temptable group by businesspath,week) as delta
                                SET old.value=old.value+delta.cnt
                                    where old.week=delta.week and old.businesspath=delta.businesspath;                           
                            ]; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' ->  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428) at org.olat.course.statistic.StatisticUpdater.updateStatistic(StatisticUpdater.java:139) at org.olat.course.statistic.StatisticUpdateManagerImpl$1.run(StatisticUpdateManagerImpl.java:161) at org.olat.core.commons.services.taskexecutor.model.DBSecureRunnable.run(DBSecureRunnable.java:42) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)>>>stack of 2.cause::java.sql.SQLException: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' ->  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2499) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:844) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:748) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
2015-11-09 17:04:22,518 [mpTaskSpringExecutor-2] INFO  StatisticUpdater  - OLAT::INFO ^%^ N1-I16737 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistic<WeeklyStatisticUpdater>: END. duration=117 milliseconds
2015-11-09 17:04:22,518 [mpTaskSpringExecutor-2] INFO  StatisticUpdateManagerImpl  - OLAT::INFO ^%^ N1-I16738 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistics: done with updater org.olat.course.statistic.StatisticUpdater@eccbcb[WeeklyStatisticUpdater]
2015-11-09 17:04:22,518 [mpTaskSpringExecutor-2] INFO  StatisticUpdateManagerImpl  - OLAT::INFO ^%^ N1-I16739 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistics: starting updater org.olat.course.statistic.StatisticUpdater@30750fb6[DayOfWeekStatisticUpdater]
2015-11-09 17:04:22,518 [mpTaskSpringExecutor-2] INFO  StatisticUpdater  - OLAT::INFO ^%^ N1-I16740 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistic<DayOfWeekStatisticUpdater>: START
2015-11-09 17:04:22,617 [mpTaskSpringExecutor-2] ERROR StatisticUpdater  - OLAT::ERROR ^%^ N1-E75 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistic<DayOfWeekStatisticUpdater>: RuntimeException while updating the statistics: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [                                update o_stat_dayofweek as old,                                    (select businesspath,dayofweek(creationdate) day, count(*) cnt from o_stat_temptable group by businesspath,day) as delta                                SET old.value=old.value+delta.cnt                                    where old.day=delta.day and old.businesspath=delta.businesspath;                            ]; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' ^%^ >>>stack of 1.cause::org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [
                                update o_stat_dayofweek as old,
                                    (select businesspath,dayofweek(creationdate) day, count(*) cnt from o_stat_temptable group by businesspath,day) as delta
                                SET old.value=old.value+delta.cnt
                                    where old.day=delta.day and old.businesspath=delta.businesspath;
                            ]; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' ->  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428) at org.olat.course.statistic.StatisticUpdater.updateStatistic(StatisticUpdater.java:139) at org.olat.course.statistic.StatisticUpdateManagerImpl$1.run(StatisticUpdateManagerImpl.java:161) at org.olat.core.commons.services.taskexecutor.model.DBSecureRunnable.run(DBSecureRunnable.java:42) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)>>>stack of 2.cause::java.sql.SQLException: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' ->  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2499) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:844) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:748) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
2015-11-09 17:04:22,617 [mpTaskSpringExecutor-2] INFO  StatisticUpdater  - OLAT::INFO ^%^ N1-I16741 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistic<DayOfWeekStatisticUpdater>: END. duration=99 milliseconds
2015-11-09 17:04:22,617 [mpTaskSpringExecutor-2] INFO  StatisticUpdateManagerImpl  - OLAT::INFO ^%^ N1-I16742 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistics: done with updater org.olat.course.statistic.StatisticUpdater@30750fb6[DayOfWeekStatisticUpdater]
2015-11-09 17:04:22,617 [mpTaskSpringExecutor-2] INFO  StatisticUpdateManagerImpl  - OLAT::INFO ^%^ N1-I16743 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistics: starting updater org.olat.course.statistic.StatisticUpdater@597fedda[HourOfDayStatisticUpdater]
2015-11-09 17:04:22,617 [mpTaskSpringExecutor-2] INFO  StatisticUpdater  - OLAT::INFO ^%^ N1-I16744 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistic<HourOfDayStatisticUpdater>: START
2015-11-09 17:04:22,724 [mpTaskSpringExecutor-2] ERROR StatisticUpdater  - OLAT::ERROR ^%^ N1-E76 ^%^ org.olat.course.statistic ^%^ n/a ^%^ n/a ^%^ n/a ^%^ n/a ^%^ updateStatistic<HourOfDayStatisticUpdater>: RuntimeException while updating the statistics: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [                                update o_stat_hourofday as old,                                    (select businesspath,hour(creationdate) hour, count(*) cnt from o_stat_temptable group by businesspath,hour) as delta                                SET old.value=old.value+delta.cnt                                    where old.hour=delta.hour and old.businesspath=delta.businesspath;                            ]; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' ^%^ >>>stack of 1.cause::org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [
                                update o_stat_hourofday as old,
                                    (select businesspath,hour(creationdate) hour, count(*) cnt from o_stat_temptable group by businesspath,hour) as delta
                                SET old.value=old.value+delta.cnt
                                    where old.hour=delta.hour and old.businesspath=delta.businesspath;
                            ]; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' ->  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428) at org.olat.course.statistic.StatisticUpdater.updateStatistic(StatisticUpdater.java:139) at org.olat.course.statistic.StatisticUpdateManagerImpl$1.run(StatisticUpdateManagerImpl.java:161) at org.olat.core.commons.services.taskexecutor.model.DBSecureRunnable.run(DBSecureRunnable.java:42) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)>>>stack of 2.cause::java.sql.SQLException: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' ->  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2499) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:844) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:748) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)


Il giorno lunedì 9 novembre 2015 10:18:39 UTC+1, oronzo lezzi ha scritto:

Hello,
I have a big issue with Statistic generation.
Although I'm recalculation statistics are always empty.

I see no error in olat.log. Where can I see the cause of the problem ?

--
--
Sie erhalten diese Nachricht, weil Sie Mitglied sind von Google
Groups-Gruppe "OpenOLAT".
Für das Erstellen von Beiträgen in dieser Gruppe senden Sie eine E-Mail
an open...@googlegroups.com
Um sich von dieser Gruppe abzumelden, senden Sie eine E-Mail an
openolat+u...@googlegroups.com
Weitere Optionen finden Sie in dieser Gruppe unter
http://groups.google.com/group/openolat?hl=de
-------------------------------------------------------------------------------------------------------------------
OpenOLAT - infinite learning - http://www.openolat.org

---
You received this message because you are subscribed to the Google Groups "OpenOLAT" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openolat+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[oronzo lezzi]

Thanks Florian

ISSUE SOLVED