Varchar-a Space At The Top Of Data

[sdozono]

[This happens both 4.2 Beta5 and 4.1]

I'm not sure the exact reason yet. It seems that it causes a trouble of synchronization. What do you think?

Some of my SQLdata had a varchar fields that has a space top of the line.

(Because of my mistake.. 🙁 )

Then, when I exported with 'Export Table As Batch Scripts',

SQLyog removed the first space and added a null character like,

('****-**-**','*****','****','****','****','*','*','07/15:19-8','*****','*'),

phpMyAdmin didn't do that.

But both of them don't display the first spaces.

And I have problem with synchronization process now.

It affected the synchronization process, I think.

SQLyog Job Agent Version 4.2

Copyright © Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Sync started at Sat Oct 08 08:08:32 2005

Table SrcRows TgtRows Inserted Updated Deleted

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

`******` 42494 42362 132 0 131

Total time taken – 88 sec(s)

[Ritesh]

Can you send me some sample data to reproduce the problem?

[peterlaursen]

Cna't you fix dat with the TRIM function ?

I have some data having double end-of-string encoding characters.

I remove it with

Code:

update track set filnavn = trim(trailing RIGHT(filnavn,1) from filnavn);

A TRIM(leading …) at the database having the NULL character should do,

Remember to back up data!

[sdozono]

peterlaursen wrote on Oct 8 2005, 07:57 AM:

Cna't you fix dat with the TRIM function ?

I have some data having double end-of-string encoding characters.

I remove it with

Code:

update track set filnavn = trim(trailing RIGHT(filnavn,1) from filnavn);

Thanks for this information!

I wish I had known this… I manually fixed almost 100 lines!

🙁

[sdozono]

Can you duplicate the problem?

========= SOURCE(MYSQL 4.1, Tunneling) ==========

DROP TABLE IF EXISTS `TestTest`;

CREATE TABLE `TestTest` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(100) NOT NULL default '',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=ujis AUTO_INCREMENT=11 ;

—

— `TestTest`

—

INSERT INTO `TestTest` VALUES (1, ' Test. Test Data.');

INSERT INTO `TestTest` VALUES (2, 'Never Ending Synchronization…');

========= TARGET(MYSQL 4.0, Tunneling) ===========

CREATE TABLE `TestTest` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(100) NOT NULL default '',

PRIMARY KEY (`id`)

) TYPE=MyISAM AUTO_INCREMENT=11 ;

#

# `TestTest`

#

INSERT INTO `TestTest` VALUES (2, 'Never Ending Synchronization…');

[peterlaursen]

Code:

Sync started at Mon Oct 10 11:31:14 2005

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`testtest`                       2        1         1        0        1  

Total time taken – 6 sec(s)

MySQL 4.1 (on localhost over a dyndns backloop)> MySQL 4.0 (on another computer on local network). Both http-tunnelled. php is 5.1 RC.

[peterlaursen]

However after executing this SQL

Code:

update testtest set name = trim(leading LEFT(name,1) from name) where LEFT(name,1) = ' ';

(1 row affected) 😀

EDIT: there is a much easier use of the TRIM() function when it is a blank character that must be removed! I forgot. But this code of mine will test for and remove ANY leading character.

you get

Code:

Sync started at Mon Oct 10 11:49:05 2005

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`testtest`                       2        1         1        0        0  

Total time taken – 3 sec(s)

and the data in the tables are identical, and charsets different as originally.

You could be a little bit more specific than just writing “Can you duplicate the problem?” WHAT IS YOUR PROBLEM (except for your charset and other localization issues of course 😀 )

[peterlaursen]

I reproduced with some simple data of my own:

Code:

CREATE TABLE `tn2` (
 `id` bigint(20) NOT NULL auto_increment,
 `t` varchar(20) default NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `tn2` */

insert into `tn2` values (1,'ggg');
insert into `tn2` values (2,'fffff');
insert into `tn2` values (3,'   eee');

and get

Code:

Sync started at Mon Oct 10 13:17:08 2005

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`tn2`                            3        2         1        0        1  

Total time taken – 8 sec(s)

when using HTTP-tunnel on both MySQL 4.1 and 5.0. The row is first inserted and then deleted! Does not happen with direct connection.

The tunneller can't handle leading blanks!

But I have no problems that query runs forever!

[Ritesh]

Even I was able to reproduce a similar problem at my end.

Will work on it tomorrow. Maybe requires some changes in the tunnel module.

[sdozono]

peterlaursen:

Thank you for taking time to experiment!

I appreciate it.

🙄

peterlaursen wrote on Oct 10 2005, 09:55 AM:

You could be a little bit more specific than just writing “Can you duplicate the problem?”  WHAT IS YOUR PROBLEM (except for your charset and other localization issues of course  :D  )

That comment was for this:

Ritesh wrote on Oct 8 2005, 03:11 AM:

Can you send me some sample data to reproduce the problem?

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

But, I should have written it specifically!

Good suggestion. Thanks.

[Ritesh]

This gets interesting. In HTTP Tunneling, SQLyog gets the data from the server in XML format.

It looks like, XML does not seem to correctly recognise leading and trailing whitespace in element data.

E.g. create a XML file with the following data:

Code:

    a

and open it in IE. It will always display it as:

Code:

a

The XML parser that we are using (IE XML parser for that matter too) interally seems to be doing just that. Thus during a sync we insert a instead of a and thus the checksum fails as a is different from [ ]a.

Thus syncing of a data with leading and trailing whitespace fails.

I are working on this issue and will keep you updated.

[Author]

Posts