[peterlaursen]

wellllllll ….

I think they should consider inproving their doc's then!

but does it explain these examples with my data

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';

BTW: All the discussions originated from my problems using a windows filename as a PK with SJA. I don't believe that SJA uses WHERE .. LIKE ??

[peterlaursen]

Some variations on the theme (continuation from Jun 26 2005, 10:54 AM)

to me it seems completely impredictable when to use \ and when to use \\ 🙁

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

/*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';

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

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

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

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

/*6c works not */ select * from test.test where filename like '%\\Abaji\\Oriental Voyage\11 maa.mp3';

/*6d works */ select * from test.test where filename like '%\\Abaji\\Oriental Voyage\\11 maa.mp3';

/*7 works not */ select * from test.test where filename like '%\Oriental Voyage\%';

/*7a works not */ select * from test.test where filename like '%\\Oriental Voyage\%';

/*7b works */ select * from test.test where filename like '%\Oriental Voyage\\%';

/*7c works */ select * from test.test where filename like '%\\Oriental Voyage\\%';

[peterlaursen]

@ ritesh

did you see that there is an answer to your posting at http://bugs.mysql.com/11496

[peterlaursen]

I'm confused now! Actually I don't quite understand your last posting.

Quote:

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

but here

Code:

\06 Balla Daffe.mp3'

the concluding confuses me. What is that ?

Data history:

0) Data are inserted to a MS-Access database by my Music player “Musicmatch”.

1) Imported to local MySQL server 5.1.7 from Access using SQLyog ODBC import wizard

2) Exported to a text-file using SQLyog export facility.

3) textfile imported to local MySQL server 4.0.24 and MySQL at my webhost

4) Since them they have been sync'ed with SJA a couple of times

and besides it's an error that there are all these empty strings. The should be NULL's (are with the Access database). But that is the problem with the export tool writing empty strings, not NULL's.

Maybe I should start all over with a fresh copy of SQLyog where the NULL-export bug is fixed ? (4.07 beta 3 or higher)

[peterlaursen]

It is “localhost” not “local host” (no space).

What do you mean with “hostname”. Windows network name won't work!

You can also use your ip.

[peterlaursen]

We're not alone!

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

[peterlaursen]

fine with me – I won't do any further then unless requested!

[peterlaursen]

@ritesh

what do you think of above case (10:54 AM) ?

It should be fairly easily reproducable. I have made a layout so that you can just copy the whole of it into SQL-pane in SQLyog and execute 1st statement, then 2nd statement etc.

The case that you posted at bugs.mysql.com was easily reproducable too, but unfortunately this Victoria doesn't seem to have tested it seriously. I believe that this case should not be rejected the same easy way. What would be best, do yo think:

1) I post it to bugs.mysql.com

2) You post it to bugs.mysql.com

3) You will have it brought to attention of MySQL AB some other way (using your contacts with MySQL ?)

It probably is not the whole truth about this issue. But it should be sufficient to demonstrate that there is a need for some research into the reasons for this.

From your point of view, I think that – if it's is a bug with MySQL that also causes SJA to fail under certain circumstances (what I believe it is) – the issue is important. Some failure with ordinary SQL can be tested with command-line client or some other client, but there is no other tool to test with if SJA fails. And thus users will blame SJA, negatively affecting the reputation of the program – even in cases where it is an issue with the MySQL server. There just is no easy way to prove it!

So what next ?

[peterlaursen]

Sorry – but I read it quite opposite

Quote:

TEXT columns have a character set

what's the character set and collation (that should be or should not be) used by for instance jpg-data ??

the other way around

Quote:

BLOB columns have no character set

Well, yes using BLOB with LONG character data is comparable to using BINARY (VAR)CHARs. But since BLOBs can't be used with fulltext indexes I don't see much use of it. But yes – it's legal.

And after all SQLyog 4.06 and 4.07 crashes when it tries to open graphics data that it has inserted itself into a TEXT column! At least it does with latin1 charset (a single byte charset) on MySQL 5.07. And what would happen for instance it you used graphics data with a double-byte character set (Chinese, Japanese, Korean)? And will it work too with both ucs and utf and … And with all available storage engines and mySQL versions ?

I shall not insist on anything with issue. I'm balancing on the edge of my knowledge! And maybe on the wrong side of the edge! But then make it work with SQLyog! Then I shall shut up!

😀

[peterlaursen]

That's great.

Nobody has complained about 4.06, so a few days more probably won't make any difference …

[peterlaursen]

I still believe some functionality is broken! I can't open grapics data in the viewer if they have been saved to the DB!

Try import a pic .. save it .. exit SQLyog .. open SQLyog .. and try if the viewer let's you see the graphics data. It displays a grey pane only! And it lets you save graphics to a TEXT field. However if you try to open, SQLyog crashes.

And if you choose save as a file from that grey pane SQLyog and even Windows' Explorer hangs.

[peterlaursen]

My God I am becoming crazy with this!

There might have been some errors with some of my data in some previous examples.

So let's have a fresh start.

However this is now VERY CAREFULLY checked.

And simplifies matters as much as possible I believe.

(example edited a little ½ hour after first posting with a few more examples)

BTW: could it be that LIKE and concat_ws uses part of same algorithms ??

That could explain that SJA is affected!

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

create database if not exists `test`;

USE `test`;

/*Table structure for table `test` */

drop table if exists `test`;

CREATE TABLE `test` (

`artist` varchar(255) default NULL,

`albumtitle` varchar(255) default NULL,

`title` varchar(255) default NULL,

`filename` char(255) default NULL

);

/*Data for the table `test` */

insert into `test` values ('Abaji','Oriental Voyage','maa','M:\Music\Abaji\Oriental Voyage\11 maa.mp3');

/* Queries that work as expected */

/*1*/ select * from test.test where filename like 'M:%';

/*2*/ select * from test.test where filename like '%mp3';

/*3*/ select * from test.test where filename like '%\11 maa.mp3';

/* Queries that don't work but should */

/* It seems like that 'too many' -characters in data and/or query influences error */

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

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

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

/*7*/ select * from test.test where filename like '%\Oriental Voyage\%';

/* However these two works – but I think they should not! */

/* – 8 should match string 'Oriental Voyage%' since % is the escape sequence of of a literal %-character */

/*8*/ select * from test.test where filename like '%\Oriental Voyage\%';

/*9*/ select * from test.test where filename like 'M:\%';

/* there isn't much logic, since these two don't work – again 'too many' characters ? */

/*10*/ select * from test.test where filename like 'M:\Music\%';

/*11*/ select * from test.test where filename like 'M:\Music\Abaji\Oriental Voyage\%';

/* these don't work either */

/*12*/ select * from test.test where filename like 'M:\%';

/*13*/ select * from test.test where filename like 'M:\Music%';

/*14*/ select * from test.test where filename like 'M:\Music\Abaji\Oriental Voyage\%';

/*15*/ select * from test.test where filename like 'M:\Music\Abaji\Oriental Voyage\11 maa.mp3';

/* this one works here – compare with 15! */

/*16*/ select * from test.test where filename = 'M:\Music\Abaji\Oriental Voyage\11 maa.mp3';

/* NOTE: Result the same with this case no matter what column is the PK (or if there are any PK or indexes at all!) */

/* and inserts, updates and deletes function/malfunction the same way with the same WHERE-clauses */

[peterlaursen]

/* SOME SUMMARIZING ON THE ISSUE */

/*

SQLyog Enterprise v4.07 BETA 2

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

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

Server version : 5.0.7-beta-nt-max

*/

create database if not exists `test`;

USE `test`;

/*Table structure for table `test` */

drop table if exists `test`;

CREATE TABLE `test` (

`kunstner` varchar(255) NOT NULL,

`albumtitel` varchar(255) default NULL,

`titel` varchar(255) default NULL,

`filnavn` char(255) NOT NULL,

PRIMARY KEY (`filnavn`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `test` */

insert into `test` values ('Abaji','Oriental Voyage','maa','M:\Peters Musik\Abaji\Oriental Voyage\11 maa.mp3');

/* Queries that don't work but should – NOTE: no % character at the end*/

select * from test.test where filnavn like '%mp3';

select * from test.test where filnavn like '%\11 maa.mp3';

select * from test.test where filnavn like '%voyage\11 maa.mp3';

/* Queries that work (and should too!) – NOTE: % character at the end */

select * from test.test where filnavn like '%mp3%';

select * from test.test where filnavn like '%\11 maa.mp3%';

/* As we have seen before this works */

select * from test.test where filnavn like '%\Oriental Voyage\%';

/* but why must % character be escaped ? – */

/* this should match string 'Oriental Voyage%' only */

/* None of these work*/

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

select * from test.test where filnavn like '%\oriental voyage\11 maa.mp3%';

/* Pattern with trailing % character broken with example on line 18 */

/* It seems to confirm theory that 'too many' -characters in data raises error*/

/* NOTE: Result the same no matter what column is the PK (or if there are any)! */

[peterlaursen]

one more issue … not a bug but just something to consider for the future:

With MySQL 5.x varchars can be around 55.000 characters long. The SQLyog GUI can't handle this a reasonable way (no GUI will ever …)

So I propose that BLOB-viewer be activated for varchars exceeding a certain length (that should be user settable since it would depend on screen resolution what is practical for each). It should – I believe – be implemeted no later than ver. 4.2 where support for MySQL 5.x features are planned (as I believe I have read here recently).

With MYSQL 5.x chars can still be “only” 255 characters. But some users might want to activate the same option for chars exceeding a certain length somewhat below the 255. So this option might as well be available for chars as well.

That would also take some consideration on how to handle “trailing blanks” in varchars . They are NOT stripped in MySQL 5.0 (and maybe not 4.1 ?? – I'm not sure). Actually it already is a problem with the DATA-pane and RESULT-pane (chars not affected since “trailing blanks” still are stripped from chars). Some “background-colouring” (not pink please !) could be one way …

I shall go to bed now and not bother you anymore for a while <_<

[peterlaursen]

I attach a DUMP of last example here

[Author]

Posts