Content Type: Cannot delete Text Area fields in MSSQL

[joseorsini]

Expected Behavior

As of 3.6.0, a background job should take care of a batch cleanup of a field once it's requested. Text Area fields should be deleted, along with cleaning up the related column on contentlet table

Current Behavior

This is failing for customers running dotCMS 3.6.1 with SQL Server databases. It's caused by a mismatch between the operator allowed by text columns (in SQL Server, text_area fields are text data type columns).

Possible Solution

Change the operators for text fields From =/!= to LIKE/NOT LIKE. Data comparison is made against empty strings.

Known Workarounds

None.

Steps to Reproduce

1. Start up master-3.7 or 3.6.1 release pointing to a SQL Server Database.
2. Apply an Enterprise License.
3. Create a Test Structure with two fields:
   • Title: Text field, required, indexed, user-searchable.
   • Description: text area field.
4. Create several contents for this content type. ON tests made locally, 2500 contents were created via Import Content tool.
5. Go to Content Type definition and delete the text area field.

Stacktrace:

[06/01/17 12:37:43:437 EST]  INFO job.DeleteFieldJob: triggerJobImmediately : Deleting Field 'description' for Structure with id: 7bb66a89-03f0-444b-8562-a6187f76c336
[06/01/17 12:37:43:467 EST] ERROR job.DeleteFieldJob: Unable to delete field 'description'. Field Inode: 6d88c019-4986-427e-ad22-69da77b3a05d, Structure Inode: 7bb66a89-03f0-444b-8562-a6187f76c336
com.dotmarketing.exception.DotDataException: Error Clearing Field 'description' for Structure with id: 7bb66a89-03f0-444b-8562-a6187f76c336
	at com.dotcms.content.elasticsearch.business.ESContentFactoryImpl.clearField(ESContentFactoryImpl.java:2293) ~[dotcms_3.6.1_cfa5586.jar:?]
	at com.dotcms.content.elasticsearch.business.ESContentletAPIImpl.cleanField(ESContentletAPIImpl.java:845) ~[dotcms_3.6.1_cfa5586.jar:?]
	at com.dotmarketing.portlets.contentlet.business.ContentletAPIInterceptor.cleanField(ContentletAPIInterceptor.java:385) ~[dotcms_3.6.1_cfa5586.jar:?]
	at com.dotmarketing.quartz.job.DeleteFieldJob.execute(DeleteFieldJob.java:116) [dotcms_3.6.1_cfa5586.jar:?]
	at org.quartz.core.JobRunShell.run(JobRunShell.java:223) [dot.quartz-all-1.8.6_2.jar:?]
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549) [dot.quartz-all-1.8.6_2.jar:?]
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The data types text and varchar are incompatible in the not equal to operator.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217) ~[sqljdbc42.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635) ~[sqljdbc42.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426) ~[sqljdbc42.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372) ~[sqljdbc42.jar:?]
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276) ~[sqljdbc42.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1793) ~[sqljdbc42.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184) ~[sqljdbc42.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159) ~[sqljdbc42.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:284) ~[sqljdbc42.jar:?]
	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82) ~[tomcat-dbcp.jar:8.0.18]
	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82) ~[tomcat-dbcp.jar:8.0.18]
	at com.dotcms.content.elasticsearch.business.ESContentFactoryImpl.clearField(ESContentFactoryImpl.java:2275) ~[dotcms_3.6.1_cfa5586.jar:?]
	... 5 more

Context

Reported by customer running dotCMS 3.6.1 with SQL Server Database.
https://my.dotcms.com/tickets/detail.dot?id=08ba3cda-a078-43d7-9259-7606cef2b276
Reproduced locally running 3.6.1 with SQL Server Database.

Your Environment

• dotCMS version used: 3.6.1.
• Browser Name and version: Google Chrome
• Operating System and version: App is running on macOS Sierra, DB is hosted on Windows Server 2016 VM.
• Application Server and version: Tomcat 8.0.18.
• Java Brand and version: Oracle Java 8.
• Database and version: Microsoft SQL Server 2014.

[joseorsini]

still working on this.

[joseorsini]

PR: #10399