[Building Sakai] Site Stats jobs that fail
Nuno Fernandes
nuno at ufp.edu.pt
Tue Sep 22 00:51:55 PDT 2009
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090922/792e2fbb/attachment.html
More information about the sakai-dev
mailing list