Database Objects Versioning In Sqlyog

[weiliang]

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

Thanks

[peterlaursen]

We have request for integration in version control systems before and we have this recorded:

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

But your request is different. And impossible to implement. For two reasons:

1) SQLyog does not know what other clients are doing

2) A client cannot do this as the server does not expose such information for clients. The server has no records of objects history (unless exposed in the general query log and this will practically never be complete for objects history from the 'very beginning'). And a client cannot even read the general log (only an Operating System user can)-

What we *could* do was to implement some version control for SQL scripts handled by SQLyog. This will make good sense if SQLyog *alone* is used for development of a database application (but will fail if other clients than SQyog are used). But even this is not planned for near future.

[weiliang]

Of course, every client have to use SQLyog in order to have version control feature.

We need this feature because we have several DBA developing stored routine and sometimes they are altering table definition. The situation is complex when we are trying to find out which routine and/or table definition is newest and bug fixed.

If SQLyog doesn't support this and won't support, do you have any recommendation how to overcome this. Thanks

peterlaursen wrote on Jun 3 2009, 04:45 PM:

We have request for integration in version control systems before and we have this recorded:

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

But your request is different. And impossible to implement. For two reasons:

1) SQLyog does not know what other clients are doing

2) A client cannot do this as the server does not expose such information for clients. The server has no records of objects history (unless exposed in the general query log and this will practically never be complete for objects history from the 'very beginning'). And a client cannot even read the general log (only an Operating System user can)-

What we *could* do was to implement some version control for SQL scripts handled by SQLyog. This will make good sense if SQLyog *alone* is used for development of a database application (but will fail if other clients than SQyog are used). But even this is not planned for near future.

[peterlaursen]

Even with 2 different SQLyog instances this is almost impossible. If only it can be guarateed that a single client will write to the database (and that is practically never the case) it will be possible to implment client-side.

You will find solutions. Often they are named 'auditing software' or 'auditing solutions'. They will need to be installed on the server. I think they will use the general log with some log rotation system , extract information from it and insert information to ts own database on the server (or they may even require that server is compiled with some extensions adding such system tables). In principle it is not much different from what we do in MONyog with the Query Analyzer, but as this one focuses on performance and not auditing we do not extract that information from the log. So should we consider this it would be part of MONyog – not SQLyog.

A google search with 'auditing +mysql':

http://www.google.com/search?client=opera&…-8&oe=utf-8

.. finds more questions and requests than solutions and answers! And if you find a solution it may not be very well-featured in 'stored programs' support but will mostly focus on tables (CREATE/DROP/ALTER tables and databases). Expect it to be quite expensive and mostly with a subscription-based license model.

Basically the problem is that MySQL is not designed for it. Oracle is as I read some of the above links.

[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.

[Author]

Posts