Apache James 2.3.2 won't write to MySQL

Question

I had a working Apache James mail server up and running, but just converted it to use a MySQL database instead of the filesystem.
It created (some) the tables, but it won't write to them. This makes it not deliver any mail.

Since it will interact with the database and insert users, I think I have my database data-source and user configured correctly...

<data-source name="maildb" class="org.apache.james.util.dbcp.JdbcDataSource">
    <driver>com.mysql.jdbc.Driver</driver>
    <dburl>jdbc:mysql://127.0.0.1/james</dburl>
    <user>xxxx</user>
    <password>yyyy</password>
    <max>20</max>
</data-source>

But when I try to send an email, I get no error from the client, but I get nothing in the database in any table. All I get is this error on James console...

exception! javax.mail.MessagingException: Exception caught while storing mail Container: ;
  nested exception is:
        com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'last_updated' at row 1

Here's the full stack trace from mailet log...

10/12/14 16:23:18 INFO  James.Mailet: Error while storing mail.
javax.mail.MessagingException: Exception caught while storing mail Container: ;
  nested exception is:
    com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'last_updated' at row 1
    at org.apache.james.mailrepository.JDBCMailRepository.store(JDBCMailRepository.java:764)
    at org.apache.james.transport.mailets.ToMultiRepository.storeMail(ToMultiRepository.java:230)
    at org.apache.james.transport.mailets.ToMultiRepository.service(ToMultiRepository.java:154)
    at org.apache.james.transport.mailets.LocalDelivery.service(LocalDelivery.java:66)
    at org.apache.james.transport.LinearProcessor.service(LinearProcessor.java:424)
    at org.apache.james.transport.JamesSpoolManager.process(JamesSpoolManager.java:405)
    at org.apache.james.transport.JamesSpoolManager.run(JamesSpoolManager.java:309)
    at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'last_updated' at row 1
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2973)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
    at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1129)
    at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:681)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:803)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
    at org.apache.james.mailrepository.JDBCMailRepository.store(JDBCMailRepository.java:752)
10/12/14 16:23:18 INFO  James.Mailet: ToRepository: Storing mail Mail1418257398530-1 in dbfile://maildb/deadletter/error

Here is how I specified the repository...

<mailet match="All" class="ToRepository">
    <repositoryPath> db://maildb/deadletter/error </repositoryPath>
</mailet>

Since it seems to be complaining about an empty last_updated datetime, I made that column null allowed, but that didn't help. I'm not changing the java in any way, so there shouldn't be anything I messed up. I don't even know where it gets the datetime.

Can anyone see something I've missed?

score 0 · Accepted Answer · answered Dec 12 '14 at 19:09

Solved: I found an obscure post in the Apache bug archives that mentions adding this parm...

<dburl>jdbc:mysql://127.0.0.1/james?useServerPrepStmts=false</dburl>

...to the data-source area. Actually, the post says to add 2 parms here, but I found that it will only accept one parm.

I don't know how I was supposed to know about those parms or what they do, but this made everything magically work. Sometimes using open source software teaches you more about researching than programming.

Apache James 2.3.2 won't write to MySQL

1 Answers1