[Ritesh]

If you check the SQL file for your table dump, you will see that there is an extra at the end of each primary key data.

I am not sure how that extra NULL got into the data. That is up to you to investigate.

Now in computer terminology, a string is always NULL terminated i.e. the end of a string is represented by a 0 value. When you see the text in SQLyog, it ends at the correct position because the extra NULL is represented as end of string but internally MySQL is keeping it as a part of data. Thus the WHERE clause fails.

How the extra NULL character came into place is a mystery 😮 Whether it appeared during import from MS Access or somewhere else – I have no clue 😮

[Ritesh]

Quote:

Parser is OK (you might even find it logical!), but error with data import (trailing “invisible” blanks)!

I will give a detailed report on this issue by evening.

[Ritesh]

Quote:

SQLyog Enterprise only (and to SJA when SJA (SQLyog Job Agent) is executed on a computer having SQLyog Enterprise installed).

Wrong 😮 You dont require SQLyog Enterprise to use tunneling feature in SJA. Its just that SJA is bundled with SQLyog Enterprise and not with SQLyog Free Edition.

You can port SQLyogTunnel.php to any web-scripting language which can parse XML and can connect to MySQL.

If pnemonic is interested in porting SQLyogTunnel.php to SQLyogTunnel.jsp then I would be happy to open up the schema/details of SQLyogTunnel.php

[Ritesh]

Would you be interested in porting SQLyogTunnel.php to SQLyogTunnel.jsp?

[Ritesh]

I guess they need to update their docs on LIKE clause and usage. I will research on your problem a little more and let you know the details.

[Ritesh]

Quote:

(char –> varchar) yields same result!

CHAR and VARCHAR just defines how the data is stored in the disk. Otherwise, there is no difference in the implementation.

[Ritesh]

peterlaursen wrote on Jun 27 2005, 05:44 PM:

Code:

/*5a works */ select * from test.test where filename like '%\Oriental Voyage\\11 maa.mp3';
/*5b works not */ select * from test.test where filename like '%\\Oriental Voyage\11 maa.mp3';

Nope.

I gotta ask the MySQL AB people on this issue.

[Ritesh]

peterlaursen wrote on Jun 28 2005, 01:34 AM:

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!))

Nope.

This is a different kind of query.

[Ritesh]

peterlaursen wrote on Jun 27 2005, 06:41 PM:

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

Only when you have data like yours i.e. data with .

[Ritesh]

Quote:

I don't believe that SJA uses WHERE .. LIKE

It does and thats crux of all the problem 🙁

[Ritesh]

Thats a public bug system and they dont give much thought to queries posted their. I am levereging my paid support contract to get to the bottom of this issue 😀

[Ritesh]

Mr. Lachlan Mulchahy of MySQL AB says:

Quote:

The problem with the above is not actually a bug in MySQL, it is actually an error in your query. This is tricky to understand, so I will try to explain it simply.

When you are using a query to match a LIKE clause the following occurs:

1. You issue the query: select * from tablename1 where id like 'M:\%'

2. The MySQL parser, strips out the slashes to leave only escaped characters. So the query looks like this:

select * from tablename1 where id like 'M:%'

3. The LIKE pattern matching parser, converts all escaped characters to their literal meanings — There is no meaning for an escaped % so this gets converted to nothing. The query then looks more like this:

select * from tablename1 where id like 'M:'

Note: This isn't exactly what occurs inside the server code, but more a good means of understanding how the escaped characters are working.

So the bottom line is that in order for you to match a literal '' within a like clause, you must escape once for the query parser, leaving you with '\' and then again for the LIKE pattern matcher, leaving you with '\\'.

In summary, the correct query for what you desire is as follows:

select * from tablename1 where id like 'M:\\%'

[Ritesh]

Looks like this is not a bug with MySQL either. If you check the SQL dump of table, its like:

Quote:

insert into `testsync` values ('0CD7EC6B-D4E6-48B8-BE8C-5','Balla Daffe','Orchestre Baobab','Pirate's Choice: The Legendary 1982 Session (2 of 2)','Afrikansk/Fusion',0,438804,6,80000,2,0,'',0,'mp3PRO','M:\Peters Musik\Orchestre Baobab\Pirate's Choice The Legendary 1982 Session (2 of 2)\06 Balla Daffe.mp3','2004-10-22 15:12:20','2004-12-20 05:04:14',0,4396369,0,0,'','','','','','')

In order for the UPDATE to match _exactly_ we will need to include this NULL as part of your WHERE clause. Since all the strings are NULL terminated, there is no way for SQLyog to know whether its a part of the data or string ending NULL.

I dont know how you got the extra NULL in the first place?

Was this data entered using SQLyog?

PS: This is not related to the SJA Data Sync error.

[Ritesh]

What is the error message that you are getting?

[Ritesh]

😀

[Author]

Posts