[Building Sakai] Site Stats jobs that fail
Sean Keesler
sean.keesler at threecanoes.com
Mon Sep 21 13:47:21 PDT 2009
Doesn't this seem odd? It looks like the last successful quartz job
processed SST_EVENT records that had higher ID's than currently exist
in that table...
mysql> select max(SST_EVENTS.ID) from SST_EVENTS;
+--------------------+
| max(SST_EVENTS.ID) |
+--------------------+
| 612959 |
+--------------------+
mysql> select max(END_EVENT_ID) from SST_JOB_RUN;
+-------------------+
| max(END_EVENT_ID) |
+-------------------+
| 37184210 |
+-------------------+
Sean Keesler
130 Academy Street
Manlius, New York 13104 USA
315-663-7756
sean.keesler at threecanoes.com
On Mon, Sep 21, 2009 at 12:06 PM, Nuno Fernandes <nuno at ufp.edu.pt> wrote:
> Hi Sean,
> "ERROR: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
> (utf8_general_ci,COERCIBLE) for operation '='"
> Yes, this error is due to a bad Sakai DB initial setup. You should have
> created your db as UTF-8, as documentation refers.
>
> I am not a DB expert so I suggest to write a new email to sakai-dev asking
> for advices on this specific issue. Also, I suspect you may have trouble
> with other tools while not having your DB in UTF-8!
> Nuno
>
> 2009/9/21 Sean Keesler <sean.keesler at threecanoes.com>
>>
>> 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
>>>
>>
>
>
>
> --
> 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
>
More information about the sakai-dev
mailing list