Export Database

[kimb]

Version 5.12 (but has been in versions since 5.02)

Bug: when you export a database as sql the stored procedures create delimiter statements as follows:

opens with :DELIMITER $$;

end with : DELIMITER ;$$

Trying to execute this from MySql command line interface gives an error. By replacing the $$; with $$ and the ;$$ with ; the code executes without a problem.

Feature request: Please can you select the stored procedures for export without having to select all the tables? It is very annoying to have to edit the output to remove the tables and stored procedures that are not needed.

[peterlaursen]

1)

The 'bug':

This is not a bug! We have been discussing this several times before! Search a little around the Forums!

The implementation of DELIMITER in MySQL command-line client and SQLyog is simply DIFFERENT. And that is it!

DELIMITER is not an SQL-statement, is not handled or understood by the server and is implemented in the CLIENT only. Basically a client supporting SP's will need to be able to distinguish between delimiters

1) INSIDE Stored Procedures

2) BETWEEN SQL statements

… and that can be done in dozens of different ways .. and does not even need to make use of the word DELIMITER at all. You could imagine using the typography (coloring, italics for instance) only.

Also refer to: http://webyog.com/faq/26_49_en.html

But it would be nice if SQLyog supported the MySQL comman-line syntax as well.

But line-breaks are insignificant in the editor component of SQLyog (unlike command-line), and that is why is is not so simple!

2)

the 'Feature request':

Due to the organisation of the MySQL system databases (the 'mysql' and 'INFORMATION_SCHEMA' databases) this is not possible. ROUTINES (SP's and FUNCTIONS), VIEWS and EVENTS are database objects, TRIGGERS are table objects.

Of curse it could be 'coded around' somehow, but that would also be a little dangerous as the user will then have to secure for himself that all tables needed by the SP's are selected. We do not parse the CREATE definition of a SP – simply query the system databases. As any client supporting SP's (including MySQL AB's own GUI tools) does.

[Ritesh]

SP are database level and thus it can access any tables, views etc. in the whole database. This is why we need a user to select all the table for exporting.

If a user deselects a table and it is referenced in some SP then the import will fail as that object will not existing.

[Donna]

Ritesh wrote on May 30 2006, 01:44 AM:

SP are database level and thus it can access any tables, views etc. in the whole database. This is why we need a user to select all the table for exporting.

If a user deselects a table and it is referenced in some SP then the import will fail as that object will not existing.

But in SQLyog I can create a stored procedure that references a table that doesn't exist. It doesn't complain until I try to call it.

There are many cases in which it's necessary to create a script for some subset of database objects. For example, when we upgrade our application we might add 10 new stored procedures. To move from the development environment to the test environment and ultimately to production, all that needs to be scripted are the 10 new procs. Is there a way to accomplish this that I'm missing? Otherwise I concur with the original poster that this would be an excellent new feature, not just for stored procs but for all db objects.

[peterlaursen]

1)

“But in SQLyog I can create a stored procedure that references a table that doesn't exist”

No, that is wrong! Correct is “In MySQL I can etc…”. If the server does not reject it, SQLyog does not either. And should not!

2)

” … move from the development environment to the test environment and ultimately to production, all that needs to be scripted are the 10 new procs. Is there a way to accomplish this that I'm missing?”.

I do not understand the problem. If you compare the databases and there are not other differences than that, then the script generated would only add those 10 procedures.

Basically when querying information about tables no information about SP's, FUNCTIONs, VIEWs or EVENTs is sent by MySQL. SQLyog cannot generate CREATE statements about objects it has no information about.

… but … well … maybe I understand a little anyway. I suppose that the tables are organized in databases another way in the development environment and the production environment? That would typically be the case if you have only one database available at a webhost, but have organized the development environment into more databases. Something like that? Also in the development proces you may have created some tables or other structures that are in (unfinished) development and that you do not want to sync to the production environment yet.

Well, I think you have a point. But it also is a little bit dangerous to play around with … But I think it is technically possible.

[Donna]

“… but … well … maybe I understand a little anyway. I suppose that the tables are organized in databases another way in the development environment and the production environment? That would typically be the case if you have only one database available at a webhost, but have organized the development environment into more databases. Something like that? Also in the development proces you may have created some tables or other structures that are in (unfinished) development and that you do not want to sync to the production environment yet.”

There are many reasons why it's helpful to be able to script out a single database object, including tables, views, stored procedures, etc. Your example above is one. Just today a developer asked me if I know a way to script just a couple stored procedures. She'd been working on her laptop at home and now wanted to integrate them into the development database. They include new stored procedures and modifications to existing stored procedures…and before she makes those changes I want to be able to back up the existing stored procedures just in case!

Another feature that would be very helpful is the ability to script out individual users and their associated permissions in each database.

For the record, though, we are finding SQLyog to be an excellent tool for MySQL!

Donna

[peterlaursen]

1) To 'script out' a few objects you can copy from the OBJECTS tab.

2)

“Another feature that would be very helpful is the ability to script out individual users and their associated permissions in each database.”

What will you use that for? Hopefully not in order copy the information to another database?

This FAQ: http://webyog.com/faq/23_76_en.html has some related information on how you should NOT create user privileges!

However just for reading it would be OK – but what is wrong with

Code:

SHOW GRANTS for 'user'@'host';

?

3)

I still think you have a point 😀 It is not priority at this moment to change anything here. But at a later point of time it may come up again!

[TomBez]

Donna wrote on Jun 8 2006, 12:29 AM:

Your example above is one. Just today a developer asked me if I know a way to script just a couple stored procedures. She'd been working on her laptop at home and now wanted to integrate them into the development database. They include new stored procedures and modifications to existing stored procedures…and before she makes those changes I want to be able to back up the existing stored procedures just in case!

this is why there are numerous version control systems on the market 🙂

[Author]

Posts