[peterlaursen]

I still would like to see a small table dump!

[peterlaursen]

BTW: SQLyog (ENTERPRISE/ENTERPRISE TRIAL) Migration Tool can do that easy for you!

With my example as in the previous post do this:

1) Create the target table in advance in another database than source

2) Create a DNS with teh MyODBC driver 3.51 pointing to the datbases of the source

3) Migrate from Source to Target with the Migration Tool:

— in the 'map' dialogue uncheck the current PK column

— use 'advanced' setting like attached screenshot (the Migration Tool will REPLACE INTO)

4) ALTER TABLE target: dorp the PK on all columns, create the new ID-column and define it as a PK!

[peterlaursen]

I think the basic solution to your problem is to use the SQL syntax “INSERT IGNORE INTO…” or “REPLACE INTO…” instead of just “INSERT INTO”.

If your old/source table is like

CREATE TABLE `oldtest` (

`ID` int(10) unsigned NOT NULL auto_increment,

`n` int(11) default NULL,

`t` varchar(50) default NULL,

PRIMARY KEY (`ID`)

)

then create a new/target table like (define a PK on ALL columns of the table)

CREATE TABLE `test` (

`n` int(11) NOT NULL,

`t` varchar(50) NOT NULL,

PRIMARY KEY (`n`,`t`)

)

Now you need to read values from source (`oldtest`) and for every row in source execute

INSERT IGNORE INTO newtest (n,t)

   values (n_value_for_the_source_row,t_value_for_the_source_row);

(or REPLACE INTO…)

INSERT IGNORE INTO will skip dublicate rows in target, REPLACE INTO will overwrite, but the result will be the same: only 1 row with the same data!

Now you can ALTER TABLE, drop the 'intermediate PK', create a new ID column and define it as the PK.

However I cannot figure any way to do this in 'pure' SQL.  I think you have two options:

1) using an external script/application reading the source on a per row base and INSERT IGNORE/REPLACE INTO the target.

2) use a Stored Procedure (with a cursor that 'runs through' the source row-by-row and does the same)

[peterlaursen]

SQLyog community 5.32 can be downloaded from

http://www.webyog.com/downloads/SQLyog532.exe

.. also Windows 98 users will need to use this one!

Regarding the characer set issue please read the FAQ:

http://www.webyog.com/faq/34_148_en.html

and my BLOG:

http://www.webyog.com/blogs/peterlaursen/i…hp?showentry=83

I would say that I am almost sure that the problem is that your data are stored incorrectly.  Probably you are using a non-chinese on non-unicode character set where 'encoded strings' but NOT REAL CHINESE characters are stored!

Pleas create a very small table, export, xip and attatch it here.  

We would like to know the exact reasong for this.

(and I think we can provide a solution too – but first I need to see some data!)

to create a small table you may

“CREATE TABLE SELECT ….. FROM  LIMIT 10;”

for instance!

Please also tell the (exact) MySQL version!

[peterlaursen]

I do not understand what is the 'any format'

… press any key ….. eehhhh where is the 'any key' ? 🙂

(I know this was an old and bad joke!)

Please provide a more detailed example.  Attach screenshot of the export dialogue and resulting CSV files.  And be careful to tell exactly what matches what and what 

rogram version is used!

… but before that also please upgrade to 6.04 if you did not allready .  I think I recall such issue in 6.0 and possibly 6.01 …

[peterlaursen]

Actually we have discussed to add some 'global mapping' options.

But currently we don't have this option, and we cannot add it overnight!

[peterlaursen]

It is the ODBC driver that returns 'SQL_TYPE_TIMESTAMP' for the datatype.

There will always be issues with such datatypes that are not identical on different systems!

We have described the issue here:

http://webyog.com/faq/17_125_en.html

I think we chose the best option!

[peterlaursen]

There is no setting available unfortunately.

It is not really SQLyog that 'decides on' the timeout.  it is the PLINK executable.  There is nothing changed in this respect between our special PLINK build and the common (OpenBSD) PLINK.

[peterlaursen]

I cannot tell if ther is sosme bug with InnoDB in that specific MySQL version!

basically MySQL 4.1 before 4.1.12 should ALWAYS be upgraded!

We need a SQL-dump of the tables too.  And consider using the ticket system and not the Forums!  I also think you have exposed a little too much details in that XML you posted!

[peterlaursen]

Please check:

http://www.webyog.com/downloads/betas/not_released/SQLyog604Ent.exe

[peterlaursen]

OK .. confirmed.

5.32 and 6.03 behave differently here!

and what I wrote about SQL SECURITY was not correct.

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

We are analyzing now!

[peterlaursen]

First conclusion is that we think that is related to the SQL SECURITY clause!

We get various results depending on in what direction we sync.  Also it looks like this occurs because the SP names are identical.

This might also involve bugs with MySQL itself!

But you claim an isse with 6.03 and not 5.x ??

With what user privileges are you running STRUCTURE SYNC?

as 'root', 'daniel' or a third one?

“SQL SECURITY …. ” tells who has access to the SQL inside the SP.  You will never be allowed to acces the contents 

of a SP defined with SQL SECURITY 

Please execute 'SHOW GRANTS FOR 'user@'host' and paste the results.

BTW: Are you sure that you have updated the privilege tables every time you have upgraded MySQL?

[peterlaursen]

Please …

1) tell the exact MySQL versions involved

2) provide two complete structure-only dumps to reproduce with.  Create a ticket if you do not want to expose it here

[peterlaursen]

If max_allowed_packet in server configuration is low (like 1 M) then please try to raise the value.  32M, 64M for instance!

[peterlaursen]

“I have this message in my log” .. you are sure this is not an old message?

will not check for FK

will do reverse

.. so you should not have this!

If the problem is as described we will need a more detailed test case (tables + jobfile).

You can create a ticket with the details if you don't want to expose here.  You may also 'fake' passwords etc.

[Author]

Posts