| 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: | |
| 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 |
[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.
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;