Archive job error: java.sql.SQLException: The user specified as a definer ('root'@'%') does not exist

I am running an archive job but it fails with this error, which can be found in the Worker log:

14:08:36, 748[Store_1476360516717_117] [ERROR] ServerErrorHandlerStrategy: reportError: StoreEmail :: com.gwava.archive.StoreEmail.handleArchiveExceptions:739 :: EXCEPTION : org.hibernate.exception.GenericJDBCException: could not execute statement org.hibernate.exception.GenericJDBCException: could not execute statement
 at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
 at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
 at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
 at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211)
 at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:96)
 at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:58)
 at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3032)
 at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3558)
 at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:98)
 at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:492)
 at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:197)
 at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:181)
 at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:216)
 at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:334)
 at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:289)
 at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:195)
 at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:126)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:209)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:194)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:114)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
 at org.hibernate.internal.SessionImpl.fireSaveOrUpdate(SessionImpl.java:684)
 at org.hibernate.internal.SessionImpl.saveOrUpdate(SessionImpl.java:676)
 at org.hibernate.engine.spi.CascadingActions$5.cascade(CascadingActions.java:235)
 at org.hibernate.engine.internal.Cascade.cascadeToOne(Cascade.java:350)
 at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:293)
 at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:161)
 at org.hibernate.engine.internal.Cascade.cascadeCollectionElements(Cascade.java:379)
 at org.hibernate.engine.internal.Cascade.cascadeCollection(Cascade.java:319)
 at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:296)
 at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:161)
 at org.hibernate.engine.internal.Cascade.cascade(Cascade.java:118)
 at org.hibernate.event.internal.AbstractSaveEventListener.cascadeAfterSave(AbstractSaveEventListener.java:470)
 at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:295)
 at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:195)
 at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:126)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:209)
 at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:55)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:194)
 at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:49)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
 at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:715)
 at org.hibernate.internal.SessionImpl.save(SessionImpl.java:707)
 at org.hibernate.internal.SessionImpl.save(SessionImpl.java:702)
 at com.gwava.engine.RetainDBStorageEngine.insertMessage(RetainDBStorageEngine.java:1017)
 at com.gwava.engine.RetainDBStorageEngine.dbStore2(RetainDBStorageEngine.java:429)
 at com.gwava.engine.RetainDBStorageEngine.dbStore1(RetainDBStorageEngine.java:282)
 at com.gwava.engine.RetainDBStorageEngine.dbStore(RetainDBStorageEngine.java:155)
 at com.gwava.datastore.client.SingleProcessStorageEngine.archiveEmail(SingleProcessStorageEngine.java:101)
 at com.gwava.archive.StoreEmail.handlePUTInternal(StoreEmail.java:395)
 at com.gwava.archive.StoreEmail.handlePUT(StoreEmail.java:187)
 at org.apache.jsp.Server.index_jsp._jspService(index_jsp.java:152)
 at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
 at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
 at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:395)
 at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:339)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
 at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
 at com.gwava.Filters.ResponseFilter.doFilter(ResponseFilter.java:48)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
 at com.gwava.Filters.MainFilter.doFilter(MainFilter.java:313)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
 at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
 at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:190)
 at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
 at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
 at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: The user..
Caused by: java.sql.SQLException: The user specified as a definer ('root'@'%') does not exist

DISCLAIMER:
This knowledge base (KB) article is provided for informational purposes only and as a courtesy service to you, our customer. GWAVA Technical Support does not have any database administration (DBA) expertise, nor does it provide DBA services or support. GWAVA is not responsible for the results of implementing any of the concepts contained in this KB article. Implementation of any of the concepts suggested in this KB article shall be done entirely at your own and sole risk, and GWAVA does not provide any kind of warranties whatsoever resulting from your decision of implementing any of the KB article’s concepts. It is up to you to do any research and to ensure yourself that any implementation and setup of any of the KB article’s concepts in your database system is correctly and properly executed. It is imperative that you have backups of your database system and storage directory before making any implementation. If you don’t have any DBA expertise, you should consult with a DBA expert before any implementation of the KB article’s concepts.  Under no circumstances, shall GWAVA, or any of its employees, be liable, in contract, tort, delict or otherwise, whether negligence is provable or not, for any direct, indirect, incidental, special, punitive, consequential or other damages, loss, cost or liability whatsoever that would result from or are related to the implementation of any of the concepts suggested in the KB article.

To the extent permitted by applicable law, GWAVA shall not be liable to you for any special, consequential, direct, indirect or similar damages, including any loss of data, arising out from migrating any type of messages, attachments, database, metadata in your Retain system to another server and/or location.

Resolution:
The customer recently upgraded MySQL from 5.5 to 5.6.  The MySQL root user credentials were messed up.  To fix this, we had to re-create the Retain database triggers:

1. Back up your Retain database
2. At a terminal prompt on the Retain Linux server, type:  mysqldump -u root -p --triggers --add-drop-trigger --no-create-info --no-data --no-create-db --skip-opt [retain db name] > /tmp/triggers.sql (make sure the "/tmp" path exists).
3. Open triggers.sql into a text editor of your choice.  Do a Search and Replace:  Find: DEFINER=`root`@`%`  Replace With: DEFINER=`root`@`localhost`
4. Save the file (i.e., triggers-new.sql).
5. Import the triggers-new.sql file into the database:  mysql -u root -p [retain db name] < [path]/triggers-new.sql

This article was originally published in the GWAVA knowledgebase as article ID 2871.