I have no experience with DTS, but it sounds like it could be a locking
issue - it might sound simple, but are you locking the tables before trying
to read out all the rows? The lock options you use probably depend on
whether you're using InnoDB or MyISAM too. If you look in the Create Backup
section of the MySQL Adminstrator GUI tool, under the "Advanced Options"
tab, you'll see the recommended options (including their SQL statements) for
consistent backups of the various table types...
From: Thomas Johnson [mailto:tjohnson@stripped]
Sent: 16 November 2006 20:15
Cc: Pedram Soheil
Subject: DTS Sometimes gets 0 rows
I have MS DTS package that pulls records from one MySQL server and puts them
into an identical table on another MySQL server. The Query is like the
Select * from scheme.table where modify between @a and @b;
Modify is a TimeStamp field that has default and on Update set to
CurrentTimeStamp. We always pull records from the last pull (@a) to the
present (@b = Now()) which are set at the beginning of the DTS package and
stored in a local table on the source DB. We pull 6 tables in this package.
Initially we have done all 6 in parallel but we have rewritten them to be
sequential. When we run them together, 90% of the time, one DATA PUMP gets
zero rows even when there is data. The Data Pump does not report an error.
When we go back into the design and execute the Data Pump manually it works.
Is there a MySQL setting we should have set that may be causing this? We
are using ODBC 3.51.12 with MySQL 5.0.24. It seems to fail for the same
table consistently and then after few attempts will switch and start to fail
for a different table, but always work when we execute just that ONE Data
Pump manually through the designer.