[peterlaursen]

to test it:

1) does the inner statement run ?

2) does the in

[peterlaursen]

and .. is unit a string-type ?

[peterlaursen]

sqlyog does not run any specific Mysql version. Sqlyog is a CLIENT that connects to a MySQL SERVER and the SERVER runs some specific MySQL version. If you execute 'something' from the SQL-pane in sqlyog, sqlyog does nothing but passing that 'something' on to the MySQL server. Any error-masages that you get back are error messages sent by the server. Sqlyog does not parse at all!

According to the mysql documentation the basic subquery construction is something like

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

A subquery is a SELECT statement inside another statement.

A subquery's outer statement can be any one of: SELECT, INSERT, UPDATE, DELETE, SET, or DO.

With my Music Library this statement

select * from music.artistview where artist = (select artist from music.mp3_files where album = 'Music spoken here' group by kunstner);

runs from sqlyog and result is correct. It returns all the albums that are that I have in the library with this artist.

Inner statement returns “John McLaughlin”

Outer statement is thus equal to

select * from music.artistview where artist = 'John McLaughlin';

mp3_files is the music libray, artistview is a VIEW based on mp3_files (but could be an ordinary/derived table as well!)

*Music spoken Here* is an album by john McLaughlin.

I don't have better data to illustrate it with ….

[peterlaursen]

By the way I don't think you will be able to transfer passwords at all (sync or any other method).

Passwords are encrypted in the base.

[peterlaursen]

No!

Users and their privileges are stores in the DB named “mysql”.

The sync tool only syncronizes the table data in the tables/columns you specify.

Try viewing (probably you must be ROOT) the “user” table in the “mysql” database, and you'll se what I mean.

I YOU have the privileges then in principple tou could syncronize the relevant tables within the “mysql” DataBase

(tables “user” “user_info “columns_priv” and so on. But don't do that if the mySQL versions are not the same, since the format of these tables varies with different version.

[peterlaursen]

And one thing more …

It could be possible to SIMULATE a sync. That would display the number of deleted records, and if people expect 0 (zero) they have been warned. That could be combined with the idea of an SQL-dump.

[peterlaursen]

I think I wrote this before … but it could be very long time ago.

Would it be possible to have an option use a unique index (that is not a PK) for syncronization ?

It would (sometimes – depending on the data) be quite simple to create one on both servers before syncronization runs!

Another workaround would be to create a sql-dump with the deleted (and maybe even the updated) data. That dump could easily be edited and imported if it contains data that should be kept.

I think a common situation is that people use DB's on their websites (that are user INSERTable and UPDATEable) and have a copy of the DB on their laptop and work on it when they are not connected. I can even see some idea with it … That could easily result in conflicting PK's.

But still I think we need a description of the situations where the sync tool as it is now doesn't meet the needs and expectations of users …

[peterlaursen]

I never tried that myself, but did you write “https://…. ” in the URL pointing to the .sqlyogtunnel.php

[peterlaursen]

What I meant

One hint for you: To have more editors, you can open more more instances of sqlyog.

[peterlaursen]

I'm afraid that I don't quite understand your last question. If you mean other programs/clients/applications, then sqlyog is the only one that I know well. With “MySQL administrator” it suffices my needs. I think you must pay about 5 times as much for a program that has more to offer than sqlyog. (referring to the Enterprise-version).

There are some of your suggestions that I can follow, some I can't, and there are workarounds for others.

I'd would like to see the graphical standard of the program develop (more intuitve coloring, icons, popups, hints etc). And more user settings. I don't need drag'n'drop (but on the other hand wouldn't mind). Not so much because it might take quite a lot of code. ( funny enough it is a MUST among DBA's that the their tools be 'leighweight' (I know know why – some adminiser DB's in the TB-range, and yet they think an tool is a catastrophy it it takes more than 10 MB 🙂 ) Rather more Quality (error-prone releases) than quantity. Security is more important than smartness with a DBA tool.

I also would like so se functionality for administering the new facilities with MySQL ver.5 (Stored Procedures, Triggers and Views) develop rapidly.

One hint for you: To have more editors, you can open more connections!

[peterlaursen]

It an answer to your last question only.

Sqlyoug does not analyse the coe. it lets the MySQL server do that.

And I agree theat the error messages from the MySQL server are somewhat annoying.

” You got an error … check the documentation”

It's more than ten years ago that I worked wit software development. And even the any Paxcal compiler or Basic Interpreter would give much better help than the MySQL server does today.

I think we should blam MySQL not Sqlyog.

But We can tell them at MySQL:

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

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

WE WANT THE INTERPRETER ON THE SERVER TO GIVE ERROR MESSAGES THAT WILL GUIDE US TO F-I-N-D THAT SYNTAX ERROR!

Hvis ni inte förstår det, får ni inte mera kjøbe sprit i Danmark!

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

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

(last line is (some kind of) Swedish and means : If you don't understand it, we won't let you buy alcohol in Denmark anymore!)

… and then they will have to distill it from shoe polish again as they did 30 years ago!

😮

[peterlaursen]

Confirmed !

The individual SQL statments in the “dump are MUCH too big for tunnelling (to the server) with a typical 128 kbs DSL line.

Divding each statement into 3.4 pieces works form here!

12,5 MB uploaded then!

Probably the idea was the the client should not negotiate the connection too often, but with tunneling that would be better!

[peterlaursen]

I guess I found something interesting!!!

The problem is that the individual SQL-statements (insert into's blocks) are too long are too long!

It's not the file as such, but the SQL-statements. Thats for HTTTP-tunneling with my ISP

In my case they contain each about 3000-4000 records and takes up about 1 MB within the file each.

If I divide each statement into 3 or four the whole dump runs …

RITESH .. why must the statements be so long ??? probably it's fine with direct connection on port 3306 on a LAN or fast DSL.

But it seems to be a probelm with tunnelling.

Think about a setting to let the user decide or a popup “Use this SQL with tunnelling ?”

I'll verify and report back!

[peterlaursen]

I have exactly the same problem right !

Didn't have it before.

I'm struggling with a 12 MB sqldump/upload right now!

What are they thinking at the ISP's …

I guess I'd better find a professional one!

[peterlaursen]

You shall not 'insert the user into the database'.

You have assigned all global privilges to user 'admin'. So now the user 'admin' can do ANYTHING with ANY database on the server. So just enter details for user 'admin' at the connection screen.

But do you think it is clever to create so a powerful user ? There is the ROOT user available already when you need to have all privileges. But that is rare …

From tools .. user manager .. manage permissions you can assign privileges to databases, tables or columns individually. Maybe that was what you were after ?

For instance with a fresh MySQL installation a user 'test' is created who has all privileges to the database 'test' (also created by the installer – but starts as an empty database) and only to this database.

[Author]

Posts