Description:
1)I create database and tables in database which has time stamp column.
2)I load data in to the table.
3)Take backup of the database.
4) Then change the sql_mode=MAXDB and then perform Restore and check the contents of the table.
The result is that data is corrupted after Restore because of sql_mode change.
How to repeat:
mysql> create database try;
Query OK, 1 row affected (0.01 sec)
mysql> use try;
Database changed
mysql> create table t1(name varchar(10), start timestamp default 0);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t1 values('aa','2008-04-04 21:37:23');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values('bb',now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+---------------------+
| name | start |
+------+---------------------+
| aa | 2008-04-04 21:37:23 |
| bb | 2008-04-04 15:27:07 |
+------+---------------------+
2 rows in set (0.00 sec)
mysql> backup database try to '/data2/heman/backup_dmp/events/tryy1';
+-----------+
| backup_id |
+-----------+
| 1 |
+-----------+
1 row in set (0.08 sec)
mysql> set sql_mode=MAXDB;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values('cc','2008-04-04 10:28:34');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+---------------------+
| name | start |
+------+---------------------+
| aa | 2008-04-04 21:37:23 |
| bb | 2008-04-04 15:27:07 |
| cc | 2008-04-04 10:28:34 |
+------+---------------------+
3 rows in set (0.00 sec)
Taking Backup Database after sql_mode change.
mysql> backup database try to '/data2/heman/backup_dmp/events/tryy2';
+-----------+
| backup_id |
+-----------+
| 2 |
+-----------+
1 row in set (0.08 sec)
Restoring when sql_mode=maxdb.
mysql> restore from '/data2/heman/backup_dmp/events/tryy2';
+-----------+
| backup_id |
+-----------+
| 3 |
+-----------+
1 row in set (0.07 sec)
mysql> select * from t1;
+------+---------------------+
| name | start |
+------+---------------------+
| aa | ¼851-67-10 89:96:03 |
| bb | ¼851-67-10 87:73:87 |
| cc | ¼851-67-10 85:94:74 |
+------+---------------------+
3 rows in set (0.00 sec)
Change the sql_mode=' ' and then perform the Restore operation and the Restore fails.
mysql> set sql_mode=' ';
Query OK, 0 rows affected (0.00 sec)
mysql> restore from '/data2/heman/backup_dmp/events/tryy2';
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 '"try" DEFAULT CHARACTER SET latin1' at line 1
mysql> set sql_mode=maxdb;
Query OK, 0 rows affected (0.00 sec)
Description: 1)I create database and tables in database which has time stamp column. 2)I load data in to the table. 3)Take backup of the database. 4) Then change the sql_mode=MAXDB and then perform Restore and check the contents of the table. The result is that data is corrupted after Restore because of sql_mode change. How to repeat: mysql> create database try; Query OK, 1 row affected (0.01 sec) mysql> use try; Database changed mysql> create table t1(name varchar(10), start timestamp default 0); Query OK, 0 rows affected (0.04 sec) mysql> insert into t1 values('aa','2008-04-04 21:37:23'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values('bb',now()); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+---------------------+ | name | start | +------+---------------------+ | aa | 2008-04-04 21:37:23 | | bb | 2008-04-04 15:27:07 | +------+---------------------+ 2 rows in set (0.00 sec) mysql> backup database try to '/data2/heman/backup_dmp/events/tryy1'; +-----------+ | backup_id | +-----------+ | 1 | +-----------+ 1 row in set (0.08 sec) mysql> set sql_mode=MAXDB; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values('cc','2008-04-04 10:28:34'); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+---------------------+ | name | start | +------+---------------------+ | aa | 2008-04-04 21:37:23 | | bb | 2008-04-04 15:27:07 | | cc | 2008-04-04 10:28:34 | +------+---------------------+ 3 rows in set (0.00 sec) Taking Backup Database after sql_mode change. mysql> backup database try to '/data2/heman/backup_dmp/events/tryy2'; +-----------+ | backup_id | +-----------+ | 2 | +-----------+ 1 row in set (0.08 sec) Restoring when sql_mode=maxdb. mysql> restore from '/data2/heman/backup_dmp/events/tryy2'; +-----------+ | backup_id | +-----------+ | 3 | +-----------+ 1 row in set (0.07 sec) mysql> select * from t1; +------+---------------------+ | name | start | +------+---------------------+ | aa | ¼851-67-10 89:96:03 | | bb | ¼851-67-10 87:73:87 | | cc | ¼851-67-10 85:94:74 | +------+---------------------+ 3 rows in set (0.00 sec) Change the sql_mode=' ' and then perform the Restore operation and the Restore fails. mysql> set sql_mode=' '; Query OK, 0 rows affected (0.00 sec) mysql> restore from '/data2/heman/backup_dmp/events/tryy2'; 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 '"try" DEFAULT CHARACTER SET latin1' at line 1 mysql> set sql_mode=maxdb; Query OK, 0 rows affected (0.00 sec)