[peterlaursen]

Where should such information be stored? An alias saved in a client will only have effect in that particular client. And storing on the server be against our concept not to touch the server we are working with (with any kind of information internal for SQLyog)

And also it would be a major task to implement because when we generate SQL statements we read from the GUI. It would require quite a lot of changes related to our internal datastructures and lot of checks/lookups in the code, I believe.

The various problems with shared hosting are welknown and today I don't think we will do many 'tricks' to work around them as the Cloud (Amazon etc.) offers hosting with much more user control and 'shared hosting' is not as important as it was some years back. And even with 'shared hosting' there are most often different 'hosting plans' with more or less options (and at different prices). Have you checked what options you have (change the hosting plan/account type or even the hosting company)? The hosting you are using seems more-than-average-dump to me in this respect. I used 'shared hosting' quite a lot 5+ years ago and never experienced this (only my user name was frozen to a machine-generated one).

Anyway we can always record a 'feature request' if you insist. But the chance that it will ever be implemented is low.

The best option currently is to use the option to define a color for connections so that you will easily see on what server a specific database resides with less chance of confusing them. Are you aware of that option (check in the advanced' tab of the Connection manager).

[peterlaursen]

I have added the discussion above to our issuetracker here:

http://code.google.com/p/sqlyog/issues/detail?id=1954

As regards to let 'copy database' CREATE a database on the target server i have alos added:

http://code.google.com/p/sqlyog/issues/detail?id=1954

.. and this is a very reasonable request.

As regards the reqeust for checking that necessary storage space is availble on the target, this is plain impossible for a MySQL client to do. There is no way to query the server how much more diskspace it has access to use. And even if we could access the file system on the server (what the MySQL client-server protocol does not provide for, and thus it would only be possible on local host) the available storage space does not necessarily tell what is available for MySQL. Also all disk space could be utilized but still there may be available space in databases files allocated by MySQL storage engines (such as ibdata* files allocated by InnoD:cool:. On production servers it is not unusal to let InnoDB allocate 1-10GB per datafile and it also may be configured not to claim more space automatically (it will then log to the error log when it needs more diskspace).

You will not find any tool doing what you want. Because it is not possible with MySQL – in particular not if it is a remote server (a different machine than where the client/tool is running). I wonder if you have a Microsoft SQL Server background? Here ther are many more options as recent versions will expose a lot of internals through a .NET/C# interface. MySQL does not. We can send SQL queries to MySQL – and that is a client can do with MySQL.

Did I forget to reply something?

[peterlaursen]

That said I think we execute (on target) basically this:

CREATE TABLE table1 ..

INSERT INTO table 1 ..

..

CREATE TABLE table2 ..

INSERT INTO table 2 ..

..

It could be instead:

CREATE TABLE table1 ..

CREATE TABLE table2 ..

INSERT INTO table 1 ..

..

INSERT INTO table 2 ..

..

.. where we could wrap all INSERTs in a single transaction like this:

CREATE TABLE table1 ..

CREATE TABLE table2 ..

START TRANSACTION ;

INSERT INTO table 1 ..

..

INSERT INTO table 2 ..

..

COMMIT;

If the job does not succeed and the connection in where the the job runs aborts/closes before COMMIT is executed, the server will (with InnoDB – and only with InnoD:cool: automatically rollback all INSERTs. But the CREATE TABLE statements cannot be rolled back (refer my link in previous post) and the tables created would still exist as empty tables. However in case of error we could next popup a dialogue to user asking if he wants to drop all newly created tables – “Job was unsuccessfull .. do you want to drop what tables were created by the job?” – or whatever similar. It would be a little complicated multithreaded programming to do so (because the copy job runs in a separate thread), but nothing is impossible of course.

[peterlaursen]

As regards the request to rollback, please tell if you are using InnoDB or not. With MyISAM tables it is simply impossible to rollback what statements are executed. With InnoDB it is – but not quite as per MySQL documentation: http://dev.mysql.com…it-commit.html. Every CREATE TABLE will cause an implict commit by the server. What means that we can only rollback INSERTS to the last table created and nothing more. And I actually think we do (by wrapping the job in a transaction – but also this I will need to have confirmed on Monday).

What you are asking here is a server functionality. The client cannot do what the server does not support. This is a feature request for MySQL/Oracle and not for us/SQLyog. Transactions will need to be handled server-side. There has been some criticism about those implicit commits in MySQL before, but that is how transactions in MySQL/InnoDB works (and is different from most other RDBMS – at least different from SQL Server, as far as I remember). And with other storage engines than InnoDB (such as MyISAM) transactions don't work at all – and thus the term 'rollback' is nonsense in a non-InnoDB MySQL context.

The other points we will return to after the weekend.

[peterlaursen]

I have added this request to our issue tracker here:

http://code.google.com/p/sqlyog/issues/detail?id=1951

[peterlaursen]

OK .. but do you know if there is any standard for such protection?

Various programs can do it (Winzip, Winrar, 7zip etc.) but can one of those programs decrypt a file encrypted by another? I know that some programs will use 128 bit encryption and others 256 bit – and byte/bit order may differ and there could be other incompabilities. What would then be the 'canonical' standard we should follow? What program should be used for decrypting? And is there a code library available with a license that can we can use in SQLyog?

We will need to research on this before promising anything.

[peterlaursen]

You don't see the same in 'mysql' client and SQLyog because they use different encodings. SQLyog is utf8 – 'mysql' is ANSI (unless you use the unicode version of 'mysql' shipped wiht MySQL 5.6 or 5.7.

What we could do was to display (VAR)BINARY data like “x'25CE00E973E525C6D70E41B88E2C0937'”. Just like we now display BIT types as “b'0'” (for instance). But we don't have resoruces to start this right now.

[peterlaursen]

additionally you need not save yoru .sql scripts in the favorites folder. You can save a shortcut/link to (a) file(s) at any position in the file system.

[peterlaursen]

OK .. I now understand.

Please try with the 'mysql' command line client to execute something like “SELECT binarycol FROM table”. You will also get some weird display, right?

We never promised that we would display anything different from what is stored in the database in the DATA tab. And what is stored is NOT “25CE00E973E525C6D70E41B88E2C0937” BUT “UNHEX(“25CE00E973E525C6D70E41B88E2C0937″)”. To display it in readable form (in SQLyog or 'mysql' CLI etc.) execute “SELECT HEX(binarycol) FROM table”.

It is an OK request that BINARY data (at least BINARY and VARBINARY columns) should – optionally or not – be displayed in HEX format, similar to how we already now display BIT columns in binary/base2 notation. It is a request that I personally added to our issuetarcker and that I personally would see promoted.

Currently you have the option to work from the RESULT tab in SQLyog populated from a query using the HEX-function for display of data. Or a more simple option: store the GUID as an ordinary (ASCII) string and do conversions in the application when needed.

[peterlaursen]

Please read our issue-tracker here:

http://code.google.com/p/sqlyog/issues/detail?id=15

We know that we have issues wiht the BINARY type in particular. What happens if you use a VARBINARY?

[peterlaursen]

Well .. SQLyog depends on other 'software modules' when importing external data (ODBC drivers, MySQL 'LOAD DATA).

Can I summarize your request to two points:

1) When using ODBC import for data ssoruces that are not 'real databases' (and there is no schemata) SQLyog should identify data lengths?

2) Support for more file types/extensions when importing data from 'flat files'.

Please confirm that I understand!

I think the answers will be (but please reply to the question above anyway):

As regards 1) it will only be able to to find the max data length after data has been imported. You cannot query the data source about it. In other words we need to create DDL before DML. I don't see any other option. SQLyog and we at Webyog are not magicians.

As regards 2) this is a limitation with the text ODBC driver from Microsoft. It identifies files with specific extensions as (possible) data files. However if you use LOAD DATA syntax file extension does not matter.

[peterlaursen]

This FAQ describes the error:

http://faq.webyog.com/content/23/36/en/i-get-error-1130-host-is-not-allowed-to-connect-or-access-denied-or-could-not-connect-.html

.. as well as possible reasons and solutions.

Please check if there is something you can use here.

[peterlaursen]

It is reproducible and it is a bug (obviously).

We plan a small bugfix release today or tomorrow. We are checking now if we can fix this issue as well in this release. We may need more time to do a proper fix. But we will update here.

It is actually a very old bug. 7.x versions are also affected. Maybe it always existed. We have tested this functionality with dumps (created by SQLyog, mysqldump etc.) and here multiple statements on a single line do not occur. And no user reported this either. This is not an excuse, but an explanation.

[peterlaursen]

It should reallynot matter how you use SQLyog: from editor or from GUI, including the Object Browser. In either case SQLyog jsut sends SQL statments. The server cannot know how the statement was generated.

So this is very strange.

You write “This happens intermittently, and may occur it the GUI has been unused and open for an extended period of time (i.e. left open overnight)”. Well, then it would probably happen with any client.

What happens if SQLyog is left idle for long time probably is that the server has disconnected SQLyog. SQLyog will then reconnect first time it sends a statement. Is it only one command/statment that fails to be logged or is it everyone after the reconnection/idle period?

What are the exact server versions BTW? The latest 5.5 version is 5.5.32 and if your server is not that – and if particular if it is much less – please try to upgrade the servers. Early 5.5 servers were quite buggy.

Besides you have posted in the MONyog category and I will move your post to the SQLyog category. 🙂

[peterlaursen]

“somewhere” could have been this FAQ: http://faq.webyog.com/content/27/74/en/how-do-i-execute-a-job-with-sja-with-a-single-mouse_click.html

[Author]

Posts