longblob attribute in mysql problem

[lynvcode]

SmartClient_v83p_2012-11-23

Smartclient server does not ignore longblob type in MySQL, this causes respond to take too long because is fetching the entire blob instead of ignoring it as it does with blob type.

see Docs for your reference.
"""
For an ordinary "fetch" operation, it's generally useless to return data for a binary field, because in most cases code running in the browser would not be able to do anything with a binary value (such as invoke a PDF viewing plugin). For this reason the SmartClient Server will automatically omit values of type InputStream, byte[] or Blob during a normal "fetch".
"""

Please include longblob in the types of fields in MySQL.

Thanks.

[Isomorphic]

Initial tests here show that MySQL longblob values appear to the Java code as regular Java InputStreams and are thus handled exactly the same as ordinary blob values. If you are seeing different results, please post more information to let us investigate - MySQL server version and JDBC driver version, and the table structure (the CREATE statement) and .ds.xml file that you see this issue with.

Also, what makes you think the longblob data is being read? Are you seeing some kind of value being sent back to the client?

[lynvcode]

1. MySQL Server version: 5.1.66-0ubuntu0.10.04.3 (Ubuntu)
2. JDBC driver: mysql-connector-java-5.1.20-bin.jar
3. Table templates

Field 	Type 	Null 	Key 	Default 	Extra
pk 	int(11) unsigned 	NO 	PRI 	NULL 	auto_increment
file_template 	longblob 	YES 		NULL 	
file_template_date_created 	date 	YES 		NULL 	
file_template_filename 	varchar(255) 	YES 		NULL 	
file_template_filesize 	varchar(45) 	YES 		NULL 	
product_id 	int(11) 	YES 	MUL 	NULL 	
description 	varchar(255) 	YES 		NULL 	
language 	enum('EN','GR') 	YES 		EN

4. DS XML

<DataSource ID="prop_templates" serverType="sql" dataSourceVersion="1" dbName="proposal_system" tableName="templates">
    <fields>
        <field name="pk" type="sequence" primaryKey="true"/>
        <field name="file_template" type="binary" length=""/>
        <field name="description" type="text" length="255"/>
        <field name="product_id" required="true" type="integer" foreignKey="prop_products.pk"></field>
        <field name="category_id"  canSave="false" type="integer" includeFrom="prop_products.category_id"></field>
        <field name="category_name" canSave="false" includeFrom="prop_products.category_name" />
	<field name="language" length="2" required="true" type="enum" >
            <valueMap>
                    <value>EN</value>
                    <value>GR</value>
            </valueMap>
        </field>
    </fields>
    <allowAdvancedCriteria>true</allowAdvancedCriteria>
</DataSource>

5.
I came to that conclusion when I changed from longblob to blob, everything was faster.

I think I am mistaken because I changed it to longblob and it still the same speed. Must be database problem I guess, this never happened to me before. This however shorted the column data and it works faster now.

It is surprising to have this behavior. I will investigate with older backup and come back with more information. It may because of the table not being optimized. Please do not waste your time on this, I will respond with more information.

[lynvcode]

So the problem was not my first guess, but the second one that follows:

I have a foreign key that points to another table, and it seems that smartclient server does a

 SELECT templates.description, templates.file_template, templates.file_template_date_created, templates.file_template_filename, templates.file_template_filesize, templates.language, templates.pk, templates.product_id, view_products.category_id, view_products.category_name FROM templates, view_products WHERE ('1'='1') AND templates.product_id = view_products.pk

and joins the two tables together, that means that each time it fetch a result it gets read all the data of the blob files inside the blob column (templates.file_template).

Is there a work around that we can work with or should I remove the foreign keys?

The datasource column that points to the other table

<field name="product_id" required="true" type="integer" foreignKey="prop_products.pk"></field>

The time it took to fetch as described as a problem was 16~18 sec.
Without the datasource column mentioned above it took 4sec. I also checked the server traffic and had peek to 100 MB per second.

[Isomorphic]

We don't automatically join just as a result of a foreignKey declaration. Seems like you are explicitly doing the join via SQL Templating or dsField.includeFrom.

[lynvcode]

I am using the displayField for name (product name) that is brought by:

        <ListGridField name="product_id" title="Product Name">
            <showHover>true</showHover>
            <optionDataSource>prop_products</optionDataSource>
            <valueField>pk</valueField>
            <displayField>product_name</displayField>
            <detail>false</detail>
            <modalEditing>false</modalEditing>
            <autoFetchDisplayMap>true</autoFetchDisplayMap>
        </ListGridField>

Any ideas? I think I will do a view without the blob column or that as a blank column. Only for a fetch, haven't tried it yet. If I do the reverse? I mean include the file from products, will that get the blob again?

[Isomorphic]

Again, this doesn't cause a join. Look for how you are causing a join.

[lynvcode]

Datasource: prop_templates

<DataSource ID="prop_templates" serverType="sql" dataSourceVersion="1" dbName="proposal_system" tableName="templates">
    <fields>
		<field sqlType="integer" primaryKey="true" sqlLength="10" name="pk" type="sequence"></field>
		<field sqlType="longvarbinary" sqlLength="16777215" name="file_template" type="binary" length=""></field>
		<field sqlType="timestamp" name="file_template_date_created" type="datetime"></field>
		<field sqlType="varchar" sqlLength="255" name="file_template_filename" length="255" type="text"></field>
		<field sqlType="integer" sqlLength="10" name="file_template_filesize" type="integer"></field>
        <field name="description" type="text" length="255"/>
        <field name="product_id" required="true" type="integer" foreignKey="prop_products.pk"></field>
	<field name="language" length="2" required="true" type="enum" >
            <valueMap>
                    <value>EN</value>
                    <value>GR</value>
            </valueMap>
        </field>
    </fields>
    <allowAdvancedCriteria>true</allowAdvancedCriteria>
</DataSource>

<ListGrid dataSource="prop_templates" autoFetchData="true" ID="LG_templates" autoDraw="false">
    <fields>
        <ListGridField name="pk" title="ID" width="50">
            <canEdit>false</canEdit>
        </ListGridField>
        <ListGridField name="file_template" type="binary" title="File Template">
            <canEdit>false</canEdit>
        </ListGridField>
        <ListGridField name="product_id" title="Product Name">
            <showHover>true</showHover>
            <optionDataSource>prop_products</optionDataSource>
            <valueField>pk</valueField>
            <displayField>product_name</displayField>
            <detail>false</detail>
            <modalEditing>false</modalEditing>
            <autoFetchDisplayMap>true</autoFetchDisplayMap>
        </ListGridField>
        <ListGridField name="description" title="Description"/>
        <ListGridField name="language" title="Language"/>
    </fields>
    <listEndEditAction>next</listEndEditAction>
    <showFilterEditor>true</showFilterEditor>
    <canEdit>true</canEdit>
    <canRemoveRecords>true</canRemoveRecords>
    <xsi:type>ListGrid</xsi:type>
</ListGrid>

Where am I causing join?

[lynvcode]

The reason I am asking is because I don't see a join.