[ekod]

peterlaursen wrote on Jun 11 2007, 11:47 AM:

BTW: 'Query Browser' from MySQL AB and phpMyAdmin does the same. It is a server issue, not a client issue!

and also BTW: all connection types are affected!

Damn, thats smart debugging then… I have MySQL 4.1.11-Debian_4sarge7 here, but it is still surprising that phpmyadmin (phpMyAdmin 2.5.7) has no problems with it ( bug + bug = …no bug…) , but phpmyadmin 2.10 it does reproduce the ? error.

I think your mysql upgrading suggestion is the best way to go! Thanks a lot overthere 🙂

[ekod]

Quote:

At least we are getting closer in understanding one another.

Always nice 😉 I understand your remarks about real utf and utf stored in latin.

Quote:

SQLyog displays data as it is stored in the database. If it does not there is a bug or at least some issue!

See the screenshots of the sqlyog display. As already said, it does not show the content right.

Quote:

It would be very nice if you could find some way to establish direct or SSH connection!

I ll work on that.

Quote:

Is the SQL with '?'marks in it copied from SQLyog HISTORY tab?

Yes.

Quote:

And when you insert blanks you open the text-string in the BLOB viewer an eidt form here (silly question probably – what else?!! But just to be perfectly sure!)

Affirmative again..

As soon as I have direct ssh connection available, I will post my findings. Stay tuned..

[ekod]

Ok, i will try again: the problem is that data corruption occurs:

It happens when latin encoded TEXT field exists, with UTF-8 content inside.

I use https incl tunnel. Serverparams are already posted

You said

Quote:

SQLyog displays data as it is stored in the database

, but here it doesnt exactly.

See the picture for what happens. [attachment=680:thumb_022.gif]

To test you can use the sql attached, which is generated by phpmyadmin [attachment=681:test.sql.txt]

I have not been able to try this with direct connection (port 3306)

[ekod]

Computers make your grey hair come off ….

Peter, I couldnt agree more with your sig at this time, I think the workaround is ok and I can live with it, but one question (in non nagging mode), the scenario I depicted that you make a db/table in UTF and then do a dds sql without explicitly stating UTF as collation would result in deformed sql is still a risk..

Can you give me an example where db (utf), table (utf) and column (latin) would make sense? Of course this is not a wordpress support forum, I m just curious how to circumvent possible problems here.

Ok, I will withdraw my harsh words to sqlyog, but I am still wondering why other clients still can make sense of this without errors (see phpmyadmin).

FYI, I am using the client in https tunneling mode, I did not try yet with direct 3306 port, could be that there is some issue with SQLyogTunnel.php?

Thanks for your response!

[ekod]

Ok, ill have a look at this later, for now Ill try to read http://trac.wordpress.org/ticket/3517 which has a nice resume of the problem.

If I can pinpoint the problem then Ill post it here.

[ekod]

peterlaursen wrote on Jun 7 2007, 02:20 PM:

The charset DEFAULT is utf8, but every string column is defined as 'latin1'. That looks weird!

If you are not using SQLyog, what are you using now to generate this example! How was the table and the data created?

What happens if you simply ALTER TABLE and define utf8 charset for the columns? (back up first!)

Thank you for the response:

I changed collation and that works, thats nice for the future, but my data is still corrupted :-(.

Default server collation was set to latin, DB and table definition was set to UTF.

This was done by a wordpress install.

Of course this should be better SQL, but if you dont specify column collation when creating the table (who thinks of that anyway when creating a table) , it will revert to server default. (lin this case latin)

This is common practice (in a lot of web based installers of cms/blog packages), but in case you have this conflict, SQLyog should see the inconsistency and report that it can lead to corrupted data and DENY the update, because corrupting data is the worst that can happen.

If I use phpmyadmin as client this does not happen, neither this behaviour appears with any other client (web based, mysql front, didnt try heidisql), and no data gets corrupted, so the combination of database (utf) – table(utf) – column(latin) is not be the problem.

I consider this critical behaviour as extremely dangerous.

I know sqlyog pushes everything to UTF-8, which is good, but it should handle these situations better imho

[ekod]

peterlaursen wrote on Jun 7 2007, 02:29 PM:

I think every GRID based GUI does like this. The only exception is TIMESTAMP fields defined as 'ON UPDATE CURRENT_TIMESTAMP' because the server should update those with now()!

How should we check otherwise than comparing with data on the server? That would demand the same or maybe more data to be transferred.

You can work from the RESULT tab and not the DATA tab and only the displayed columns will be updated.

Fast response 🙂

Ok, agree about result tab, but since you dont check anyway with the server data when sending the update, you might check if a column has changed on the client side only, true?

Plz correct me if i'm wrong..

[ekod]

peterlaursen wrote on Jun 7 2007, 02:08 PM:

what is your windows version?

Please read this FAQ: http://webyog.com/faq/32_145_en.html

One place to look is http://www.dll-files.com

.. last I checked there was a XP version there!

Yep, thanks for the suggestion, but its not there anymore, why not offer it as a downloadlink since it is redistributable afaik. Its about 1 MB. Now you have to download and install the full framework, works as well.

13 MB Download link at MS

[Author]

Posts