MySQL

From Starfish ETL
Revision as of 20:45, 13 March 2019 by Jkuehlthau (talk | contribs)
Jump to navigation Jump to search

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.