[DonQuichote]

peterlaursen wrote on Mar 3 2009, 11:42 PM:

anyway we released 8.03 beta2 and you should be able to use your file from the editor as well now!

Confirmed. Thank you! This also allows me to change the INSERTs to INSERT IGNOREs without firing up a separate editor…

[DonQuichote]

peterlaursen wrote on Mar 3 2009, 10:08 AM:

But can't you run your file from tools .. restore from .. ?

Ah. That was the feature I overlooked. I did use the 6.14 version for the real database server (the error was on my development database). It would be nice if SQLyog would give a clear warning that only part of the file can be read (and suggest to use the restore tool instead).

My biggest problem is that I do not want to run failing or half scripts on a live database server. I personally make sure most of my SQL is repeatable, but such third-party files are of the “fire-and-pray” type. That is why I always run them against my local development database first.

[DonQuichote]

Ouch! So what you see is what you don't get! As all our live databases run on Linux (case sensitive!), this is a really nasty option. Tables are not keywords and even MySQL is intelligent enough to see the difference (well, often). Please do NOT try to outsmart the user!

[DonQuichote]

GreenAlien wrote on Dec 4 2008, 05:44 PM:

Will be interested to know how well Sqlyog Enterprise works using a WINE or equivalent. Those that have been using Sqlyog like this, can you comment on how well it performs, eg:

– Stability. Any crashes?

– Connecting to local MySQL database (installed on Mac OS, not through WINE)

– Connecting to a remote database through SSH

– Syncing structure and data between local and remote databases.

Stability is fine. I did experience crashes only for some beta versions. I am using Linux thoug, and not a mac. But I still hope my experiences are useful for mac users. Connecting to local databases never gave me any trouble whatsoever. These databases are installed on the natibe OS (Xubuntu Linux on different machines). For connecting to remote databases, SQLyog works fine with SSH, but the normal SSH keys are not used. To convert a traditional SSH key to a “putty” key as used in SQLyog, see:

http://www.webyog.com/forums//index.php?showtopic=4454

I always use SSH connections for connecting with remote databases, and never had any problems. I do not use SQLyog's synching method, as I want to be in absolute full control for all database upgrades. This means more work in writing the SQL files, but also being able to upgrade any database, including the live one, without any data loss.

Hope this helps.

[DonQuichote]

Linda Patterson wrote on Jan 20 2009, 09:35 PM:

Hi there,

We use SQLYog (Community) to make a manual copy of our database from our server (mySQL v5.0.32) to a local machine (mySQL v5.0.27). However the copy converts all tablenames from mixed to lower case. (All field names, on the other hand, retain their mixed case.)

Any idea why this happens and how to fix it? Would changing the character set of the new database (currently defaulting to latin1) make any difference?

edit the my.ini file on the new destination machine (it sounds like Windows, as on other systems this setting is not the default). Now add the following line to the server section of that file:

Code:

lower_case_table_names=2

Now restart the mysql service. You should actually do this before you define any database on that server. So if you still have that backup, drop the database entirely, set the above setting and recreate the database from the backup. In my opinion, the default setting on windows is actually a bug. Messing up the character case is always wrong.

Using another character encoding should not make a difference, as SQLyog tries to be consequent in it. If you want to use another character encoding, I would recommend reading the backup first, and transform the encoding afterwards. There is a special SQL command to do so:

ALTER TABLE  CONVERT TO ;

[DonQuichote]

Maarten Schouten wrote on Jan 15 2009, 01:00 PM:

does anybody no about possibility of Command Line Execution? Something like “SQLyog.exe mysql://user:pass@host:port”

I think this will be the easy way to open a MySQL database remotely by starting SQLyog from our password manager application. 🙂

Well, that leaves your password easily recognizable in the process / task list, and if there is a history running (I don't know if there is a stored history on Windows), your password is kept visible for a long time. Also, this method is quite complicated when you use tunnels (which I sincerely recommend if you connect over the big bad internet). And why use a password application if you can just store all connection data in SQLYog?

[DonQuichote]

Larry Woods wrote on Jul 27 2008, 05:29 PM:

In the past, using MS Access (Sorry for cussing) I could use VB to do difficult table structure conversions. SQLyog is VERY powerful and I am impressed with all that I can do with it, but I can't do all of the data manipulation that I would like.

There is another programming engine already available to you: MySQL itself. Is there anything you want to do that you cannot do with stored procedures?

Alternatively, if you are on windows, you can off course still use MS-Access for programming. If you are on Linux, you may find Knoda useful (note that Knoda does not deal correctly with character encodings if you do not send the right SET NAMES command yourself).

[DonQuichote]

peterlaursen wrote on Jun 24 2008, 10:18 AM:

“Also, the standard window box buttons on the upper right of the “document” window (minimize, close, unmaximize) do not work.”

I remember this issue wiht a specific Wine version. It was fixed in next release. But you are using 1.0, so that should not happen?

Anyway .. maybe an old bug found its way back to Wine?

I figured out what went wrong. i still had wine configured to emulate Windows 98. Setting it to Windows XP solved the windowing issue.

[DonQuichote]

garf wrote on Mar 13 2007, 01:31 PM:

And anoter question (didn't know where to put it):

I used this code to add a record to table 'adItems' when I used access.

once I added the record, I pulled the new id of it (just after it was created).

[codebox]

Set rs = Server.CreateObject(“ADODB.Recordset”)

rs.open “select * from adItems”,conn,3,3

rs.AddNew

rs(“catId”) = catId

rs(“expiry”) = expiry

rs(“adTitle”) = adTitle

rs.Update

newid = rs(“id”)

rs.close[/codebox]

now, that I moved to MySql, newid remains blank. why is that? Any idea?

You add a new record to a SELECT query. Not to a table. This code is probably from an MS-Access database, where you open a recordset based on a table and add a row. You might see what happens if you do that for MySQL. I don't know how intelligent the ODBC driver is. In general, build an INSERT query and check for the value of LAST_INSERT_ID().

[DonQuichote]

Samer Fayssal wrote on Jun 9 2008, 08:58 PM:

PK allows you to reset the table counter. It means the following: if you add a new record and the table automatically gives this record a new serial number, and then you decide to delete one of those records, pk allows the table to reconfigure itself and reassign the old serial bumber to another record. You need to check your environment before using it.

That is exactly what a primary key should not be. It is true that in most databases there are hacks to reuse primary key values, but they are really hacks. Primary key values should not be reused by their very nature. A primary key value is not only unique, it is unique along the life span of the system. A normal unique key is just unique “at any monent”. So if you have an old primary key value that is not in the table, you should be confident that that record was deleted. The other way around, if you delete a record, you should be confident that no other record will take its primary key value and therefore its identity.

A primary key denotes identity. Identity is somewhat abstract. Two rows can carry the same data, but still be “brothers”. They are not one row. Like twin brothers are different, even if almost every property of them is the same. So make this identity visible and workable, it is usually embodied in an autonumber that is added to the “data columns”. This is also the reason why a primary key should never have any meaning as data: equal dat would reduce to equal identity, which is just not true.

[DonQuichote]

peterlaursen wrote on Mar 15 2008, 04:11 PM:

I am talking about SQLyog (Enterprise) Data Synchronisation feature! Try read about it in the help file!

No need to export scripts to sync a test/development and a production database when it can be done much easier!

So I think we have the feature in our (Enterprise) product already required for the task.

OK, I briefly looked into it. I think I'm in for a good read if I want to make it modular. But I won't do it now. It's weekend…

peterlaursen wrote on Mar 15 2008, 04:11 PM:

Sorry, but sometimes we will have to ask people to pay for features!

That's OK, I'm running the enterprise edition anyway.

[DonQuichote]

I do not know what you mean with “data sync”, but if that is a synched master-slave replication, it would not fit my needs.

For me, it is a way to deploy a site fast and consistently. We have a lot of PHP code and SQL code in a subversion repository. That repository is set up in “modules”, which can be linked to. Those modules have a PHP part and an SQL part (if they need them, off course). I wrote a script that can handle “include” comments in SQL, so effectively I can re-run the entire definition of a database in SQL, even if a lot of it comes from standard modules in other files.

So, in short, it would greatly help me to build (re-)deployment scripts.

This means I can check out the total project code anywhere (at my development PC, at home, at testing machines, at the live server) and get both the database and the PHP structure up-to-date in seconds. Most people would probably call this “continuous integration”, I guess.

So, effectively, there is a lot more to sync than just data. It is the complete definition of the whole project, which can be deployed wherever the conditions are right (mysql and subversion client).

Alas, some of our live servers do not yet support stored procedures, but you'd be amazed what you can do with plain SQL: “IF (NOT) EXIST” clauses, SQL variables, “LAST_INSERT_ID()” and “ON DUPLICATE KEY UPDATE”. There are a lot of little tricks in SQL. For instance, here is a nice one (where `code` is unique):

CREATE DATABASE IF NOT EXISTS example;


USE example;


CREATE TABLE IF NOT EXISTS Templates(...)


CREATE TABLE IF NOT EXISTS TemplateField(...)





INSERT INTO Templates(code,contents) VALUES('main','test') ON DUPLICATE KEY UPDATE templateId=LAST_INSERT_ID(templateId),contents=VALUES(contents);


SET @TemplateId=LAST_INSERT_ID();


INSERT IGNORE INTO TemplateField(id,fieldname) VALUES(@TemplateId,'one');


INSERT IGNORE INTO TemplateField(id,fieldname) VALUES(@TemplateId,'two');

Look at the first INSERT command. If the row did not exist, if would be inserted. If it did exist, the parameter to LAST_INSERT_ID() would reset that function to the existing ID of that row (but not change the existing ID in the row) and the contents would be updated to 'test'. So this script is completely re-runnable, even after you change the contents of the template to something more useful. On a live server, it does not even use DELETE commands to ensure that the INSERT commands will not fail.

I hope I am not boring you with details here…

[DonQuichote]

REPLACE INTO actually deletes the existing row. This means that all values that are not given are replaced by their defaults. This is not likely a wanted situation. For instance, any autonumber field is not updated by a REPLACE INTO statement, but actually deleted. So imagine a table “Relations” with a row like:

ID=3 (autonumber), Name='willem', RelationType='User'

After

REPLACE INTO Relations(Name) VALUES('Peter');

You will have a situation where the above row is deleted and a new row is inserted, for instance:

ID=26 (autonumber), Name='Peter', RelationType='' (default value)

This will break any relationship with foreign keys. If you are lucky (restrict type), you will get an error because the record cannot be deleted. If you are unlucky (cascading type), you can lose a lot of data.

INSERT IGNORE does just that: try to insert a row and do nothing if that row already exists (actually, a unique key constraint would be violated). Such scripts are re-runnable as well, but not editable or replaceable: if you modify a value that is not in the unique key, a re-run of the statement will do nothing.

There is one caveat: INSERT … ON DUPLICATE KEY was introduced in MySQL 4.1.0 and will not work in earlier versions. REPLACE INTO is somewhat older.

[DonQuichote]

peterlaursen wrote on Feb 1 2008, 09:36 PM:

2)

What reconnect we do is that if connection gets lost due to *some accidental error on the line* we will connect by creating a new server process/thread.

I am not adding anything technical to this discussion, but have a question about it. Is a connection also repaired when executing a batch of SQL statements? In that case, does it open a new connection to the server? The reason I ask is that I use SQL variables a lot in my “repeatable database build” scripts. For those scripts, failing half-way is better than repairing with a new connection (because those variables are lost in the new connection).

[DonQuichote]

peterlaursen wrote on Dec 11 2007, 05:30 PM:

I know this setting in PSPAD. I was just not sure what file you would use as such 'plugin' for PSPAD!

No, I do not use a plugin for PSPad.

Quote:

I don't think our own CHM will be appropriate. This is instructive for the GUI – it is not an SQL guide, and is not intended to be either!

That is why I was suggesting that you let the user provide the helpfile by downloading it from the MySQL site, just like PSPad does.

Quote:

But there are other sources:

1 :In the 'edit' menu we have 'templates' allready (though rather elementary).

2: There also are help tables in the 'mysql' database (see `help_topics` table for instance). I think the help tables are only populated with recent server versions and I also think you can populate them further with some SQL script, but frankly do no remember details any more!

So basically what I could suggest was that we look up in those (1st lookup in help table, next our own templates) and display a tooltip when alt+f1 is pressed.

I think we can look up a selected/marked string. If no string is marked then the word where the cursor is positioned will be considered the selected/marked string.

Frankly, I did not think of that. The help in the database is way to little. A typical situation when I would use the context sensitive help is for really specialized things, like: I am writing a SELECT query and want to force the use of an index. I do not use this feature often, but I know it exists in the help of the SELECT command. So I press alt-F1 to see the syntax of this feature and read more about it. Or: I want to look up the format stings of the DATE_FORMAT function. I think tooltips are too small for these things.

I looked again at my installed version of MySQL and I saw that there is an URL field in the help table too. That is great. It points you to the online docs, which are very complete. Even better, the help table automatically points to the right version of the help for that server. So firing up the default browser with the given URL would also do the trick.

The major drawback to this solution is that the database user you connecting with needs read permission on the “mysql” database.

Quote:

Did I get you right this time?

I hope so! I see context-sensitive help as an automated “expert who can show you the details”.

[Author]

Posts