[Building Sakai] Site Stats jobs that fail
Sean Keesler
sean.keesler at threecanoes.com
Tue Sep 22 05:56:38 PDT 2009
Ignoring the obvious problem with the db...I think I need to
understand what that job is doing.
I thought that the quartz job would process any new SST_EVENTS records
(those with ID's > END_EVENT_ID) and store the results in
SST_SITEACTIVITY and update the SST_JOB_RUN table with the latest
END_EVENT_ID.
Sean Keesler
130 Academy Street
Manlius, New York 13104 USA
315-663-7756
sean.keesler at threecanoes.com
On Tue, Sep 22, 2009 at 3:51 AM, Nuno Fernandes <nuno at ufp.edu.pt> wrote:
> Hi Sean,
>
>> 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.
>
> No. The SST_EVENTS.ID is just an table identifier totally unrelated with the
> SST_JOB_RUN or SAKAI_EVENT tables. The SST_JOB_RUN.END_EVENT_ID field is
> related with SAKAI_EVENT table, meaning that the last SAKAI_EVENT row
> processed by the job has SAKAI_EVENT.ID = 37184210.
> You should really try to solve you DB charset issue or you will probably
> find failures on other Sakai parts.
> Some threads that may be of interest:
>
> http://forums.mysql.com/read.php?26,6528,6528
> http://forums.mysql.com/read.php?103,27204,27204#msg-27204
>
> Hope it helps,
> Nuno
>
>>
>> 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 |
>> +-------------------+
>>
>> How could the last job have
>>
>>
>> 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
>
>
>
> --
> 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