[peterlaursen]

To import a .SQL dump with SQLyog do like this:

menu .. tools .. Import from SQL statements.

“If I right-click on the empty contacts database and select “Run MySQL Restore Command”, SqlYog is looking for its own proprietary MYD file.” NOPE! The .MYD file is not a SQLyog file but a MySQL file – it is the MyISAM DATA FILE itself! The commands 'backup database' and 'restore database' should only be used when backing up and restoring on the same server. Actually they are old commands and largely depreciated, but still supported though.

Now if you intend to import with SQLyog I would recommend that you do the backup with SQLyog! You have two ways to do so: the 'export' tool and the 'secheduled backup' powertool. There can be incompability issues (especially when working across different MySQL versions). Also looks like the is a problem with this phpMyAdmin dump as even not command-line client will accept it. However those issues are most often simple things that can be fixed in an editor! Looks like it is at the very beginning of the file something is happening.

Now .. first try 'menu .. tools .. Import from SQL statements' with your existing file ..

If that won't work either you can copy the first – say 25 lines – in here or try to do the backup with SQLyog.

[peterlaursen]

“SQLyog bombs out with “Unable to start Plink 2:The system cannot find the file specified”

To my best belief this is not correct. It must be the OS that sends this message an not SQLyog. Alone the fact the the message is instantaneous tells that!

However, this “SSH Error: FATAL ERROR: Network error: Connection Timed out.” error message is a PLINK err. msg that SQLyog 'picks up' and sends to the screen.

And this look pretty weird actually! First the OS tells that plink cannot be started. But obviously it IS started – because if it was not it would not be able to issue an error message!

Isn't it possible to give READ and EXECUTE permission to all (also non-admin) users to plink.exe or the complete SQLyog installation folder? I must admit that I know almost nothing about the privilege system on Windows Server systems. But that could be the issue.

Please report any findings!

[peterlaursen]

At this very moment SQLyog User Management is actually only complete as of MySQL 3.23.

Privileges added to the MySQL privilege system from 4.02 and forward are not yet supported.

But now it is a matter of days only before it will be fully complete as of MySQL 5.1. Code is practically all written and testing going on!

[peterlaursen]

“copy/move only the newer data from the more recent DB and add it to the old DB”

SQLyog Enterprise DATA SYNC should do that trick! Even the TRIAL will do (though you will need to import one table per job with the trial – it is restricted to that).

You will then need to import your .SQL file to another database (at the same server or another server – does not matter), and sync the databases as a one-way sync from 'most-recent' to 'old-one'.

Please read this article:

http://webyog.com/articles/Using_SQLyog_En…L_Databases.pdf

.. and understand how the DATA SYNC tool uses the Primary Key of a table. And understand how the sync works without a PK if you do not have such in every table! But with a one-way sync as you'll need here there should be very little chance of any problems.

However always DO BACKUP when you are 'playing around' with such tool that you do not know in detail yet how functons !!!

[peterlaursen]

I am not quite sure I understand! Is it the MySQL server that shall be updated? Now as we are at an ISP I do not think that is the case!

Or do you want to import a V-Bulletin database from an older version of V-Bulletin to a more recent V-bulletin version?

“I have a MySQL DB from a previous date”. Is that a SQL dump (created by the 'mysqldump' program. by phpMyAdmin or by SQLyog – that does in principle not matter!) ?

If you want to import a V-Bulletin database from an older version of V-Bulletin to a more recent V-bulletin version that may not be so simple if V-bulletin has changed their database schema in between! Actually you should import that DUMP into the same version as where it was created and run the V-bulletin update script(s), export again and now you should be able to import to the same V-bulletin version.

Attempting to apply the changes to the database manually or to edit the DUMP script can be very-very-very tricky!

The OS (Linux or whatever) and the webserver (Apache or whatever) is not the problem. MySQL databases are completely the same! But the problem is that V-Bulletin may have changed the database Schema between versions. If they cannot give you a SQL update script and if you cannot figure out the changes yourself, you will have to import to the version where it was created an follow their upgrade procedure – that means execute the update.php script or whatever they do.

Now I do not know if the old V-Bulletin verson is available to you at all? If so you can install it on you local. But you will of course need a webserver (preferably Apache) configured with PHP too. If you never set up this before then .. well .. at least it is a good exercise!

But please first explain more in detail what is to be “update”d. Did I get it right?

[peterlaursen]

I cannot help it …

the 'filtering' option is pretty high priority, however.

We will probably release next version on Saturday, and soon after that update this FAQ:

http://webyog.com/faq/33_20_en.html

.. with more accurate priorities.

The phrase here “There are plans for a GUI SQL-builder as you find it in Office Suite -type databases” also covers such filtering option.

The priorities as of now are like:

1) User management complete as of MySQL 5.1 and the remaining 'small isuues' that we allready announced in the 5.1 tree + a few pending bug-fixes and optimizations. Should be around 1 week now.

2) Full Unicode compliance. Add 2-3 weeks. But this has some 'uncertanity' built in.

3) GUI-related: Filtering, GUI SQL-builder, better Spreadsheet interoperability. This will be done in parallel with extracting strings from the code itself to enable localisations. Plus better support for BINARY data (BIT, BINARY, VARBINARY and (non-image)BLOB types.

4) Events and Partitions support (actually Events may come before, as it is quite simple to implement)

But at this very moment I cannot be exact on the 'timing' of issues in category 3)

We will do detailed 'man-power' planning next week and publish details.

[peterlaursen]

'find' and 'replace' are text editor functions. Cursor must be placed in the SQL-editor area / a Query tab (up-right).

Now if you click once in the editor and expand the 'edit' menu are they still disabled?

To search and replace in the DATA, you will need to exectue a

“SELECT .. FROM WHERE …” and then edit data in the RESULT tab

.. but we do plan some 'filtering' option similar to Office-type programs.

[peterlaursen]

Do you have the option to connect with SSH?

[peterlaursen]

The SQLyog HTTP-tunneller is only functional with SQLyog and SJA ('SQLyog Job Agent'). It cannot be used as a 'generic proxy' by other applications.

However the Tunnelling technology of SQLyog would allow for that as well. We plan to release this feature as a seperate application in some months.

[peterlaursen]

Can I just grant all to root@% to all databases?[/quote]

Yes, the SQL is then

Code:

Grant all on *.* to 'root'@'%' with grant option;

.. after that every 'root' user (no matter from which host he connects) wil have ALL privileges just as 'root'@'localhost'.

.. and to execute this successfully user must have ALL privileges including GRANT option himself! By default 'root'@'localhost' has.

[peterlaursen]

Yes .. Stored Procedures are stored within the database.

To create it execute the 'create procedure ….' statement. Once you have entered the ('create …') definition in the SQLyog editor click the 'double arrow' icon, and the create statement is executed – and the SP created and saved.

Quote:

If I define a stored procedure I'm prompted to create a .sql file.

I do not understand! A new editor TAB opens with a 'Stored Procedure' template. But that is not a 'file'.

Quote:

I've also gone into permissions and granted root@% all permissions on this particular database

HOW did you do that? As of now SQLyog does not support 'Create Routine' privilege (coming soon). You could execute:

Code:

Grant all on the_database.* to 'root'@'%' with grant option;

Please execute

Quote:

Show grants for 'user'@'%';

.. it does not look as if privileges are in place!

BTW: what is the MySQL version?

[peterlaursen]

SQLyog and SJA only connects to MySQL using TCP-IP and neither 'named pipes' (on Windows) or 'socket' (on *nix)

You have some other software using a 'named pipe' on port 3301. However that should not prevent you from using TCP-IP too on (typically) port 3306.

Is the Server configured not to used TCP-IP? Any particular reason for that? You can cooment-out 'disable-networking' in MySQL cofiguration then (you will need to retart the server).

BTW: I think that it is well known that 'named pipes' on Windows are less efficient that TCP. On some *nix systems 'socket' is most efficient.

[peterlaursen]

you can do yourself like this:

1) create table test.user select * from mysql.user where user = '****' and host = '****';

2) export table test.user as 'data only' SQL

3) import wherever you want to use it.

4) do not forget to 'flush privileges' !!!

But this wasy of manipulating users is not supported by MySQL! And when working across diffrent MySQL versions be aware that the user table is different

I do NOT support a functionality that just does 'GRANT ALL'all the time …

What is your use of such functionality?

[peterlaursen]

@Intel352

Are you sure that makes any difference?

I just tested with my chess database with + 4 mio. rows

Code:

/*four queries below that return the same value: '4150229'*/

/* 1 */ select count(*) from game as no_s;
/* 2 */ select count(id) from game as no_s;
/*these two are both instantaneous – '0 ms taken'
– are you able to get different results with some table of yours? */

/*however using the DISTINCT clause slows down things a lot – approx 30 seconds*/
/* 3 */ select count(distinct id) from game;

/*using a subquery is better for some reason */
/* 4 */ select count(*) from (select distinct id from game) as no_s;
/*takes about 10 seconds*/

Now I'll have to ask developers if SJA DATA SYNC uses DISTINCT.

If there is a PK there should be no need to!

[peterlaursen]

OKOKOKOK .. I give up then … let us return to this later next week!

[Author]

Posts