[DonQuichote]

 

Either you don’t understand what we are telling or we don’t understand you. For instance you claim that with 8.12 “syntax is coloured”.  It also is with 11.4

I know. In fact, I copied my settings from 8.12 to 11.4, so the option was “on” in both cases. However, only the last word in each line seems to be syntax coloured in 11.4. So there is “some” syntax colouring, but not really useful. Yes, I checked the colouring settings for keywords and so on and the application should behave otherwise. Also, autocompletion only seems to work after you have typed  the query with all the FROM and JOIN clauses, so typing the JOIN statements require a lot of looking up. This really breaks the thought process in designing a complicated query. That was the killer “feature” that made me wonder why I was torturing myself with the new version and re-installed the old one.

 

I know that I cannot re-use an open SSH port and should not try to. So all my stored connections list the port number in their name and use a different local port number. I never had any problem with that, it even helps me to connect other applications through the same port.

[DonQuichote]

The new version was way too much of a productivity killer, so I uninstalled 11.3.3 and installed 8.12 again. What an improvement!

• SQL is legible
• Autocompletion works
• syntax is coloured

And, as a bonus, the SSH tunnels have a defined port number, so other applications (the one I am developing, for example) can hop along.

[DonQuichote]

I don’t know if there is a fool-proof way if you are not on the server. You can check

 

SELECT @@version_compile_machine;

but I don’t know if compiling for 32 bit on a 64 bit system would yield a useful result. If you are at the server, typing

 

mysqld --version

 

in a terminal should provide the necessary info.

[DonQuichote]

Too bad. As you see, it was not redundant.

[DonQuichote]

Ah. Now I see. The option has disappeared. It was there in 8.12 (see oldtools.png) but is no longer there (see newtools.png). Or maybe it has become “edition-dependent”. I don’t know if there were as many editions back then.

[DonQuichote]

That’s my problem. I want the formatting unchanged (I want to see what is there), but the new auto-completed keyword values in upper case. I tried converting the values in Keywords.db to upper case, but that did not have any noticeable effect.

 

I upgraded from an old version (8.12).

[DonQuichote]

The only thing SQLyog could do that would satisfy all customers or at least would not get in their way is to define some interface and let your application run as a plug-in. This is off course no small feature. Especially for blobs, this could be helpful. In fact, I already requested a comparable feature before. (see http://www.webyog.com/forums/index.php?showtopic=3211 )

Such a plug-in or external command interface could be quite “dumb” in the sense that it does not need to do any content parsing. You could leave that to the called plug-in or script. For instance, most formats start with some kind of header. The user can write a script that looks for supported headers and starts the appropriate application. For edit actions, the contents of the field could be written to a temp file and the temp file could be monitored for changes. For read-only cases, just start the application with the location of the temp file without monitoring.

If C scares you, you could try to adapt CrunchyFrog. It is an open source application written in Python with a plugin structure. It is not as feature-complete as SQLyog, but it is easily extended.

But I can understand why you would not implement all kinds of editors. SQLyog is like a swiss army knife. It is not suited for a specific business process, but a general database front-end. In my opinion, an extension possibility would fit the kind of application it is.

Alas I am not a C programmer…

[DonQuichote]

There is a trick: Issue an ALTER IGNORE TABLE command with a uniqueness constraint. It will delete any superfluous rows (if permitted by any other foreign key constraints off course). After that, you can optionally delete that constraint. But it is good to have an identity column in every table (autonumber primary key).

[DonQuichote]

Yes I saw it. It's funny. This site should have been in place when MySQL was handed over to Sun. Now MySQL is already out of hand, this site comes much too late. You can petition what you want, but the independent MySQL is already gone for quite some time now.

It's good good that the site says that the GPL is not the answer to the problem. Maybe it could be, if someone with a good insight and programming skills or at least programming resources (the ability to invite or hire programmers) would take it over. For now, the most famous fork is Drizzle, which more destroys MySQL than saves it. Lots of features and even complete OS support is dropped and what's left is too little to be a serious or even feasible MySQL alternative.

Which makes me sad. As I think now, there is no good alternative. The only database that is somewhat capable of being used in an evolving project is MS SQL Server, but of course it does not run on any of our servers or even my workstation (it won't run on Linux). PostgreSQL would run on it, but seems totally inadequate for agile work.

[DonQuichote]

'marquix' wrote on '15:

Hi everyone,

I found this software in my free time as I was exploring my interest in learning more about mySQL. I essentially put together Joomla and WordPress websites but started to learn more about how the database sort of works when before it just seemed like this far away mysterious hidden database somewhere I couldn't really see or grasp the way I can see create and modify a graphic for example. My question is now what? I was able to connect using tunnel, make changes (that saved me tons of time) and even back up the database (which did not appear identical to the one phpmyadmin makes).

For making a backup, the data is read and a backup is distilled from that. Both programs do that in their own way.

'marquix' wrote on '15:

Now what I ask because my thoughts were along the lines of making my SEO work a little easier. Learning how to create content without having to use the interface and begin to understand content for what it is. Pure text, nothing really more but information, DATA. Does anyone know of any real world training that I can get for direct database content building or methods that builders use? Also, I've had people ask me to create like a Multiple Listing Service database from scratch but I just do not know how yet. What would someone suggest I do to learn the tricks that people do stuff with using SQLyog. I'm sure everyone has their shortcuts and methods of doing things. I have a mysql for beginners audio but it seems too boring and so far away from real usage. I almost wish I could work for someone part time or for free just for the experience but it's difficult around here since I live in Mexico.

I think you are trying to work “backwards”. Long ago, you gad to write queries or modify the source code of a site to make any changes to a site. The content management systems exist to make the changes easier than directly in the source or in the database. If SEO stands for Search Engine Optimization, I think the CMS is probably the easiest tool, or at least it should be. SQLyog is more like a swiss army knife: it can do almost anything, but it may not be the best tool for all everyday tasks.

'marquix' wrote on '15:

Any comments would be greatly appreciated. I'm essentially a home taught wannabe trying to survive and move up in this exciting and profitable IT world.

Okay. First, let me tell you I am a programmer. I write programs in an evolving way: they start small and grow. The databases have to grow with them, in both structure and data. I wrote the following howto about that:

http://www.howtoforge.org/node/4833

Also, if you want to transfer changes on your development database to a production database, it can be good to see what queries were actually sent to the database (by a CMS, for example). To do that, enable the query log in either my.ini (Windows) or my.cnf (Linux) on your development machine and use a program like Baretail to monitor it.

'marquix' wrote on '15:

I have a new computer I purchased solely for working with SQL or Linux (not sure what to do with it yet) and just have the curiosity what guys like you would have on a computer for database work. What assortment of programs, OS, or trinkets make up a DB Admin toolkit? I'm a wannabe trying to hang out with the big boys and just learn a bit on the way. Like I said, any advice will be greatly appreciated.

I use SQLyog (off course), DBDesigner for the design work (alas it is getting quite old, but I will never switch to MySQL workbench), Baretail, the MySQL command-line tools and subversion (both command-line and through TortoiseSVN).

I do a lot from the command-line and can update a site by starting one script: that script updates the PHP and SQL source from subversion, “builds” the SQL recreate script and runs it. I do this on my development machines as well. This way, working at home is almost as easy as working at the office, and every update for a development machine is a test for a an update on the live server.

'marquix' wrote on '15:

I have a new computer I purchased solely for working with SQL or Linux (not sure what to do with it yet)

The combination (Linux AND MySQL) is very common. So you may do both. You can even keep on using SQLyog if you install wine (a system to run Windows programs on Linux). I have this setup at home and at work and it works like a charm. If you want to keep Windows on that machine (I suspect it comes with Windows preinstalled), you may set up a dual boot configuration or you may even install Linux inside the windows partition. If you run Linux inside a virtual machine, you can have Windows and Linux running at the same time.

[DonQuichote]

'eddierosenthal' wrote on '01:

is there a way to see where in the executing sql the process is currently?

the sja log is empty, but is there some other place to observe the execution in some other log file?

If the server is on a development machine, it can be wise to activate the query log in my.cnf or my.ini. You can use a log viewer (like baretail, just search the net for it) to monitor it. Off course this is totally different from what monyog does. For live servers, this can be useful also, but only if you know what you are doing. Otherwise, it is just a performance and diskspace eater.

Even on your development machine, rotate or delete the query log once in a while to keep it feasible.

[DonQuichote]

peterlaursen wrote on Jul 13 2009, 01:05 PM:

We have been doing this for the last 6-9 months already. Just use the option to 'generate sync script' and you will see. Doesn't it?

I downloaded the latest version (8.12 enterprise) and to find that option. I was using 8.06. I now see your answer to where the option is located. I just made a dump with tools>”Backup database as SQL dump”. I use SQLyog purely for development. Scheduled backup is done with scripts on the Linux servers. Thanks for the answer.

[DonQuichote]

I can really recommend not being “lazy” and providing a name yourself. That name is then not dependent on the tool you use and consistent across servers (so your update / upgrade scripts will work consistently). I would not like SQLyog to be inconsistent with MySQL. If you do not give a name, MySQL provides one in a consistent fashion. I would not want that process hijacked by my database frontend.

[DonQuichote]

weiliang wrote on Jun 3 2009, 11:16 AM:

Hi, do you have plan to implement version control system of table schema, stored routines, etc using CVS, SVN or others.

Thanks

As others already pointed out, this is near to impossible with a database front-end program.

I use subversion for all my databases though. The main issue with automated programs is that there are different kinds of data, which no program can tell apart:

• system data
• test data
• live data

System data should be in subversion. Period. it is part of the system. Test data should also be in subversion, but not necessarily rolled out. It should be “at hand”. Live data should never pollute your version control system. Off course, test data should not be in the way of live data either.

Let's make it more difficult now. I also use externals a lot, to have separate “modules” in my standard library. So my database version system should be able to include files from subdirectories.

Now we're at it: all my files should be repeatable. This means that I can run such a file, and if something goes wrong I fix it and just run it again, without spending a few days to hunt and undo the effects of the half-run script. This should cause the right situation to appear.

You will have guessed by now that you should be 100% in control of what code is sent to your database. That immediately rules out all automatic synchronization libraries and -systems. So I use SQL alone, with a very small tool to make the inclusion possible from other directories.

To make a script repeatable, here are a few tips:

• Do everything in the right order (e.g. drop the depending table before the lookup table).
• Use IF NOT EXISTS in CREATE statements and IF EXISTS in DROP statements
• Give everything a name. Especially indexes and foreign keys, or you won't be able to modify them anymore.
• Use the IGNORE keyword in INSERT, ALTER TABLE and DELETE.
• Use ON DUPLICATE KEY clauses
• Write temporary procedures when it really gets tough. You can query the information_schema database to see if you should add something or not.
• Use the @@session.sql_log_off variable to suppress the GRANT statements to appear in the querylog.

Using the above techiques, I have quite a few databases in subversion. An update on a development machine is just running both the recreate and the test data script. Rolling out on the live server is nothing more than a subversion update and then running the recreate script. I can update a site in about 15 seconds (I cannot type the mysql root password THAT fast), including the database.

Maintaining the scripts seems a lot of work, but you are only maintaining them. You will save yourself a giant lot of work from the fact that any error is easily forgiven on your development machine.

For the technique and the inclusion script, see:

http://www.howtoforge.org/set-up-a-modular…or-php-websites

Hope this helps.

[DonQuichote]

eduperosa wrote on Apr 26 2009, 07:10 PM:

I'm having a problem when I try to make a table with 2 foreign keys. This table should make the n:n relationship between two other tables. So I have this message “error number 1005” and “Can't create table 'dbeduardo.#sql-8b4_2c' (errno: 121)”. Does anybody know how to make n:n relationships?

You do not tell us what queries you use or what you do exactly, but you can define two or more foreign keys without any problem. There are a few things that can go wrong:

– Not all engines support foreign keys,

– The data type must be the same at both ends (you cannot have a VARCHAR column as a foreign key to an INTEGER column, for example). It must really be the same, so TINYINT and INTEGER are not compatible.

– The foreign key must have an index, as Peter already told. Newer versions of MySQL add one automatically if it does not exist.

Some tips:

– Give the foreign key constraint a name. This name must be unique in the whole database (not just in the table). If you give a name yourself, you can use the code on different machines and they will have the same name. This means that a ALTER TABLE .. DROP FOREIGN KEY will also work on all those machines.

– The same with the index. Define it and give it a name yourself (only needs to be unique in the table) and the DROP INDEX statements work on all machines.

[Author]

Posts