[peterlaursen]

FAQtured 😀

http://www.webyog.com/faq/5_71_en.html

[peterlaursen]

Yes .. I have thought about the FAQ. A will write a simple guide.

And actually it WORKS perfectly. But it does not look beautiful! The Times font ysed for menus etc. does not work well with WINE. I tested different desktop systems.

But if desktop resolution is not bigger than 1024 it is usable. With 1280+ (and a LCD monitor everything is practically unreadable). The fonts-setting allows for setting of fonts used with SQL pane and DATA pane. Of the fonts distributed with WINE I think Bitstream Vera sans works best.

Is it a big deal to build a binary with standard font not TIMES but SWISS/HELVETICA or the like?

[peterlaursen]

CONFIRMED! 😀 😀 😀

SQLyog 4.2 beta5 runs perfectly on WINE/SUSE LINUX 10 too. Even SJA for Windows does!

[peterlaursen]

Some more research:

Now that I got a local LINUX working I am able to research some more into issues like this one. I am also able to run SJA for LINUX.

I created a simple test database with only one table identically on a Windows host and a LINUX (SUSE 10) host. MySQL is 5.0.13 on both

Code:

CREATE TABLE `TableName1` (
 `id` bigint(20) NOT NULL auto_increment,
 `t` varchar(40) default NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `tablename1` */

insert into `TableName1` values (1,'a');
insert into `TableName1` values (2,'b');
insert into `TableName1` values (3,'c');
insert into `TableName1` values (4,'d');

observations:

1) I changed data a little bit in both. DATA sync was perfect with SJA for LINUX as well as SJA for Windows.

2) Now changed tablenames to `TableName1` on both hosts. It results in errors

“Error No. 1050 Table 'tablename1' already exists” or

“Could not get FIELD information for '`tablename1`' in the SOURCE database”

I believe that the first is a MySQL error and the latter an internal SqLyog error ?

3) Next I renamed both tables to `tablename1` and renamed column`t` to `T` (with ALTER TABLE) on the windows host . A sync with SJA for LINUX returns the error “Column NAME mismatch for '`tablename1`”. An internal SQLyog error, I think? A sync with SJA from Windows runs successfylly.

4) Now reversed things. Column name is `t` on windows and `T`on LINUX (on LINUX SQLyog displays an erroneous error message when alter'ing table, but that is cosmetical!). Same result as with 3) – runs on Windows – error on LINUX.

5) It is correct that the STRUCT-SYNC tool completely disregards CASES for TABLE names and and COLUMN names.

6) Now I created an empty database TEST on LINUX (the `test`databae is still there). The SQLyog GUI cannot distinguish between `test` and `TEST` – lots of GUI errors occur. For instance when trying to copy `test` to `TEST`, `TEST` does not appear on the list of all. And when opening a new connection, SQLyog is fooled to believe that `tablename1` exists in both `test` and `TEST`, but when trying to open `TEST`,`tablename1` a MySQL error is this played (of course). “Cannot fetch ….”)

7) The struct sync tool now only displays `TEST` not `test` on the LINUX host.

[peterlaursen]

What do you choose then ?

There is no language selection here. You can choose between the fonts installed on the system. Attached picture shows an example 😀 . Works fine! Even returns a resultset!

You must explain somewhat better what you want to achieve, and what you try to do.

[peterlaursen]

NULL-mismatch is not a problem with the DATA but with the STRUCTURE of the table. One or more colums are defined differently as 'NULL' og 'NOT NULL' in the databases. Structure must be totally identically for the sync tool to work. That include NULL/NOT NULL, DEFAULTS and INDEXES. You can use the ALTE TABLE functionality og SQLyog to make structure identically

The NULL mismatch also can occur with MySQL databae versions that are “too much different” for the sync. Read a little about it here: http://www.webyog.com/faq/10_68_en.html. But you did sync once, so that is probably not the problem (if the two databases were the same the two times)

If this won't help you we don't need the DATA but the CREATE STATEMENT for the table (You can copy it from the OBJECTS pane in SQLYOG) and the ecact MySQL server versions. If you don't know the query

Code:

Select version();

returns the MySQL server version.

[peterlaursen]

With tunnelling SJA crashes with a similar setup. See pic.

Tested on my webhost a new local Linux installation.

(yeah I managed to rudimentarily set up Apache + php in 10 minutes 😀 )

[peterlaursen]

See atached.

The databases are on SUSE linux 10 with MySQL 5.0.13 on one conputer on my network. Obviously SQLyog (form a windows computer on the network) offers to sync. However the databases are not sync'ed – indsted the err msg “Could not get FIELD information for '`TableName1`' in the TARGET database” . Obviously because there is not `TableName1` but a `tablename1`.

On my webhost I experienced in a similar situation that databases were sync'ed. And you have a third thing happening ?

[peterlaursen]

@Ritesh

I don't understand your point!

Does that mean that you don't finde any error or found a new one ??

Actually I just got Linux installed locally and have MySQL running with it.

So I could test somewhat more. But what to test ?

[peterlaursen]

But still DB1.TableName1 and DB2.tablename1 (both on my Linux webhost) were synced.

[peterlaursen]

@ ritesh

this CSV

Code:

'1','a'
'2','b'

needs to be escaped like you showed, however this one

Code:

“1”,”a”
“2”,”b”

does not.

This must be because SQLyog internally adds singlequotes (and not doublequotes) around the strings processed ??

According to the MySQL docs http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html there is NO difference at all in how to escape with ' and ” (when not sql_mode = 'ANSI_QUOTES' 😮 – then ” has same meaning ` (backquote) ) So it must be because of the way this input is handled by the SQLyog program ?!

[peterlaursen]

Just some additional info for readers:

To read about the use of escaping with MySQL read

http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html

Further I created a crazy example. Let's say you have the string

Code:

this is ours', he said.

and you export that to a CSV-file with , as field delimiter and ' as field enclose character. This resultis in

Code:

'1','this is ours', he said.','400'

(to follow the database schema here. Impossible to import! Thats is what the SQLyog escape character is for. With the use of for instance ^ as SQLyog escape character when exporting you get

Code:

'1','this is ours^'^, he said.','400'

and you can import with the settings shown in picture.

The SQLyog escape character ( here ^ )takes effect with the SQLyog CLIENT and is NOT sent to the server ( 'stripped' before that ), the MySQL escape character ( ) is always used by MySQL when interpreting strings in a query.

And there is even one detail more: when using LIKE in an SQL-statement there is a parser that evaluates the substring behink the LIKE and strips one sescape character. So to test for whether the character is contained in a the string you must write LIKE '%\\%'. Thus this statement

Code:

Select 'backs\ash' LIKE '%\\%'

returns TRUE. But that is a MySQL issue – not an SQLyog issue!

It seems that we got a FAQ issue here. 😀

@Ritesh: no plan for any change here just over night I think ??

[peterlaursen]

You could have written that in the first place!

Now I have been spending no more than 20 hours with aspects of this CSV-tool.

You know how your code reads the user input and what it does with it.

Nobody else has a chance.

[peterlaursen]

Quote:

You need to escape ' with '

You mean that WHEN GENRATING THE CSV the ' should be escaped with .

This is The MySQL escape-character. Not the escape-character that the CSV-import tool uses. The latter handles situations where the 'enclose' or delimiter -character also is present in strings in your data.

@ Ritesh: Nobody understands those half explanations!

But:

It does not work either with 4.2 !

this .csv-file

Code:

'1','hispanic','100'
'2','caucasian','200'

generates the warning as pic shows and numbers are adjusted to 0 (zero) – not matter whether 'optional' is checked or not!

And BTW: maybe the application generating the CSV does not allow for escaping?

Whou told you that if was SQLyog that was used for generating the CSV ?

Much easier: change singlequote to doublequote

[peterlaursen]

It is a problem with the singlequote character used as enclose-character. These two examples work without problems:

Quote:

1,hispanic,100

2,caucasian,200

“1”,”hispanic”,”100″

“2”,”caucasian”,”200″

So you can “search and replace” in an editor and replace single-quote with double-quote.

I remember we had a l-o-o-o-n-g discussion about a similar issue about a year ago. The problem is that the single-quote character has a special meaning with MySQL. That means that it must be escaped with the -character. Unlike other SQLyog functionality the CSV-tools do not handle this automatically.

Last time I tried to understand this I spent a whole weekend. There was a workaround. But either I can't reconstruct details or it simply is gone.

Ridiculous CSV-tools! 😡

[Author]

Posts