Copy Database Bug

[PerFredlund]

Hey!

(my first post here…)

When i use the SQLyog to Copy selected tables and stored procedures to a different database and the copying process detects a duplicate database name the copying process stops. The remaining tables are not getting copied. But they do remain in the original database, so it's not a really big deal. But much worse the stored procedures that i wanted to COPY are getting deleted from the original database. 😮 And they do not appear in the targeted database either. I wasn't asking for MOVING, just copying …

Im not sure if this is a bug with SQLyog or if it's yet another MySQL issue. I still haven't found an administration tools that works satisfactory with stored procedures. The new features in MySQL 5+, including stored procedures, is the reason im switching from MS Sql Server 2000 to MySQL. Not having an administration tool that deals with stored procedures effectively and securely is a bit annoying 😕

Regards,

Per

[Manoj]

Hello,

We are not able to reproduce this bug.

Can you tell me the SQLyog and MySQL version you are using? and exactly what are the steps you did to perform the copy database?

Are you copying to the same server? if not, what is the target server version?

Regards,

Manoj

[peterlaursen]

could you elaborate a little

“… detects a duplicate database name the copying process stops.”

example, dumps, screenshots!! We cannot read your thoughts!

If there is such issue, and if it is a SQLyog issue it will be fixed with high priority!

But you will have to provide a 'reproducable tast case' as we cannot create one ourselves from the information available …

[PerFredlund]

Hey !

Ok, i will supply more details about what i did. I didn't get any other error message than some message about duplicate table in the target database.

Ok what i did was this :

In the SQLyog problem left pan i highlighted one of the databases. Then i chose Copy database to different Host/DB. I selected all tables in the Source Database. And i selected the correct Target Database. And i checked the box to copy Stored Procedures as well. Then i clicked the Copy button. Soon after i got that message about duplicate table names. I did not check drop table if exists in target database. And i did not change the deafult option of copying both structure and data. I wanted the data to be copied over to the other database as well. When i got the message about duplicate table names i clicked ok, and was hoping the rest of the tables and stored procedures would be copied over. But that was not the case. Not all tables were copied over. And no stored procedures were copied over either. The really bad thing was that all stored procedures were gone from the source database 🙁 I do not have any dumps or screenshots of the incidence 😮

I could have accepted that the copying process failed. It was my fault that i had duplicate table names in the 2 databases. But it's really not my fault that the stored procedures had been dropped from the source database since i was only performing a copy operation.

Hope that this information was helpful 🙂

Best regards,

-Per

[peterlaursen]

And I understand that it is not possible to attach a (could be structure-only) dump of the databases as they were before the 'incident'?

What was the program version?

[peterlaursen]

Plase answer too: how did you fill-out this dialogue? (dialogue.jpg)

I just tried to copy the database 'test' from one server to another. Did it twice. I do not understand “When I got the message about duplicate table names … “. The second time I copied I had a dublicate tablename 'Ivan', and did not get such message. 'objects.jpg' shows source after that. There is a VIEW and a STORED PROCEDURE, and they were not deleted on the source.

Did I misunderstand something?

Again: program versions, please! Both MySQL and SQLyog! If we shall track this issue we need EVERYTHING – structure, Names of Tables, SP's etc, EVERY setting, DETAILED program information. That is a so-called 'reproducable test case'.

BTW: If you ever should consider reporting a bug to MySQL they will simply not touch a bug report before such 'reproducable test case' is described in detail. *And reasonable too. Because such issue exists it may occur only in certain combinations of settings, table names etc.

[peterlaursen]

“duplicate table names”

Then I must also ask on which platform the server(s) run? Do you happen to copy between a Linux and a Windows installation, where you (on Linux) have identically named tables except for the LETTERCASE?

Could you explain the “duplicate table names” phenomenon?

[PerFredlund]

Hey again!

OK, more details 🙂

I have attached the settings and tablenames etc in the attached pictures/files. The copying was from one database to another database on the same server : Windows Server 2003 SP 1.

MySQL version = 5.0.18

SQLyog version = 5.13 (free edition)

Luckily the leksjon (source database) was only a development-database and not a production-database 😮 Even i selected to copy table-data as well all tables were empty.

Best regards,

-Per

[peterlaursen]

Please … 🙂

export the database as 'structure only' (include all objects), so that I am sure to get it totally identical – also indexes, table types, possible foreign keys

[peterlaursen]

MySQL 5.0.22 on Windows, SQLyog 5.15 beta 4:

From your description I created the database `test` (copybug.zip). After mulitple copies to database `test2` all Stored Procedures were still there in source as well as target.

Am I missing something?

[PerFredlund]

Hi again 🙂

Have attached the file u asked for. U should make the test2 database contain the table kurstilbyder already. Incidentally that table has a different structure from the table kurstilbyder in the source database 😕

Maybe this whole issue arose from the fact that the table w same name – kurstilbyder – but different structure existed in both databases ??

Kind regards,

-Per

Hi <_< To add to the confusion i tried again the same operation just now. No tables at all were being copied, got the same message about duplicate table name. This time the stored procedures got copied to the target database however, and were not deleted in the source database. This is a much better outcome. But i think the copy operation should at least give me an option to cancel my operation or at the very least copy over those tables that do not exist in the target database … :huh: -Per

[peterlaursen]

Now ..

The whole operation aborts if an error occurs. If you do not check 'drop table if exists in target' AND IT DOES then the whole operation will abort first it tries to copy one from source where it encounters an existing table of same name in target. That option is a SECURITY PRECAUTION (so that no table will not be overwirtten on target if user is unaware of the existing table with dublicate name) and not a BUG 😀 And truly it displays then the MySQL Server error 1050 'table allready exists' (that was what you meant by “dublicate tables names”? (now it is better to repeat the error message exactly/litterally!). Now this is a MYSQL server error and not a SQLyog error! All 4 digit error starting with '1' are MySQL Server errors, http://webyog.com/faq/32_8_en.html

That could easily explain what you write “No tables at all were being copied”. First it tries to copy a table that exists in target allready (without dropping it first), the server stops the show!

I am still not able to reproduce a deletion of any kind (or modification/alter for that sake) in source! And there should not be of course!

[peterlaursen]

BTW: I wonder if you are attempting to 'merge' or 'union' data from two tables into one?

and this

“But i think the copy operation should at least give me an option to cancel my operation ..”

Well .. to do this SQLyog would need to use transactions. It does not and I do not think any GUI tools does.

But this is no bad request as a long term request! perform operation .. preview result and COMMIT or ROLLBACK. Thanks for the idea!

But as of now SQLyog executes a series of SQL statements – and if an error occurs with SQL statement no. 137 all statements from 1 to 136 have been executed!

However most people still use MySQL with MyISAM tables that do not support transactions. But that may change as more storage engines supporting transactions arrive.

[PerFredlund]

Hi 🙂

No, im not trying to merge or union any data. I just wanted to copy tables and stored procedures from one database to another on the same MySQL instance.

What exactly does 'drop table if exists in target' do? Will it overwrite that table (structure and data) or does it mean that it is simply skipping to copy that table ?

I am still of the opinion that SQLyog should query if any table(s) of same name exists on the target database. This can be easily done before submitting the whole 'batch job' to MySQL ? Agree ? Actually this check can be done while selecting tables to be copied and mark those tables with an asterisk or something. I agree that is the user responsibility to check duplicate table names, but it's nice if SQLyog automatically can do this check also. I can't see that transactions come into play if done like i already stated 😕

Ok, as a sidetrack to this thread …

I want to create a stored procedure where i first do a select query. Then i want to perform some action on each tuple in the resulting set of tuples. I can't find anywhere how this is easily done in MySQL.

Thanx for all help,

-Per 😎

[peterlaursen]

1)

“What exactly does 'drop table if exists in target' do?”

It simply executes the SQL “drop table if exists” before copying each table. So there will never be a chance that the server prevents the copy operation for the reason that table allready exists. Unless another client comes 'in between' and creates it first …

2

“I am still of the opinion that SQLyog should query if any table(s) of same name exists … could be done .. agreed?”

well , yes agreed, it could be done. And as you write yourself this should then be tested before building the SQL. But as of now I think the SQL is built 'on the run' and not 'beforehand'. But I am not sure. This SQLyog functionaly is identical to all GUI tools I know off – including MySQL AB's own.

3

“but it's nice if SQLyog automatically can do this check also”

a “check for existing tables in target” -button or something like that is no big deal I think.

The sidetrack:

My first idea (but they may come better):

Let the SP use a SELECT to create an (updateable) VIEW or a (could be temporary) table and do the operations here.

Use VIEW if you want the operations be performed on the underlying tables (and you can then DROP the VIEW when done), a new (temporary) table if not.

[peterlaursen]

I could not help myself creating a simple example (not tested!)

Code:

DELIMITER $$;
DROP PROCEDURE IF EXISTS `musik`.`per`$$

CREATE PROCEDURE `musik`.`per` ()

BEGIN

CREATE VIEW `musik`.`jazz` AS
select `mp3_filer`.`Title` AS `Titel`,
`mp3_filer`.`No` AS `Nr`,
`mp3_filer`.`Artist` AS `Kunstner`,
`mp3_filer`.`Album` AS `Albumtitel`,
`mp3_filer`.`Genre` AS `Genre`,
`mp3_filer`.`Filename` AS `Filnavn`
from `mp3_filer`
where ((`mp3_filer`.`Kunstner` is not null) — skips bad entries in music library
and (`mp3_filer`.`Albumtitel` is not null) — skips bad entries in music library
and (`mp3_filer`.`Genre` LIKE '%jazz')
order by `mp3_filer`.`Kunstner`, `mp3_filer`.`Albumtitel` $$

update `musik`.`jazz` set kunstner = 'duke' where kunstner LIKE '%ellington' $$
update `musik`.`jazz` set genre = 'superjazz' where kunstner = 'duke' $$
update `musik`.`jazz` set genre = 'superjazz' where titel LIKE '%round midnight' or titel LIKE '%bout midnight'$$
— note that these updates affect underlying table `mp3_filer`

select into outfile 'this is superjazzrn=================rnrn' $$
select into outfile * , 'rn' from jazz where genre = 'superjazz'
order by `jazz`.`Albumtitel`, `jazz`.`Kunstner` $$

drop view jazz $$ — we don't need it anymore

END$$

DELIMITER;$$

[Author]

Posts