web stats
Server Log: Could not prune message database - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 01-19-2009, 01:07 PM
don don is offline
Mirth Newb
 
Join Date: Jan 2009
Posts: 7
don
Default Server Log: Could not prune message database

Since going up on version 1.8, I've noticed that every hour I get the following warning regardless of whether there are messages to be "pruned" or not. I'm running the service on Windows and hitting again SQL Server 2005. There are no other errors being generated, and the server has no problem writing messages to the database. Just wondering what might be causing the warnings. As long as there's a channel running, the errors are generated. The channels are all configured to prune messages after 30 days, which naturally won't be for a while yet. I tried changing to 1 day, and it was unable to prune the messages. I can stop/start the service and it generates the warning on startup. Never saw this warning on version 1.7.
Thanks.


[2009-01-19 15:40:25,249] WARN (com.webreach.mirth.plugins.messagepruner.MessageP runerService:260): could not prune message database
com.webreach.mirth.server.controllers.ControllerEx ception: com.ibatis.common.jdbc.exception.NestedSQLExceptio n:
--- The error occurred while applying a parameter map.
--- Check the Message.pruneMessages-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Incorrect syntax near '@P0'.
Caused by: java.sql.SQLException: Incorrect syntax near '@P0'.
at com.webreach.mirth.server.controllers.DefaultMessa geObjectController.pruneMessages(DefaultMessageObj ectController.java:387)
at com.webreach.mirth.plugins.messagepruner.MessagePr unerService.execute(MessagePrunerService.java:245)
at org.quartz.core.JobRunShell.run(JobRunShell.java:2 02)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run (SimpleThreadPool.java:529)Caused by: com.ibatis.common.jdbc.exception.NestedSQLExceptio n:
--- The error occurred while applying a parameter map.
--- Check the Message.pruneMessages-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Incorrect syntax near '@P0'.
Caused by: java.sql.SQLException: Incorrect syntax near '@P0'.
at com.ibatis.sqlmap.engine.mapping.statement.General Statement.executeUpdate(GeneralStatement.java:91)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelega te.update(SqlMapExecutorDelegate.java:505)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelega te.delete(SqlMapExecutorDelegate.java:528)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.de lete(SqlMapSessionImpl.java:98)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.del ete(SqlMapClientImpl.java:75)
at com.webreach.mirth.server.controllers.DefaultMessa geObjectController.pruneMessages(DefaultMessageObj ectController.java:370)
... 3 moreCaused by: java.sql.SQLException: Incorrect syntax near '@P0'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnos tic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.processRes ults(JtdsStatement.java:525)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL (JtdsStatement.java:487)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.ex ecute(JtdsPreparedStatement.java:475)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.exe cuteUpdate(SqlExecutor.java:81)
at com.ibatis.sqlmap.engine.mapping.statement.General Statement.sqlExecuteUpdate(GeneralStatement.java:2 00)
at com.ibatis.sqlmap.engine.mapping.statement.General Statement.executeUpdate(GeneralStatement.java:78)
... 8 more
Reply With Quote
  #2  
Old 01-20-2009, 01:51 PM
ccitrano@tangible.com ccitrano@tangible.com is offline
OBX.3 Kenobi
 
Join Date: Apr 2008
Posts: 122
ccitrano@tangible.com
Default Re:Server Log: Could not prune message database

I'm getting the exact same message. My install was a clean install.

I exported Channels, deleted database, uninstalled 1.7, installed 1.8, recreated new SQL 2005 database, imported channels.

Everything else seems to be working fine.

Thanks.
Chuck.
__________________
Chuck Citrano
ccitrano at tangible.com
[url][/http://www.tangible.comurl]
Reply With Quote
  #3  
Old 01-20-2009, 02:24 PM
jacobb jacobb is offline
Mirth Employee
 
Join Date: Aug 2006
Location: Irvine, CA
Posts: 1,218
jacobb is an unknown quantity at this point
Default Re:Server Log: Could not prune message database

Good catch. This will be fixed in 1.8.1. If you'd like to fix it in the meantime, you can edit sqlserver2005-message.xml and sqlserver2005-message.xml in the conf/sqlserver2005 directory. Simply change line 290 (5th from the bottom) from "SELECT TOP #limit# ID FROM MESSAGE" to "SELECT TOP (#limit#) ID FROM MESSAGE". Let me know if this fixes your problem.
__________________
Jacob Brauer
Director, Software Development
NextGen Healthcare

Reply With Quote
  #4  
Old 01-21-2009, 05:29 AM
don don is offline
Mirth Newb
 
Join Date: Jan 2009
Posts: 7
don
Default Re:Server Log: Could not prune message database

That looks like a winner...I'm no longer getting the warning in the server log. You all are doing an awesome job with Mirth. Thanks for your help!
Reply With Quote
  #5  
Old 03-24-2009, 04:24 PM
jasonschulz jasonschulz is offline
What's HL7?
 
Join Date: Sep 2008
Posts: 3
jasonschulz
Default

This fix does not seem to work for SQL 2000. Is there a similar fix available for 2000?

Also, I was getting error messages when using the "Quick Search" or the "Containing" fields of the log search. In my case I was able to fix this by editing sqlserver2005-message.xml and sqlserver-message.xml and replacing all instances of:

UPPER(RAW_DATA) or UPPER(ENCODED_DATA) or UPPER(TRANSFORMED_DATA)

with:

UPPER(SUBSTRING(RAW_DATA, 1, 4000)) or UPPER(SUBSTRING(ENCODED_DATA, 1, 4000)) or UPPER(SUBSTRING(TRANSFORMED_DATA, 1, 4000)) respectively. Is that going to cause any issues?

Thanks,
Jason
Reply With Quote
  #6  
Old 03-24-2009, 04:59 PM
jasonschulz jasonschulz is offline
What's HL7?
 
Join Date: Sep 2008
Posts: 3
jasonschulz
Default

This is the error you get on SQL 2000 when you replace #limit# with (#limit#)

[2009-03-24 20:08:52,365] WARN (com.webreach.mirth.plugins.messagepruner.MessageP runerService:260): could not prune message database
com.webreach.mirth.server.controllers.ControllerEx ception: com.ibatis.common.jdbc.exception.NestedSQLExceptio n:
--- The error occurred while applying a parameter map.
--- Check the Message.pruneMessages-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Line 1: Incorrect syntax near '('.
Caused by: java.sql.SQLException: Line 1: Incorrect syntax near '('.
at com.webreach.mirth.server.controllers.DefaultMessa geObjectController.pruneMessages(DefaultMessageObj ectController.java:387)
at com.webreach.mirth.plugins.messagepruner.MessagePr unerService.execute(MessagePrunerService.java:245)
at org.quartz.core.JobRunShell.run(JobRunShell.java:2 02)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run (SimpleThreadPool.java:529)Caused by: com.ibatis.common.jdbc.exception.NestedSQLExceptio n:
--- The error occurred while applying a parameter map.
--- Check the Message.pruneMessages-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Line 1: Incorrect syntax near '('.
Caused by: java.sql.SQLException: Line 1: Incorrect syntax near '('.
at com.ibatis.sqlmap.engine.mapping.statement.General Statement.executeUpdate(GeneralStatement.java:91)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelega te.update(SqlMapExecutorDelegate.java:505)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelega te.delete(SqlMapExecutorDelegate.java:528)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.de lete(SqlMapSessionImpl.java:98)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.del ete(SqlMapClientImpl.java:75)
at com.webreach.mirth.server.controllers.DefaultMessa geObjectController.pruneMessages(DefaultMessageObj ectController.java:370)
... 3 moreCaused by: java.sql.SQLException: Line 1: Incorrect syntax near '('.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnos tic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.processRes ults(JtdsStatement.java:525)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL (JtdsStatement.java:487)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.ex ecute(JtdsPreparedStatement.java:475)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.exe cuteUpdate(SqlExecutor.java:81)
at com.ibatis.sqlmap.engine.mapping.statement.General Statement.sqlExecuteUpdate(GeneralStatement.java:2 00)
at com.ibatis.sqlmap.engine.mapping.statement.General Statement.executeUpdate(GeneralStatement.java:78)
... 8 more
Reply With Quote
  #7  
Old 05-26-2009, 08:25 AM
joeluisdlg joeluisdlg is offline
OBX.1 Kenobi
 
Join Date: Oct 2008
Location: Houston, TX
Posts: 26
joeluisdlg
Default

Quote:
Originally Posted by don View Post
That looks like a winner...I'm no longer getting the warning in the server log. You all are doing an awesome job with Mirth. Thanks for your help!
This fixed my problem as well.
Thanks
Reply With Quote
  #8  
Old 06-16-2009, 11:40 AM
blueberry blueberry is offline
Mirth Newb
 
Join Date: Dec 2008
Posts: 8
blueberry
Default Re: SQL 2000 problems

Quote:
Originally Posted by jasonschulz View Post
This is the error you get on SQL 2000 when you replace #limit# with (#limit#)

<< lines snipped >>
Is there any way to find out what SQL command was being run to cause this problem? I also get this message and would like it fixed before I deploy it to a live server. I'm not a database expert but several of my colleagues are and I'm sure that one of them could assist if I knew what the command was!

Regards

John Davies
Reply With Quote
  #9  
Old 06-25-2009, 10:58 AM
wseay wseay is offline
OBX.1 Kenobi
 
Join Date: Dec 2007
Posts: 32
wseay is an unknown quantity at this point
Default

Quote:
Originally Posted by jasonschulz View Post
This is the error you get on SQL 2000 when you replace #limit# with (#limit#)

[2009-06-25 09:07:49,952] WARN (com.webreach.mirth.plugins.messagepruner.MessageP runerService:260): could not prune message database
com.webreach.mirth.server.controllers.ControllerEx ception: com.ibatis.common.jdbc.exception.NestedSQLExceptio n:
--- The error occurred while applying a parameter map.
--- Check the Message.pruneMessages-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Line 1: Incorrect syntax near '('.
Caused by: java.sql.SQLException: Line 1: Incorrect syntax near '('.
I tried the same fix for SQL Server 2000 and now I also get the same results as above. Has anyone been able to get this to work for SQL2000?

Please take a look at what is in the 1.8.1 RC1 and see if that works for you. Let me know.

Last edited by wseay; 06-25-2009 at 11:35 AM.
Reply With Quote
  #10  
Old 06-25-2009, 11:20 AM
jacobb jacobb is offline
Mirth Employee
 
Join Date: Aug 2006
Location: Irvine, CA
Posts: 1,218
jacobb is an unknown quantity at this point
Default

Quote:
Originally Posted by wseay View Post
I tried the same fix for SQL Server 2000 and now I also get the same results as above. Has anyone been able to get this to work for SQL2000?
SQL Server 2000 is the opposite. SQL Server 2000 can not have the parenthesis around the limit/top. SQL Server 2003+ I believe required the parenthesis because the top can now be an expression.
__________________
Jacob Brauer
Director, Software Development
NextGen Healthcare

Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error: could not prune message database pgturner Support 0 05-09-2008 01:57 PM
Error: could not prune message database pgturner Support 0 05-09-2008 01:57 PM
Could not prune database warning nshaik Support 9 09-18-2007 11:05 AM
Mirth 1.5-Could not prune database error nshaik Support 8 06-04-2007 02:28 PM
JDBC driver for SQL Server database in Mirt shifting_18 Support 5 04-25-2007 04:15 AM


All times are GMT -8. The time now is 10:46 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2020, vBulletin Solutions, Inc.
Mirth Corporation