Bug #19419 VIEW: View that the column name is different by master and slave is made
Submitted: 28 Apr 2006 9:53 Modified: 25 Sep 2006 20:02
Reporter: Gokan Kazuo Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.20/5.0BK/5.1BK OS:Windows (windows xp & linux)
Assigned to: Alexander Ivanov CPU Architecture:Any

[28 Apr 2006 9:53] Gokan Kazuo
Description:
View that the column name is different by master and slave is made under
thereplication environment. 

mysql>create table sample (sample_date date,regist int);

mysql>create view sample_view (sample_date,regist)
      as select sample_date,sum(regist) from sample group by sample_date;
The following table is made on the master side. 
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| sample_date | date          | YES  |     |         |       |
| regist      | decimal(32,0) | YES  |     |         |       |
+-------------+---------------+------+-----+---------+-------+
The binlog output of this SQL is as follows. 
--->CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
    SQL SECURITY DEFINER VIEW `sample_view` AS select sample_date,sum(regist)
    from sample group by sample_date;
This SQL is executed on the slave side and following view is made. 
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| sample_date | date          | YES  |     |         |       |
| sum(regist) | decimal(32,0) | YES  |     |         |       |
+-------------+---------------+------+-----+---------+-------+

If SQL is changed as follows, view of the same content is made by mastering and
the slave as an evasion method.
mysql>create view sample_view as select sample_date,sum(regist)
      as regist from sample group by sample_date;
--->CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
    SQL SECURITY DEFINER VIEW `sample_view` AS select sample_date,sum(regist)
    as regist from sample group by sample_date;

How to repeat:
mysql>create table sample (sample_date date,regist int);
mysql>create view sample_view (sample_date,regist)
      as select sample_date,sum(regist) from sample group by sample_date;

Suggested fix:
mysql>create view sample_view (sample_date,regist)
      as select sample_date,sum(regist) from sample group by sample_date;
I want you to make it give the column name as follows when this SQL is thrown
and output. 
--->CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
    SQL SECURITY DEFINER VIEW `sample_view`(sample_date,regist)
    AS select sample_date,sum(regist) from sample group by sample_date;
[4 May 2006 17:33] MySQL Verification Team
Thank you for the bug report. I was unable to repeat the behavior
reported with 5.0 current source on Suse Linux. Below how I tested:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all on *.* to 'slaveuser'@'localhost' identified by 'slaveuser';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 98
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.01 sec)

mysql> CREATE DATABASE db2;
Query OK, 1 row affected (0.05 sec)

mysql> USE db2
Database changed
mysql> create table sample (sample_date date,regist int);
Query OK, 0 rows affected (0.01 sec)

mysql> create view sample_view (sample_date,regist)
    ->       as select sample_date,sum(regist) from sample group by sample_date;
Query OK, 0 rows affected (0.00 sec)

mysql> show create view sample_view\G
*************************** 1. row ***************************
       View: sample_view
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sample_view` AS select `sample`.`sample_date` AS `sample_date`,sum(`sample`.`regist`) AS `regist` from `sample` group by `sample`.`sample_date`
1 row in set (0.01 sec)

mysql> 

------------------------------------------------------------------------

miguel@hegel:~/dbs/5.0s> bin/mysql --defaults-file=/home/miguel/dbs/5.0s/var/my.cnf -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CHANGE MASTER TO
    ->  MASTER_HOST='localhost',
    ->  MASTER_USER='slaveuser',
    ->  MASTER_PASSWORD='slaveuser',
    ->  MASTER_LOG_FILE='mysql-bin.000001',
    ->  MASTER_LOG_POS=98;
Query OK, 0 rows affected (0.03 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: localhost
                Master_User: slaveuser
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 98
             Relay_Log_File: hegel-relay-bin.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 235
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

mysql> use db2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| sample        | 
| sample_view   | 
+---------------+
2 rows in set (0.01 sec)

mysql> show create table sample\G
*************************** 1. row ***************************
       Table: sample
Create Table: CREATE TABLE `sample` (
  `sample_date` date default NULL,
  `regist` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create view sample_view\G
*************************** 1. row ***************************
       View: sample_view
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sample_view` AS select `sample`.`sample_date` AS `sample_date`,sum(`sample`.`regist`) AS `sum(regist)` from `sample` group by `sample`.`sample_date`
1 row in set (0.00 sec)
[4 May 2006 17:38] MySQL Verification Team
Sorry now I noticed:

`sample_date`,sum(`sample`.`regist`) AS `regist` from `sample` group by

`sample_date`,sum(`sample`.`regist`) AS `sum(regist)` from `sample` group by

Will test now with 5.1.
[4 May 2006 18:44] MySQL Verification Team
Thank you for the bug report.
[11 May 2006 18:12] Timothy Smith
See also bug #19736, "column names not quoted properly when view is replicated".
[5 Jun 2006 0:56] 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/7255
[17 Sep 2006 11:37] 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/12090

ChangeSet@1.2257, 2006-09-18 03:21:00+04:00, aivanov@mysql.com +3 -0
  BUG#19419: VIEW: View that the column name is different
   by master and slave is made.
[17 Sep 2006 12:45] Alexander Ivanov
Pushed to 5.0.25
[23 Sep 2006 7:21] Lars Thalmann
Pushed into 5.0.26 and 5.1.12.
[25 Sep 2006 20:02] Paul DuBois
Notes in 5.0.26, 5.1.12 changelogs.

Column names supplied for a view created on a master server
could be lost on a slave server.