who is truncating columns? On 3/1/15, 3:27 PM, "Sergei Golubchik" <serg@mariadb.org> wrote:

Hi, Tom!

On Mar 01, Tom Worster wrote:

...

I discovered this showstopper on Friday while working with real data in development of a new app. I managed to narrow it down today and filed the bug report.

https://mariadb.atlassian.net/browse/MDEV-7650

Looks like Maria saves an illegally formatted dyncol string if a dynamic column is longer than 64kB and is not alphabetically last by name among the dynamic column in the blob.

Tbh, even is this were fixed tomorrow so I could proceed with my work, I'm not sure I would use dyncols. My confidence in the feature has been shaken. It's a pity because the Active Record ORM extension I wrote was working.

You could've paid attention to warnings from your test case:

=========== MariaDB [test]> insert into t (a, b, dcols) values (1, 'two', column_create('one', 1, 'css', @txt)); Query OK, 1 row affected, 1 warning (0.00 sec)

Warning (Code 1265): Data truncated for column 'dcols' at row 1 ===========

Indeed, blob size is limited by 64K. Another test case for your bug is:

=========== MariaDB [test]> select length(column_create('one', 1, 'txt', @txt)); +----------------------------------------------+ | length(column_create('one', 1, 'css', @txt)) | +----------------------------------------------+ | 65563 | +----------------------------------------------+ 1 row in set (0.00 sec)

MariaDB [test]> select column_check(left(column_create('one', 1, 'txt', @txt), 65535)); +-----------------------------------------------------------------+ | column_check(left(column_create('one', 1, 'txt', @txt), 65535)) | +-----------------------------------------------------------------+ | 1 | +-----------------------------------------------------------------+ 1 row in set (0.00 sec)

MariaDB [test]> select column_check(left(column_create('one', 1, 'css', @txt), 65535)); +-----------------------------------------------------------------+ | column_check(left(column_create('one', 1, 'css', @txt), 65535)) | +-----------------------------------------------------------------+ | 0 | +-----------------------------------------------------------------+ 1 row in set (0.00 sec) ===========

So the bug is that column_check() doesn't always detects that the dyncol was truncated.

Either way, even if column_check() returns 1, you probably shouldn't use truncated dynamic columns in your application :)

Use LONGBLOB instead.

Regards, Sergei

May I give you a general suggestion? By default, MySQL/MariaDB tends to hide problems. Your case is an obvious example: as far as I understand your application doesn't check for warnings, everything seems to work, but data are truncated and dynamic columns are invalid. I generally suggest to use a strict SQL_MODE. In your case, with STRICT_TRANS_TABLES and/or STRICT_ALL_TABLES, the insertion would fail with an error - and you would notice the problem. And even if your applications doesn't report the error, you could find it using SQL_ERRLOG. Regards Federico -------------------------------------------- Dom 1/3/15, Tom Worster <fsb@thefsb.org> ha scritto: Oggetto: Re: [Maria-discuss] Giving up on dynamic columns A: "maria-discuss email list" <maria-discuss@lists.launchpad.net> Data: Domenica 1 marzo 2015, 21:59 so it turns out the problem is my incompetence. i thought blob allowed long values. i've been using mysql since 3.something and i still don't know much about its abundance of types. i retract my comments and crawl back into my hole. _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to     : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help   : https://help.launchpad.net/ListHelp