Control Compliance Suite - MySQL External Data Integration - Error: You have an error in your SQL syntax.

Article:TECH199666  |  Created: 2012-11-13  |  Updated: 2012-11-13  |  Article URL http://www.symantec.com/docs/TECH199666
Article Type
Technical Solution


Issue



After configuring a MySQL External Data Integration connection - during the data collection, the CCS Manager reports an error: You have an error in your SQL syntax. You double checked your syntax - you are sure you did not make an error.

 


Error



Date,Message,Details,Category,Asset,Asset type,Asset owner,
13/11/2012 10:37:16,Job Exception Occurred,SQL query execution failed: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.63-0+squeeze1]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 '1  * FROM MySQLDB.AssetView' at line 1,,,,,

 


Environment



Control Compliance Suite 10.x and 11.x

Windows 2003, 2008, 2008R2

MySQL

 


Cause



During the configuration of the data system within CCS, you selected the query type: "Table or View name" and entered the table or view name. Although this would work if you were to query a Microsoft SQL database, this does not seem to work for MySQL data connections.

 

 click to enlarge

Fig 1. This is how the schema, table and view shows in MySQL workbench.

 

 click to enlarge

Fig 2. This shows how that view is addressed in the Data System configuration when targeting a "table or View" and causes the error during the data collection job. Note: putting in the view here does allow you to continue and shows you the data in the table/view - the error only shows when the job is actually scheduled and run.

 


Solution



The current solution to this problem is to put in the SQL command that would query the view.

 

 click to enlarge

Fig 3. This is how the view is queried directly by means of an SQL command in the Data System configuration and no longer generates the error.

 

As you're reading this article, you might also be interested in the following articles:

External Data Integration - creating a MySQL Data Location - http://www.symantec.com/docs/TECH199623

MySQL External Data Integration - Error: The specified DSN contains an architecture mismatch between the Driver and Application. - http://www.symantec.com/docs/TECH199664

 




Article URL http://www.symantec.com/docs/TECH199666


Terms of use for this information are found in Legal Notices