Local Storage MySQL Error

Toad Data Point
1

Hello! I used to fetch some data from oracle view to new tda feature ‘local storage’. The query runs properly and I received data set. But when i’m trying to send it to local storage i get MySQL Database Error: 'Row is too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs. I’m sure that the max size of VARCHAR2 in that view is 4000 bytes. What could go wrong?
P.S. I’m trying newest 3.1 tda beta

2

Hello,

What is the total size of the row in your result set that you try to send to Local Storage? Is there only one varchar2 in your result set?

Thanks,

Igor.

3

There are 135 columns and 4789 rows in my result set. 99 fields are VARCHAR2 Datatype, 38 of which are 4000 bytes and 61 are shorter (from 1 to 30 bytes).
Thanks for respond.

4

Local Storage is a mySQL database. There are row length limitations. The message you are getting is correct. Your row length exceeds the 65535 bytes.

Please see this reference.

http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

Debbie

5

I see, 65535 is for the entire row. Anyway mySQL local storage works definitely faster rather than sending the queries to remote oracle database. This feature comes in handy when you gotta deal with some snapshots of data.

6

This issue is that Local Storage ignores the datatype of the actual data (e.g. VARCHAR(2)) and sets it to VARCHAR(255) for each VARCHAR field; thus, ensuring that the rowlength if unnecessarily very long. This seems to be a bug.

7

We added to this release the ability to change the datatype size before saving to local storage. I can see if we can add setting the varchar length the same as the original column. CR96751