MySQL Bugs: #29878: Garbage data generation when executing SESSION

Bug #29878	Garbage data generation when executing SESSION_USER() on a slave.
Submitted:	18 Jul 2007 18:40	Modified:	6 Aug 2007 17:20
Reporter:	Dave Costantino	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.30, 5.0 BK, 5.1 BK	OS:	Any
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
The SESSION_USER() function generates junk data when executed on a slave server when MySQL is configured for replication.

While it may not be possible to execute this function identically on the slave, it should not produce garbage.

How to repeat:
Configure 2 MySQL servers in a master/slave configuration.

Create a table on the master:

 CREATE TABLE `test_rep` (
  `id` int(11) NOT NULL auto_increment,
  `data` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
 ) ENGINE=MyISAM;

On the master, execute an INSERT:

  insert into test_rep(data) values (session_user());

Verify the data is correct with 'select'. On the master, you will see valid data:
mysql> select * from test_rep;
+----+----------------------+
| id | data                 |
+----+----------------------+
|  1 | root@127.0.0.1       |
+----+----------------------+
1 row in set (0.02 sec)

Execute the same select on the slave, and you will see garbage for the data field:

mysql> select * from test_rep;
+----+------------------------------------------------------------------------------------------------------+
| id | data                                                                                                 |
+----+------------------------------------------------------------------------------------------------------+
|  1 |             (iO    [            jO            hfO    ??W    hfO    ?R?     ?r?36??+    |
+----+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
If the master SESSION_USER cannot be determined from the execution context on the slave, have it generate an empty string or other sensible value.

Thank you for the report.

Verified as described.

Version 4.1 returns different result:

select * from test_rep;
id      data
1

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31540

ChangeSet@1.2476, 2007-07-25 15:56:55+05:00, holyfoot@mysql.com +5 -0
  Bug #29878 Garbage data generation when executing SESSION_USER() on a slave.
  
  Item_func_user doesn't calculate anything in it's val_str() method,
  just returns saved str_value.
  Though Item::save_in_field method can destroy str_value, relying on
  val_str() return. As a result we get the garbage stored in field.
  
  We cannot use Item::save_in_field implementation for Item_func_user,
  reimplement it in simpler way.

ok to push

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31710

ChangeSet@1.2476, 2007-07-27 18:42:25+05:00, holyfoot@mysql.com +5 -0
  Bug #29878 Garbage data generation when executing SESSION_USER() on a slave.
  
  Item_func_user doesn't calculate anything in it's val_str() method,
  just returns saved str_value.
  Though Item::save_in_field method can destroy str_value, relying on
  val_str() return. As a result we get the garbage stored in field.
  
  We cannot use Item::save_in_field implementation for Item_func_user,
  reimplement it in simpler way.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.21 changelogs.

SESSION_USER() returned garbage data (rather than the correct value
of the empty string) when executed by a slave SQL thread.