[Maria-discuss] Giving up on dynamic columns
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. Tom
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
who is truncating columns?
On 3/1/15, 3:27 PM, "Sergei Golubchik"
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
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.
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
Hi, Tom! On Mar 01, Tom Worster wrote:
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.
In fact, I found your bug report very helpful. The fact that COLUMN_CHECK does not always detect data truncation is a bug. Thanks for reporting this behavior! We'll try to fix it. Regards, Sergei
Hi Tom, On 01.03.2015 22:38, 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.
Please note that regardless any particular feature, an application working with real data should either check for warnings which happen upon DML -- in your case, INSERTs produce "Data truncated for column 'dcols'", -- or, better still, set a strict sql_mode which will convert these warnings to errors, so the illegal data could never be inserted in the first place. Regards, Elena
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.
Tom
_______________________________________________ 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
participants (4)
-
Elena Stepanova -
Federico Razzoli -
Sergei Golubchik -
Tom Worster