MySQL patches by Codership

view definer is not replicated

Bug #618603 reported by Seppo Jaakola on 2010-08-16

10

This bug affects 2 people

Affects		Status	Importance	Assigned to	Milestone
	MySQL patches by Codership	Fix Released	Medium	Seppo Jaakola	MySQL patches by Codership 5.5.20-23.4
	0.7	Won't Fix	Medium	Seppo Jaakola
	0.8	Won't Fix	Medium	Seppo Jaakola	MySQL patches by Codership 0.8.2
	5.5	Fix Released	Medium	Seppo Jaakola	MySQL patches by Codership 5.5.20-23.4
	Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC	Fix Released	Medium	Patrick Crews

Bug Description

To reproduce, create a simple view:

CREATE VIEW v (mycol) AS SELECT 'abc';

Then, check the view status in 'master' and 'slave' nodes:

master:

mysql> show create view v\G
*************************** 1. row ***************************
View: v
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`bulldog` SQL SECURITY DEFINER VIEW `v` AS select `abc` AS `mycol`
...

slave:
mysql> show create view v\G
*************************** 1. row ***************************
View: v
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `v` AS select `abc` AS `mycol`
...

Related branches

lp:codership-mysql/5.5

Seppo Jaakola (seppo-jaakola) on 2010-08-16

Changed in codership-mysql:
status:	New → Incomplete
importance:	Undecided → Medium
assignee:	nobody → Seppo Jaakola (seppo-jaakola)
milestone:	none → 0.7.5

Seppo Jaakola (seppo-jaakola) on 2010-08-16

Changed in codership-mysql:
status:	Incomplete → New

Seppo Jaakola (seppo-jaakola) on 2010-11-04

Changed in codership-mysql:
milestone:	0.7.5 → 0.7.6

Revision history for this message

Seppo Jaakola (seppo-jaakola) wrote on 2011-07-04:

#1

Tested with 0.8.1RC and problem still persists with the new DDL replication scheme.
Fixing requires more work, will postpone to next release.

Changed in codership-mysql:
milestone:	0.7.6 → 0.8.2

Alex Yurchenko (ayurchen) on 2011-09-07

Changed in codership-mysql:
milestone:	0.8.2 → none

Revision history for this message

Seppo Jaakola (seppo-jaakola) wrote on 2012-01-29:

#2

fix pushed in wsrep-5.5: http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3695

Revision history for this message

Patrick Crews (patrick-crews) wrote on 2012-02-01:

#3

Seeing this in my testing as well. Creating a test case.

Changed in percona-xtradb-cluster:
assignee:	nobody → Patrick Crews (patrick-crews)
importance:	Undecided → Medium
status:	New → Confirmed

Revision history for this message

Patrick Crews (patrick-crews) wrote on 2012-02-01:

#4

Added cluster_basic.view_definer_test:
20120201-163550 ===============================================================
20120201-163550 TEST NAME [ RESULT ] TIME (ms)
20120201-163550 ===============================================================
20120201-163550 cluster_basic.view_definer_test [ fail ] 6032
20120201-163550 test_basic1 (view_definer_test.basicTest) ... FAIL
20120201-163550
20120201-163550 ======================================================================
20120201-163550 FAIL: test_basic1 (view_definer_test.basicTest)
20120201-163550 ----------------------------------------------------------------------
20120201-163550 Traceback (most recent call last):
20120201-163550 File "/kewpie/percona_tests/cluster_basic/view_definer_test.py", line 69, in test_basic1
20120201-163550 self.assertEqual(master_slave_diff, None, master_slave_diff)
20120201-163550 AssertionError: {'s2': ["s2: expected_result= (('t1_view', 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t1_view` AS select `t1`.`b` AS `b` from `t1` order by `t1`.`b` desc', 'latin1', 'latin1_swedish_ci'),) | slave_result= (('t1_view', 'CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `t1_view` AS select `test`.`t1`.`b` AS `b` from `t1` order by `test`.`t1`.`b` desc', 'latin1', 'latin1_swedish_ci'),)"], 's1': ["s1: expected_result= (('t1_view', 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t1_view` AS select `t1`.`b` AS `b` from `t1` order by `t1`.`b` desc', 'latin1', 'latin1_swedish_ci'),) | slave_result= (('t1_view', 'CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `t1_view` AS select `test`.`t1`.`b` AS `b` from `t1` order by `test`.`t1`.`b` desc', 'latin1', 'latin1_swedish_ci'),)"]}
20120201-163550
20120201-163550 ----------------------------------------------------------------------
20120201-163550 Ran 1 test in 6.032s
20120201-163550
20120201-163550 FAILED (failures=1)
20120201-163550
20120201-163550 ERROR Failed test. Use --force to execute beyond the first test failure
20120201-163550 ===============================================================
20120201-163550 INFO Test execution complete in 39 seconds

Added cluster_basic.view_definer_test:
20120201-163550  ===============================================================
20120201-163550  TEST NAME                                  [ RESULT ] TIME (ms)
20120201-163550  ===============================================================
20120201-163550  cluster_basic.view_definer_test            [ fail ]     6032
20120201-163550  test_basic1 (view_definer_test.basicTest) ... FAIL
20120201-163550  
20120201-163550  ======================================================================
20120201-163550  FAIL: test_basic1 (view_definer_test.basicTest)
20120201-163550  ----------------------------------------------------------------------
20120201-163550  Traceback (most recent call last):
20120201-163550  File "/kewpie/percona_tests/cluster_basic/view_definer_test.py", line 69, in test_basic1
20120201-163550  self.assertEqual(master_slave_diff, None, master_slave_diff)
20120201-163550  AssertionError: {'s2': ["s2: expected_result= (('t1_view', 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t1_view` AS select `t1`.`b` AS `b` from `t1` order by `t1`.`b` desc', 'latin1', 'latin1_swedish_ci'),) | slave_result= (('t1_view', 'CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `t1_view` AS select `test`.`t1`.`b` AS `b` from `t1` order by `test`.`t1`.`b` desc', 'latin1', 'latin1_swedish_ci'),)"], 's1': ["s1: expected_result= (('t1_view', 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t1_view` AS select `t1`.`b` AS `b` from `t1` order by `t1`.`b` desc', 'latin1', 'latin1_swedish_ci'),) | slave_result= (('t1_view', 'CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `t1_view` AS select `test`.`t1`.`b` AS `b` from `t1` order by `test`.`t1`.`b` desc', 'latin1', 'latin1_swedish_ci'),)"]}
20120201-163550  
20120201-163550  ----------------------------------------------------------------------
20120201-163550  Ran 1 test in 6.032s
20120201-163550  
20120201-163550  FAILED (failures=1)
20120201-163550  
20120201-163550 ERROR Failed test.  Use --force to execute beyond the first test failure
20120201-163550  ===============================================================
20120201-163550 INFO Test execution complete in 39 seconds

Revision history for this message

Seppo Jaakola (seppo-jaakola) wrote on 2012-02-15:

#5

Explicit view definer was not replicated, e.g. DEFINER=CURRENT_USER was not effective in slave nodes.
A fix for this pushed in: http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3710

Vadim Tkachenko (vadim-tk) on 2013-01-09

Changed in percona-xtradb-cluster:
status:	Confirmed → Fix Released

Revision history for this message

Shahriyar Rzayev (rzayev-sehriyar) wrote on 2018-01-18:

#6

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1034

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.