SQL Maximum Command Length

[andy]

I have created a MySQL database and a LabView 8.6 application which inserts data into the database, using the SQL toolkit.  I'm having trouble inserting very long character data into a LONGTEXT field.  I've determined that the issue occurs when either the length of the SQL command exceeds 1,048,574 characters or the length of one particular field exceeds 1,048,326 characters (can't tell which).

The error I get is "Error -2147467259 occurred at NI_Database_API.lvlib:Conn Execute.vi.  Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [MySQL][ODBC 5.1 Driver][mysqld-5.1.35-community]MySQL server has gone away in NI_Database_API.lvlib:Conn Execute.vi"

According to the MYSQL documentation, a LONGTEXT can hold 4,294,967,295 characters, so length shouldn't be an issue.  The documentation also states "The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory", but I'm not sure how to configure the packet size in LabView. 

I'm attaching example code which replicates the problem, although it depends upon a particular table structure to actually execute.  The table definition is included on the diagram. 

[PhillipBrooks · Accepted solution]

This setting would be configured outside of LabVIEW (UDL or DSN).

You need to configure both the server and the client:

I found this link by searching on stackoverflow

http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

You may need to select/configure a specific (different) provider in order to set this variable.

[andy]

That was exactly the information needed, thanks so much!.  Using your link, I determined that I needed to change the mysql default buffer size (1M) to something larger.  I added this to the my.ini file, and now it works.

max_allowed_packet=16M