[Building Sakai] Site Stats jobs that fail

Sean Keesler sean.keesler at threecanoes.com
Mon Sep 21 08:54:03 PDT 2009 

I do have an error...after running your SQL...apparently this client set up
the db tables incorrectly.
Aside from altering the tables and setting that right, do have any other
suggestions?

INFO: Starting job: DEFAULT.SiteStats Event Aggregator (2009-09-21
08:47:10,376 QuartzScheduler_Worker-1_org.
sakaiproject.sitestats.impl.StatsAggregateJobImpl)
WARN: SQL Error: 1267, SQLState: HY000 (2009-09-21 08:47:10,845
QuartzScheduler_Worker-1_org.hibernate.util.JDBCExceptionReporter)
ERROR: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '=' (2009-09-21 08:47:10,845
QuartzScheduler_Worker-1_org.hibernate.util.JDBCExceptionReporter)
WARN: SQL Error: 1267, SQLState: HY000 (2009-09-21 08:47:10,846
QuartzScheduler_Worker-1_org.hibernate.util.JDBCExceptionReporter)
ERROR: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '=' (2009-09-21 08:47:10,846
QuartzScheduler_Worker-1_org.hibernate.util.JDBCExceptionReporter)
WARN: Unable to commit transaction:  (2009-09-21 08:47:10,847
QuartzScheduler_Worker-1_org.sakaiproject.sitestats.impl.StatsUpdateManagerImpl)
org.hibernate.exception.GenericJDBCException: could not execute query
        at
org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.loader.Loader.doList(Loader.java:2223)
        at
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
        at org.hibernate.loader.Loader.list(Loader.java:2099)
        at
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
        at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
        at
org.sakaiproject.sitestats.impl.StatsUpdateManagerImpl.doUpdateResourceStatObjects(StatsUpdateManagerImpl.java:586)
        at
org.sakaiproject.sitestats.impl.StatsUpdateManagerImpl.access$400(StatsUpdateManagerImpl.java:64)
        at
org.sakaiproject.sitestats.impl.StatsUpdateManagerImpl$4.doInHibernate(StatsUpdateManagerImpl.java:497)
        at
org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372)
        at
org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:338)
        at
org.sakaiproject.sitestats.impl.StatsUpdateManagerImpl.doUpdateConsolidatedEvents(StatsUpdateManagerImpl.java:475)
        at
org.sakaiproject.sitestats.impl.StatsUpdateManagerImpl.collectEvents(StatsUpdateManagerImpl.java:210)
        at
org.sakaiproject.sitestats.impl.StatsAggregateJobImpl.startJob(StatsAggregateJobImpl.java:214)
        at
org.sakaiproject.sitestats.impl.StatsAggregateJobImpl.execute(StatsAggregateJobImpl.java:118)
        at
org.sakaiproject.component.app.scheduler.jobs.SpringJobBeanWrapper.execute(SpringJobBeanWrapper.java:72)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:203)
        at
org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
Caused by: java.sql.SQLException: Illegal mix of collations
(latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation
'='
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
        at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
        at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1448)
        at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
        at
org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
        at org.hibernate.loader.Loader.doQuery(Loader.java:674)
        at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
        at org.hibernate.loader.Loader.doList(Loader.java:2220)
        ... 17 more
ERROR: An error occurred while processing/persisting events to db. Please
check your logs, fix possible problems and re-run this job (will start after
last successful processed event). (2009-09-21 08:47:10,848
QuartzScheduler_Worker-1_org.sakaiproject.sitestats.impl.StatsAggregateJobImpl)
INFO: Summary: An error occurred while processing/persisting events to db.
Please check your logs, fix possible problems and re-run this job (will
start after last successful processed event). (2009-09-21 08:47:10,848
QuartzScheduler_Worker-1_org.sakaiproject.sitestats.impl.StatsAggregateJobImpl)
INFO: Finishing job: DEFAULT.SiteStats Event Aggregator (2009-09-21
08:47:10,849
QuartzScheduler_Worker-1_org.sakaiproject.sitestats.impl.StatsAggregateJobImpl)


Sean Keesler
130 Academy Street
Manlius, New York 13104 USA
315-663-7756
sean.keesler at threecanoes.com


On Mon, Sep 21, 2009 at 11:49 AM, Sean Keesler <sean at keesler.org> wrote:

> I do have an error...after running your SQL...apparently this client set up
> the db tables incorrectly.
> Aside from altering the tables and setting that right, do have any other
> suggestions?
>
> INFO: Starting job: DEFAULT.SiteStats Event Aggregator (2009-09-21
> 08:47:10,376
> QuartzScheduler_Worker-1_org.sakaiproject.sitestats.impl.StatsAggregateJobImpl)
> WARN: SQL Error: 1267, SQLState: HY000 (2009-09-21 08:47:10,845
> QuartzScheduler_Worker-1_org.hibernate.util.JDBCExceptionReporter)
> ERROR: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
> (utf8_general_ci,COERCIBLE) for operation '=' (2009-09-21 08:47:10,845
> QuartzScheduler_Worker-1_org.hibernate.util.JDBCExceptionReporter)
> WARN: SQL Error: 1267, SQLState: HY000 (2009-09-21 08:47:10,846
> QuartzScheduler_Worker-1_org.hibernate.util.JDBCExceptionReporter)
> ERROR: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
> (utf8_general_ci,COERCIBLE) for operation '=' (2009-09-21 08:47:10,846
> QuartzScheduler_Worker-1_org.hibernate.util.JDBCExceptionReporter)
> WARN: Unable to commit transaction:  (2009-09-21 08:47:10,847
> QuartzScheduler_Worker-1_org.sakaiproject.sitestats.impl.StatsUpdateManagerImpl)
> org.hibernate.exception.GenericJDBCException: could not execute query
>         at
> org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
>         at
> org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
>         at
> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
>         at org.hibernate.loader.Loader.doList(Loader.java:2223)
>         at
> org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
>         at org.hibernate.loader.Loader.list(Loader.java:2099)
>         at
> org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
>         at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
>         at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
>         at
> org.sakaiproject.sitestats.impl.StatsUpdateManagerImpl.doUpdateResourceStatObjects(StatsUpdateManagerImpl.java:586)
>         at
> org.sakaiproject.sitestats.impl.StatsUpdateManagerImpl.access$400(StatsUpdateManagerImpl.java:64)
>         at
> org.sakaiproject.sitestats.impl.StatsUpdateManagerImpl$4.doInHibernate(StatsUpdateManagerImpl.java:497)
>         at
> org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372)
>         at
> org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:338)
>         at
> org.sakaiproject.sitestats.impl.StatsUpdateManagerImpl.doUpdateConsolidatedEvents(StatsUpdateManagerImpl.java:475)
>         at
> org.sakaiproject.sitestats.impl.StatsUpdateManagerImpl.collectEvents(StatsUpdateManagerImpl.java:210)
>         at
> org.sakaiproject.sitestats.impl.StatsAggregateJobImpl.startJob(StatsAggregateJobImpl.java:214)
>         at
> org.sakaiproject.sitestats.impl.StatsAggregateJobImpl.execute(StatsAggregateJobImpl.java:118)
>         at
> org.sakaiproject.component.app.scheduler.jobs.SpringJobBeanWrapper.execute(SpringJobBeanWrapper.java:72)
>         at org.quartz.core.JobRunShell.run(JobRunShell.java:203)
>         at
> org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
> Caused by: java.sql.SQLException: Illegal mix of collations
> (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation
> '='
>         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
>         at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
>         at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
>         at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
>         at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
>         at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
>         at
> com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1448)
>         at
> org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
>         at
> org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
>         at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
>         at org.hibernate.loader.Loader.doQuery(Loader.java:674)
>         at
> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
>         at org.hibernate.loader.Loader.doList(Loader.java:2220)
>         ... 17 more
> ERROR: An error occurred while processing/persisting events to db. Please
> check your logs, fix possible problems and re-run this job (will start after
> last successful processed event). (2009-09-21 08:47:10,848
> QuartzScheduler_Worker-1_org.sakaiproject.sitestats.impl.StatsAggregateJobImpl)
> INFO: Summary: An error occurred while processing/persisting events to db.
> Please check your logs, fix possible problems and re-run this job (will
> start after last successful processed event). (2009-09-21 08:47:10,848
> QuartzScheduler_Worker-1_org.sakaiproject.sitestats.impl.StatsAggregateJobImpl)
> INFO: Finishing job: DEFAULT.SiteStats Event Aggregator (2009-09-21
> 08:47:10,849
> QuartzScheduler_Worker-1_org.sakaiproject.sitestats.impl.StatsAggregateJobImpl)
>
>
> Sean Keesler
> 130 Academy Street
> Manlius, New York 13104 USA
> 315-663-7756
> sean.keesler at threecanoes.com
>
>
> On Mon, Sep 21, 2009 at 7:53 AM, Nuno Fernandes <nuno at ufp.edu.pt> wrote:
>
>> Just a small correction on the previous email:
>>
>>> (...) and set that value plus one (+1) as the last event id on (...)
>>>
>> Should be:
>>
>> (...) and set that value as the last event id on (...)
>>
>> The provided SQL is correct.
>>
>> Thanks,
>> Nuno
>>
>>
>>> Since we can't specify target table 'SST_JOB_RUN' for update in FROM
>>> clause (as subqueries), we have to issue 3 sql queries:
>>>
>>>    - *(1)* select max(END_EVENT_ID) from SST_JOB_RUN)
>>>    - *(2)* select max(ID) from SST_JOB_RUN)
>>>
>>> The first (1) query will give you the last event id processed;
>>> The second (2) will give the last job id;
>>> Replace these on the query below and execute it:
>>>
>>>    - update SST_JOB_RUN set END_EVENT_ID = *(1)* where ID = *(2)*;
>>>
>>>
>>> However, I would like more information to understand why you have
>>> END_EVENT_ID = 0 on your last job run.
>>>
>>>    - Did you remove the entries from SST_JOB_RUN before the job started?
>>>    - Are there any related ERRORs on you catalina.out logs?
>>>    - Are your previous entries from SST_JOB_RUN equal to the one you
>>>    referred?
>>>
>>> It would be great if you could provide me you further information!
>>>
>>> Thank you,
>>> Nuno
>>>
>>>
>>> 2009/9/19 Sean Keesler <sean.keesler at threecanoes.com>
>>>
>>>> I am seeing a site stat job fail with the following log entries:
>>>>
>>>> INFO: Starting job: DEFAULT.SiteStats Event Aggregator (2009-09-18
>>>> 20:54:11,666
>>>> QuartzScheduler_Worker-3_org.sakaiproject.sitestats.impl.StatsAggregateJobImpl)
>>>> WARN: No events were returned - nothing to do. (2009-09-18 20:54:11,668
>>>> QuartzScheduler_Worker-3_org.sakaiproject.sitestats.impl.StatsAggregateJobImpl)
>>>> INFO: Summary: 0 events processed (ids: 0 - 0) in 0s (2009-09-18
>>>> 20:54:11,669
>>>> QuartzScheduler_Worker-3_org.sakaiproject.sitestats.impl.StatsAggregateJobImpl)
>>>> INFO: Finishing job: DEFAULT.SiteStats Event Aggregator (2009-09-18
>>>> 20:54:11,670
>>>> QuartzScheduler_Worker-3_org.sakaiproject.sitestats.impl.StatsAggregateJobImpl)
>>>>
>>>> The last record in SST_JOBS_RUN is:
>>>>
>>>> ID: 662
>>>> JOB_START_DATE: 2009-09-18 20:54:11
>>>> JOB_END_DATE: 2009-09-18 20:54:11
>>>> START_EVENT_ID: 0
>>>> END_EVENT_ID: 0
>>>> LAST_EVENT_DATE: NULL
>>>>
>>>> What is the right procedure to fix this?
>>>>
>>>>
>>>>
>>>> Sean Keesler
>>>> 130 Academy Street
>>>> Manlius, New York 13104 USA
>>>> 315-663-7756
>>>> sean.keesler at threecanoes.com
>>>>
>>>> _______________________________________________
>>>> sakai-dev mailing list
>>>> sakai-dev at collab.sakaiproject.org
>>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>>>
>>>> TO UNSUBSCRIBE: send email to
>>>> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of
>>>> "unsubscribe"
>>>>
>>>
>>>
>>>
>>> --
>>> Nuno Fernandes  [www.linkedin.com/in/nfgrilo]
>>> Software Developer - UFP-UV  [Universidade Virtual]
>>> Software Developer - Sakai Foundation  [sakaiproject.org]
>>> Sakai Fellow 2008 - Sakai Foundation  [
>>> confluence.sakaiproject.org/confluence/x/FIF9AQ]
>>>
>>> Universidade Fernando Pessoa  [www.ufp.pt]
>>> Praça 9 de Abril, 349
>>> 4249-004 Porto
>>> tel: + 351 22 507 13 00
>>> fax: + 351 22 550 82 69
>>>
>>
>>
>>
>> --
>> Nuno Fernandes  [www.linkedin.com/in/nfgrilo]
>> Software Developer - UFP-UV  [Universidade Virtual]
>> Software Developer - Sakai Foundation  [sakaiproject.org]
>> Sakai Fellow 2008 - Sakai Foundation  [
>> confluence.sakaiproject.org/confluence/x/FIF9AQ]
>>
>> Universidade Fernando Pessoa  [www.ufp.pt]
>> Praça 9 de Abril, 349
>> 4249-004 Porto
>> tel: + 351 22 507 13 00
>> fax: + 351 22 550 82 69
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090921/ca08a558/attachment.html

More information about the sakai-dev mailing list