MySQL Connector

MySQL Connector



MySQL Origin

Note that the connection string must match the sample connection string shown in Starfish:

 server=<server_name>;uid={0};pwd={1};database=<db_name>;

The string components must be placed in that order. {0} is a variable that refers to the Username field. {1} is a variable that refers to the Password field.

Sample Origin Filter

Use standard MySQL statements.

When connecting directly to a Sugar MySQL database, and possibly other databases, when running a standard MySQL query, it can fail when a field type is a standard MySQL ID field which requires 32 characters and Sugar uses the id "1" for the admin team:

 SELECT  id, team_id, name FROM accounts

This will cause an error in the MySQL connector:

 Failed, error message was: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

To resolve this, make sure to cast any Guid fields:

 SELECT  id, cast(team_id as char(36)) id, name FROM kbdocuments

Issues

Some users have reported receiving this error after reading a few hundred to a few thousand rows:

MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during data read. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Reading from the stream has failed. ---> System.IO.EndOfStreamException: Attempted to read past the end of the stream.
   at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.FetchDataRow(Int32 statementId, Int32 columns)
   at MySql.Data.MySqlClient.Driver.FetchDataRow(Int32 statementId, Int32 columns)
   at MySql.Data.MySqlClient.ResultSet.GetNextRow()
   at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlDataReader.Read()
   at Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Method TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags Flags)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.ObjectLateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
   at StarfishEngine.StarfishService.AdvanceOrigin(rowdat& RowData)

There are a couple of things you can try. You can try updating the settings on your MySQL server:

set net_write_timeout=99999
set net_read_timeout=99999

You can do this with a Before Operation. Action Type: VBScript Procedure. Exec When: Once Before Query.

Sub VBScriptProcedure
                ExecSQL "set net_write_timeout=99999;", "ORIGIN"
                ExecSQL "set net_read_timeout=99999;", "ORIGIN"
End Sub

You can also switch to using the ODBC MySQL Connector. It has been reported that the ODBC MySQL Connector is half as fast, but it doesn't get the error.

Additional Connection String parameters

This is for additional paramters. For example, if you get the error "The host localhost does not support SSL connections.", you can try putting "SslMode=none" in this field.


Parameter
Description
Server
Database

MySQL Connection Edit Screen


    • Related Articles

    • Hubspot Connector

      Hubspot Connector Note: As of September 8, 2020 the Hubspot API does not allow external applications to write into their Quoting module.  See below. Overview The StarfishETL Hubspot Connector enables you to easily connect to Hubspot for integrations ...
    • Act! Connector

      ACT! Premium Cloud: StarfishETL supports two-way integration. ACT! On-Prem: Starfish does not have a native Act! connector for versions less than Act v18 so we read directly from the database. Configuration Options Connect Act Premium (v18+) using ...
    • Sharepoint Connector

      If connecting to Sharepoint using Single Sign-on (SSO) You might see this error if not connected appropriately - "[500] Could not execute the specified command: unrecognized response when getting SharePoint online security cookies, please check your ...
    • Pardot Connector

      Overview The Starfish Pardot Connector uses the pardot v3 API. See http://developer.pardot.com/ for more information. Origin Where do I find my "API User Key"? In Pardot, hover over the gear icon in the upper right corner of the screen, click ...
    • CSV Connector

      CSV Via Upload (in Cloud Designer) When you upload a new file, you need to clear the connector cache before the file will show in the Query Builder and before Origin Preview will work. If you run into any issues with the data being read in unexpected ...