[peterlaursen]

this should be all you'll need to input.

You can check the my.ini file to see which port is used by MySQL, but I don't see why it is not 3306.

It could be a firewall issue. Some firewalls block connections with TCP even to localhost

[peterlaursen]

then look here !! There IS some “binary garbage” at the end of data in file that has been imported from text-file. There is not when data is entered from keyboard.

Thus the problem with the trailing % is not related to the the WHERE…LIKE problem

[peterlaursen]

Sorry – I didn't see that Ritesh was in here allready 😮

[peterlaursen]

the SQLyog HTTP-tunnelling file (SQLyogTunnel.php) connects to SQLyog Enterprise only (and to SJA when SJA (SQLyog Job Agent) is executed on a computer having SQLyog Enterprise installed).

So I guess the answer must be:

1) If you are taking about launching SJA from .jsp code (ie. java) the answer is yes. You must provide the connection details in the job-file.

2) If you want to execute SQL from .jsp-code and connect to a remote MySQL server using HTTP-tunnelling the tunnelling feature of SQLyog it not for that purpose. It's only for connecting with SQLyog (and SJA) as the mySQL client.

I don't know if there are some other tunnelling solutions avaliable for java.

[peterlaursen]

I just did a test with data on my webhost (MySQL 4.0.24).

upper SQLyog-window: “production”-database. Data imported with SQLyog from text-file. See complete DATA-history above!

Edited:

Quote:

Synced aprox. 4 times with SJA.

Wrong!!! I did a fresh import about two days ago to test the “export NULL” issue. SJA has not been in use with these data!

lower SQLyog-window: database with one row of data entered from the keyboard into SQLyog DATA-pane..

[peterlaursen]

However (just like before)

select kunstner, albumtitel, titel, filnavn

from musik.test having filnavn like '%Oriental Voyage\\10 Tchai.mp3%'

/* finds data */;

select kunstner, albumtitel, titel, filnavn

from musik.test where filnavn like '%Oriental Voyage\\10 Tchai.mp3%'

/* finds data */;

See attached pic. You can see the cursor. It's moved as much rightward as possible, which tells that there are no trailing blanks! So that's not the reason why the trailing %-character is needed in WHERE..LIKE!

[peterlaursen]

Quote:

I will research on your problem a little more and let you know the details.

Thanks. But I found a workaround long ago (exporting an Access binary replication type to a MySQL char/varchar type that can be used as a PK as well). So it should not be done for my blue eyes! For your own (if you have them blue 😀 ). As long as this behaviour is not completely understood you can't know what errors it will generate in the future. It could very well be very critical errors with corruption of data. Untill now the only error that has occurred with SJA is that no sync took place where it should. But no guarantee for the future!

And remember: user “Davowb” had an issue where SJA would work with an empty target, but not a target with data in it (what was also my experience). My intuition tells me that this is part of the same problem. And yes: I am a very intuitive person. Even more than most women! And I have pretty good results with it!

[peterlaursen]

BTW: I have also tried some experiments with putting where-clause in HAVING and INSTR()-function.

select kunstner, albumtitel, titel, filnavn

from musik.test having filnavn like '%Oriental Voyage\\10 Tchai.mp3'

/* doesn't find data */;

select kunstner, albumtitel, titel, filnavn

from musik.test where filnavn like '%Oriental Voyage\\10 Tchai.mp3'

/* doesn't find data */;

select kunstner, albumtitel, titel, filnavn

from musik.test Where Instr(filnavn,'Oriental Voyage\10 Tchai.mp3')

/* NB: finds data !!!! NOTE: LIKE-parser not active with INSTR!! I stille believe there is a problem with the parser*/;

select artist, albumtitle, title, filename

from test.test where filename like '%Oriental Voyage\\11 maa.mp3'

/*finds data*/;

select artist, albumtitle, title, filename

from test.test having filename like '%Oriental Voyage\\11 maa.mp3'

/*finds data*/;

select artist, albumtitle, title, filename

from test.test where Instr(filename,'Oriental Voyage\11 maa.mp3')

/*finds data*/;

/*(Now I can't use file ” …11 maa.mp3″ to test with music.test anymore – hope you understand that point!*/

[peterlaursen]

Quote:

Otherwise, there is no difference in the implementation.

I know. But I just wanted to make the cases are TOTALLY identical !!

[peterlaursen]

well I thought I understood

starting with

select * from test.test where filename like '%\Oriental Voyage\\11 maa.mp3';

stripped by the parser >>

select * from test.test where filename like '%Oriental Voyage\11 maa.mp3';

evaluated by MySQL server >>

select * from test.test where filename like '%Oriental Voyage11 maa.mp3';

should find one record!

starting with

select * from test.test where filename like '%\\Oriental Voyage\11 maa.mp3';

stripped by the parser >>

select * from test.test where filename like '%\Oriental Voyage11 maa.mp3';

evaluated by MySQL server >>

select * from test.test where filename like '%Oriental Voyage11 maa.mp3';

should find nothing.

That's how it works with the test.test table in above example – but not with the musik.test table!!

Most crazy: adding trailing blanks, saving and removing trailing blanks it works with musik.test too!

Is there some non-visual “binary garbage” in the table ?!

[peterlaursen]

Yes I was somewhat blind

but

CREATE TABLE `test` (

`artist` varchar(255) default NULL,

`albumtitle` varchar(255) default NULL,

`title` varchar(255) default NULL,

`filename` char(255) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

;

and

CREATE TABLE `test` (

`artist` varchar(255) default NULL,

`albumtitle` varchar(255) default NULL,

`title` varchar(255) default NULL,

`filename` varchar(255) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

(char –> varchar) yields same result!

all 3 queries were tested with SQLyog and Command-Line client as well.

But after adding some trailing blanks to that single record (there weren't any from the start) and removing them again even /*2*/ gets data !!

[peterlaursen]

I shall try to hold back a little, but I believe this is too interesting …

I now believe that I understand that to search for literal string 'Oriental Voyage11 maa.mp3' where there are no chrs after “mp3”

you must write ” … like '%Oriental Voyage\\11 maa.mp3' ” in the WHERE-stmt

/* 1: – it works here */

select artist, albumtitle, title, filename

from test.test where filename like '%Oriental Voyage\\11 maa.mp3'

/* returns

artist albumtitle title filename

---

---

---

---

Abaji Oriental Voyage maa M:MusicAbajiOriental Voyage11 maa.mp3

*/;

/* 2: – but not here */

select Kunstner, Albumtitel, Titel, Filnavn

from musik.test where Filnavn like '%Oriental Voyage\\11 maa.mp3'

/* returns nothing */;

/* 3: – but adding a trailing % gets result. I really don't understand this! */

select Kunstner, Albumtitel, Titel, Filnavn

from musik.test where Filnavn like '%Oriental Voyage\\11 maa.mp3%'

/* returns

Kunstner Albumtitel Titel Filnavn

---

---

---

---

Abaji Oriental Voyage maa M:Peters MusikAbajiOriental Voyage11 maa.mp3 */;

/* create statements are identical or am I blind ? */

/* create statement for musik.test */

CREATE TABLE `test` (

`Kunstner` varchar(255) default NULL,

`Albumtitel` varchar(255) default NULL,

`Titel` varchar(255) default NULL,

`Filnavn` varchar(255) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

;

/* create statement for test.test */

CREATE TABLE `test` (

`artist` varchar(255) default NULL,

`albumtitle` varchar(255) default NULL,

`title` varchar(255) default NULL,

`filename` char(255) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

;

/* tried with and without PK's – and even tried renaming columns to identical names */

/* the only difference I can see with data and SQL in case 1 and 2-3 respectively is */

select count(*) from musik.test /* returns 38326 */;

select count(*) from test.test /* returns 1* /;

[peterlaursen]

found this:

http://bugs.mysql.com/bug.php?id=11594

is this related somehow to our SJA issue ?

(funnily enough it's about using concat_ws() with WHERE…LIKE and music data (though no -character involved!))

[peterlaursen]

One comment from here:

I had given Ritesh carte blanche to do minor changes on his own (his English is probabaly better than mine – at least as spelling goes). This should also ensure that terminology used is identical with the one used on the Webyog website and in the SQLyog documentation.

It's all OK except for one change

I wrote:

Quote:

Why use the SJA ?

…

…

1) It may not be desirable

2) It may not be practicable

Ritesh changed “practicable” to “practical”. According to my dictionary “practicable” means “possible to do” or “feasible”.

That was what I meant!

[peterlaursen]

From my examples it seems like it will be safe to always escape the -character like “\\” when used with WHERE … LIKE.

[Author]

Posts