MySQL Connector

MySQL Connector



MySQL Origin

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

 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

      Additional Documentation This Connector is based on the Hubspot ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/DHG/ado/ Hubspot Connector Note: As of September 8, 2020 ...
    • Act! Connector

      ACT! Premium Cloud: StarfishETL supports two-way integration. ACT! On-Prem: StarfishETL 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

      Additional Documentation This Connector is based on the Sharepoint ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/RSG/ado/ If connecting to Sharepoint using Single ...
    • Pardot Connector

      Additional Documentation This Connector is based on the Salesforce Pardot ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/GPG/ado/ Overview The Starfish Pardot Connector ...
    • CSV Connector

      Additional Documentation This Connector is based on the CSV ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/RVG/ado/ CSV Via Upload (in Cloud Cartographer) When you upload ...