mysqldump --where clause is not working as expected

Question

I want to take the dump only of the latest rows. But When i pass the --where option its not dumping the rows. but when i execute select query with the same where clause it returns the rows. here is my select query:

SELECT  * FROM abc.`xyz` WHERE UPDATED_AT >="2015-11-11 10:03:37";

here is my mysqldump command:

 mysqldump --no-create-info --replace -h source_server --user=backup1 --password='passcode' abc xyz --where='UPDATED_AT >="2015-11-11 10:03:37"'  > "backup.sql"

What is the problem in the above mysqldump command why its not dumping the records?

dump file portion is as under:

 --
 -- Dumping data for table `xyz`
 --
 -- WHERE:  UPDATED_AT >='2015-11-11 10:03:37'

 LOCK TABLES `xyz` WRITE;
 /*!40000 ALTER TABLE `xyz` DISABLE KEYS */;
 /*!40000 ALTER TABLE `xyz` ENABLE KEYS */;
 UNLOCK TABLES;

Then the `where` condition really is the problem. Right now, it is never returning true. — Tim Biegeleisen, Nov 11 '15 at 06:43
UPDATED_AT is a timestamp not a datetime. Should i give it a try to change the field type? — Baran, Nov 11 '15 at 06:44
It worked fine when I updated the field type from timestamp to datetime. — Baran, Nov 11 '15 at 06:47
Hang on...let's find a way to keep things the same and convert from timestamp to datetime in the actual `mysqldump` command. — Tim Biegeleisen, Nov 11 '15 at 06:47
that would be really nice as a lot of tables i have to sync on the server. those have same field type. — Baran, Nov 11 '15 at 06:51
@TimBiegeleisen your propsed unix_timestamp() worked well with MySQL5.0.5 — Baran, Nov 11 '15 at 08:09
I butchered my answer too many times to take credit. Please update your own answer and then mark it correct. — Tim Biegeleisen, Nov 11 '15 at 08:10

Baran · Accepted Answer · 2015-11-11T08:35:31.947

0

In where clause the UPDATED_AT field type was timestamp I changed the field type to DATETIME. It worked as expected.

For the latest version above field type changing works but for the mysql 5.0.5 FROM_UNIXTIME works.

edited Nov 11 '15 at 08:35

answered Nov 11 '15 at 06:49

Baran

1,114
1
10
25

FROM_UNIXTIME returned all the rows. – Baran Nov 11 '15 at 06:54

score 0 · Answer 2 · edited Mar 19 '18 at 02:17

0

Try the following command:

mysqldump --tz-utc=0 --no-create-info --replace -h source_server --user=backup1 --password='passcode' abc xyz --where='UPDATED_AT >="2015-11-11 10:03:37"'  > "backup.sql"

edited Mar 19 '18 at 02:17

Omar Einea

2,478
7
23
35

answered Mar 19 '18 at 01:54

user8482913

1

mysqldump --where clause is not working as expected

2 Answers2