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

[18 Jul 2007 18:40] Dave Costantino
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.
[18 Jul 2007 22:25] Sveta Smirnova
Thank you for the report.

Verified as described.

Version 4.1 returns different result:

select * from test_rep;
id      data
1
[25 Jul 2007 11:58] Bugs System
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.
[27 Jul 2007 13:16] Alexander Barkov
ok to push
[27 Jul 2007 14:43] Bugs System
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.
[2 Aug 2007 19:11] Bugs System
Pushed into 5.1.21-beta
[2 Aug 2007 19:14] Bugs System
Pushed into 5.0.48
[6 Aug 2007 17:20] Paul DuBois
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.