Import issues when using MySQL back-end

After a bit of trial-and-error, I thought I had the 1.2.1 version of the app working with a MySQL back-end using the following config:

dataSource.pooled = true
dataSource.dialect = org.hibernate.dialect.MySQLDialect
dataSource.driverClassName = com.mysql.jdbc.Driver
dataSource.username = rundeck
dataSource.password = awesomerundeckpassword
dataSource.url = jdbc:mysql://primaryserver,slaveserver:3306/rundeck?autoCommit=true&autoReconnect=true

On startup, I saw this error, which seems related to ticket #258 Postgresql issue with use of reserved key words ("option" is a reserved word in MySQL land):

ERROR SchemaUpdate: Unsuccessful: create table option (id bigint not null auto_increment, version bigint not null, values_url tinyblob, scheduled_execution_id bigint, regex varchar(255), delimiter varchar(255), description varchar(255), name varchar(255) not null, enforced bit not null, default_value varchar(255), required bit, multivalued bit, primary key (id))
ERROR SchemaUpdate: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option (id bigint not null auto_increment, version bigint not null, values_url t' at line 1
ERROR SchemaUpdate: Unsuccessful: alter table option add index FKC3CA8875243CF2FF (scheduled_execution_id), add constraint FKC3CA8875243CF2FF foreign key (scheduled_execution_id) references scheduled_execution (id)
ERROR SchemaUpdate: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option add index FKC3CA8875243CF2FF (scheduled_execution_id), add constraint FKC' at line 1
ERROR SchemaUpdate: Unsuccessful: alter table option_values add index FKCF0B942CFB2E8DBA (option_id), add constraint FKCF0B942CFB2E8DBA foreign key (option_id) references option (id)
ERROR SchemaUpdate: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option (id)' at line 1

I temporarily sidestepped this issue by creating the table as rundeck.option, which got MySQL to do the right thing. I modified the other ALTER TABLE commands to use the same syntax.

Now Rundeck starts without incident. I try to load an XML dump of all my jobs into the now-empty database with rd-jobs, but I get the following error:

$ rd-jobs load -f everything.xml
ERROR WebserviceHttpClientChannel: Unable to parse result document: Error on line 1 of document  : The markup in the document following the root element must be well-formed. Nested exception: The markup in the document following the root element must be well-formed.
org.dom4j.DocumentException: Error on line 1 of document  : The markup in the document following the root element must be well-formed. Nested exception: The markup in the document following the root element must be well-formed.
        at org.dom4j.io.SAXReader.read(SAXReader.java:482)
        at org.dom4j.io.SAXReader.read(SAXReader.java:365)
        at com.dtolabs.client.utils.WebserviceHttpClientChannel.postMakeRequest(WebserviceHttpClientChannel.java:238)
        at com.dtolabs.client.utils.HttpClientChannel.makeRequest(HttpClientChannel.java:377)
        at com.dtolabs.client.services.ServerService.makeRundeckRequest(ServerService.java:115)
        at com.dtolabs.client.services.RundeckCentralDispatcher.loadJobs(RundeckCentralDispatcher.java:808)
        at com.dtolabs.rundeck.core.cli.jobs.JobsTool.loadAction(JobsTool.java:616)
        at com.dtolabs.rundeck.core.cli.jobs.JobsTool.go(JobsTool.java:589)
        at com.dtolabs.rundeck.core.cli.BaseTool.run(BaseTool.java:107)
        at com.dtolabs.rundeck.core.cli.jobs.JobsTool.main(JobsTool.java:368)
Nested exception:
org.xml.sax.SAXParseException: The markup in the document following the root element must be well-formed.
        at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
        at org.dom4j.io.SAXReader.read(SAXReader.java:465)
        at org.dom4j.io.SAXReader.read(SAXReader.java:365)
        at com.dtolabs.client.utils.WebserviceHttpClientChannel.postMakeRequest(WebserviceHttpClientChannel.java:238)
        at com.dtolabs.client.utils.HttpClientChannel.makeRequest(HttpClientChannel.java:377)
        at com.dtolabs.client.services.ServerService.makeRundeckRequest(ServerService.java:115)
        at com.dtolabs.client.services.RundeckCentralDispatcher.loadJobs(RundeckCentralDispatcher.java:808)
        at com.dtolabs.rundeck.core.cli.jobs.JobsTool.loadAction(JobsTool.java:616)
        at com.dtolabs.rundeck.core.cli.jobs.JobsTool.go(JobsTool.java:589)
        at com.dtolabs.rundeck.core.cli.BaseTool.run(BaseTool.java:107)
        at com.dtolabs.rundeck.core.cli.jobs.JobsTool.main(JobsTool.java:368)
Error: Failed request to load jobs: Response content unexpectedly empty. Jobs Uploaded. Succeeded: 0, Failed: 5, Skipped: 0

The same document imports into the app if I flip the storage back end back to HyperSQL.

Also, the "who's online" example job XML from the documentation imports successfully.

Digging into service.log, I see this:

Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'adhoc_local_string' at row 1
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2932)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3255)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1293)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1566)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1480)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1465)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
        at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:94)
        at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57)
        ... 261 more

This seems to indicate that queries aren't being constructed properly for MySQL to handle them... specifically, that a column isn't big enough to handle the data being inserted into it (maybe something's using a VARCHAR instead of a TEXT ?).

Anyway, I hope this report at least gives you a starting point to find a solution. If you need any further information, please let me know.