Description:
1) I created database with swe7 character set and swe7_bin collate
2) I created table and column with same character set and collate.
3) Inserted some values in the table.
4) createed view for table .
5) When you select data from view, error occurs and no data is present.
Note : This problem with views is seen only with character set swe7.
Consequence on BACKUP DATABASE/RESTORE:
========================================
Step 4 doesn't fail for me (means as a customer I may think that VIEW create is successful). Unless I try to read the data from VIEW table, I won't know that VIEW didn't had the actual data. After step 4 perform the following :
- Backup database to a local file.
- Drop database, perform a RESTORE. Action successful.
- Verify the database and tables.
- View is not present.
==> Here because the VIEW had the problem before, either BACKUP or RESTORE might have ignored that malicious VIEW. From a customer's stand point, it's very much ambiguous and might give the opinion that VIEWs weren't restored properly.
How to repeat:
mysql> create database sw character set swe7 collate swe7_bin;
Query OK, 1 row affected (0.00 sec)
mysql> set names swe7;
Query OK, 0 rows affected (0.00 sec)
mysql> use sw;
Database changed
mysql> create table t1(a varchar(2) character set swe7 collate swe7_bin )engine=memory character set swe7 collate swe7_bin;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values('a'),('b'),('c'),('d'),('e'),('f'),('g');
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+
| a |
+------+
| a |
| b |
| c |
| d |
| e |
| f |
| g |
+------+
7 rows in set (0.00 sec)
mysql> create view v1 as select * from t1;
Query OK, 0 rows affected (0.00 sec)
mysql> show full tables;
+--------------+------------+
| Tables_in_sw | Table_type |
+--------------+------------+
| t1 | BASE TABLE |
| v1 | VIEW |
+--------------+------------+
2 rows in set (0.00 sec)
mysql> show table status;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | MEMORY | 10 | Fixed | 7 | 8 | 126992 | 1048576 | 0 | 0 | NULL | NULL | NULL | NULL | swe7_bin | NULL | | |
| v1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 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 '?sw?.?t1?.?a? AS ?a? from ?sw?.?t1?' at line 1 |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE ?t1? (
?a? varchar(2) COLLATE swe7_bin DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=swe7 COLLATE=swe7_bin |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create view v1;
ERROR 1064 (42000): 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 '?sw?.?t1?.?a? AS ?a? from ?sw?.?t1?' at line 1
mysql> select * from v1;
ERROR 1064 (42000): 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 '?sw?.?t1?.?a? AS ?a? from ?sw?.?t1?' at line 1
===================================================================================
Consequence of VIEW on Backup and Restore:
==========================================
mysql> show full tables;
+--------------+------------+
| Tables_in_sw | Table_type |
+--------------+------------+
| t1 | BASE TABLE |
| v1 | VIEW |
+--------------+------------+
2 rows in set (0.00 sec)
mysql> backup database sw to '/data2/heman/backup_dmp/char/sw12';
+-----------+
| backup_id |
+-----------+
| 3 |
+-----------+
1 row in set (0.08 sec)
mysql> restore from '/data2/heman/backup_dmp/char/sw12';
+-----------+
| backup_id |
+-----------+
| 4 |
+-----------+
1 row in set (0.07 sec)
mysql> show full tables;
+--------------+------------+
| Tables_in_sw | Table_type |
+--------------+------------+
| t1 | BASE TABLE |
+--------------+------------+
1 row in set (0.00 sec)
mysql> select * from v1;
ERROR 1146 (42S02): Table 'sw.v1' doesn't exist
mysql>
Description: 1) I created database with swe7 character set and swe7_bin collate 2) I created table and column with same character set and collate. 3) Inserted some values in the table. 4) createed view for table . 5) When you select data from view, error occurs and no data is present. Note : This problem with views is seen only with character set swe7. Consequence on BACKUP DATABASE/RESTORE: ======================================== Step 4 doesn't fail for me (means as a customer I may think that VIEW create is successful). Unless I try to read the data from VIEW table, I won't know that VIEW didn't had the actual data. After step 4 perform the following : - Backup database to a local file. - Drop database, perform a RESTORE. Action successful. - Verify the database and tables. - View is not present. ==> Here because the VIEW had the problem before, either BACKUP or RESTORE might have ignored that malicious VIEW. From a customer's stand point, it's very much ambiguous and might give the opinion that VIEWs weren't restored properly. How to repeat: mysql> create database sw character set swe7 collate swe7_bin; Query OK, 1 row affected (0.00 sec) mysql> set names swe7; Query OK, 0 rows affected (0.00 sec) mysql> use sw; Database changed mysql> create table t1(a varchar(2) character set swe7 collate swe7_bin )engine=memory character set swe7 collate swe7_bin; Query OK, 0 rows affected (0.03 sec) mysql> insert into t1 values('a'),('b'),('c'),('d'),('e'),('f'),('g'); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------+ | a | +------+ | a | | b | | c | | d | | e | | f | | g | +------+ 7 rows in set (0.00 sec) mysql> create view v1 as select * from t1; Query OK, 0 rows affected (0.00 sec) mysql> show full tables; +--------------+------------+ | Tables_in_sw | Table_type | +--------------+------------+ | t1 | BASE TABLE | | v1 | VIEW | +--------------+------------+ 2 rows in set (0.00 sec) mysql> show table status; +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | MEMORY | 10 | Fixed | 7 | 8 | 126992 | 1048576 | 0 | 0 | NULL | NULL | NULL | NULL | swe7_bin | NULL | | | | v1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 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 '?sw?.?t1?.?a? AS ?a? from ?sw?.?t1?' at line 1 | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show create table t1; +-------+--------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE ?t1? ( ?a? varchar(2) COLLATE swe7_bin DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=swe7 COLLATE=swe7_bin | +-------+--------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create view v1; ERROR 1064 (42000): 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 '?sw?.?t1?.?a? AS ?a? from ?sw?.?t1?' at line 1 mysql> select * from v1; ERROR 1064 (42000): 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 '?sw?.?t1?.?a? AS ?a? from ?sw?.?t1?' at line 1 =================================================================================== Consequence of VIEW on Backup and Restore: ========================================== mysql> show full tables; +--------------+------------+ | Tables_in_sw | Table_type | +--------------+------------+ | t1 | BASE TABLE | | v1 | VIEW | +--------------+------------+ 2 rows in set (0.00 sec) mysql> backup database sw to '/data2/heman/backup_dmp/char/sw12'; +-----------+ | backup_id | +-----------+ | 3 | +-----------+ 1 row in set (0.08 sec) mysql> restore from '/data2/heman/backup_dmp/char/sw12'; +-----------+ | backup_id | +-----------+ | 4 | +-----------+ 1 row in set (0.07 sec) mysql> show full tables; +--------------+------------+ | Tables_in_sw | Table_type | +--------------+------------+ | t1 | BASE TABLE | +--------------+------------+ 1 row in set (0.00 sec) mysql> select * from v1; ERROR 1146 (42S02): Table 'sw.v1' doesn't exist mysql>