| Bug #44018 | Unable to connect to a (MySQL) database in ShapePoint Designer 2007 | ||
|---|---|---|---|
| Submitted: | 1 Apr 2009 18:47 | Modified: | 29 May 2013 6:11 |
| Reporter: | Marcus Matthews | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | MySQL v5.1 & ODBC 5.1Driver | OS: | Windows (XP [Version 5.1.2600]) |
| Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
| Tags: | Dell PowerEdge, MOSS, MySQL ODBC 5.1 Driver, MySQL v5.1, SharePoint Designer 2007, Windows Server 2003 | ||
[1 Apr 2009 18:50]
Marcus Matthews
ODBC Trace Log
Attachment: SQL-1.zip (application/octet-stream, text), 20.30 KiB.
[1 Apr 2009 19:07]
Tonci Grgin
Hi Marcus and thanks for your report.
I have never used this SharePoint (designer and server) so you'll have to lead me here. In my MSDN subscriber downloads I see:
Applications(1)
* SharePoint Designer 2007
Developer Tools(1)
* SharePoint Services 3.0 Tools
Servers(5)
* Forefront Security for SharePoint
* SharePoint Server 2001
* SharePoint Server 2003
* SharePoint Server 2007
* SharePoint Server 2007 Search
Tools and Resources(2)
* SharePoint Products and Technologies
* Windows SharePoint Services
So what do I need to install and where and in what order?
I am not familiar with option "TRUSTED_CONNECTION = true" and I don't think c/ODBC is processing it. Where does it come from?
Attach please full description of Clients table. Is it a view or physical table? Please also check tables on MySQL server in case they are corrupted.
[1 Apr 2009 19:41]
Marcus Matthews
SharePoint Designer 2007 and SharePoint Services 3.0 Tools. The "TRUSTED_CONNECTION = true" was used and accepted in the connection to connect a control to a data source. It came from some forum I found for connection string formats. In the last update from the Tech - he said that the "TRUSTED_CONNECTION = true" did throw an exception -- but he couldn't tell if it occurred when trying to connect to a database or configure a data source for a control. Honestly, just about anything we put in the string worked. That may be something you might want to look at later on. We do not use (have) SharePoint Server or use any of the other tools and services. The Clients table is a physical table. Field Type Null Key Default Extra Clientname varchar(200) NO PRI Contact varchar(200) No Street1 varchar(200) NO Street2 varchar(200) NO City varchar(200) NO State varchar(5) NO Zip varchar(15) NO Phone varchar(15) NO Fax varchar(15) NO Email varchar(15) NO Clientstatus varchar(20) NO Quickbookssync varchar(200) NO
[1 Apr 2009 20:08]
Tonci Grgin
Marcus, of course "just about anything we put in the string worked" as c/ODBC is simply not processing that. Now, can you please attach mysqldump of table "Clients". Seems that some of the values in column "Clientname" are breaking processing. I do not think I need actual data for any of the other columns. If table contains sensitive data, you can attach dump as private and/or obfuscate data in other columns, but not in "Clientname" please. It would be good to attach my.ini file you use to start MySQL server as well as output of SHOW VARIABLES LIKE "%char%" (like this): mysql> show variables like "%char%"; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /opt/mysql/mysql/share/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.05 sec) I will download that SW you pointed out now and try it tomorrow.
[1 Apr 2009 20:56]
Marcus Matthews
Tonci, I hope it won't be a problem, but I will not be working on this issue after 5:00 April 1st. The Administrator and the other intern will be supplying information for the rest of this report. Thanks for your help! Marcus
[6 Apr 2009 12:18]
Tonci Grgin
Marcus, others... en_sharepoint_services_3.0_tools_visual_studio_extensions_1.2_x86.exe just won't install on x64 and I do not see x64 version. Can anyone point out to me how to do this test and if sharepoint-services are really needed?
[9 Apr 2009 15:50]
Tonci Grgin
Hello! Anybody there?
[9 Apr 2009 22:02]
Marcus Matthews
Tonci, Sorry about that. I will have an answer for you Friday. Marcus
[10 Apr 2009 6:57]
Tonci Grgin
Thanks Marcus. I'd really like to help since I have all that SW at hand.
[10 May 2009 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[29 May 2013 6:11]
Bogdan Degtyariov
I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.
Description: All company information is stored in Quickbooks, retrieved using MySQL that is connected to SharePoint to display the requested data from queries created in SharePoint Designer (SPD) using a custom connection string to configure the data source for drop-down boxes/lists or dataViews. The custom connection string "DRIVER={MySQL ODBC 5.1 Driver};SERVER=192.168.10.11;DATABASE=soslibrary;UID=root;PASSWORD=;OPTION=3;TRUSTED_CONNECTION = true" has allowed us to configure the data source for drop-down boxes/lists and other controls in SPD to access the MySQL Database, but will not allow us to connect SharePoint Designer to a database. Every attempt to record a failed connection using the ODBC trace has ended with an empty or blank text file. Every successful connection was recorded, but with one or two error codes. How to repeat: Server: Dell PowerEdge 2.4GHz processor 1GB RAM Windows Server 2003 Machine OS: Microsoft Windows XP v5.1.2600 with Microsoft SPD 2007 Database & Driver: MySQL v5.1 & ODBC 5.1 Driver In SPD: If the Data Source Library task pane is not visible, on the Task Panes menu, click Data Source Library. In the Data Source Library task pane, click Connect to a database. Note If the Database Connections heading is collapsed, click the plus sign (+) to expand it. In the Data Source Properties dialog box, on the Source tab, click Configure Database Connection. In the Configure Database Connection dialog box, select the Use Custom connection string check box, and then click Edit. In the Edit Connection String dialog box, in the Provider Name list, click the data provider that you want to use. In the Connection String text box, type your connection string. DRIVER={MySQL ODBC 5.1 Driver};SERVER=192.168.10.11;DATABASE=soslibrary;UID=root;PASSWORD=;OPTION=3;TRUSTED_CONNECTION = true Click OK, then click Next. Suggested fix: Upgrade database and drivers from 3.23 to 5.1, check connection string format, permissions and retest.