[Building Sakai] Site Stats jobs that fail
Nuno Fernandes
nuno at ufp.edu.pt
Mon Sep 21 09:06:29 PDT 2009
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090921/2835b7af/attachment.html
More information about the sakai-dev
mailing list