[MDEV-4387] UDF - Base64 encode/decode Created: 2013-04-11  Updated: 2013-09-23  Resolved: 2013-09-23

Status: Closed
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Trivial
Reporter: roberto spadim Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-251 USEFULL FUNCTION WITH WEB APPLICATIONS Closed
is duplicated by MDEV-5025 Merge TO_BASE64() and FROM_BASE64() f... Closed
Relates
relates to MDEV-4800 base16, base32, base64 functions Open

 Description   

Hi guys, could BASE64_ENCODE and BASE64_DECODE be implemented as native functions in mariadb?

there's some base64 function in source code of mariadb 10.0.0, and a old feature request in mysql bug system (year 2006) but it was removed (maybe oracle problems?)

some links:
http://php.net/manual/en/function.base64-encode.php

inside mariadb-10.0.0 source code directory:

mariadb-10.0.0# find -iname base64
./include/base64.h
./mysql-test/t/mysqlbinlog_base64.test
./mysql-test/r/mysqlbinlog_base64.result
./mysql-test/suite/binlog/t/binlog_base64_flag.test
./mysql-test/suite/binlog/r/binlog_base64_flag.result
./unittest/mysys/base64-t.c
./mysys/base64.c <--- this have encode and decode functions



 Comments   
Comment by roberto spadim [ 2013-04-11 ]

why implement this functions?

well some guys use plain text mysql clients and must have a a 'secure blob transfer', but they can't send blobs or predefined queries
one solution for this is base64, but it is 33% bigger than raw data , but it's binary safe because it don't use special chars

how guys could use it?
1) INSERT INTO some_table (primary) values (1);
2) at client side, $base64_file_value=base64_encode($real_file_value); // CPU use is high here, but it's not a problem, use wait the file to be 'uploaded'
3) UPDATE some_table SET file=COMPRESS("$base64_file_value") WHERE primary=1; // ok COMPRESS is normally used to use less space but consume more CPU
4) SELECT UNCOMPRESS(file) FROM some_table WHERE primary=1; // without COMPRESS we have less CPU use, it's a use option..
5) at client side, $file=base64_decode($value_from_field_file); // since mariadb can't decode base64 before COMPRESS(), we need to decode in client side

and? what this help? well check what we could optimize:
1) INSERT INTO some_table (primary) values (1)
2) at client side, $base64_file_value=base64_encode($real_file_value);
3) UPDATE some_table SET file=COMPRESS(BASE64_DECODE("base64value")) WHERE primary=1
4) SELECT UNCOMPRESS(file) FROM some_table WHERE primary=1
5) at client side, $file=$value_from_field_file; // no decode needed =) less cpu use, and less ram use at client side

with this, we have
1)smaller network traffic at (4) -> 33% less traffic
2)less CPU utilization at (5)
3)more CPU use at (3)
4)more CPU use in (3) isn't a problem, it's a solution, we have more CPU use at UPDATE and less in SELECT, since read is more intensive than write for BLOBS, it's a very good solution!

well that's all, thanks guys and google summer code good works

Comment by roberto spadim [ 2013-04-11 ]

like MDEV-251 (closed)

Comment by roberto spadim [ 2013-04-11 ]

like http://bugs.mysql.com/bug.php?id=18861 (mysql bug database)
http://bugs.mysql.com/file.php?id=3294

Comment by roberto spadim [ 2013-07-01 ]

hi sergey, the UDF plugin function link, works in mariadb, maybe just a 'default mariadb function' could be considered

Comment by roberto spadim [ 2013-09-23 ]

PLEASE CLOSE THIS MDEV
http://bazaar.launchpad.net/~maria-captains/maria/10.0/revision/3822

revision 3822 -> FUNCTIONS TO_BASE64 / FROM_BASE64

Generated at Thu Feb 08 06:56:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.