Can't delete and update from DATA-tab

[peterlaursen]

With 4.07 Beta1 I can't delete or update from the the DATA-tab. Data are “in the first run” shown updated or deleted, but clicking another tab and returning to DATA-tab, shows everything as originally. A “select * …” also show all original data with RESULT tab.

However it is still possible to update and delete from RESULT tab – also when everything (result of a “select * …”) is shown!

[Ritesh]

Very strange as I am not able to reproduce the error. The data-tab works perfectly for me.

Do you see any corresponding INSERT/UPDATE/DELETE statement in the History Tab. I checked the option with and without tunneling. Everything seems to be working.

[peterlaursen]

I found out that it is a “” problem too !!

With “Filnavn” (still the example with my music tables) as the PK you can't delete and update.

Changing PK to be built from column “Guid” instead of “Filnavn” you can delete and update!

It is not a 4.07 issue – it's the same with 4.06!

[peterlaursen]

History tab shows for instance

delete from `test`.`testsync2` where `Filnavn`='M:\Peters Musik\Abaji\Oriental Voyage\11 maa.mp3'

delete from `test`.`testsync2` where `Filnavn`='M:\Peters Musik\Abaji\Oriental Voyage\03 For Rita – Morning Take.mp3'

or

delete from `test`.`testsync` where `Filnavn`='M:\Peters Musik\Orchestre Baobab\Pirate's Choice The Legendary 1982 Session (2 of 2)\06 Balla Daffe.mp3'

delete from `test`.`testsync` where `Filnavn`='M:\Peters Musik\Orchestre Baobab\Pirate's Choice The Legendary 1982 Session (1 of 2)\03 Ledi Ndieme M'Bodj.mp3'

and escape character use is correct as far as I can see with both the ” ” and the ” ' ” characteres

[peterlaursen]

Tried to paste one of the delete statements into “MySQL Query Browser” and execute! It does not delete either! Could be an C-API problem! I shall test with the command-line client next!

[peterlaursen]

Command Line Client does not delete either!! 0 rows affected 😡

What's wrong here ??

[peterlaursen]

Conclusion: MySQL can't handle this PK correctly!! But it lets me create it! Must be a bug with MySQL. What do you think ??

[peterlaursen]

a little bit more … (see pic)

This problem AND the problem with SJA is NOT an SQLyog issue. It's a MySQL issue.

Davobw's problem

http://www.webyog.com/forums/index.php?act…st=0&#entry6004 maybe not either

I can't see that I violate anye rule of how to use a PK or how to build a WHERE-clause. If you agree with that it should be reported to MySQL ….

Anyone who has an opinion on this, PLEASE COMMENT!

[Ritesh]

I have been working on your issue for the last 2 hours and even I reached the same result. I think its a problem with MySQL. It does not seem to handle '\' correctly in PKs.

[Ritesh]

The SJA problem you reported earlier is similar to this.

[peterlaursen]

Who knows if it does correctly with “ordinary” indexes too then ???

Did you hear from Davobw ?

If it is part of the same problem, they could as well be reported as one bug!

[Ritesh]

Cant seem to find a issue like this in the MySQL Bug System. :huh: I am opening a new bug at http://bugs.mysql.com

[peterlaursen]

thanks! 😀

[Ritesh]

Bug details can be found at:

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

[peterlaursen]

I added a comment myself to the MySQL bug-system.

[peterlaursen]

there is an ansser already from some Victoria Reznichenko

Quote:

Because MySQL uses the C escape syntax in strings (for example, 'n' to

represent newline), you must double any '' that you use in your LIKE strings.

For example, to search for 'n', specify it as '\n'. To search for '', specify

it as '\\' (the backslashes are stripped once by the parser and another time

when the pattern match is done, leaving a single backslash to be matched).

I think she misses the point ??

We do

Quote:

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

[Ritesh]

Looks like they are considering it as “Not A Bug”.

[peterlaursen]

Yes that's how they have classified it till now. Maybe they have too much success at MySQL at the time. That way of just quoting 4 lines from some dicumentation without investigating the case (she couldn't possible do that in a few minutes) is what we call “left-hand-work” in Danish. It seems more important to close an issue than to investigate it

… that just as with my web hosting. If you have some support issue here you'll sometimes have to reopen it 4 or 5 times.

But I can see you have asked her a question. Let's see if she can answer it!

[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.

[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]

@ ritesh

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

[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 😀

[Author]

Posts