Transfer Instead Of Sync

[kmoon]

Hello,

Is it possible to transfer and append data to the target DB instead of just synchronizing?

I have very limited space on my MySQL server and I want to keep all data in a locally hosted server.

Thank you.

kmOon

[peterlaursen]

hmmmm …

I understand you like this:

You have a webhost with limited space and a localhohost with more space. Data added at the webhost should be transferred to the local host.

If that is it you can run a one-way sync webhost >> localhost an use the NEVER DELETE option.

Was that it? If not you must explain somewhat better!

[kmoon]

peterlaursen wrote on Oct 28 2005, 11:23 PM:

hmmmm …

I understand you like this:

You have a webhost with limited space and a localhohost with more space.  Data added at the webhost should be transferred to the local host.

If that is it you can run a one-way sync webhost >> localhost  an use the NEVER DELETE option. 

Was that it? If not you must explain somewhat better!

[post=”7721″]<{POST_SNAPBACK}>[/post]

I have a webhost with very limited space for MySQL database.

I need to transfer all data from one table at the webhost to a local host once pr day.

AND I need to delete all data from same table in order to free up the space again.

Data should of course be APPENDED to the localhost database.

I don't think I can explain it any better.

Thanks for replying.

[peterlaursen]

Well .. yes!

You then do a one-way sync with webhost as 'source' and localhost as 'target' with the option 'NEVER DELETE' set.

After that you truncate the database/tables at the webhost.

If you want it all done in one operation you create a for the sync and a for truncation of the database/tables. You can then create a .bat file like

Code:

sja syncjob.xml
sja notifyjob.xml

There is one nag here: If the webiste is open for user/visitor input data may be added to the database from the time of sync to the time of truncation. These data will be lost. So it would be more safe to put a TIMESTAMP-column in the table and instead of complete truncation only delete rows with a timestamp smaller the time-of-last midnight for example.

How to use the TIMESTAMP depends on the MySQL version. With a more recent version you can define it as

Code:

default NOT NULL on UPDATE CURRENT_TIMESTAMP

Now the server will automatically update the timestamp whenever there is any change. Check the MySQL docs for your MySQL version for more detailed description (look in … column types .. data and time variables)

And I repeat: don't forget to set 'NEVER DELETE' option! 😀

[kmoon]

peterlaursen wrote on Nov 1 2005, 10:28 AM:

After that you truncate the database/tables at the webhost.

If you want it all done in one operation you create a for the sync and a for truncation of the database/tables.  You can then create a .bat file like

Code:

sja syncjob.xml
sja notifyjob.xml

How to use the TIMESTAMP depends on the MySQL version.  With a more recent version you can define it as

Code:

default NOT NULL on UPDATE CURRENT_TIMESTAMP

Now the server will automatically update the timestamp whenever there is any change.  Check the MySQL docs for your MySQL version for more detailed description (look in … column types .. data and time variables)

And I repeat: don't forget to set 'NEVER DELETE' option!  😀

[post=”7762″]<{POST_SNAPBACK}>[/post]

Mange tak.

But since I am a complete novice in these things I am afraid I will need even more help.

How do I create the batchfiles?

I a command prompt?

What is “truncate” and how do I use it?

How do I insert my own SQL statements like the TIMESTAMP thing…?

Anyway, I am very happy to hear that it IS possible. That is a really great help to me.

[peterlaursen]

How do I create the batchfiles?

>> in Notepad for example. Enter the two lines and save as myjob.bat (NOT myjob.bat.txt !). The .bat file can be executed from commandline o from windows scheduler

What is “truncate” and how do I use it?

>>in SQL to truncate a table could look like:

Code:

truncate table `dbname`.`tablename`

It deletes all rows DATA in the table but not the columns-definition

How do I insert my own SQL statements like the TIMESTAMP thing…?

>>in SQL:

Code:

alter table `dbname`.`tablename`, add column `ts` timestamp  NOT NULL default CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP  after `somefield`;

But check that your MySQL version supports this.

But unfortunately here is a known big with SQLyog since it writes this SQL when a row is updated:

Code:

update `dbname`.`tablename` set  `id`='2',  `ts`='CURRENT_TIMESTAMP' where `id`='2'

Thus the server is 'overruled'. CURRENT_TIMESTAMP is not the same as 'CURRENT_TIMESTAMP' . The first is a time value – the latter is a literal string!

SQLyog should only write

Code:

update `dbname`.`tablename` set  `id`='2',  `ts`= CURRENT_TIMESTAMP where `id`='2'

or simply omit the ts-field

Code:

update `dbname`.`tablename` set  `id`='2' where `id`='2'

I think fixing this is on the TODO of SQLyog 4.3. Until then you should not update data with a TIMESTAMP defined this way with SQLyog. Do you need the TIMESTAMP thing ?

[peterlaursen]

BTW … I think this TIMESTAMP issue is the most important thing to fix with SQLyog.

ANYONE I know using databases professionally on corporate systems have that sort of TIMESTAMP field in EVERY table!!

It would not even be acceptable in a HighSchool project! 😛 😀 😀 😛

[kmoon]

I already have a DATETIME field but I guess I would need the TIMESTAMP as well.

Unfortunately me webhost is running MySQL 4.0 which apparently does not support this function.

Is is possible to truncate only up to a specific row ID instead?

Meaning:

I do a sync rows 0 to n.

I delete or truncate rows from 0 to n.

Rows added in the meantime will have values n + *

[peterlaursen]

I already have a DATETIME field

>>> how is it maintained/altered ? May you can use this one. If you have some application code updating that datetime.

But MySQL prior to 4.1 works this way with timestamps

Quote:

Automatic updating of the first TIMESTAMP column in a table occurs under any of the following conditions:

You explicitly set the column to NULL.

The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.

The column is not specified explicitly in an UPDATE statement and some other column changes value. An UPDATE that sets a column to the value it does not cause the TIMESTAMP column to be updated; if you set a column to its current value, MySQL ignores the update for efficiency.

according to http://dev.mysql.com/doc/refman/4.1/en/tim…mp-pre-4-1.html .

But note that this applies to the first TIMESTAMP column in a table only! With first TIMESTAMP column in a table you don't need the 'on update' in the definition. It is implicit!

This is also the key to a workaround when updating rows with SQLyog. You can work from the RESULT tab (and omit the column in the SELECT statement copying data here) and not the DATA tab

[peterlaursen]

And …

Quote:

Is is possible to truncate only up to a specific row ID instead?

If the Primary Key is an autoincremented integer it is very simple

Code:

delete from `dbname`.`tablename` where `id` < 1234

[kmoon]

peterlaursen wrote on Nov 1 2005, 12:38 PM:

And …

If the Primary Key is an autoincremented integer it is very simple

Code:

delete from `dbname`.`tablename` where `id` < 1234

[post=”7771″]<{POST_SNAPBACK}>[/post]

Yes, I got that, but how do I tell yog which row ID to delete up to. (in a batch job)

(once again thank you for your help )

[peterlaursen]

Quote:

but how do I tell yog which row ID to delete up to

Well …

you can simply edit your 'notify.xml' file before running the job. The SQL-statment is hardcoded inside it. You will then have to open your database to see what value to use.

But the timestamp is 'smarter' I think because you can write

Code:

delete from xx where myts < 'yyyymmdd000000'

Timestamps are internally stored as 14 character strings so if

yyyy=current year

mm = current month

dd = current day

the statement deletes everything from before last midnight. 000000 is 0 hours 0 minutes and 0 seconds.

Try creating a simple job with an unimportant local database, and you'll see that it is pretty simple!

[Author]

Posts