[peterlaursen]

Coming closer 🙂

In this case I have a TIMESTAMP field named 'modtime' and a 25 char-type name 'GUID'. GUID is the PK.

this XML

Code:

`mp3_filer`
ModTime modtime > current_timestamp – 5000000

Sync started at Thu Jun 02 03:24:45 2005

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`mp3_filer` 37410 37325 146 501 61

Total time taken – 1240 sec(s)

(correct)

This XML

Code:

`mp3_filer`
´`ModTime“ modtime > current_timestamp – 5000000

procucs this erroneous result

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`mp3_filer` 37410 37325 0 0

Total time taken – 7 sec(s)

and the error message:

Error No. 1064

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 'ModTime“`)),1,8),16,10)),

To point it out: NOT using backquotes works, using doble (or more) backquotes does not. Using exactly one backquote works too!

And it is actually very easy to make make the wizard place multiple backquotes in the XML. Just open the jobfile several times with the wizard.

The problem with table.column -adressing that I have postulated before might also have been a backquote problem. this XML

Code:

`mp3_filer`
`mp3_filer`.`ModTime` modtime > current_timestamp – 5000000

now works correct

And there is one problem more with the wizard:

and when you first added a column to the colums-list with the wizard it is almost impossible to get rid of it again, unless you open the XML-file in an editor and delete the lines.

This small parser/XML-writer-module or whatever you call it is very poor. You won't pass IT-exam in highschool with this one. 🙁

[peterlaursen]

Try to have a look at the tool “syncronization wizard” (Power tools .. database syncronization wizard) It can do a “two-way syncronization”. It does not “export only a single column of a table” but maybe you can still use it ??

but PLEASE PLEASE PLEASE:

read the inline help and read some posts in the forum about “Data Synchronization and SQLyog Job Agent” and understand how it works. And backup your tables before experimenting with it! You must still define one DB as the “source” and another as the “target” You must have an identical Primary Key in the tables, and in case of conflicting Primary Keys (and only then) data from the “source” will overwrite the target.

the answer to your original question

Quote:

Can somebody tell me how I can export only a single column of a table?

is hard to ansvwer. Export to where ?? But maybe just use a simple INSERT … SELECT statement where you SELECT from existing tabel and INSERT It to new rows a an identical table.

syntax:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]

[INTO] tbl_name [(col_name,…)]

SELECT …

[peterlaursen]

Quote:

It was installed on top of windows 2000.

Then I believe the error is the same as with a win2K installation. Registry structure and data are not totally identical with a fresh XP-install as an “op top install”. I might even depend on the SP-level of the 2K of the time you updated.

So the fix for 2K when it is out should probably help you as well.

[peterlaursen]

I see in another post of yours that it is mySQL 4.1.12, son u don't need to answer this!!

Can you show data in the data-pane and result-pane ???

[peterlaursen]

yes it should look somewhat like

Quote:

/*

SQLyog Enterprise v4.06 RC1

Host – 5.0.6-beta-nt-max : Database – test

*********************************************************************

Server version : 5.0.6-beta-nt-max

*/

create database if not exists `test`;

USE `test`;

/*Table structure for table `mytable` */

drop table if exists `mytable`;

CREATE TABLE `mytable` (

 `ID` bigint(20) NOT NULL,

 `mytext` varchar(255) NOT NULL,

 PRIMARY KEY  (`ID`,`mytext`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `mytable` */

insert into `charindex` values (1,'dfdfdfdf');

insert into `charindex` values (2,'dfdfhhdf');

insert into `charindex` values (3,'dfdfjjdf');

[peterlaursen]

From Laursen to Larsen …

Hi!

I never heard abut anything like it! I can't repoduce anything like it!

Could it be that the user that you log on with has only the SHOW_DB privilege but not the SELECT privilege ??

Please try to logon as “ROOT” or some “admin” user (with the highest privilege that you can get )and repeat !

If you use the predefined user “test” he has only orivilges to to the “test” database and NOT “test2” or anything else unless you have changed it!

Is it a locally installed MySQL or at som webhosting ?? And what is the MySQL version ?

And the host from where you connect is defined correctly with the user ? (unless you specify otherwise user is only allowed to connect from “localhost” – that how MySQL works).

Do you experience the same problem with otehr applications ??

If you have updated your MySQL-version from a previous version did you remmeber to update the GRANT-tables ?

I believe you are Danish so you might write in Danish if you want …

[peterlaursen]

I have one more question concerning this (now) rather confused matter 🙄

Does the SQL_WHERE option in the job-file have effect

1)for INSERTS+UPDATES

or

2)for UPDATES only

(think the the latter should be the case since the PK could perfectly control INSERTS and DELETES).

Besides the WHERE- will never find deleted data so I can't see how it will ever work with DELETES.

Of course I could test it with some sample data, but you would probably know !?

[peterlaursen]

It seems like they read the webyog forums at MySQL … now they have updated the page. Still the link to their own “Migration Toolkit” is outdated!

[peterlaursen]

BTW:

To me user management with SQLyog is not important any more. I do fine with “MySQL Administrator”. Actually I think developer resources at webyog should concentrate on aspects of worrking with MySQL that are not available with free tools from MySQL, or where it could be done better (more intuitve and user-friendly for instance). But of course – as long as there is a user management feature in SQLyog it should be complete! And MySQL Administrator does not support tunnelling. However I never use user management through a tunneled connection (on local server it does not make sense, on my webhost they only allow their “Control Panel” -application to manage users anyway).

[peterlaursen]

With an dump-file as big as that I would divide it into pieces using a text-editor.

You also could query the remote database to find out which record was the last one succesfylly imported. Then delete the lines in the dump-file corresponding to the data successfully imported and start over again with the new file containing only un-imported data.

[peterlaursen]

exactly like this (with this syntax it takes MySQL 4.1.1 or higher)

[peterlaursen]

see pic below 😀

this WHERE-clause modtime > (SELECT now() – INTERVAL 1 month) also works with SJA (of course – it's the MySQL server parsing in both cases).

You don't have to SELECT NOW() – you can SELECT FROM … And that means that you can write CURRENT_TIMESTAMP with a simple script or an application to a seperate TABLE and COLUMN (call the table lastsync and the column syncstart) just before starting SJA. And next time you sync you use the

WHERE modtime > (SELECT syncstart FROM lastsync).

For security you could use WHERE modtime > ((SELECT syncstart FROM lastsync) – INTERVAL 1 MINUTE).

Managing modtime is simple. It should be declared NOT NULL DEFAULT CURRENT_TIMESTAMP, and when the application is updating or inserting a row should explicitly be set to NULL.

See that's the sort of ideas I would expect from the SQLyog documentation .. and with documented and working examples …. 😀

[peterlaursen]

By …

1) omitting the TIMESTAMP-field (DateLastPlayed) that ODBC handles incorrectly

2) using ONLY the PK and the “Modtime” field for compare .. (modtime will ALWAYS change if data are changed by the application using and generating the data . SJA can't know, but I know!)

3) and using WHERE (last update not older than a month) a sync operation that involves around 2000 rows is done in about 15 minutes.

That was what I was after !

If I had not had this ODBC-import problem and not adressing like “tablename.columnname” in the first run I might have avoided trouble at all. But wisdom grows from ignorance …

[peterlaursen]

Quote:

BTW: I came across too that the ODBC-import wizard converts timestamps with NULL-values to “current_timestamp”-values even if no default is set with the wizard. If I export from MS-Access that does not occur. Is that an issue with SQLyog or due to the fact that two different ODBC-drivers are used ?

Here it comes! That of course is the reason why sync is so slow in my case! Because with each new ODBC-import the rows change if this particular TIMESTAMP-value is NULL (and it is in about 32000 out of 37325). And then of course SJA will UPDATE. It would not if NULL-value from the Access databae was correctly written to MySQL as NULL. But it writes a TIMESTAMP of import-time!

[peterlaursen]

And

Modtime > 20050500000000 must be sufficient! Since ModTime is always equal to DateAdded or later.

[Author]

Posts