[peterlaursen]

AVATARS .. It does .. but restricted to 64*64 as far as I remember.

click “my controls”

[peterlaursen]

I am sorry.

The links pointed to nonexistent 5.15 betas – not 5.16 betas.

Now corrected!

[peterlaursen]

it is all explained here:

http://www.webyog.com/faq/8_99_en.html

I think your column is defined as DEFAULT NULL ?

For those few words that are KEYWORDS, you must enclose the string in backquotes like `default` .

[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;$$

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

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.

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

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?

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

“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?

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

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]

A few times (when I think that InnoDB has been too greedy) I have been doing this:

1) exporting data

2) dropping all InnoDB databases

3) disabling innodb in configuration

4) restarting server

5) deleting InnoDB files

6) enabling innodb in configuration again

7) restarting server

8) importing data

But using the innodb_file_per_table option is easier.

Each table will then have its own file.

And you can do the above series of operations (export .. drop .. delete file ..import) for a table or database at a time. And without the need to stop the server.

[peterlaursen]

I think we should try to get deeper into that Scheduler issue now.

Do you have any ideas?

I noticed recently at bugs.mysql.com that they have a pending issue with scheduled backups from 'MySQL Administrator'. Changing the LETTERCASE of one of the parameters did the trick.

Please confirm:

I understand that

1) you add the job to the Windows Scheduler

2) from the Scheduler you can successfully 'execute now'

3) but a scheduled job does nothing – though Scheduler reports that it was run.

Is that correct ?

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

[Author]

Posts