Bug #41558 CREATE VIEW on MASTER works but fails on SLAVE
Submitted: 17 Dec 2008 15:22 Modified: 13 Feb 2009 11:08
Reporter: andrew watson Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.0.67 OS:Any
Assigned to: CPU Architecture:Any
Tags: replication, VIEW

[17 Dec 2008 15:22] andrew watson 
Description:
Slave reports error:

                 Last_Error: Query caused different errors on master and slave. Error on master: 'Table '%-.64s' already exists' (1050), Error on slave: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PÀ6' at line 1' (1064). Default database: 'corp'. Query: 'CREATE ALGORITHM=UNDEFINED DEFINER=`eh`@`localhost` SQL SECURITY DEFINER VIEW `cmts_view` AS PÀ6'

However, the master logged no errors and created the view as expected.

How to repeat:
Create several databases that each have a 'cmts' table with this structure:

CREATE TABLE `cmts` (
  `id` int(11) NOT NULL auto_increment,
  `hostname` varchar(30) NOT NULL default '',
  `ip_address` varchar(15) NOT NULL default '',
  `read_string` varchar(30) NOT NULL default '',
  `cm_read_strings` varchar(140) NOT NULL default 'public',
  `cm_write_strings` varchar(140) NOT NULL default '',
  `status` enum('active','disabled','deleted') default 'active',
  `responsive` tinyint(4) default '1',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM

create view cmts_view as             
SELECT 'orangecounty' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from orangecounty.cmts where status = 'active' 
UNION   SELECT 'santabarbara' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from santabarbara.cmts where status = 'active' 
UNION   SELECT 'sandiego' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from sandiego.cmts where status = 'active' 
UNION   SELECT 'phoenix' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from phoenix.cmts where status = 'active' 
UNION   SELECT 'macon' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from macon.cmts where status = 'active' 
UNION   SELECT 'gulfcoast' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from gulfcoast.cmts where status = 'active' 
UNION   SELECT 'hamptonroads' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from hamptonroads.cmts where status = 'active' 
UNION   SELECT 'omaha' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from omaha.cmts where status = 'active' 
UNION   SELECT 'oklahomacity' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from oklahomacity.cmts where status = 'active' 
UNION   SELECT 'tulsa' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from tulsa.cmts where status = 'active' 
UNION   SELECT 'wichita' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from wichita.cmts where status = 'active' 
UNION   SELECT 'neworleans' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from neworleans.cmts where status = 'active' 
UNION   SELECT 'batonrouge' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from batonrouge.cmts where status = 'active'
UNION   SELECT 'cleveland' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from cleveland.cmts where status = 'active' 
UNION   SELECT 'rhodeisland' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from rhodeisland.cmts where status = 'active'
UNION   SELECT 'nova' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from nova.cmts where status = 'active' 
UNION   SELECT 'roanoke' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from roanoke.cmts where status = 'active' 
UNION   SELECT 'palosverdes' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from palosverdes.cmts where status = 'active' 
UNION   SELECT 'gainesville' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from gainesville.cmts where status = 'active' 
UNION   SELECT 'lasvegas' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from lasvegas.cmts where status = 'active' 
UNION   SELECT 'connecticut' as instance,hostname,ip_address,read_string,cm_read_strings,cm_write_strings from connecticut.cmts where status = 'active';

that will work on the master but fail on the slave

Suggested fix:
A better error message would help!
[17 Dec 2008 16:02] Valeriy Kravchuk 
Looks like a duplicate of bug #36922. Please, check.
[17 Dec 2008 16:40] andrew watson 
Well, it seems similar except the part about the error on the slave saying that different errors happened on slave and master when there was no error on the master...
[13 Jan 2009 11:08] Sveta Smirnova 
Thank you for the feedback.

You say no errors on master in your case, but SLAVE STATUS you posted has message: "   Last_Error: Query caused different errors on master and slave. Error on master: 'Table '%-.64s' already exists' (1050), Error on slave: 'You h..." If view existed already you would not have such a message in the server error log file, so it can happen what you just didn't notice this error.

Please indicate which operating system do you use, so we can be sure if it is same as bug #36922
[14 Feb 2009 0:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".