MySQL Bugs: #17178: Microsoft Project 2003 Fails on MySQL ODBC datasource

Bug #17178	Microsoft Project 2003 Fails on MySQL ODBC datasource
Submitted:	7 Feb 2006 6:01	Modified:	2 Sep 2009 7:33
Reporter:	Jonathan Siegel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S4 (Feature request)
Version:	3.51.12	OS:	Linux (RHEL 4.0)
Assigned to:		CPU Architecture:	Any

Description:
Microsoft Project can store its data into an ODBC compliant database. This works flawless with MSSQL, but fails during save attempts with MySQL as the ODBC datasource with the errors:

[MySQL][ODBC 3.51 Driver][mysqld-5.0.17-standard]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 'LONGCHAR , PROJ_OPT_CALC_ACT_COSTS BIT , PROJ_PROP_CATEGORY LONGCHAR , PROJ_PR' at line 1

When running MySQL 5.0.17 on a linux server. And:

[MySQL][ODBC 3.51 Driver][mysqld-4.1.10a-log]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 'LONGCHAR , PROJ_OPT_CALC_ACT_COSTS BIT , PROJ_PROP_CATEGORY LONGCHAR , PROJ_PR' at line 1

When running on MySQL 4.1. This behavior is observed on mysql user accounts that require passwords and with mysql user accounts that have empty passwords.

How to repeat:
On client:
Install Microsoft Project 2003 (v11) and current XP service packs.
Install MySQL ODBC Driver 3.51.

On MySQL server:
Create empty database and authorized user.

On Client:
Open Project 2003.
File->Open->ODBC->Machine Data Source->New
System Data Source->Next->MySQL 3.51 Driver->Next->Finish
Fill out Login form, then test. Should see "success" message.
Cancel->Cancel to return to Project.
File->Save->ODBC->Machine Data Source->Select Datasource configured above->OK
Project Export Wizard Appears->Next->A Full Project->Next->Project1->Finish

ERROR APPEARS! Normal operation would save data into ODBC-compliant database.

Suggested fix:
Implement the required SQL operations to support MS Project data storage.

Thank you for the problem report. Please, inform about the exact version of Connector/ODBC used. 

It may be a server bug and/or just improper settings or incorrect column name for MySQL. So, please, try to figure out what exact SQL statement gives you that error message. You can do it by enabling trace for the MySQL datasource and analysing trace file, or using the general query log on the MySQL server. Upload ttrace file and/or general query log to this report.

I enabled logging on MySQL server and found the query that is causing the issue. My understanding is that MS Project queries the database initially to see if an existing schema is in place--and if none is found, it creates the schema. This is how MS Project works with MS SQL when used in the same fashion. This is the entire log contents up until the error occurs on the CREATE statement:

060208 15:02:38   22751 Connect     --- on test_odbc
060208 15:02:39   22751 Query       show tables
      22751 Query       select PROJ_NAME, PROJ_PROP_AUTHOR, PROJ_LAST_SAVED from MSP_PROJECTS
      22751 Query       select PROJ_NAME, PROJ_PROP_AUTHOR, PROJ_LAST_SAVED from MSP_PROJECTS
060208 15:02:41   22751 Query       select PROJ_NAME, PROJ_PROP_AUTHOR, PROJ_LAST_SAVED from MSP_PROJECTS
060208 15:02:42   22751 Query       show tables like 'MSP_PROJECTS'
      22751 Query       create table MSP_PROJECTS ( PROJ_ID  INTEGER , PROJ_NAME VARCHAR ( 255 )  , PROJ_PROP_AUTHOR  LONGCHAR , PROJ_OPT_CALC_ACT_COSTS  BIT , PROJ_PROP_CATEGORY  LONGCHAR , PROJ_PROP_COMPANY  LONGCHAR , PROJ_OPT_CRITICAL_SLACK_LIMIT  INTEGER , PROJ_OPT_CURRENCY_DIGITS  SMALLINT , PROJ_OPT_CURRENCY_POSITION  SMALLINT , PROJ_OPT_CURRENCY_SYMBOL VARCHAR ( 20 )  , PROJ_OPT_NEW_ARE_EFFORT_DRIVEN  BIT , PROJ_INFO_CURRENT_DATE  DATETIME , PROJ_OPT_DEF_FINISH_TIME  DATETIME , PROJ_OPT_DEF_FIX_COST_ACCRUAL  SMALLINT , PROJ_OPT_MINUTES_PER_DAY  INTEGER , PROJ_OPT_MINUTES_PER_WEEK  INTEGER , PROJ_OPT_DEF_OVT_RATE  DOUBLE , PROJ_OPT_DEF_STD_RATE  DOUBLE , PROJ_OPT_DEF_START_TIME  DATETIME , PROJ_OPT_DEF_TASK_TYPE  SMALLINT , PROJ_OPT_DUR_ENTRY_FMT  SMALLINT , PROJ_INFO_FINISH_DATE  DATETIME , PROJ_OPT_HONOR_CONSTRAINTS  BIT , PROJ_PROP_KEYWORDS  LONGCHAR , PROJ_LAST_SAVED  DATETIME , PROJ_PROP_MANAGER  LONGCHAR , PROJ_OPT_MULT_CRITICAL_PATHS  BIT , PROJ_CREATION_DATE  DATETIME , PROJ_POOL_ATTACHED_TO  LONGCHAR , PROJ_INFO_CAL_NAME  LONGCHAR , PROJ_IS_RES_POOL  BIT , PROJ_INFO_SCHED_FROM  SMALLINT , PROJ_OPT_SPLIT_IN_PROGRESS  BIT , PROJ_OPT_SPREAD_ACT_COSTS  BIT , PROJ_OPT_SPREAD_PCT_COMP  BIT , PROJ_INFO_START_DATE  DATETIME , PROJ_INFO_STATUS_DATE  DATETIME , PROJ_PROP_SUBJECT  LONGCHAR , PROJ_PROP_TITLE  LONGCHAR , PROJ_OPT_TASK_UPDATES_RES  BIT , PROJ_OPT_WORK_ENTRY_FMT  SMALLINT , PROJ_OPT_CALC_SUB_AS_SUMMARY  BIT , PROJ_OPT_WEEK_START_DAY  SMALLINT , PROJ_OPT_FY_START_MONTH  SMALLINT , PROJ_OPT_FY_USE_START_YR  BIT , PROJ_OPT_DAYS_PER_MONTH  SMALLINT , PROJ_OPT_NEW_TASK_EST  BIT , PROJ_OPT_SHOW_EST_DUR  BIT , PROJ_OPT_EXPAND_TIMEPHASED  BIT , PROJ_PROJECT VARCHAR ( 203 )  , PROJ_VERSION VARCHAR ( 50 )  , PROJ_CREATION_DATE_EX  DATETIME , PROJ_ACTUALS_SYNCH  SMALLINT , PROJ_ADMINPROJECT  SMALLINT , PROJ_ENT_LIST_SEPARATOR VARCHAR ( 2 )  , PROJ_EXT_EDITED_DATE  BIT , PROJ_EXT_EDITED_DUR  BIT , PROJ_EXT_EDITED_NUM  BIT , PROJ_EXT_EDITED_FLAG  BIT , PROJ_EXT_EDITED_CODE  BIT , PROJ_EXT_EDITED_TEXT  BIT , PROJ_IGNORE_FRONT_END  BIT , PROJ_EXT_EDITED  BIT , PROJ_DATA_SOURCE VARCHAR ( 128 )  , PROJ_READ_ONLY VARCHAR ( 10 )  , PROJ_READ_WRITE VARCHAR ( 4 )  , PROJ_READ_COUNT VARCHAR ( 10 )  , PROJ_LOCKED VARCHAR ( 4 )  , PROJ_MACHINE_ID VARCHAR ( 100 )  , PROJ_TYPE  INTEGER , PROJ_CHECKEDOUT  INTEGER , PROJ_CHECKEDOUTBY VARCHAR ( 255 )  , PROJ_CHECKEDOUTDATE  DATETIME , RESERVED_BINARY_DATA  LONGBINARY )
060208 15:02:43   22751 Query       show tables like 'MSP_PROJECTS'
      22751 Quit

Changing LONGCHAR to TEXT and LONGBINARY to BLOB and manually running the CREAT TABLE query seems to get past this error and to the next one. That is, MS Project sees the existence of the MSP_PROJECT table and goes on to try and create the MSP_ASSN_ENTERPRISE table. It then fails with an error on LONGBINARY in MSP_ASSN_ENTERPRISE. I will continue to hand-enter these queries to see how far I can take things, but it seems that a simple alias of the types above may match MySQL's typing system to handle MS SQL's. Can this be done in configuration?

MySQL server does not support LONGCHAR and LONGBINARY data types now, so it looks like your idea on manualy changing all the offending statements one by one is the only way to go. 

You can make a feature request out of this bug report and I'll verify it. I'll also keep loking for any ability to remap that data types at the MyODBC level, but I don't know about any one yet.

Support for LONGCHAR and LONGBINARY types (or allowing mappings such as LONGCHAR->TEXT and LONGBINARY->BLOB) would provide compatibility with MS Project's ODBC support. [Can someone post a pointer to where in the source this might be patched for a short-term workaround?]

I too, would like to see a work around or implementation for this.  Can anyone confirm one ?

I am not sure there is anything we can do if MSProject is hardcoded against MSSQL server. But I've downloaded MSProject2007 and will see what can be done.

It appears that Microsoft has decided to *exclude* such option from latest release of it's product (Project 2007) thus making this feature request obsolete:

  In Microsoft Office Project 2007, you cannot save projects by using Open Database Connectivity (ODBC) (ODBC: A vendor-neutral interface, based on the SQL Access Group specifications announced by Microsoft. A developer can use ODBC to gain access to data in a heterogeneous environment of relational and nonrelational databases.). This includes saving your project to an Oracle or Microsoft SQL Server database. This also includes saving to a server as a Microsoft Office Project database (MPD file format (MPD file format: The Microsoft Project Database [MPD] file format is a record-based Access-compliant file format that you can use for saving an entire project. Files saved in this format have an .mpd extension.)) or a Microsoft Office Access database (MDB file format), as both of these formats use ODBC as the underlying technology. We recommend saving the project as an XML (XML: Extensible Markup Language [XML] is a format for delivering rich, structured data from an application in a standard, consistent way. XML describes the content of a document, whereas HTML tags describe how the document looks.) file if you need to read and write project data outside of Office Project 2007.

Closing the report as the feature requested can not be used with latest version of MS Projects.