[peterlaursen]

@to both of you!

To me it looks either like a networking issue of some kind or a MySQL configuration issue (max_allowed_packet ?).

How do you connect?  HTTP-tunnel, direct connection?

Any idea about how we can have 'hands on' this

1) a more detailed test case

2) remote access to both ODBC data and MySQL 

[peterlaursen]

Ok .. you solved this.

i only have a single comment on Putty and SQLyog.

When you use Putty you connect to the 'mysql' client program on the server.  This client next connects to the server.  So connection to the MySQL server is form the same computer as where the server runs.

With SQLyog the client is SQLyog (the client code – teh mySQL C-API – is compiled into SQLyog.  SQLyog does not use the 'mysql' client on the server. So here you connect from a remote machine!

[peterlaursen]

“is running locally …”

do you mean on LOCAL MACHINE or LOCAL NETWORK ?

“I can connect to it outside of sqlyog ..”

How? With what client can you connect?  If you are connecting from the same host are you sure that the details are identical?

This FAQ:

http://webyog.com/faq/23_15_en.html

.. deals with the error 2003!

(note: this is a connection issue, not an authentication issue)

[peterlaursen]

yes – most often it does.

But when SQLyog stores your first two examples as files they are both UTF8 encoded.  When we read them (using the API to detect the codepage) one of the two is detected wrongly as ANSI.  Only a few space characters makes the difference between what is detected right and wrong.  And this is not just a single example as you demonstated one more example of this!

We are trying to understand why that happens.  What I wrote was that a personal theory only.  We are trying to find the exact reason.  If it was our own code we could debug and trace every variable for every code line.  We cannot with the API.  So we just have to 'build theories in our head' and test them out!

 

[peterlaursen]

We have been working with this and it looks very much like an issue with the win32 API. Maybe the problem is that the API has problems in detecting the character set/codepage correctly with short files where only a limited range is used of the complete character set available.

We are trying to figure out how to 'code around' it.

[peterlaursen]

hmmm ….

we will check with the LINUX's that we have available (mostly Fedora's and SuSE's) at office.  Won't be untill tomorrow however.

1)  Please try TEXT types as well and also please try both BLOB and TEXT from RESULT tab.

2)  The wine version installed is? (console command “wine –version” tells)

[peterlaursen]

1) yes you can simple export with SQLyog, and open the file in a text editor and change 'create database …' to 'create database … charset utf8 [collate ….]' and import again.  Now the database will be created with the specified charset and not the server default charset.  Be sure that there is no different CHARSET definition in the CREATE TABLE statements (just delete 'DEFAULT CHARSET …' for every table and tables will be created with database default

2) you can also use  ALTER TABLE for every table.

first ALTER TABLE … charset utf8 (changes the table default)

next change the charset/collation for every column in SQLyog ALTER TABLE dialogue (this will re-encode existing data)

.. but teh default charset for the database will still be the old one.  So whenever you want to create a unicode table the charset must be specified explicitly.

You may also want to change the server default from a non-unicode charset to utf8. This you do by editing my.ini/my.cnf

[peterlaursen]

1) yes you can simple export with SQLyog, and open the file in a text editor and change 'create database …' to 'create database … charset utf8 [collate ….]' and import again.  Now the database will be created with the specified charset and not the server default charset.  Be sure that there is no different CHARSET definition in the CREATE TABLE statements (just delete 'DEFAULT CHARSET …' for every table and tables will be created with database default

2) you can also use  ALTER TABLE for every table.

first ALTER TABLE … charset utf8 (changes the table default)

next change the charset/collation for every column in SQLyog ALTER TABLE dialogue (this will re-encode existing data)

.. but teh default charset for the database will still be the old one.  So whenever you want to create a unicode table the charset must be specified explicitly.

You may also want to change the server default from a non-unicode charset to utf8. This you do by editing my.ini/my.cnf

[peterlaursen]

If you have a few more examples you are welcome to post those, so that we can test with those as well!

[peterlaursen]

If I understand right you can use a SJA notifications job and “SELECT … INTO OUTFILE”

http://dev.mysql.com/doc/refman/5.0/en/select.html

says

Quote:

The SELECT … INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT … INTO OUTFILE. In that case, you should instead use a command such as mysql -e “SELECT …” > file_name to generate the file on the client host.

[peterlaursen]

yes .. use INTERVAL of course 🙂

[peterlaursen]

“It's not a problem of data, only source code”

OK .. now we have what we need to understand this issue!

(edit next morning)

Verified with the two examples of yours!

The saved file is OK (and data as well as the file itself is utf8 encoded) with both examples, but when reading the file it is becoming read like ANSI in one of the cases.

[peterlaursen]

1) what does it mean that you “reload” the file!

2) I write a “é” .. And you write in SQLyog editor? or in the grid of the DATA/RESULT tab?

3) you “open again” .. how???  You mean open in SQLyog?

do you

1) execute an external file ('restore from SQL dump')

2) copy into the editor from 'file .. open'

3) or copy/paste using the clipboard.

is there a SET NAMES statement in the file?

you do not SET NAMES from the editor yourself?

Now please attach such file!! Words won't bring us anywhere!

You may take an existing file form 5.19 and create a new one as well with 6.x!

Don't forget to tell which is which!

Create a dump of a table with a few tables and rows, export it, xip and attach here.  

AND paste a screnshot of data should display (in whatever program you want)! 

[peterlaursen]

“but I would like to see 061506”

If there is no direct way you will have to generate day, mont and year seperately and concat() them (or concat(trim())

“select …. where time_stamp = now()-2”

i think you can but I think it will only subtract 2 seconds.  If you want to subtract 2 hours or days I think you must give teh # seconds.

I think everybody reads the doc and experiments a little with this when they need!

[peterlaursen]

Yes.  Data sync does.

You can install a MySQL server locally import to this one and sync with a remote server.

[Author]

Posts