MySQL Bugs: #78486: to_base64() insert an extra '\n' char

Bug #78486	to_base64() insert an extra '\n' char
Submitted:	19 Sep 2015 18:52	Modified:	30 Oct 2015 12:34
Reporter:	Tamas Bagyal	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.6.26-1debian7, amd64, 5.7.9	OS:	Debian (wheezy, uptodate)
Assigned to:		CPU Architecture:	Any

Description:
You can see below the problem:

mysql> SELECT username, password, uid, gid, homedir, NULL FROM `ftpaccounts` WHERE `username` = 'test1';
+----------+----------------------------------------------------------------------------------------------+-----+-------+------------------------------------------------+------+
| username | password                                                                                     | uid | gid   | homedir                                        | NULL |
+----------+----------------------------------------------------------------------------------------------+-----+-------+------------------------------------------------+------+
| test1    | +dB0UTw+U1SDKuAboXOI1Dj+FC89xW3+QjU5Vv+KDo7AX7BCBVBiOQm4lyDOLWtmHxWkOWMGtQ8Yl7bjvPWIqDEyMw== | 107 | 65534 | /var/www/bsgithu/sites/inventory.bsg-it.hu/www | NULL |
+----------+----------------------------------------------------------------------------------------------+-----+-------+------------------------------------------------+------+
1 row in set (0.00 sec)

mysql> SELECT username, TO_BASE64( FROM_BASE64(`password`) ) as password, uid, gid, homedir, NULL FROM `ftpaccounts` WHERE `username` = 'test1';
+----------+-----------------------------------------------------------------------------------------------+-----+-------+------------------------------------------------+------+
| username | password                                                                                      | uid | gid   | homedir                                        | NULL |
+----------+-----------------------------------------------------------------------------------------------+-----+-------+------------------------------------------------+------+
| test1    | +dB0UTw+U1SDKuAboXOI1Dj+FC89xW3+QjU5Vv+KDo7AX7BCBVBiOQm4lyDOLWtmHxWkOWMGtQ8Y
l7bjvPWIqDEyMw== | 107 | 65534 | /var/www/bsgithu/sites/inventory.bsg-it.hu/www | NULL |
+----------+-----------------------------------------------------------------------------------------------+-----+-------+------------------------------------------------+------+
1 row in set (0.00 sec)

In the second query you can see an extra line-break after the char-position 76. this is not an expected behaviour.

environment:

os:
debian wheezy 7.9 up-to-date

mysql:

installed with apt, from "http://repo.mysql.com/apt/debian/ wheezy mysql-5.6"

ii  libmysqlclient18:amd64          5.6.26-1debian7               amd64        MySQL shared client libraries
ii  mysql-apt-config                0.2.1-1debian7                all          Auto configuration for MySQL APT Repo.
ii  mysql-client                    5.6.26-1debian7               amd64        MySQL Client meta package depending on latest version
ii  mysql-common                    5.6.26-1debian7               amd64        MySQL Common
ii  mysql-community-client          5.6.26-1debian7               amd64        MySQL Client
ii  mysql-community-server          5.6.26-1debian7               amd64        MySQL Server
ii  mysql-server                    5.6.26-1debian7               amd64        MySQL Server meta package depending on latest version

How to repeat:
simply create a min. 80 byte length varchar type field, insert a string with length above 78 byte. execute to_base64(from_base64(`field`)) and you have got a  line with extra wrap.

Suggested fix:
i'm not a mysql developer, so...

Please provide the complete script test case not just its how to repeat description. Thanks.

not needed any script for reproduce the bug. just import the table below, and run the "to_base64(from_base64(`password`))" query, then you can see a wrapped line.

CREATE TABLE IF NOT EXISTS `ftpaccounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(128) NOT NULL,
  `password` varchar(255) NOT NULL,
  `homedir` varchar(256) NOT NULL,
  `uid` int(5) NOT NULL,
  `gid` int(5) NOT NULL,
  `fullname` varchar(128) CHARACTER SET latin2 COLLATE latin2_hungarian_ci NOT NULL,
  `datelimit` date NOT NULL,
  `active` tinyint(1) DEFAULT '1',
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=149 ;

INSERT INTO `ftpaccounts` (`id`, `username`, `password`, `homedir`, `uid`, `gid`, `fullname`, `datelimit`, `active`, `last_modified`, `created`) VALUES
(148, 'test1', '+dB0UTw+U1SDKuAboXOI1Dj+FC89xW3+QjU5Vv+KDo7AX7BCBVBiOQm4lyDOLWtmHxWkOWMGtQ8Yl7bjvPWIqDEyMw==', '/var/www/sites/www', 107, 65534, 'ftpaccount', '2015-07-01', 1, '2015-09-19 16:22:03', '2015-07-24 19:28:28');

one more info: i upgraded from mysql 5.5 from debian wheezy official repository, then run the mysql_upgrade for upgrade tables.

Thank you for the requested data.
This is seen in 5.6.26/5.7.9.

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.28-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> CREATE TABLE IF NOT EXISTS `ftpaccounts` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `username` varchar(128) NOT NULL,
    ->   `password` varchar(255) NOT NULL,
    ->   `homedir` varchar(256) NOT NULL,
    ->   `uid` int(5) NOT NULL,
    ->   `gid` int(5) NOT NULL,
    ->   `fullname` varchar(128) CHARACTER SET latin2 COLLATE latin2_hungarian_ci NOT NULL,
    ->   `datelimit` date NOT NULL,
    ->   `active` tinyint(1) DEFAULT '1',
    ->   `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   `created` timestamp NULL DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `username` (`username`)
    -> ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=149 ;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `ftpaccounts` (`id`, `username`, `password`, `homedir`, `uid`, `gid`, `fullname`, `datelimit`, `active`, `last_modified`, `created`) VALUES
    -> (148, 'test1', '+dB0UTw+U1SDKuAboXOI1Dj+FC89xW3+QjU5Vv+KDo7AX7BCBVBiOQm4lyDOLWtmHxWkOWMGtQ8Yl7bjvPWIqDEyMw==', '/var/www/sites/www', 107, 65534, 'ftpaccount', '2015-07-01', 1, '2015-09-19 16:22:03', '2015-07-24 19:28:28');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT username, password, uid, gid, homedir, NULL FROM `ftpaccounts` WHERE `username` = 'test1';
+----------+----------------------------------------------------------------------------------------------+-----+-------+--------------------+------+
| username | password                                                                                     | uid | gid   | homedir            | NULL |
+----------+----------------------------------------------------------------------------------------------+-----+-------+--------------------+------+
| test1    | +dB0UTw+U1SDKuAboXOI1Dj+FC89xW3+QjU5Vv+KDo7AX7BCBVBiOQm4lyDOLWtmHxWkOWMGtQ8Yl7bjvPWIqDEyMw== | 107 | 65534 | /var/www/sites/www | NULL |
+----------+----------------------------------------------------------------------------------------------+-----+-------+--------------------+------+
1 row in set (0.00 sec)

mysql> SELECT username, TO_BASE64( FROM_BASE64(`password`) ) as password, uid, gid, homedir, NULL FROM `ftpaccounts` WHERE `username` = 'test1';
+----------+-----------------------------------------------------------------------------------------------+-----+-------+--------------------+------+
| username | password                                                                                      | uid | gid   | homedir            | NULL |
+----------+-----------------------------------------------------------------------------------------------+-----+-------+--------------------+------+
| test1    | +dB0UTw+U1SDKuAboXOI1Dj+FC89xW3+QjU5Vv+KDo7AX7BCBVBiOQm4lyDOLWtmHxWkOWMGtQ8Y
l7bjvPWIqDEyMw== | 107 | 65534 | /var/www/sites/www | NULL |
+----------+-----------------------------------------------------------------------------------------------+-----+-------+--------------------+------+
1 row in set (0.00 sec)

// 5.7.9

mysql> SELECT username, password, uid, gid, homedir, NULL FROM `ftpaccounts` WHERE `username` = 'test1';
+----------+----------------------------------------------------------------------------------------------+-----+-------+--------------------+------+
| username | password                                                                                     | uid | gid   | homedir            | NULL |
+----------+----------------------------------------------------------------------------------------------+-----+-------+--------------------+------+
| test1    | +dB0UTw+U1SDKuAboXOI1Dj+FC89xW3+QjU5Vv+KDo7AX7BCBVBiOQm4lyDOLWtmHxWkOWMGtQ8Yl7bjvPWIqDEyMw== | 107 | 65534 | /var/www/sites/www | NULL |
+----------+----------------------------------------------------------------------------------------------+-----+-------+--------------------+------+
1 row in set (0.01 sec)

mysql> SELECT username, TO_BASE64( FROM_BASE64(`password`) ) as password, uid, gid, homedir, NULL FROM `ftpaccounts` WHERE `username` = 'test1';
+----------+-----------------------------------------------------------------------------------------------+-----+-------+--------------------+------+
| username | password                                                                                      | uid | gid   | homedir            | NULL |
+----------+-----------------------------------------------------------------------------------------------+-----+-------+--------------------+------+
| test1    | +dB0UTw+U1SDKuAboXOI1Dj+FC89xW3+QjU5Vv+KDo7AX7BCBVBiOQm4lyDOLWtmHxWkOWMGtQ8Y
l7bjvPWIqDEyMw== | 107 | 65534 | /var/www/sites/www | NULL |
+----------+-----------------------------------------------------------------------------------------------+-----+-------+--------------------+------+
1 row in set (0.00 sec)

This is actually documented behaviour for to_base64
http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_to-base64

"A newline is added after each 76 characters of encoded output to divide long output into multiple lines. "

You can do
SELECT username, replace(TO_BASE64( FROM_BASE64(`password`)), '\n', '') as password ....